SQLite writing a query where you select only rows nearest to the hour I've got a set of data where data has been taken approximately every minute for about three months and the time has been stored as a UNIX timestamp. There is no regularity to the timestamp (i.e. the zero minute of the hour may not contain a reading, 00:59:55 and the next measurement could be 01:01:01) and days may be missing.

What I need is the row nearest to the hour, with the time-step rounding to the hour, as long as the nearest value is not more than 30 minutes away from the hour.

Where a matching hour could not be found it would be helpful if the query could include a time but no value.

I realize I am asking a lot, but this would be incredibly helpful.

By the way, the table is just PK(autoincrement), timestamp, value, sensor id(FK). I have tried this to get the data out:

SELECT strftime('%S', time, 'unixepoch'),
       strftime('%M', time, 'unixepoch'),
       strftime('%H', time, 'unixepoch'),
       strftime('%d', time, 'unixepoch'),
       strftime('%m', time, 'unixepoch'),
       strftime('%Y', time, 'unixepoch'),
       value
FROM Timestream
WHERE idSensor = 359;

Assuming that you want a SQL-only solution and your table is defined as:

CREATE TABLE measurements (
  pk INTEGER PRIMARY KEY,
  timestamp INTEGER NOT NULL,
  value TEXT NOT NULL,
  sensor_id INTEGER);

then create the following views:

CREATE VIEW vw_measurements AS
SELECT pk, timestamp, value, sensor_id,
       3600*ROUND((timestamp+1800)/3600) AS hour,
       ABS(timestamp - 3600*ROUND((timestamp+1800)/3600)) AS distance
FROM measurements;
CREATE VIEW min_distances AS
SELECT hour, MIN(distance) AS min_distance
FROM vw_measurements
GROUP BY hour;

Then you can get the records closest to every hour as:

SELECT m.hour, GROUP_CONCAT(m.timestamp), GROUP_CONCAT(m.value)
FROM vw_measurements m, min_distances d
WHERE m.hour = d.hour AND m.distance = d.min_distance
GROUP BY m.hour;

WARNING: SQLite SQL lacks many useful constructs that other SQL variants have, so the solution above can't discriminate between records with timestamps equidistant to their closest hour (either same timestamps or hour±samedelta); ergo the GROUP_CONCAT aggregate. Typically each row will have a single timestamp and value.

Note that if you have many thousands of records, the performance can be abysmal (tested on SQLite 3.7.9), and it might be better if min_distances becomes a temporary table.

Also: this solution does not fetch NULL values for hours without any related records (plus/minus 30 minutes).


Sharing keys and certificates between SQL Server 2014 server for encrypted backup and restoration I have six SQL Server 2014 servers.  I am not using TDE, Transparent Data Encryption, on any server.

I would like to be able to take an encrypted backup of a database on ANY server and restore that backup to ANY OTHER server.

What actions do I need to take on each server with the service master key, master database key,  and certificate? I would like to use the minimum number of keys, certificates, and backup files for these keys and certificates.

What actions do I need to take if I add an additional SQL Server 2014 to the group?

Thank you all.  Server Fault and Stack Overflow have helped me many times in the past.

These links are a good place to start

https://www.mssqltips.com/sqlservertip/3572/recovering-a-sql-server-tde-encrypted-database-successfully/

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

To summarize:
On EACH Sql Server Instance on which you want to enable TDE:

1).  In "master', create the database master key to encrypt the certificate that will be used for TDE

USE [master];
GO
CREATE MASTER KEY
  ENCRYPTION BY PASSWORD = 'StrongPassw0rd!';
GO

2). Create the certificate we're going to use for TDE.  Make sure you include the EXPIRY_DATE.  The default is one year from creation date and even though TDE is technically unaffected by an expired certificate, it's still annoying to see the message 'The certificate you just installed has expired' when creating this certificate on another server.

CREATE CERTIFICATE TDECert
WITH SUBJECT = 'TDE Cert for Test',
EXPIRY_DATE = '99991231';;

3). Back up the certificate and its private key to a REALLY safe place.  Remember the password!  Losing this information will prevent you from being able to restore a TDE database,

BACKUP CERTIFICATE TDECert
  TO FILE = N'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 ENCRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'to
  );
GO

4). Switch over to the database you want to apply TDE to and create the Database Encryption Key so you'll be able to encrypt the database.

USE [DatabaseWithTDE];
GO

CREATE DATABASE ENCRYPTION KEY
  WITH ALGORITHM = AES_256
  ENCRYPTION BY SERVER CERTIFICATE TDECert;
GO

5).  Reconnect to master and turn on TDE for the user database

USE [master];
GO

ALTER DATABASE [DatabaseWithTDE]
  SET ENCRYPTION ON;
GO

Monitor the progress of encryption

SELECT db_name(database_id) AS DatabaseName
,CASE
    WHEN encryption_state = 0
        THEN 'No Database Encryption'
    WHEN encryption_state = 1
        THEN 'Unencrypted'
    WHEN encryption_state = 2
        THEN 'Encryption In Progress'
    WHEN encryption_state = 3
        THEN 'Encrypted'
    WHEN encryption_state = 4
        THEN 'Key Change In Progress'
    WHEN encryption_state = 5
        THEN 'Decryption In Progress'
    WHEN encryption_state = 6
        THEN 'Protection Changes In Progress'
    END AS EncryptionState
,percent_complete FROM sys.dm_database_encryption_keys

At this point, you have TDE on your chosen Sql Server instances and database.

For you to be able to restore databases from any other instance, you'll have to 'import' the certificates you created and 'exported' (Step 3 above) on all of the other instances.

So, let's say you're on SERVER1

Note that, when we originally created the certificates on each instance, they were all called TDECert.  When 'importing' the certificates from the OTHER instances, you'll need to give them a unique name (FromServer2, FromServer3, etc.) - although, I'd shy away from using the actual server or instance name as part of the certificate name as that could change in the future as newer servers replace older ones and the certificates get moved around.

--Create the certificate from SERVER2.
CREATE CERTIFICATE TDECert_FromServer2
--The directory where you saved the certificate for SERVER2
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER3.
CREATE CERTIFICATE TDECert_FromServer3
--The directory where you saved the certificate for SERVER3
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER4.
CREATE CERTIFICATE TDECert_FromServer4
--The directory where you saved the certificate for SERVER4
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER5.
CREATE CERTIFICATE TDECert_FromServer5
--The directory where you saved the certificate for SERVER5
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

--Create the certificate from SERVER6.
CREATE CERTIFICATE TDECert_FromServer6
--The directory where you saved the certificate for SERVER6
  FROM FILE = 'C:\SQLBackups\TDECert.cer'
  WITH PRIVATE KEY (
    FILE = N'C:\SQLBackups\TDECert_key.pvk',
 DECRYPTION BY PASSWORD = 'APrivateKeyP4ssw0rd!'
  );
GO

Go into each Sql Server instance and repeat the above 'importing' of the certificates that exist on OTHER instances.  So, for SERVER2, you'd 'import' certificates from SERVER1, SERVER3, SERVER4, SERVER5 and SERVER6

At this point, you 'should' be able to restore any TDE database from the above example to any Sql Server instance in the above example.

Also, if, during a restore, you DO get the message about 'thumbprint doesn't match', you can go into EACH Sql Server instance and SELECT * FROM sys.certificates and look for the 'thumbprint' column to figure out which certificate you're missing.


Speed up count queries on a couple million rows Assuming a db full of products. A product can belong to exactly 1 collection and is created by a user. Rough scale of the db:

Products: 52.000.000
Collections: 9.000.000
Users: roughly 9.000.000 as well

I am trying to retrieve the amount of products+collections a user has, and the amount of products inside each collection (this information is supposed to get generated all x days and indexed in ElasticSearch) .

For the user query, I am currently doing something like this:

      SELECT
        users.*,
        (SELECT
          count(*)
        FROM
          products product
        WHERE
          product.user_id = user.id
        ) AS product_count,
        (SELECT
          count(*)
        FROM
          collections collection
        WHERE
          collection.user_id = user.id
        ) AS collection_count
      FROM
        users user

all *_id fields are indexed. Using explain(analyze, verbose) (sensitive information removed):

 Limit  (cost=0.00..156500.97 rows=100 width=41) (actual time=0.064..28345.363 rows=100 loops=1)
   Output: (...), ((SubPlan 1)), ((SubPlan 2))
   ->  Seq Scan on public.users user  (cost=0.00..14549429167.11 rows=9296702 width=41) (actual time=0.064..28345.241 rows=100 loops=1)
         Output: (...), (SubPlan 1), (SubPlan 2)
         SubPlan 1
           ->  Aggregate  (cost=1415.84..1415.85 rows=1 width=0) (actual time=261.101..261.102 rows=1 loops=100)
                 Output: count(*)
                 ->  Bitmap Heap Scan on public.products product  (cost=7.32..1414.95 rows=355 width=0) (actual time=0.282..260.767 rows=382 loops=100)
                       Output: (...)
                       Recheck Cond: (product.user_id = user.id)
                       Heap Blocks: exact=32882
                       ->  Bitmap Index Scan on products_user_id_index  (cost=0.00..7.23 rows=355 width=0) (actual time=0.165..0.165 rows=382 loops=100)
                             Index Cond: (product.user_id = user.id)
         SubPlan 2
           ->  Aggregate  (cost=149.13..149.14 rows=1 width=0) (actual time=22.333..22.333 rows=1 loops=100)
                 Output: count(*)
                 ->  Index Only Scan using collections_user_id_index on public.collections collection  (cost=0.43..149.02 rows=44 width=0) (actual time=0.610..22.300 rows=28 loops=100)
                       Output: collection.user_id
                       Index Cond: (collection.user_id = user.id)
                       Heap Fetches: 2850
 Planning time: 0.214 ms
 Execution time: 28345.508 ms

When timing the read queries:

LIMIT 1: 0.695ms
LIMIT 10: 10434ms
LIMIT 100: 150471ms

As the query times become unusably slow when retrieving more than a couple of rows, I am wondering if it is possible to speed this up a bit.

If I were to beef up the DB machine, would adding more CPUs help? AFAIK postgres doesn't execute queries on multiple cores so I am not sure how much that would help.

(Also slightly related, but how come the count() for collections uses a index only scan, while products uses a bitmap heap scan instead?)

While computing numbers for all or most users, it's much more efficient to use plain subqueries to aggregate counts per user before joining instead of correlated subqueries:

SELECT u.*, p.product_count, c.collection_count
FROM   users u
LEFT   JOIN (
   SELECT user_id AS id, count(*) AS product_count
   FROM   products
   GROUP  BY 1
   ) p USING (id)
LEFT   JOIN (
   SELECT user_id  As id, count(*) AS collection_count
   FROM   collections
   GROUP  BY 1
   ) c USING (id);

