mysql, performance issue with FOR UPDATE query I have a query like this:

SELECT id,domain_name FROM domains WHERE test_stage=1 order by id LIMIT 200 FOR UPDATE

This is happening up to 500 times multithreaded within a second, so quite fast and it's overloading my database server sometimes.
I think it's got something to do with an internal state of mysql, usually it's handled quickly but sometimes it just builds up and up.

Server is mysql 5.5. (I wish I could use a newer one but it's not possible to upgrade)
I have an index on test_stage,id
test_stage is an enum with just a few entries, id is autoincrement integer with a few million entries.

Anyone has an idea how to do that sort of query faster?
I know later mysql servers allow to "skip" locked rows, sadly I am with 5.5.

The purpose is selecting a number of "jobs" for a multithreaded worker.

P.S.
The only reason why my server is not crashing is because I have a CPU check added. As soon as 10 cores are fully loaded it stops adding more threads.

Update:
The server is quite strong, 120GB memory (mostly for mysql) and it's far from being maxed out.

Item(s) coming in up to 10,000 times a second.
1-20,000 arrive in each batch.
The client threads are limited to 600 threads by php and additional logic (runs on apache).
The threading uses  PHP 7.2 on serverside .
max_connections = 1500
Processing one item takes, on average 1-2 ms when everything works fine.
The table domains is current 20MM rows; it is purged never

CREATE TABLE `domains` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `domain_name` varchar(64) NOT NULL,
  `test_time` datetime DEFAULT NULL,
  `website_seo` varchar(512) DEFAULT NULL,
  `context` varchar(15) DEFAULT NULL,
  `test_stage` enum('a','b','c','d','e','f') DEFAULT 'a',
  `type` enum('a','b','c','d','e','f') DEFAULT NULL,
  `counter_z` smallint(6) DEFAULT NULL,
  `counter_a` smallint(6) DEFAULT '0' ,
  `counter_b` smallint(6) DEFAULT '0' ,
  `counter_c` smallint(6) DEFAULT '0' ,
  `info_a` varchar(256) DEFAULT NULL,
  `info_b` varchar(256) DEFAULT NULL,
  `extrapolate_a` varchar(16) DEFAULT NULL ,
  `extrapolate_c` varchar(16) DEFAULT NULL,
  `extrapolate_int` smallint(6) DEFAULT '0' ,
  `max_successful` smallint(6) DEFAULT NULL,
  `counter_successful` smallint(6) DEFAULT NULL,
  `timeout_counter_successful` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `domain_name` (`domain_name`),
  KEY `domain_name_test_time` (`domain_name`,`test_time`),
  KEY `test_time` (`test_time`),
  KEY `test_stage` (`test_stage`),
  KEY `counter_c_t` (`counter_c`,`test_time`),
  KEY `test_stage_test_time_counter_c` (`test_stage`,`test_time`,`counter_c`),
  KEY `test_stage_id` (`test_stage`,`id`)
) ENGINE=InnoDB AUTO_INCREMENT=19437107 DEFAULT CHARSET=latin1

I'd like to add: The issue is not apache or the number of threads, it's mysql not delivering the data.
My problem is that sometimes the "FOR UPDATE" select which selects a bunch of rows, and then updates them to be used  is delaying.
It can happen with 1000 rows being unprocessed, it can happen with 100,000 rows unprocessed.
After a while it suddenly works again, usually mysql can catch up and then you don't see anything in "processlist" anymore.
When it happens you see around 100-200 of them waiting and CPU spikes up to 10 cores (then the queue is locked from further additions)

Update:
I've had issues with similar problems before, the only solution then was to actually move all "open" requests into a temporary table and make the queue selections from there, synchronizing it back into the full table.
It's alot of additional work but then mysql is fast, I hate to do it again :(

My Mantra:  "Don't queue it, just do it."  Although I don't fully understand your design, I suspect that the problems would decrease if you simply processed the data as it comes in.

A Rule of Thumb:  If an item can be processed in less time than it takes to enqueue and dequeue it, then the Queue is wasting time.

To better discuss this, I need to understand all the dataflow.  For starters, fill in the blanks:

Item(s) coming in ___ times a second.
__ arrive in each batch.
The client threads are limited to ____ threads by ______ (Apache/Tomcat/...).
The threading uses _____ (PHP/Java/C#/...).
max_connections = ____
Processing one item takes, on average ___ ms.
The table domains is current ____ rows; it is purged ______ (never/daily/immediately/...)  And provide SHOW CREATE TABLE domains.

More

Thanks for the update!

I gather that something else is changing test_stage, thereby effectively re-queueing rows?

Get rid of KEY test_stage (test_stage), since it is redundant with two other indexes.

Change KEY test_stage_id (test_stage, id) to KEY tid (test_stage, id, domain_name) so that it is "covering".

One thought is to get rid of id and promote domain_name to be the PRIMARY KEY.  This avoids the need to deal with two unique keys.  Note that that would change the query and optimal index to

SELECT domain_name  FROM domains  WHERE test_stage=1
    ORDER BY domain_name  LIMIT 200  FOR UPDATE
INDEX(test_stage, domain_name)

Another thought is to assign the "worker" threads to contiguous chunks of the table, based on the PRIMARY KEY.  That way, they should never (or at least rarely) collide over which rows to grab.  (I suspect this is the core of the issue.)

I assume your buffer_pool is big enough.  But if you would like a critique of the VARIABLES, please provide SHOW GLOBAL STATUS and SHOW GLOBAL VARIABLES.


Transaction Replication:Intialize from Backup I have a database which i want to replicate to another server.
Since the database is huge(~1.5 TB) i have decided to do initialize from backup.
But since it is a production live database,new insert/updates happen all the time.
I have done intialiaze from backup from a full backup earlier.
I want to know if i can implement transactional replication from a full backup + differential backup.

So when i create subscriber using the below script,do i have to give the differential.bak as the backupdevicename or the fullbackup file name.

-----------------BEGIN: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------
use [AdventureWorks2012]
exec sp_addsubscription @publication = N'Demo_pub', @subscriber = N'DESKTOP-K0UF973\SQLTEST', @destination_db = N'AdventureWorks2012', @sync_type = N'initialize with backup',
@subscription_type = N'pull', @update_mode = N'read only',@backupdevicetype=N'disk',
@backupdevicename=N'C:\Rep\Replication_Scripts\differential.bak'
GO
-----------------END: Script to be run at Publisher 'DESKTOP-K0UF973\DEMOSERVER'-----------------

-----------------BEGIN: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------
use [AdventureWorks2012]
exec sp_addpullsubscription @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publication = N'Demo_pub', @publisher_db = N'AdventureWorks2012',
@independent_agent = N'True', @subscription_type = N'pull', @description = N'', @update_mode = N'read only',
@immediate_sync = 0

exec sp_addpullsubscription_agent @publisher = N'DESKTOP-K0UF973\DEMOSERVER', @publisher_db = N'AdventureWorks2012',
@publication = N'Demo_pub', @distributor = N'DESKTOP-K0UF973\SQLTEST', @distributor_security_mode = 1,
@distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64,
@frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0,
@frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959,
@active_start_date = 20180731, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'',
@use_ftp = N'False', @job_login = null, @job_password = null, @publication_type = 0
GO
-----------------END: Script to be run at Subscriber 'DESKTOP-K0UF973\SQLTEST'-----------------

Since it is a live database,i cannot make the database into single user mode and take full backup and initialize the subscriber.I have trans logs also being taken every hour.So i am planning to restore recent full backup + differential to subscriber and will disable trans log backup job temporarily.
Is this a correct approach.?
if anyone encountered similar situation ,please let me know.

You don't have to take the publisher offline when you initialize from a backup.  Just like initializing from a snapshot, the initialization just needs to bring the subscriber database to a point-in-time within the Distribution Retention Period, and to configure the subscription with the LSN to start from.

Perhaps you're thinking of Manual Initialization which requires you to synchronize the subscriber before beginning repilication.


Find rows matching only values from a specific list with SQL I have a table like this:

  Id    Status
  --    -----------
  1     Closed
  1     In progress
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed
  5     In progress

I want to show rows that have the Status of closed, rejected or cancelled. However, I want to exclude the IDs that have a different status value, even if they also have any of those three.

So, for my example I would like the output to be this:

  Id    Status
  --    ---------
  2     Rejected
  3     Cancelled
  4     Rejected
  4     Closed

What should my query look like?

One way would be to use NOT EXISTS.

SELECT *
FROM   YourTable T1
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND NOT EXISTS (SELECT *
                       FROM   YourTable T2
                       WHERE  T2.Id = T1.Id
                              AND T2.Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )) ;

Or an alternative method if your DBMS supports windowed aggregates..

SELECT Id,
       Status
FROM   (SELECT *,
               COUNT(CASE WHEN Status NOT IN ('Closed','Rejected','Cancelled')
                             THEN 1 END)
                     OVER (PARTITION BY Id) C
        FROM   YourTable) D
WHERE  Status IN ( 'Closed', 'Rejected', 'Cancelled' )
       AND C = 0 ;

And another option using EXCEPT:

SELECT *
FROM YourTable
WHERE Id IN
      ( SELECT Id FROM YourTable
        WHERE Status IN ( 'Closed', 'Rejected', 'Cancelled' )
      EXCEPT
        SELECT Id FROM YourTable
        WHERE Status NOT IN ( 'Closed', 'Rejected', 'Cancelled' )
      ) ;


Is there a way other than WKT to insert geometry to SQL Server? I am using design data from *.dwg and *.dgn to insert geometries to SQL Server field which is set to geometry. All examples I see are using something called WKT which is a specification by a company called OGC but they do not offer any converters so not straightforward to convert from *.dwg / *.dgn to prepare the insert statement.

Is there a different way to the WKT syntax to insert into SQL Server?

Microsoft doesn't support input from foreign formats. However, you can use GDAL's ogr2ogr to convert foreign formats representing Simple Features to a MSSQL import

DGN is supported by the opensource GDAL

DWG requires building against Teigha which while open source is not, afaik, free. However, they provide a File Converter. You can find more information about Teigha fiasco/political situation here, but there is explicit mention of support with Teigha in the GDAL docs,

OGR supports reading most versions of AutoCAD DWG when built with the Open Design Alliance Teiga library. DWG is an binary working format used for AutoCAD drawings. A reasonable effort has been made to make the OGR DWG driver work similarly to the OGR DXF driver which shares a common data model. The entire contents of the .dwg file is represented as a single layer named "entities".

