Can I modify a column's data type against a live table in Postgres? I have a sparse matrix table where every attribute is citext. There are some indexes built against a few of the fields.

I want to change these columns back to varchar.

Can I do this against a live production table, or do I need to bring down the
database?
Do the indexes need to be rebuilt afterwards?
Will this result in any space savings?
Will this give us any kind of performance boost (if those columns were heavily indexed)?

Can I do this against a live production table, or do I need to bring down the database?

Yes. You can do it against a live table. Will even be very fast since citext and varchar are binary coercible, so no table rewrite is required (since Postgres 9.1).

But this still acquires an ACCESS EXCLUSIVE lock on the table, which makes any concurrent access on the table wait, and potentially fail. And if there are any indexes involving the column, those must still be rebuilt (automatically), which may take substantially longer.

And any dependent objects like views that use the column are road blockers and need to be removed before the change (and added back later).

Related:

Does "alter table … set data type" functionally differ from "alter table … type"?

  Do the indexes need to be rebuilt afterwards?

No. Indexes involving the column in any way have to be rebuilt, but that happens automatically. See above. The manual:

  Indexes and simple table constraints involving the column will be automatically converted to use the new column type by reparsing the originally supplied expression.

  Will this result in any space savings?

No. Binary coercible means same space on disk and in RAM. There may be a one-time effect for rebuilt indexes that are now in pristine condition - especially if those were bloated.

  Any kind of performance boost?

Not likely. Depends on the complete picture. citext may or may not have been useful. I don't recommend citext after mixed experiences. I rather go with expression indexes. Or consider a non-deterministic collation in Postgres 12 or later.

Generally, citext operators are a bit slower everywhere than plain text / varchar operators. (They have to do more work.) Possible one-time effects from rewriting involved indexes can be achieved with REINDEX as well.

Related:

Can I change CITEXT columns to VARCHAR without encountering any surprise difficulties?
What is the overhead for varchar(n)?


PostgreSQL 10 - secure remote logical replication (SSH?) I am exploring Postgresql for a new project, and the upcoming logical replication feature in version 10 interests me.

I would like to use it to have a remote server automatically hold an up-to-date copy of some tables from a different server, across the internet. With proper database design this could make entire webservices unnecessary.

However, the feature requires the use of a plain connection string in the CREATE SUBSCRIPTION command. This means I cannot simply point it to a remote server, not without exposing it to the internet.

First, what secure options do I have available here?

Creating a VPN between the two servers. Safest option, most cumbersome.
Establish a SSH tunnel between the two servers. Less cumbersome, still safe as long as the SSH server is properly configured. Once the SSH tunnel is up the connection becomes just a normal SQL connection, so it should work with replication, right?
Postgresql has native SSL support. I'm not sure if the replication subscriber would automatically use it when connecting to the publisher. But more importantly, while this would protect the data in transfer, it would still require me to expose the server to the internet, use something like fail2ban to protect against intruders, and I'm sure I've missed other potential threats, so all in all it would make me uneasy.
Any other option I may have overlooked?

Second, are there any other reasons why using logical replication in such a fashion would be a bad idea?

For native SSL support:

On the subscription side, you can put sslmode=verify-full in the CONNECTION string for your CREATE SUBSCRIPTION, to force ssl to be used.

On the publication side, you can whitelist only the PostgreSQL port and only for the known IP address of the subscriber at the firewall.  There shouldn't be a need for fail2ban if you do this.  You can also demand that the subscriber side use ssl client cert authentication rather than password authentication, by using cert for the pg_hba method.

Of course you will need a CA to issue certificates, or run your own mini-CA.


How can I check if my log is truncated? I have a testdb. the recovery mode is on simple and I am back my system up with veeam. Now how can i check if my log from the testdb was being truncated after the backup or not?

I just found some commands like DBCC SQLPERF(LOGSPACE). But with that i just see the usedspace.

In simple recovery mode the log gets truncated automatically when checkpoint is issued.  Now when you run DBCC SQLPERF(LOGSPACE) and your log is more than 70% full then you can assume that your log is not getting truncated because a checkpoint is supposed to be issued when the log grows to 70% in simple recovery.

If you suspect that your log is not getting truncated in simple recovery you can use sys.databases, log_reuse_wait_desc columns to find out why the last log truncation did not work.

If you wanna dig a bit deeper, you can use dbcc loginfo and check the status column to see which virtual log records are active and in that way figure out if its getting truncated or not


Azure GRANT ALTER ANY EVENT SESSION What is the correct way to give someone permission to create extended event sessions in Azure SQL Server?

I've been bouncing queries to my manager non-stop today, because when he tried to grant me permission to do it myself using the following statement:

GRANT ALTER ANY EVENT SESSION TO myUserName

He got the following errors:
Securable class 'server' not supported in this version of SQL Server. and
This property is not supported: ApplicationName.

Has anyone else ever needed to grant this permission before on an azure sql database?

Based on the documentation (here) one must have Control permission on the database to issue a CREATE EVENT SESSION and the same permission should be required for the ALTER EVENT SESSION.


Select query taking too long MYSQL 5.6.17 I have the below select and can't understand why it's taking about an hour to give me the result.

SELECT
(CASE
    WHEN a.ACODE='000A' THEN c.datfinprel
    WHEN a.ACODE='000T' THEN c.datiniprel
    WHEN a.ACODE='000S' && a.ACODETP=1 THEN c.datfinprel
    WHEN a.ACODE='000S' && a.ACODETP=2 THEN c.datiniprel
END) AS DATA_PREL,

A.`date_doc`, DATE(A.`created`) AS created, A.`stagione`, A.`fabbisogno`, A.`modello`, A.`commessa`,A.`code`, A.`title`, A.`color`, A.`measure`, A.`alt_pz`, A.`dist_base`, A.`qnt`,B.price, A.`cause`
FROM
    wb_mod_deposit_out A, wb_mod_deposit B, from_generale_java C
WHERE
    A.`stagione` IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')
AND B.ID=A.`deposit_id`
AND a.`code`=B.`code`
AND a.`color`=B.`color`
AND a.`stagione`=c.`stagione`
AND a.`modello`=c.`modello`
AND TRIM(LEADING '0' FROM a.`commessa`) = TRIM(LEADING '0' FROM c.`commessa`)
AND a.`type`=1;

The extended explain:

id  select_type   table               type    possible_keys                                   key           key_len  ref                    rows  filtered  Extra
 1  SIMPLE        C                   index   stagione,generale                               generale      123      (NULL)                 3729    100.00  Using where; Using index
 1  SIMPLE        A                   ref     stagione,test_OUT,CODE+COLOR,modello+comm+stag  stagione      153      csm_db.C.stagione       261    100.00  Using index condition; Using where
 1  SIMPLE        <subquery2>         eq_ref  <auto_key>                                      <auto_key>    153      csm_db.A.stagione         1    100.00  (NULL)
 1  SIMPLE        B                   eq_ref  PRIMARY,test_depo                               PRIMARY       4        csm_db.A.deposit_id       1    100.00  Using where
 2  MATERIALIZED  wb_mod_deposit_out  range   stagione,modello+comm+stag,date_created         date_created  6        (NULL)                15245    100.00  Using index condition; Using MRR

You could try doing your Case selects as a cte.
The  line: A.'stagione' IN (SELECT DISTINCT STAGIONE FROM wb_mod_deposit_out WHERE CREATED BETWEEN '2017-12-11' AND '2017-12-17')

will also be a memory hog. Do you have an index on your CREATED column?

Also, if you can find a way to take out TRIM(LEADING '0' FROM a.'commessa') = TRIM(LEADING '0' FROM c.'commessa') - maybe do it in an update statement or a third cte.


Two backup programs, diff and log out of sync I am running encrypted backup on SQL Server 2017.
Doing a test restore, it failed:
RESTORE DATABASE [test]
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\FULL\DPH-SQL-SLI-12P_BTBLEAD_FULL_20201021_185145.bak'
WITH NORECOVERY, REPLACE;

RESTORE DATABASE [test]
FROM DISK = N'\\DPH-DD-SLI-001\SLI_SQL_Backups\Encrypted\DPH-SQL-SLI-12P\BTBLEAD\DIFF\DPH-SQL-SLI-12P_BTBLEAD_DIFF_20201022_071002.bak'
WITH NORECOVERY;

Processed 199264 pages for database 'test', file 'BTBLEAD_Data' on file 1.
Processed 2 pages for database 'test', file 'BTBLEAD_Log' on file 1.
RESTORE DATABASE successfully processed 199266 pages in 446.348 seconds (3.487 MB/sec).
Msg 3136, Level 16, State 1, Line 6 This differential backup cannot be restored
because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 6 RESTORE DATABASE is terminating abnormally.

It WAS running flawlessly, until they installed Commvault cloud backup.
I found that if a Commvault backup kicks off, I can no longer restore my native local backup past the point in time that Commvault kicked off!
Apparently, SQL Server resets the LSN on each backup, but doesn't know there are two distinct programs running.  When Commvault kicks off, the LSN is brought forward, and my native backup does not know about it
I like having my own local backup, as it is faster and quicker to restore than the cloud backup, than waiting for another group to do the restore from the cloud for me.
Is there any way to keep the backups separated?
Another group manages the Commvault backup. I am new to SQL Server - I am an Oracle DBA by trade.

Differential backups are attached to the Full. If your other backup tool takes a full, it's normal that you cannot restore your diff without that full.
You should not use 2 different backup tool in parallel. If you really need to, then you can check if it's possible to specify the "copy_only" option for the backup in CommVault (so your native backup will remains the "official" backups).
If it's not possible, you could then do only full (with copy only) on your side. Of course, if the database are big, this mean bigger backup and longer time to restore.
Maybe you should address the problem at the source. It seems to me that what you really need is permission in CommVault to be able to run restore from that tool.


SSMS shortcut to comment/uncomment a specific part of the query? I'm looking for an option to comment/uncomment a particular part of the query with the Slash Star Comment option.

Scenario:

While debugging the queries with N numbers of JOINs, I comment out each table in the join and its related conditions one by one and find which table causing the issue or wrong data. If the commented table didn't cause the issue, I will uncomment the table and its related condition and try with the next table.

For this purpose currently I manually add -- comment in front of the particular line or press Ctrl + C to comment the entire line, if the select/conditions are in middle of the line, I move the select/condition alone to the next line and add -- comment. If there is no issues with the join, again I undo the changes or Ctrl + U to uncomment.

Moving the condition to the new line and adding the comment is burden to me, some time it caused bad alignment too.

Expectation:

What I want is need some shortcut key to add the /* in front and */ in end of the selected text.