The index-only scan and bitmap index we see in the EXPLAIN output only benefit queries for a small subset of rows (LIMIT 100). Your test is misleading in this respect. While computing numbers for all (or most) users, indexes won't help. Sequential scans will be faster.

The Bitmap Heap Scan you see is only the second step needed for the bitmap index scan - which comes as a surprise for just 100 rows. Either your table statistics are out of date or those 100 users have a lot of related products or rows in products are highly clustered (physically, meaning multiple rows for one user reside on the same or few data pages). Postgres only switches to a bitmap index scan if it expects to find multiple rows per data page, which comes as a surprise for 100 users and 52.000.000 products (rows=355 expected and rows=382 found).

Related:

Index usage on a temporary table
Configuring PostgreSQL for read performance


Is there any reason not to use Percona 'innodb_fast_checksum'? I recently came across a change that Percona Server implemented over normal MySQL InnoDB. The new feature is innodb_fast_checksum

  XtraDB can use a more CPU-efficient algorithm, based on 4-byte words, which can be beneficial for some workloads (for instance write-heavy workloads on servers that can perform lots of IO).

Sounds good, but I wonder if there are any workloads in which this change would perform worse than the old method.

The documentation goes on to say:

  The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

So, assuming that you enable innodb_fast_checksum and rebuild all your InnoDB tables as suggested to remove the old checksums, are there any situations that the extra check on the old checksums would cause performance degradation?

In short, is there any reason not to run innodb_fast_checksum?

The wording you pasted is very specific:

  The original algorithm is checked after the new one, so you can have data pages with old checksums and data pages with new checksums. However in this case, you may experience slow reads from pages having old checksums.

This means that it can still read the old checksum, but from now on in, only Percona Server will be able to read your data.  This is later noted here:

  Once enabled, turning it off will require table re-creation as well, since it will fail to start on data files created when innodb_fast_checksums was enabled.

You've got to put it in context - InnoDB only verifies the checksums when it reads a page from the block storage device, and updates it before flushing it back to the storage device.  While in memory, the checksum is not maintained.

For many years, an IO to a disk has taken something on the order of 5-10ms (1ms = 1/1000th of a second).  Computing a checksum probably takes somewhere around 50us (1us = 1/1000000th of a second).  I don't have the actual data what it is in InnoDB's case, but if you Google "Numbers everyone should know", you'll hopefully agree I'm correct within an order of magnitude.

Enter an era now where we have things like Fusion-io flash devices, which on paper have a ~20-30us access time, and you can see that reevaluating the checksum makes sense.

My general advice: Don't ruin your backwards compatibility with MySQL releases unless you really need it.  Most people do not need it yet.


Updating an existing SQL Database with thousands of records I am having some trouble thinking of how to update an existing table. There are currently 1500 items in it and the structure of the table is like this:
Old:

ID
Manager ID
Manager Name
Director
Regional Director
State Manager
Head Manager

22
"tom123"
"Tom Hawkins"
"Jerry Lewis"
"Mary Brauhm"
"Anna Michaelson"
"Jack Edwards"

New:

ID
Manager ID
Manager Name
Director
Regional Director
State Manager
Head Manager

22
"tom123"
"Tom Hawkins"
"Dennis Martin"
"Mary Brauhm"
"Nicholas Andrew"
"Jack Edwards"

Say a state manager has left the company and now all items need to be updated. But while we're at it, we also want to correct and update the managers who are no longer with the company.
I know that we could update where stateDirector = "Anna Michaelson", but what if not all people formerly under Anna are assigned under the New Director? We have all these details in a seperate(current) file(excel, csv).
I know the structure of the table itself could be improved (also, been trying to think of this improvement), but what is the correct approach to updating all these if multiple IDs (employees) can exist under a manager.

Plan on doing one UPDATE per change needed.  It might look like
UPDATE t SET president = 'Biden' WHERE president = 'Trump';

If someone has left the company but has not yet been replaced, that gets tricky.
Walking down the hierarchy also gets tricky.  You seem to have a lot of redundant info.  It might be better to have rows that have only
id, manager_id, employee_id, title

However, then you must walk through the "tree" of rows to get to the various levels of management.
However, however, this would let you have a different depth for different departments.  (Currently you have a very rigid number of columns.)
"Thousands of records" is not a big deal.  "Millions" begins to get interesting.  "Billions" would be challenging.  (But what company has a billion employees?)


Comparing 2 items at different dates I have a Netezza database which gets a daily feed of account information.

I’m trying to write a query which will only return accounts when they experience a price change and advise what the price change was. I.E. in the example below it would return account 100 having a price change of +10 on 2020-02-02.

Date                  Account             Price
2020-02-01       100                    200
2020-02-01       101                    200
2020-02-01       102                    200
2020-02-02       100                    210
2020-02-02       101                    200
2020-02-02       102                    200

I don't have access to a Netezza device, but according to the wiki,

  Netezza software was based on PostgreSQL 7.2,[9] but did not maintain
  compatibility.

so, I'll use the PostgreSQL option for the fiddle which you can find here. According to the documentation, Netezza has Window/Analytic functions.

What I did to answer your question was:

Create a share table:

CREATE TABLE share
(
  t_date  DATE NOT NULL, -- BAD IDEA to use SQL keywords for fieldnames!
  account INTEGER NOT NULL,
  price   INTEGER NOT NULL
);

Populate it:

INSERT INTO share
VALUES
('2020-02-01',       100,                    200),
('2020-02-01',       101,                    200),
('2020-02-01',       102,                    200),
('2020-02-02',       100,                    210),
('2020-02-02',       101,                    200),
('2020-02-02',       102,                    200);

Then I ran the following query (using Window - or Analytic - functions):

SELECT
  ROW_NUMBER() OVER() as rn,
  t_date AS d, account AS a,
  LAG(price) OVER () AS la, price, LEAD(price) OVER() AS le
FROM share;

Result:

rn  d   a   la  price   le
1   2020-02-01  100     200 200
2   2020-02-01  101 200 200 200
3   2020-02-01  102 200 200 210
4   2020-02-02  100 200 210 200
5   2020-02-02  101 210 200 200
6   2020-02-02  102 200 200

The rn field is a proxy PRIMARY KEY - I presume your table has one? OK, so far so good. But, you can't put Window functions in a where clause, so this will be our basis for the following query:

SELECT
  rn, d AS tx_date, a AS acct, la AS previous, price, le AS future,
  CASE
    WHEN le > la THEN 'Increase'
    ELSE 'Decrease'
  END AS action
FROM
(
  SELECT
    ROW_NUMBER() OVER() as rn,
    t_date AS d, account AS a,
    LAG(price) OVER () AS la, price, LEAD(price) OVER() AS le
  FROM share
) AS tab
WHERE le != la;

Result:

rn  tx_date     acct    previous    price   future  action
3   2020-02-01  102          200      200      210  Increase
5   2020-02-02  101          210      200      200  Decrease

So, this is, I think, pretty much what you wanted. Following tranaction 3, there was an increase and prior to transaction 5 there was a decrease. If you want the focus to be on transaction 4, then just play around with the SQL - it shouldn't be too difficult. Window/Analytic functions are very powerful and well worth learning! BTW, next time you ask a question, could you please provide your table structures as DDL (CREATE TABLE foo...) and your data as DML (INSERT INTO foo VALUES (...) - or use a fiddle. p.s. welcome to the forum!


MySQL: ERROR 126 (HY000): Incorrect key file for table + slow logical backups I've got '/tmp' directory mounted with 'tmpfs' and for some reason this is causing the following error:

mysql> SELECT DISTINCT table_schema FROM information_schema.tables WHERE table_schema NOT IN ('information_schema','mysql')
    -> ;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_29ef_0.MYI'; try to repair it

-

# df -h /tmp/
Filesystem            Size  Used Avail Use% Mounted on
tmpfs                 2.0G   12K  2.0G   1% /tmp
# df -i /tmp/
Filesystem            Inodes   IUsed   IFree IUse% Mounted on
tmpfs                2041621       7 2041614    1% /tmp
# mount | grep /tmp
tmpfs on /tmp type tmpfs (rw,size=2048M)

Please note that the same query works fine when '/tmp' dir is mounted with ext4 file system.

EDIT:

Server_01

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
1876765

but this also happened on server with much less tables:

Server_02

# cd /var/lib/mysql ; ls -lR | grep -c "\.frm$"
49514

I was using this query to list all databases but as it didn't work with tmpfs I've just replaced it with simpler one (SHOW DATABASES...).

I was watching disk space on /tmp mounted with tmpfs and there still was a plenty of space so I'm not sure how it could run out of space?

Basically I've got a problem with logical backups on the server with ~8000 DBs - it takes many hours (~24) to complete this task. I've created a simple BASH script (please see below) and instead of mysqldump I'm using mydumper as initial tests showed that it's much faster.

Backups running very fast initially and then slowing down dramatically:

# ./backup.sh
Backing up database number: 1
Completed in: 0.016
Backing up database number: 2
Completed in: 0.078
Backing up database number: 3
Completed in: 0.074
Backing up database number: 4
Completed in: 0.068
Backing up database number: 5
Completed in: 0.071
Backing up database number: 6
Completed in: 0.060
Backing up database number: 7
Completed in: 0.067
Backing up database number: 8
Completed in: 0.070
Backing up database number: 9
Completed in: 0.065
.....
Backing up database number: 107
Completed in: 10.749
Backing up database number: 108
Completed in: 12.125
Backing up database number: 109
Completed in: 11.313
Backing up database number: 110
Completed in: 11.572
Backing up database number: 111
Completed in: 11.371
.....

Script:

#!/usr/bin/env bash

DATA_DIR="/tmp/mysqlbackup"
LOCKFILE=/tmp/backup.lock
NOW=$(date +%Y%m%d)
COUNT=1

if [ -f "$LOCKFILE" ]; then
        echo "$(basename $0) is already running: PID $(cat $LOCKFILE)"
        exit 0

else

        echo $$ > $LOCKFILE

        if [ ! -d $DATA_DIR/$NOW ]; then
                mkdir -m 700 -p $DATA_DIR/$NOW
        fi

        while read DB; do

                (( COUNT++ ))

                echo "Backing up database number: $COUNT"

                START=$(date +%s.%N)

                mydumper -e -o $DATA_DIR/$NOW/$DB -B "$DB"

                ELAPSED=$(date +%s.%N)

                printf "Completed in: %.3F\n" $(echo $ELAPSED - $START | bc)

        done <<< "$(mysql -A -B -N -e "SHOW DATABASES" | egrep -v '(mysql|*_schema|log)')"

        echo "Removing backup dir...";

        rm -rf $DATA_DIR/$NOW

        rm -f $LOCKFILE

        exit 0

fi

