Slow query on mid-size tables with good index coverage I'm running a query that touches 2 moderately sized tables (1-5 million rows). EXPLAIN shows it touches only indexes and very few rows, however it takes very long to complete (~8 seconds). The only thing that might be of concern is the temporary table it creates. If anyone has any ideas, it would be appreciated!

Query:

SELECT `visits`.`id`
FROM `visits`
INNER JOIN `visits_views` ON visits_views.visit_id=visits.id
GROUP BY `visits`.`id`
ORDER BY `visits`.`created` DESC
LIMIT 20

EXPLAIN:

id  select_type  table          type    possible_keys   key     key_len     ref         rows    Extra
1   SIMPLE       visits         index   PRIMARY     created     4           NULL        20  Using index; Using temporary
1   SIMPLE       visits_views   ref     visit_id    visit_id    4           visits.id   1   Using index

Table structure

CREATE TABLE `visits` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `affiliate_id` int(11) DEFAULT NULL,
  `referer` varchar(250) DEFAULT NULL,
  `domain` varchar(70) DEFAULT NULL,
  `user_agent` varchar(300) DEFAULT NULL,
  `country` char(2) DEFAULT NULL,
  `ip` int(10) unsigned DEFAULT NULL,
  `bot` tinyint(1) NOT NULL DEFAULT '0',
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `domain` (`domain`),
  KEY `created` (`created`),
  KEY `referer` (`referer`),
  KEY `ip` (`ip`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

CREATE TABLE IF NOT EXISTS `visits_views` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `visit_id` int(11) NOT NULL,
  `url` varchar(250) NOT NULL,
  `prev_id` int(11) DEFAULT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  KEY `visit_id` (`visit_id`),
  KEY `created` (`created`),
  KEY `url` (`url`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

In order to make very small temp tables, you need to rewrite the query to do this:

LIMIT first on the 20 most recent created timestamps
then JOIN those 20 ids to visits_views

Here is the proposed query

SELECT A.id FROM
(SELECT id FROM visits ORDER BY created DESC LIMIT 20) A
LEFT JOIN `visits_views` B ON A.id = B.visit_id;

I use LEFT JOIN to preserve the order of the subquery

If you want to count the views

SELECT * FROM
(
    SELECT A.id,A.created,COUNT(1) views FROM
    (SELECT id,created FROM visits ORDER BY created DESC LIMIT 20) A
    LEFT JOIN `visits_views` B ON A.id = B.visit_id
    GROUP BY A.id,A.created
) AA ORDER BY created;

I have addressed making small temp tables in queries like this. See my post Fetching a Single Row from Join Table in StackOverflow. It demonstrates how I did a LIMIT 40 on a subquery and used that 40 row result set to JOIN to other tables.


Error creating connection pool for dbid: We have an application that uses TomCat and Java to receive files and move them to a SQL Server 2012 instance.

Previous versions of this software worked just fine.

The new release fails on our system with the error

  Error creating connection pool for dbid:

Many hours have been spent adjusting the configuration to get past this error with no positive result.  Any insight on what the error means and how it might be traced would be appreciated.

Connection Pooling occurs completely at the client-side of an application, is seldom affected by the SQL Server directly.

Connection pooling depends on parameters in the connection string used by the client to connect to the SQL Server.  Identical connection strings are typically candidates for pooling, assuming the following connection string settings allow pooling:

╔════════════════╦═══════════════╦═════════════════════════════════════════════════════════════╗
║    Setting     ║ Default Value ║                         Description                         ║
╠════════════════╬═══════════════╬═════════════════════════════════════════════════════════════╣
║ Max Pool Size  ║          100  ║ The maximum number of connections allowed in the pool.      ║
║ Min Pool Size  ║            0  ║ The minimum number of connections allowed in the pool.      ║
║ Pooling        ║            1  ║  Enable connection pooling.  0 Disables connection pooling. ║
╚════════════════╩═══════════════╩═════════════════════════════════════════════════════════════╝

The SQL Server may be running out of service worker threads, if existing connections are not closed properly, which would typically result in connection problems.  If the max worker threads server configuration option is set incorrectly, or too low, that might be a problem.  The default value for this option is 0, indicating that SQL Server will automatically configure the value, according to these rules:

╔══════════════════╦══════════════════╦═════════════════╗
║ Number of CPUs   ║ 32-bit computer  ║ 64-bit computer ║
╠══════════════════╬══════════════════╬═════════════════╣
║ <= 4 processors  ║             256  ║             512 ║
║ 8 processors     ║             288  ║             576 ║
║ 16 processors    ║             352  ║             704 ║
║ 32 processors    ║             480  ║             960 ║
║ 64 processors    ║             736  ║            1472 ║
║ 128 processors   ║            4224  ║            4480 ║
║ 256 processors   ║            8320  ║            8576 ║
╚══════════════════╩══════════════════╩═════════════════╝

You can check the configured value using this query:

SELECT c.name
    , c.value
    , c.value_in_use
FROM sys.configurations c
WHERE c.name = 'max worker threads';

Based on the error message, it looks like your client end is configured incorrectly, but that is only a guess.  Check with your developers or vendor for help determining the cause of the problem.


Accessing Shared Folder Using Oracle Database I have two machines A & B. I shared a folder from machine B and mapped it as a drive on machine A. Now I want to configure my Oracle Database in Archive Log mode and want to set the Archive location to that mapped drive. I have also set full (Read/Write) permissions to that folder. But for testing purpose I tried to create a file on that folder through SQL as:

SQL> CREATE pfile='z:\archive\pfile.ora' FROM spfile;

It says:

ERROR at line 1:
ORA-09210: sftopn: error opening file
OSD-04002: unable to open file
O/S-Error: (OS 3) The system cannot find the path specified.

How can I make this drive available for Oracle Database?

Regards,
Ali

To troubleshoot this type of erro follow the steps bellow :

1- make sure that the location where you want to write exists.
2- make sure that the oracle(or the user who runs the database instance) user has the right to write in that
location.(try to create txt file in that location as Oracle user.)

Ok , more steps for you to troubleshoot:
- i see you are running Oracle on Windowzzz! Correct ?
 Go to your Services tool and see who(what account) is running the Oracle services, form here make sure this user has access to the directory where you what to write to!

Now maybe is better for you to write into another location(your user home directory):

SQL> CREATE pfile='C:\Users\user.name\Downloads\pfile' FROM spfile;

if the error persists then your problem is different !
From this point you can think of security baselines etc...


PostgreSQL 9.1: vacuumdb vs. autovacuum Have a question on the difference between just allowing autovacuum to take care of vacuum'ing or having to run a full db vacuum in a certain interval (every night?, every week?).

We used to run a full vacuumdb every night before a full backup.  It used to take around an hour and a half.  Then we stopped and just allowed autovacuum to do it's thing.

Is that a good idea?  Should there still be a vacuumdb run every night, week?

I noticed some processes tonight...

"autovacuum: VACUUM public.large_table (to prevent wraparound)"

Is that normal or is that because we stopped running a full vacuumdb every night?

Thanks all for any insight.

A manual vacuumdb should only be needed for special circumstances, like after running pg_upgrade or after doing some kind of bulk load or major maintenance.  Otherwise, just let autovacuum do its thing.

If your database is mostly idle at night, you can still do vacuumdb and it will concentrate the maintenance into the off hours so that autovacuum is less likely to kick in during the day.  But if you do that, you probably still shouldn't turn off autovacuum.

The vacuums to prevent wrap around are normal for any system that processes hundreds of millions of transactions over its lifetime.  They are nothing to worry about.  It is sometimes nice to know when they are occurring, because anti-wrap around autovacuums will block some kinds of work, such as index builds, whereas regular autovacuums will not.


What are the disadadvantages of a datawarehouse with no datawarehouse tables, but only datamarts? The ETL design pattern prescribes transformation from staging tables to a central data warehouse data model, followed by extracting data from the data warehouse data model to data marts.
Are there any limitations, and what could go wrong, with transforming data from the staging tables directly into a data mart?  (e.g., skipping the data warehouse data model)
Doing that would save a lot of development time and effort in my view.  It would also save time when the data warehouse model changes.  Data history could be accomplished in the staging layer.
The data warehouse my organisation is currently developing (based on the ETL pattern) seems to take ages.  We do need a data warehouse/business intelligence solution, but it doesn't need to be extremely advanced; we are a midsize organisation (1000+ employees).
Would using ELT instead of ETL save development time? (e.g., writing SQL queries for the transformation instead)

The purpose of the data warehouse is to serve as a single source of truth1 for your BI consumers. It provides you with the central place where the data and metadata for your reports and dashboards originate. If you deprive it of  this role and create a multitude of data marts populated by your operational data (via staging tables or not), inevitably each of these data marts will contain its own "source of truth", not necessarily correlating with what other data marts think is the truth, thus diminishing the value of business intelligence derived from these multiple truths.
Building a data warehouse is indeed a notoriously long and failure-prone process, but developing ETL procedures is not nearly the most time-consuming part of it; it is master data management and modelling that takes the most time and effort.
Also, the size of your organization is barely relevant; it's the value that you expect to derive from your BI and, indirectly, from the data warehouse that feeds it that should drive your decision to take shortcuts.

1 - Some say it's a single version of truth, because ultimately the source of those data is elsewhere.


How to put the results of a query into a variable? I have written a Postgres anonymous block. In which I have written join query on multiple tables like:

select a.portal.*, a.p_fb_config.*, a.p_gplus_config
from a.portal
   left outer join a.p_fb_config on a.portal.pid = a.p_fb_config.pid
   left outer join a.p_gplus_config on a.portal.pid = a.p_gplus_config.pid;

Now I want to catch this value in variable. So how should I declare that variable?

I need something like:

         portal_row a.portal%ROWTYPE;//for 3 tables

I assumed you were using a pl/pgsql block (probably a function).

For the generic case, you can use record, it can basically take a row of any structure, often used like

DECLARE
    i record;
BEGIN
    FOR i IN SELECT this, that, something, else FROM some_table
    LOOP
        ...
    END LOOP;

There is also a possibility of defining a view:

CREATE VIEW v_portal_data_with_config AS
select a.portal.*, a.p_fb_config.*, a.p_gplus_config
-- note that this does not work straight away, there will be a column naming collision,
-- so you'll have to add column aliases (which then makes using * impossible)
from a.portal
   left outer join a.p_fb_config on portal.pid = p_fb_config.pid
   left outer join a.p_gplus_config on portal.pid = p_gplus_config.pid;

Having this, you can then

DECLARE
    i v_portal_data_with_config%ROWTYPE;
BEGIN
    SELECT * FROM v_portal_data_with_config WHERE ... ;

Sometimes, when having the view makes sense anyway (the query appears in multiple functions, for example), this may me a more convenient way.  However, you introduce an additional dependency which makes harder to change the logic.


postgres function definition not saving top whitespace For reference I am running postgres 9.6 . I'd like to write functions inside of postgres where the top of the function saves.

 create or replace function
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on

When I build this, if I relookup the function definition in datagrip the top part loses my white space I put in there and it looks like this.

create or replace function apqp.apqp_list_gentex_tool_report_getlist(_omni_search text DEFAULT ''::text, _cust_id integer DEFAULT 0) returns TABLE(reason text, program text, mold_number text, rev text, stone_part_number text, part_description text, cust_request_num text, status text, kickoff date, mold_delivered date, first_shot date, ppap_submission_date date, interim_approval_expiration_date date, tool_shop text, manager text, apqp_status text, apqp_list_id integer, comments text)
language sql
as $$

Is there a setting in postgres or someway to save to the database when I CREATE a function that it will save my white space at the top. I'd like to be able to repull the definition while maintaining my white space.

The function body is the only part that is actually saved as string, conserving all white space, code comments etc. Stored in pg_proc.prosrc. It will be parsed etc. at execution time.

The rest of the command is executed immediately and translates to an entry in system catalog pg_proc. What clients like DataGrip later present is the reverse engineered representation derived from system catalog information. Original insignificant whitespace is completely lost in the process. The format is what the client chooses to make it. (DataGrip does not seem to do a great job in the example.)

To preserve original format, you'll have to save your create statement somewhere. Like in the COMMENT on the function - I suggest another layer of dollar quoting, or you'll have to escape single quotes:

COMMENT ON FUNCTION apqp.apqp_list_gentex_tool_report_getlist(text, integer) IS
$com$
create or replace function
apqp.apqp_list_gentex_tool_report_getlist(_omni_search text    DEFAULT ''::text
                                        , _cust_id     integer DEFAULT 0
                                         )
 returns TABLE(reason       text
             , program     text
             , mold_number text
             , rev               text
             , stone_part_number text
             , part_description  text
             , cust_request_num  text
             , status text
             )
language sql
as $$ -- and so on
$com$;

Or a code repository or a user table in the DB or whatever. DataGrip has built-in ways to save and handle files:

https://blog.jetbrains.com/datagrip/2019/01/30/working-with-files-in-datagrip/


How do I write a conditional statement (IF) with MySQL? What is wrong in below simple query? I tried to google it but not found

IF 1 = 1 THEN
  SELECT 1;
ELSE
  SELECT 12;
END IF;

What you want is a CASE expression which is standard-SQL method of implementing a conditional and supported by every major database,

SELECT CASE WHEN 1 = 1 THEN 1 ELSE 12 END;

Or you can use the totally silly and proprietary IF statement

SELECT IF( 1=1, 1, 12 );


Is it possible that information_schema.columns doesn't hold an user table column information? I am facing a strange issue here. I have a table which is not visible in information_schema.columns view. What could be the reason for that.

Note: I am using RDS Aurora Postgresql 9.6.12

You will only see entries for a table in the information_schema views if you have permissions on the table in question.

You can get column metadata for tables where you have no access from pg_attribute, but you have to deal with PostgreSQL's internal data structures that may change between versions.


Log shipping on secondary server Log shipping fails with the below error. Sql agent on secondary server has access to the folder and files in security. It is not a firewall issue. I created the jobs using the LS scripts as it fails through GUI.

I have done it before on a different server where there were several LS databases. This is a new primary and secondary server and not sure what I am missing. Thanks for the help

***** Error: Access to the path '\\sqlp\R$\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\Database' is denied.(mscorlib) ***
----- END OF TRANSACTION LOG COPY     -----**

I've ran into this same problem! As Mr. Vernon stated, the account needs admin access to the server.  However, that is not the recommended way of providing access to the service account, especially if you're to follow the "least privilege" security mantra.

Instead, perform the following:

Create an appropriately named security group
Add the SQL Server Agent account to the group
Create a shared folder on the server you're shipping the logs to
Assign the newly created security group with Modify permissions to the folder


SQL Server Schema Security My company uses a multi-tenant database (one database, all tables have a customerID field).

We are in the process of setting up a data warehouse and giving clients direct access.  Our initial thoughts on architecture are to create a schema for each customer, with views in each schema that providing the filtering by customer ID.

i.e.

Main Database

dbo.Table1

Schema1

schema1.Table1 (View) Select * from dbo.Table1 where custId = '1'

This works, except the user with rights to schema1 can ALSO do a select all from dbo.Table1.  I would like to prevent this.

Is that possible?

Yes.  Simply grant the user that you create that lets them log into the SQL Server SELECT rights only on the view and not the table.

You could actually do all this through a single view.

Create a function that looks up the CustomerId based on the username.  Then call that function as part of the view.

create view YourView as
select *
from YourTable where CustomerID = dbo.YourFunction(suser_sname())


pgAgent not working on linux (Ubuntu 14.04) I am running Postgres 9.3 on Ubuntu 14.04 and I am unable to get pgAgent daemon/service to run.

I have confirmed that the daemon is not running using service --status-all.

When trying to run pgagent (whilst logged in as the only user in ubuntu) using pgagent hostaddr=127.0.0.1 dbname=postgres user=user1 the following messages are displayed:

WARNING: Couldn't create the primary connection (attempt 10): fe_sendauth: no password supplied

ERROR: Stopping pgAgent: Couldn't establish the primary connection with the database server.

The lack of running pgAgent was also confirmed when trying to run a job on demand.

The user 'user1' is able to log into pgAgmin and connect to the database from other computers on the local network.

The user looks like this in the pg_hba.conf file:

# Database administrative login by Unix domain socket
local   all             user1                                md5

A .pgpass password file also exists for this user in the home directory.

What is causing the above messages and what needs to be done to get pgAgent to run scheduled tasks?

Late answer but...

  What is causing the above messages and what needs to be done to get pgAgent to run scheduled tasks?

Seems that, as stated in comments, this boils down to why the .pgpass file is being ignored.

Reading the documentation I found the following relevant details on this page, emphasis mine:

  On Unix systems, the permissions on a password file must disallow any access to world or group; achieve this by a command such as chmod 0600 ~/.pgpass. If the permissions are less strict than this, the file will be ignored. On Microsoft Windows, it is assumed that the file is stored in a directory that is secure, so no special permissions check is made.

So, if your .pgpass file does not have such permissions it will be ignored. This could be what is causing the error you see.


Why get queryplans recompiled every minute I have a server running SQL Server Enterprise 2016 SP2 and I noticed that every minute several hundred queryplans get invalidated.
I run sp_BlitzCache @expertmode=1 to check the 'Created At'-column to see when the plan is generated.
Every minute about 500 plans are re-created and this continues the entire day and it are always different plans that get recompiled.

When I use the DMV sys.dm_db_index_usage_stats to check the user_updates I see that there happen between 10.000 and 1.000.000 updates on the indexes.
Some tables have more than 10.000.000 records but most of them have between 100.000 and 2.000.000 records.

As far as I know a queryplan gets invalidated in following circumstances:

Modifications made to table/view definition by ALTER TABLE/ALTER VIEW
Changes made to any indexes used by execution plan
Updates to the statistics used by the execution plan (manual or automatically)
Dropping an index used by queryplan
sp_recompile / with recompile
Large numbers of changes to the table
Adding/dropping a trigger on a table used by the execution plan
Memory pressure

All the queries use KEEP PLAN and KEEPFIXED PLAN, so I think automatic or manual update of the statistics will not invalidate the plan. This is a third-party application so I can not alter the queries.
When I run UPDATE STATISTICS [TableTest] WITH FULLSCAN I don't see a recompilation of the queries that use that table.

Next I used a script by Jonathan Kehayias to check if there was memory pressure but the script only returned RESOURCE_MEMPHYSICAL_HIGH so I do not think this is the problem.

No modification to the table definition, indexes happened nor was the query executed with sp_recompile / with recompile

Is there something else that can cause invalidation of a queryplan?

Update:
After some more research I think the recompiles are triggered by memory pressure on the plancache. This article gives a lot of information about the plan cache internals. This is about SQL Server 2005 but hopefully this still applies to SQL Server 2016.
According to this article you can calculate the pressure limit based on the total amount of visible target memory.
My research advances slow because it is hard to find info about pressure on the plan cache.
I'll keep you posted on the progress

After some extended research I found the cause of my problem.
So to calculate the cache pressure limit SQL Server uses following formula:

  75% of visible target memory from 0-4GB + 10% of visible target memory from 4GB-64GB + 5% of visible target memory >64GB

Since SQL Server 2012 and above, memory pressure is triggered when a cache store grows to 62.5% of the plan cache pressure limit.
When this happens, plans are removed from the cache using an algoritm called eviction policy, which is based on plan cost.
So at certain moments I see a drop in the size of my plan cache and this scenario happens several times in a day, causing every time the compilation of new plans.


How to restore dump from MariaDB 10.1 to MySQL 5.5 I generate backups of my databases by using mysqldump.exe -u root --all-databases > mariaDbBackup.sql.

When I try to restore my dump by doing mysql -u root -p < mariaDbBackup.sql, I get an

  Error 1064 (42000) near 'STATS_PERSISTENT=0'

Here is the faulty line:

CREATE TABLE `engine_cost` (
  `engine_name` varchar(64) NOT NULL,
  `device_type` int(11) NOT NULL,
  `cost_name` varchar(64) NOT NULL,
  `cost_value` float DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `comment` varchar(1024) DEFAULT NULL,
  PRIMARY KEY (`cost_name`,`engine_name`,`device_type`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 STATS_PERSISTENT=0;
/*!40101 SET character_set_client = @saved_cs_client */;

It is the first line out of seven on the backup that includes STATS_PERSISTENT=0 so I assume it might have to be a compatibility issue. I am using specifically MariaDB 10.1.37 and MySQL 5.5.39.

I assume there have to be some flag in either generating or reading the dump that I can use to be able to do it.

So, how can I restore the dump or how do I generate a compatible dump?

I've not tested this myself, but in the MariaDB mysqldump main page there is a paragraph about the --create-options switch which seems to indicate you can solve the problem by using the switch --skip-create-options:

  Include all MariaDB and/or MySQL specific create options in CREATE
  TABLE statements. Use --skip-create-options to disable.


Retrieve double data type value in non exponential form Is it possible to retrieve non-exponential value in double data type column? After the width of the column is increased to 15 it shows the values in exponential form.

Now I get the following:

mysql> create table abc(a int,b double) engine innodb;
mysql> insert into abcd values(10,123456789123456789123456789);

mysql> select * from abc;
+------+-----------------------+
| a    | b                     |
+------+-----------------------+
|   10 | 1.2345678912345679e26 |
+------+-----------------------+

Instead of this I want it to be shown in the form what I entered as 123456789123456789123456789 on mysql client.

It isn't exactly possible to do what you are attempting to do, because a double precision floating point value cannot contain the number 123456789123456789123456789 without a loss of precision.

A "double" is stored in 8 bytes of memory and is limited to around 15 significant digits of precision -- not by MySQL but by the nature of a "double."

When you perform the insert shown above, you're actually storing 1.2345678912345679 x 10^26 ... so MySQL is actually returning the value correctly.

It is possible to coerce the number back to the original format but not to the exact original value because you didn't store the exact value.  Cast it to a sufficiently large DECIMAL value and then cast the DECIMAL to a CHAR:

SELECT CAST(CAST(b AS DECIMAL(30)) AS CHAR) FROM abcd;

The value returned is 123457890123456790000000000... which is correct (despite appearances to the contrary) because that's what's being stored in the field... but the value expressed in that format gives an inaccurate sense of precision... which is presumably the reason it's being returned in exponential notation in the first place.

If you need the precision, you will want to store these values in a DECIMAL column, not a DOUBLE.


Query another schema on a different sid Oracle I have 3 db's that should be in sync with each other, but there are some differences that I am trying to rectify.  We try to stay out of the main schema and do all of our work on a separate user schema, the data is then streamed around if updates are made.

I need to be able to query data on a different schema on a different sid to get the tables in sync, but I get an ORA-01017: invalid username/password; logon denied when I do so.

For example when I am logged in as schema2 on sid1:

/* works */
select * from schema1.my_table@sid1

/* throws an error */
select * from schema1.my_table@sid2

Likewise when I am logged in as schema2 on sid2

/* works */
select * from schema1.my_table@sid2

/* throws an error */
select * from schema1.my_table@sid1

Is this type of query possible?  There is already a database link created between the db's.  I use connect through when I log on so I do not know the schema2 password and I am afraid to change it.  Is it that they are different on all three nodes?

I hope this is making sense, please ask for any clarifications.

Your database links are configured with the wrong passwords.


What causes pt-online-schema-change to slow down the further it gets through a table? I'm currently running a schema migration on a table containing ~23 million rows. The rows have a normal primary index and we're running MySQL 5.5.

The migration, to add a couple of columns, started quickly. At a rapid rate, it copied the first 3 million in about 3 minutes. The next 3 took about 20 minutes. Overnight, it did another 9 million in about 8 hours.

Other than modifying the replica lag I'm willing to accept, I've not modified the defaults - so it's obeying the chunk time and aiming to get each copy in the ~0.5 second range.

As I'm running the tool with PTDEBUG on so I can see detailed output, and it would appear the operations are correctly in that range - but I don't understand why they're getting slower and slower. At peak it was around 7000 rows a second, and it's slowly dropping further and further, we're down to ~470 now and still have ~15million rows to go.

I have several assumptions, but I'm not sure which is correct, or how to diagnose it.

Are any of my assumptions correct, and how would I identify which?

It's the index on the original table - the query itself is taking too long. I'm assuming it's not this, as the SELECT is off the primary key.
It's something to do with the insertion process. The larger the table, the more "thinking" has to be done about the INSERT and so that process is taking longer.
There's a hardware limitation somewhere. We're running on RDS with 3 replicas, by the way. Monitoring doesn't suggest we're hitting a bottleneck at the moment.

Here's the CREATE TABLE statement anonymised:

CREATE TABLE `user_offer` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `CreatedDate` datetime NOT NULL,
  `A` int(11) NOT NULL,
  `B` int(11) NOT NULL,
  `C` int(11) NOT NULL,
  `D` datetime DEFAULT NULL,
  `E` datetime DEFAULT NULL,
  `F` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
  `G` int(11) DEFAULT NULL,
  `H` int(11) DEFAULT NULL,
  `I` int(11) NOT NULL,
  `J` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `K` tinyint(1) NOT NULL,
  `L` tinyint(1) NOT NULL,
  `M` tinyint(1) NOT NULL,
  `N` tinyint(1) NOT NULL,
  `O` int(11) NOT NULL,
  `P` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Q` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `R` tinyint(1) NOT NULL,
  `S` tinyint(1) NOT NULL,
  `T` int(11) DEFAULT NULL,
  `U` tinyint(1) NOT NULL,
  `V` int(11) DEFAULT NULL,
  `W` datetime DEFAULT NULL,
  `X` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
  `Y` tinyint(1) NOT NULL,
  `Z` tinyint(1) DEFAULT NULL,
  `AA` int(11) DEFAULT NULL,
  `AB` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `IDX_CB147C66AAB016B8` (`I`),
  KEY `IDX_CB147C6668D3EA09` (`J`),
  KEY `L_idx` (`L`),
  KEY `H_idx` (`H`),
  KEY `IDX_CB147C6632A90D98` (`T`),
  KEY `IDX_CB147C66F6CB3049` (`V`),
  KEY `createdDate_idx` (`CreatedDate`),
  KEY `consentedDate_idx` (`W`),
  CONSTRAINT `FK_CB147C6632A90D98` FOREIGN KEY (`T`) REFERENCES `table_1` (`id`),
  CONSTRAINT `FK_CB147C6668D3EA09` FOREIGN KEY (`H`) REFERENCES `table_2` (`id`),
  CONSTRAINT `FK_CB147C66AAB016B8` FOREIGN KEY (`I`) REFERENCES `table_3` (`id`),
  CONSTRAINT `FK_CB147C66F6CB3049` FOREIGN KEY (`V`) REFERENCES `table_2` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=23448523 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

The alter statement is: ADD AC VARCHAR(5) plus adding indexes on AC, P and Q.

A guess...  The Change Buffer is full and overflowing.

The operation is building 11 secondary indexes.  For InnoDB, the building of secondary indexes is "delayed" by storing to-do info in a part of the buffer_pool called the Change Buffer.

Eventually the "changes" need to be flushed to disk.  Your table is probably much bigger than the buffer_pool, so it is probably overflowing.  Here's (roughly) how the 11 indexes for a given row are being created for the new table:

Write 11 records to the Change Buffer.  Such a record is (I guess) the secondary key column(s) plus the primary key column(s).
Check for overflow.  If running out of room (which you are by now), read some index block from disk, fold the index entries into it, queue the block for writing back to disk, and free up the index entries from the Change Buffer.

Initially all 11 indexes were empty, so the above process was dumping blocks full of stuff out to disk -- very efficient.

As the processing continues, the indexes get bigger, and the "dumping of more entries" becomes less efficient because there are more blocks to deal with and few records to put in each one.  That is, each read-modify-write step is freeing up fewer and fewer entries from the Change Buffer.

This feeds back to slowing down the processing, which pt-online-schema-change smartly turns into smaller chunk sizes.

Alternatives

It is sometimes better to rebuild indexes after building the table.  However, you probably don't want to because it would involve downtime.
In 5.6 (and more in 5.7), many ALTERs can be performed with very little impact on the running system.  5.5 is now the oldest supported version; you should consider upgrading.  (Pt-online-schema-change is still be useful for cases where online alters do not apply.)
Vertical partitioning -- Instead of adding columns to this table, you could have considered building a 'parallel' table with the new columns.  This can usually be done with no downtime.  Although some SELECTs will need a JOIN, this is not necessarily a performance burden.


Upgrading from SQL Server 2012 SP1 to SQL Server 2019 I'm upgrading an internal database at work, and we are creating a new VM to host it.

I can't see an option to generate a script to export the SQL Server 2012 SP1 database for server version SQL Server 2019. Would it be better for me to:

Follow the upgrade path for SQL Server 2012 to SQL Server 2019 on the current database server, and export the database to the new server when the upgrade is complete.

or

Export the database from SQL Server 2012 as is, and script it for the highest server version available (2017).

If I installed SQL Server 2012 SP4, would that give me an option to Script for Server Version SQL Server 2019?

Thanks for your help in advance.

This handy page from Microsoft should be very useful for you as it explains in detail how to carry out a migration and the tools and methods available:
Supported Version and Edition Upgrades for SQL Server 2019

Use the Data Migration Assistant (DMA) to scan your databases to make sure they can be migrated to the 2019 server. It will highlight any potential blocking issues such as deprecated features that you may need to resolve before you can migrate.

It looks like you will still need to upgrade your SQL Server 2012 to SP4 to be fully supported though.


Why is SQL Profiler showing empty for database name and database id? I have some code that uses Entity Framework to call a stored procedure in SQL Server. If I run SQL Profiler and filter by the database it is calling, I can see that the profiler correctly shows the call.

However, the database name and database id column are always empty. Anyone know why it is not showing this information?

I'm using this version:

  Microsoft SQL Server 2008 R2 (SP1) - 10.50.2500.0 (X64) Jun 17 2011
  00:54:03 Copyright (c) Microsoft Corporation Standard Edition (64-bit)
  on Windows NT 6.1  (Build 7601: Service Pack 1)

Confirm the trace is actually gathering those columns.  The following query will show the events and columns being captured for each trace (except the default system trace):

SELECT TraceID = t.id
    , TracePath = t.path
    , EventName = te.name
    , ColumnName = tc.name
FROM sys.traces t
    CROSS APPLY sys.fn_trace_geteventinfo(t.id) ei
    LEFT JOIN sys.trace_events te ON ei.eventid = te.trace_event_id
    LEFT JOIN sys.trace_columns tc ON ei.columnid = tc.trace_column_id
WHERE t.id > 1 /* exlude the system trace */
ORDER BY t.id
    , ei.columnid;

The results look something like this (depending on the events and columns you've defined for your trace):

╔═════════╦═════════════════╦═════════════╦═════════════════╦═════════╦══════════╗
║ TraceID ║    TracePath    ║  EventName  ║   ColumnName    ║ eventid ║ columnid ║
╠═════════╬═════════════════╬═════════════╬═════════════════╬═════════╬══════════╣
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ ApplicationName ║      78 ║       10 ║
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ SPID            ║      78 ║       12 ║
║       2 ║ C:\temp\trc.trc ║ CursorClose ║ EventSequence   ║      78 ║       51 ║
╚═════════╩═════════════════╩═════════════╩═════════════════╩═════════╩══════════╝


How accurate is the sys.partition.rows column? The system view sys.partitions has a column "rows" that is the total number of rows in a given partition.  For a table that is not partitioned (or only has one partition depending on how you look at it) this column gives the number of rows in the table.

I'm curious how accurate this column is and if I can use it instead of a SELECT COUNT(1) FROM TableName.  I've done some experiments where create a table and add a few thousand rows, delete a few hundred, add a few thousand more etc and the count has always been dead on.  However I have one table with about 700 mil rows and several indexes.  The row in sys.partitions for the clustered index is again dead on, however the other indexes show some slight variations (+-20k).

Does anyone know how this row is calculated and if it is as accurate as it appears?

Books Online states that the rows field "indicates the approximate number of rows in this partition."  I would therefore expect it to be close, but not 100% accurate, 100% of the time.
Michael Zilberstein reports an example of sys.partitions being wildly incorrect in For want of a nail. Not saying it is a common occurrence, but it is possible.
sys.dm_db_index_physical_stats contains a record_count field that appears to be more accurate, although be aware running the DMV may result in a REDO blocking issue if you run it on an instance hosting an AlwaysOn Readable Secondary Replica.
The explanation for the record_count field shows the following info:

Total number of records.
For an index, total number of records applies to the current level of the b-tree in the IN_ROW_DATA allocation unit.
For a heap, the total number of records in the IN_ROW_DATA allocation unit.
For a heap, the number of records returned from this function might not match the number of rows that are returned by running a SELECT COUNT(*) against the heap. This is because a row may contain multiple records. For example, under some update situations, a single heap row may have a forwarding record and a forwarded record as a result of the update operation. Also, most large LOB rows are split into multiple records in LOB_DATA storage.
For LOB_DATA or ROW_OVERFLOW_DATA allocation units, the total number of records in the complete allocation unit.

See also Martin Smith's answer to a similar question on Stack Overflow.


Issue with SQL Server 2008 replication I'm doing merge replication between two SQL Server 2008 machine.But,when I attempt to do any modification on system,I got the following error.

The Merge Agent failed because the schema of the article
at the Publisher does not match the schema of the article at the Subscriber.
This can occur when there are pending DDL changes waiting
to be applied at the   Subscriber.
Restart the Merge Agent to apply the DDL changes and
synchronize the subscription.
(Source: MSSQL_REPL, Error number: MSSQL_REPL-2147199398)

Filters

The simple answer is to use VIEWS for all your replication filters. That way, you can easily modify your filters without having to drop and re-subscribe all subscribers.

  That is golden rule no. 1

DDL

If you modify any tables that are used in merge replication, you have to allow the DDL to apply down to the subscribers.
If you are worried about some tables replicating down at different times, maybe because they are in a different publication, and perhaps the FK will fail during replication inserts, then simply edit the FK's and add 'NOT FOR REPLICATION'.

  That is my golden rule no. 2

If I can impress anything on you, please use views for your filters. They are lifesavers. We have over 100 subscribers to our databases, and they are the best thing since sliced bread. We can easily modify a VIEW and voila, all done, with no pain at all.


Several mongod instances that do automatic failover without duplication of data? Because we don't have much storage space and we already have a RAID system for data availability, we don't want to use mongodb's replication set with duplication of data.

I just want to know if mongodb's replication allows any automatic failover of mongod instances without duplication of data. I just need the mongodb service to be available, not the data.

I read the replication section on the mongodb site but i did'nt find any mention about my use case. What I want exactly is to have 2 machines running a mongod instance on each and doing automatic failover between them. They are connected to servers which store data (no additional duplication of data as we have already our RAID system). If the automatic failover is not possible in this case, we may need to do it manually.

Any experiences, suggestions or related links are welcome. Thanks in advance.

Your setup is plainly wrong.

First, what sense does it make to have automatic failover when your storage system or the connection to it creates a single point of failure? And if you have a storage system which eliminates every single point of failure ( redundancy in power, network interfaces plus network infrastructure, RAID controllers, main boards and according RAM ), this would be much more expensive than setting up two simple boxes (plus a virtualized arbiter) in a replica set. And you still would have only the same advantage as the much simpler and easy solution.

Next, the backup issue. Granted, SAN snapshots are propably the easiest way to create backups and they are pretty fast. However, there will be an interruption in the service, however short it may be, when doing a snapshot on one instance of data only.

Third, a situation dreaded in all high availability scenarios: the split brain situation. How should you deal with that when both data bearing nodes would try to access the same data set? Since one would hold the lock on the data set but is demoted to secondary by an election and the elected primary can not get hold of the data set as the other instances is holding the lock, the newly elected primary would step down. Let me note that this scenario could only very theoretically take place if you started a second node by means of HALinux or something with a very strange setup. You would start to have to deal with something like STONITH, which comes with it's very own and sometimes rather delicate problems, further increasing the most expensive ressource you have: continuous administrative costs. A classical HA setup requires 24/7 monitoring and response times measured rather in seconds than in several minutes. MongoDBs failover capabilities are - when set up correctly - reliable and usually works without the need of manual intervention unless in rare edge cases. Edge cases of a failover, that is. So the edge cases of anyway rare edge cases.

Having that said: no, what you want is not possible with MongoDB out of the box. The reason for that is that there are lock files created in the data directory, preventing the second server from accessing the same data. So what you would have to do is to set some HALinux, remove that lock file before firing up the second server and then fire up the IP address. This comes with some other serious drawbacks from the MongoDB side, however. The data may be inconsistent, as the first server might not be able to flush it's data to the data files. Thinking of it twice, it might well be that the lock is only checked during startup, which might actually lead to the two servers flushing their data on the same data set, resulting in data FUBAR.

TL;DR: Use MongoDB (or any other tool, for that matter) within it's intended environment and for it's intended purpose. Don't do it as you originally planned. It's neither worth the effort nor does it give any advantage except saving - compared to the costs you create by the idea like how you want to do it - relatively cheap disk space. Don't take chances with your production data.


Two tables with same field QUESTION: I have QUOTATION table and SOA table
quotation - table is for pending and not approve quotation.
SOA- table for approve quotation.

i will just transfer the data from quotation table to soa once it is approve, the difference is in soa it has soa_account_number.

the question is, is it advisable to do this process or i just use 1 table.

QUESTION: I have two table with same fields

motor_car_quotation
motor_car_soa

the motor_car_quotation table is the storage of motor car that is not yet approve.
the motor_car_soa is the table for approve quotation, i will just transfer all the fields from the quotation to motor_car_soa table once it is approve.

the question is, is it advisable to do this process or i just use 1 table.

This is definitely a case where you should use one table IMHO.

The entities involved are virtually identical only differing by 1 important field - that makes them very good candidates for a single table.

Have the SOA_account_number in the table and set it to NULL if the quote hasn't been approved and have a value set when the approval process has been successfully completed.

This simplifies your schema and all you have to do to distinguish between pending and approved quotes is to use a WHERE clause - i.e.

WHERE SOA_account_number IS (or IS NOT) NULL

You haven't given us the full table structure (SHOW CREATE TABLE My_Table\G), but I would suggest having a couple of DATE fields (or possibly DATETIME) - something like quote_offered_date and quote_accepted_date. Just a couple of thoughts.


Appropriate use of mysql Optimize Table I want to come up with some best practice for maintaining our MySQL database, versions 5.5/6 and using InnoDB.

I came across this article which is basically saying that Optimize table:

not going to show much improvement in case your queries don't use the PK.
other indexes on the table are built in a pseudo-random order and most likely would not benefit from the Optimize table.
could actually make Updates slower due to the fact that now each change have higher probability of resulting in a page split.

My questions are:

Are the 3 points above always true? Partially? Not at all?
When is it a good shot to try and Optimize a table?
In which cases Optimizing a table would not benefit or even make some uses of the table worse?
Is there a way to Optimize the indexes of a table other than its PK?

Baron Schwartz, the author of that post, is one of the coauthors of High Performance MySQL, 3rd Edition, one of the best books out there regarding MySQL performance. While the argument of authority is not always a good one, I would like to remark that probably he knows what he is saying.

While everything he says is correct -in my humble opinion-, you must understand the actual underlying argument: defragmenting an InnoDB table is in many cases useless (for performance), and many people recommending to do it frequently are wrong.

Fragmentation and page-splitting is a delicate topic, which people like Jeremy Cole http://blog.jcole.us/2013/04/09/innodb-bugs-found-during-research-on-innodb-data-storage/ and Facebook engineers have mentioned a lot (specially, regarding to compression): https://www.facebook.com/note.php?note_id=10150348315455933 and its implications on performance.

Many times, your performance is load-dependent- Do you insert using an auto-increment? Do you insert and delete many times in the middle of your table? Can you afford the extra disk space if your table is very dynamic?

There are some good practices that I can recommend you (which is probably what you want):

Defragment only if you have done a batch DELETE of lots of records (and you do not intend to insert them back). In other cases, it may not be necessary. If you want to know if there is a huge difference between logical data and file size, compare the .idb file with the data + index size from show table status.
Speedup insertion by inserting always in PRIMARY KEY order, so you do not force unnecessary page splits.
Use partitioning to isolate changes that may alter the internal structure of the table.
There is no way to "optimize the secondary indexes", but I would never find such a thing necessary. The change buffer makes sure that changes/rebalances to indexes are done asyncronously without huge performance problems. A BTREE should always be balanced, so assuming your change buffer is not full and the purge thread deletes old row records promptly, your performance should be ok. One way I can think of "optimizing secondary indexes", as you called it, is DROPing the index and recreating it (asuming you are using the InnoDB Plugin or MySQL 5.5+), but I see absolutely no reason to do that.

Of course, if you really want to dig into this topic, create some tables, defragment them and check if you actually have some gain afterwards. In general, tablespace handling and statistics gathering is something relatively automatic on InnoDB.


Can someone explain why select with nolock will query a potion of updated data? I was reading the answer from here (from stackoverflow, I think should ask in here)

  NOLOCK means placing no locks at all.

  Your query may returns portions of data as of before UPDATE and
  portions as of after UPDATE in a single query.

I get that nolock will not place lock to the table, so other people can query the same time.

From the answer and example it show, it fetch data while the data is being updating.

Why does that happen?

I am assuming for normal select it will try place lock on table, so when update statement is executed, it place a lock on the row or page.  Then when I try to run select statement, it cannot put the lock until the update statement lock is released.

But in this case because the select statement doesn't try to put lock on the table, so it can run without waiting for the update statement release the lock?

It is not quite true that NOLOCK means placing no locks at all. Queries under this hint will still take Sch-S locks and (possibly HOBT locks).

Under read committed isolation level SQL Server will (usually) take row level S locks and release them as soon as the data is read. These are incompatible with the X locks held on uncommited updates and thus prevent dirty reads.

In the example in the linked answer the SELECT query is not blocked when it encounters a modified row so reading partial updates is quite likely.

It can also happen at default read committed isolation level too though that a SELECT reads some rows with the "before" value and others with the "after" value. It is just needed to engineer a situation where

Select query reads value of row R1 and releases its S lock
Update query updates R2 and takes an X lock
Select query tries to read R2 and is blocked.
Update query updates R1 and takes an X lock.
Update transaction commits thus releasing its locks and allowing the Select to read R2

This type of situation might arise for example if the SELECT and UPDATE are using different indexes to locate the rows of interest.

Example

CREATE TABLE T
(
X INT IDENTITY PRIMARY KEY,
Y AS -X UNIQUE,
Name varchar(10),
Filler char(4000) DEFAULT 'X'
)

INSERT INTO T (Name)
SELECT TOP 2500 'A'
FROM master..spt_values

Now in one query window run

DECLARE @Sum int

SELECT 'SET @@ROWCOUNT' WHERE 1=0

WHILE (@@ROWCOUNT = 0)
SELECT @Sum = SUM(LEN(Name))
FROM T
WHERE Y IN (-1, -2500)
HAVING SUM(LEN(Name)) = 3

This will run in an infinite loop. In another run

UPDATE T
SET Name=CASE WHEN Name = 'A' THEN 'AA' ELSE 'A' END

This will likely stop the loop in the other query (try again if not) meaning that it must have read either A,AA or AA,A


How does trigger file in recovery.conf work? Can someone explain how the trigger_file in recovery.conf works?

As far as I know the standby server will become the primary server when it detects the trigger_file. But how will the trigger_file be created when the primary server fails?

Any reliable failover scenario requires a third party, sometimes called a "witness", "arbitrator", "cluster monitor", "cluster manager" etc. to detect failure of one member and initiate a failover. This process ideally resides on a network separate from the one that connects cluster members to each other. It "pings" node(s) that are deemed to be masters or active nodes and triggers failover when they don't respond within the timeout threshold. In your case this monitor will create trigger_file to initiate the failover.


Joining three tables I have a question

Find the titles of all movies not reviewed by Chris Jackson

Where the relations are 3
movie(mID, title, year, director),
reviewer(rID, name),
rating(rID, mID, stars, ratingDate)

I have tried this query so far. Any idea of how to improve it or what am I doing wrong?

SELECT DISTINCT M.TITLE
FROM MOVIE AS M
JOIN RATING AS R ON R.mID = M.mID
WHERE R.rID NOT IN (SELECT Rating.rID FROM Rating JOIN Reviewer ON Reviewer.rID = Rating.rID WHERE Reviewer.name = 'Chris Jackson');

Your query is too complex; the join between movie and rating is not necessary because the movie ID is already directly available from the subquery.

Anyway, using subqueries instead of joins usually is easer:

First, these are all reviews by anyone named Chris Jackson:

SELECT *
FROM rating
WHERE rID IN (SELECT rID
              FROM reviewer
              WHERE name = 'Chris Jackson')

(If you know that there is only one Chris Jackson, use WHERE rID = (SELECT ... instead of WHERE rID IN (SELECT ....)

Now you want all movies that do not have such a review:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT mID
                  FROM rating
                  WHERE rID IN (SELECT rID
                                FROM reviewer
                                WHERE name = 'Chris Jackson'))

It would be possible to combine the two inner subqueries with a join:

SELECT title
FROM movie
WHERE mID NOT IN (SELECT rating.mID
                  FROM rating
                  JOIN reviewer USING (rID)
                  WHERE reviewer.name = 'Chris Jackson')


Execute Remote Powershell script from SQL Agent A test SQL Server job hangs when it executes a Powershell script.  It does work using my login and a Powershell window, but not using the Agent account and a job.  The SQL Agent user has read/write/execute permissions to the directory with the powershell script.

Example:

Run as: SQL Server Agent Service Account

powershell.exe -file \\Server02\test2\Test.ps1

The agent account has been given access also.

This test file currently does something very basic: displays a string.

When it runs, it does not show an error message, because it just does not respond. Do you have any idea about what is happening, or which security settings are missing?

JNK was correct in the comment above.  If I ran it in the command shell, there was a prompt saying

  Security Warning Run only scripts that you trust

In order to avoid this message, I added -ExecutionPolicy Bypass to the command

Example:
powershell.exe -ExecutionPolicy Bypass -file \Server02\test2\Test.ps1

The job is now able to complete.


Why do I have to run mongod as root? When I run "/usr/bin/mongod --config /var/data/rs1/rs1db0v3.conf". It failes. The error is:

warning: bind_ip of 0.0.0.0 is unnecessary; listens on all ips by default
about to fork child process, waiting until server is ready for connections.
forked process: 16970
ERROR: child process failed, exited with error number 1

The permission of the relative files and folds are following:

drwxr-xr-x 6 root    root     115 Mar 16 14:47 .
drwxr-xr-x 5 root    root      43 Jan 20 12:12 ..
drwxr-xr-x 6 mongodb mongodb 4096 Mar 16 15:05 db
drwxr-xr-x 2 root    root      28 Jan 20 10:04 keys
drwxr-xr-x 2 mongodb mongodb   23 Jan 20 10:32 log
-rwxr-xr-x 1 mongodb mongodb  593 Mar 16 14:47 rs1db0v3.conf
-rwxr-xr-x 1 mongodb mongodb 21303832 Mar  3 08:03 /usr/bin/mongod

I found similar questions in this website, but no answers can help me...
-----SOLVED-----
I have no idea how to run mongod as mongodb, so I changed the ownership to user1, and then run mongod as user1. It works.

drwxr-xr-x 6 root    root     115 Mar 16 14:47 .
drwxr-xr-x 5 root    root      43 Jan 20 12:12 ..
drwxr-xr-x 6 user1  mongodb 4096 Mar 16 15:05 db
drwxr-xr-x 2 root    root      28 Jan 20 10:04 keys
drwxr-xr-x 2 user1  mongodb   23 Jan 20 10:32 log
-rwxr-xr-x 1 user1  mongodb  593 Mar 16 14:47 rs1db0v3.conf
-rwxr-xr-x 1 user1  mongodb 21303832 Mar  3 08:03 /usr/bin/mongod

First of all when you run mongod as a normal user you need to make sure that the particular user has the following permissions:

Access to network interface ( open socket ) in order to be able to listen to connections
Access to the database folder. The default is accessible only by root or mongod user (/data/db)
Access to logging folder /var/log/mongod

All of the above are automatically set and granted for the user mongodb (mongod in earlier versions). If you take a look at /etc/init.d/mongodb you will see that the service will be stared under the user mongodb DAEMONUSER=${DAEMONUSER:-mongodb} and not as root.


Will Partitions and Indexes on the same table help in performace of Inserts and Selects? I have a table containing the list of visitors and this table has the following information.

Visitor Browser Information
Visitor Location Information
Visitor Time Information
No of Visits

I have a second table that maintains the history of each visits, which means I if the same visitor visits the site, I insert into the second table and update the no. of visits on the first table.

The kind of reports that I have to generate for this table are

Count of Visitors/day or days (Search Between days)
Count of Visitors/month
Count of Visitors/year
Count of Visitors/browser or grouped by browsers

On an average there are about 20000 inserts to the second table and about 15000 inserts to the first table, meaning 5000 were updates to the first table (5000 repeat visits).

I need to decide between partitioning the tables by month and sub-partitioning by days for the reports 1,2,3 and index the browser related columns for report 4.

There will be more reports in the future not sure on what clauses.

Does partitioning/sub-partitioning along with indexing help in the performance of inserts and selects?

Should I perform partitioning on both the tables?

I am currently using MySQL 5.5 + InnoDB

May I suggest the following strategy:

Since there is a limit of 1024 partitions and sub partitions in single table have a Current
table (1-2 months of data) partitioned by day and browser. And another History table for archive partitioned by month/day.

If you need to run reports on browser split, then have several tables with day/browser key.

Although there will be a number of tables (you may keep e.g. a table per quarter and then have second line of archiving to a table with month/browser key) it will be manageable, because partitions are quickly switched from one table to another.

Non-clustered indexes always help your reports, so having an index on a often used column
is a good idea whatever partitioning strategy is implemented.

Depending on your reporting requirements you may also consider creating a summary table, which will have data already pre-aggregated for your reports. It can be populated overnight and speed up your reports dramatically (indexes is a must on this table too). You can even populate this summary table several times during the day without locking the table by using READ UNCOMMITTED - it is unlikely that you need precision to every visitor.


Minimum configurations for mongodb replica set though a java program I'm new to morphia. I'm using morphia and mongo-java-driver.jar to talk with replicaset(I need for cluster also) through a java program. I'm new to morphia.
Wrote a sample program code is below
    public static void createDBConnection() {
    try {

        List<ServerAddress> addrs = new ArrayList<ServerAddress>();
         addrs.add( new ServerAddress( "192.168.1.80" , 27017 ) );
         addrs.add( new ServerAddress( "192.168.1.81" , 27017 ) );
         addrs.add( new ServerAddress( "192.168.1.82" , 27017 ) );

        MorphiaObject.mongo = new MongoClient(addrs);

        ReplicaSetStatus status = MorphiaObject.mongo.getReplicaSetStatus();
        List<String> dbs = MorphiaObject.mongo.getDatabaseNames();

        MongoOptions mongOptions = MorphiaObject.mongo.getMongoOptions();
        MorphiaObject.mongo.setWriteConcern(WriteConcern.REPLICAS_SAFE);
        MorphiaObject.mongo.setReadPreference(ReadPreference.secondaryPreferred());
        System.out.println("Read prefrence"+ MorphiaObject.mongo.getReadPreference());
    } catch (UnknownHostException e) {
        e.printStackTrace();

    }
}

If I want to write data safely in all nodes what are steps need to do.
What are the minimum configurations that I need to set for replica?
What are validations need to do like replica set is down or alive, is readable, how can we manage if app or program can't find master?
Thanks in advance.

Misconceptions

  "It is the best way to ensure data integrity to have MongoDB write to all nodes."

This is entirely wrong. Let's assume you have three members in a replica set. Well, you want the data written to all nodes, so you set your write concern to 3. Now, one of your nodes fails. And while your replica set would work perfectly for reads and writes, with a write concern set to 3, your writes will start to fail. Now you are loosing data, since you can not write any more until all members of your replica set are up, running and resynced.

The write concern REPLICAS_SAFE usually is enough. However, if you have more than 3 data bearing nodes, you absolutely need (relatively ) fast consistency across all nodes and give a damn about write performance, you might want to create a custom write concern this way

private static final int NODES = 5;
private static final int LOOONG_TIMEOUT = 5000;
int paranoidReplicas = Math.floor((NODES/2)+1);

WriteConcern custom = new WriteConcern(paranoidReplicas, LOOONG_TIMEOUT, true);

  "I have to manage the status of the cluster on the application side."

Wrong. All drivers provided by MongoDB are replica set aware. So if your primary fails, your driver will figure out which the new primary is. Transparently.

However, what you could do is to catch according exceptions, check wether there is a primary at the moment (you need the low level driver to do so) and if there isn't one, retry the write after a couple of seconds (the time it takes for electing a new primary - 4 seconds is what I tend to use).

  With a high write concern and a read preference, I can do load balancing without the need of a sharded cluster.

Ok, this is a bit of a stretch from my side, but it smells like that's being the case.

Can you do it? Well, with reservations. First of all, you have to understand that a write concern > 1 does not imply that the change is applied on all nodes simultaneously. It just guarantees that the driver will not return until the change is written to the number of specified nodes. However, until the driver returns, the change may be applied to one node, which will happily send the changed data when a query comes in, while it isn't applied to another node, yet, which will send the unchanged data. To make a long story short: do whatever you want, but consistency among the members of a replica set remains eventual. Please see the replica set introduction on read operations for details.

Another problem is that a higher write concern affects your applications performance. When just using the primary for writes, with the data journaled, your modification will be executed rather fast. With a higher write concern, your modifications will take proportionally longer.

A problem related to the last one is that connections will be returned to your connection pool at a slower pace (and might well block threads of your thread pool, in case you use some sort of servlet engine or application server), allowing fewer concurrent users, although this only applies to loads bordering your hardware's (and configuration's) limits.

Is it a good idea? IMHO, in no way. You will get only a marginal read load balancing with eventual consistency and you pay for it with a rather high impact on your write performance and resource efficiency. If your primary can't handle the load, it is under dimensioned. You either need to scale up or out.

  Asking this question on Stackoverflow will provide me with enough information to run or use a MongoDB cluster properly.

Erm... ...no. You might get a hint, but a short answer can not replace proper training. Or reading the docs thoroughly, for that matter. See above.


Using IF and other conditions in T-SQL (trying to import data from Oracle using SQL Server SSIS) I am using SQL Server 2012 SSIS package to fetch data from Oracle and insert it into SQL Server 2012.

I am doing it for the first time and every thing is fine i.e. all data from a particular table is being inserted into table in SQL Server.

But I want to place a condition i.e. if some record is new, only then add it in SQL Server. For e.g. there is an ID field in Oracle table which I can check in SQL Server table so if that particular ID is already present in SQL Server table then do not insert it.

I think this IF condition cannot be used in regular SQL and I have to use T-SQL which I have never used so how do I write this simple condition?  Suppose following is table structure:

Oracle
Table: OracleTable
Fields: ID, Name

SQL Server
Table: SQLTable
Fields: ID, Name

So it will be something like:

SELECT * FROM OracleTable;
If (Oracle.ID != SQL.ID)
    INSERT INTO SQLTable(rest of the query here)

Or may be there is some option available in SSIS which will allow me to do this without writing query?

assuming you can see the oracle table from your SQL Server

INSERT INTO SQLTable( ID, field2 )
SELECT ID,
       field2
FROM OracleTable o
WHERE NOT EXISTS( SELECT NULL FROM SQLTable s WHERE s.ID = o.ID )

this is just the simple SQL for inserts. I have not used SSIS so YMMV


Regular expression in a SQL case statement I have records formatted as such:

CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

What I am trying to accomplish with a REGEX case statement in Oracle SQL is to have a field in a view that lists the opening alphas in one column, the following digits in a second column, and the two concatenated in a third column.  The beginning of the record will have two or three alphas followed possibly by a blank space, a /, a -, or a digit.  How can I separate these out?  I've tried:

  case
  when subj_no like '%' then
     regexp_substr(subj_no, '^[A-Z]+') subj_no
  end subj_no,

The end result for the given data should be:

COL 1           COL 2    COL 3

CBS405 505      CBS      405 505
BR404 504 604   BR       404 504 604
AP490           AP       490
APR501          APR      501
MP300           MP       300
ACX501          ACX      501
ACX400          ACX      400

How can I use a regular expression in a case statement to arrive at that output?

May I suggest doing it in a couple of passes:

SQL> select * from records;

SUBJ_NO
--------------------------------------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

SQL> l
  1  with strip as(
  2  select translate(subj_no, '/-', '  ') subj_no
  3    from records
  4  )
  5  , reg as (
  6  select regexp_substr(subj_no,'^[A-Z]{2,3}') col_2
  7   , trim(regexp_substr(subj_no,'[0-9 ]+')) col_3
  8    from strip
  9  )
 10  select col_2||col_3 col_1
 11   , col_2
 12   , col_3
 13*   from reg
SQL> /

COL_1                COL_2      COL_3
-------------------- ---------- --------------------
CBS405 505           CBS        405 505
BR404 504 604        BR         404 504 604
AP490                AP         490
APR501               APR        501
MP300                MP         300
ACX501               ACX        501
ACX400               ACX        400

7 rows selected.

Strip replaces the slashes and dashes with spaces so they don't get in the way later.

Reg rips off your regexp and adds another one that finds the first group of digits and spaces, trimmed for neatness.

Last one takes the results and formats them as you requested.

Edit:  If I've understood your comment below then you have all those values in a single row.  Slice it up with a pipelined function.

SQL> select * from records;

SUBJ_NO
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

1 row selected.

SQL> create or replace function get_records return tt_subj_no pipelined as
  lv_recs  records.subj_no%TYPE;
  lv_subj  varchar2(100);
  lv_start number := 0;
  lv_end   number := 10;
begin
  select *
    into lv_recs
    from records;

  lv_end := instr(lv_recs, chr(10));
  while lv_end > 0
  loop
    pipe row(substr(lv_recs, 1, lv_end));
    lv_recs := substr(lv_recs, lv_end + 1);
    lv_end := instr(lv_recs, chr(10));
  end loop;
  pipe row(lv_recs);
end;
/

Function created.

SQL> select * from table(get_records);

COLUMN_VALUE
----------------------------------------------------
CBS405/505 Fourth Week(Wtr Qtr)
BR 404-504-604 Fifth Week(Fall Qtr)
AP490-QB-09 Weekly Sessions(Spr Qtr)
APR501 Fourth Week(Wtr Qtr)
MP 300 Fourth Week(Wtr Qtr)
ACX/501 Fifth Week(Sum Qtr)
ACX 400 Ninth Week(Mas Term)

7 rows selected.

Stick that in the original query and you should be good to go.


sp_Blitzcache doesn't return as many results as sys.dm_exec_query_stats for a given query I am trying to find plans for a particular set of queries (those that update MyTable) using sp_blitzcache:

EXEC sp_Blitzcache @SlowlySearchPlansFor = 'UPDATE [My Table]'

this returns 3 results, however, when I run the following query

SELECT  d.name,
        t.text AS TSQL_Text,
        s.creation_time,
        s.execution_count,
        s.total_worker_time AS total_cpu_time,
        s.total_elapsed_time,
        s.total_logical_reads,
        s.total_physical_reads,
        p.query_plan
FROM    sys.dm_exec_query_stats s
        CROSS APPLY sys.dm_exec_sql_text(s.plan_handle) t
        CROSS APPLY sys.dm_exec_query_plan(s.plan_handle) p
        JOIN sys.databases d
            ON t.dbid = d.database_id
WHERE   t.text LIKE '%UPDATE \[My Table\]%' ESCAPE '\'

it returns far more.

Is there some sort of limit in sp_blitzcache around what queries are found? For example, are low cost queries ignored?

Yes, there's a known issue with that parameter not returning all results due to spaces, and I wouldn't be surprised if that's also affecting you:

https://github.com/BrentOzarULTD/SQL-Server-First-Responder-Kit/issues/2202

However, let's zoom out a little: you can't rely on a text search of the plan cache to find everything updating a table. You could have multiple spaces, tabs, even line returns or comments between the word UPDATE and the start of your table name. The table name might also be prefixed with the database name or schema name.

As a result, I wouldn't recommend this technique to find queries that update a particular table.


Error deleting SQL Server local database I'm doing a project in Visual Studio 2012 and it involves working with the given database. As I add the database.mdf to the project (asp.data folder) I got a connection error, but nothing out of the ordinary. To "repair" the database we are supposed to enter this into cmd

cd "C:\Program Files\Microsoft SQL Server\110\Tools\Binn"
SqlLocalDB.exe delete v11.0

At school everything worked fine, but at home I got this error in cmd

  Delete of LocalDB instance "v11.0" failed because of the following error:
  Requested operation on LocalDB instance cannot be performed because specified instance is currently in use. Stop the instance and try again.

What do I have to close/do?

You simply need to make sure you have stopped the database before deleting it.

You can check the current state by running this:

SqlLocalDb info "MyInstance"

and stop it by running :

SqlLocalDb stop   "MyInstance"

Once stopped you should be able to delete the instance.

This is a nice guide on installing and deleting express databases.


Why can't SQL Server optimizer use foreign key I have the following tables(See below for complete reproducible SQL):

ToBeTransferredTemp (ID varchar(100),type varchar(100) NOT NULL, other...)
CODES(Key1,Key2,Key3, IDSTRING varchar(100), other...)

I have the following query:

SELECT *
FROM
ToBeTransferredTemp a
INNER JOIN codes c on a.type = c.idstring

There is an index on ToBeTransferredTemp.type and unique index on CODES.idstring.
There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

You would think SQL Server would gather from this, that the join will produce the same number of rows as is present in ToBeTransferredTemp. But it doesn't.
There are 8500 rows in ToBeTransferredTemp, and the estimated number of rows after the join is 450, and "actual number of rows" is, not surprisingly, 8500 rows.

Any idea why SQL Server misses so terribly? I've checked that the foreign key constraint is trusted by SQL Server.

Could it be that SQL Server 2017 don't take foreign key and unique constraints into account when estimating cardinality of joins?

I know I could fix this easily by just doing a LEFT OUTER JOIN, but it feels so clumsy. I'm really interested to know why SQL Server can't figure this one out.

EDIT: queryplan:
https://www.brentozar.com/pastetheplan/?id=ByL54PtNB

EDIT: SQL needed to reproduce. Note that you might need to re-run the query a few times. Sometimes the values form statistics that make it seem to work

CREATE TABLE dbo.TEST_CODES
(
    key1 varchar(60) NOT NULL,
    key2 varchar(60) NOT NULL,
    key3 datetime NOT NULL,
    Filler4 varchar(100),
    IDSTRING varchar(100)
);
ALTER TABLE [dbo].[TEST_CODES] ADD  CONSTRAINT [PK_CODES_TEST] PRIMARY KEY CLUSTERED
(
    key1 ASC,
    key2 ASC,
    key3 ASC
)

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.TEST_Codes (IDSTRING);

CREATE TABLE dbo.TEST_ToBeTransferredTemp
(
    ID varchar(100) NOT NULL,
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp_TEST PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type])
        REFERENCES dbo.TEST_CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.TEST_ToBeTransferredTemp ([type]);

INSERT INTO dbo.TEST_CODES
SELECT TOP (18139)
    CAST(NEWID() AS varchar(60)),
    CAST(NEWID() AS varchar(60)),
    GETDATE(),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100))
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.TEST_ToBeTransferredTemp
SELECT TOP (8002)
    CAST(NEWID() AS varchar(100)),
    (SELECT TOP 1 IDSTRING FROM TEST_CODES order by newid()),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
     FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

SELECT *
FROM
TEST_ToBeTransferredTemp a
inner join TEST_codes c on a.type = c.idstring

I can't reproduce your problem, which means there's likely something missing in your description (or I've missed something in your description!).  If you want more detailed help with this issue, please suggest changes to this repro code that allow it to more closely match your situation.

Here's an attempt and recreating the tables and data based on your description:

  There is an index on ToBeTransferredTemp.type
  and unique index on CODES.idstring.
  There is a foreign key constraint on ToBeTransferredTemp.type -> codes.idstring

In addition to those three pieces of information, I noticed that there is a clustered index on each of the two tables.

USE [master];
GO
DROP DATABASE IF EXISTS [245776];
CREATE DATABASE [245776];
GO

USE [245776];
GO

CREATE TABLE dbo.CODES
(
    IDSTRING varchar(100),
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_CODES PRIMARY KEY (IDSTRING)
);

CREATE UNIQUE INDEX UQ_Codes_IDSTRING ON dbo.Codes (IDSTRING);

CREATE TABLE dbo.ToBeTransferredTemp
(
    ID varchar(100),
    [type] varchar(100) NOT NULL,
    Filler1 varchar(100) NOT NULL,
    Filler2 varchar(50) NOT NULL,
    Filler3 int NULL,

    CONSTRAINT PK_ToBeTransferredTemp PRIMARY KEY (ID),
    CONSTRAINT FK_ToBeTransferredTemp_type FOREIGN KEY ([type])
        REFERENCES dbo.CODES (IDSTRING)
);

CREATE NONCLUSTERED INDEX IX_type ON dbo.ToBeTransferredTemp ([type]);

Here are the two tables, with clustered primary keys, foreign key constraint, an NC index, and a unique index.

I have included some filler columns in order for the original key lookup plan shape to be possible.

INSERT INTO dbo.CODES
SELECT TOP (8500)
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    v1.number
FROM master.dbo.spt_values v1
    CROSS JOIN master.dbo.spt_values v2;

INSERT INTO dbo.ToBeTransferredTemp
SELECT
    CAST(NEWID() AS varchar(100)),
    c.IDSTRING,
    CAST(NEWID() AS varchar(100)),
    CAST(NEWID() AS varchar(50)),
    NULL
FROM dbo.CODES c;

This loads exactly 8500 rows into each table.

The query as written in the OP produces a merge join plan for me:

SELECT *
FROM ToBeTransferredTemp a
    INNER JOIN codes c
        ON a.[type] = c.idstring;

In order to get the plan shape from the OP, I have to hint the join type (INNER LOOP JOIN) and hint that the CODES table should be accessed by the unique index.

SELECT *
FROM ToBeTransferredTemp a
    INNER LOOP JOIN codes c WITH (INDEX (UQ_Codes_IDSTRING))
        ON a.[type] = c.idstring;

This appears to be the right plan shape.  However, the estimates are exactly right in this plan.


Commands creating format files to import txt file into sql server I'm trying to create a format file using bcp utility following the instructions on this page:

Using a format file to bulk import data

https://technet.microsoft.com/en-us/library/ms178129(v=sql.105).aspx

However, this page only discusses how to create format files for tables in the databases. My target is creating a format file for a txt file on my disk and then using the format file to import the raw data in the text file to the database. I tried the following command:

bcp C:\data.txt format nul -c -f txt1.fmt -T

and get the following error:

A valid table name is required for in, out, or format options.

I'm using sql server 2016 and my system is windows 10 64 bit.

Can anyone help me with this, thanks in advance for your help!

Jason

You need to put in the full path for your format file.  Use the table of the database where you will be importing the data to create your format file:

bcp Database.dbo.table format nul -f c:\txt1.fmt -c -T

Once the format file is created, then you can import the data from the txt file into your database using the format file:

bcp Database.dbo.table in c:\data.txt -f c:\txt1.fmt -T

https://msdn.microsoft.com/en-us/library/ms162802.aspx


How to improve restoring the PostgreSQL database? I'd like to restore the PostgreSQL database from a dump. The below command works fine for me:

CALL "%PGBIN%\pg_restore" -c -d NetCatalog %DUMPFILE%

How can I add a few lines to my .bat file to rename the existing NetCatalog to NetCatalog_Old and create a new database NetCatalog before calling the above line?

PS. Is there an argument that allows me to log the restoring process?

Thanks!

Something like:

psql -U postgres -d postgres -c "DROP DATABASE netcatalog_old"
psql -U postgres -d postgres -c "ALTER DATABASE netcatalog RENAME TO netcatalog_old"

You can redirect the info output of the restore like

pg_restore ... > my_log_file.txt

If you add the "-v" flag you can get more details of what's happening, but probably too much.


PostgreSQL naming conventions Maybe double post but I could not find any answer here.

I saw couple of posts how to name my tables and I understood that (semi-officially) it should contain lower cases, should be underscored and singular (f.e. my_table)

My question arose when I had to do the following.
I have an app where I have user, platform and user_info, where platform is what the user is working on (f.e. MAC, PC, LINUX). If I need to create junction table between user and platform, how do I name it in order the next person browsing the schema to understand that user and platform have a junction table named user_platform, but to not be confused when he sees the table user_info?

BR

In case the reasoning behind lower-case names wasn't clear, here's why: Postgres automatically and invisibly folds names down. So, all of these names are the same:

user_platform

USER_PLATFORM

UsEr_PlAtFoRm

They're all user_platform internally. Unless you put them in double-quotes:

"UserPlatform" is "UserPlatform". But to use this field or table, you would then need to put the name in double quotes everywhere you use it. It's a total pain. So, it's pretty standard to see names like

user_platform

With that said, as long as you don't use the double-quotes, you can write the name

User_Platform

if you find it easier to read.

As far as the naming convention goes, yes, pick one, stick to it and document your rules. You get 64 characters to work with (by default), which I haven't found to be an issue, except sometimes for index names.

For linking tables, whatever you like is fine. Linking users and platforms?

user_platform

user_link_platform

whatever you prefer.

As far as the singular vs. plural argument, it's a matter of taste. Some people care. A lot. The gutters run red with the blood of the unrighteous! I say do whatever you like. I'm of the make-table-names-singular school, but mostly because it's a habit that I picked up long ago.

Ah, one thing about the double-quotes...I do use them. We ship Postgres data to a visualization/analytics program called Domo. For that, it's nicer to have column (attribute) names in MixedCase. For that, I create views with "MixedCase" column aliases so that the data exports with a "MixedCase" name. This is a slightly exotic scenario, but maybe you'll run into something similar one day. Normally, I'd change the tables client-side, but the views were the easier solution.

Bonus tip! If you haven't discovered it already, Postgres text fields are case-sensitive. If you don't like that, you can:

Write your queries to deal with the data case-blindly. Every. Single. Query.
Use the standard citext extension. (Case Insensitive Text.)
Wait for PG12 to ship with case-insensitive collations. (Haven't tried it out yet.)

Forgot to mention...Postgres has unusually good support for commenting on system elements, including tables:

https://www.postgresql.org/docs/current/sql-comment.html

Someone has to look for and read your comments, of course, but it's another place that you can provide context. Even if that context is nothing but a link to a wiki you maintain, etc.


generate_series for multiple record types in postgresql. Fill missing with last previous value my question is closely related to the following question asked in this thread generate_series for multiple record types in postgresql. The only difference is, that instead of only inserting missing values, I want to fill the gaps with the last previous value. To illustrate it with the same example. I have two tables.

CREATE TABLE pests(id,name)
AS VALUES
  (1,'Thrip'),
  (2,'Fungus Gnosts');

CREATE TABLE pest_counts(id,pest_id,date,count)
AS VALUES
  (1,1,'2015-01-01'::date,14),
  (2,2,'2015-01-02'::date,5);

The desired output should be nearly identical. However, I want to fill missing values with previous values, if possible. So the second entry for Thrip should be 14, since a previous value exists and the first value of Fungus Gnats should be 0, since no previous value exists. Is it possible to do this using Postgres?

expected results

name         | date       | count
-------------+------------+-------
Thrip        | 2015-01-01 | 14
Thrip        | 2015-01-02 | **14** <- fill with existing previous value.
....
Fungus Gnats | 2015-01-01 | 0
Fungus Gnats | 2015-01-02 | 5
...

Example data with two rows added:

insert into pest_counts(pest_id, date, count) values
    (1, '2015-01-03', 10),
    (2, '2015-01-04', 7);

select * from pest_counts;

 id | pest_id |    date    | count
----+---------+------------+-------
  1 |       1 | 2015-01-01 |    14
  2 |       2 | 2015-01-02 |     5
  3 |       1 | 2015-01-03 |    10
  4 |       2 | 2015-01-04 |     7
(4 rows)

Use count() as a window function to generate groups. Each not null value starts a new group:

select name, day, count, count(count) over (partition by name order by day) as grp
from (
    select id, name, day::date
    from pests
    cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
) d
left join pest_counts on d.id = pest_id and day = date;

     name     |    day     | count | grp
--------------+------------+-------+-----
 fungus gnats | 2015-01-01 |       |   0
 fungus gnats | 2015-01-02 |     5 |   1
 fungus gnats | 2015-01-03 |       |   1
 fungus gnats | 2015-01-04 |     7 |   2
 thrip        | 2015-01-01 |    14 |   1
 thrip        | 2015-01-02 |       |   1
 thrip        | 2015-01-03 |    10 |   2
 thrip        | 2015-01-04 |       |   2
(8 rows)

Now you can use sum() as a window function over partitions by names and generated groups:

select name, day, coalesce(count, sum(count) over (partition by name, grp order by day), 0) as count
from (
    select name, day, count, count(count) over (partition by name order by day) as grp
    from (
        select id, name, day::date
        from pests
        cross join generate_series('2015-01-01'::timestamp, '2015-01-04', '1 day') day
    ) d
    left join pest_counts on d.id = pest_id and day = date
) s;

     name     |    day     | count
--------------+------------+-------
 fungus gnats | 2015-01-01 |     0
 fungus gnats | 2015-01-02 |     5
 fungus gnats | 2015-01-03 |     5
 fungus gnats | 2015-01-04 |     7
 thrip        | 2015-01-01 |    14
 thrip        | 2015-01-02 |    14
 thrip        | 2015-01-03 |    10
 thrip        | 2015-01-04 |    10
(8 rows)


Is it possible to set up ADSI linked server in SQL Server Express? Is there any limitation in SQL Server Express that prevents you from creating a linked server to Active Directory?

(I don't have an AD environment available to test.)

Linked Servers do not seem to be an edition specific feature according to this MSDN article : Linked Servers (Database Engine) SQL 2016

To add a linked server without the SQL Server Management Studio you would use the system stored procedures sp_addlinkedserver (SQL 2008+ or SQL 2012) and sp_addlinkedsrvlogin (SQL 2008+).

A similar Q&A was previously posted in SO as Linked Server In SQL Server Express


Are regular VACUUM ANALYZE still recommended under 9.1? I'm using PostgreSQL 9.1 on Ubuntu. Are scheduled VACUUM ANALYZE still recommended, or is autovacuum enough to take care of all needs?

If the answer is "it depends", then:

I have a largish database (30 GiB compressed dump size, 200 GiB data directory)
I do ETL into the database, importing close to 3 million rows per week
The tables with the most frequent changes are all inherited from a master table, with no data in the master table (data is partitioned by week)
I create hourly rollups, and from there, daily, weekly and monthly reports

I'm asking because the scheduled VACUUM ANALYZE is impacting my reporting. It runs for more than 5 hours, and I've had to kill it twice this week, because it was impacting regular database imports. check_postgres doesn't report any significant bloat on the database, so that's not really an issue.

From the docs, autovacuum should take care of transaction ID wrap around as well. The question stands: do I still need a VACUUM ANALYZE?

VACUUM is only needed on updated or deleted rows in non-temporary tables. Obviously you're doing lots of INSERTs but it's not obvious from the description that you're also doing lots of UPDATEs or DELETEs.

These operations can be tracked with the pg_stat_all_tables view, specifically the n_tup_upd and n_tup_del columns. Also, even more to the point, there is a n_dead_tup column that tells, per table, how much rows need to be vacuumed.
(see Monitoring statistics in the doc for functions and views related to statistics gathering).

A possible strategy in your case would be to suppress the scheduled VACUUM, keeping an eye on this view and checking on which tables the n_dead_tup is going up significantly. Then apply the aggressive VACUUM to these tables only. This will be a win if there are large tables whose rows never get deleted nor updated and the aggressive VACUUM is really necessary only on smaller tables.

But keep running the ANALYZE for the optimizer to always have fresh statistics.


Query output conditional upon values in dataset If the number column contains values 5 AND 6, AND the value X exists in the chr column, I would like to exclude rows with 10 in the test column.

But, if the number column contains values 4 AND 5, AND the value X does not exist in the chr column, I would like to exclude all but ONE of the rows with 10 in the test column.

+----+------+-----+--------+
| id | test | chr | number |
+----+------+-----+--------+
|  1 |    7 | C   |      4 |
|  2 |    7 | T   |      5 |
|  3 |    8 | C   |      4 |
|  4 |    8 | T   |      5 |
|  5 |    9 | A   |      4 |
|  6 |    9 | G   |      5 |
|  7 |   10 | T   |      4 |
|  8 |   10 | A   |      5 |
|  9 |   10 | X   |      6 |
| 10 |   14 | T   |      4 |
| 11 |   14 | G   |      5 |
+----+------+-----+--------+

How is it possible to do in MySQL?

I am pretending your table name is number_table. You query can be divided into two separate ones -- the first excludes rows with 10 in the test column and the second includes only one row with 10 in the test column. I assume the two queries are independent. From interpreting your question, I believe this query will do the job.

SELECT * FROM number_table WHERE
    (EXISTS(SELECT * FROM number_table WHERE number = 5 OR number = 6) AND EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number <> 10)
UNION
SELECT * FROM number_table WHERE
 ((EXISTS(SELECT * FROM number_table WHERE number = 4 OR number = 5))
    AND NOT EXISTS(SELECT chr FROM number_table WHERE chr = X) AND number = 10
 ) LIMIT 1;


Is there a standard for naming foreign keys when two columns reference the same table? I've always used the name FK_CurrentTableName_ReferencedTableName. I just ran into a situation where I've got a table with CreatedById and ModifiedById both pointing to the same (users) table, so this naming convention won't work.

Is it actually better to change all my reference constraints to FK_CurrentTableName_CurrentColumnName? Or should I keep it the same unless there is a conflict and then do FK_CurrentTableName_CurrentColumnName_ReferencedTableName or FK_CurrentTableName_ReferencedTableName_CurrentColumnName?

I've searched and read contradictory opinions. For SQL Server, is there a standard?

No, there is no standard naming convention for this or just about any other scenario. Naming conventions are purely subjective: that your convention makes sense, and that you use your standard consistently, are both much more important than which standard you choose.

That said, in this case, I would opt for this option:

FK_CurrentTable_Created_Users
FK_CurrentTable_Modified_Users

This provides all the information you would need if you wanted to deduce something about this foreign key without all the difficult labor of retrieving the actual definition. If you just use this:

FK_CurrentTable_Created

Then you don't get any information about the other table. In this specific case it should be easy enough to infer, but that won't always be true. If you use this:

FK_CurrentTable_Users_Created

That just feels to me like it's in the wrong order. "This table references that table on column x." Much more intuitive, IMHO, to say "This table's column x references that table." It might even call for full references on both sides (of course, the users table may have more than one candidate key, though it is fairly uncommon for an FK to point to a candidate/natural rather than the surrogate):

FK_CurrentTable_Created_Users_UserID

And therein lies the problem. You're going to continue finding contradictory opinions precisely because this decision has to, by definition, be driven by opinion. And not everyone is going to agree.


SSMS fail to connect localhost SQL Server 2008 R2 suddenly Until today when accessed by remote desktop to my sql server could connect using localhost, but suddenly I cant (without any update or change in my server), now I can connect using localhost\instance but cant using localhost.

This is the error, and also it affects connections to several linked servers in this machine

A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections.

Checkpoints:

Firewall - disabled
Allow remote connections - yes
TCP connection allowed - yes
Server running - yes
User running services - domain administrator

Also in my computer I can access to this server using SSMS and only the IP.

In our development environment the user running sql server services is local administrator and everything go fine, but our network team doesn't change anything in our domain that affect administrator user.

SO: Windows Server 2008 R2 Datacenter SP1

If it's a named instance well, then, you should have always been using localhost\instance. This is how it's supposed to work. You shouldn't have been able to connect before and I suspect there is a vital detail missing that would explain that.

Possible explanations (there may be others): the instance was the only one on the machine, and maybe an alias was set up or it happened to be running on port 1433 or the browser was able to redirect to the instance. The alias has since been removed or the service has been restarted and/or the instance is for other reasons (such as a manual config change) now on a different port or the browser service has been disabled.

In any case, the solution is to update your connections to reference the full path to the instance, namely: localhost\instance or as Kin suggests make sure the instance is hard-coded to run on a specific port and then use localhost,<port number>.


What is the status of the PostQUEL source code? Recently as part of my answer to "Is the keyword “ALIAS” actually used?" I wanted to go back further than Postgres95. Postgres95 is the first version of Postgres that was migrated to use SQL rather than QUEL.

Is POSTQUEL (the version that used QUEL) open source and is it available anywhere? The PostgreSQL repo starts with an import of Postgres95.

The database was not known as POSTQUEL, but as POSTGRES. PostQUEL is "an incompatible, extended version of QUEL (the Ingres query language)."

As to the source code, I found it linked from this Command Prompt Blog Post,

You can find it available on the POSTGRES Project page at Berkeley.


Databases missing after exporting server and rebuilding master DB We had a server running Microsoft SQL Server 2014 databases on Hyper-V. I exported it (with C: and D: drives) and opened on other machine.
I could not start the MSSQLSERVER service because it said the master db needed to be rebuilt.
I've rebuilt the master db, but now the databases are empty, I cannot see databases except master, model, msdb, and tempdb.
I have all databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA. How to restore them and not lose all functionality?

I have all Databases (mdf and ldf files) in this folder D:\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA how to restore them and not lose all functionality?

When you rebuild master, it loses track of where the user databases are.  You just need to attach them, and recreate the logins for any orphaned users.
And also consider linked servers, scheduled jobs and everything else which is stored outside the user databases and was scratched by the rebuild.


Nested Cursors in Mysql problem with FETCH I have the problem with my nested cursors in MySQL.

My second cursor only advances 1 single record of the first courses and then shows me message indicating. Below is a sample of the response from my Mysql Workbeanch.

OPEN queryLegalArticleSource;
    queryLegalArticleSource_LOOP : LOOP
        -- PARSED VARS
        FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;
        -- ************************************
        -- VALIDATE PROCESS noMoreRows
        -- ************************************
        IF noMoreRowsTwo = 1 THEN
            CLOSE queryLegalArticleSource;
            LEAVE queryLegalArticleSource_LOOP;
        END IF;
        SELECT legalBodyId, companyIdSource, configTypeArticleTypeTwo, noMoreRowsTwo;
    END LOOP queryLegalArticleSource_LOOP;
CLOSE queryLegalArticleSource;

If I comment the FETCH line

-- FETCH queryLegalArticleSource INTO configTypeArticleTypeTwo;

Get all the results, but I do not have the variable I need.

Check if any cursor was closing it before, but apparently I have everything right.

Does anyone have any idea?

You are trying to close the cursor after it is already closed. If no more rows=1 is true, it closes the cursor. Then exists the loop. Then tries to close the cursor again.