Samples:

The below is the sample case, which my query looks like (in actual it may contain more select columns, tables and conditions).

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate,
       T2.AccountName, T3.LinkId, T3.LinkName, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1
INNER JOIN TableFour T4 ON T4.Id = T1.Id AND T4.LinkId = T3.LinkId AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL AND T3.DeletedDate IS NULL AND T4.DeletedDate IS NULL

Here if I want to comment the TableThree and its related content in the SELECT, JOIN and conditions in WHERE. I will select the content and press the shortcut key to add the /*  */ comment separately.

Expected the behavior is:

SELECT T1.Id, T1.Name, T1.Designation, T1.CreatedDate,
       T2.AccountName/*, T3.LinkId, T3.LinkName*/, T4.OwnershipName
FROM TableOne T1
INNER JOIN TableTwo T2 ON T2.Id = T1.Id AND T2.Name = T1.Name
/*INNER JOIN TableThree T3 ON T3.Id = T2.Id AND T3.IsActive = 1*/
INNER JOIN TableFour T4 ON T4.Id = T1.Id /*AND T4.LinkId = T3.LinkId*/ AND T4.IsActive = 1
WHERE T1.CreatedDate BETWEEN @FromDate AND @ToDate AND T1.DeletedDate IS NULL
    AND T2.DeletedDate IS NULL /*AND T3.DeletedDate IS NULL*/ AND T4.DeletedDate IS NULL

You can easily do this with Code Snippets, something like this will work for your example:

<?xml version="1.0" encoding="utf-8" ?>
<CodeSnippets  xmlns="http://schemas.microsoft.com/VisualStudio/2005/CodeSnippet">
    <_locDefinition xmlns="urn:locstudio">
        <_locDefault _loc="locNone" />
        <_locTag _loc="locData">Title</_locTag>
        <_locTag _loc="locData">Description</_locTag>
        <_locTag _loc="locData">Author</_locTag>
        <_locTag _loc="locData">ToolTip</_locTag>
        <_locTag _loc="locData">Default</_locTag>
    </_locDefinition>
    <CodeSnippet Format="1.0.0">
        <Header>
            <Title>SLASH STAR COMMENT (surrounds)</Title>
            <Shortcut></Shortcut>
            <Description>Surround selected text with slash star comment block /**/</Description>
            <Author>wBob</Author>
            <SnippetTypes>
                <SnippetType>SurroundsWith</SnippetType>
            </SnippetTypes>
        </Header>
        <Snippet>
            <Declarations>
                <Literal>
                    <!--<ID>x</ID><ToolTip>y</ToolTip><Default>z</Default>-->
                </Literal>
            </Declarations>
            <Code Language="SQL">
                <![CDATA[/* $selected$ $end$ */]]>
            </Code>
        </Snippet>
    </CodeSnippet>
</CodeSnippets>

Save the file with a .snippet extension and follow the instructions from here to add the snippet to SQL Server Management Studio (SSMS).

Instructions for use

highlight the text you want to surround with the comment block in the query editor
Press Ctrl+K, Ctrl+S to summon the 'Surround With' snippets.  Note the comma between the two sequences, it means "press Ctrl+K, then press Ctrl+S in quick succession"
Select your snippet from the list
Your highlighted text is surrounded with the comment block
press Ctrl+Z to Undo - this is the normal windows undo command.  It will remove the comment block as long as you haven't made any other edits.


How to find archivelog gaps in the backups I manage Oracle databases that are backed up using RMAN. How can I find gaps in the backed-up archivelogs?

I.e., I want to detect when something like this happens:

Take full backup with archivelogs
Back up morning archivelogs and delete input
Back up evening archivelogs and delete input
Someone permanently deletes the backupsets containing the morning archivelogs
Crosscheck so RMAN knows some backupsets have gone missing

Short of doing or simulating a full restore and recovery, how can I detect that there is a gap? How can I see that backupsets are missing that would be needed for full recovery?

I think that REPORT is the appropriate command, but at the moment I cannot test it. Before you execute the report command you should execute a CROSSCHECK command so that rman does actually know which backups exists and which are missing.

If you want to find out if there are archive log backups missing then

REPORT NEED BACKUP DATABASE;

should do the job. The whole database must be backed up because of the missing archive logs the database cannot be restored to the current point in time. So the command will report all data files.

If backup pieces were destroyed that contain datafiles of the morning full backup then

REPORT NEED BACKUP DAYS 1 DATABASE;

So this command will report both problems.

The report-command is documented in the Backup and Recovery Reference and in the Backup and Recovery User's Guide.

So all in all do

CROSSCHEK BACKUP;
REPORT NEED BACKUP DAYS 1 DATABASE;


Multi SQL Server Web licence on Single Machine I have a cloud server on 1and1.com and that has 8 Socket/Core. I have aslo installed the SQL Server Web Edition provided by 1and1.com. At the the time of installing the options shows to me you need 2 licence (as Web edition is limited to 4 socket). They also telling, you have 2 licence and 2 licence distributed among  8 core and billed me for two licences. but it's look like our sql server is using only 4cpu.

When i run

  select scheduler_id, cpu_id, status, is_online
  from sys.dm_os_schedulers
  where status = 'VISIBLE ONLINE'

It is showing only 4 cpu is online. and rest four are offline when i run

  select scheduler_id, cpu_id, status, is_online
  from sys.dm_os_schedulers
  where status = 'VISIBLE OFFLINE'

Can anyone help me to understand with this situation.

Apart from the licensing question, there's a question about the technical limitations of Web/Standard edition.

Web edition is limited to "Lesser of 4 sockets or 16 cores", but for a Virtual Machine the sockets and cores are reported by the hypervisor, and may differ from the underlying hardware.  That appears to be the case here, as the hypervisor is presenting the VM with 8 VCPUs and claiming that they are on 8 separate sockets.

SQL Server on startup looks at the reported sockets and cores when and will limit the schedulers accordingly.  Here that's limiting the CPU use to half of what it should be.

To fix this, the hoster should reconfigure the hypervisor to accurately report the socket and core count of the hypervisor host.


Add database to existing replication which uses binlog_do_db? We have two MySQL 5.5 servers running on Ubuntu 12.04.

We are successfully replicating 3 databases from server01 to server02, but need to add a 4th (new) database.

Our my.cnf on the master has:

binlog_do_db = database1
binlog_do_db = database2
binlog_do_db = database3

How do I add the 4th database to replication? Everything I've read has indicated we'll need to restart MySQL, is there any way around this? Or do we just need to schedule it in?

This is the plan we have so far, but I was hoping for a method which didn't require a restart. Am I overcomplicating this?

GRANT REPLICATION SLAVE ON *.* TO 'slaveusr'@'%' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;
QUIT;

Edit my.cnf to include the new database:

binlog_do_db = database4

Restart MySQL

  service mysql restart

Backup the database for the initial copy to the slave server

USE database4;
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;

In a new shell

mysqldump -u root -ppassword --opt database4 > database4.sql
scp database4.sql root@server02:/home

In the old shell

UNLOCK TABLES;
QUIT;

Change my.cnf on the slave server

replicate-do-db = database4

Restart MySQL

service mysql restart

Restore the dumped DB

mysql -u root -ppassword database4 < database4.sql

Making changes to binlog-do-db will require a restart I'm afraid.

Personally I would remove all the binlog-db-db from the Masters my.cnf, and just use replicate-do-db on the slave to filter what you want to be processed.

In this way you can addd more databases to the master in future without needing to restart.

If I understand correctly, you are trying to copy the new Database (database4) onto a slave that already has 3 databases replicating (these are all on the same MySQL Database).

The problem then is that replication will need to be paused whilst you do this.

The process I would use would be:

1) MASTER - restart the master with new binlog-do-db | remove all binlog-do-db in order to start populating the binary log with database4's data

2) SLAVE - Check the slave Database's replication is up to date (SHOW SLAVE STATUS > Seconds_Behind_master). If not wait till it is

3) MASTER - FLUSH TABLES WITH READ LOCK; To stop new data entering the master

4) SLAVE - Check replication is fully caught up and no data is replicating (read_master_log_pos and exec_master_log_pos should be the same and not changing (in show slave status;))

5) SLAVE - STOP SLAVE; to stop any data replicating into the database once you unlock the master shortly.

6) Run MySQLDUMP with --single-transaction option

7) Once MySQLDUMP has started running unlock the master Database UNLOCK TABLES; In this way your systems can continue reading and writing to the master, hopefully keeping downtime to a minimum

n.b. that MySQLDUMP may lock the database4 schema while it works depending on your system

8) Once MySQLDUMP has completed, import it into the slave Database

9) check the newly imported database4 looks correct

10) Restart the slave Database, adding replicate-do-db=database4 to the my.cnf file as you go

Once it comes back on, it should continue replicating from where it left off, but including database4.


SELECT week, GROUP_CONCAT(DISTINCT status)
    FROM tbl
    GROUP BY week
    HAVING COUNT(DISTINCT status) > 1;


Preventing sysquerymetrics from being created ASE 15.7 Can I prevent Sybase ASE 15.7 from creating the sysquerymetrics view for a database upon creation?  And also, if the view is dropped how can it be added later?

Prevent it to be created, I don't think so, it uses system tables, so even if you could, not wise you could impact other processes that might need those tables.
You can drop it if you desire.

To recreate it, check the installmaster script, it should be there the definition of that view.


SQL Server Analysis Services - who uses them? We all know Sql Server Analysis Services (SSAS) delivers online analytical processing (OLAP) and data mining functionality for business intelligence applications.

Who has used this service in their projects? I need some guidance on what this really involves?

Analysis Services is a very large and widely used part of the MSSQL stack, your question is entirely too broad to give an accurate answer. I will tell you that a typical SSAS project involves more than just OLAP; you have to shape your data first. A typical scenario starts with one or more sources of data which have disparate structures. An ETL process (often times performed using a tool like Integration Services or SSIS)  is used to gather that data, perform operations such as cleansing and ultimately drops that data into a data warehouse/mart which is a central database typically designed using a star or snowflake schema. (this is a world unto itself with lots of debate but think of a more flattened data design). From this warehouse you use SSAS to design cubes which are built from the data in said warehouse and once they are complete you browse those cubes with everything from Excel up to an including highly specialized reporting environments.

Again, your question lacks specificity so it is hard to explain things beyond that without writing a small book.


PostgreSQL socket not listening on the right one I'm trying to get my PostgreSQL to work.

It is clearly running, according to: ps aux | grep postgres | grep -v grep

I am getting -