ASPECT #1: BAD FILE

Something tells me you somehow ran out of space in /tmpfs.

Whenever MySQL creates a temp table

It is created as a MyISAM table
No .frm is created
Initial .MYD is 0 bytes
Initial .MYI is 1024 bytes (1K)
There are never any indexes in the .MYI

I find that strange that MySQL could not create a 1K .MYI.

Since ext3 has a default block size of 1K, while ext4 has 4K, perhaps the 4K blocksize may have allowed for more data. This, in itself, is not the real problem.

ASPECT #2: YOUR QUERY

If you have tens of thousands of tables, the query you are running looks quite demanding. In fact, the query says "Get me all databases that have 1 or more tables in them." If you have a large number of tables, you could be looking at a rather large temp table in order collect table_schema values before doing the DISTINCT.

Sometimes it is easy to forget

the SELECT DISTINCT executes a GROUP BY internally
This true in the older versions of MySQL.

There are two Stackoverflow posts that mention this

Jun 06, 2012 : How to select distinct rows without using group by statement (My Post)
Feb 24, 2009 : What's faster, SELECT DISTINCT or GROUP BY in MySQL?

ANALYSIS

My guess is a combination of anomalies

ANOMALY #1 : You have a large number of tables

ANOMALY #2 : The temp table being made must have run out of space to have a damaged .MYI

ANOMALY #3 : The query does not use an index.

Look at the table

mysql> show create table information_schema.tables\G
*************************** 1. row ***************************
       Table: TABLES
Create Table: CREATE TEMPORARY TABLE `TABLES` (
  `TABLE_CATALOG` varchar(512) NOT NULL DEFAULT '',
  `TABLE_SCHEMA` varchar(64) NOT NULL DEFAULT '',
  `TABLE_NAME` varchar(64) NOT NULL DEFAULT '',
  `TABLE_TYPE` varchar(64) NOT NULL DEFAULT '',
  `ENGINE` varchar(64) DEFAULT NULL,
  `VERSION` bigint(21) unsigned DEFAULT NULL,
  `ROW_FORMAT` varchar(10) DEFAULT NULL,
  `TABLE_ROWS` bigint(21) unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint(21) unsigned DEFAULT NULL,
  `DATA_FREE` bigint(21) unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint(21) unsigned DEFAULT NULL,
  `CREATE_TIME` datetime DEFAULT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(32) DEFAULT NULL,
  `CHECKSUM` bigint(21) unsigned DEFAULT NULL,
  `CREATE_OPTIONS` varchar(255) DEFAULT NULL,
  `TABLE_COMMENT` varchar(2048) NOT NULL DEFAULT ''
) ENGINE=MEMORY DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql>

A full table scan is required, resulting in the need for a temp table with an in-memory sort.

SUGGESTIONS

SUGGESTION #1 : Use ext4. You already said the query works fine in ext4.

SUGGESTION #2 : Increase tmpfs to 16G

SUGGESTION #3 : Query the information_schema so that it does not use as much memory

DROP TABLE IF EXISTS mysql.dbtbcount;
CREATE TABLE mysql.dbtbcount
(
    table_schema VARCHAR(64) NOT NULL,
    table_count INT NOT NULL DEFAULT 0,
    PRIMARY KEY (table_schema)
) ENGINE=MEMORY;
INSERT INTO mysql.dbtbcount (table_schema)
SELECT schema_name FROM information_schema.schemata
WHERE schema_name NOT IN ('information_schema','mysql');
INSERT INTO mysql.dbtbcount (table_schema)
SELECT table_schema FROM information_schema.tables
ON DUPLICATE KEY UPDATE table_count = table_count + 1;
SELECT * FROM mysql.dbtbcount;
SELECT COUNT(1) DistinctDBs FROM mysql.dbtbcount WHERE table_count > 0;

This counts all tables in all databases

When any row has a zero table_count, that database is empty.
Count all rows that have a nonzero table_count to get the DISTINCT count.
When done, run DROP TABLE mysql.dbtbcount;

Give it a Try !!!

UPDATE 2013-08-19 17:40 EDT

Your problem should have been posted as a separate question. Nevertheless, let's see what we can surmise from this.

You should not be using /tmp as a backup location. Why?

The OS needs to write temp file there. At OS reboot, all files vanish from it. So, a transient folder should not be the place for backups.
mysqld has /tmp defined as the default folder for temp files

You need to reorganize the script to do parallel dumps

SUGGESTION

You should collect the databases and parallel mysqldump 25 databases at a time, starting from the biggest databases to the smallest. It gets faster with each next set of 25...

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT="SELECT db FROM (SELECT table_schema db,SUM(data_length) datasize"
SQLSTMT-"${SQLSTMT} FROM information_schema.tables"
SQLSTMT-"${SQLSTMT} WHERE table_schema NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(table_schema,7)<>'_schema'"
SQLSTMT-"${SQLSTMT} GROUP BY table_schema) A"
SQLSTMT-"${SQLSTMT} ORDER BY datasize DESC"
mysql ${MYSQL_CONN} -ANe"${SQLSTMT}" > /tmp/ListOfDatabases.txt

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

UPDATE 2013-08-20 10:03 EDT

If you don't care about the size of the database here is an alternate method that will start the mysqldump process faster:

BACKUP_FOLDER=/any/path/but/tmp

MYSQL_HOST=localhost
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-h${MYSQL_HOST} -u${MYSQL_USER} -p${MYSQL_PASS}"

SQLSTMT-"${SQLSTMT} SELECT schema_name FROM information_schema.schemata"
SQLSTMT-"${SQLSTMT} WHERE schema_name NOT ('mysql','log')"
SQLSTMT-"${SQLSTMT} AND RIGHT(schema_name ,7)<>'_schema'"

COMMIT_COUNT=0
COMMIT_LIMIT=25
MYSQLDUMP_OPTIONS="--hex-blob --routines --triggers"
for DB in `cat /tmp/ListOfDatabases.txt`
do
    BACKUP_FILE=${BACKUP_FOLDER}/${DB}.sql.gz
    mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} ${DB} | gzip > ${BACKUP_FILE} &
    (( COMMIT_COUNT++ ))
    if [ ${COMMIT_COUNT} -eq ${COMMIT_LIMIT} ]
    then
        COMMIT_COUNT=0
        wait
    fi
done
if [ ${COMMIT_COUNT} -gt 0 ]
then
    wait
fi

I wrote about doing parallel database and table dumps before : How can I optimize a mysqldump of a large database?

If you cannot get mydumper to throttle based on database size, this is the way to go.

GIVE IT A TRY !!!


Unable to remotely access MySQL I am experiencing problems with mysql 5.1 at the moment and possibly due to that I am unable to connect.