More info:

Formats GDAL supports our of the box here
Download a copy of GDAL for proprietary overpriced operating systems here.

To ease upgrading in the future to Ubuntu, you can install ogr2ogr with only
sudo apt-get install gdal-bin


Oracle 10 RAC VIP returns non-VIP address for client I am not a DB or even Oracle expert, but I wanted to understand when and why Oracle returns a different non-VIP address to the client, assuming this is correct behaviour?

listener.ora

LISTENER_DB-A =
(DESCRIPTION_LIST =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = DB-A-VIP)(PORT = 1521)(IP = FIRST))
       # this is the VIP 10.0.0.15
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.10)(PORT = 1521)(IP = FIRST))
    )
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
    )
  )
)

Here, after connecting to the IP address 10.0.0.15, the Oracle server returns the 10.0.0.10 address to the client. This is my network sniff of the said behaviour:

Client to Oracle

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.15)(PORT=1521)))
(CONNECT_DATA=(CID=(PROGRAM=)(HOST=__jdbc__)(USER=))(SERVER=DEDICATED)
(SERVICE_NAME=my_db)(FAILOVER_MODE=(TYPE=SELECT)(METHOD=BASIC)(RETRIES=5)(DELAY=5))))

Oracle to Client

(DESCRIPTION=(CONNECT_DATA=(SERVICE_NAME=DB-A))(ADDRESS=(PROTOCOL=TCP)(HOST=10.0.0.10)(PORT=1521)))

I know that this behaviour is not mandatory, as I've seen Oracle server respond without said redirection. What is the reason the server is acting this way? Thanks in advance!

It is even more complex when it comes to 11gR2 and SCAN listeners. But let's say that reason behind is load balancing. The listener will redirect client onto least loaded node.

Another reason can also be workload balancing. Imagine that you have two services (SERVICE_NAME) on our cluster. One called LOADERS and is used by processes which push data into the database. The other is called WEB and is used by application, which fetch data from database. Each service is located on different node. Then this redirect will guarantee that the database is used effectively and all the loaders use the same cluster node even if one of the nodes fails.

BTW: I think that the redirect message does not contain an IP address, but a hostname. Therefore there should not be any NAT between DB client and DB server and also they should use the same DNS server.


MySQL Workbench is not finding MySQL service? I set up a local MySQL server, currently with no databases. I'm trying to create a new server instance profile in MySQL Workbench to manage the server, and during the Create New Server Instance Profile wizard, it gets to the Windows Management section and tells me that No MySQL service found.

I went into Task Manager and found the process mysqld.exe to be running, under the user name of NETWORK SERVICE. Then I went into the Services tab and found that MySQLServerName (I custom named the Windows service) is also there, and status is running.

I ran cports and looked at what ports mysqld.exe is using, and ran a telnet command on that port. It's reporting that the port is being listened in on. I then ran the MySQL Command Line Client to be sure, and after \r it gives me a proper connection ID and a list of databases (NONE at this point).

But with all this, I can't make the wizard find a running service. I've Googled this and found no answers, so please, if someone would help shed some light on this issue that'd be great!

I custom named the Windows service

This may seem a bit of an obvious question... but have you considered not doing that?


Why am I getting the An expression of non-boolean type specified in a context where a condition is expected, near ',' error This is my code:
SELECT *
FROM mytable
WHERE (Permit,Stampdate) IN (SELECT Permit,MAX(Stampdate) AS Stampdate
                             FROM mytable GROUP BY Permit)

SQL SERVER doesn't support this.
You have to switch to a subwquery

CREATE TABLE mytable (Permit integer,Stampdate Datetime)
GO

INSERT INTO mytable (Permit,Stampdate) VALUES (1,GETDATE()),
 (1,DATEADD(day, DATEDIFF(day, 0, GETDATE()), '05:30:00'))
GO

SELECT *
FROM mytable As t1
WHERE
  EXISTS ( SELECT 1 FROM

   ( SELECT Permit,MAX(Stampdate) AS Stampdate
    FROM mytable As t2
       GROUP BY Permit) t2 WHERE  t1.Permit = t2.Permit
      AND t1.Stampdate = t2.Stampdate
)
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897

SELECT *
FROM mytable As table1
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897
     1 | 2020-07-02 05:30:00.000

    SELECT Permit,MAX(Stampdate) AS Stampdate
    FROM mytable As t2
GROUP BY Permit
GO

Permit | Stampdate
-----: | :----------------------
     1 | 2020-07-02 16:22:06.897

db<>fiddle here


Connecting to sql azure from ssms throw exception 'sys.configurations' Connecting to Sql Server Azure from MSSQL Server MGMT Studio, following exception occures :

Cannot connect to xxxxxxxxx.database.windows.net

An exception occured while executing a Transact-SQL Statement or batch.
   (Microsoft.SqlServer.ConnectionInfo)

sql management studio connection to windows azure invalid exception object name
'sys.configurations'

Any clue ?

And here's the whole message : Cannot connect to ndkkyxvogj.database.windows.net.

===================================

An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)

Program Location:

at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.CheckDacAndSmo(SqlConnection sqlConnection) at Microsoft.SqlServer.Management.UI.VSIntegration.ObjectExplorer.ObjectExplorer.ValidateConnection(UIConnectionInfo ci, IServerType server) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

Invalid object name 'sys.configurations'. (.Net SqlClient Data Provider)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=11.00.9164&EvtSrc=MSSQLServer&EvtID=208&LinkId=20476

Server Name: ndkkyxvogj.database.windows.net Error Number: 208 Severity: 16 State: 1 Line Number: 17

Program Location:

at Microsoft.SqlServer.Management.Common.ConnectionManager.ExecuteTSql(ExecuteTSqlAction action, Object execObject, DataSet fillDataSet, Boolean catchException) at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteWithResults(String sqlCommand)

there can be couple of issues -

Firewall : You will need to add your local IP range to the allowed SQL Azure Firewall rules. Refer to : How to: Configure the Server-Level Firewall Settings (Windows Azure SQL Database)