username            5781   0.0  0.0  2441364    524   ??  Ss    9:05AM   0:00.33 postgres: stats collector process
username            5780   0.0  0.0  2445216   1628   ??  Ss    9:05AM   0:00.19 postgres: autovacuum launcher process
username            5779   0.0  0.0  2445084    532   ??  Ss    9:05AM   0:00.14 postgres: wal writer process
username            5778   0.0  0.0  2445084    576   ??  Ss    9:05AM   0:00.14 postgres: writer process
username            5777   0.0  0.0  2445084    592   ??  Ss    9:05AM   0:00.00 postgres: checkpointer process
username            5775   0.0  0.1  2445084   3544   ??  S     9:05AM   0:00.12 /usr/local/opt/postgresql/bin/postgres -D /usr/local/var/postgres -r /usr/local/var/postgres/server.log
username            6044   0.0  1.6  2884196  66420   ??  S     9:44AM   0:21.62 /System/Library/Java/JavaVirtualMachines/1.6.0.jdk/Contents/Home/bin/java -Dfile.encoding=UTF-8 -classpath /Applications/RubyMine.app/plugins/DatabaseSupport/lib/jdbc-console.jar:/Applications/RubyMine.app/lib/util.jar:/Users/username/Library/Preferences/RubyMine50/jdbc-drivers/postgresql-driver-license.txt:/Users/username/Library/Preferences/RubyMine50/jdbc-drivers/postgresql-9.1-901.jdbc3.jar com.intellij.persistence.database.console.RemoteJdbcServer org.postgresql.Driver

But when I try to createuser -s -U $USER, I am getting:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
Is the server running locally and accepting
connections on Unix domain socket "/var/pgsql_socket/.s.PGSQL.5432"?

However, it is clearly running, but just not talking to the correct socket. I tried: sudo find / -name .s.PGSQL.5432 -ls and am getting:

find: /dev/fd/3: Not a directory
find: /dev/fd/4: Not a directory
48453370        0 srwxrwxrwx    1 username   wheel    0 Apr 24 11:59 /private/tmp/.s.PGSQL.5432

How do I get PostgreSQL to talk with the correct socket?

Update:

I checked ls -la under /var/pgsql_socket/.s.PGSQL.5432:

total 0
drwxrwxrwx   2 _postgres  _postgres   68 Sep  19  2011 .
drwxr-xr-x  28 root       wheel      952 Nov   8  2011 ..

Try the following command:

createuser -h 127.0.0.1 -s new_role

And if you want to connect your db by using psql interface:

psql -h 127.0.0.1 db_name role_name


Filter array attribute in jsonb column type using jsonb_array_elements_text My Postgres version: 10.9

Using JsonB Column type which has a below structure:

columnName: favorites
{
"favouriteCity": "Boston",
"favoriteColors": ["Blue", "Red", "Green"],
}

Now, I want to filter all records which have favorite colors as Blue assigned.

I tried the below queries, but it didn't work.

select *
from r_fav
where favorites->>'favoriteColors' in ('Blue', 'Red')

or

jsonb_array_elements_text(favorites->>'favoriteColors' @>'Blue', 'Red')

Any idea how can I filter this?

You can use the contains all ?& operator with arrays:

select *
from r_fav
where favorites -> 'favoriteColors' ?| array['Blue', 'Red']

That will return rows at least one of the colors exist in the array. If want those where all colors are contained, use the operator ?& instead


Does my m2n-table need a primary key? Two tables called Chip and Dale have m2n-relations to each other. I am used to build a link table called

CHIP2DALE (or Dale2Chip) containing:

id NUMBER UNIQUE NOT NULL
chip_id NUMBER NOT NULL
dale_id NUMBER NOT NULL

and an UNIQUE INDEX CHIP2DALE_UQ ON (chip_id, dale_id), because multiple relations don't make sense.

My question is: Do I still need the id column here? Is there any good reason for having this column? My rows are ultimatly identified by the combination of chip_id and dale_id GIVEN THAT I have absolutely no other columns here but the two.

You never ever need the id column in any table. It is a surrogate key which replaces the natural key for pragmatic reasons. Often this is because a narrow, monotonic key gives better performance, or because join clauses become cumbersome with multiple natural key columns.

Some shops have an id on every table as a standard. Consistentcy is good, so that would suggest having the id in this table, too. If this table is the parent of a 1:m relationship it can help performance to have the parent keys directly in the child table without having to follow surrogate keys.

It does take room on disk. If you have a gazillion rows that may be an important performance consideration. This would suggest omitting the id.

Broadly, in my experience it is best to omit the id from intersection tables, and add it later if the model changes.


Pulling data from identical tables in different databases I'm dealing with a situation in SQL Server 2008 where each client is setup as their own database, with each database having identical table structures.

I have a query that selects from across ~20 different tables within a database, and inserts the records into a reporting database.  What we'd like to do is repeat this process for each database, so that all reporting information across all the client databases are then contained in a consolidated reporting database.

The "easy" way would be to just copy/paste the code and do a find/replace all on the database names to have the script execute across all clients.  Is there any better way than that though?

This answer assumes you have a master database/table of clients and their associated database names, or some way to figure out which databases in a given instance of SQL Server have relevant databases that you want to query.

Since this is for reporting purposes, you may want to implement some form of snapshotting technique to ensure the data sets consolidate to the same point in time, on at least a per-client basis. As a nice side effect, these techniques usually also alleviate locking/blocking effects of running these types of queries directly against the production data.

Pure Dynamic SQL
Using the master list, build up a SQL statement that uses 3-part names like you're doing now, but instead, inject the database name dynamically. It's unclear from the question what format the data is going to end up in, but it sounds like you want to UNION ALL everything together, which is easily accomplished.

This may be advantageous for you now because it will require very little up-front work.

The downside is that this approach is not very flexible and quickly becomes complicated if you need to run the query when the individual database revisions are different (i.e., the query was tied to a given database revision, and when a database update was deployed, it didn't succeed for all clients). It also may have problems or complications depending on the security model you're using for these databases.

Database-Based Object(s) + Dynamic SQL
This solution creates permanent objects within every database which you can query in an external process. Usually this means a view, stored procedure, or table-valued function. Dynamic SQL will be used to inject the database names into the query as above, but this time all that's needed is to select from, or execute, the database object instead of the raw query directly.

This method gives all the advantages of protecting your code behind an interface just like it would by using views, stored procs, etc., in application code. This also solves the security issues much more easily.

The disadvantage, of course, is that this needs a little more up-front work and management, and that the queried object(s) can only be deployed in database updates, instead of independently.

My personal opinion is that the latter approach is a much better long-term solution, so in this type of scenario, that's the one I would opt for. For ad-hoc types of queries, using the pure dynamic SQL approach is fine.


SQL Server transferring results slowly in table result set, but instantly with XML I have a query that left joins 100+ tables and returns 2,000+ columns, but only a few rows (10-20).  This part is out of my control at the moment and it used to run ok with a bit fewer columns (maybe under the normal non-wide table width).  The execution is "instant" after the initial plan is compiled and I don't actually return all the results to the client.  If I return 1 row, it is instant and returns the result instantly.

It's when I return all 11 or so results it goes down to taking about 15 seconds.  BUT, if I add any of the "FOR XML [RAW [ELEMENTS, XSINIL]]" iterations, it returns all the results plus the XML overhead instantly as well.  This rules out the actual execution of the query, as far as I can tell, and isolates it to something between the query results being calculated and them being sent to the client.

I've been looking for about 4 days for anything to explain this.  Is there a way to get the XML speeds with the result sets?  Why is it doing this?

Have tried TCP/IP and Named Pipes on 2008R2 and 2016

UPDATE

Based on the feedback, for the tabular results, what happens is the first time through, it takes about 20 some seconds.  The timer in SSMS keeps running until about 11 seconds, then it stops.  Then, when the results are displayed, the timer jumps to the 20 seconds.  The next run the timer just hangs, then when the results are show, the timer pops up to around 9-11 seconds.

The way the 11 rows are selected by the joining the unique id of the main table against a table function.  I have tried replacing the table function with a temp table.  For selecting one record I basically pass data into the table function that results in one result.  I also tried using a straight id = literal number.  For those, the timer doesn't even leave 0 before the result is shown.

I can't "insert into" a temp table because there are too many columns for a regular table.  I also can't create a indexed view on it to check that out.

I ran the query with FOR XML again and checked for the timer.  It sits at 0:00 and says query complete.  I try again and immediately open the result in xml and it shows 11 rows of xml rows, with the last one of them 54,000 characters.  It does indeed only show 2023 if you cut and paste the tabular result.  The timing differences might be the amount of data in each row?  There are multiple columns with text types.

Speaking of text types, it keeps me from doing a "count distinct" on the full query to pull all the columns but not send them at all.  Otherwise I used SSMS and told it to discard the results.  I'm not sure if that affects the query plan.  It runs fast.

I was hoping to get this thing limping along for enough to change the way it was architected.  This simply isn't acceptable in the medium to long term.

THANKS for everyone's insight and help so far.

Those XML results you keep mentioning. I do not think you are seeing what you think you are seeing. ;-)

Is FOR XML magic?

When you have the XML result in the grid, click on the left margin that contains the row number of the result set (there should be just 1 row). That should highlight the XML result. Now hit Control-C to copy the XML and then in the query editor above, put the cursor on the beginning of an empty line and hit Control-V to paste the XML. That should leave the cursor on the far right, at the end of the XML. Look down at the status bar that has the Line #, Column #, and Character #. What does it shown for Column # and Character #? Mine shows 2033, yet the XML got clipped and there should be more.

Now click on the XML itself in the result set. That takes you to another tab with the full XML result, unclipped. That is more than likely well over 2033 characters of text.

The "instantaneous" result you are seeing with the FOR XML is merely the fact that SSMS is only displaying the first 2033 characters of however many there are, where it is 5000 or 5,000,000 characters more in the actual result value. And in fact, if your query takes long enough, you can pay close attention to the query timer in the lower, right-hand corner and if you re-run the query and are returning 10+ rows, that counter can still be going after the result grid finishes displaying its truncated XML snippet.

Do the number of rows and columns greatly affect the time to display?

I put together some code (shown below), which returns 2300 columns, to test this behavior. It starts out with the FOR XML being uncommented. After the initial run, it returns immediately each time, no matter how you adjust the @Rows input param. Set it to 10, then 20, then 40. The XML always comes back immediately. But also watch the query timer as it will keep going, especially as you increase the value of @Rows.