The port that I am using for mysql is open (http://www.yougetsignal.com/tools/open-ports/)

All of a sudden i was unable to access mysql remotely, could be due to luck of memory or something similar?! I can put any user name and password and still same result.

I have no data in the log files to share, nothing is logged for some reason.

I have checked and mysqld does listen to correct port

When I telnet xx.xx.xx.xx xxxx I have following result

Trying xx.xx.xx.xx...
Connected to xx.xx.xx.xx.
Escape character is '^]'.
?
5.1.66-0+squeeze1l_k{KBI'(7y!1rlHc8,u^CConnection closed by foreign host.

"and see if you get the same ... if not then you've a problem with the server. if that works ok but you can't do the same from the client machine(s) then you've got a problem at the client end." (http://ubuntuforums.org/archive/index.php/t-1625679.html) So this is a client problem

By default remote access to the MySQL database server is disabled for security reasons. However, some time you need to provide remote access to database server from home or a web server.

Please refer the link


Conversion of statements to E-R Diagram EDIT: PLEASE DON'T CONSIDER ARROWS :)

statements are

"A teacher can teach many courses. A student
can enrol in many courses. A course may
be a part of one or many programmes. A
teacher can be mentor of many students,
however a student can have only one
mentor."

Having confusion is Mentor an entity or not

I have drawn two E-R diagrams with

and without mentor

this diagram can be accessed here

is Mentor of is a relationship between a teacher and a student.
From your diagram I conclude that a course can be only be enroled by one student. I is not explicitely stated but I think that a course can be enroled by many students. Also I think that a course can be part of more then one program. Finally I get the following diagram:

The diagram is created with dia and the source can be found here.

In the diagram all relationships are defined mandatatory. So the the diagram should be refined in respect to this. For example it seems to be wrong that each teacher is mentor of at least one student.


SQL Server using incorrect plan In our application we have the following tiers for data processing:

.NET orchestration application that works out which SSIS packages to fire
SSIS packages that do much of the work and call Stored Procedures
Stored procedures to perform calculations and read/write data where SSIS data flows are not appropriate

One stored procedure runs in 40 seconds if executed from SSMS, and approximately the same time if the SSIS package is called manually.

If however, the SSIS package is called by the .NET app, then the query takes more than 14 hours to run, and flat lines all processors.

Looking at the execution plans, it seems that if the procedure is run from SSMS or the SSIS package is fired manually and then calls the procedure, SQL Server uses a sensible plan based on current statistics.

However, if the SSIS package is called from the .NET app, then SQL server create a new plan, using totally the wrong statistics. The following things made no difference:

Updating stats
Clearing the cache
Restarting the instance
Setting WITH RECOMPILE on the procedure execution

We fudged resolution by using a MAXDOP hint in the query, but my question is this:

What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

It looks like you have mostly answered your other questions in your monologue, and the things that made a difference are the standard list of things to do to force a recompile after being afflicted by parameter sniffing.

To answer your last question, the one marked my question is this:

  What would make SQL Server produce a totally different plan, based on totally incorrect statistics when called with the same parameters and same data through the method I described?

The answer is that the plan cache is very specific about SET settings that are in effect on the session that is presenting the query. A different SET setting could need a very different plan, such as the setting SET ANSI_NULLS.  Very likely, your connection from SSMS has different settings from what your .Net application.  This is easily verifiable by using SQL Server Profiler to trace for Existing Connections and Login Audits, which show you the SET settings.

The effect is that the seemingly same query will result in a Cache Miss and a new one has to be created, which will be somewhat based on the parameters being presented to SQL Server for this instance of the query.


How do I tell which Edition and Version my SSAS Instance is running? In SQL Server on the database engine, you can run a SQL query like this to get the server's version info like Edition and Version, and Update Level, etc.

SELECT @@VERSION

What is the MDX equivalent for querying an SSAS (Tabular or Multidimensional) instance? Are there SSAS DMVs that can get me this answer?

I browsed the Books Online page for SSAS DMVs, but I didn't notice any DMVs that could help me. DISCOVER_INSTANCES didn't seem to have the info.

Let's assume that SQL Server Database Engine is not installed on this same server as the SSAS instance, so I cannot check this by querying the database engine.

In my case, this is for a SQL 2012 Tabular instance, but would like to know how to query Multidimensional instance too.

The easiest way is :

using SSMS -- connect to instance --> reports -> standard reports --> General

There are other ways as well - How to find Analysis Services Server Version ?.


Exclude .LOG file while restoring a database I have a database backup file (MyDatabase.bak). When restoring the database through SSMS it gives me "Not enough space on disk" message. I looked for an option to exclude .LOG file from restore but could not find one.

I even tried to remove the "MOVE .LOG" from restore query but got an error message "could not find .LOG file use WITH MOVE option".

Is there an option to exclude the log file while restoring the database?

Thanks

No: the log is an essential part of the database and is used during RESTORE process (redo/undo phases) to ensure consistency.

Use WITH MOVE to restore to a compressed folder to gain "more" disk space temporarily. Or an external USB drive etc

Edit:

By compressed, I mean NTFS compression (for XP but still applies).

WITH MOVE is part of restore exposed as "Restore the database files as" in SSMS

Edit 2:

SQL Server can not use network drives unless you enable trace flag 1807. Now, use at your own risk to get running again only


The process is running and is waiting for a response from the server - transactional replication I could not yet figure it out why one of my transaction replication subscription is constantly coming out with the following annoying message:

The process is running and is waiting for a response from the server.
Query Timeout Expired.

I have tried to increase the timeout on the Log Reader Agent Profile, but it is read-only.
I have also tried to create another profile and associate it with the Log Reader Agent, but it is not applied.

Questions:

1) How can I change the Log Reader Agent Profile via T-SQL? That would give me the proper error message.

2) How can I find out what is causing the timeout, where is it stuck?

From my previous experience, this error message disappears with time, but in this case it is not going away, and I feel that I need to change some setting, but I would like first to see clear where it is stuck, in order to assess best what exactly to change and why.

The Query Timeout message you are seeing is your Distribution Agent timing out, not your Log Reader Agent.  You need to increase your Distribution Agent profile Query Timeout parameter value to alleviate the error.  Please see the section To view and edit the parameters associated with a profile in Work with Replication Agent Profiles to edit the QueryTimeout parameter value for your Distribution Agent.

Note that if you run your Distribution Agent continuously that you will need to restart your Distribution Agent for the profile change to take affect.


Can I set different database directories in MongoDB? In db2 we can create databases on different directories through db2 list db directory. Can we also do that on MongoDB?

I am aware of the dbPath in the config file. But do all databases created have to be stored there?

All databases for a MongoDB server are created within the configured storage.dbPath directory.

You can't directly specify directory paths for new databases via an end user command, however you can enable the storage.directoryPerDB option in your mongod config to use a separate subdirectory for each database. The subdirectory name used is determined by the storage engine, but the default storage engines (WiredTiger in MongoDB 3.2+, MMAPv1 in earlier versions) currently base this on the database name. Since database names use UTF8, any special characters that might be problematic will be encoded when mapped to a subdirectory name.

With a directoryPerDB  configuration you can also mount or symlink subdirectories to different volumes in your filesystem (assuming the dbPath isn't in use when you change existing mount points). This can be useful if you want to separate I/O for different databases. Note: if you end up managing databases on different volumes this may also affect your backup strategy. For example, filesystem-level backups require a consistent backup of the full dbPath — you cannot independently backup database subdirectories in a MongoDB deployment.

An important caveat noted in the documentation:

  To change the directoryPerDB option for existing deployments, you must restart  mongod instances with the new directoryPerDB value and a new data directory (dbPath), and then repopulate the data.

The directoryPerDB config option can vary per member in a replica set, which can be useful for migration between different dbPath layouts without downtime.


Get column name based on value in table I have the given table:

mysql> describe streaming_innodb;
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| Field         | Type                   | Null | Key | Default             | Extra                       |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
| row_id        | bigint(20) unsigned    | NO   | PRI | NULL                | auto_increment              |
| create_time   | timestamp              | NO   | MUL | CURRENT_TIMESTAMP   | on update CURRENT_TIMESTAMP |
| user_time     | timestamp              | NO   | MUL | 0000-00-00 00:00:00 |                             |
| uTINYINT      | tinyint(3) unsigned    | YES  |     | NULL                |                             |
| uSMALLINT     | smallint(5) unsigned   | YES  |     | NULL                |                             |
| uMEDIUMINT    | mediumint(8) unsigned  | YES  |     | NULL                |                             |
| uINTEGER      | int(10) unsigned       | YES  |     | NULL                |                             |
| uBIGINT       | bigint(20) unsigned    | YES  |     | NULL                |                             |
| uDecimal_10_5 | decimal(10,5) unsigned | YES  |     | NULL                |                             |
| uDecimal_10_0 | decimal(10,0) unsigned | YES  |     | NULL                |                             |
| uFloat_30_15  | float(30,15) unsigned  | YES  |     | NULL                |                             |
| uFloat_10_10  | float(10,10) unsigned  | YES  |     | NULL                |                             |
| sTINYINT      | tinyint(4)             | YES  |     | NULL                |                             |
| sSMALLINT     | smallint(6)            | YES  |     | NULL                |                             |
| sMEDIUMINT    | mediumint(9)           | YES  |     | NULL                |                             |
| sINTEGER      | int(11)                | YES  |     | NULL                |                             |
| sBIGINT       | bigint(20)             | YES  |     | NULL                |                             |
| sDecimal_4_1  | decimal(4,1)           | YES  |     | NULL                |                             |
| sDecimal_10_2 | decimal(10,2)          | YES  |     | NULL                |                             |
| sFloat_2_0    | float(2,0)             | YES  |     | NULL                |                             |
| sFloat_15_8   | float(15,8)            | YES  |     | NULL                |                             |
| rCHAR5        | char(5)                | YES  |     | NULL                |                             |
| rCHAR100      | varchar(100)           | YES  |     | NULL                |                             |
| rDATE         | datetime               | YES  |     | NULL                |                             |
| rTIME         | time                   | YES  |     | NULL                |                             |
| rDATETIME     | datetime               | YES  |     | NULL                |                             |
| rTIMESTAMP    | timestamp              | NO   |     | 0000-00-00 00:00:00 |                             |
+---------------+------------------------+------+-----+---------------------+-----------------------------+
27 rows in set (0.00 sec)

Assuming I want to find the value of a column, based on a value in another column, I can use the LIKE method

Example:

mysql> select user_time from streaming_scaledb where rTime like "04:44:21";
+---------------------+
| user_time           |
+---------------------+
| 2019-03-18 04:52:05 |
+---------------------+
1 row in set (0.02 sec)

However, for this problem I only know the value "04:44:21"; ie I need to find X and don't know the expected user_time.

mysql> select user_time from streaming_scaledb where X like "04:44:21";

Is there any query I can use such in order to find X?

Plan A:  Client code to construct the query with the variable column name.

Plan B:  Do a similar thing, but using a Stored Routine in which you use CONCAT, PREPARE and EXECUTE.

Meanwhile, FLOAT(m,n) is a bad idea.  It leads to two roundings.  Suggest simply using FLOAT.  Also, FLOAT contains about 7 significant digits, yet many of your float columns seem to expect more.  Perhaps you want DOUBLE (~16 digits) or DECIMAL.


Execute ALTER SYSTEM command in oracle scheduler I would like to set a job in the scheduler which will do a change of the resource manager plan.

    BEGIN
      DBMS_SCHEDULER.CREATE_JOB (
       job_name             => 'scott.dayplan',
       job_type             => 'PLSQL_BLOCK',
       job_action           => 'BEGIN ALTER SYSTEM SET RESOURCE_MANAGER_PLAN = 'dayplan'; END;'
       start_date           => '17-MAY-10 01.00.00 AM Europe/Warsaw',
       repeat_interval      => 'FREQ=DAILY; BYHOUR=8;',
       end_date             => '99-SEP-04 01.00.00 AM Europe/Warsaw',
       comments             => 'Resource manager daytime plan');
    END;
    /

But the job_action is given wrong. Any ideas how to execute alter system command in create job function?

Create procedure to run your SQL statements then call that procedure in job_action.

SQL> create or replace procedure my_proc
as
begin
execute immediate 'ALTER SYSTEM SET RESOURCE_MANAGER_PLAN=DEFAULT_MAINTENANCE_PLAN';
end;
/

Procedure created.

SQL> EXEC dbms_scheduler.drop_job('job1');

PL/SQL procedure successfully completed.

SQL> BEGIN
DBMS_SCHEDULER.CREATE_JOB(
   job_name          =>  'job1',
   job_type          =>  'PLSQL_BLOCK',
   job_action        =>  'begin my_proc(); end;',
   start_date        =>  SYSDATE,
   repeat_interval   =>  'FREQ = DAILY; INTERVAL = 1');
END;
/

PL/SQL procedure successfully completed.

SQL> EXEC dbms_scheduler.run_job('job1');

PL/SQL procedure successfully completed.
SQL> select log_date, status from dba_scheduler_job_run_details where job_name='JOB1';

LOG_DATE                                STATUS
-------------------------------------   --------------
10-MAY-17 06.36.43.906003 AM -04:00     SUCCEEDED

Starting from Oracle 12c, we have SQL_SCRIPT as job_type which specifies that the job is a SQL*Plus script.

DBMS_SCHEDULER: Oracle 12c

Moreover, if you just want to switch resource allocation plan, as Balaz Papp mentioned in the comment, you could use Scheduler Windows.

  You create windows to automatically start jobs or to change resource allocation among jobs during various time periods of the day

Oracle Scheduler Concepts: Window


SQL Server 2012 Business Intelligence licensing I have Microsoft SQL Server 2012 Entreprise Edition license, What are the tools needed to work on SSIS/SSAS/SSRS?

Since BIDS was replaced with SSDT on the 2012 version, does it mean I will need to get a separate license and install Visual Studio then get the free SSDT add-in?

Sorry for the confusion, I am new to Microsoft BI and can't find relevant information onlines

No, you do not have to buy a VS licence to use SSDT or SSDT-BI, you have the licence for the services (IS, AS, RS) if you have a SQL Server licence. SSDT and SSDT-BI are just tools to use those services.

You can freely download SSDT and SSDT-BI from Microsoft:

SSDT: http://msdn.microsoft.com/en-us/data/hh297027 (for SQL Server Database projects)
SSDT-BI: https://www.microsoft.com/en-us/download/details.aspx?id=36843 (for SSIS, SSAS and SSRS projects)

Edit It is not required to preinstall any kind of VS, the will install the VS Shell if it is not installed.

Note You can install BIDS/SSDT-BI using the SQL Server installer, but it contains the version which was available when the installer built (mostly hardly outdated).


Poor performing subquery with date comparisons When using a subquery to find the total count of all prior records with a matching field, the performance is terrible on a table with as little as 50k records. Without the subquery, the query executes in a few milliseconds. With the subquery, the execution time is upwards of a minute.

For this query, the result must:

Include only those records within a given date range.
Include a count of all prior records, not including the current record, regardless of date range.

Basic Table Schema

Activity
======================
Id int Identifier
Address varchar(25)
ActionDate datetime2
Process varchar(50)
-- 7 other columns

Example Data

Id  Address     ActionDate (Time part excluded for simplicity)
===========================
99  000         2017-05-30
98  111         2017-05-30
97  000         2017-05-29
96  000         2017-05-28
95  111         2017-05-19
94  222         2017-05-30

Expected Results

For the date range of 2017-05-29 to 2017-05-30

Id  Address     ActionDate    PriorCount
=========================================
99  000         2017-05-30    2  (3 total, 2 prior to ActionDate)
98  111         2017-05-30    1  (2 total, 1 prior to ActionDate)
94  222         2017-05-30    0  (1 total, 0 prior to ActionDate)
97  000         2017-05-29    1  (3 total, 1 prior to ActionDate)

Records 96 and 95 are excluded from the result, but are included in the PriorCount subquery

Current Query

select
    *.a
    , ( select count(*)
        from Activity
        where
            Activity.Address = a.Address
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc

Current Index

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON [dbo].[Activity]
(
    [ActionDate] ASC
)
INCLUDE ([Address]) WITH (
    PAD_INDEX = OFF,
    STATISTICS_NORECOMPUTE = OFF,
    SORT_IN_TEMPDB = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS = ON,
    ALLOW_PAGE_LOCKS = ON
)

Question

What strategies could be used to improve the performance of this query?

Edit 1
In reply to the question of what I can modify on the DB: I can modify the indexes, just not the table structure.

Edit 2
I have now added a basic index on the Address column, but that didn't seem to improve much. I am currently finding much better performance with creating a temp table and inserting the values without the PriorCount and then updating each row with their specific counts.

Edit 3
The Index Spool Joe Obbish (accepted answer) found was the issue. Once I added in a new nonclustered index [xyz] on [Activity] (Address) include (ActionDate), the query times went down from upwards of a minute to less than a second without using a temp table (see edit 2).

With the index definition that you have for IDX_my_nme, SQL Server will be able to seek using the ActionDate column but not with the Address column. The index contains all of the columns needed to cover the subquery but it likely isn't very selective for that subquery. Suppose that almost all of the data in the table has an ActionDate value of earlier than '2017-05-30'. A seek of ActionDate < '2017-05-30' will return almost all of the rows from the index, which are further filtered down after the row is fetched from the index. If your query returns 200 rows then you would probably doing almost 200 full index scans on IDX_my_nme, which means you will read around 50000 * 200 = 10 million rows from the index.

It's likely that seeking on Address will be far more selective for your subquery, although you haven't given us full statistical information about the query so that's an assumption on my part. However, suppose that you create an index on just Address and your table has 10k unique values for Address. With the new index, SQL Server will only need to seek 5 rows from the index for each execution of the subquery, so you'll read around 200 * 5 = 1000 rows from the index.

I'm testing against SQL Server 2016 so there might be some minor syntax differences. Below is some sample data in which I made similar assumptions to the above for data distribution:

CREATE TABLE #Activity (
    Id int NOT NULL,
    [Address] varchar(25) NULL,
    ActionDate datetime2 NULL,
    FILLER varchar(100),
    PRIMARY KEY (Id)
);

INSERT INTO #Activity WITH (TABLOCK)
SELECT TOP (50000) -- 50k total rows
x.RN
, x.RN % 10000 -- 10k unique addresses
, DATEADD(DAY, x.RN / 100, '20160201') -- 100 rows per day
, REPLICATE('Z', 100)
FROM
(
    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
) x;

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([ActionDate] ASC) INCLUDE ([Address]);

I've created your index as described in the question. I'm testing against this query which returns the same data as the one in the question:

select
    a.*
    , ( select count(*)
        from #Activity Activity
        where
            Activity.[Address] = a.[Address]
            and Activity.ActionDate < a.ActionDate
    ) as PriorCount
from #Activity a
where a.ActionDate between '2017-05-29' and '2017-05-30'
order by a.ActionDate desc;

I get an index spool. What that means at a basic level is that the query optimizer build a temporary index on-the-fly because none of the existing indexes against the table were suitable.

The query still finishes quickly for me. Perhaps you aren't getting the index spool optimization on your system or there's something different about the table definition or the query. For educational purposes I can use an undocumented feature OPTION (QUERYRULEOFF BuildSpool) to disable the index spool. Here's what the plan looks like:

Don't be fooled by the appearance of a simple index seek. SQL Server reads nearly 10 million rows from the index:

If I'm going to be running the query more than once then it probably doesn't make sense for the query optimizer to create an index each time it runs. I could create an index upfront that would be more selective for this query:

CREATE NONCLUSTERED INDEX [IDX_my_nme_2] ON #Activity
([Address] ASC) INCLUDE (ActionDate);

The plan is similar to before:

However, with the new index SQL Server only reads 1000 rows from the index. 800 of the rows are returned to be counted. The index could be defined to be more selective but this could be good enough depending on your data distribution.

If you aren't able to define any additional indexes on the table I would consider using window functions. The following appears to work:

SELECT t.*
FROM
(
    select
        a.*
        , -1 + ROW_NUMBER() OVER (PARTITION BY [Address] ORDER BY ActionDate) PriorCount
    from #Activity a
) t
where t.ActionDate between '2017-05-29' and '2017-05-30'
order by t.ActionDate desc;

That query does a single scan of the data but does an expensive sort and calculates the ROW_NUMBER() function for every row in the table, so it feels like there's some extra work done here:

However, if you really like that code pattern you could define an index to make it more efficient:

CREATE NONCLUSTERED INDEX [IDX_my_nme] ON #Activity
([Address], [ActionDate]) INCLUDE (FILLER);

That moves the sort towards the end which will be much less expensive:

If none of this helps then you'll need to add more information to the question, preferably including actual execution plans.


How to Revoke and Re-grant Permissions for an in-use MySQL User We have a user which (wrongly) has global privileges (i.e. *.* rather then database specific).

We would like to restrict this user to one database, so my approach would normally be to REVOKE ALL and then GRANT per-database.

However, the account is currently being used by a live application, so we ideally need to preserve access for the required database while removing others.

My question is twofold: 1) Will it even work? and 2) will the below SQL do the job?