You are using older/unsupported version of SSMS. If yes, then upgrade to the latest version (e.g SSMS 2008R2 - build 10.50.** Refer : SQL Azure July 2011 Service Release

You might be hitting - Invalid object name ‘sys.configurations’. (Microsoft SQL Server, Error: 208)


How to find out who deleted some data SQL Server My boss had a query from a customer yesterday asking how they could find out who deleted some data in their SQL Server database (it is the express edition if that matters).

I thought this could be found from the transaction log (providing it hadn't been truncated) - is this correct? And if so how do you actually go about finding this information out?

I've not tried fn_dblog on Express but if it is available the following will give you delete operations:

SELECT
    *
FROM
    fn_dblog(NULL, NULL)
WHERE
    Operation = 'LOP_DELETE_ROWS'

Take the transaction ID for transactions you're interested in and identify the SID that initiated the transaction with:

SELECT
    [Transaction SID]
FROM
    fn_dblog(NULL, NULL)
WHERE
    [Transaction ID] = @TranID
AND
    [Operation] = 'LOP_BEGIN_XACT'

Then identify the user from the SID:

SELECT
    *
FROM
    sysusers
WHERE
    [sid] = @SID

Edit: Bringing that all together to find deletes on a specified table:

DECLARE @TableName sysname
SET @TableName = 'dbo.Table_1'

SELECT
    u.[name] AS UserName
    , l.[Begin Time] AS TransactionStartTime
FROM
    fn_dblog(NULL, NULL) l
INNER JOIN
    (
    SELECT
        [Transaction ID]
    FROM
        fn_dblog(NULL, NULL)
    WHERE
        AllocUnitName LIKE @TableName + '%'
    AND
        Operation = 'LOP_DELETE_ROWS'
    ) deletes
ON  deletes.[Transaction ID] = l.[Transaction ID]
INNER JOIN
    sysusers u
ON  u.[sid] = l.[Transaction SID]


SQL Server renaming table from another database Is there a way in SQL Server to rename a table in another database? When you are in current database and migrating data over to another database in the TEMP table and then renaming TEMP table in that another database. I do not want to use "USE [database]" since the database name could change on different servers.

Thanks for the help in advance!

You should use dynamic sql to do this. Build a string and execute it. As long as you know the name of the destination database, everything should be fine.

use CurrentDB
declare @x varchar(1000), @otherDb sysname = 'NewDB';

set @x = 'use ' + @otherDB + '

EXEC sp_rename ''dbo.temp'', ''temp2''
'
exec (@x);
print @x;
GO


Why mysql throws "invalid default value for field" only when two or more timestamp fields exist in the create statement? In mysql 5.7.28 I create a table like this:

create table t1 (
id int not null,
d1 timestamp)
engine=innodb;

which works fine and creates d1 as non-null with default value current_timetamp,on update current_timestamp.

But when I try to create the same table with a two timestamp fields as below:

create table t1 (
id int not null,
d1 timestamp,
d2 timestamp)
engine=innodb;

I get an error:

  SQL error (1067): invalid default value for 'd2'

Why I get an error only when I add a second timestamp field?
Is this a bug or some expected behavior in mysql?

This behaviour is described in explicit_defaults_for_timestamp system variable which is by default disabled for 5.6,5.7 (and effectively disabled on 5.1) and it is enabled in 8.0.

Quoting from above link:

  (5.7) If explicit_defaults_for_timestamp is disabled, the server enables
  the nonstandard behaviors and handles TIMESTAMP columns as follows:

  TIMESTAMP columns not explicitly declared with the NULL attribute are    automatically declared with the NOT NULL attribute. Assigning
  such a    column a value of NULL is permitted and sets the column to
  the    current timestamp.
  The first TIMESTAMP column in a table, if not explicitly declared    with the NULL attribute or an explicit DEFAULT or ON UPDATE
  attribute, is automatically declared with the DEFAULT
  CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP attributes.
  TIMESTAMP columns following the first one, if not explicitly declared    with the NULL attribute or an explicit DEFAULT attribute,
  are    automatically declared as DEFAULT '0000-00-00 00:00:00' (the
  “zero”    timestamp). For inserted rows that specify no explicit value
  for such    a column, the column is assigned '0000-00-00 00:00:00' and
  no warning    occurs.

  Depending on whether strict SQL mode or the NO_ZERO_DATE SQL mode is
  enabled, a default value of '0000-00-00 00:00:00' may be invalid.

Now the issue with 5.7 is that NO_ZERO_DATE mode is enabled. This does not allow the default value of 0000-00-00 00:00:00 which as described in the documentation would be added as default value (if not explicitly declared) in timestamp columns following the first one.

Mysql 8.0 still has the NO_ZERO_DATE mode enabled, but the explicit_defaults_for_timestamp envvar is by default enabled which according to the documentation it would add null as the default value see below (which will not cause any errors on table creation ):

  (8.0) If explicit_defaults_for_timestamp is enabled, the server disables the
  nonstandard behaviors and handles TIMESTAMP columns as follows:

  [..]

  TIMESTAMP columns not explicitly declared with the NOT NULL attribute are automatically declared with the NULL attribute and
  permit NULL values. Assigning such a column a value of NULL sets it to
  NULL, not the current timestamp.

  [..]

  The first TIMESTAMP column in a table is not handled differently from TIMESTAMP columns following the first one.

This behavior has also been discussed on MySQL's issue tracker but has marked as "Not a bug".

As @Akina mentioned in comments, do not rely on default values. Always write the full specification for the field.


Database design for multiple account types management system I am trying to create multiple account types management system.
At first time, I have to types  normal type, and professional type (each type has own table, so I have two tables at this time), and new tables are coming.
Each account type has some profiles. Each profile has own table, example: table profile_from_service_A, table profile_from_service_B, ... (new profile types are coming).
When new record is inserted to normal (or professional) table, I call to service A, service B, .. to create profiles, and insert profile result to corresponding table. There is not any relation between these service about business domain.
Most used query is used for reporting , example result: (account_id, account_type, profile_A_id, profile_A_propery_1, profile_A_propery_2, profile_B_id, profile_B_name, profile_E_id,...)
I intend create two mapping tables. For profile_from_service_A, mapping table is service_A_account_mapping with columns: (id_service_A, account_type, accounnt_id), account_type is normal or professional at this time.
This structure is also used for service_B_account_mapping, but I use id_service_B instead of id_service_A
If there is new profile, I have to create new mapping table.
Another solution: add profile_A_id, profile_B_id to normal, professional tables. Don't use mapping table anymore.
So which solution is better?
Thank you!

You are going down the path of table explosion with resulting complicated queries.
In a relational database, you want fewer tables but more columns, fewer columns but more rows.
Applying this mantra: your two types of accounts should be one accounts table with a type column. You could have two child tables to store data specific to each type of account if there would otherwise be too many null columns. But if there are only one or two such attributes I would personally add them as nullable columns and add a constraint something like:
 alter table accounts
   add check (type = 'normal' and pro_attribute is null or type = 'professional' and pro_attribute is not null);

where pro_attribute is the name of an attribute that only applies to professional accounts.
This could be simplified to:
alter table accounts
  add check ((type = 'normal') = (pro_attribute is null));

but that's not clearer.
Then for your profiles, you need to apply the same pattern: One profiles table with a service column. If individual services have different attributes you can store them in their own child tables.
As far as the relationship to services: it sounds like each account has one profile for each service that exists. In this case I would create the profiles table with columns account_id (foreign key to accounts), service (or service_id as a foreign key to a services table) and any attributes you need for that particular service for that particular account.


Altering db schema in production environment I have some questions on db schema changes in production environment without taking the database server and the webapp offline.

Q1- I want to be able to make changes to database schema (adding, removing columns, normalizing tables etc) in my production environment without taking the db server and my webapp offline. What are some of the best practices to achieve this? Also are there any free tools that deal with this problem. I am using PostgresSQL.

Q2- While the db is being upgraded, will the app servers in production work against the old db or the new db. Or is there usually some sort of a hybrid using perhaps a strategy pattern in the app? What are some of the best practises for this? When would it be safe to point the appserver to the upgraded db. Will there be some sort of downtime involved or can that be avoided altogether somehow?

Thanks.

It really depends how big, complicated and active your database is. Bear in mind that while you are making schema changes, many of the tables will be read-only or completely inaccessible until the changes are completed. Also bear in mind that the reverse is true - your schema changes will need to acquire exclusive locks on entire tables and possibly all their relations, which may take a long time or even be impossible to acquire if the application gets in the way. Theoretically this can result in circular locks which will freeze the whole DB. Basically, if there are a lot of reads and writes going on and changes are going to cause significant disruption to your application, then you should schedule downtime for it instead.

Then you have to account for the changes your application will require. Will changing the schema require changes in the application too? Is your application small enough or modular enough that you can make those changes in increments as you change the database? Ultimately, making changes in production is really hard. Unless your app is designed for it from the ground up, it's almost universally not worth the extra effort required for the sake of some small downtime.

The other option to minimise downtime is to build a new database with an improved schema and a new app server connected to it, then build a widget to sync the data between the new database and the old one. When you are ready to deploy you simply direct traffic to the new app server. Again this is a tradeoff between how hard it is to create the system and how hard it is to accept some downtime. There are very few applications that cannot tolerate an hour or two with a "Scheduled Maintenance" message starting 2am on a tuesday night.


How to get the records from Amazon Athena for past week only I am writing a query to get Amazon Athena records for the past one week only. Here is what I wrote so far:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin';

But I am not sure how to write it to extract records for the past 1 week only.

@Philᵀᴹ's answer is almost there. I just used it on my query and found the fix. I would have commented, but don't have enough points, so here's the answer.

You have to use current_timestamp and then convert it to iso8601 format. Like so:

WITH events AS (
  SELECT
    event.eventVersion,
    event.eventID,
    event.eventTime,
    event.eventName,
    event.eventType,
    event.eventSource,
    event.awsRegion,
    event.sourceIPAddress,
    event.userAgent,
    event.userIdentity.type AS userType,
    event.userIdentity.arn AS userArn,
    event.userIdentity.principalId as userPrincipalId,
    event.userIdentity.accountId as userAccountId,
    event.userIdentity.userName as userName
  FROM cloudtrail.events
  CROSS JOIN UNNEST (Records) AS r (event)
)
SELECT userName,sourceIPAddress,eventName,eventTime FROM events WHERE eventName='ConsoleLogin'
and eventTime > to_iso8601(current_timestamp - interval '7' day);

You can test the format you actually need by doing a test query like this:

SELECT to_iso8601(current_date - interval '7' day);

Returns: '2018-06-05'

SELECT to_iso8602(current_timestamp - interval '7' day);

Returns: '2018-06-05T19:25:21.331Z', which is the same format as event.eventTime, and that works.


Join Tables of Varying Criteria in Order of Priority I am trying to join two tables based on varying criteria in order of priority.

The situation

I have inherited a clinical database which has inherently bad data quality from years of poor input and shockingly bad application build etc... all out of my control but I am tasked with creating a data warehouse from this system.

I have a table which has diary appointments in them

The structure is

(Appointments)

Appointment_ID (PK),
Appointment_Date,
Appointment_User (FK),
Appointment_Patient_ID (FK),
Appointment_Outcome

Events

When a patient is seen in an appointment there are events that occur..

The events table contains the following information

Event_ID (PK),
Event_Date,
Appointment_ID (FK),
Patient_ID (FK),
User_ID (FK),
Event_Type_ID (FK),
Event_Outcome

The poor design of the application allows:

1) Users create an event without linking it to an appointment.

2) It also allows someone to complete an event on behalf of a clinician (medical secretary) whilst impersonating them - however in the database it records the medical secretary's ID instead of the clinicians ID, but keeps the clinicians ID in the appointment table.

The poor data quality exists where:

1) The user creates the event on the wrong date (so no appointment_ID and date is wrong).

So whilst the event should contain the appointment_ID, user_ID and be on the same date as the appointment it isn't always.

also

A patient can have more than one appointment on a day with and with more than one clinician (as defined by the user_ID).

The ideal would be to get the data quality fixed but the organisation can't do this in time scale (there are people going in and fixing all the errors but this won't be finished for another couple of months) The organisation has agreed to best match on 5 sets of criteria in a certain order of priority and accept the risk that there will be some inaccuracies.

The required outcome

I need to create a table which shows the following information

All data from Appointments (a) on the left
All rows from all columns (where there is a match) on the right from Events (e)

The join should occur in the order of priority:

1) Join e on a.appointment_ID = e.appointment_ID

if there is no match then

2) Join e on a.patient_ID = e.Patient_ID AND a.date = e.date AND a.user_ID = e.user_ID AND e.event ID not found in the results from join 1

if there is no match then

3) Join e on a.patient_ID = e.Patient_ID AND a.date = e.date AND e.event ID not found in the results from join 1 or join 2

if there is no match then

4) Join e on a.patient_ID = e.Patient_ID AND a.date - e.date +/- 5 days AND a.user_ID = e.user_ID AND e.event ID not found in the results from join 1, 2 or 3

5) Join e on a.patient_ID = e.Patient_ID AND a.date - e.date +/- 5 days AND e.event ID not found in the results from join 1, 2, 3 or 4

I tried doing nested not IN statements on the join but as the data is in the GB and records are in the millions of rows per table it's taking half an hour to run the query and this needs to be updated on demand as people are checking the impact of the data quality amendments as they go.

Is there a resource and query run time efficient way of running the join on these 5 sets of criteria in order of priority?

This would be better accomplished through a series of inserts. Create a temporary table that contains all of the columns from table Appointments and table Events.

Insert into the temp table using each of your join predicates in the order of importance that you have listed above. For each insert after the first one (the one joined on appointment_id), use the following predicates:

appointment_id not in (select appointment_id from #yourTempTable) and
   event_id not in (select event_id from #yourTempTable)

Since your goal is to have all appointments listed, do a final insert for everything from Appointments that has:

appointment_id not in (select appointment_id from #yourTempTable)

Then you will want to run a report showing what events were not matched up to an appointment by searching all event_ids from Events that do not exist in #yourTempTable.


Something was wrong in the reader, after a restart the issue went away.


sysarticles system view - what does a status of 57 mean? I'm trying to work out why one article within a publication isn't replicating. I've scripted it out and confirmed the replication procs it's supposed to call are the correct ones, and have run a trace on the destination instance looking for those procs being called - no results.

Querying the sysarticles view in the published database, I noticed the article in question has a status of 57, whereas the rest of the articles have a status of 17.

Looking at msdb, http://msdn.microsoft.com/en-us/library/ms174970(v=sql.90).aspx, there are 5 bitwise values available: 1, 8, 16, 24, 64.

So it makes sense that the articles with status 17 are made up of 1 and 16 above.

But even if I add all bitwise values above excluding 64, I can't get a status value of 57. The max is 49.

Can anyone please explain to me what a status of 57 is, and whether it could be the cause of my article not replicating.

Thankfully this isn't on production, so I'm going to try and update the status manually, and if that doesn't work, take the table out of replication and add it again as its own publication.

But it would be good to understand what a status of 57 means.

I'm not sure what that status value means, but most of my articles are listed with status 57 and they are working just fine.

I'm guessing that there's a bitwise value of 32 which is missing (why else go from 16 to 64) and I'm guessing that 24 should actually be 32 as 24 isn't a valid bitwise value.

57 = 1+8+16+32

I'll see if I can get it confirmed that the 24 should actually be 32.


Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2? Can I attach a SQL 2008 R2 database to SQL 2008 that is not R2?

I have a database that I am detaching from SQL 2008 (not R2) and can attach this to SQL 2008 R2.

Can I then detatch the database from SQL 2008 R2 and attach it to SQL 2008 (not R2)?

No.

You can't do that from a higher to a lower version. R2 is considered as a new version of sql server. You can go from lower version to higher version with detach/attach.


convert string allow null col to int? I want to convert string to int my string type  [DT_WSTR] and my expression

ISNULL(col) || TRIM(col) == "" ? (DT_I4)0 : (DT_I4)col

I get this error
[Derived Column [518]] Error: The conditional operation failed.

You've got something that doesn't convert to an integer value in your source data.

Since the expression language doesn't have a tryparse method, the cleanest mechanism is to use a script component as a Transformation and leverage the .NET methods.

Connect a Script Component to your source.
On the Input Columns tab, check col as ReadOnly.
On the Inputs and Outputs tab, expand Output 0, click Output Columns and select Add Column
Rename this column to CleanCol and leave with the default data type of DT_I4
Click back to the Script tab and click Edit Script...
Use the following method in your code

Line here so formatting picks up

public override void Input0_ProcessInputRow(Input0Buffer Row)
{
    int colOut = 0;
    if (!Int32.TryParse(Row.col, out colOut))
    {
        Row.CleanCol_IsNull = true;
    }
    else
    {
        Row.CleanCol = colOut;
    }
}

Here's my source query

SELECT
    D.*
FROM
(
VALUES
    (N'1')
,   ('')
,   ('0')
,   ('3')
,   ('X') -- Uncomment this one to generate error
,   (NULL)
) D(col);

Results

As I think about it a bit longer, in the above script, if you wanted to have zero outputted for "bad" values, replace the first action in the if statement with Row.CleanCol = 0; Otherwise, this will make numbers of numbers and NULL of non-numbers.


Strange things with Avg_row_length The data from one tables is dumped to file and inserted into calls_old table with load data infile it shows Avg_row_length 7366.

Later I dumped table with mysqldump, droped table, then created again and imported data. Now Avg_row_length is 471.

| Name       | Engine | Rows     | Avg_row_length | Data_length | Index_length | Data_free  |
+------------------------------+--------+---------+------------+----------+----------------+-

          before
| calls_old  | InnoDB |  1185666 |           7366 |  8733835264 |   4875419648 | 5329911808 |

         after

| calls_old  | InnoDB |  1277654 |            471 |   601882624 |    464175104 |    4194304

What causes this behavior?

There are two distinct conditions you need to be aware of
CONDITION #1 : Fragmentation
It is obvious the table has experienced lots of INSERTs, UPDATEs, and DELETEs (or non-SELECT DML). Even loading data sorted by PRIMARY KEY order will make index pages lopsided, at least 45% sparse.
I have posts about this phenomenon before

Aug 03, 2011 : When should I rebuild indexes?
Nov 26, 2012 : Does the Int type primary key insert order affect query performance in MySQL?
Oct 26, 2012 : How badly does innodb fragment in the face of somewhat out-of-order insertions? (I discuss AVL Trees Here)

Thus, data pages shrinking from 8733835264 (8.13G) to 601882624 (574M) and shrinking index pages from 4875419648 (4.54G) to (~443M) is not alarming. This will improve read performance.
CONDITION #2 : InnoDB Row Count
InnoDB is notorious for INFORMATION_SCHEMA.TABLES having spurious counts. MyISAM tables give accurate row counts in the table_rows column of INFORMATION_SCHEMA.TABLES.
InnoDB likes to do dives into the index pages in order to guess the number the rows in the table.
I have discussed this feature before as well

Feb 23, 2013 : How does mysql deal with queries that touches myisam and innodb tables? (See ASPECT #3)
Aug 04, 2011 : Optimizing InnoDB default settings
Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics?

If you want an accurate count of an InnoDB table, you must bite the bullet and run
SELECT COUNT(1) FROM calls_old;

which will produce the same count every time.
Running this:
SELECT table_rows FROM information_schema.tables
WHERE table_name = 'calls_old';

never gives you the same value each time.
In fact, if you run
SHOW INDEXES FROM calls_old;

look at the cardinality column. It will produce different numbers each time. You would have to lock it down by setting innodb_stats_on_metadata to 0. Even if you did that, the stats would become stale over time through normal non-SELECT DML just like a MyISAM table. Running ANALYZE TABLE calls_old with innodb_stats_on_metadata set to 0 will fix the index statistics.
Having innodb_stats_on_metadata remain at 1 will make ANALYZE TABLE calls_old  completely useless. (See my post From where does the MySQL Query Optimizer read index statistics?).
Since the table is called calls_old, I can surmise that you do not need to adjust any other options since it may be an archive table as the name suggests.
EPILOGUE
The value of Avg_row_length is a good indicator that you should defragment the table. When you see an InnoDB table growing that much, you could just run
ALTER TABLE calls_old ENGINE=InnoDB;

to shrink that table.
Thus, the behavior you are seeing is driven by the two conditions I just discussed.
SUGGESTION
If you want to verify the contents, you should these two queries:

SELECT COUNT(1) FROM calls_old;
CHECKSUM TABLE calls_old;

before and after dumping/importing.


SQL Replication - Specify Schema on destination Table wondering if it's possible to replicat into a different schema using Transactional Replication?

My situation is that I have 3 DB servers, each hosting it's own instance of our software, in three different locations. I would like to grab the data from each database and replicate them to a central server for reporting in Azure. I'm seeing if it's possible to combine them into a single database as opposed to having to pay for 3.

e.g. (hope this makes sense)
Server 1, db=MySoftware, Table=dbo.Invoice -> replciation to db=MySoftware, table=server1.invoice in Azure SQL server
Server 2, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server2.invoice in Azure SQL server
Server 3, db=MySoftware, Table=dbo.invoice -> replication to db=MySoftware, table=server3.invoice in Azure SQL server

I'm not worried if it's not possible, more of a curiosity thing.

Cheers

Yes, this approach is feasible; it just requires the right setup.  I'm going to assume you'll be setting up replication via the wizard because any other way is basically like trying to navigate your way out of a series of caverns with a terrible flashlight.  This answer is a bit wordy and light on instructions because it combines links to a few walkthroughs that are just better documented.  I'm only going to draw attention to the key items you need to adjust in order to achieve your desired setup.

First, you must define publications at each individual server.  When specifying your articles in each publication, you need to be on the lookout for the Destination Object Owner setting.  You can specify a new schema for your articles to get pushed to.  I assume you'll want this to be different for each publication.

The above screenshot was taken from a SQL Server Central Walkthrough on how to set this up within the publication, which can be found here.

After you have completed configuring your publications and distributor, you now setup what I've heard referred to as a central subscriber.  To do this, you basically setup subscriptions to point to the same subscriber database.  This SQL Server Central Article walks through the process, but because you've setup your publications to remap the tables to different schemas (unlike the article), I believe you can initialize each publication via snapshot, which should hopefully save you some time on initial setup and anytime down the line you need to reset the subscription.  It's only when you are combining tables within the subscriber that one publisher snapshot will overwrite the data from a different publisher's snapshot.  I don't believe this will occur in your setup, but test to be certain.


Running a CTE query in a loop using PL/pgSQL I'm trying to execute query that is repeatedly called in a loop using plpgsql -the loop iterates over another table (named coordinates) that contains top left and bottom right latitude/longitude coordinates of grids, I pass the top left and bottom right latitude / longitude values into my CTE in order to display the amount of requests (hourly) made within those coordinates for given two timestamps-. However, I cannot display the results of my CTE and I get the following error message:

ERROR:  query has no destination for result data
HINT:  If you want to discard the results of a SELECT, use PERFORM instead.
CONTEXT:  PL/pgSQL function "inline_code_block" line 6 at SQL statement

What should I change here in order to make the entire query work as needed? My code is as below:

DO $$
<<outer_scope>> DECLARE
  coords RECORD;
BEGIN
    FOR coords IN SELECT topleftlat, topleftlon, bottomrightlat, bottomrightlon FROM coordinates LOOP
        WITH cal AS (
        SELECT generate_series('2011-02-02 00:00:00'::timestamp ,
                   '2012-04-01 05:00:00'::timestamp ,
                   '1 hour'::interval) AS stamp
    ),
    qqq AS (
      SELECT date_trunc('hour', calltime) AS stamp, count(*) AS zcount
      FROM mytable
      WHERE calltime >= '2011-02-13 11:55:11'
        AND calltime <= '2012-02-13 01:02:21'
        AND (calltime::time >= '11:55:11'
        OR calltime::time <= '01:02:21')
        AND lat BETWEEN coords.bottomrightlat AND coords.topleftlat
        AND lon BETWEEN coords.topleftlon AND coords.bottomrightlon
     GROUP BY date_trunc('hour', calltime)
    )
    SELECT cal.stamp, COALESCE (qqq.zcount, 0) AS zcount
    FROM cal
    LEFT JOIN qqq ON cal.stamp = qqq.stamp
    WHERE cal.stamp >= '2011-02-13 11:00:00'
      AND cal.stamp <= '2012-02-13 01:02:21'
      AND (
        extract ('hour' from cal.stamp) >= extract ('hour' from '2011-02-13 11:00:00'::timestamp) or
        extract ('hour' from cal.stamp) <= extract ('hour' from '2012-02-13 01:02:21'::timestamp)
      )
    ORDER BY stamp ASC;

    END LOOP;
END;
$$;

The DO command has no facility to actually return data (except with RAISE, or you could write to a (temp) table .. ).

You need to create a PL/pgSQL function that can define a return type with RETURNS and call it.

You could return the result with RETURN QUERY EXECUTE. But I suspect the whole operation can be simplified ...

Rewrite as single SQL query

You probably don't need plpgsql or loops at all. Consider this plain SQL query instead:

WITH v AS (
   SELECT '2011-02-13 11:55:11'::timestamp AS _from -- provide times once
         ,'2012-02-13 01:02:21'::timestamp AS _to
   )
, q AS (
   SELECT c.coordinates_id
        , date_trunc('hour', t.calltime) AS stamp
        , count(*) AS zcount
   FROM   v
   JOIN   mytable t ON  t.calltime BETWEEN v._from AND v._to
                   AND (t.calltime::time >= v._from::time OR
                        t.calltime::time <= v._to::time)
   JOIN   coordinates c ON (t.lat, t.lon)
                   BETWEEN (c.bottomrightlat, c.topleftlon)
                       AND (c.topleftlat, c.bottomrightlon)
   GROUP  BY c.coordinates_id, date_trunc('hour', t.calltime)
   )
, cal AS (
   SELECT generate_series(GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
                        , LEAST('2012-04-01 05:00:00'::timestamp, v._to)
                        , '1 hour'::interval) AS stamp
   FROM v
   )
SELECT q.coordinates_id, cal.stamp, COALESCE (q.zcount, 0) AS zcount
FROM   v, cal
LEFT   JOIN q USING (stamp)
WHERE (cal.stamp::time >= v._from::time OR
       cal.stamp::time <= v._to::time)
ORDER  BY q.coordinates_id, stamp;

Instead of looping through rows in table coordinates, join to the CTE and produce the whole result in one go.
As you aggregate per row of coordinates we need the primary key of this table (or any other unique set of columns) I assume a pk named coordinates_id.
I added the CTE v (for "values") on top to provide _from and _to timestamps once only.
I use _from and _to to limit the time range of the calender right away, instead of adding WHERE clauses to trim the surplus in the final SELECT.

GREATEST('2011-02-02 00:00:00'::timestamp, v._from)
LEAST('2012-04-01 05:00:00'::timestamp, v._to)

I use "ad-hoc rows" like demonstrated in this related answer by @kgrittn for a much simpler JOIN condition:

     ON (t.lat, t.lon)
BETWEEN (c.bottomrightlat, c.topleftlon)
    AND (c.topleftlat, c.bottomrightlon)

I cast to time (::time) instead of using extract ('hour' ..), because it's simpler and faster.

I am not 100 % sure this is exactly what you are after, but it should be very close.


Why can vectorization reduce the use of virtual functions? Vectorized Query can be very helpful to get a high-performance analytical systems.

I know that vectorization can execute all operations on blocks of values (SIMD instructions) instead of interpreting a query expression tuple at a time.
But how can I understand ,the vectorization can reduce the overhead of virtual functions?

This question came to me when I read a paper from TU Munich:
Data Blocks: Hybrid OLTP and OLAP on Compressed Storage using both Vectorization and Compilation

  The jump in query evaluation efficiency is typically achieved by using vectorized execution where, instead of interpreting query expressions tuple at a time, all
  operations are executed on blocks of values. The effect is reduced interpretation overhead because virtual functions implementing block-wise operations handle thousands of tuples per function call, and the loop over the block inside these function implementations benefits from many loop-driven compiler optimizations including the automatic generation
  of SIMD instructions.

Such formulation could also be found in other DBMS papers, because vectorization is a popular way.

This question is related with general DBMS and the Paper from TUM is related with HyPer, which is a not so "famous" DBMS. I would be glad to receive any kinds of examples, explanations. If I have wrong with formulation, just feel free to edit it.

Glancing through the paper, it sounds like the answer is as simple as this:

for "row-based" (non vectorized) operations, the (virtual) functions used to process query results have to be called once per row.
for "batch-based" (vectorized) operations, the (virtual) functions are called once per batch, and then optimized loops can be run inside the virtual function implementation (reducing virtual function overhead by virtue of just calling those functions less times)

The is mostly covered in the quote you mentioned

  ...virtual functions implementing block-wise operations handle thousands of tuples per function call...

Calling a function once, to handle thousands of rows, is going to much cheaper than calling the function thousands of times, once for each row.

In any program, there is overhead to calling functions, especially virtual functions (looking up the actual function implementation's address in a lookup table, missing out on certain compile-time optimizations because the actual implementation function isn't known until runtime, etc - see In C++ why and how are virtual functions slower? for more discussion on virtual function overhead).

Looking to SQL Server's implementation of batch-based query processing (I realize the paper is about HyPer, but the general principles hold), you can find other sources discussing the per-row vs per-batch cost savings in terms of CPU.  Consider this post:

Columnstore Index Performance: BatchMode Execution

It compares row based processing:

  Query was executed in ROW mode. The predicate was applied to 3580 rows, one row at a time, to get 1200 qualified rows
  ...The actual CPU cost was 15 ms...

To batch based processing:

  The query (specifically SCAN operator) was executed in BatchMode. There were two batches, each containing 900 rows, were executed. The predicate was applied to 900 rows at one time
  ...the actual execution time is < 1 ms (the minimal time that can be measured)...

Processing query results in batches thus reduces CPU costs in SQL Server for a number of reasons, but virtual function call overhead is likely one of them (although it's probably less significant than, say, operating on compressed data, applying predicates per batch, calculating aggregates per batch, etc).


How to extract a whole database schema using only ODBC access? I need to migrate an old proprietary database (custom database based on a Btrieve engine) to a newer system . All I have to access this database is an ODBC driver that works pretty well. When I connect to the database, I only see views but still the database documentation provides the table names and I am able to access them even though the driver does not show them.

Unfortunately the database documentation is quite poor and does not allow me to generate the full schema. Some information like the column precision and scale is missing.

So is there a tool that would allow me to read the full database schema and dump it to a file ?

I really just need a description of the the tables like table name, column name, datatype, precision, scale and optionally comments. I don't even need relationship and keys. For the rest, I have enough skill to process those information and generate a script for my destination database.

Regards.

Following on from chat in "The Heap"...

Visio can reverse engineer a database. However, I assume thus needs to read metadata or system tables to get everything (keys, DRI, datatypes the works).

This old MSDN article may help "Migrating Btrieve Applications to MS SQL Server 7.0" too


INSERT / UPDATE to merge data, when empty data is an empty string Consider a table of some family members:

mysql> SELECT * FROM some_table;
+--------+--------+--------+
| f_name | l_name | work   |
+--------+--------+--------+
| Meirav | Cohen  | school |
| Dotan  | Cohen  |        |
+--------+--------+--------+
2 rows in set (0.00 sec)

Assume that f_name is the primary key, and that all columns are VARCHAR. Unknown data are not represented as NULL, but rather as an empty string (as can be seen in the work column).

I would like to add some data that I know about a person, but I would prefer to not query the database to see what is there but rather have the database merge the extant value with the new values:

mysql> INSERT INTO some_table (f_name, l_name, work)
    VALUES ('Dotan', '', 'php')
    ON DUPLICATE KEY UPDATE
        f_name = IFNULL(f_name, 'Dotan'),
        l_name = IFNULL(l_name, ''),
        work   = IFNULL(work,   'php');

Of course, IFNULL doesn't fit the job as it only checks for NULL, not for empty strings. Also, there is the disadvantage that each value to add must appear twice in the SQL.

Is there any way to have the database merge the data against empty strings? Also, can it be made such that the strings to insert appear only once in the SQL?

To avoid putting values two times in your INSERT statement use VALUES(<column_name>) function.

  INSERT ... ON DUPLICATE KEY UPDATE Syntax
  You can use the VALUES(col_name) function in the UPDATE clause to
  refer to column values from the INSERT portion of the INSERT ... ON
  DUPLICATE KEY UPDATE statement. In other words, VALUES(col_name) in
  the ON DUPLICATE KEY UPDATE clause refers to the value of col_name
  that would be inserted, had no duplicate-key conflict occurred. This
  function is especially useful in multiple-row inserts. The VALUES()
  function is meaningful only in INSERT ... UPDATE statements and
  returns NULL otherwise.

Unfortunately it's not very clear in your question what is your desired outcome. But assuming that you want update only columns that have NULL or empty strings you can do

INSERT INTO some_table (f_name, l_name, work)
VALUES ('Dotan', '', 'php')
ON DUPLICATE KEY UPDATE
  f_name = COALESCE(NULLIF(f_name, ''), VALUES(f_name)),
  l_name = COALESCE(NULLIF(l_name, ''), VALUES(l_name)),
  work   = COALESCE(NULLIF(work, ''), VALUES(work));

Here is SQLFiddle demo

If it's not exactly what you want, please consider to post your desired outcome and explain what values under what conditions you want to update


Serial fields at 1 after logical replication I'm migrating my database from one server to another.

So far I've done pg_dump -s and I was able to successfully transfer the schema to my new server. As for the data I went with logical replication, all of the tuples are where they should be, but trying to insert another row leads to an error saying Key (id) = (1) already exists. Is replication the wrong tool for the job, or is there a way to get the tables up to date?

Solved this by dumping just the sequences with pg_dump -t '*_id_seq' dbname > sequences.sql


How can I reinitialize some MyISAM tables without restarting MySQL? I'm using rsync to copy some measurement data from one PC to the server.

Is it possible to load the new MyISAM tables without restarting the MySQL server?

Absolutely !!!
Suppose you are moving a MyISAM table with the following characteristics:

MyISAM table is called mydb.mytable
datadir is /var/lib/mysql
Moving From ServerA to ServerB

You can move it to another folder without disturbing any other DB activity
Here is how
Step01) Create the Target Database on ServerB with this command
CREATE DATABASE mydb;

Step02) Copy the table to the mydb database on ServerB
Run this rsync on ServerA
rsync -av --progress /var/lib/mysql/mydb/mytable.* ServerB:/var/lib/mysql/mydb

That's it. The table should be immediate registered in the information_schema without a restart. To verify that the rsync worked, login to mysql on ServerB and run this:
SELECT COUNT(1) TableExists FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT table_rows TableCount1 FROM information_schema.tables
WHERE table_schema='mydb' AND table_name='mytable';
SELECT COUNT(1) TableCount2 FROM mydb.mytable;

If TableExists is 1 and  TableCount1 =  TableCount2, CONGRATULATIONS !!!
If you want to double check the rsync, run thi command on both ServerA and ServerB
CHECKSUM TABLE mydb.mytable;

Both checksum values should be identical
Give it a Try !!!


Unable to open database after losing data file Last day I've deleted the data files in my database server (Oracle11g in Windows platform) by mistake. Unfortunately, now my database is not opening up. It's showing the following error.

    SQL> startup
ORACLE instance started.

Total System Global Area 2572144640 bytes
Fixed Size                  2283984 bytes
Variable Size             620758576 bytes
Database Buffers         1946157056 bytes
Redo Buffers                2945024 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 10 - see DBWR trace file
ORA-01110: data file 10:
'C:\APP\SERVICEADMIN\ORADATA\TESTPROD\TEST_IDX02.DBF'

I'm having RMAN backups for my database, but I've deleted these backup files as the FRA folder size was getting increased to the maximum. However, I've saved these backup files in my local machine. When I tried to restore the DB using RMAN after coping these files to FRA backup set location, I encountered the below error:

  RMAN-00571: ===========================================================
  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
  RMAN-00571: ===========================================================
  RMAN-03002: failure of restore command at 02/23/2017 14:58:35
  RMAN-06026: some targets not found - aborting restore
  RMAN-06023: no backup or copy of datafile 5 found to restore
  RMAN-06023: no backup or copy of datafile 4 found to restore
  RMAN-06023: no backup or copy of datafile 3 found to restore
  RMAN-06023: no backup or copy of datafile 2 found to restore
  RMAN-06023: no backup or copy of datafile 1 found to restore

As suggested, I tried adding catalog for the backup, but still unable to recover the file.

RMAN> CATALOG BACKUPPIECE 'C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TA
G20170223T112327_DBWY6SJK_.BKP';

cataloged backup piece
backup piece handle=C:\FRA\TRENDZPROD\BACKUPSET\2017_02_23\O1_MF_NNNDF_TAG201702
23T112327_DBWY6SJK_.BKP RECID=27 STAMP=936726443

RMAN> list copy;

specification does not match any datafile copy in the repository
specification does not match any control file copy in the repository
specification does not match any archived log in the repository

RMAN> recover database;

Starting recover at 23-FEB-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=26 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2017 17:48:37
RMAN-06094: datafile 10 must be restored

I am not getting any idea how to restore my database using these files.

You just need to restore or catalog the backups which were stored another location. Follow the following steps.

I assume that you have spfile, controlfile and redo log files in place.

As you said you have copied the RMAN backup in another location, restore that backup to your regular backup location.

Catalog the backup files.

RMAN> catalog start with '/u01/app/oracle/fast_recovery_area/backupset/';

Then use RMAN to restore database.

RMAN> restore database;

Recover the database with available archived logs.

RMAN> recover database;

Then simply try to open the database(use resetlogs clause if you performed incomplete recovery).

SQL> alter database open;

OR

 SQL> alter database open resetlogs;

Done!


DBATools offline install - export-dbainstance was not found I followed the instruction at https://dbatools.io/offline/:

download zip & unzip
run $env:PSModulePath to find all the paths.
copy to C:\Program Files\WindowsPowerShell\Modules.
run Get-ChildItem -Recurse   "C:\Program Files\WindowsPowerShell\Modules" | Unblock-File

When I try to run Export-DbaInstance -SqlInstance I got below error :

  The term Export-DBAInstance is not recognized".

Any help will be appreciated.

You need to import the module using import-module dbatools

See Properly importing the module

  Specifying the path to the .psd1 file

hope that helps you !

Also, what Scott commented is documented in the same link you have (just read the page carefully to see if you are not missing any steps):

  Once you’ve extracted the folder, make sure you rename it from dbatools-master or dbatools-development to plain o’ dbatools.


T-SQL OUTPUT DELETED columns by information schema I have two tables:

Whenewher I'm removing some records from TableFirst I need to insert deleted record data into TableSecond. But they have different number of columns.

Is it possible to output deleted values of common columns using information schema(except Id of course) in delete statement?

DELETE FROM TableFirst
OUTPUT DELETED.* INTO TableSecond
WHERE Id = 1

I'm trying to avoid naming each column (I'll have up to 50 columns in my project).

I find out the way to do it with the dynamic sql, we just need to compose query, where we can select all required column names using information schema and execute it.

Get column names first:

DECLARE @ColumnNamesTable Table([Name] NVARCHAR(MAX));
INSERT INTO @ColumnNamesTable
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableFirst') AND [Name] <> 'Id'
INTERSECT
SELECT [Name] FROM SYS.COLUMNS WHERE OBJECT_ID = OBJECT_ID(N'TableSecond')

Compose OUTPUT DELETED and column name strings:

DECLARE @OutputDeleted NVARCHAR(MAX), @ColumnNames NVARCHAR(MAX);

SELECT @OutputDeleted = STUFF((SELECT ', DELETED.' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')
SELECT @ColumnNames = STUFF((SELECT ',' + Name FROM @ColumnNamesTable  FOR XML PATH ('')), 1,1,'')

Compose query:

DECLARE @Query NVARCHAR(MAX), @MyId INT = 5;
SELECT @Query = CONCAT('DELETE FROM dbo.TableFirst OUTPUT', ' ', @OutputDeleted, ' ','INTO dbo.TableSecond(', @ColumnNames,') WHERE Id = ', @MyId)

Finaly we just execute the composed query:

exec sp_executesql @Query;

But we can get issues when there are some FKs.


SQL Server 2008 mirrored Database with manual failover, need reporting database Our current setup is a principal database used in production with a manual failover mirrored database for HA.

I need to setup a reporting database as well. What would be the best approach? Shall I setup a SQL job to drop and create the snapshot of the mirrored Database and refresh this every night?

Does anyone have any other suggestions?

Thanks

There's a few questions you need to ask first.

Are you using SQL Server Enterprise Edition?  Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise.

Are you already paying for licensing on the mirror?  There's a lot of fine print, but you basically get the mirror for free - only as long as you're not querying it.  However, if you're going to add snapshots on the mirror and offload the reporting work there, then you'll have to start paying for the mirror.

Are you doing synchronous mirroring?  If so, the mirror hardware needs to be as fast as possible in order to keep up with writes on the primary.  Adding query loads to the mirror may slow down your primary's writes, and that may be a no-go for the business.

Is the business okay with data being as-of-yesterday?  No one can be running a query while you drop and recreate the snapshot, which generally means you can only refresh it after-hours.

Do you have enough space for a growing snapshot?  If you do high-volume activities like index rebuilds or reloading ETL tables, your snapshot can grow - by a lot.  Ideally, if your database is 1TB (and nearly full), you'll need another 1TB free for the snapshot - not right away, but it can happen if you do things like rebuild all indexes.  You'll want to know your change rate, design free space for that, and implement monitoring on both free space and database growth.


Back up Full Recovery Model Without Log Backups I have a database that I need to backup to URL, but without the log file.

 BACKUP DATABASE [MyDB] TO URL = @destination
  WITH INIT, FORMAT, COPY_ONLY, CREDENTIAL = '', stats=1

The above script includes the log file.

When I back up to disk (not what I need) with the following command, it does not however back up the Log file - which is what I want

BACKUP DATABASE MyDB
TO DISK = 'F:\SQLServer\MyDB.bak'
WITH COPY_ONLY;

Why would the top script include the log file, and the bottom script exclude the log file?

When you take full database backup it will not take all the log file, and it seems you have some confusion about copyonly option.

If the issue you are facing is free desk space then you can refer to Is it possible to restore sql-server bak and shrink the log at the same time?
if your transaction log is growing too fast then you can start troubleshooting  and check this URLtoo

if the database is used for development environment then you can change it to simple mode this will truncate transaction log for you, but if you are on production site then do not do any changes to transaction log

see what will happen if you truncate logs


/usr/sbin/mysqld was deleted and replaced by another mysqld with different inode This happened on three of our mysql server running on RHEL. While checking for open and deleted files, I found that mysqld in use is deleted (as seen in lsof) and was replaced by a similar mysqld (in /usr/sbin/mysqld) with a different inode. Size and blocks of both the files (deleted and current) are same. new mysqld (not in use) seems to be of the same version as the deleted one. I am trying to figure out what could have caused this (there are no cronjobs running on the system). I checked system logs, database logs and yum logs and found nothing relevant. Any input is appreciated.

Thanks!

At first glance, this seems extremely suspicious, but based on the information below, there may be a very logical and non-dangerous, although counter-intuitive, explanation.

Out of curiosity, I looked for important-but-deleted files on some Linux machines and I found such interesting things as...

COMMAND     PID       USER   FD      TYPE     DEVICE   SIZE/OFF       NODE NAME

bash      30851       root  txt       REG      202,1     959120     142485 /bin/bash
bash      31501       root  txt       REG      202,1     955024     131092 /bin/bash (deleted)

Yikes.  Really?

These systems are apt vs yum systems but the principle is the same... it's inconceivable in the world of windows but entirely sensible and not all that unexpected in the world of Linux (or any Unix afaik) to overwrite files that are "open" by another process.  The space allocated to the files is freed as soon as all of the processes that are holding them open... close them.

The log, in my case, confirms that "bash" was involved in one of the packages that has been upgraded since the last reboot but some of the copies of bash that are running are actually still the old bash that was running before the update.

However... your binaries are the same size where mine are different, so this would seem to suggest that it wasn't an outright upgrade.  This led me to speculate that it might have been an upgrade of the package that contained mysqld but that didn't change mysqld itself, causing the file to be overwritten by an identical copy of itself.  But that seems like speculation.

I don't like speculation, so I kept digging.  I use Ubuntu these days, but still have some old Red Hat machines around, so I took a look in the directories in /etc/cron.* and stumbled upon prelink.

I realize you said there are no cronjobs running on the system... but are you sure about that?  Not even the "factory" default cronjobs in /etc/cron?

01 * * * * root run-parts /etc/cron.hourly
02 4 * * * root run-parts /etc/cron.daily
22 4 * * 0 root run-parts /etc/cron.weekly
42 4 1 * * root run-parts /etc/cron.monthly

If there really are no cron jobs running, then of course, the rest of this post is nonsense, but it would seem very unusual to me to literally have no cron jobs on a Red Hat machine.

Take a look at the timestamp of /var/log/prelink/prelink.log.  If it's a match for the time window in question, then we have a suspect.

I've been a Linux sysadmin for even longer than I've been a DBA and yet somehow this is the first time I remember ever running across this, and frankly, on first reading, it made me nervous that an overnight process was actually poking at bits inside executables, but I guess it's not as problematic as it seems like it would be.

Perhaps it's never caught my attention because I never use the package manager to install MySQL (I see it as too important for anything to touch but me) and the "standard" path for installing official binary distributions is /usr/local/mysql[/bin], which isn't in the paths that prelink touches.

Prelink optimizes program execution and and provides some degree of immunity against exploits based on buffer overruns by making the addresses inside executables and shared libraries somewhat random on a system by system basis.  What you might find, depending on the mix of installed software on each machine, is that each machine's current copy of the mysqld binary is the same size but that they do not actually have the same md5sum or sha1sum checksums as each other even if they're identical versions.  Before today, that would have made no sense at all... but given what I found on prelink... it wouldn't surprise me at this point.

http://people.redhat.com/jakub/prelink/prelink.pdf

I think it seems very likely that this is what's happened.  If your servers are properly firewalled and nothing else seems amiss, it seems unlikely that an intruder would have deliberately and successfully compromised multiple machines in the same way.

If the above turns out to be a likely candidate, you should find, curiously enough, the you can still get to, and make copies of, those files that are deleted, as long as you don't shut down the running mysqld process.  I categorically do not recommend that you put them back but if you are curious, you might find the following informative.

Find the process ID of mysqld from lsof and cd into the /proc/nnnnn directory on your system, where nnnnn is the process id.

Inside this directory you'll find what appears to be a broken symlink called exe.  This is not exactly what it seems, because it actually still references the deleted file in a very real fashion.

I'll illustrate the rest of this with one of my deleted copies of "bash" from above.  The process ID was 31501.

root@host:/# cd /proc/31501
root@host:/proc/31501# ls -l exe
lrwxrwxrwx 1 root root 0 May  1 06:41 exe -> /bin/bash (deleted)

You can actually cp exe /some/new/place and salvage this deleted file, precisely because it is still open.  Again, I wouldn't recommend that you start using this copy, but this is the one that is on the disk that has been deleted, if you want to do any detailed byte by byte analysis.

For a coarser example, you could do a checksum of it.  Again, the example is from my system, with the deleted "bash" which is process 31501.

root@host:/proc/31501# md5sum exe
748c6ee926dd3cf55bcfb60ce928d367  exe

Not surprisingly, that's not the same checksum as the live copy of "bash" on my system.

root@host:/proc/31501# md5sum /bin/bash
3cac23b8442e3971f1d10660cc779b9f  /bin/bash

However, by lucky coincidence, I have another machine that has not been upgraded as recently as the one above.  You'll notice this copy of bash is exactly the same size as the deleted copy on my machine... and it even has the same checksum.

user@older:~$ ls -l /bin/bash
-rwxr-xr-x 1 root root 955024 Apr  3  2012 /bin/bash
user@older:~$ md5sum /bin/bash
748c6ee926dd3cf55bcfb60ce928d367  /bin/bash

You also could... but you really really really shouldn't... actually run the file represented by "exe" from inside the process directory of mysqld (mine is running on 21691).  Seriously, don't do it.  But here's what it will do, if you're curious.  The executable is using its named on disk to identify itself as ./exe ... but it's MySQL all right:

/proc/21691# ./exe --version
./exe  Ver 5.6.10 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))

How about that?


Can I refuse inserts/updates with timestamps without time-zone in postgresql? I use exclusively timestamps with time-zone in PostgreSQL, but I just noticed something slightly worrying: If a client inserts/updates a record with a naïve time-stamp, PostgreSQL falls back to a default time-zone which is either given by an environment variable, localtime or a session-setting.

This might not always be correct.

Is it possible to configure PostgreSQL in a way that it will raise an error if it sees a value without time-zone?

Not sure if it is feasible in your case, but you could use functions for inserting data. Use a text parameter for the timestamp and throw an error if the string doesn't contain a + or -.


SQL ODBC silently truncating numeric data We have a middleware that replicates data from an Oracle (11g) database to a SQL Server (2012) using ODBC connection to the destination. One of the tables has a NUMBER data type on Oracle and given there is no exact equivalent on SQL Server, we have used DECIMAL (18,0) on the SQL Server side in hope of catching errors if the data actually has fractions. However we've noticed that replication works without any error while truncating the fractions from the numbers. We traced the queries coming from the middleware and there are bunch of sp_prepexec statements where parameters are defined as the target type (e.g. DECIMAL(18,0)) and values are already converted before reaching SQL server and hence no error despite having NUMERIC_ROUNDABORT ON on the SQL Server. Is this how ODBC supposed to work? Are there ways to force errors on these type of data type conversions?

If the parameter to the ODBC prepared statement is defined as decimal(18,0), that's how it's transmitted to the server.  The conversion is performed by the ODBC driver.  AFAIK the driver is not required by the standard to emit a diagnostic when dropping the fractional part on conversion to an integral type.  Presumably the user knows what he's doing.  ;-)

I think you're mistaken, though.   From what I make of the NUMBER datatype, it's identical to SQL Server's decimal: you specify scale and precision up to a maximum of 38 digits.  How are they incompatible?

If you really can't pass the data as a decimal type, I think I'd pass it as a varchar(30) and use a constraint on the SQL Server table to make sure it conforms.


Should I use an int column to represent date? I am designing a Invoice table and would like assistance deciding on a couple indexes and the Primary Key.

For the Primary Key - using GUID may sound like a good idea to me, but does this mean the child tables need to have this very GUID as a foreign key as well?
Invoices are always filtered on creation date using a date range. So instead of a datetime column, I plan to have an extra int column which only contain the date information like '20160101' because I assume comparing the int is much faster than datetime and would benefit me. Since the creation time will be ever increasing, I also plan to apply the Cluster Index on this column. Is this a good idea?
Invoices may also be queried based on customer as well, should I put another index on CustomerID or compose with the date column?

Shorter answer to the title of the question: No. Why would you want to lose the ability to treat a date like a date. So important for sorting, date functions, etc.

A few thoughts to get you started at least, not sure which DBMS while answering, answering based on my experience with SQL Server:

1.) GUID as a Primary Key is generally not a great idea. Not in SQL Server especially. What's wrong with an Integer primary key? Yes whatever your Primary Key is becomes your Foreign Key in the child table(s) so that is large and probably not necessary.

2.) I would use a regular datetime column. Performance wise, if you are well indexed, you shouldn't notice a difference here. And a datetime column is more versatile as, well, a date. You can ask questions of it that you can't ask as easily of an INT column with built in datetime functions. If you don't need time and you are on a DBMS or version that has just the DATE you can use that data type.