Then comment-out the SET statement that adds the FOR XML so you get the 2300 individual fields in the grid. It might take 30 takes to run the first time. Running again it took me only 2 - 3 seconds each time, even as I increased the @Rows from 4 to 10, 20, 40, 50, even 80. Hence, the time it takes to display the results for 2000+ columns is not a function of how many rows are being returned, at least not at this small scale.

The Code

DECLARE @Select NVARCHAR(MAX) = N'SELECT TOP (@Rows)
         OBJECT_NAME(sac1.[object_id]) AS [ObjectName], *';
DECLARE @From NVARCHAR(MAX) = N'FROM   [sys].[all_columns] sac1';
DECLARE @JoinTemplate NVARCHAR(MAX) = N'
INNER JOIN [sys].[all_columns] sac{{counter}}
        ON sac{{counter}}.[object_id] = sac1.[object_id]
       AND sac{{counter}}.[column_id] = sac1.[column_id]';

DECLARE @CRLF NCHAR(2) = NCHAR(0x0D) + NCHAR(0x0A);
DECLARE @Counter INT = 2;

WHILE (@Counter < 93)
BEGIN
  SET @From += REPLACE(@JoinTemplate, N'{{counter}}', CONVERT(NVARCHAR(5), @Counter));

  SET @Counter += 1;
END;

SET @Select += @CRLF + @From;
--SET @Select += @CRLF + N'ORDER BY OBJECT_NAME(sac1.[object_id])';
SET @Select += @CRLF + N' FOR XML RAW, ELEMENTS';

EXEC sp_executesql
  @Select,
  N'@Rows INT',
  @Rows = 4;

Uncomment the SET line that has the ORDER BY to see that it takes the full 30 - 40 seconds every time you run the query (due to the sort).


MySQL uses range and where on UPDATE with the primary key I have these simple SELECT and UPDATE requests on MySQL using the primary key, there are about 13 million rows in the table.

When running an EXPLAIN on both, I notice that the UPDATE one does not use the primary key in the same way and is reported as slow in the slow query log:

type is "range" for UPDATE and const for SELECT
extra is "using where" for UPDATE and "NULL" for SELECT

Here is the data:

Thank you for your help

So I would have to look at the query optimizer source code of why it is printing range, but my guess is that for writes it will just use a limited number of write methods for simplification/limitation of EXPLAIN. If we look at the actual low row level operations of both queries, we can see that the SELECT:

MariaDB [db]> flush status; SELECT `BuyPacker_Email`.`emailOpen` = 1567007592 FROM `BuyPacker_Email` WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)
1 row in set (0.000 sec)

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 1     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 0     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 0     |
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.001 sec)

... does a single row read through and index. And for the update:

MariaDB [db]> FLUSH STATUS; UPDATE `BuyPacker_Email` SET `BuyPacker_Email`.`emailOpen` = 1567007592 WHERE `BuyPacker_Email`.`emailId` = 17040352; SHOW STATUS like 'Hand%';
Query OK, 0 rows affected (0.000 sec)

Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0

+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| Handler_commit             | 2     |
| Handler_delete             | 0     |
| Handler_discover           | 0     |
| Handler_external_lock      | 0     |
| Handler_icp_attempts       | 0     |
| Handler_icp_match          | 0     |
| Handler_mrr_init           | 0     |
| Handler_mrr_key_refills    | 0     |
| Handler_mrr_rowid_refills  | 0     |
| Handler_prepare            | 2     |
| Handler_read_first         | 0     |
| Handler_read_key           | 1     | <-----
| Handler_read_last          | 0     |
| Handler_read_next          | 0     |
| Handler_read_prev          | 0     |
| Handler_read_retry         | 0     |
| Handler_read_rnd           | 0     |
| Handler_read_rnd_deleted   | 0     |
| Handler_read_rnd_next      | 0     |
| Handler_rollback           | 0     |
| Handler_savepoint          | 0     |
| Handler_savepoint_rollback | 0     |
| Handler_tmp_delete         | 0     |
| Handler_tmp_update         | 0     |
| Handler_tmp_write          | 0     |
| Handler_update             | 1     |  <----
| Handler_write              | 0     |
+----------------------------+-------+
27 rows in set (0.000 sec)

... it uses the exact same read strategy + the required write to update the record.

So there is no overhead in terms of reading more records than necessary or any difference in terms of more expensive read plans.
Whether it is a simplification  for EXPLAIN, or a limitation due to write queries, where it cannot go too far as to tell; or it shows a real difference because how writes work, and marks a real performance impact, one would need to go into implementation details- but for most cases, as long as the rows column says "1", I don't see any problems with the query- a range of 1 row should be almost equivalent to a const access, broadly speaking.


MongoDB Sharded Cluster: Balancing chunks not working, stuck at "step 2" I've one collection enable sharding by id key on 2 shard rs01 and rs02

Today I added a new shard rs03 into cluster.
I've checked balancer status by sh.status() as below:

shards:
    {  "_id" : "rs01",  "host" : "rs01/mongo30-01:27017,mongo30-02:27017" }
    {  "_id" : "rs02",  "host" : "rs02/mongo30-03:27017,mongo30-04:27017" }
    {  "_id" : "rs03",  "host" : "rs03/mongo30-05:27017,mongo30-06:27017" }
  balancer:
    Currently enabled:  yes
    Currently running:  yes

and some chunks already migrated to rs03

{  "_id" : "myDB",  "partitioned" : true,  "primary" : "rs01" }
    myDB.myCo
        shard key: { "_id" : 1 }
        chunks:
            rs01    20027
            rs02    19532
            rs03    8

I found I set too few memory on rs03.
I stopped mongod service on rs03, added memory and start it after reboot.

After many hours, I found the migrated chunks still 8.
I checked the operation in rs03 primary by db.currentOp() as below:

{
  "inprog": [
    {
      "desc": "migrateThread",
      "threadId": "0x22677e00",
      "opid": 206642,
      "active": true,
      "secs_running": 19754,
      "microsecs_running": NumberLong("19754679731"),
      "op": "none",
      "ns": "myDB.myCo",
      "query": {

      },
      "msg": "step 2 of 6",
      "numYields": 0,
      "locks": {

      },
      "waitingForLock": false,
      "lockStats": {
        "Global": {
          "acquireCount": {
            "r": NumberLong("662"),
            "w": NumberLong("660")
          }
        },
        "Database": {
          "acquireCount": {
            "r": NumberLong("1"),
            "w": NumberLong("659"),
            "W": NumberLong("1")
          }
        },
        "Collection": {
          "acquireCount": {
            "r": NumberLong("1"),
            "w": NumberLong("330"),
            "W": NumberLong("1")
          }
        },
        "oplog": {
          "acquireCount": {
            "w": NumberLong("328")
          }
        }
      }
    },

the opid kept as "step 2 of 6" for many hours.
Anything I can do to make the migration going on?
I tried to kill the opid and it forked again still "step 2 of 6" now.
and the chunks in sh.status() still 8.

The definition of the steps referenced in a chunk migration are a little fluid, and can depend on the version you are running. However, based on what you have provided I suspect that the primary on rs01 and the number of chunks you are trying to move is the source of the issue here.

Last time I looked at this in detail (around version 2.6), step 2 was basically a "sanity check" on the source shard primary to make sure it was ready to kick off another migration.  There are two common reasons (and some not so common) for the primary to get "stuck" in step 2:

The primary is "too busy" - i.e. it is processing a lot of deletes from previous migrations and does not want to take on more migrations until some of those finish
There is an error being hit (chunk too big for example) and the migration is being aborted

Given that your two existing shards were not yet balanced when you added a third shard, though rs03 will now be the new preferred destination for migrating chunks since it has the lowest total, adding a third shard will not make this happen any more quickly.  In fact it just means there are more chunks to move than before you added the third shard.

To confirm what the root cause is here, you will need to take a look at the logs on the primary of shard rs01. The shard with the most chunks will always be chosen as the source of a migration, and the primary will be the one doing all the work.  The logs should tell you why the migrations are being aborted or taking a long time.

If it is a case of being too busy, you do have an option that will free it up, temporarily at least (it may get stuck again later). You can step down the primary, thereby killing any background delete jobs it is running for cleanup.

However, this will mean leaving behind "orphaned chunks" and you will need the clean up command to fix that later - you are not solving anything per se, you are just putting the work off until later. There are also only limited options to tweak this behavior, for more see here.

There are other options in terms of balancing chunks more quickly (mitosis), but they are way beyond the scope of an answer on this site I'm afraid.

If there is an error being hit, and the cleanup is not the issue, then it would probably be best to get the primary log messages and post a separate question to figure out the cause.


When would I want to use COMPRESS and DECOMPRESS functions in SQL Server 2016? In what live operational or development situation /requirement would I want to use the Compress function?

Like when should I become thankful for Compress and Decompress functions introduced in version 2016?

Use them when you want to achieve the benefits and you are prepared to pay the costs.

Compression can significantly reduce the number of bytes required to represent a large value. This saves the cost of disk, reduces the time to take backups, and restore after an incident. Performance can improve as fewer bytes must be pulled off disk and passed over the network.

The cost is in CPU to (de)compress the value before it can be used. This adds latency, consumes cycles which aren't available for real work and burns electricity.

So if you have large objects (compression would make a significant difference) such as text or XML (compress well) that are used used infrequently (minimize overhead), disk is expensive and CPU is cheap compression maybe helpful.

If you're storing jpg (already compressed), are on a SAN (built-in compression & de-dupe, likely fast network) and on a small, busy server then likely compression is not beneficial.


Join tables with some same values I'm trying to get lines that have same ADAPTERID and Attribute_Name, but Attribute_Value can be active or designed automatically.
I used:

SELECT * FROM database1
where Attribute_Value IN ('ACTIVE','DESIGNED AUTOMATICALLY')
and Attribute_Name = 'State'
and ADAPTERID = '1277667'

and I get:

ADAPTERID   CFS_Name        Attribute_Name  Attribute_Value
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           ACTIVE

How can I get these  that are actually only different ones:

ADAPTERID   CFS_Name        Attribute_Name  Attribute_Value
1277667     1277667-Željka  State           DESIGNED AUTOMATICALLY
1277667     1277667-Željka  State           ACTIVE

thanks

If you want to see the records that have more than one value in Attribute_Value then you can't simply use filters on a WHERE because you want to validate rows in a group (a WHERE will filter by row, not by set).

Try using a GROUP BY with HAVING to check existance of multiple values for a particular set, then use this result to display the proper records.

SELECT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
FROM
    (
        SELECT
            Attribute_Name,
            ADAPTERID
        FROM
            database1
        WHERE
            Attribute_Value IN ('ACTIVE','DESIGNED AUTOMATICALLY')
        GROUP BY
            Attribute_Name,
            ADAPTERID
        HAVING
            COUNT(DISTINCT(Attribute_Value)) > 1
    ) Mult
    INNER JOIN database1 D ON
        Mult.Attribute_Name = D.Attribute_Name AND
        Mult.ADAPTERID = D.ADAPTERID

This will still display multiple records with the same Attribute_Value if repeated (by Attribute_Name and ADAPTERID), then you can just add DISTINCT right after SELECT to see uniques. You might also want to filter by Attribute_Value on the outmost SELECT in case you are just interested in this values.

SELECT DISTINCT
    D.Attribute_Name,
    D.ADAPTERID,
    D.Attribute_Value
-- ...

Also the INNER JOIN will filter rows that have NULL value on either Attribute_Name or ADAPTERID (on most databases). If you still want to display them you will have to add NULL check functions (the syntax depends on the DBMS, like ISNULL() for SQL Server or NVL() for Oracle).


Joining multiple subqueries I need a query that returns something in this format:

pk  id  v1 v2 v3 v4 v5
----------------------
...
pk1 id1 A1 A2 A3 A4 A5
pk6 id2 B1 B2 B3 B4 B5
...

My current data looks like this

CREATE TABLE foo(pk,id,value)
AS VALUES
    ( 'pk1' , 'id1',  'A1' ),
    ( 'pk2' , 'id1',  'A2' ),
    ( 'pk3' , 'id1',  'A3' ),
    ( 'pk4' , 'id1',  'A4' ),
    ( 'pk5' , 'id1',  'A5' ),
    ( 'pk6' , 'id2',  'B1' ),
    ( 'pk7' , 'id2',  'B2' ),
    ( 'pk8' , 'id2',  'B3' ),
    ( 'pk9' , 'id2',  'B4' ),
    ( 'pk10', 'id2',  'B5' )
;

The source table has ~160,000,000 rows. All columns are indexed (btree).

Currently I'm doing 5 (the real number is dynamic and maybe around 30, but for example above it's 5) different queries to extract:

1:

P1 id1 A1
P6 id2 B1

2:

P2 id1 A2
P7 id2 B2

3:

P3 id1 A3
P8 id2 B4

etc

and then join these queries. Something like this:

SELECT q1.pk,q1.id,q1.v1,q2.v2,q3.v3,q4.v4,q5.v5 FROM (SELECT pk, id, value FROM table WHERE id=1) AS q1, (SELECT pk, id, value FROM table WHERE id=2) AS q2, (SELECT pk, id, value FROM table WHERE id=3) AS q3, (SELECT pk, id, value FROM table WHERE id=4) AS q4, (SELECT pk, id, value FROM table WHERE id=5) AS q5 WHERE q1.id=q2.id and q2.id=q3.id and q3.id=q4.id and q4.id=q5.id;

But as I mentioned above number of joins are much more (~30) and it runs really slow on Postgres. I examined the planning and disabled nestloop and it got much faster but it's still too slow. If I fetch all these queries to memory and join them programmatically (using python for example) it runs in ~1 second. But Postgres takes too long (~30 seconds for joining 110 rows).
Any ideas? I think there must be a better way than making 30 subqueries and joining them. Another approach would be grouping rows based on id column and do some magic (for example with RowNumber()) to generate the desired table.

P.S. It's not applicable to pre-generate the result table. Everything must happen runtime.

For your example, all you want is

SELECT
  min(pk),
  id,
  array_agg(value ORDER BY value)
FROM foo
GROUP BY id;

 min  | id  |    array_agg
------+-----+------------------
 pk1  | id1 | {A1,A2,A3,A4,A5}
 pk10 | id2 | {B1,B2,B3,B4,B5}
(2 rows)

If you need the array_agg to be unwrapped, you can do,

SELECT pk, id, a[1] AS v1, a[2] AS v2, a[3] AS v3, a[4] AS v4, a[5] AS v5
FROM (
  SELECT
    min(pk),
    id,
    array_agg(value ORDER BY value)
  FROM foo
  GROUP BY id
) AS t(pk, id, a);

  pk  | id  | v1 | v2 | v3 | v4 | v5
------+-----+----+----+----+----+----
 pk1  | id1 | A1 | A2 | A3 | A4 | A5
 pk10 | id2 | B1 | B2 | B3 | B4 | B5

Note, in all future examples please don't use id1 and pk1. You're sample values should preferably be just 1 and 2. As you can see here, we're struggling with getting pk10 instead of pk6 and that's a problem only because of the sort order.


Does auto increment on Primary Key prevent concurent inserts? I have a Sql Server logging table, that continuously is being inserted into, I want to revise the design of this table.

Currently, we are using a GUID as the PK,  because the software can generate this and we just insert it, and hopefully, multiple inserts won't block each other. However, UniqueId is not a very efficient PK for a table and our indexes are very large and fragmented.

If I change this column to a long with auto-increment enabled.  Will this become a concurrent insert bottleneck?

I do not need my primary key to be in any sort of order, Just a unique id for that row?

If I change this column to a long with auto-increment enabled. Will this become a concurrent insert bottleneck?

The answer is NO because the step when SQL Server gets next identity value is outside of transaction. This is why when you perform ROLLBACK identity does not decrease and you can have gaps.

Please see answer to similar question here


Easily grant access to list of users after database is created I have a couple of databases that are occasionally deleted and recreated. This happens when the schema changes.

I have a group of developers that need access to those databases. In order to grant these developers access to those databases, I can either:

Use a shared login, and grant that login access to each database using SSMS. This is bad security, but fairly quick. I don't want to do this except that it saves me a lot of time.
Use individual logins, and grant each individual login access to each database using SSMS. This is good security, but costs me time every time I have to recreate the databases. I have to select each user's login, and edit the user's User Mapping.

How can I easily grant a group of users access to a database that I've dropped and recreated?

Versions

Microsoft SQL Server Management Studio 12.0.2569.0
SqlServer 12.0.4100.1

Scope

There are about six developers.
There are two SqlServer servers.
Each server has two databases to which developers are granted access.
Databases are deleted/recreated once or twice a month.

Create an Active Directory group and add all of the necessary users to it. Grant the group access to the database, which can be done easily with CREATE USER. If you want to add the group to a role, such as db_owner/db_datareader/db_datawriter, then use ALTER ROLE.

You could also do the steps that you've been doing in the UI, but then select the option to script it out so that you don't have to lookup the syntax for CREATE USER or ALTER ROLE. I often use the UI to help me with scripts I'm building.

Once the script works as desired, save it and use it for a recreated database. You should just need to switch which database to run the script in.

Alternatively, add the group to the model database and whichever roles the group should be in so that when you create a new database the group automatically has access. See this page for more information on model. But only use model if the group needs access to all newly created databases.


Postgres lowering max_connection I have a question regarding max_connections in postgresql.conf.

In the Postgresql documentation, it says it determines the maximum number of concurrent connections to the database server. Does it literally mean users connected to it? or am I missing something?

It is the number of connections.  It is not the number of users, as one user can have many connections concurrently and each one counts against max_connections.  And you can also have many more users than connections, as long as users are not permanently connected.


Sql Server 2017 - Cannot shrink transaction log file, blocked by LOG_BACKUP status Last week my sql server 2017 instance (running on windows server 2016) ran out of space, filling the disk completely up with a 190GB transaction log file.

Luckily this was on a cloud instance, so I quickly unmounted the disk, grew it an additional 200GB or so, and then remounted and formatted the unused space.

However I still had this giant 190GB log file. Log backups weren't setup. So I took a manual backup of the transaction logs. I then ran a Disk Usage report, and thankfully it was showing that 95% of the transaction log space was unused:

However, the log file itself is still 190GB. After doing some research, people suggested that I shrink the log file, having just cleared most of the data out of it by doing a backup. So I tried shrinking it, and it processed for a few seconds, and then the shrink utility window closed, without any error. However, the log file remains at 190GB.

I queried the log_reuse_wait_desc for my database, and "LOG_BACKUP" was returned. This status apparently means that before I shrink the transaction log file, I must do a backup, however I just did a backup?

I am stuck at this point, I would like to have a smaller log transaction file (something like 10-20GB), and I would like to automatically backup the log, either regularly enough that it never exceeds 10-20GB, or automatically when the file hits the 10-20GB mark.

Does anyone know how to deal with the LOG_BACKUP status blocking my ability to shrink the file, besides simply running a backup (since I've already done that)?

Here are my dm_db_log_stats for the database:

You'll notice that my total vlf count is huge (1097), my active vls count is smaller (300), but I'm not sure what that means.

The ldf file is divided into Virtual Log Files (VLFs). The last used VLF sets the limit to how small the file can be shrunk. I.e., the file can only be shrunk from the end towards the beginning. So you apparently have a used VLF towards the end of the file. You need to get un-used VLFs at the end.

What you do is you empty the log (BACKUP LOG if not in SIMPLE mode, else the CHECKPOINT command), and then shrink. And then you do that several times. That is the key, several times. At some point in time, you will have as many un-used VLFs at the end so the file will shrink to your liking.

And then set recovery model properly (based on if you do log backups or not).

You can investigate the VLF layout using DBCC LOGINFO.


Best away to keep monthly records of loans I'm trying to make a database to keep records of the amounts payed on a loan on a monthly basis.

I already have tables for the clients, accounts, products and loans:

create table product(
    id serial not null primary key,
    name varchar(50) not null,
    internal_code int not null
);

create table client(
    id serial not null primary key,
    name varchar(100) not null,
    number bigint not null

);

create table account(
    id serial not null primary key,
    number bigint not null,
    id_cliente int references client(id) not null
);

create table loan(
    id serial not null primary key,
    id_product int references product(id) not null,
    id_account int references account(id) not null,
    date_registry date not null,
    date_due date,
    amount_debt decimal not null
);

Every mouth a new record will be inserted in the loan table with a new amount_debt and a new date_registry (the date the client had payed he loan in that month), does it make sense to have a table with just those records having loan.id as FK on that new table?

Real world financial transactions do not follow simple assumptions such as "the client will make one payment per month".

Make a TRANSACTION table that records money coming in and money going out along with the details of that transaction, such as where the money came from (which client), where the money went to (which loan), and importantly when this happened (the date).  You might want to consider adding a transaction type to provide extra information, with values like "loan", "payment", "adjustment" etc.


Query in procedure occasionally does not finish I have a question about how to debug very strange behavior (nested query getting stuck) in SQL Server 2008 R2 (Standard Edition) running a query.

Sometimes (unfortunately not always) when I run a complex procedure that does some data transformations involving:

-- Update of statistics.
EXEC sp_updatestats;

-- Run of another procedure with single complex query
-- procedure declared with recompile.
EXEC usp_query_procedure;

I run into problem that this procedure is not completing (typical time < 1s, when stuck the running time can be days, never finished yet).

If I take out the query text and run it separately it finishes in under a second, but if I run it inside the procedure it gets stuck.

When these symptoms started I didn't have either WITH RECOMPILE or sp_updatestats.
I have googled around and found that cached bad execution plan (for some input paramters) may be used and that with recompile will help (provided that statistics are updated). While these two options seem to have mitigated the problem (it occurrs less often) it sure did not go away completely. Why didnt it help in all cases? What else can cause this problem?

When the procedure is "stuck" and I attach a SQL Profiler I can see thousands of lock acuqires/releases every refresh tick. When the query runs normally there is only fraction of used locks, but thats probably obvious.

What is also very strange is that this happens only on one (Standard Edition) of four servers running the same application (mix of editions Standard / Enterprise).

How can I go about debugging this further? What events should I log in profiler to be able to see what is causing the problem? Did something similar happened to sombody else here?

Please even a hint on what to google, what to look for, what to trace can lead to a solution. I will be sure to post my findings here for future generation If I manage to solve the problem.

I wouldn't bother with using Profiler at this point. You're likely just exasperating the issue, since streaming that back through a UI is not exactly light on resources. If you want to trace, set up a server-side trace and inspect the results afterward.

But first, let's start simpler. When the procedure is running in its "stuck" state, look at the DMV sys.dm_exec_requests. You want to see what it is waiting on (wait_type, last_wait_type) and whether it is being blocked by another session (blocking_session_id). You can also see if certain SET settings (quoted_identifier, arithabort, etc.) are different for the session than they are for the plan (which you can get in a roundabout way from sys.dm_exec_plan_attributes) and for the stored procedure itself - some of which you can get using OBJECTPROPERTY().

For example, let's compare just a few settings for the procedure, the session and the plan. First, while the query is running forever (and assuming you know the spid; I'll assume here it is 67), check these values from sys.dm_exec_requests and sys.dm_exec_sessions:

SELECT [status], wait_type, last_wait_type, plan_handle, blocking_session_id
  FROM sys.dm_exec_requests
  WHERE session_id = 67;

SELECT [quoted_identifier], [arithabort], [ansi_nulls], [date_format]
  FROM sys.dm_exec_sessions
  WHERE session_id = 67;

Now, this might immediately tell you where to look for the problem : you may have a wait_type that indicates there is an I/O problem, or you might see that the session is being blocked - in which case you can investigate what that session (blocking_session_id) is doing.

If neither of those pieces of information are useful, let's move on to the plan. A common issue with plans is that they can be different depending on various SET options, and this can result in quite different performance for two different users or an application and SSMS.

The plan handle is going to be a big 0x00... varbinary value in the results above. You can copy and paste that into this query:

SELECT
  [quoted_identifier] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        64 & CONVERT(INT, a.value) END = 64 THEN 1 ELSE 0 END),
  [arithabort] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        4096 & CONVERT(INT, a.value) END = 4096 THEN 1 ELSE 0 END),
  [ansi_nulls] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        32 & CONVERT(INT, a.value) END = 32 THEN 1 ELSE 0 END),
  [date_format] = MAX(CASE WHEN attribute = 'date_format' THEN
        CONVERT(INT, a.value) END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a

WHERE p.plan_handle = 0x... --<-- *** put the plan handle here ***

AND a.attribute IN ('set_options', 'date_format');

And you can compare those results with the session values you pulled above, and also some of these properties are stored with the procedure itself:

SELECT OBJECTPROPERTY(OBJECT_ID('dbo.usp_query_procedure'), 'IsQuotedIdentOn');
SELECT OBJECTPROPERTY(OBJECT_ID('dbo.usp_query_procedure'), 'ExecIsQuotedIdentOn');

You can also repeat that query for any other plans you find for the same procedure, to see if there are in fact cases where the procedure is executed with different settings:

SELECT
  p.plan_handle,
  [quoted_identifier] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        64 & CONVERT(INT, a.value) END = 64 THEN 1 ELSE 0 END),
  [arithabort] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        4096 & CONVERT(INT, a.value) END = 4096 THEN 1 ELSE 0 END),
  [ansi_nulls] = MAX(CASE WHEN
      CASE WHEN attribute = 'set_options' THEN
        32 & CONVERT(INT, a.value) END = 32 THEN 1 ELSE 0 END),
  [date_format] = MAX(CASE WHEN attribute = 'date_format' THEN
        CONVERT(INT, a.value) END)