GRANT ALL ON mydb.* to 'myuser'@'%';
REVOKE ALL PRIVILEGES ON *.* FROM 'mysuer'@'%';
FLUSH PRIVILEGES;

I hope It should work, Please have a look at following

GRANT ALL ON *.* TO 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
1 row in set (0.00 sec)

Above shows that all privileges are granted to myuser on all databases.Now grant privileges for single db.

GRANT ALL ON mydb.* to 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)

Now revoke unnecessary privleges

REVOKE ALL PRIVILEGES ON *.* FROM 'myuser'@'%';
Query OK, 0 rows affected (0.00 sec)

Now check which privileges still exits

show grants for 'myuser'@'%'\G
*************************** 1. row ***************************
Grants for myuser@%: GRANT USAGE ON *.* TO 'myuser'@'%'
*************************** 2. row ***************************
Grants for myuser@%: GRANT ALL PRIVILEGES ON `mydb`.* TO 'myuser'@'%'
2 rows in set (0.00 sec)

That's what you need !!


Speed to read small table I am trying to react to a Sql Table change very quickly.

I have set-up a SqlDependency in c# and on receipt I query the table for "New" records.

My table contains a few thousand rows. There are 2 areas where delays slip in:

SqlDependency. On Average there is a 3 ms delay before I get notified.
Table Read. I select all rows from the table where a column contains the string "NEW". This table has max 5,000 rows. On average this "read" takes 4.5 ms.

So my minimum time to start acting on new data is 7.5 ms. This is quite long for my application.

Are there any tricks to improve this?

Additional

When I run this query in MS Studio with time statistics I get:

  SQL Server parse and compile time:     CPU time = 0 ms, elapsed time =
  0 ms. SQL Server parse and compile time:     CPU time = 0 ms, elapsed
  time = 0 ms.

  (9 row(s) affected)

  SQL Server Execution Times:    CPU time = 0 ms,  elapsed time = 0 ms.

But If run it in c# like this I get average 1.67 ms.

private void button1_Click(object sender, EventArgs e)
{
    DataTable dt = null;

    SqlOMS2 sqlOMS2 = new SqlOMS2();

    Stopwatch sw = new Stopwatch();
    sw.Start();
    int num = 100;

    for (int i = 0; i < num; i++)
    {
        sqlOMS2.GetAnyNewOrders(out dt);
    }

    Console.WriteLine("Time taken: " + sw.ElapsedMilliseconds / (double)num);

    if (dt == null || dt.Rows.Count == 0)
    {

    }
}

public bool GetAnyNewOrders(out DataTable dt)
{
    dt = null;

    try
    {

        string commandText = "SELECT * FROM [" + DBName + "].[LB].[Orders] WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent'  ORDER BY [OrderDate],[OrderUTC] DESC";

        SqlCommand cmd = new SqlCommand
        {
            Connection = conn,
            CommandText = commandText
        };

        cmd.ExecuteNonQuery();
        SqlDataAdapter da = new SqlDataAdapter(cmd);
        DataSet ds = new DataSet();
        dt = new DataTable();
        ds.Reset();
        da.Fill(ds);
        dt = ds.Tables[0];

        if (dt.Rows.Count == 0)
            return false;

        return true;
    }
    catch (Exception)
    {
        return false;
    }
}

SqlDependency was not really intended for real time type processing.  3 ms is still pretty good.

You should only retrieve required columns and you should use a Reader.  DataTable has a lot of overhead.  And you should be using Using.

string commandText = "SELECT PK FROM [" + DBName + "].[LB].[Orders] " + Environment.NewLine() +
                     "WHERE [AccountIdent] = 'LB:B' AND [Exchange] = 'FX' AND [Status] = 'Sent' " + Environment.NewLine() +
                     "ORDER BY [OrderDate], [OrderUTC] DESC";
using (SqlCommand cmd = conn.CreateCommand())
{
    cmd.CommandText = commandText;
    conn.Open();
    using (SQLdataReader rdr = cmd.ExecuteReader())
    {
        while(rdr.Read())
        {
           list.Add(rdr.GetInt(0));
        }
    }
    conn.Close();
}


Tsql help - doing a large update from CSV file to a table in ssms I have been given a csv file with just ids in there and those ids are in a table in ssms with columns that require updating. Out of 90,000 ids in that table, only 83,000 of them require updating from the csv file.
the main table is:
ID  ENABLED    AGREED
1       1                       no
2       1                       no
3       1                       no
4       0                       yes

