MySQL execute statement returns 'NULL' Could you please advise why this query returns 'NULL' on BASH shell? Basically I need to script it so could you please also advise what's the best practices to execute queries in BASH script?

MySQL CLI

mysql> SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password
     > FROM mail
     > LEFT JOIN domains ON domains.id=mail.dom_id
     > LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;
+-------------------------------------+----------+
| email_address                       | password |
+-------------------------------------+----------+
| an@example1.com                     | password1|
| an@example2.com                     | password2|
+-------------------------------------+----------+
2 rows in set (0.00 sec)

BASH Shell

# mysql test -e "SELECT CONCAT(mail_name,"@",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"
+---------------+----------+
| email_address | password |
+---------------+----------+
| NULL          | password1|
| NULL          | password2|    |
+---------------+----------+

Whenever you are trying to concatenate a string with NULL, NULL is always returned, and for some reason @ is interpreted as NULL in MySQL. That is why the query returns NULL.

Since you wrap the string in the shell with " and you use it inside the query, the ones inside the query will be removed (literally, it is 3 strings that is being passed to MySQL).

You need to escape the quotation marks inside the query.

Like this:

# mysql test -e "SELECT CONCAT(mail_name,\"@\",name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"

or

# mysql test -e "SELECT CONCAT(mail_name,'@',name) AS email_address,accounts.password FROM mail LEFT JOIN domains ON domains.id=mail.dom_id LEFT JOIN accounts ON accounts.id=mail.account_id LIMIT 2;"


What Geopartioning or distributed SQL database can I use? I want to split medical data on multiple nodes because I have Swiss, Europeans and Americans and I need to store data in citizen country.

I don't want to manage this function in App but in the SQL database engine.
The user’s table has a country code and automatically data are stored on good nodes/data center.

Do you have any solution to do this ?

Since this is medical data, I am going to make the assumption that you can afford a bit of a budget on this project?

I would suggest that you could do worse than taking a look at CockroachDB - from here:

  With CockroachDB’s distributed SQL engine, your business can grow
  without ever manually sharding. Keep your services running with a
  database that automatically scales, rebalances, and repairs itself.

Their SQL engine is compatible with the PostgreSQL client, so anything that connects to PostgreSQL, can connect.

and with respect to Geo-distribution, you have from here:

  The easiest way to maintain compliance with global data regulations
  Keep customer data where it needs to be. With CockroachDB’s
  policy-based data controls like geo-partitioning, serving customers in
  ever-changing geopolitical environments has never been easier or more
  affordable.

and also this:

  Control Data Residency at the Row-Level Implement table and row-level
  policies that dictate where a user’s data resides using CockroachDB’s
  geo-partitioning.

They also have a managed offering if that would suit. I've used it for a Masters project and found it to be excellent - their documentation is superb (lovely graphic design!) and their support even for a lowly student on a free tier was great!

I also found that they were open about their product's limitations, but appear to be working well to overcome these. I've used it on AWS, GCP and Azure with no problems.

Also, when I said "budget" above, I meant over and beyond what you'd be paying for a cloud provider anyway. With CRDB, there is a free option, but I would suggest that with sensitive medical data, you should pay a bit more for the extra features of the pay version.

One of the guys who founded the company is Spencer Kimball who, as a student, began writing the Gimp (Gnu Image Manipulation Programme), Open Source's answer to Photoshop! He then worked on the Spanner/F1 system in Google!

If this sounds like a commercial, it's not! I'm just a happy user who was blown away with what you got on the free (and Apache licenced Open Source) tier!

If you already have a system that works off MySQL, you might want to look at TiDB! It's similar in scope to CockroachDB (not suprising as they're both based on Google's Spanner/F1 architecture). That is, if you can live with its limitations which are, IMHO, a bit severe for production still - but, hey, have a look - I've also used it on AWS, GCP and Azure also


How can I create a TRIGGER in MySQL from this Oracle syntax? I'm teaching myself RDMSs through a text book. I'm used to using MySQL so I'm sticking to it, but the book uses Oracle.

I'm trying to create a trigger in MySQL with the Oracle command

CREATE OR REPLACE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT
BEGIN
    UPDATE ON PRODUCT
        SET P_REORDER = 1
        WHERE P_ONHAND <= P_MIN;
END;
/

When I run the above, I get

  Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE OF P_ONHAND ON PRODUCT'

Basically in the PRODUCT table, if an INSERT or UPDATE is made, and the quantity on-hand (P_ONHAND) is less than the product minimum (P_MIN), a flag should be raised on P_REORDER.

I'm not sure how to go about putting this into MySQL syntax without separating the INSERT OR UPDATE, which would break the requirement of the command. Is this even possible in single TRIGGER with MySQL?

This is the best I can think of after going through the MySQL manual

delimiter //
DROP TRIGGER TRG_PRODUCT_REORDER;
CREATE TRIGGER TRG_PRODUCT_REORDER
AFTER INSERT OR UPDATE ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < P_MIN THEN
            SET P_REORDER = 1;
        END IF;
    END; //
delimiter ;

But I still get the error

  Error code 1064, SQL state 42000: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near
  'CREATE TRIGGER TRG_PRODUCT_REORDER AFTER INSERT OR UPDATE ON PRODUCT FOR EACH R' at line 2'

Any help would be appreciated.

Check the MySQL documentation about the trigger syntax and examples.

You cannot have AFTER INSERT OR UPDATE triggers in MySQL. Define 2 triggers, one for INSERT and one for UPDATE. Another limitation is that AFTER triggers cannot change the updated rows, so you can use BEFORE triggers. Like this:

delimiter //
DROP TRIGGER IF EXISTS TRG_PRODUCT_REORDER_BEFORE_INSERT ;
CREATE TRIGGER TRG_PRODUCT_REORDER_BEFORE_INSERT
BEFORE INSERT ON PRODUCT
FOR EACH ROW
    BEGIN
        IF NEW.P_ONHAND < NEW.P_MIN THEN
            SET NEW.P_REORDER = 1;
        END IF;
    END; //
delimiter ;

and similarly for the BEFORE UPDATE trigger


SQL admin Scripts - Enabling read only access to all databases I would like a script that will allow our web developers read only access to all databases on our SQL server.

Currently we are having to do this manually as we have no SQL expert / DB Admin.

Would somebody be able to help me out by creating a script that will add there windows user to SQL and Grant Read only access to all databases.

Occasionally they will need read / write access on a specific database so we can change this manually but I would like a script that we can schedule overnight that will change the access back if we forget.

Any help is much appreciated.

Regards

If you have a list of known usernames, you can store them in a table somewhere:

CREATE TABLE dbo.WebDevelopers(name SYSNAME PRIMARY KEY);

INSERT dbo.WebDevelopers(name)
  SELECT N'YourDomain\User1'
  UNION ALL
  SELECT N'YourDomain\User2';

Now you can create a variety of scripts to ensure that:

they exist as a login at the server level
for each relevant database:

they exist as a user
they are in the db_datareader role
they have SELECT permission on the dbo schema