FROM sys.dm_exec_cached_plans AS p
CROSS APPLY sys.dm_exec_plan_attributes(p.plan_handle) AS a
INNER JOIN sys.dm_exec_procedure_stats AS s
ON p.plan_handle = s.plan_handle
AND s.[object_id] = OBJECT_ID('dbo.usp_query_procedure')
AND a.attribute IN ('set_options', 'date_format')
GROUP BY p.plan_handle;

If you see more than one result, then check every column. Any columns where one plan shows a 1 and the other plan shows a 0 would be suspicious to me.

In the meantime you can read a few great posts about this and similar issues:

http://www.sommarskog.se/query-plan-mysteries.html

http://www.simple-talk.com/sql/t-sql-programming/dmvs-for-query-plan-metadata/

http://www.benjaminnevarez.com/2011/09/parameter-sniffing-and-plan-reuse-affecting-set-options/

And yes, sp_WhoIsActive will probably give you some insight into what is going on as well.


Pg install: "The database cluster initialisation failed" (Note: This question has been compeltely rewritten from its original form, so it reflects the actual problem and will help others find a solution to the same issue).

I still haven't been able to successfully install PostgreSQL on my Windows 7 Ultimate x64 computer. See this prior question for some history.

I did a fresh instillation of 9.2 and the only error I got was this one:

It reads:

  "Problem running post-install step. Installation may not complete
  correctly. The database cluster initialisation failed".

This occurs even after I do a complete manual uninstall of PostgreSQL and pgAmdin-III:

Ran the PostgreSQL uninstaller, which failed with the error Error stopping service postgresql-x64-9.2.
Removed all the PostgreSQL-related registry entries for PostgreSQL under HKEY_LOCAL_MACHINE\SOFTWARE and HKEY_CURRENT_USER\SOFTWARE
Removed the postgres user account using net user postgres /delete in an Administrator command prompt (shift-right clicked on "Command Prompt" in start menu, chose "Run as administrator")
Removed the postgres user profile
Deleted the C:\Program Files\PostgreSQL\ directory, including the data directory within %appdata%
Removed some PgAdmin-III registry entries that pointed to PostgreSQL
Removed the service account for postgres from services.msc

When I reboot and try the installer again it fails with the same message.

The install-postgresql.log file from %TEMP% contains:

Executing cscript //NoLogo "C:\Program Files\PostgreSQL\9.2/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Program Files\PostgreSQL\9.2" "C:\Program Files\PostgreSQL\9.2\data" 5432 "DEFAULT"
Script exit code: 1

then another error a bit later:

creating template1 database in C:/Program Files/PostgreSQL/9.2/data/base/1 ... initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See full installer log file download here.

It appears that the root cause of this issue may have been that @Celeritas's computer had an incorrect value for the COMSPEC environment variable. It had a trailing semicolon, so instead of the normal:

C:\Windows\system32\cmd.exe

it was:

C:\Windows\system32\cmd.exe;

This one-character difference is enough. The above isn't a valid command prompt path, so popen() calls were failing. Unfortunately, instead of something useful like:

'"C:\Windows\system32\cmd.exe;"' is not recognized as an internal or external command, operable program or batch file

it instead reports the delightfully useful error No error:

initdb: could not execute command ""C:/Program Files/PostgreSQL/9.2/bin/postgres.exe" --boot -x1 -F ": No error

See related:

http://forums.enterprisedb.com/posts/list/2125.page
http://postgresql.1045698.n5.nabble.com/initdb-failure-td2083455.html

I've reported this to the installer team and written a wiki entry to describe it. See blog post.

The issue was eventually resolved by:

Editing the COMSPEC environment variable
Uninstalling PostgreSQL
Rebooting
Reinstalling PostgreSQL

though there was a lot more before that which shouldn't actually be necessary to resolve this, including doing a total manual clean uninstall of PostgreSQL by hand.

Now I just want to find the people who wrote the program that modified this environment variable.


mysql binlog file size stop growing I made a master(A)-slave(B)-slave(C) mode MySQL instances for backup. But today when I see whether the mode works fine, I find A and B are OK. That is B can get binlog from A, but B does not write updates to its binlog. So C cannot sync with B.

After some investigation, I see that B's new binlog file is 120 bytes and there are not any errors in MySQL's log. The sql_log_bin is ON. So I don't know what can be the reason that causes the problem. What I can supply as an addition is that the new binlog's create time is when I made a mysqldump on B. So I guess there may be some relationship between the mysqldump event and the binlog write event? Hopefully someone can help me out, thanks a lot.

B's master status:

mysql> show master status;
+-----------------+----------+--------------+------------------+-------------------+
| File            | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-----------------+----------+--------------+------------------+-------------------+
| mysqlbin.000016 |      120 |              |                  |                   |
+-----------------+----------+--------------+------------------+-------------------+

B's binlog file size status:

ll /data/mysql/
-rw-rw----. 1 mysql mysql        166 7月   7 02:00 mysqlbin.000015
-rw-rw----. 1 mysql mysql        120 7月   7 02:00 mysqlbin.000016
-rw-rw----. 1 mysql mysql        288 7月   7 02:00 mysqlbin.index

B's mysqldump SQL:

mysqldump --master-data=2 --all-databases --flush-logs --user=xxx  --socket=/tmp/mysqld.sock -A > /tmp/backup.sql

You have chain replication as

Master--> SLAVE A--> SLAVE B (Slave A is Slave of Master and Slave B is slave of Slave A)

As you told On Slave Server(A) binary log is enabled.

  You additionally need to enable log_slave_updates because a slave
  does not log to its own binary log any updates that are received from
  a master server. This option tells the slave to log the updates
  performed by its SQL thread to its own binary log.

So you need to enable log_slave_updates on Slave A.

Please have a look at Improving Replication Performance.