I know I can do an update on the table from the ids i have been given in the csv such as:
update table1
set enabled = 0,
agreed = 'yes',
where id in('1','2','3')

however, my problem is, there are 83,000 of them that require updating to those specific conditions and wanted to find the best way to do this. I have been told to write a script to import the CSV into a temp table, then join that temp table onto the main table and perform the update rather than hard code a list of id's in a script.
How can I do this? Does anyone know a better way?

You can use the Import Flat File feature in SSMS to import a CSV file to a Table in your database. (Import Data is another feature you can use too but it's a little more bulky and older.) Either of these features can be found be right clicking on the database you want to import into, clicking Tasks, then clicking Import Flat File (or Import Data if you prefer). Then it's just a matter of following the Import Wizard which is only a few steps.
If the update you're making to your main table are the same values for all 83,000 rows that are being updated, then you can just update them with a similar hard-coded update query to this, after you import your CSV file:
UPDATE M
SET M.enabled = 0, -- Hard-coded value for all rows
    M.agreed = 'yes' -- Hard-coded value for all rows
FROM MainTable AS M
INNER JOIN CsvImportedTable AS C
    ON M.id = C.id

Otherwise if you have different values you need to set for different rows within your list of 83,000 IDs then you'll need to pre-define those values in new columns in the CSV file first (using Excel for example) before you import it. Then after you import it, your CSV table will have those columns as well so you can do a more dynamic update like this:
UPDATE M
SET M.enabled = C.enabledNewValue, -- Values you entered in your CSV
    M.agreed = C.agreedNewValue -- Values you entered in your CSV
FROM MainTable AS M
INNER JOIN CsvImportedTable AS C
    ON M.id = C.id

Additional resources on importing CSVs and data into a database via SSMS:

SQLShack - Importing and Working with CSV Files in SQL Server

Microsoft Books Online on the Import Data feature

MSSQLTips - Simple way to import data into SQL Server


Index to speed up query I have this SQL query that I have to speed up:

SELECT DISTINCT A.attribute1, A.attribute2
FROM table1 A, table2 B
WHERE A.attribute1 = B.attribute3
AND B.attribute4 = 'String';

What I am currently doing is creating a hash index on B.attribute3 to speed up the JOIN and creating another hash index on B.attribute4 to speed up the equality with 'String'.

CREATE INDEX atr3_idx ON table2 USING HASH (attribute3);
CREATE INDEX atr4_idx ON table2 USING HASH (attribute4);

Is there a better way to optimise this query?

My first attempt would be to add btree indexes, not hash ones.

an index on B (attribute4, attribute3) and
an index on A (attribute1, attribute2)

The reasoning behind the suggestion is that the only a small part of the first index on B needs to be read, only the part that has attribute4 = 'String4' and then the values of attribute3 will be used for the join, against the other index on A.


Select Mysql Table based on other table field value I wonder if its possible to make query like this one:

SELECT cs.platform,
     (SELECT COUNT(*) FROM general_list as loc WHERE loc.platform=cs.platform) as indexed,
     (SELECT COUNT(*) FROM cs.table     as loc WHERE loc.platform=cs.platform) as sandbox
FROM (
    SELECT platform, table
    FROM states
    WHERE visible=1
) as cs

For each iteration in the main select, I need to take the table name from cs.table to info from such table.

Is there anyway to accomplish this?

The only way to "dynamically" use a tablename is via a Stored Routine in which you do CONCAT(...) to build the SQL, plus PREPARE and EXECUTE to then perform the constructed SQL.

Needing to do this is usually a sign of poor schema design.  Please explain the schema and why you went that direction; we may be able to help 'fix' it.


What would the relational diagram look like in this situation? Situation

Last week I had to do an assignment, and I did not agree with my teacher's ERD. She used a n-ary relationship while I used two binary relationships. I would like to know a true dba's (you) opinion on this.

The original assignment was to design an ERD, but since the ERD notation we use at school is very uncommon I'm going to ask what the relational diagram is supposed to look like. I think I can figure it out by myself once I know what the relational diagram is supposed to look like.

Assignment

There's a housing assocation company that wants a database.

When a customer registers, the customer has to fill in his details (name, dob, phonenumber), and at that moment he also has to fill in a second form. There, he has to specify what city's he wants to live in, and what types of houses he wants to live in (house, mansion, villa etc.). It's required the customer fills in at least one city and one house.

The second form also has some other fields that the customer must fill in, such as a maximum price and a remark (e.g. the house has to have a swimming pool).

My answer

CUSTOMER: customer# (PK), firstname, lastname, dob, phonenumber
FORM: customer# (PK), dateofregister, remark, maxprice
contains1: customer# (PK), city (PK)
contains2: customer# (PK), housetype (PK)

CUSTOMER 1..1 FORM
FORM 1..n contains1
FORM 1..n contains2

My teachers answer

She merges both CUSTOMER and FORM into the same table since they have the same key customer#, and creates the table CONTAINS: customer# (PK), city (PK), housetype (PK).

I don't agree with her, since I could specify housetype twice with the same customer#. Same thing goes for city. I also don't agree on the fact that she puts all the FORM attributes into the CUSTOMER table. It looks sloppy IMO, and I believe you would split it up in the 3rd normal form and you would end up with my result again...

I am with your teacher here. I cannot see any difference in degree of Normalization between the two solutions, but you are embedding aspects of a particular implementation presentation (Two views of what is often referred to as the External or Logical Schema.) into the core data structures. When performing the initial database design (of the Conceptual Schema) it is preferred to hide as much as possible both of the external presentations and of the physical table design.

Once the Conceptual Schema has been designed (and fully normalized), then one would determine the best way to present the Logical views of that structure to external applications, and the optimal physical assignment of columns to tables (the Physical Schema) to maximize performance.

Your proposal is pushing subtle choices of Physical and External design into the Conceptual Schema inappropriately.

It appears to me that your teacher is having difficulty explaining that your proposal might be a valid physical database design, later, given certain assumptions, and accurately reflects the External views as currently implemented, but fails to meet the needs of a Conceptual Schema that maximizes normalization with minimal clutter.

A key observations is that whenever two tables (entities) have the same Primary Key, then even if the normalization is the same the clutter has been increased. There will always be many ways to clutter a clean (Conceptual Schema) design, but this clutter can always be eliminated from it. In a real world database design with hundreds or even thousands of tables minimizing clutter is an essential design attribute.


Non-deterministic functions and row-based binary logging (MySQL) Newbie questions. I'm working on a MySQL project that will use only row-level binary logging. There will be no replication. Since logging is row-level, does it even matter if a function is deterministic? From the manual, my understanding is that it does not.

Added 6/9/20:

I've added an example which I think would be non-deterministic; please tell me if I'm wrong. The result will always be the same for a given state of the data, returning the highest date value in a set of rows, but would not always return the same value for the same input of a given patient id, since that changes over time. Is that right?

CREATE FUNCTION `last_enc_date`(patient INT) RETURNS date
BEGIN
# Returns the date of the most recent encounter (of any kind) for patient
DECLARE lastvisit DATE;
SELECT MAX(DATE)
    INTO lastvisit
    FROM encounters
    WHERE personid = patient;
RETURN lastvisit;
END

Your understanding is correct. RBR is safe even with non-deterministic functions.


Why won't SQL Server optimize the UNIONs? Consider these queries (SQL Fiddle):

Query 1:

SELECT * INTO #TMP1 FROM Foo
UNION
SELECT * FROM Boo
UNION
SELECT * FROM Koo;

Query 2:

SELECT * INTO #TMP2 FROM Foo
UNION
SELECT * FROM Boo
UNION ALL
SELECT * FROM Koo;

Note that Koo does not overlap with Boo/Foo, so the end result is the same.  The question is why the first UNION / UNION combination is not merged into a single SORT operation?

The query optimizer does have n-ary operators, though the execution engine has rather fewer.  To illustrate, I'm going to use a simplified version of your tables - (SQL Fiddle).

SELECT DISTINCT
    number
INTO foo
FROM master..spt_values
WHERE
    number < 1000;

SELECT DISTINCT
    number
INTO boo
FROM master..spt_values
WHERE
    number between 300 and 1005;

SELECT DISTINCT
    number
INTO koo
FROM master..spt_values
WHERE
    number > 1006;

ALTER TABLE dbo.foo ADD PRIMARY KEY (number);
ALTER TABLE dbo.boo ADD PRIMARY KEY (number);
ALTER TABLE dbo.koo ADD PRIMARY KEY (number);

Given those tables and data, let's look at the input tree for a three-way UNION query:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_Union
    OUTPUT(COL: Union1006 )
    CHILD(QCOL: [f].number)
    CHILD(QCOL: [b].number)
    CHILD(QCOL: [k].number)
    LogOp_Project
        LogOp_Get TBL: dbo.foo(alias TBL: f)
        AncOp_PrjList
    LogOp_Project
        LogOp_Get TBL: dbo.boo(alias TBL: b)
        AncOp_PrjList
    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList

The logical union operator has one output and three child inputs.  After cost-based optimization, the physical tree chosen is a merge union with three inputs:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8607);

PhyOp_MergeUnion
    PhyOp_Range TBL: dbo.foo(alias TBL: f)(1) ASC
    PhyOp_Range TBL: dbo.boo(alias TBL: b)(1) ASC
    PhyOp_Range TBL: dbo.koo(alias TBL: k)(1) ASC

The optimizer's output is reworked into a form that the execution engine (without n-ary merge union) can handle:

The post-optimization rewrite unfolds the n-ary PhyOp_MergeUnion into multiple Merge Union operators.  Notice how all the estimated cost remains associated with the 'original' union operator - the others have a zero cost estimate.

That the optimizer reasons about unions using n-ary operators provides one explanation for why it does not consider rewriting your first example to the same plan as the second example (the three-way union is a single tree node).

The second reason is there are no constraints to enforce the 'lack of overlap'.  Before constraints are in place, a union between boo and koo cannot be guaranteed not to produce duplicates, so we get a duplicate-removing plan (a Merge Union in this case):

SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

Adding the following constraints ensures the non-overlap condition cannot be violated without invalidating cached plans for the query:

ALTER TABLE dbo.foo WITH CHECK ADD CHECK (number < 1000);
ALTER TABLE dbo.boo WITH CHECK ADD CHECK (number BETWEEN 300 AND 1005);
ALTER TABLE dbo.koo WITH CHECK ADD CHECK (number > 1006);

Now it is safe for the optimizer to simply concatenate:

However, even with those constraints in place, the three-way union query still appears as three unions because the optimizer does not normally consider splitting n-ary operators to explore alternatives.  The n-ary operator thing is very useful in keeping the search space under control; breaking it apart would often be counter-productive given the optimizer's goal of finding a good plan quickly.

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION
SELECT k.number FROM dbo.koo AS k;