Here is a start to solve 1. (I'll come back and address the others as I have time later today):

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'';

SELECT @sql = @sql + @cr + 'CREATE LOGIN '
 + QUOTENAME(d.name) + ' FROM WINDOWS;'
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN sys.server_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;

PRINT @sql;
-- EXEC sp_executesql @sql;

For the rest, this makes some basic assumptions: you always elevate privileges using database-level roles, rather than explicit GRANT/DENY, server role membership or AD group membership. Like the table of developers, you also want to have somewhere a list of the databases you want to affect:

CREATE TABLE dbo.DeveloperDatabases(name SYSNAME PRIMARY KEY);

INSERT dbo.DeveloperDatabases(name)
  SELECT N'some_database'
  -- UNION ALL N'...';

Now you can build a script that will drop the user from each database, create the entire list from scratch, and add each user only to the db_datareader role. You may want to customize the script if you want to grant explicit rights on a certain schema or set of objects.

DECLARE @sql NVARCHAR(MAX), @cr NCHAR(2) = CHAR(13) + CHAR(10);

SELECT @sql = N'SELECT @sql2 = N'''';';

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + ';
  DROP USER '' + QUOTENAME(d.name) + '';''
FROM dbo.WebDevelopers AS d
INNER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name;'
FROM dbo.DeveloperDatabases;

SELECT @sql = @sql + @cr + N'SELECT @sql2 = @sql2 + ''
  USE ' + QUOTENAME(name) + ';
  CREATE USER '' + QUOTENAME(d.name) + '' FROM LOGIN '' + QUOTENAME(d.name) + '';
  EXEC sp_addrolemember ''''db_datareader'''', '''''' + d.name + '''''';''
FROM dbo.WebDevelopers AS d
LEFT OUTER JOIN ' + QUOTENAME(name) + '.sys.database_principals AS p
ON d.name = p.name
WHERE p.name IS NULL;'
FROM dbo.DeveloperDatabases;

DECLARE @sql2 NVARCHAR(MAX);

EXEC sp_executesql @sql, N'@sql2 NVARCHAR(MAX) OUTPUT', @sql2 OUTPUT;

PRINT @sql2;
-- EXEC sp_executesql @sql2;

This is very rough, and lots of things can make it break. I could have combined the two selects into a single query but I think double-nested dynamic SQL was complex enough without adding conditional string concatenation in there as well.


Query To Compare Data I'm trying to build a query to show rows in a Customer table that match rows exactly in 2 columns (Address 1 and Address 2) but then show only if a 3rd column is not matching.

Customer ID Status  Address 1 Address 2
----------  ------ ---------- ----------
    2        Free   1 Main St. Apt 2
    3        Paid   1 Main St. Apt 2
    5        Free   2 Main St. Apt 1
    7        Free   2 Main St. Apt 1

So in the above data example I would want the query to only show that Customer IDs 2 and 3 match on Address 1 and Address 2 but do not match by Status. It would be like an exception report showing those data matches. Hope this makes sense.

You can join the table with itself:

SELECT c1.CustomerID, c1.Status, c2.CustomerID, c2.Status
  FROM Customers AS c1
  INNER JOIN Customers AS c2
    ON c1.Address1   =  c2.Address1
  AND (c1.Address2   =  c2.Address2 OR (c1.Address2 IS NULL AND
                                        c2.Address2 IS NULL))
   AND c1.Status     != c2.Status
   AND c1.CustomerID <  c2.CustomerID

The last line makes sure you don't get duplicate rows (unless there are more than two customers on the same address).

As suggested in the comments by Nick Roz, some fields could be NULL. Since NULL != NULL, you need to check specifically for those cases. In the query, I assumed CustomerID, Status and Address1 are never NULL, but Address2 can be.


Unable to connect from remote computer, while local connection works I have a Windows 2008 R2 64-bit server with SQL server 2008 R2 installed on it.
I manage to log to SQL server with SSMS with the sa SQL account and with a test SQL account.
But when I try to connect from another computer on the same network I have the following error for both accounts:

Cannot connect to 192.168.x.x.
Login failed for user 'sa'. (Microsoft SQL Server, Error: 18456)

From the Event Viewer I have the following detailed errors

Login failed for user 'sa'.
Reason: Password did not match that for the login provided. [CLIENT: 192.168.x.y]
Login failed for user 'test'.
Reason: Could not find a login matching the name provided. [CLIENT: 192.168.x.y]

The error with the sa account implies I entered the wrong password, while I know I entered the good one.
The error with the test account implies the test account doesn't exist while it exists.
Remote connections are allowed on the SQL server.
Name pipes and TCP/IP are also enabled.

Rather than using the IP, Use yourdomain\SQLinstance

Also have you allowed the TCP port inbound through the windows firewall on the server side?

Also change server authentication:

Lastly I would change the Pass just incase


Change the behavior of temporal tables to log actual value changes rather than dummy updates Currently the articles I read on temporal tables push the responsibility of the clean-up to the application code. Is it possible to configure the temporal tables to record particular column value changes instead of when the update statement is called on the table?

Example for the expected behavior:

USE Master;
GO

-- Create test database
CREATE DATABASE [TemporalTables];
GO

USE TemporalTables;
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- Hidden period columns
CREATE TABLE [dbo].[PersonHistory](
    [PersonID] [int] NOT NULL,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2  NOT NULL,
    [SysEndTime] datetime2 NOT NULL
);

CREATE CLUSTERED COLUMNSTORE INDEX IX_PersonHistory
   ON PersonHistory;
CREATE NONCLUSTERED INDEX IX_PersonHistory_ID_PERIOD_COLUMNS
   ON PersonHistory (SysEndTime, SysStartTime, PersonID);
GO

CREATE TABLE [dbo].[Person](
    [PersonID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    [EmailAddress] [varchar](75) NULL,
    [IsSubscribed] [bit] NULL,
    [SysStartTime] datetime2 GENERATED ALWAYS AS ROW START HIDDEN NOT NULL,
    [SysEndTime] datetime2 GENERATED ALWAYS AS ROW END HIDDEN NOT NULL,
    PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)
)
WITH
(
      SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.PersonHistory)
);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:11:56.6294004

--Insert Rows
INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed]) VALUES ('SD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] VALUES ('RD.Burman@gmail.com', 1);
GO

INSERT [dbo].[Person] ([EmailAddress], [IsSubscribed], [SysStartTime], [SysEndTime])
VALUES ('AR.Rehman@gmail.com', 1, default, default);
GO

-- Query the main table
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
-- Shows all changes for specific PK (for inserts to show up)
SELECT *, SysStartTime, SysEndTime
FROM dbo.Person
FOR SYSTEM_TIME ALL
where Personid IN (1,2,3);

-- Current Time UTC
SELECT sysutcdatetime();
-- Value: 2017-03-14 16:13:27.0374068

--Update Rows (Actual Updates)
UPDATE [dbo].[Person] SET [EmailAddress] = 'SD.Burman@hotmail.com' WHERE [EmailAddress] = 'SD.Burman@gmail.com';
GO
UPDATE [dbo].[Person] SET [EmailAddress] = 'RD.Burman@hotmail.com' WHERE [EmailAddress] = 'RD.Burman@gmail.com';
GO
--Update Rows (Dummy Update)
UPDATE [dbo].[Person] SET [EmailAddress] = 'AR.Rehman@gmail.com' WHERE [EmailAddress] = 'AR.Rehman@gmail.com';
GO

-- Query the history table
SELECT *
FROM dbo.PersonHistory

-- !!!
-- What I'd want is that the Id = 3 update to not get saved since the data did not change.
-- Also a way I can limit the number of columns I want this behavior on.
-- PersonID    EmailAddress                                                                IsSubscribed SysStartTime                SysEndTime
-- ----------- --------------------------------------------------------------------------- ------------ --------------------------- ---------------------------
-- 1           SD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5018193 2017-03-14 16:13:27.0374068
-- 2           RD.Burman@gmail.com                                                         1            2017-03-14 16:12:29.5118172 2017-03-14 16:13:27.1484051
-- !!!

Similar question highlighting this behavior:

Do temporal tables log changes when there are none?

I'm looking for a workaround for the mentioned behavior.

Change Data Capture would be able to handle this (or even custom triggers for that matter), but I'm exploring if I could get the benefits of temporal tables with selective history recording.

The challenge is, we have an ETL job that touches the syncdatatime columns often, and we want to exclude or include certain columns, as well only record when the actual data value changes, preferably with the temporal tables.

On a further dive it seems that this currently falls under : Manage Retention of Historical Data in System-Versioned Temporal Tables as a reactionary solution to the problem, instead of a prior configuration.

And they propose three approaches:

Stretch Database
Table Partitioning
Custom Cleanup Script

There are some other nice articles I found, which partially answer my question, however, I'd love if I can manage this behavior at the point of data creation rather than the cleanup, so I'd request more information in case someone has it, or if this changes in the future.

Other articles:
Managing Temporal Table History in SQL Server 2016
Temporal Table Considerations and Limitations


Most useful stored procedures for a new DBA? I am a new DBA working with several more experienced DBA's.  When I am scratching my head one of them says "oh just run SP_xx_xx", and sure enough that begins the path to solution.

Of course they all just know them, I have decided to start a list.  I have not been writting them all down, so all I have in my list is these three.

sp_Cycle_Errorlog
sp_whoisactive
sp_change_users_login 'report'

I Googled and came up with some very old lists

Useful system stored procedures in SQL Server(SO)
Two Dozen of My Favorite System Stored Procedures, By Kiely, Don

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014?

What are the best or most helpful Stored Procedures for a New DBA to have in their tool kit, for SQL 2012/2014?

You are asking Tools that are most useful to DBA

In no particuliar order ...

Brent Ozar Crew has sp_Blitz®, sp_BlitzCache®, sp_BlitzIndex®, sp_AskBrent®, sp_BlitzRS™, sp_BlitzTrace™ - This helps to do health check for sql servers.
To migrate sql server from one server to another use powershell - dbatools [it migrates over 25 components] from Chrissy LeMaire
To document sql server use - SQL Power Doc from kendalvandyke
For login transfer use Transferring Logins - By SQLSoldier
Monitoring SQL Server use sp_whoisactive from Adam Machanic
Analyzing Query plans - use SQL Sentry's Free Plan Explorer

The inbuild system SP are limited to what they can report or show. The best way is to create your own (use existing ones and build your own <-- make sure you give proper credit ! ) using DMVs.

e.g. sp_who is way ancient vs sp_whoisactive is what you will install on your server to quickly findout whats going on with the server.

Remove sp_change_users_login 'report' as it is deprecated. Instead you should use ALTER USER


How do you create a composite foreign key when key being referenced is also a foreign key If you have table a (column x) which is 1 to many with table b (columns x, y) which is 1 to many with table c (columns y, z), it's pretty straight forward to define the foreign keys.

If for performance reasons, table c was altered to be (columns x, y, z) is there a way to specify that table c (columns x, y) have a foreign key relationship to table b's (columns x, y)?

Previously table c contained 2 single column foreign keys x --> table a and y --> table b.  While this is correct, it creates erroneous results when trying to use commercial tools to create database diagrams.

You can create a composite foreign key from B to C:

alter table TableC
     add constraint FK_TableC_XY
     foreign key (x, y)
     references TableB (x, y)

That's better than two foreign keys: this will ensure that the combination of (x, y) is present, and not just the individual values.


What is the difference between Shrink Database and File? DBCC ShrinkDatabase()
DBCC ShrinkFile()

Do I need to run both DBCC commands in order to shrink the database?
What is the difference between these two above?

Simply...

DBCC ShrinkDatabase(): shrink all files
DBCC ShrinkFile(): just one file

For example, you may have a log backup issue and it's grown out of control so you run DBCC ShrinkFile().

You almost never use ShrinkDatabase.

Before you consider using either command, please read Paul Randal's blog on shrinking.

I'd shrink neither one of the files (mdf, ldf) unless there was a clear reason. The files are the size they are because they need to be. Any blogs suggesting to do so as part of regular maintenance probably don't understand how SQL Server works.


Commonly Used Acronyms by Database Administrators What are the most commonly used acronyms among database administrators and what are their correlated meanings?

This is for the community and those searching for meanings of commonly used terms and acronyms when working with databases, etc.

ACID – Atomicity, Consistency, Isolation, Durability

AIO Asynchronous I/O

BASE - Basically Available, Soft-State, Eventually Consistent...essentially a counterpart to (though not really "opposite" of) ACID, this is the core principle of most NoSQL implementations.

BI - Business Intelligence

BLOB – Binary Large OBject

CAP – Consistency, Availability, Partition tolerance...  The three requirements of a distributed system (as they apply to a database) according to Eric Brewer's CAP Theorem.

CDM – Copy Data Management

CI – Clustered Index

CK – Candidate Key

CLOB – Character Large OBject

CRUD – Create, Read, Update, and Delete

CS – Cursor Stability - an isolation level supported by different database management systems.

CTE – Common Table Expression

DB – Database

DBA – Database Administrator

DBMS – Database Management System

DCL – Data Control Language

DDL – Data Definition Language

DML – Data Manipulation Language

DMV - Dynamic Management Views

DR - Disaster Recovery

DRBD - Distributed Replicated Block Device

DRDA – Distributed Relational Database Architecture

DRI - Declarative Referential Integrity

DSS - Decision Support Systems

DTD – Document Type Definition

DW or DWH – Database Warehouse

EAV – Entity-Attribute-Value (aka. the archenemy)

ERD - Entity Relationship Diagram

ETL – Extract, Transform, Load

FDW - Foreign Data Wrapper (PostgreSQL)

FK – Foreign Key

FLWOR – For, Let, Where, Order, Return - an expression form used within XQuery to query XML within a database (not sure if DB2 only)

FS – Filesystem

FTS – Fulltext Search

GBP – Group Buffer Pool

HA – High Availability

HADR – High Availability Disaster Recovery

HDD – Hard Disk Drive

ICP – Index Condition Pushdown (MySQL)

IOPS – IO Per Second

IOT – Index Organized Table (Oracle)

ISAM - Indexed Sequential Access Method

I/O – Input/Output

JDBC – Java Database Connectivity

KV – Key/Value

LAMP - Linux, Apache, MySQL and PHP

LBAC - Label Based Access Control

LOB – Large OBject

LPAR – Logical Partition

LRU – Last Recently Used (algorithm)

LUN – Logical Unit Number

MDC – Multidimensional Clustering Table

MDM – Master Data Management

MDX – Multidimensional Expressions

MED – Management of External Data

MQT – Materialized Query Table (IBM DB2)

MV – Materialized View

MVCC – Multiversion Concurrency Control

NAS - Network Attached Storage

NCI – Non-clustered Index

NF - Normal Form (ie: 1NF, first normal form)

ODBC – Open Database Connectivity

ODS - Operational Data Store

OLAP – Online Analytical Processing

OLTP – Online Transaction Processing

OODBMS – Object-Oriented Database Management System

OOM – Out Of Memory

ORM – Object-Relational Mapping

OS – Operating System

PK – Primary Key

PL/pgSQL – Procedural Language/SQL (PostgreSQL) used for writing stored procedures. Similar to PL/SQL.

PL/SQL – Procedural Language/SQL (Oracle) used for writing stored procedures. Also see SQL PL.

QPS – Queries Per Second

RAC – Real Application Clusters (Oracle)

RAID – Redundant Array of Independent Disks

RBAR – Row By Agonizing Row

RDBMS – Relational Database Management System

RBR – Row-Based Replication (MySQL)

RPO - Recovery Point Objective - how much data you can afford to lose. If your server went down, this is the point at which you'd be able to recover the data.

RR – Repeatable Read - an isolation level supported by different database management systems.

RS

– Read Stability - an isolation level supported by different database management systems.

– Replica Set - multiple physical nodes forming a logical node with redundant data. Most commonly used in the MongoDB ecosystem

RTO - Recovery Time Objective - how much time it would take you to recover the data to the RPO

SAN – Storage Area Network

SBR – Statement-Based Replication (MySQL)

SCD – Slowly Changing Dimension

SE – Storage Engine (MySQL and forks)

SEQUEL – Structured English QUEry Language, which was IBM's precursor to SQL, which is why SQL is sometimes (often?) pronounced SEQUEL and not S.Q.L.

SP – Stored Procedure

SQL – Structured Query Language

SQL PL – SQL Procedure Language used for writing stored procedures. Also see PL/SQL.

SQL/XML – an extension of the SQL language used for querying XML.

SSD – Solid State Drive

TPS* - Transactions Per Second, a measurement of database performance.

UAT - User Acceptance Testing

UDF – User Defined Function

UDT – User Defined Type

UR – Uncommitted Read - an isolation level supported by different database management systems.

URLT - Update Resume; Leave Town - For those DBAs that don't bother putting together a proper recovery strategy

XML – eXtensible Markup Language

XSD – XML Schema Definition

XSLT – XML Stylesheet Transformation


SSIS Package display MessageBox in Task I have a SSIS package task that is displaying messageboxes even those that code in the task is commented out when I run it. How do I stop that?

You will need to disable interactive mode in the package. The answer to this question on Stack Overflow should point you in the right direction and the documentation for this property on Tech Net can be found here.

Alternatively, try right-clicking on the solution, Properties -> Configuration Properties -> Debugging -> Interactive Mode. Set this to false.


Can SQL Server Always Encrypted be used for temporary tables? I'm trying to determine if I can use Always Encrypted on a column in a temporary table (SQL Server 2016 SP1 Enterprise on Windows 2016).  I've tried as follows:

CREATE TABLE #listofdates (compare_dt datetime2(7) encrypted with (
    COLUMN_ENCRYPTION_KEY = [ColEnc1]
    , ENCRYPTION_TYPE = Deterministic
    , ALGORITHM = 'AEAD_AES_256_CBC_HMAC_SHA_256'
    ) NOT NULL
)

However, I get the error:

  Msg 33293, Level 16, State 8, Line 3
  Cannot find the column encryption key "ColEnc1" because it does not exist or you do not have permissions.

I think this error is misleading, because the statement works if I simply replace table name #listofdates with another name without the leading # (therefore switching from a temporary table to an ordinary table).

The section on the MS site that lists limits/restrictions on Always Encrypted doesn't mention temporary tables.


Reporting table needs to get refreshed without killing user's read connection to it, any ideas? I'm investigating a solution that will allow a reporting table to be refreshed that can potentially have users reading from it at a time that it needs to be refreshed. Has anyone implemented a way of doing something like this without needing to kill the user's connection to that table at the time of refresh?

Sure.  The simplest thing is to just make sure the reporting users using Row Versioning, either in SNAPSHOT ISOLATION, or by setting the database to READ_COMMITTED_SNAPSHOT.  See eg, Snapshot Isolation in SQL Server.

Then just make your updates in a transaction.  Reporting users will not be blocked, and will continue to see the "old" version of every row you change until you commit your transaction.

If the changes take a long time, you can alternatively build new tables, and once they are done start a transaction where you drop and rename the tables, or perform ALTER TABLE ... SWITCH to switch the table data out to a staging table, and switch the new data into the target table.


Non-Clustered Index takes much longer to create than clustered Index I have a procedure that creates a clustered and a non-clustered index on a table in order to do a partition swap.  The problem I have is the clustered index takes about 2 minutes to create:

CREATE UNIQUE CLUSTERED INDEX UCX_Idx ON myTable (
    IdCol1 ASC
    ,Col2 ASC
    ,IdCol4 ASC
    ,IdCol3 ASC
    ,Col1 ASC
    ,Col6 ASC
    ) ON PS_1 (IdCol1) END

But the non-clustered index takes about 1.5 hours to create:

CREATE NONCLUSTERED INDEX IX_Idx1 ON myTable (
    IdCol1 ASC
    ,IdCol2 ASC
    ,IdCol3 ASC
    ,IdCol4 ASC
    ,Col1 ASC
    ) INCLUDE (
    Col2
    ,DateCol1
    ,Col2
    ,Col3
    ,Col4
    ,Col5
    ,Col6
    )
    WITH (SORT_IN_TEMPDB = ON) ON PS_1(IdCol1) END

I'm not seeing this behavior with SQL Server 2014 but I am with SQL Server 2016.  Same amount of RAM and CPU.   I've tried it with out the SORT_IN_TEMPDB = ON but it has a similar problem.  I've actually been seeing this in different places in my environment and all with SQL Server 2016 (Standard Edition) installations.

Open Questions

What does the table's DD look like?
What order are the columns in?

Clustered Index Creation

When you create a clustered index, you are actually sorting the data in the table in the order of the clustered index you are creating.

  They are not ~a copy~ of the table, they are the table, ordered by the column(s) you choose as the key. It could be one. It could be a few. It could be a GUID! But that’s for another time. A long time from now. When I’ve raised an army, in accordance with ancient prophecy.

Reference: Clustered Index key columns in Nonclustered Indexes (Brentozar.com)

Non-clustered Index Creation

The column order of the clustered index is totally different from the order of the columns in the non-clustered index, which has a direct impact on the creation of the index. The database engine has to search through your clustered index multiple times until it has all the information together in the correct order to create the non-clustered index. That is why it is taking so long to create the non-clustered index.

Then there is the overhead that a clustered index is always part of the non- clustered index.

Differences between SQL Server 2014 and SQL Server 2016

As for the difference in index creation on the various versions: Are you really comparing apples with apples and not with pears? Or to put it differently: Are the SQL Server 2014 servers running on identical hardware with the same CPU core frequency, with the same amount of RAM and identical MAX_DOP settings?

There some limitations/restrictions regarding index creation when using Standard or Enterprise Edition...

Enterprise vs. Standard Edition

  Parallel index operations are not available in every SQL Server edition. For more information, see Features Supported by the Editions of SQL Server 2016

  [...]

  Parallel index execution and the MAXDOP index option apply to the following Transact-SQL statements:

  CREATE INDEX
  ALTER INDEX REBUILD
  DROP INDEX (This applies to clustered indexes only.)
  ALTER TABLE ADD (index) CONSTRAINT
  ALTER TABLE DROP (clustered index) CONSTRAINT

Reference: Configure Parallel Index Operations (Microsoft Docs)

If you then have a quick look at the features for SQL Server 2014 and 2016 then both editions limit MAXDOP for Standard Edition:

SQL Server 2014 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          -

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

SQL Server 2016 - Editions and supported features of SQL Server 2016 - RDBMS Manageability

                            Enterprise    Standard
Parallel indexed operations     Yes          No

Reference:Features Supported by the Editions of SQL Server 2014 (Microsoft Docs)

This option has a direct impact on index creation and this is probably the reason why you are seeing great differences in index creation time.

Thoughts

You might want to consider a re-design of your indexes. I'd recommend reading some of the following articles:

SQL Server Index Architecture and Design Guide (Microsoft Docs)
Where Clustered Index Keys Dare (Brenozar.com)
Nonclustered indexes require the “lookup” key in the b-tree when? (SQLSkills.com)
SQLskills SQL101: Indexing Basics (SQLSkills.com)

Additional Reading

Configure Parallel Index Operations (Microsoft Docs)
When should a primary key be declared non-clustered? (DBA.SE)


Does PgBouncer create a separate pool for every database config entry? If I set up a pgbouncer.ini config file with several database entries, does pgBouncer create a separate pool for each one?  I would think so, but have not been able to confirm this.

For example, with something like this:

[databases]
db1 = ...
db2 = ...

[pgbouncer]

.
.
.
max_client_conn = 480

default_pool_size = 250

Would I have a default pool of 250 for each DB and a max of 480, or a total of 250 default and max of 480 connections that are shared between both?

Thanks very much!

Yes, pgbouncer creates a separate pool for each database. You really don't want a pool size as big as 250 in a scenario like this - because that is 250 connections from pgbouncer to the postgres server for each database.

The max of 480 is the total number of connections from clients into pgbouncer, regardless of which database they go into.


Microsoft SQL Server, Error: 3205 I am getting this error while restoring the backup.

  Too many backup devices specified for backup or restore; only 64 are
  allowed. RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL
  Server, Error: 3205)

I have .bak file, I am using SQL Server 2008 R2.

Thanks.

I am sorry to say, but you have a corrupt backup file.  If you can, backup the database again, but use the "with verify" option to ensure it is fully functional.


Mysql Alias IF statement How do you query something like

if parent = 0 then alias is network_id else alias is group_id

Is this possible with only queries?

It's impossible to do what you want, and if not impossible, not a good idea. The columns in a resultset belong to every row in the resultset. You can't have a different alias for each row. That wouldn't make sense. Imagine a table in a spreadsheet that had a column header for each column and for each row. How would you even notate that?

It seems, though, that you want to be able to consider some upstream column to be a network_id in some cases and a group_id in others. I won't question your schema or query design, though it is suspect. If it's reasonable, then you can easily achieve this by simply having two columns: network_id and group_id. Only fill in network_id if parent = 0, and only fill in group_id if parent is not 0, like so (using standard SQL):

...
CASE WHEN parent = 0 THEN alias ELSE NULL END AS network_id,
CASE WHEN parent <> 0 THEN alias ELSE NULL END AS group_id,
...

Your application code can use whichever column needs to be used.


Programming query execution Postgres I need to execute update querys routinely over a Postgres table. Is it possible to program an automatic execution of the query, let's say, every day at 15:00 hs? The Postgres version is 9.5, I'm working on Windows 7.

There is no task scheduling functionality built into PostgreSQL.

You can use an extension like pg_cron, but I don't know how easy it is to build that on Windows.

The simplest way would probably to use the Windows task scheduler to start your job regularly.


Using XLOCK In SELECT Statements Is using XLOCK (Exclusive Lock) in SELECT statements considered bad practice?

Let's assume the simple scenario where a customer's account balance is $40. Two concurrent $20 puchase requests arrive. Transaction includes:

Read balance
If customer has enough money, deduct the price of the product from the balance

So without XLOCK:

T1(Transaction1) reads $40.
T2 reads $40.
T1 updates it to $20.
T2 updates it to $20.

But there should be $0 left in the account.

Is there a way to prevent this without the use of XLOCK? What are the alternatives?

I would consider it to be a bad practice unless you're 100% sure that it won't limit your effective concurrency. For example, I use TABLOCK all the time when I know that I'll be the only one accessing the table on a development database.

One technique that can help with the hypothetical issue that you called out is to write the transaction to use a single query instead of multiple queries. SQL Server will take a lock at the appropriate level (depends on the table structure) and I think that things will work out. Consider the following query:

BEGIN TRANSACTION

UPDATE Balance_table
SET balance = balance - 20
WHERE customer_id = ????
and balance >= 20;

COMMIT TRANSACTION;

For the default isolation level that query should prevent the situation that you called out from occurring.

For more complicated examples, you could consider using sp_getapplock to lock a resource at the account level. You could also try a UPDLOCK and HOLDLOCK pattern.


How DB index works internally at high level? I have around 500 GB of data in one table of MySQL which has around 5 billion records. It has around 15 columns. It has index on all the required columns. When I do select * from big_table where index_column = some_value it takes couple of minutes to return the data.

I am not sure how indexing works internally here.Here is my understanding

DB vendor will bring indexed column complete(not just data in where clause) data in memory first
Find the values(in where clause) under data fetched in step_1 to get the record row location in actual table.
Then another IO call will go to disk to get all required data based on row location fetched in step_2

Is that correct ? I am not sure on first step i.e. whether complete data of indexed column is fetched in memory or values are searched on disk itself without bringing the complete data in memory ?

This is an example for Db2, so a number of details will differ if you use another DBMS, but in general, it will look pretty much the same. Lets create a sample table:

create table test
( x int not null generated always as identity
, y int not null
, z int not null
);

create unique index testix on test (x);
insert into test (y,z)
with t(n,m) as (
    values (0, rand())
    union all
    select n+1, rand()
    from t where n<100000
) select 10000*m, 1000*m from t;

So, we have a table with 100000 rows. Let's examine the unique index, before we add any data we will have just the root node:

select nlevels, nleaf from syscat.indexes where indname = 'TESTIX'

NLEVELS NLEAF
1       1

NLEVELS is the height of the tree, and NLEAF are the number of leaf-pages. After adding data:

NLEVELS NLEAF
2       206

So we have a tree that in ASCII-art looks something like:

1         R
2   ... / | \ ...

The 206 leaf-pages address all data in the table. If we look at the plan for a query like:

select * from test where x = 1234

it will look like:

                Rows
              RETURN
              (   1)
               Cost
                I/O
                |
                 1
              FETCH
              (   2)
              13.637
                 2
           /----+-----\
          1           100001
       IXSCAN   TABLE:    LELLE
       (   3)          TEST
       6.82834          Q1
          1
         |
       100001
 INDEX:    LELLE
       TESTIX
         Q1

But what information did we touch to achieve this?

select num_executions, rows_read, pool_data_l_reads, pool_data_p_reads, pool_index_l_reads, pool_index_p_reads
from sysibmadm.snapdyn_sql
where stmt_text = 'select * from test where x = 1234'

NUM_EXECUTIONS       ROWS_READ            POOL_DATA_L_READS    POOL_DATA_P_READS    POOL_INDEX_L_READS   POOL_INDEX_P_READS
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
                   1                   16                   35                   12                   66                   30

ROWS_READ (16) is the number of rows (data) that we accessed for this query
POOL_DATA_L_READS (35) is the number of pages read from memory for data
POOL_DATA_P_READS (12) is the number of pages that could not be found in memory and had to be read from disk. In any normal situation, 1 logical read results in 0 or 1 physical reads.
POOL_INDEX_L_READS (66) is the number of pages read from memory for index
POOL_INDEX_P_READS (30) is the number of pages that could not be found in memory and had to be read from disk

So, even with this minimal amount of data in the table we only touch a fraction of pages (66) compared with the total number of pages (206+intermediate pages) for indexes.

In general, a read from the index will be a walk down the tree choosing a path at each new level.


Select query not giving result for inner query values Here is my schema and data

create table mytable (id numeric, val text);
create table mytable1 (id1 numeric, id text);

insert into mytable values (123, 'aaa');
insert into mytable values (124, 'bbb');
insert into mytable values (125, 'ccc');
insert into mytable1 values (1001, '[123]');
insert into mytable1 values (1002, '[123,124]');
insert into mytable1 values (1003, '[123,124,125]');

When I am running the below query, I am getting expected result.

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003

Result:

123,124,125

But, when I am passing the above query as inner query for a select query, I am not getting the result

select mt.val from mytable mt where mt.id = any (select string_to_array(trim(mt1.id, '[]'), ',')::numeric[] from mytable1 mt1 where mt1.id1 = 1003)

Expected result:

val
---
aaa
bbb
ccc

Anything wrong in the query?

(using Postgresql-9.1)

The problem is that:

select string_to_array(trim(mt1.id, '[]'), ',')::numeric[]

does not return an array, it returns a set of arrays (even though it's just a single row - it's still a set)

If you are sure that your sub-query always returns a single row, you can simply cast the result of the select to an array:

select mt.val
from mytable mt
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')::numeric[]
                   from mytable1 mt1
                   where mt1.id1 = 1003)::numeric[])

You actually don't need the cast twice, so the above can be simplified by removing the inner cast:

select mt.val
from mytable mt
where mt.id = any ((select string_to_array(trim(mt1.id, '[]'), ',')
                   from mytable1 mt1
                   where mt1.id1 = 1003)::numeric[])


Can changing database owner affect permissions or role memberships? A couple years ago we ran into a situation where we were changing the database owner from a DBA's login to sa, and immediately thereafter we received a complaint that the service login (which was NOT the DBA's login) no longer had access to the database.  On checking, the service login had indeed lost it's role memberships.

We only had 3 DBAs at the time, and nobody else had access to make a role membership change, so I'm pretty sure that someone did not specifically remove the service login from the roles.  However, I have not seen any warnings on articles about changing owner about something like this occurring, so I've not been able to explain it, and we couldn't reproduce the issue.

So, for example, the database owner was changed from DOMAIN\jdoe, and Windows login "DOMAIN\appuser" mysteriously lost it's built-in role membership.

Is there a situation where changing the owner of the database would cause permission or role changes?

Note that this question pertains to changing the actual database owner, NOT removing a login from the db_owner role.

If the user is the owner of the database, that user implicitly gets db_owner permission, without having to be explicitly assigned that role.

The owner of the database (a single user) is a different assignment than membership in the db_owner role, but have the same effect. I like to remember it as the owner of the database is an INSTANCE LEVEL setting (ie, it's in sys.databases, but db_owner role membership is a DATABASE LEVEL permission.

A user can be ONLY the (server level) database owner, and not have any permissions within that database. When that happens, changing the database owner to sa would effectively lock that user out of the database they previously owned. This would not revoke permissions from the user, but it would change the effective permissions.

To avoid this problem, you would need to add the user to the db_owner role for that database first, then change the owner.

In the context of your question, if the service account was the prior database owner, then this would explain a change in effective permissions, even though there were no changes to the explicit database-level permission/role assignments.


Index re/organize/Rebuild Report using Ola Is there any way we can generate report after running IndexOptimize - USER_DATABASES jobs.

I need a report which tells me which indexes have been rebuild/re-organised.

    Select * from commandlog

I had a look in the above table, but I am unable to identify which indexes has this actually performed rebuild/re-organize. Even though there is a command logged in the table, there are always few tables which didn't get rebuild/reorganized for different reasons like pagecounts etc..

Does anyone had any requirement like this. Can anyone share any script with me please.

You can find answer for this question at Ola Hallengren website itself. He has put lot of effort to make DBA's life easier and provided lot of parameters in order to customize and run the script as per user's requirement.

SELECT DatabaseName,
       SchemaName,
       ObjectName,
       CASE WHEN ObjectType = 'U' THEN 'USER_TABLE' WHEN ObjectType = 'V' THEN 'VIEW' END AS ObjectType,
       IndexName,
       CASE WHEN IndexType = 1 THEN 'CLUSTERED' WHEN IndexType = 2 THEN 'NONCLUSTERED' WHEN IndexType = 3 THEN 'XML' WHEN IndexType = 4 THEN 'SPATIAL' END AS IndexType,
       PartitionNumber,
       ExtendedInfo.value('(ExtendedInfo/PageCount)[1]','int') AS [PageCount],
       ExtendedInfo.value('(ExtendedInfo/Fragmentation)[1]','float') AS Fragmentation,
       CommandType,
       Command,
       StartTime,
       EndTime,
       CASE WHEN DATEDIFF(ss,StartTime, EndTime)/(24*3600) > 0 THEN CAST(DATEDIFF(ss,StartTime, EndTime)/(24*3600) AS nvarchar) + '.' ELSE '' END + RIGHT(CONVERT(nvarchar,EndTime - StartTime,121),12) AS Duration,
       ErrorNumber,
       ErrorMessage
FROM dbo.CommandLog
WHERE CommandType = 'ALTER_INDEX'
ORDER BY StartTime ASC


Use a set database schema or EAV design? I have already created a database design with a set schema.
The database schema is for a web quotation form. PS it is not a simple quotation form and hence need to customise the form, form builders like Gravity Form have been tried and suitable in this case.

I have about 15 tables (mostly in 2NF and 3NF) with set attributes and datatype.

But the question is should I stick with the traditional normalized schema design or use EAV design instead for some tables? It seems Gravity Form uses EAV DB design but that is due to the dynamic nature of a form builder.

This question has come up due to the fact that later on a attribute may need to be added, removed or updated from one or set of tables. The fields in the quotation form are reasonably standard with research done and I don't imagine modifying the fields and hence the attributes in the DB often.

I have read there are concerning pitfalls of EAV design. So if the fields are not going to changed often then is a set schema design with relationships useable?

Also in the event of modifying the schema is ALTER table going to cause any issues? If one adds a attribute to a table with ALTER table what would the previous data sets in the table have the value of the new attribute?
What are the alternative of ALTER table?

Well, wait until you see the ugly JOINs you will need.  I'm a strong opponent of EAV.  I have seen too many large EAV datasets groan under the inefficiencies.  However, since you have a tiny dataset, you might not run into the problems that others encounter.

Here's my blog on it, including one suggested alternative:  http://mysql.rjweb.org/doc.php/eav


MySQL table locks solution -> InnoDb / Partitions I'm experiencing many table locks in a MyISAM MySQL table that is pretty big (Constx10xGB and 15M rows).

It's mainly caused due many heavy inserts. My current thoughts to optimize:

Changing to Innodb - to avoid table level lock. In the past I've had bad experience with Innodb, possibly due to bad configuration. If you would recommend this option, please advise regarding configuration.
Adding partitions - we're running on one server, and we don't have different mounts in the HDs, so we're just avoiding locks in such a solution. Currently the table isn't 'key-value', but it can be done (normalization to different table).

Which would you recommend / other options?

Thanks.

Definitely go with InnoDB, and especially upgrade to MySQL 5.5 (if you're not already running it and if possible). There are many improvements in InnoDB performance in 5.5.

Frankly, your use-case of a table that is 10GB and 15million rows can easily be handled by InnoDB. Proper tuning is the stuff of another question though!

As a side note though, even if you partitioned it and left it as MyISAM, you would still get table-locking issues.

  For example, a SELECT from a partitioned MyISAM table causes a lock on the entire table.


Postgres chooses seq scan over index scan I have the following query:
SELECT *
FROM foo
  LEFT OUTER JOIN foo_history ON foo.id = foo_history.foo_id
WHERE foo.entity_id = 11111

foo table is around 8Million rows and foo_history is around 3Billion rows.
Postgres chooses the following plan
Gather  (cost=1000.58..132862.85 rows=9514 width=70) (actual time=462.013..5886.972 rows=14304 loops=1)
  Workers Planned: 4
  Workers Launched: 4
  ->  Nested Loop Left Join  (cost=0.58..130911.45 rows=2378 width=70) (actual time=380.838..1450.773 rows=2861 loops=5)
        ->  Parallel Seq Scan on foo  (cost=0.00..93746.74 rows=6 width=24) (actual time=366.122..366.168 rows=6 loops=5)
              Filter: (entity_id = 11111)
              Rows Removed by Filter: 1634858
        ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=3.905..192.879 rows=511 loops=28)
              Index Cond: (foo_id = foo.id)
Planning Time: 1.091 ms
Execution Time: 5891.124 ms

Which requires a seqscan over foo table (8Million row). But when disabling seqscan, postgres chooses a much faster plan:
Nested Loop Left Join  (cost=1.01..148667.31 rows=9514 width=70) (actual time=0.138..27.413 rows=14304 loops=1)
  ->  Index Scan using ix_foo_entity_id on od  (cost=0.43..8.46 rows=24 width=24) (actual time=0.048..0.090 rows=28 loops=1)
        Index Cond: (entity_id = 11111)
  ->  Index Scan using ix_foo_history_foo_id on foo_history foo_history_1  (cost=0.58..6005.63 rows=18849 width=46) (actual time=0.027..0.678 rows=511 loops=28)
        Index Cond: (foo_id = foo.id)
Planning Time: 1.135 ms
Execution Time: 28.927 ms

Which use the index over foo.entity_id to find the relevant rows.
The statistics don't seem off to me since they roughly match the ones obtained when running the query (or i'm misreading smth in EXPLAIN ANLYZE).
Also random_page_cost is set to 1.1 which should "encourage" postgres to do index scans.
So I don't know what's mileading postgres here. Maybe the cost of "Index Scan using ix_foo_history_foo_id" is too high ? But why would that be the case since random_page_cost is set to a reasonable value !
EDIT:
Postgresql version: PostgreSQL 12.5 on x86_64-pc-linux-gnu, compiled by gcc (Debian 8.3.0-6) 8.3.0, 64-bit
Tables definitions:
create table foo
(
    id integer default nextval('foo_id_seq'::regclass) not null
        constraint foo_pkey
            primary key,
    entity_id integer not null
        constraint foo_entity_id_fk_entity_id
            references entity
                on delete cascade,
    misc1 integer not null,
    misc2 integer not null,
    misc3 integer
);

create table foo_history
(
    id integer default nextval('foo_history_id_seq'::regclass) not null
        constraint foo_history_pkey
            primary key,
    foo_id integer not null
        constraint foo_history_foo_id_fk_foo_id
            references foo
                on delete cascade,
    history_day integer not null,
    misc1 integer default 0 not null,
    misc2 numeric(10,2) default '0'::numeric not null,
    misc3 numeric(10,2) default '0'::numeric not null,
    misc4 numeric(10,2) default '0'::numeric not null,
    misc5 integer
);

Data and context:

I have a table foo, each foo row being attached to an entity. And each foo having it's own history. The goal is to get all foo items and their history that are related to a given entiy.
foo.entity_id contains 586972 distinct values
Here is a histogram representing how many entities have between 0 and 20 foo items related to them, etc.

Also I've noticed that the following query

SELECT COUNT(*) FROM (
  SELECT *
  FROM foo_history
    LEFT OUTER JOIN foo ON foo.id = foo_history.foo_id
  WHERE foo.entity_id = 11111
) t

uses the 2nd plan (the fast one)

Yes, you are right, the problem is the high cost for the Index Scan using ix_foo_history_foo_id.  Observe that it estimates way too many result rows.
Perhaps it helps to
ANALYZE foo_history;

If that alone does not put the estimates right, try
ALTER TABLE foo_history ALTER foo_id SET STATISTICS 1000;
ANALYZE foo_history;

If that does not tilt the scales, perhaps you can INCLUDE some more columns in ix_foo_history_foo_id and replace the SELECT * with only those columns that you really need, so that you get an index-only scan.


Select count distinct on key column where multiple others satisfy condition I have a table where I am trying to find a count distinct on a column that is not a the primary key

account_id | media_id | sat_cond1 | sat_cond2
---------------------------------------------
123        |  333     |   Y       |     N
123        |  334     |   N       |     Y
123        |  335     |   N       |     N
124        |  221     |   N       |     Y
124        |  222     |   N       |     N
125        |  111     |   Y       |     Y

I'm trying to figure out a query that will return a count of account_ids where there's at least one row that satisfies sat_cond1 and at least one row that satisfies sat_cond2.

For instance, in the above table account_id 123 would, because media_id 333 satisfies sat_cond1 and 334 satisfies sat_cond2.  account_id 124 would not be counted, however, because though media_id 221 satisfies sat_cond2 there is no media_id that satisfies sat_cond1 for it.  account_id 125 meets all conditions on a single media_id, so it would be counted.

What sort of query would give me this?

Create groups with GROUP BY based on account_id. Find the MAX of sat_cond1 and sat_cond2 for each group. If the MAX values equal Y for both condition, keep the account_id. Count the distinct account_id values.

Above translated to SQL:

select
  count(*)
from
  (
    select
      account_id
    from
      data
    group by
      account_id
    having
      max(sat_cond1) = 'Y'
      and max(sat_cond2) = 'Y'
  )
;


How to avoid index scans in SQL Server 2005? I have a table named Cell which has columns like Cell_ID, CellValue and CellRow.

Cellrow can have values maximum 1 to 5. There is a clustered index on Cell_ID field. This table contains 100000 rows.

In my query I have to use the CellRow column in the WHERE clause like CellRow > 3 to get data from CellValue field. The problem is when I do it like this then it is always table scan that makes my query very slow.

I have also checked it by putting non-clustered index on CellRow field but still index scan because the CellRow field don't have so much values, it has only 1 to 5 values each time. I can't use CellID in the where clause.

I am using many tables in my query like

Table_1 is joining with table_2 by using clustered index column and table 2 is joining with table_3 by using non-clustered index column and table_3 is joining with Cell table by using non-clustered index.

The ending story is, I am getting different values from all the tables including cell table but when I use CellRow field in the WHERE clause, I am getting index scan.

Any solution :)

Thank you so much!

Any solution :)

No, not really.

As you said yourself - CellRow is just not very selective - 5 possible values, 100'000 rows = roughly 20'000 rows for each possible value.

SQL Server's query optimizer recognizes this and probably figures it's easier and more efficient to do a index scan rather than a seek for 20'000 rows.

The only way to avoid this would be to use a more selective index, i.e. some other column that selects 2%, 3% or max. 5% of the rows for each query.

PS: Check your execution plan - does it get the values straight from the index, or does it need a "Key Lookup" step to go get the data??

You didn't mention what data types your columns are - if the CellValue isn't too big, you could add it to the index (or at least include it in the index) to avoid costly key lookups:

CREATE INDEX IX_CellRow_CellValues
ON dbo.Cell(CellRow) INCLUDE(CellValue)

You'd still have the index scan, though


How to optimize query based on explain analyze in PostgreSQL? I am running the following query via EXPLAIN ANALYZE but it is taking a bit of a long time to run. Based on the output of EXPLAIN ANALYZE, what are some things I can do to my table to make this query execute faster?

select count(amount), sum(amount)
from mytable
where color_code = 5 and shade_type = 'Light';

EXPLAIN ANALYZE output:

 Finalize Aggregate  (cost=946640.31..946640.32 rows=1 width=40) (actual time=4799.256..4799.257 rows=1 loops=1)
   ->  Gather  (cost=946640.09..946640.30 rows=2 width=40) (actual time=4799.191..4800.566 rows=3 loops=1)
         Workers Planned: 2
         Workers Launched: 2
         ->  Partial Aggregate  (cost=945640.09..945640.10 rows=1 width=40) (actual time=4797.002..4797.003 rows=1 loops=3)
               ->  Parallel Seq Scan on mytable  (cost=0.00..945573.39 rows=13338 width=6) (actual time=656.722..4791.404 rows=10103 loops=3)
                     Filter: ((color_code = 5) AND ((shade_type)::text = 'Light'::text))
                     Rows Removed by Filter: 4888180
 Planning time: 0.257 ms
 Execution time: 4800.661 ms
(10 rows)

You need this index to makenthe query fast:

CREATE INDEX ON mytable (color_code, shade_type);

If you want to aim for an index only scan, you can use

CREATE INDEX ON mytable (color_code, shade_type) INCLUDE (amount);

But that is only useful if the table gets vacuumed often enough, and you don't need HOT updates for the amount column.


MySQL database getting connected with any username and password We have a website running on an custom Ubuntu server managed and hosted by the client himself, since last two days the website database is getting hacked and someone deletes all the db tables from the database.

Upon checking the Db connection I come to know that database is getting connected with any username and password, is there any configuration issue in mysql? I am not able to find anything on google or stackoverflow.

Any help would be appreciated. TIA.

EDIT: I checked mysql users table and there are three users in the table as listed below,

root
mysql.sys
mysql.session

As per suggested by Mr. Barmar it was causing due to --skip-grant-tables in MySQL.

To disable this option just run FLUSH PRIVILEGES; command in your MySQL CLI and this will solve the issue.


Why is 'Allow Snapshot Isolation' set to TRUE on the master and msdb databases? Why is the database property Allow Snapshot Isolation set to TRUE on the master and msdb databases, but FALSE on other system databases? The default option for this property is also FALSE when creating user databases.

I would imagine that it is enabled to minimize blocking in the master and msdb databases of the system processes.  It is disabled in user databases because in user databases it can greatly increase the disk IO requirements of the tempdb database.


What happens to a Trigger if a table is dropped and rename and recreated with same name What happens to a Trigger if a table is dropped and rename and recreated with same name?

For e.g:

We have a Trigger X which is define for Table A.
We have a Trigger Y which is define for Table B.

Trigger X is owned by SchemaA
Trigger Y is owned by SchemaB

Now everyday below steps a done during non-business hrs :

Drop Table B.
Rename Table A to Table B.
Create Table A.
Drop Trigger X & Y and Re-create same Trigger X & Y.

For some reasons above Step 4 was next executed means Trigger X & Y was not dropped and recreated. And everything seemed to be fine till the next day non-business activity started

Actual problem started when we were unable to drop Table B next day.

I wanted to know does the problem occurred because Trigger was not dropped and re-created.
Here I understand, When a table is dropped corresponding Triggers are dropped.

What exactly would have happened?

As per my understanding below things have happened
1. Drop Table B. (Table B is renamed as $BIN$B) (Now what will happen to its trigger)
2. Rename Table A to Table B.(Which trigger (X or Y) is Pointing to Table B NOW)
3. Create Table A. (Does Trigger X is still pointing to Table A)

As Trigger is owned by two different Schema what would have stopped from dropping Table B?

The SQL:2008, draft version, has this to say in Part 11, Schemata :

(in the description of the INFORMATION_SCHEMA table that describes triggers)

...

CONSTRAINT TRIGGERS_FOREIGN_KEY_SCHEMATA
FOREIGN KEY ( TRIGGER_CATALOG, TRIGGER_SCHEMA )
REFERENCES SCHEMATA,

CONSTRAINT TRIGGERS_REFERENCES_TABLES
CHECK ( EVENT_OBJECT_CATALOG <>
ANY ( SELECT CATALOG_NAME
FROM SCHEMATA )
OR
( EVENT_OBJECT_CATALOG, EVENT_OBJECT_SCHEMA, EVENT_OBJECT_TABLE ) IN
( SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME
FROM TABLES ) )

...

The first constraint merely states that a trigger cannot exist if the schema it is in does not itself exist,

The second one states that the EVENT_OBJECT_TABLE (the table from which the trigger is fired) must exist in the catalog and schema, or else if that other condition evaluates to true.

So dropping the table and leaving the trigger can only possibly be a valid option if "that other condition" evaluates to true [or unknown, but let's not get into that].  Let's take a look.

EVENT_OBJECT_CATALOG <> ANY ( SELECT CATALOG_NAME FROM SCHEMATA )

is a <quantified comparison predicate> as per chpt 8.9, pg 421 [Part 2, foundations] in my draft version.  The relevant part of the spec here is :

c) If the implied <comparison predicate> is True for at least one row RT in T, then “R <comp op> <some> T” is True.
d) If T is empty or if the implied <comparison predicate> is False for every row RT in T, then “R <comp op> <some> T” is False.

So, the condition becomes true if there exists at least one row in SELECT CATALOG_NAME FROM SCHEMATA whose catalog name differs from the catalog that the triggering table is in.

So what all of this seems to say is :

If you have only one single CATALOG_NAME in your database, then dropping a table while leaving a trigger that is fired off of it, is invalid.  Either the DROP should be rejected or it should something like CASCADE DELETE its corresponding triggers.

If you have some CATALOG_NAME in your database that is different from the CATALOG_NAME that the triggering table is in, dropping a table while leaving the trigger seems perfectly OK (oddly enough, I must say).

Final disclaimer : Since this is a rule that derives from Part 11 of the SQL standard, of course it only applies to products that claim to support Part 11 of the standard.


How to create trigger with NOT FOR REPLICATION I tried adding "NOT FOR REPLICATION " in many parts in the below code but ended up in the below code. I searched a lot for an example where I can see how its done with a trigger.

Can any one show me how I can modify the below code to add option NOT FOR REPLICATION in the below code.

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
IF (NEW.updated_replication < OLD.updated_replication) THEN
CALL raise_error;
END IF;
SET NEW.updated_replication = NOW();
END //

Or how can I alter this trigger to make it not for replication(Alter Query). I have spent hours trying this but no results.

You have two options

OPTION #1 : Make the Column Immutable

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        NEW.updated_replication = OLD.updated_replication;
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

OPTION #2 : Break the Trigger Midstream

DELIMITER //
CREATE TRIGGER update_table
BEFORE UPDATE ON table
FOR EACH ROW
BEGIN
    IF (NEW.updated_replication < OLD.updated_replication) THEN
        SELECT dummy FROM information_schema.tables WHERE table_name='whatever';
    ELSE
        SET NEW.updated_replication = NOW();
    END IF;
END //
DELIMITER ;

The line

SELECT dummy FROM information_schema.tables WHERE table_name='whatever';

should break the trigger since there is no column called dummy in information_schema.tables

This is a technique I have written about before

Apr 25, 2011 : Trigger in MySQL to prevent insertion
Dec 23, 2011 : check constraint does not work?

GIVE IT A TRY !!!


What is an "unmentionable word" to SQL? What is an "unmentionable word" to SQL?

I am reading sys.query_store_query_text (Transact-SQL) and I see the following, so I google {SQL "unmentionable" word}

  has_restricted_text
  bit
  Query text contains a password or other unmentionable words.

Other then 3 links that take me back to where I found it, I don't find anything that appears to show a relationship to a password or anything that SQL might care about.

Changing my answer, as I was incorrect in my previous assertion after testing. I believe this is another way of expressing that the value is either a password or another form of ciphertext (encrypted value) instead of a plaintext value.


Merge replication - can't create snapshot – timeout I have a SQL Server 2008 database, and I need a merge replication because I want to sync with mobile devices afterwards.

So I created a replication but when it comes to start the snapshot agent, the agent tries to start for about 20 minutes and then it shows the message:

The replication agent has not logged a progress message in 10
minutes. This might indicate an unresponsive agent or high system
activity. Verify that records are being replicated to the destination
and that connections to the Subscriber, Publisher, and Distributor are
still active.

There aren't any other error messages, neither in the snapshot-agent-status-window nor in the agent-log-window.

I don't have the administrator of the domain, but the local administrator and a domain user with admin-privileges. Both have all rights to database, are in the access-list of the replication.

The server agent runs on the local administrator-account and there are *3 Merge Replications   working on the server *

The job runs also under the local administrator.

So it works again...

Maybe someone else has got the same issue one day, so i post the solution here:

I researched on the server and found out, the sql server service is running under a local user. The reason for this is, that there were problems with the backupsystem, used by our customers and so they changed it years ago.

Because of the local user account the 15404-Error occures.

Knowing, that i mustn't use domain-accounts, I also solved the initial problem with my snapshot-agent. I searched for hours (nearly days ;) ) and it was just this little change:

When the Replication is created, the job is created too. The job has three steps. The Job-owner is the local-admin, also for the server-agent-service. But the second step of my job (replictionsnapshot) has one setting: run as. And by default this isn't the job-owner but the user running the creation, in my case my domain-account.

Now, that I set it to the local-administrator as well everything works fine again.

Thanks, Karl


How can I optimize my mysql setup to create my index faster? I have a server running Ubuntu 10.04 with Mysql 5.1x installed via package.  The system has 128GB ram, 8 cores, and has 4TB's free space where Mysql & Mysql tmp are stored.

I have an MyISAM like this:

CREATE TABLE `data_store` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `uniqname` varchar(150) NOT NULL,
  `data` blob,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM CHARSET=latin1;

I inserted 800mil records (about 350gb data before inserting), then attempted to add the following index:

ALTER TABLE data_store DISABLE KEYS;
ALTER TABLE data_store ADD INDEX uniqname_index (uniqname);
ALTER TABLE data_store ENABLE KEYS;

(Regarding the DISABLE KEYS command, I saw that suggested elsewhere to use BEFORE inserting data, and since the command never got to ENABLE KEYS, I assume it is serving no purpose for me.  I've mainly just included to be thorough in my description of what I'm doing.)

When I start the index job, the first status that shows in SHOW PROCESSLIST is "copying to tmp table".

After a few hours, I check bac, and the status remains on the "Repair With Keycache" message even after 24 hours.  I tried running the job on a slightly older server, and after 3 days, it still remains with status "Repair with keycache". Because of this, I've canceled the create-index command on this newer machine.

I've read that the "Repair With Keycache" can be very slow, and "Repair by sorting" is preferred in many cases.

Based on some Stack Exchange and random posts online, I added the following settings to my Mysql server:

myisam_sort_buffer_size = 80G
bulk_insert_buffer_size = 80G
myisam_repair_threads   = 8
max_heap_table_size     = 20G
myisam_max_sort_file_size = 500G
tmp_table_size          = 20G
key_buffer_size         = 20G
sort_buffer_size        = 20G
join_buffer_size        = 20G

I restarted the job, and the same process happened again (copy to tmp file, then repair via key cache).

After I killed the job, I noticed in mysql/error.log, there is a message, "myisam_sort_buffer_size is too small". This happened earlier in the day, and not exactly when I killed the job.

questions

Am I going about this wrong way?  I just want to be able to lookup my data quickly by some key (uniqname).
Is there any benefit to starting over w/ my table, adding the index from the beginning, use the DISABLE KEYS command, insert my 800mil records, and then ENABLE KEYS?  I read elsewhere that this can prevent the copying tmp table (which may only save me a few hours?)
Do I want this 'Repair by sorting'?

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.

Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:

Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

Next, create a folder called /mysqltmp and mount the RAM disk on it

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

Add this to my.cnf and restart mysql

[mysqld]
tmpdir=/mysqltmp

Now, any tmp table made via DDL lands in the RAM disk.

Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

This will prevent any moving around of BLOB data when indexing.

From here, you would have to always join data_store using its name like this:

SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

Making these changes will sidestep this whole mess of dealing with keycache, RAM disks,  and tmp tables.

Give it a Try !!!


postgres backup for specific table with blob oid I have a table which has an oid column. When I'm taking a backup of this table, it takes complete oids (complete blob / oids backup). But my table only has 10 entries (which is approximately 30 entries in pg_largeobject table). I read the following link and added -o and -b to the backup parameters.

https://www.postgresql.org/docs/9.1/static/app-pgdump.html

I am using below command to take backup.

 pg_dump -i -h myhost -p 5432 -U myuser -F c -v -t schema.my_table -o -b -f mytablebkp.backup "mydb"

Is anything wrong with that command? How can I take a backup of only the specific table with only those blobs that belong to it?

Update:

I read the following from documentation (https://www.postgresql.org/docs/9.5/static/app-pgrestore.html),

pg_restore cannot restore large objects selectively; for instance, only those for a specific table. If an archive contains large objects, then all large objects will be restored, or none of them if they are excluded via -L, -t, or other options

Since pg_restore can not do selective restore, is there no way / possibility to take selective table backups with blob / oid ?

The oid column are avaliable in all tables, and I believe thats not a issue.

To create a logical dump of this table, you just need add a option -b (or --blobs in the pg_dump arguments, eg:

pg_dump \
  -U user\
  -t table_name \
  --blobs \
  -f dump_file_name \
  database_name

Another options will be useful is the -F c (or --format=custom) to change the default format from plain to custom.

Please take a look at the documentation for more details:
https://www.postgresql.org/docs/current/static/app-pgdump.html


How to add up a column depending on data from another column I have a table which has results and a table that has band_name and band_id.

Now I need to create a query which will add up the total points for each band and display the band name and ID, so far I have

SELECT
  SUM(placing.points) AS sumpoints,
  band.band_id
FROM placing, band
GROUP BY band.band_id
ORDER BY SUM(placing.points) DESC

The results end up giving me each unique band_id but the total of all the points on all bands as oppose to the bands points.

The problem is that you are not joining your two tables based on band_id, as a result you are creating a Cartesian product.  When you create this cartesian product, you are combining each row from your placing table to each row in the band table.

For example, if you have data similar to the following:

create table band
(
  band_id number
);

insert into band values (1);
insert into band values (2);
insert into band values (3);
insert into band values (4);
insert into band values (5);

create table placing
(
  band_id number,
  points number
);

insert into placing values (1, 10);
insert into placing values (1, 2);
insert into placing values (2, 1);
insert into placing values (3, 3);
insert into placing values (3, 89);
insert into placing values (4, 45);
insert into placing values (5, 63);

And you query the data without a join on the band_id column:

SELECT
  placing.points,
  band.band_id
FROM placing, band;

You are generating data similar to:

| POINTS | BAND_ID |
|--------|---------|
|     10 |       1 |
|      2 |       1 |
|      1 |       1 |
|      3 |       1 |
|     89 |       1 |
|     45 |       1 |
|     63 |       1 |

See Demo. As you can see band_id = 1 now has every single point value from the placing table even though your really only have points = 10 and points=2.

In order to get the correct result, you need to JOIN the two tables on the band_id column. Your query will be similar to the following:

SELECT
  SUM(p.points) AS sumpoints,
  b.band_id
FROM placing p
INNER JOIN band b
  on p.band_id = b.band_id
GROUP BY b.band_id
ORDER BY SUM(p.points) DESC;

See SQL Fiddle with Demo. This will give a result of:

| SUMPOINTS | BAND_ID |
|-----------|---------|
|        92 |       3 |
|        63 |       5 |
|        45 |       4 |
|        12 |       1 |
|         1 |       2 |


How to select only the first distinct value in a column, but have blanks in that column in the other rows otherwise Is there any way that I can turn a table like this with a query like

SELECT name,child from People

|----------------------|
| name    |   child    |
| John    |   Jack     |
| John    |   Jerry    |
| Carol   |   Carol    |
| James   |   Carl     |
| James   |   Lawrence |

into this?

|----------------------|
| name    |   child    |
| John    |   Jack     |
|         |   Jerry    |
| Carol   |   Carol    |
| James   |   Carl     |
|         |   Lawrence |

Thanks!

For mysql at least:

SELECT
(
    CASE WHEN child = (SELECT child FROM people AS inner_people WHERE inner_people.name = people.name ORDER BY people.name, people.child LIMIT 1)
    THEN name
    ELSE ''
    END
) AS name,
child
FROM people ORDER BY people.name, people.child;


Best practices for transferring data across long, wide tables As part of my data warehouse project, I'll be transferring data from our OLTPs to the Data Warehouse. Some of the tables are long and wide so obviously, I'll only be transferring required columns.

To reduce the overhead, I'm considering data tracking on these tables so we only look at changed values rather than scanning the complete table. Unfortunately our source system runs on SQL Server 2014 Standard Edition and CDC is an option we can not take advantage of.

Is there an alternative method that would be better than change tracking?

Have you considered adding a ''rowversion'' column to your source tables?
The simplest way to think about rowversion (formerly timestamp in SQL Server) is as a database-wide autonumber.  Every INSERT and UPDATE increments the database-wide rowversion.  Any table that has a rowversion column defined has that column's value set to the database rowversion when the associated row is INSERTed or UPDATEd.
Here's a brief algorithm for how you could use this:

Save the database-wide rowversion value (available via @@DBTS; i.e., DataBase TimeStamp)
Process your data into your Data Warehouse
Next time through, only process rows where the rowversion value is greater than the previously saved @@DBTS

Note that while you should not miss any rows that should be processed, you may re-process rows with no actual change in data.  This is because the rowversion is incremented with every UPDATE statement, even if the data is "UPDATEd" to its current value and not actually changed.
Also, it won't help identify which columns might have been changed.  As the name implies, it works strictly at the row level.


Slow SELECT examining whole table Can you please help me to understand, why this select examines 828004 rows (whole database) to return 21 rows?

# Time: 161121 14:00:44
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 2.507397  Lock_time: 0.000089 Rows_sent: 21  Rows_examined: 828004
SET timestamp=1479733244;
SELECT DATE(FROM_UNIXTIME(Epoch)) AS NodeDate, DeviceID as NodeID, Description,
AVG(AD0) AS AverageAD0,AVG(AD1) AS AverageAD1
FROM Data
WHERE DATE(FROM_UNIXTIME(Epoch)) >= CURDATE()
AND HOUR(FROM_UNIXTIME(Epoch)) >= HOUR(current_time())
GROUP BY NodeDate, NodeID
ORDER BY AVG(AD0) DESC, AVG(AD0) DESC;

Data table structure

mysql> DESCRIBE Data;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| Id          | int(10)     | NO   | MUL | NULL    | auto_increment |
| Epoch       | int(10)     | NO   |     | NULL    |                |
| Address     | varchar(32) | NO   |     | NULL    |                |
| DeviceID    | varchar(32) | NO   |     | NULL    |                |
| Description | varchar(32) | NO   |     | NULL    |                |
| AD0         | int(10)     | NO   |     | NULL    |                |
| AD1         | int(10)     | NO   |     | NULL    |                |
| AD2         | int(10)     | NO   |     | NULL    |                |
| AnalogZero  | int(8)      | YES  |     | NULL    |                |
| Lat         | varchar(32) | NO   |     | NULL    |                |
| Lon         | varchar(32) | NO   |     | NULL    |                |
| RSSI        | varchar(8)  | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
12 rows in set (0.00 sec)

Explain

+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows   | Extra                                        |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+
|  1 | SIMPLE      | Data  | ALL  | NULL          | NULL | NULL    | NULL | 822772 | Using where; Using temporary; Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+--------+----------------------------------------------+

Indexes

mysql> SHOW INDEX FROM Data;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Data  |          1 | Id       |            1 | Id          | A         |      839767 |     NULL | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.00 sec)

The select is used to obtain averages from today AND current hour+.

This query is also examining all rows and looks super-simple. I have a 2-Core VPS and that is not handling such simple queries.

SELECT AVG(AD1) AS AverageAD1,AVG(AD2) AS AverageAD2, AVG(AD0) AS AverageAD0
FROM Data
WHERE DeviceID="xxxxxxxx"
AND FROM_UNIXTIME(Epoch) >= NOW() - INTERVAL 60 MINUTE;

You want information based information that is in the Epoch field. Not even the whole field but just on DATE(FROM_UNIXTIME(Epoch)) and HOUR(FROM_UNIXTIME(Epoch)). Unless you have an index on that information the database will always need to read every row of the table to see if the row fits the criteria.

Adding an index on Epoch as a whole will not help too. The DATE(FROM_UNIXTIME(Epoch)) can be bypassed with (if it exists) TO_UNIXTIME(CURDATE()) but the HOUR(FROM_UNIXTIME(Epoch)) needs a specific index.


Extract street address from string with multiple delimiters and positions Trying to pull just the Street addresses out of this data:

CREATE TABLE foo(Places varchar(50));
INSERT foo(Places) VALUES
('MARKET @123 NORTH RD'),
('HARDWARE @600 Main RD ;W1'),
('MAIN AV / NORTH RD'),
('500 NORTH RD @LIBRARY'),
('500 ANYSTREET ; *** SIDEWALK****'),
('MARKET @123 NORTH RD'),
('700 ANYSTREET'),
('(088.12345,088.12345) ;75 SOUTH RD');

I've trying to do use CASE, SUBSTRING and (CHAR/PAT)INDEX... But I'm just not doing this correctly.

Select
[Address] = CASE WHEN PATINDEX('%@[0-9]%' , Places) = 0 THEN     (SUBSTRING(Places, 0, CHARINDEX('@',Places)))
                WHEN PATINDEX('%@[a-z]%' , Places) = 0 THEN     (SUBSTRING(Places, CHARINDEX('@',Places) + 1, LEN(Places)))
                WHEN PATINDEX('%;[0-9]%' , Places) = 0 THEN (SUBSTRING(Places, CHARINDEX(';',Places) + 1, LEN(Places)))
                WHEN PATINDEX('[0-9]%' , Places) = 1 THEN (SUBSTRING(Places, 0, CHARINDEX(';',Places)))
                WHEN PATINDEX('%/%' , Places) > 0 THEN Places END

From dbo.foo

What is the better way to grab that data? I've looked at string selects for finding email addresses, but seem to fall flat again when dealing with the ';'

TIA!

edit for desired output, table containing:

123 NORTH RD
600 Main RD
MAIN AV / NORTH RD
500 NORTH RD
500 ANYSTREET
123 NORTH RD
700 ANYSTREET
7575 SOUTH RD

Try this.

;With cte as
(
Select
    places,
    StartHere =
        CASE
            WHEN PATINDEX('%@[0-9]%', places) > 0 THEN PATINDEX('%@[0-9]%', places) + 1     -- @ plus number, start here
            WHEN PATINDEX('%;[0-9]%', places) > 0 THEN PATINDEX('%;[0-9]%', places) + 1     -- ; plus number, start here
            ELSE 0
        END
    ,EndHere =
        CASE
            WHEN PATINDEX('%@[a-z]%', places) > 0 THEN PATINDEX('%@[a-z]%', places)   -- @plus letter, stop here
            WHEN PATINDEX('%@[0-9]%', places) > 0 AND PATINDEX('%;%', places) > 0 THEN  PATINDEX('%;%', places)  --@ plus number stop on semicolon if there
            WHEN PATINDEX('%;[0-9]%', places) = 0 AND PATINDEX('%;%', places) > 0 THEN PATINDEX('%;%', places)   --no semi plus number, so stop on semicolon if there
            ELSE len(places) + 1
        END
FROM
    foo
)
    SELECT
        places,
        StartHere
        ,EndHere
        ,SUBSTRING(places, StartHere, EndHere - StartHere)
    FROM
        cte


Check Orders that can be delievered with reducing quantity from stock Just like any retail business we have an Orders table and an Inventory table. What I am trying to do is to check Orders for which we have enough stock available to dispatch. A few things I need to consider:

If all the items in an order are available only then consider this order to be “Deliverable”

Check Order's deliverable status in the order of OrderID (int value) .i.e OrderID 1 then 2 and so on.

Before checking for deliverability of next order, reduce the available of stock for the next order. (not update the Inventory table but just take into account the stock quantity that has been already consumed by previous orders).

If we do not have enough stock for 1 or more items in the order, completely ignore the order and do not reduce available stock quantity for the next order to be checked.

In the following example:

Order 100 is fully Deliverable because we have enough stock for all products.
Order 200 is not fully Deliverable because PID 2 requires Qty 5 but we only have 3 left after 2 being consumed by the Order 100
Finally, Order 300 is also fully Deliverable because we have enough stock for all products.

Test Data
INSERT INTO @Inventory(PID, Qty)
VALUES  (1  , 10)
    ,   (2  , 5)
    ,   (3  , 2)

INSERT INTO @Order (OrderID, PID, Qty)
VALUES  (100 , 1 , 2)   --\
    ,   (100 , 2 , 2)   ----> This order is fully available
    ,   (100 , 3 , 1)   --/

    ,   (200 , 1 , 2)   --\
    ,   (200 , 2 , 5)   ----> This order is not fully available
    ,   (200 , 3 , 1)   --/     because of PID 2 only 3 QTY left

    ,   (300 , 1 , 2)   --\
    ,   (300 , 2 , 2)   ----> This order is fully available
    ,   (300 , 3 , 1);  --/

Expected Output
OrderID Status
100     Deliverable
200     NOT Deliverable
300     Deliverable

My Attempt
I know that it is far from the actual solution but I still wanted to share what I have been trying :)
WITH OrderCTE AS
    (
        SELECT DENSE_RANK() OVER (ORDER BY OrderID) AS OrderRN
            , OrderID
            , PID
            , Qty
        FROM @Order
    )
,CTE AS
(
    SELECT o.OrderID
         , o.PID
         , o.Qty
         , i.Qty - o.Qty AS QtyAvailable
         , o.OrderRN  AS OrderRN
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    WHERE o.OrderID IN (
                            SELECT TOP 1 o.OrderID
                            FROM @Order o
                            WHERE NOT EXISTS ( SELECT 1 FROM @Inventory i WHERE i.PID = o.PID AND i.Qty < o.Qty)
                            ORDER BY o.OrderID
                        )

    UNION ALL

    SELECT o.OrderID
         , o.PID
         , o.Qty
         , o.Qty - c.QtyAvailable
         , c.OrderRN + 1
    FROM OrderCTE o
    INNER JOIN @Inventory i ON i.PID = o.PID
    INNER JOIN CTE c ON c.OrderRN + 1 = o.OrderRN AND c.PID = o.PID
    WHERE o.Qty <= c.QtyAvailable

)
SELECT *
FROM CTE

I'm not a big fan of cursor but it seems like a good case for it.
Declare OrderCursor Cursor
Local Static Read_Only Forward_only
For select distinct OrderID from @Order order by OrderID;

declare @OrderID INT;
create table #Remaining_Inventory  (PID int, Qty int);
create table #Result (OrderID int, Stat varchar(20));

insert into #Remaining_Inventory
select * from @Inventory;

OPEN OrderCursor
FETCH NEXT FROM OrderCursor INTO @OrderID;

    WHILE @@FETCH_STATUS = 0
    BEGIN

    if not exists (
    select 1
    from @Order o
    left join #Remaining_Inventory i on o.PID = i.PID and o.Qty <= i.Qty
    where o.orderid = @OrderID
    and i.PID is null
    )
        begin
            insert into #Result select @OrderID, 'Deliverable';
            update #Remaining_Inventory set #Remaining_Inventory.Qty= #Remaining_Inventory.Qty-o.Qty
            from #Remaining_Inventory
            join @order o on #Remaining_Inventory.PID = o.PID;
        end
    else
        insert into #Result select @OrderID, 'Not Deliverable';

        FETCH NEXT FROM OrderCursor INTO @OrderID
    END

    select * from #Result;

    drop table #Remaining_Inventory;
    drop table #Result;
    CLOSE OrderCursor
    DEALLOCATE OrderCursor

will return the expected result.


Replicating data to a head office with limitations Here's my situation.  We have cash registers that send transactions to the store server.  With every transaction received, the store server updates tables... at the same time other management transactions modify tables.  At the end of the night we send everything to the head office for which they have all the info for all the stores.  usually stores and cash registers only use SQL Express (we support 2008 and up) as for the central head office DB, they usually use SQL Server Standard edition and up (again we support 2008 and up).

I'm trying to revamp that process due to the fact that right now our process requests from the stores all data of days that have been modified and that for 25 tables out of hundreds. The problem is that if 1 record changes for a previous day we send all data of that day that was touched for all 25 tables.

These 25 tables are daily summaries that are updated live at the store and the changes are sent to the head office once a night usually at midnight.

My first instinct was to create triggers on each of the 25 tables until I realized that every item of a transaction will trigger 25 different triggers.
Which quickly became unattractive.

my next try was to send each transaction once to the head office and replay everything at the head office, but that will be too much of a load at the head office since they might be receiving data from 200 stores all at once.

Ideally, I'm looking for a way to transfer only data from the 26 tables that NEED to be transferred.  Maybe by having the head office compare some type of Checksum of every table with a store's checksum of the same tables (SQL checksum won't work since the head office will always have more data).

I'm asking for different opinions to hopefully find other ideas I can explore.

very much appreciated.
Thank you

You might be able to do something with actual SQL replication, but if you've already got an existing routine, just modify it to filter specifically on a new bit field in every table: uploaded or whatever you want to call it.

So when the register writes the changes to the table, make sure it clears that bit. Or, if you can't alter that part of the routine, put an ON UPDATE trigger on the table that clears it when a row is updated.

Then make sure that the "upload to corporate" routine sets all those bits when it copies the data up.

Or, what might be even more useful, is to have a "last changed" field updated by your register uploads, and a "last uploaded to corporate" field. That way you can compare the two.


MySQL replication conflicts I'm having more and more errors with my MySQL (latest 5.1) replication, in a master-slaves (one master, two slaves). A couple of times in the last couple of days, I'm getting duplicate entry, like this:

Error 'Duplicate entry '4818122-74' for key 'PRIMARY'' on query.
Default database: 'xxxxx'.
Query: 'INSERT INTO `xxxxxx` (`xxxx`, `xxxxx`, `xxxx`, `xxxxx`, `xxxx`) VALUES ('4818122', '74', '566.29411764706', '34', '10')'

It happens 99% of the time on the same table. All databases are replicated.

What can cause this?

The root cause could be very varying, specially if your slave is not in read_only mode. Clearly, the table has a data drift (means that both master and slave tables have different contents).

I suggest you to use pt-table-checksum in order to check the table consistency.

However, at this point I think that your table lacks of consistency, so I will recommend to dump and restore that table into the slave or, in s straightest way, clone again your slave. If you plan to rebui


Primary key with randomly varying increments (so it cannot be guessed easily) I would like the primary keys to be auto-incremented and generated but in varying increments. For ex, if I have increment range as 100... then the auto generated keys would be something like below:
- 20 (random number between 1 and 100)
- 30 (add random number 10 that's between 1 and 100)
- 113 (add random number 83 that's between 1 and 100)
- 118 (add random number 5 that's between 1 and 100)
- 217 (add random number 99 that's between 1 and 100)
- 220 (add random number 3 that's between 1 and 100)

The data domains are often exposed via HTTP REST endpoints, and I would like the end users to not able to guess the primary keys by simply incrementing numbers.

I'm trying to avoid UUID/GUIDs if possible. My REST URLs are longer, often with parent-child identifiers. (Yes, it's avoidable but I prefer it for simpler testing/troubleshooting). So I prefer numbers as identifiers.

Is there any simpler solution that I'm not aware of? I'm using PostgreSQL but any generic solution is also fine.

I suggest a function taking a regclass parameter that runs ALTER SEQUENCE with a new randomly generated increment before it returns the next value from a given sequence.
Can be used as drop-in replacement for nextval().

Per documentation on ALTER SEQUENCE:

  increment

  The clause INCREMENT BY increment is optional. A positive value will
  make an ascending sequence, a negative one a descending sequence. If
  unspecified, the old increment value will be maintained.

However:

  You must own the sequence to use ALTER SEQUENCE.

So we need to take care of privileges. You could make the function SECURITY DEFINER and owned by a superuser. If you don't REVOKE privileges from public it works for anyone on any sequence. There are two basic strategies to restrict usage:

To allow for selected sequences only, change the owner of those sequences to some dedicated role, say randseq and make randseq own the function (still with SECURITY DEFINER).
To allow for selected roles only, REVOKE all privileges on the function from public and GRANT EXECUTE to said roles. You might use a group role to simplify privilege management.

Or combine both:

CREATE OR REPLACE FUNCTION nextval_rand(regclass)
  RETURNS int AS
$func$
BEGIN
EXECUTE format('ALTER SEQUENCE %s INCREMENT %s'
               , $1                          -- regclass automatically sanitized
               , (random() * 100)::int + 1); -- values between 1 and 100
RETURN nextval($1)::int;
END
$func$ LANGUAGE plpgsql SECURITY DEFINER;

-- to restrict usage:
ALTER FUNCTION nextval_rand(regclass) OWNER TO randseq;
REVOKE ALL ON FUNCTION nextval_rand(regclass) FROM public;
GRANT EXECUTE ON FUNCTION nextval_rand(regclass) TO randseq;
GRANT randseq TO ???;

Call:

SELECT nextval_rand('tbl_tbl_id_seq'::regclass);

All you have to do now is replace nextval() with nextval_rand() in the column default of any serial column. And possibly change the owner of the sequence.

ALTER SEQUENCE tbl_tbl_id_seq OWNER TO randseq;
ALTER TABLE tbl ALTER COLUMN tbl_id SET DEFAULT nextval_rand('tbl_tbl_id_seq'::regclass);

Notes

ALTER SEQUENCE is designed not to block concurrent transactions. It takes effect immediately and cannot be rolled back. It should work reliably in a multi-user environment. Read the Notes section of the manual page for the fine print of ALTER SEQUENCE behavior.

There is a very slim chance for a race condition, where two concurrent operations each run ALTER SEQUENCE before calling nextval(). Since we are operating with random numbers anyway, this really doesn't matter.

Since we are running dynamic SQL I would normally SET search_path = public, pg_temp for the function. But since the parameter is regclass, only valid sequence names can be passed and are automatically schema-qualified and escaped unambiguously.