How to remove references to performance_schema.user_variables_by_thread in mysql 5.6? I have a Jira 6.4.7 instance running on MySQL 5.6.44. I'm in the progress of updating the Jira instance to 7.0.5, which still supports MySQL 5.6.
I accidentally hooked up a newer Jira 7.0.5 to the same database, wich inserted performance_schema tables. Jira 7.0.5 probably thought it was running with >= mysql 5.7.
I immediately stopped the Jira 7.0.5 instance, but the references to performance_schema are still there.
When I look at the database with MySQL Workbench, I get this error:
Error Code: 1146 Table 'performance_schema.user_variables_by_thread' doesn't exist

Everything still seems to work with jira 6.4.7, but I can't dump the database anymore to move it somewhere else.
I've tried using the xml export and import from jira, but this copies the error from the database.
I've tried
[mysqld]
performance_schema=OFF

but the error persists.
I couldn't find the performance_schema table in other MySQL databases, because then I could have copied the table.
So how can I fix this? Can anyone provide an SQL statement of how this table should look like? Or how can I find and delete the references to performance_schema in my current MySQL 5.6 database?.

performance_schema.user_variables_by_thread is in MySQL-5.7 but not MySQL-5.6. It cannot be copied as its generated by the server to represent the internal state.
MySQL-5.6 seems to have deprecated Jira support per https://confluence.atlassian.com/adminjiraserver/supported-platforms-938846830.html
MySQL-5.7 reference on user_variables_by_thread


redesign database around selecting rows by time range I currently have a MySQL database that grows about 50,000 rows a day. When querying data out of the database, I query information from the info table and use a subquery on the session table to refine by date range like:

SELECT info.sessionID, info.otherstuff, t.time FROM info
INNER JOIN
(SELECT session.sessionID, session.time FROM session
WHERE session.time > :afterSomeDate AND session.time < :beforeSomeOtherDate) as t
ORDER BY t.time DESC

This causes an exponentially large bottleneck in query time as the date range increases. As I am completely redesigning the database, any suggestions are valid including different DB types. There is currently a 3 month RANGE partition on the time column, as well as an INDEX.

During my research I have done a few tests directly in the MySQL database. For instance a query to select every session in my table in a date range of 2 months, has a duration time of .453s and fetch time of 81s (returned 1.7 million rows). According to the accepted answer of this question, my bottleneck could really be the network connection between database and the application.

What else could I do to get a significant performance increase?

Look into PostgresQL with Timescale Extension:

https://docs.timescale.com/v1.1/using-timescaledb


i cannot install mysql plugin keyring_file.dll I have been trying to install the keyring_file.dll of MySQL 5.7.26 plugin from WAMP 3.1.9 without success. It keeps me telling in the CMD Prompt:

  ERROR 1123 (HY000): Can't initialize function 'keyring_file'; Plugin initialization function failed.

...and the MySQL log states:

  [ERROR] Plugin keyring_file reported: 'File 'C:/Program Files/MySQL/MySQL Server 5.7/keyring\keyring' not found (Errcode: 2 - No such file or directory)'

Could somebody help me figure out what the root cause is?

The reason for that error is that mysql can't locate the keyring_file.dll file.

To fix that do the ff:

Edit the my.ini file located in C:\ProgramData\MySQL\MySQL Server 5.7\ and add the ff in the [mysqld] block:

early-plugin-load=keyring_file.dll
keyring_file_data=C:/Program Files/MySQL/MySQL Server 5.7/lib/plugin/keyring_file

Restart the MySQL service:

net stop MySQL57
net start MySQL57

Validate that the keyring plugin is now active:

SELECT PLUGIN_NAME, PLUGIN_STATUS FROM INFORMATION_SCHEMA.PLUGINS WHERE PLUGIN_NAME LIKE 'keyring%';


Query slow - not using relevent indexes The table has around 7 million rows.
The following query takes around 7 seconds and it seems to use none of the related indexes:

select
lead_id as id ,
user_id ,
cmol_status_id ,
crm_status_id ,
product_status_id ,
callmeback ,
followup_date ,
lead_attempt_id ,
allocated_to ,
new_id ,
leadpriority
  from gen_que
 where lead_attempt_id > '1'
   and followup_date <= curdate()
   and lead_type in ('User', 'LenderOffer')
   and crm_status_id in ('147', '180', '181', '182')
   and product_family_id in ('1', '2', '21', '23', '19')
   and product_status_cat in ('01', '02', '05', '06', '07','021', '022', '023')
   and new_id in ('11', '12', '13', '14', '15', '16', '17', '18', '19', '20', '21', '22', '23', '24', '25', '29', '30', '31', '32', '33', '35', '36', '37', '39', '40', '41', '42')
order by lead_id desc limit 5;

Explain extended result:

**id select_type table       type
  1  SIMPLE      gen_queview index

possible_keys
igen_queview_attemptallocated, igen_queview_followup_date, igen_queview_newcmol_id,
igen_queview_crmstatus_id, igen_queview_prodstatuscat, igen_queview_prodfamiliyid

key     key_len ref rows filtered Extra
PRIMARY 8       \N  1023 2921.7   Using where**

Instead of checking for multiple values in 'In' clause, if I use one value, then the result is in .03 secs.

Can anyone help out? Thanks in advance.

According to my knowledge of indexes (i haven't work with MySql lately), first: whatever field you have in your LIST of SELECT should also be indexed.

But i noticed another probable behavior that could be happening in your given query: in your INs you are looking for strings ('1', '2', 'etc'), are those fields Varchars, Chars?.

If not, try to use INT instead of STRING, if your "product_family_id" is INT and you are doing "product_family_id IN ('1','2') the engine executes an implicit conversion, resulting in more executon time.

If "product_family_id" is string and you are storing numbers as text. and if you only have numbers stored in there (as string though) then try to cast to int

cast(product_family_id as int) IN (1,2,3,4,5)

Try both solutions. Add an Index to the select list members, use numbers instead of text. and try casting as well.


Do I need SQS queues to store remote data in the Amazon Web Services (AWS) cloud? My first question is, do I need SQS queues to receive my remote data, or can it go directly into an Amazon cloud storage solution like S3 or EC2?

Currently, my company uses a third-party vendor to gather and report on our remote data. By remote data, I mean data coming from our machines out in the wilderness. These data are uploaded a few times each day to Amazon Web Services SQS queues (setup by the third party vendor), and then the third-party vendor polls the data from the queues, removing it and saving it in their own on-premises databases for one year only. This company only provides reporting services to us, so they don't need to store the data long-term.

Going forward, we want to own the data and store it permanently in Amazon Web Services (AWS). Then we want to use machine learning to monitor the data and report any potential problems with the machines.

To repeat my first question, do we need SQS queues to receive this data, or can it go directly into an Amazon cloud storage solution like S3 or EC2?

My second question is, can an SQS queue send data to two different places? That is, can the queue send the data to the third party vendor, and also to an Amazon Web Services database?

I am an analyst/data scientist, so I know how to use the data once it's in a database. I just don't know the best way of getting it into a database.

No SQS is not required. SQS is a publicly available service that only requires an authentication token in the request header.  You can grant that using an IAM user account with security keys or if the calling service is another AWS account you can grant access by IAM roles.  S3 is also a public service that works similar to SQS with regards to access. The process for setting up cross AWS account access is a bit different but the concept is the same.  You also can send data directly to an EC2 instance by either assigning it a public IP address in a public subnet or by using an Elastic Load Balancer (ELB).

SQS is a queue service that only holds onto the data for another service to pull down. However if you are looking to have a service broadcast a dataset to multiple destinations you are looking for SNS. SNS will allow you to send to multiple destinations and each destination can have its own type of delivery. For example if you posted a message to SNS you could have it deliver that message to a lambda function in AWS and a web endpoint that accepts JSON.

The only caveat to all of this is that SNS takes the message it receives and sends it on to the destinations without any transformation so each destination will receive the exact same message and be responsible for extracting the data.


Move data with schema change between MariaDB servers I am tasked with the challenge to move data between two MariaDB servers. The source database is a MariaDB 5.x, and the destination database is a MariaDB 10.x. The problem is that I need to add a column to the target.

We are splitting a monolith service into microservices. The new database needs an additional column (found in a separate table in the source database).

Is the below the way to go or are there any nicer way of moving the data?

SELECT CONCAT("insert into table4(id,level,name,levelt2) VALUES(",   quote(table1.id),   ",",    quote(table1.level),   ",",    quote(table2.name),   ",",    quote(table2.level),    ");") as q
       from table1 join table2 on table1.id = table2.table1_id
       join table3 on table3.id = table2.table3_id
       where table3.name in ('fee', 'fi', 'fo', 'fum');

cat query.sql|mysql --skip-column-names --raw > table4.sql

Write a SELECT statement that JOIN the two tables together.  Specify exactly the target columns desired (most coming from one table, one from the other table).
Then do

Something like

INSERT INTO new (a,b,c,d)
    SELECT x.a, x.b, x.c, y.d
        FROM x
        JOIN y ON ...

For reading from one server and writing to another, see FEDERATEDx.


Extract a table from a MySQL database using mysql/databasename folder I am not an SQL expert, not even a novice so please excuse me if this question is naive. I have a copy of an old Linode drive where my Joomla website was hosted. I think that the Joomla database is located in "/var/lib/mysql/sitename".

I need to extract a specific table from this database but the database is not online anymore and I can not boot up from that drive anymore. What is the procedure to extract the info that I need from that database? (I know the table name from the Joomla documentation).

If you see [tablename].MYD and [tablename].MYI files it's MYISAM. If you see [tablename].ibd or only the .frm file then it's InnoDB. Based on that the process:

MyISAM

You can simply copy the .MYI, .MYD and .frm files to any existing MySQL instance's database directory.

For example:

You have your database in /var/lib/mysql/sitename then you find the table which will be /var/lib/mysql/sitename/[dbname]/[tablename].* (.MYD, .MYI and .frm). You can copy only these three files to an arbitrary database on a newly installed MySQL like /var/lib/mysql/sitename/[restored_db]/.

InnoDB

It's possible to import tablespace since 5.6 but if you can it's much simpler to copy the whole directoy. It's a common procedure to clone new replication slaves.

In your case you copy the whole /var/lib/mysql/sitename directory onto the new server and start MySQL over it. Make sure the innodb_log_file_size and innodb_log_files_in_group matches the size and number of what you have now. These are the files called ib_logfile0 and ib_logfile1.

When you have the server running you can use mysqldump or any other method you prefer.


Passing info on who deleted record onto a Delete trigger In setting up an audit trail I have no problem tracking who is updating or inserting records in a table, however, tracking who deletes records seems more problematic.

I can track Inserts/Updates by including in the Insert/Update the field "UpdatedBy". This allows the INSERT/UPDATE trigger to have access to the field "UpdatedBy" via inserted.UpdatedBy. However, with the Delete trigger no data is inserted/updated. Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Here is an Insert/Update trigger

ALTER TRIGGER [dbo].[trg_MyTable_InsertUpdate]
ON [dbo].[MyTable]
FOR INSERT, UPDATE
AS

INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges)
VALUES (inserted.ID, inserted.LastUpdatedBy)
FROM inserted