When written as a UNION and UNION ALL, an n-ary operator can no longer be used (the types do not match) so the tree has separate nodes:

SELECT f.number FROM dbo.foo AS f
UNION
SELECT b.number FROM dbo.boo AS b
UNION ALL
SELECT k.number FROM dbo.koo AS k
OPTION (QUERYTRACEON 3604, QUERYTRACEON 8605);

LogOp_UnionAll
    OUTPUT(COL: Union1007 )
    CHILD(COL: Union1004 )
    CHILD(QCOL: [k].number)

    LogOp_Union
        OUTPUT(COL: Union1004 )
        CHILD(QCOL: [f].number)
        CHILD(QCOL: [b].number)

        LogOp_Project
            LogOp_Get TBL: dbo.foo(alias TBL: f)
            AncOp_PrjList

        LogOp_Project
            LogOp_Get TBL: dbo.boo(alias TBL: b)
            AncOp_PrjList

    LogOp_Project
        LogOp_Get TBL: dbo.koo(alias TBL: k)
        AncOp_PrjList


Is Index Used for This Query If so, which one?

How do we know?

I have 3 indexes in a query. Actually only the first 2 should be useful

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent"▼: NumberInt(-1),
     "indexContents": NumberInt(1)
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent_indexContents",
   "dropDups": false,
   "background": false
}

{
   "v": NumberInt(1),
   "key": {
     "LongitudeLatitude": "2d",
     "Prominent": NumberInt(-1)
  },
   "ns": "database.tablebusiness",
   "name": "LongLat_Prominent",
   "dropDups": false,
   "background": false
}

This one is not necessary:

{
   "v": NumberInt(1),
   "key": {
     "indexContents": NumberInt(1)
  },
   "ns": "database.tablebusiness",
   "name": "indexContents",
   "dropDups": false,
   "background": false
}

Now I do this query

(the query is from PhP so I'll just paste what I see in mongodb log and mongovue

database.tablebusiness query: { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } ntoreturn:20 ntoskip:80 nscanned:100 nreturned:20 reslen:1147 1927ms

Now copying { LongitudeLatitude: { $nearSphere: [ 106.772835, -6.186753 ], $maxDistance: 0.04498373205078301 }, Prominent: { $gte: 15 }, indexContents: { $all: [ /^aru/, /^op/ ] } } into mongovue find command gets me the real database command

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50);

Which is good enough (except that the limit change a little bit). Doing explain in mongo.exe I got

db.tablebusiness.find({ "LongitudeLatitude" : { "$nearSphere" : [106.772835, -6.186753], "$maxDistance" : 0.044983732050783008 }, "Prominent" : { "$gte" : 15 }, "indexContents" : { "$all" : [/^aru/, /^op/] } }).limit(50).explain();

{
        "cursor" : "GeoSearchCursor",
        "nscanned" : 50,
        "nscannedObjects" : 50,
        "n" : 50,
        "millis" : 3291,
        "nYields" : 0,
        "nChunkSkips" : 0,
        "isMultiKey" : false,
        "indexOnly" : false,
        "indexBounds" : {

        }
}

Now based on that, what index is used? Does mongodb use any index at all? Any tips on how to improve this query will also be appreciated.

The reason you can't tell which one is being used is because of a bug whereby explain does not include the index name of the index being used for geo index queries:

https://jira.mongodb.org/browse/SERVER-4213

What we do know is that it has to be one of the first two you have listed, that have a 2d element first.  It cannot be the "indexContents": NumberInt(1) index.  You can use .hint() to specify the index to use and then compare the results of the index in each case.  I would expect the first one to perform better in general (but be larger) since it contains all of the elements you are querying on:

"LongitudeLatitude": "2d",
 "Prominent"▼: NumberInt(-1),
 "indexContents": NumberInt(1)

However, that will depend on the actual data itself.  I would test over a representative set of queries and then remove the slower of the two indexes to avoid confusion.


Can we use wildcard characters on the PostgreSQL hba_conf file for database names? e.g.

# TYPE  DATABASE        USER            ADDRESS                 METHOD

# "local" is for Unix domain socket connections only
local   all             all                                     peer
# IPv4 local connections:
host    ffc*            fatfc             127.0.0.1/32            md5
host    all             all             127.0.0.1/32            ident
# IPv6 local connections:
host    all             all             ::1/128                 ident

According to the documentation for The pg_hba.conf File, you can have:

database
Specifies which database name(s) this record matches. The value all specifies that it matches all databases. The value sameuser specifies that the record matches if the requested database has the same name as the requested user. The value samerole specifies that the requested user must be a member of the role with the same name as the requested database. (samegroup is an obsolete but still accepted spelling of samerole.) Superusers are not considered to be members of a role for the purposes of samerole unless they are explicitly members of the role, directly or indirectly, and not just by virtue of being a superuser. The value replication specifies that the record matches if a replication connection is requested (note that replication connections do not specify any particular database). Otherwise, this is the name of a specific PostgreSQL database. Multiple database names can be supplied by separating them with commas. A separate file containing database names can be specified by preceding the file name with @.

That is, wildcards are not possible, although you have all the following alternatives:

The value all, which is actually working as a wildcard matching "all databases".
The value sameuser, which is a database with the same name as the user that connects to the database.
The value samerole
The value replication
Multiple database names, separated by commas.
@FileName, specifying a file which contains the names of the databases

In your specific case, you could use:

fcca, fccb, fccd, ...
@fcc_names.txt

(and have fcc_names.txt filled in with all fcc* database names)


SQl Sever Backup Exclude Users I have a database on SQL Server 2012 that I need to backup and send to someone on a different network / domain. Is there a way to create a full backup file that will not include the users / security information from the source database?

i.e. I don't want the users from my network to show up in the restored database's users list.

Unfortunately no, you can't exclude anything from a backup.

If you are talking about Windows Logins then it probably isn't that big a deal.  The SIDs won't match anything on their network (very unlikely anyway) so all they will have is the usernames without any of the permissions associated.  SQL Logins of course will work fine as long as they create a login to match the users.

The only ways I can think of to avoid having the users in your DB when you do the backup are going to range from annoying to difficult.  Basically you either create a copy of your DB that you keep up to date but doesn't have any of the database principals you don't want or you script out all of the permissions, drop them, run the backup, and re-create the permissions.  Of course I wouldn't try that second option during the business day.


Update all the elements of JSON array nested in a JSON column (Postgres) I have json column with data that looks like this:

{
    a: [
        { q: 5 },
        { q: 8 },
        { q: 10 }
    ]
}

I'd like to map over the elements in the array and add an extra property to get something like this:

{
    a: [
        { q: 5, r: [] },
        { q: 8, r: [] },
        { q: 10, r: [] }
    ]
}

Any and all would be greatly appreciated.

PostgreSQL 9.6.6

I think I worked it out. Something like:

SELECT my_column::jsonb ||
    jsonb_build_object('a',
            (SELECT json_agg(a::jsonb || jsonb_build_object('r','[]'::jsonb))
            FROM jsonb_array_elements(my_column->'a') a)
    )
FROM my_table;

Though this might not be perfect since I'm translating to this example without testing.


Is this a normal set of MySQL privileges? Using phpmyadmin on a local test wamp setup, and well as pretty much the title states, there are 3 users marked as user = ANY, password = NO, such as:

USER | HOST    | Password | Global Priv | Grant
-----+---------+----------+-------------+------
Any  | %       | No       | USAGE       | No
Any  | Local   | No       | USAGE       | No
Any  | Domain  | No       | USAGE       | No
-----+---------+----------+-------------+------

None of the 3 any users have any specific privileges for any tables, so it appears they can't do much. Is this some sort of generic security measure, to explicitly state that 'Any' user that doesn't fall into any other user group has no privileges?

In reality, those three user accounts are actually quite dangerous. They pose a very great threat to test databases.

Unfortunately, mysql comes with full access to test databases. How can you find them ?

Run this query:

 SELECT user,host,db from mysql.db;

Upon installation of mysql, you will see two rows that give full access to any database named 'test' or whose first 5 characters are 'test_'.

Why is this a problem ???

Try running this command:

$ mysql -u'' -Dtest

You will have connected the test database without a password.

Now, create a table and load it with a row:

CREATE TABLE mytable (a int);
INSERT INTO mytable VALUES (1);

OK, big deal. Could you double this table in size 30 times ???

INSERT INTO mytable SELECT * FROM mytable;
INSERT INTO mytable SELECT * FROM mytable;
... (30 times)
INSERT INTO mytable SELECT * FROM mytable;

What do you get ?? A table with 1,073,741,824 rows. Easily, 4GB+.

Imagine creating any table of any size. How about creating a bunch of tables in the test database and freely accessing them at will ?

The best thing you can do under these circumstances is to run this query:

DELETE FROM mysql.db;

and restart mysql. Then, those three accounts will be properly rendered inoperative.

Give it a Try !!!

UPDATE 2011-09-12 10:00 EDT

This delete:

DELETE FROM mysql.db;
FLUSH PRIVILEGES;

is just what you need for an initial installation. However, if you have users already established, You can run this instead:

DELETE FROM mysql.db WHERE db IN ('test','test_%');
FLUSH PRIVILEGES;

This will remove the specific two DB permissions.

As I mentioned in my answer, the three permissions are very dangerous for test databases. Running this DELETE will neutralize those accounts for having full rights to test databases.


Preventing replication of alter table command to change engine I am in the process of switching a MySQL table from MyISAM to InnoDB and I do not want the slave to perform the alter table command until I execute it manually. The intention is to have a backup for a few hours in case there is an issue that I have not addressed already. Is there a way to tell the MySQL slave to not process alter table commands from the Master for a period of time?

If you want to prevent any ALTER TABLE ... ENGINE=InnoDB; from executing on a Slave, just run this on the Master:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;

What this does is prevent the Master from recording the ALTER TABLE ... ENGINE=InnoDB; in its binary logs. Since the Slave only replicates from the binary logs of the Master, just don't record the commands that you do not want replicated.

If you have multiple tables to convert and not replicate, then you can do this:

SET SQL_LOG_BIN=0;
ALTER TABLE ... ENGINE=InnoDB;
ALTER TABLE ... ENGINE=InnoDB;
.
.
.
ALTER TABLE ... ENGINE=InnoDB;
SET SQL_LOG_BIN=1;


Online backups with VENDOR library: what should be set for ACTIVE LOG PATH? My customer is using Veritas NetBackup so we use its library file in the LOGARCHMETH1 parameter for the VENDOR option. That is for archive logging path.

What path should be set for ACTIVE LOG PATH (LOGPATH)? Some location on local database storage? Will DB2 then transfer from active to archive logs between those two servers (database and Veritas NetBackup) automatically? Or how does it work?