3.) Yes. Especially if CustomerID is a foreign key to a Customers table. Good to index. Whether or not you need to make an index on CustomerID AND the Date column depends on how the queries will typically look. If you are often querying joining to customer and specifying a date range you may find it beneficial to have the date. You may find it beneficial to include some other columns to cover other queries as parts of the key or included columns. It really depends on your queries and data, though.

As far as clustering on the date column. That's a hard one. If this were a fact table in a warehouse and every single query was always on a date range, then there are some benefits there. If this is an operational invoice table, I imagine your app also joining into invoices in other approaches also. I also imagine invoices being queried by invoice IDs stored in other tables, etc. So I don't believe there is enough to determine clustered key. I'm of the school that prefers a simple surrogate key for OLTP tables. An InvoiceID INT (or BIGINT if you really would blow out an INT) setup as an identity column so it is always increasing and avoiding page splits. But I don't know if there is a definite wrong answer here (well there are many, but you haven't proposed any of those)


Why does this SQL work on one version of MySql-Server but not another? I'm trying to run the following very simple SQL command on two different MySQL databases:

CREATE TABLE mytable (
    created DATETIME(6) NOT NULL,
    updated DATETIME(6) NOT NULL,
    my_id INTEGER NOT NULL AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    PRIMARY KEY (my_id),
    UNIQUE (name)
);

This mysql fails when I run it against mysql-server 5.5.
But it succeeds when I run it against mysql-server 5.6. Why?

Below you can see how actually ran it. Apparently 5.5 doesn't allow giving an integer-size with a DATETIME column.

Failing Case:

    # mysql -D my_db_instance -h 127.0.0.1
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 938
    Server version: 5.5.46 MySQL Community Server (GPL)

    Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );

    ERROR 1064 (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 '(6) NOT NULL,
        updated DATETIME(6) NOT NULL,
        my_id INTEGER NOT NULL A' at line 2

Succeeding Case:

    $ mysql -u myuser -p -D my_db_instance -h 127.0.0.1
    Enter password:
    Reading table information for completion of table and column names
    You can turn off this feature to get a quicker startup with -A

    Welcome to the MySQL monitor.  Commands end with ; or \g.
    Your MySQL connection id is 6
    Server version: 5.6.21 Homebrew

    Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.

    Oracle is a registered trademark of Oracle Corporation and/or its
    affiliates. Other names may be trademarks of their respective
    owners.

    Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

    mysql> CREATE TABLE mytable (
        ->     created DATETIME(6) NOT NULL,
        ->     updated DATETIME(6) NOT NULL,
        ->     my_id INTEGER NOT NULL AUTO_INCREMENT,
        ->     name VARCHAR(255) NOT NULL,
        ->     PRIMARY KEY (my_id),
        ->     UNIQUE (name)
        -> );
    Query OK, 0 rows affected (0.01 sec)

The datatype DATETIME used to be only yyyy-mm-dd hh:mm:ss.  But, beginning with 5.6.4(?), fractional seconds were allowed.  DATETIME(6) allows for microseconds:  yyyy-mm-dd hh:mm:ss.ffffff.

If you want that CREATE to work on both versions, remove the (6).  Notice how the error message pointed exactly at that.


how azure sql geo replication works internally? My company moved to azure and started to use azure Sql database and geo replication.

For our on-premise servers, we heavily rely on Microsoft transactional replication to move data. Now we setup transactional replication between on-premise server (publisher) and azure sql db. Then we found that the transactional replication itself is constantly consuming 100% DTU on azure(we are using a 125 DTU P1).

We upgraded it to 250 DTU and replication is much happier. However, we are not sure it is a great idea to constantly updating to higher DTU. So we did this:

Created two 125DTU servers.
Setup transactional replication between on-premise server and server A.
Setup geo replication between server A and server B.
Our customers will use server B.

The benefit is that heo replication will not trigger the 100% DTU throttle, and when we raised this to a Microsoft support, he also said it is a good idea. The support person also said that sql geo replication is much more efficient to transactional replication.

However, when we asked him what kind of technology azure geo-replication internally uses and why it is better than old transactional replication, he just gave out some marketing reply:

geo-replication leverages the Always On technology of SQL Server to asynchronously replicate committed transactions on the primary database to a secondary database using snapshot isolation. Impact to your main database is minimal as this process runs with the same context where the automated backups run, for example.

Personally, I didn't really get why geo replication is better than old replication from the reply. Does anyone know some technical detail?

As you were told by Support, Azure SQL Database's geo replication uses a special flavor of Always On Availability Groups to replicate data. The "special flavor" doesn't matter much in the context of your question, so I am going to respond in terms of "Replication vs Availability Groups" since it applies to your case, too.

Replication

SQL Server Transaction Replication is a way to replicate specific data between databases. By default, it will replication data changes between the Published and the Subscriber. The Distributor has a Log Reader Agent that reads the transaction Log of the Publisher, and converts that into separate update statements, which are sent to the Subscriber via a Distribution Agent.

A single update statement that updates 1,000 rows on the Publisher will generate 1,000 separate update statements on the Subscriber, doing row-by-row updates based on the PK.

There are alternatives, but in most cases, this is the configuration people use, as it is the default behavior. (For example, you can configure stored procedure executions to replicate, so that rather than replicating row-by-row data updates, the Subscriber executes the stored procedure again... You probably aren't doing that.)

As you can imagine, this row-by-row update on the subscriber can be inefficient. Each individual update on the Subscriber will be quick because it is a single-row update based on a Primary Key--but the row-by-row vs set-based operation isn't always ideal.

Availability Groups

Availability Groups (AGs) handle things differently. With AGs, the entire database is replicated, not just specific data. The replication method involves writing to the transaction log on the secondary server, and then redoing the transaction on the secondary server. These two steps are generally referred to as "send" and "redo" phases.

A simplified version of how it works: The overhead on the Primary server for "send" is essentially just that it has to write to the transaction log locally, and also send that same exact information to the Secondary for it's copy of the transaction log. The overhead on the Secondary server for "redo" is essentially just processing that transaction the same way it was handled on Primary.


What problems are there in not using a primary key column at all? What purpose would a primary key column serve on a table such as post_versions here? Nothing refers to it, and there are no queries where I will ever want to select a row by post_versions.id. It'll be joined to posts in most queries.

CREATE TABLE posts (
    id       SERIAL PRIMARY KEY,
    created  TIMESTAMPTZ NOT NULL DEFAULT now(),
    user_id  INTEGER NOT NULL REFERENCES users ON DELETE RESTRICT
);

CREATE TABLE post_versions (
    id       SERIAL PRIMARY KEY, -- serves no purpose, could remove I think
    post_id  INTEGER NOT NULL REFERENCES posts ON DELETE CASCADE,
    updated  TIMESTAMPTZ NOT NULL DEFAULT now(),
    body     TEXT NOT NULL
);

Are there any problems if I just remove it?

there are no queries where I will ever want to select a row by
  post_versions.id

If you have a column that you know you'll never have any use for whatsoever, be it a synthetic key or any other kind of column, then sure, remove it.

The problem with a table without a primary key, is that it can potentially  have duplicate rows, and, in the relational model, duplicate tuples do not exist.
So if your table happens to have duplicate tuples, it would violate a fundamental rule of the relational model.

But that's not necessarily a reason to create a synthetic key that you have no use for, for the sole purpose of having a primary key. Firstly, the way your table is structured with its updated column, it's very unlikely that it will ever have duplicates. Secondly, even if it happened, the consequences of having duplicates must be evaluated relatively to what is done with the data. When for instance a table is used to store log entries to be only displayed, there is no problem having duplicates. On the other hand, if an application had to update some specific row for some reason , then the absence of any primary key would be a problem and probably reveal a design error.


Query a table with linked objects as columns Title of the question is not clear, I'll try to describe it.

In my model I have the following :

A Network object includes Lines, and a Line object includes Routes (this is for public transportation).

Now I have a Subscription table where I handle user subscriptions for travelling on Networks, Lines or Routes :

╔════╦═════════╦══════╦═══════╗
║ ID ║ Network ║ Line ║ Route ║
╠════╬═════════╬══════╬═══════╣
║ A  ║       1 ║ 6    ║ 12    ║
║ B  ║       2 ║ 3    ║ NULL  ║
║ C  ║       1 ║ NULL ║ NULL  ║
╚════╩═════════╩══════╩═══════╝

That means :

Subscription A is valid only on route 12
Subscription B is valid on all routes of line 3
Subscriptions C is valid on all routes of network 1.

I need to get all subscriptions allowed on route#12, i.e. I need to fetch first and third row.

What kind of request would allow me to do so please ?

In the end I'll be doing this in Java with JPQL, but I'd like to understand how to do it in plain SQL first.

Here's a SQL Fiddle in case anyone is willing to help.

This is not the best way of writing this Query but this should do the trick.

SELECT
    *
FROM YourTable
WHERE ((Line IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
AND [Rout] IS NULL)
OR [Route] = 12)
OR (
Network IN (SELECT DISTINCT
    Network
FROM YourTable
WHERE [Route] = 12)
AND (LINE IS NULL
OR LINE IN (SELECT DISTINCT
    LINE
FROM YourTable
WHERE [Route] = 12)
))

You can change those where conditions to joins. As you can see, its performing 3 select statements in IN Clause (one of which is a repetition) which may affect performance. Something like:

SELECT
    Main.*
FROM Subscription as Main
LEFT OUTER JOIN Subscription as Lines ON Lines.[Route] = 12
LEFT OUTER JOIN Subscription as Networks ON Networks.[Route] = 12
WHERE
    Main.[Route] = 12
    OR (Main.Line = Lines.Line and Main.Route IS NULL)
    OR (Main.Network = Networks.Network AND (Main.Line IS NULL OR Main.Line = Lines.Line ))


auto_explain does not log looks like i do it wrong. I turned auto_explain on with adding next valued to postgresql.conf:

shared_preload_libraries = 'pg_stat_statements,pg_wait_sampling,pg_stat_kcache,auto_explain'
auto_explain.log_min_duration = '3s'

in my monitoring I can see long running queries that exceeded limit of 3s and successfully finished.

'values' here says how long query lasted:

But when I look into my log 'postgresql-Mon.log' (and next days) there is nothing about those queries. Only a few strings about modules during database startup process.

Am I missing something?

Look over the documentation regarding this extension

pg_stat_statements  does not persist between restarts...
pg_stat_statements

pg_stat_statements_reset() will clear all data


MySQL will not run after exhausting disk space while indexing After I tried to index a column in a huge table (40G) and ~5million rows, I can no longer connect to the datatbase.

Indexing failed with this error

Later, I found out I ran out of disk space while mysql was stopped. And, now I can not get mysql run again. I tried almost everything on SO except where purging mysql/reinstalling was mentioned. Is there anything I can do to get my data back or run mysql again? Or, I could extract my tables from ibdata1?

There could be about 3 things going on here...

You ran out of disk space.  Can you scrounge some space, even a little?
You cannot connect because of a socket problem.  Remove the socket and try again.
The attempt to add an index could have been performed in a couple of different ways.  Was the table MyISAM?  Or InnoDB?  If InnoDB, was innodb_file_per_table ON?

In any case, look for #sql... files they were the tmp tables being built before "renaming" them into place.  They are useless no; remove them.

If file_per_table was off, then probably ibdata1 got a lot larger in this failed operation.  This will call more a messier repair.

Since you have an old version of MySQL, I won't point out ALTER TABLE .. ALGORITHM=INPLACE, ADD INDEX ..;


Am I breaking something if I set an autoincrement PK to 0? I have this table
CREATE TABLE `test_table` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name_UNIQUE` (`name`)
) ENGINE=InnoDB;

Insert some records...
INSERT INTO `test_table` (`name`) VALUES ('a');
INSERT INTO `test_table` (`name`) VALUES ('b');
INSERT INTO `test_table` (`name`) VALUES ('c');

So I get records 1, 2 and 3 with values a, b and c respectively.
Now, I need to have a record with 0 as id and empty string as name. So I did this
INSERT INTO `test_table` (`id`, `name`) VALUES ('0', '');
UPDATE `test_table` SET `id`='0' WHERE `id`='4';
ALTER TABLE `test_table` AUTO_INCREMENT = 4 ;

Do I have a guarantee that everything will continue to work properly? Table references, auto increment sequence, etc...

Although not usual practice, a primary key can be 0 because 0 is a valid integer. If you set auto increment on the column it will normally start from 1, however, manually changing an id on an existing row to 0 will not cause any issues. If you have any foreign keys that referenced ID 4 before you changed it to 0, then you'll need to update those as well.
Note that if you use 0 in an INSERT for a primary key, MySQL interprets that as a request to generate a new key. In those cases, you can use the following to tell MySQL to interpret 0 INSERTs as a true 0, not a PRIMARY KEY generation request.
SET SESSION sql_mode='NO_AUTO_VALUE_ON_ZERO';


Client not reading from Mongo secondary database I've implemented a replica set that I'm using globally. I have my master in Oregon, US and 4 secondaries. California and Virginia, Frankfurt and Sydney. I also have web servers in those same regions as well. Those web servers connect to mongo using mongoose:

var mongoose = require("mongoose");
var dbUrl = "mongodb://***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017,***.***.***.***:27017/exampleDb";
var dbOptions : {
   "replSet": {
      "rs_name": "exampleRepSet",
      "readPreference": "ReadPreference.SECONDARY_PREFERRED",
      "read_preference": "ReadPreference.SECONDARY_PREFERRED",
      "w":0,
      "slaveOk": true
    }
}
mongoose.connect(dbUrl, dbOptions);

My problem is that my client's have higher latency to the database depending on how far away they are from the master. California get 40ms while Sydney gets 400ms. I don't understand why this is happening since they should be reading off of the secondary database in their region.

I understand that writes must be done to the primary but even if I perform a find then shouldn't it be done on the regional secondary and return pretty quick?

I realize there are some redundant options in that config but I'm getting desperate. I've also tried the option "ReadPreference.NEAREST" to no avail.

Using "ReadPreference.SECONDARY_PREFERRED" does not provide any such guarantee to read from a node in the same region.

I'm not familiar with Mongoose but the first thing I would do is prove this via the Mongo shell first. Second I would remove the redundancy you acknowledged above. If it shouldn't be there, get rid of it. Via the shell; try using a read preference of "secondaryPreferred" but really what I think you want is "nearest" which will read from a node with the least network latency.
Again; I cannot speak towards Mongoose specifically but there are some non Mongoose related to resolve first per what you shared.

The best this will do is confirm for you if you have a Mongoose issue or not.

Also; using a WriteConcern of 0 and reading from Secondary nodes is not a terrific practice if you rely on the data being on the secondary. If you are okay with the eventual consistency, then a write concern of 0 and reading from secondaries is fine.