Using SQL Server 2012

Is there a way to pass information onto the Delete trigger such that it could know who deleted the record?

Yes: by using a very cool (and under utilized feature) called CONTEXT_INFO. It is essentially session memory that exists in all scopes and is not bound by transactions. It can be used to pass info (any info--well, any that fits into the limited space) to triggers as well as back and forth between sub-proc / EXEC calls. And I have used it before for this exact same situation.

Context info is a VARBINARY(128)
Set via: SET CONTEXT_INFO
Get via: CONTEXT_INFO()

Test with the following to see how it works. Notice that I am converting to CHAR(128) before the CONVERT(VARBINARY(128), ...  This is to force blank-padding to make it easier to convert back to VARCHAR when getting it out of CONTEXT_INFO() since VARBINARY(128) is right-padded with 0x00s.

SELECT CONTEXT_INFO();
-- Initially = NULL

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), 'I deleted ALL your records! HA HA!')
                          );
SET CONTEXT_INFO @EncodedUser;

SELECT CONTEXT_INFO() AS [RawContextInfo],
       RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())) AS [DecodedUser];

Results:

0x492064656C6574656420414C4C20796F7572207265636F7264732120484120484121202020202020...
I deleted ALL your records! HA HA!

PUTTING IT ALL TOGETHER:

The app should call a "Delete" stored procedure that passes in the UserName (or whatever) that is deleting the record. I assume this is already the model being used since it sounds like you are already tracking Insert and Update operations.
The "Delete" stored procedure does:

DECLARE @EncodedUser VARBINARY(128);
SET @EncodedUser = CONVERT(VARBINARY(128),
                            CONVERT(CHAR(128), @UserName)
                          );
SET CONTEXT_INFO @EncodedUser;

-- DELETE STUFF HERE

The audit trigger does:

-- Set the INT value in LEFT (currently 50) to the max size of [UserWhoMadeChanges]
INSERT INTO AuditTable (IdOfRecordedAffected, UserWhoMadeChanges)
   SELECT del.ID, COALESCE(
                     LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50),
                     '<unknown>')
   FROM DELETED del;

Please note that, as @SeanGallardy pointed out in a comment, due to other procedures and/or ad hoc queries deleting records from this table, it is possible that either:

CONTEXT_INFO has not been set and is still NULL:

For this reason I have updated the above INSERT INTO AuditTable to use a COALESCE to default the value. Or, if you don't want a default and require a name, then you could do something similar to:

DECLARE @UserName VARCHAR(50); -- set to the size of AuditTable.[UserWhoMadeChanges]
SET @UserName = LEFT(RTRIM(CONVERT(VARCHAR(128), CONTEXT_INFO())), 50);

IF (@UserName IS NULL)
BEGIN
   ROLLBACK TRAN; -- cancel the DELETE operation
   RAISERROR('Please set UserName via "SET CONTEXT_INFO.." and try again.', 16 ,1);
END;

-- use @UserName in the INSERT...SELECT

CONTEXT_INFO has been set to a value that is not a valid UserName, and hence might exceed the size of the AuditTable.[UserWhoMadeChanges] field:

For this reason I added a LEFT function to ensure that whatever is grabbed out of CONTEXT_INFO will not break the INSERT. As noted in the code, you just need to set the 50 to the actual size of the UserWhoMadeChanges field.

UPDATE FOR SQL SERVER 2016 AND NEWER

SQL Server 2016 added an improved version of this per-session memory: Session Context. The new Session Context is essentially a hash table of Key-Value pairs with the "Key" being of type sysname (i.e. NVARCHAR(128) ) and the "Value" being SQL_VARIANT. Meaning:

There is now a separation of values so less likely to conflict with other uses
You can store various types, no longer needing to worry about the odd behavior when getting the value back out via CONTEXT_INFO() (for details, please see my post: Why Doesn’t CONTEXT_INFO() Return the Exact Value Set by SET CONTEXT_INFO?)
You get a lot more space: 8000 bytes max per "Value", up to 256kb total across all keys (compared to the 128 bytes max of CONTEXT_INFO)

For details, please see the following documentation pages:

sp_set_session_context
SESSION_CONTEXT


Can I rely on the estimate execution plan to recommend indexes? I have a database on Microsoft SQL Server 2016, with a table of about one million rows, let's call it books. This query takes more than one minute, which is not acceptable:

select * from books where publisher_id in (857413, 857317, 857316)

There is a proper FK from publisher_id to the publishers table.
I display the Estimated Execution Plan, and it tells me that 100% of the cost is in the "Clustered Index Scan (clustered)" on the primary key of the books table.
And what worries me, it does not recommend an index at all.
By looking at the query, it seems obvious that an index will help. And in fact, when I create the index the query returns results instantly.
Did something go corrupt in my database, maybe statistics? Or do you believe I should nor, in general, trust what I read in the estimated execution plan?

There's a limitation where it will not give you missing indexes when the query plan is trivial, perhaps that's what you are encountering.

Right click on the SELECT operator and select Properties. Check if the Optimization Level is TRIVIAL.

I use the estimated execution plan a great deal when performance troubleshooting. It's rare that I'll have the actual plan. Regarding missing indexes, I don't just add what the plan thinks is missing. I test the index, variations of it, etc.


Compute Leap Seconds I use PostgreSQL 11 and have two columns. One is unix timestamp which is not aware of leap seconds and an utc timestamp which is aware of leap seconds.

Is there a way either to find the number of leap seconds between an unix timestamp (without leap Seconds) and an utc timestamp (with leap seconds)?

Alternatively is there a way to convert an utc timestamp (with leap seconds) into an unix timestamp (without leap seconds)?

I think „UTC Timestamp“ is not the correct term since UTC is defined in terms of Leap Seconds.

What is a more usual term here is TAI time or GPS time, both use no leap seconds and UTC is defined in terms of TAI (GPS is basically TAI starting at a different offset, so it has 19s less leap time).

The difference of UTC time to TAI time cannot be calculated (unless you have both stamps, then you just need to subtract them). Instead, tTo get the official offset at any point in the past and some time in the Future you need to lookup the official tables, this is the authoritative source: http://hpiers.obspm.fr/eop-pc/index.php

I think some timezone Information files/libraries also have that information, but have not seen any support in commercial databases.


Oracle: Database writers idle until 'Checkpoint not complete' even with multiple redo logs I work on an application with a steady data income of 400 MB/hr. For that purpose we have setup 10 redo log groups with a size 100MB each. Therefore, we roll over a log approx. every 15min.

Because of limited disk space, we have a retention job which deletes 'old' data after a certain time. When this job is run it is doing a delete cascade based on timestamps. So far so good...

When the data removal process is running the redo log interval shortens to 15s and after 9 log rollovers we see the infamous 'Checkpoint not completed' message.

We oberserved with top and iotop how the log writer (ora_lgwr_XXXX) and database writers (ora_dbwX_XXXX) behave and found that the database writers are completely idle until the point of 'Checkpoint not complete'. The logwriter writes with MB/s and the database writers idle. Regarding the documentation the database writers start on created checkpoints and these are created after every redo log rollover.

So, why are the database writers not working until the redo logs are all full?

Shouldn't I see some significant I/O for the database writers after the first checkpoint is completed with the first redo log rollover?

We see some accumalted kBs for the two datbase writers before the 'Checkpoint not completed' message with at least 9 redo log rollovers. Afterwards, the database writers store the data with a rate of MB/s to database.

Can the database writer processes made more aggressive? Idealy depending on the amount of data pending in the redo logs? I do not need to have every byte integrated into database files immeditially, but when a lot of data is pending in the database buffer cache (and the redo logs are still active) and a lot of data is coming in, there should be a mechanism to drive the database writers to a higher performance?

Metadata:

8 vCPUs
56 GB RAM
Oracle 11.2.0.3
log_checkpoint_interval=0
log_checkpoint_timeout=1800
db_writer_processes=1
10 redo log groups, 100MB each
1 1TB parition via iSCSI for all data (oradata)

I guess you could play with the fast_start_io_target and log_checkpoint_timeout initialization parameters to entice database writers to write out dirty blocks more aggressively. Unless your redo logs are on a different I/O path from your data files, you are unlikely to see any benefits from this though, because both log and database writers will compete for the same I/O bandwidth. A better choice, as others have mentioned, would be to increase the size of your redo logs to accommodate long-running transactions.


SQL Server Configuration Manager 64-bit Nodes Missing on 64-bit WIndows 10 This problem appears to have been written about quite a lot, and answers posted here too, but I have tried all the suggestions and some of my own to no avail, so please bear with me.

I have Windows 10 V1607, 64-bit. I've used SSMS for a long time, and keep up-to-date with the latest versions, especially now that it is shipped independently, and free, from SQL Server.

When I switched from Windows 7 to Windows 10 on this machine, I installed SSMS V16, and noticed then that there were no 64-bit nodes in SSCM. I didn't think too much of it at the time, and set about creating my 32-bit aliases.

Recently, I changed an old application to 64-bit, compiled and ran it in Visual Studio (2017), and tried to connect to a database. It failed.

I thought it might be that I have not set up any 64-bit aliases, so returned to SSCM to add them. But no 64-bit nodes.

I have seen various suggestions about copying files from a working copy, so I tried that. No difference.

I spotted the latest SSMS V17.1 had just been released, so installed that. It too is missing the 64-bit nodes.

I have tried repairing, reinstalling, turning it off and on again, but still they fail to appear.

Please, does anyone have an insight into why this happens, but more to the point what to do to fix it?

I have SQL Server installed on another machine. I want my application to connect to a database on that machine, remotely. To make it easier to manage the remote database I have SSMS installed locally; in that way, I don't have to use RDP to connect to the server. When I run my application and it connects to the remote database, I want to use an alias, such as 'MyRemoteDatabase', instead of using the IP address and port number directly. In the past, I have run SSCM to create the alias, but my 64-bit applications don't see my 32-bit aliases, which is why I want the 64-bit nodes.

Right click the Config Mgr shortcut in the Start Menu, open the file location, change the shortcut Target to:

C:\Windows\SysWOW64\mmc.exe /32 C:\WINDOWS\SysWOW64\SQLServerManager13.msc

The *.*14.msc was my issue.