The active log path (database configuration parameter LOGPATH) for a database MUST be a locally accessible directory on the database server.  Preferably one with very good read/write performance.  (I'd say it must be a local directory, but technically you can specify the path on an NFS mount).

When DB2 fills a transaction log file (and all transactions contained within have been committed), the DB2 log archiver process will copy the file according to what you've set in the LOGARCHMETH1 and LOGARCHMETH2 configuration parameters.


Determine weekend based on country I've a table called BOND which have columns BOND NAME, COUNTRY of ISSUE and ISSUE DATE

BOND  Country  ISSUE_DATE
===========================
B1    INDIA    20-AUG-2017
B2    ISRAEL   18-AUG-2017

How do I find the issue date is weekend or not based on the issue country of BOND, without changing NLS_TERRITORY

SELECT TO_CHAR(ISSUE_DATE,'DY') DAY FROM BOND;

In the above dates 18-AUG-2017 is a weekend in ISRAEL but not in INDIA

Try to test the following:

select
TO_CHAR (issue_date, 'D', 'NLS_DATE_LANGUAGE=HINDI) IN (6, 7)
from table

To see if it works for you. One thing you need to check is, depending on the country, if the week starts at a Monday or Sunday. You might have to change those in values accordingly.
Edit: doing this, you're only changing the value on the query. Isn't it what you wanted? And sorry for not getting the value right. Corrected.


Server Local Connection Provider has stopped listening on local pipe due to a failure All,

I'm looking at a database where every so often, connections to it will fail. The event viewer and SQL Logs have the following error in them:

  Server local connection provider has stopped listening on [
  \.\pipe\SQLLocal\MSSQLSERVER ] due to a failure. Error: 0xe8, state:
  4. The server will automatically attempt to re-establish listening.

I'm trying to determine the cause of this, as it's taking down the production database once or twice a day. Most of the time, it will reconnect within 30 seconds, but sometimes it can take 30+ minutes, and at that point, the customer restarts the SQL server, which gets it working again.

Here is what I've investigated so far:

Configuration Manager had all 3 protocols (shared memory, TCP/IP, named pipes) enabled for all options. We have since disabled named pipes.
sys.dm_exec_connections shows that 34 of the 45 connections to the DB right now are shared memory, so I don't think disabling named pipes did anything.
Shared memory is just named pipes on a local machine.
There are no additional events at the time of these errors in the event viewer. I've looked throughout Applications and Services logs as well as the Windows Logs, and nothing else is logged at the same time.
Connections work if we specify the hostname with tcp: in front of it. This seems to be an issue entirely with shared memory.
I've enabled trace flags 3689,4029 according to this post
Once the traceflags were enabled, I've seen additional entries in the event viewer like the following:
Network error code 0xe8 occurred while establishing a connection; the connection has been closed.  This may have been caused by client or server login timeout expiration.  Time spent during login: total 471 ms, enqueued 12 ms, network writes 0 ms, network reads 206 ms,  establishing SSL 252 ms, network reads during SSL 2 ms, network writes during SSL 0 ms, secure calls during SSL 0 ms,  enqueued during SSL 249 ms, negotiating SSPI 0 ms, network reads during SSPI 0 ms, network writes during SSPI 0 ms,  secure calls during SSPI 0 ms, enqueued during SSPI 0 ms, validating login 0 ms, including user-defined login processing 0 ms. [CLIENT: local machine]
The above error happened about 15 seconds before the connection stopped listening.
These errors are a lot more common when we have Sophos Endpoint protection enabled, but they still happened once when it was disabled.

SQL server @@version string:

Microsoft SQL Server 2012 (SP2-GDR) (KB3194719) - 11.0.5388.0 (X64)
    Sep 23 2016 16:56:29
    Copyright (c) Microsoft Corporation
    Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: ) (Hypervisor)

It's only SP2, so we may try upgrading the DB in case this got fixed.

Does anyone have ideas on where to go from here? I'm considering disabling Shared Memory, as that seems to be the culprit, but based on what I've read, I'm going to need to do a lot more research on that.

Had this for the first time in x years. Happened about the same time as the log backups.

However, I also got the Severity 17 alert email which had an additional bit of information:

  Comment: Error 17 - Insufficient Resources.

Am investigating but it currently looks like this is connected to Shared Memory and IIS logging. The suggestion I found was that IIS on the server (VM) had run out of memory.

Might be worth sending yourself the alert by email (Severity 17) to see if you do get more information - may help you determine the cause. Meanwhile, I'll keep digging into why I got mine...


Restoring multiple differential backups SQL Server 2008 Over the bank holiday the backups failed on a db the full has been restored and now I have several differentials to be restored on top.

Could someone give best method or do I restore just sequentially one after the other in  T-SQL? Are there pitfalls that I should be aware of prior to doing  this.

First thing you do is restore the FULL backup, and then you restore the latest DIFF backup.  That's it!  This is assuming no FULL backups were taken in between the DIFF and the FULL you are restoring from (only exception is if you used a COPY_ONLY option in your full backup but most likely that wasn't the case). Remember to use WITH NORECOVERY on the full restore so you could continue to restore the DIFF.

In the rare exception that the DIFF throws an error stating that the LSN's don't match and your full backup is not the right one, you can query MSDB or right click on the DB, go to 'reports', and 'backup/restore' history.  That'll tell if a full backup was taken in between but by the sounds of it that is not the case.  On the DIFF restore make sure to state WITH RECOVERY to replay the log.


postgresql replication - pg_stat_replication is showing empty columns I've a postgresql 9.2 streaming replication setup. It appears that the slave is getting the updates from master and is in sync. I've verified it by looking at pg_xlog dir and process list.

$ ps aux | grep 'postgres.*rec'
postgres 26349  2.3 42.9 38814656 18604176 ?   Ss   Sep20  24:06 postgres: startup process   recovering 000000010000026E00000073
postgres 26372  4.9  0.1 38959108 78880 ?      Ss   Sep20  51:27 postgres: wal receiver process   streaming 26E/731E05F0

And the startup logs on the slave also look alright.

2013-09-21 03:02:38 UTC LOG:  database system was shut down in recovery at 2013-09-21 03:02:32 UTC
2013-09-21 03:02:38 UTC LOG:  incomplete startup packet
2013-09-21 03:02:38 UTC FATAL:  the database system is starting up
2013-09-21 03:02:38 UTC LOG:  entering standby mode
2013-09-21 03:02:38 UTC LOG:  redo starts at 26E/71723BB8
2013-09-21 03:02:39 UTC FATAL:  the database system is starting up
2013-09-21 03:02:39 UTC LOG:  consistent recovery state reached at 26E/75059C90
2013-09-21 03:02:39 UTC LOG:  invalid xlog switch record at 26E/75059E98
2013-09-21 03:02:39 UTC LOG:  database system is ready to accept read only connections
2013-09-21 03:02:39 UTC LOG:  streaming replication successfully connected to primary

What worries me is that the pg_stat_replication table on the master. It shows that there is a client connected, but doesn't show anything more.

archive=> select * from pg_stat_replication;
 pid | usesysid |  usename   | application_name | client_addr | client_hostname | client_port | backend_start | state | sent_location | write_location | flush_location | replay_location | sync_priority | sync_state
-----+----------+------------+------------------+-------------+-----------------+-------------+---------------+-------+---------------+----------------+----------------+-----------------+---------------+------------
 999 |    16384 | replicator | walreceiver      |             |                 |             |               |       |               |                |                |                 |               |
(1 row)

Is this the expected behavior? I remember seeing values for client_addr, sent_location, replay_location etc. when I did a test run some time back. Is there anything that I'm missing?

From the prompt, it looks like you are not connecting as a superuser. To view the address information about the connection, you must be connected as superuser.


What does the repmgr the symbol "*" mean in the repmgr cluster status output? I'm new to postgres administration and was wondering what does the "*" symbol means in the repmgr cluster show command output.
 ID | Name    | Role    | Status    | Upstream | Location | Connection string
----+---------+---------+-----------+----------+----------+------------------------------------------------------------------
 1  | node-1  | standby |   running | node-2   | default  | host= [...]
 2  | node-2  | primary | * running |          | default  | host= [...]
 3  | node-3  | witness | * running | node-2   | default  | host= [...]

I didn't find anything about it in the documentation (https://repmgr.org/docs/4.0/repmgr-cluster-show.html)
Using repmgr cluster show --csv as mentioned in the documentation, I can see that the node with ID 1 is in recovery state "in recovery". Is it related ?
postgresql version : 10.5
repmgr version : 4.0

It's not documented, but the code is here; it looks like * running is used for the running state for primaries and witnesses, but not secondaries. I'm not sure how intended that is; it might have just been a copy/paste of the code block that was used for primary; here's the relevant commit that introduced the 2nd * running status string.


Why is this integer range selection so slow? I'm currently doing some inner joins in PostgreSQL, which I know aren't the speediest, but I've run into an efficiency issue.

The following query returns in 167ms

with structure as (
    SELECT s.id, s.terr_id, FROM structures s WHERE s.id=55
  )

SELECT r.id FROM territories t INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum > r.colnum - 2
    AND t.colnum < r.colnum + 2
)

This query returns 9 rows. The columns are filled with primitives.

The table territories has 5 million rows. The table resources has 400,000 rows. The structure query is negligible.

I have indices set up on all of the rows involved. When I simply query as such:

with structure as (
    SELECT s.id, s.terr_id, FROM structures s WHERE s.id=55
  )

SELECT r.id FROM territories t INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum = r.colnum
)

eliminating the colnum variation, the query returns 3 rows and only takes 11ms. If I were to run each row individually it would only take 33ms, a fraction of what it takes when combined. I've tried the BETWEEN keyword, using a box contains method, etc. Can anyone point me in the right direction?

First thing stop using CTEs.

with structure as (
    SELECT s.id, s.terr_id
    FROM structures s
    WHERE s.id=55
)
SELECT r.id
FROM territories t
INNER JOIN structure ON (t.id = structure.terr_id)
LEFT JOIN resources r ON (
    t.rownum > r.rownum - 2
    AND t.rownum < r.rownum + 2
    AND t.colnum > r.colnum - 2
    AND t.colnum < r.colnum + 2
)

Get's rewritten as..

SELECT r.id
FROM structures AS s
JOIN territories t
  ON (t.id = s.terr_id)
LEFT JOIN resources r
  ON (
    t.rownum BETWEEN (r.rownum - 1) AND (r.rownum + 1)
    AND t.colnum BETWEEN (r.colnum - 1) AND (r.colnum + 1)
  )
WHERE s.id=55;

And equijoins are always going to be faster

They never fan and they're easy to optimize.
They're direct index lookups.