How to model the following problem? Let's say, I have the following tables in MS-SQL Database:

Please note: This database composition is just fictional, because I'am not allowed to share sensitive business data.

Category

Id (int, pk)
Name (varchar(50))

Game

Id (int, pk)
Name (varchar(50))
CategoryId (int, fk: Category->Id)
etc...

Participant

Id (int, pk)
Name (varchar(100))
etc...

Composition

Id (int, pk)
ParticipantId (int, fk: Participant->Id)
GameId (int, fk: Game->Id)
Date (date)
Time (time)

The problem is that, only one Game per Category per Participant is allowed for a given Date and Time.

So for example:

Tim can participate only in one game from one category at a time.

Valid composition:

Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
Tim -> DOOM (Video games) -> 2017/02/02 -> 12:00
Tim -> Football (Ball games) -> 2017/02/02 -> 13:00
Tim -> DOOM (Video games) -> 2017/02/02 -> 13:00

Invalid composition:

Tim -> Football (Ball games) -> 2017/02/02 -> 12:00
Tim -> Baseball (Ball games) -> 2017/02/02 -> 12:00

My first idea was to set a unique index to Composition table ParticipantId + GameId + Date + Time, but it still allows me to add two or more games from the same Category.

Finally the question is, is it a design problem, or I just have to add a check constraint to maintain data consistency?

One way to enforce this is to add CategoryID in Composition. Then you can add a UNIQUE constraint on (ParticipantID, CategoryID, Date, Time).

This requires to modify the foreign key to Game and to add a UNIQUE constraint on Game (or modify the existing one). The tables that need changes:

Game
    Id (int, pk)
    Name (varchar(50))
    CategoryId (int, fk: Category->Id)
    etc...
      UNIQUE (CategoryID, Id)                            -- added

Composition
    Id (int, pk)
    ParticipantId (int, fk: Participant->Id)
    CategoryId (int)                                     -- added
    GameId (int)
      FK (CategoryId, GameId) -> Game (CategoryId, Id)   -- modified
    Date (date)
    Time (time)
      UNIQUE (ParticipantID, CategoryID, Date, Time)      -- added


I am looking for a query to determine when the snapshot has been finished applying on the subscriber Is there any way to determine with a query when the replication has finished applying the snapshot on the subscriber?
I have a transnational pull replication set up for reporting purposes and it also includes the non-clustered indexes. Both servers are SQL Server 2016. When I set it up manually I can see in the replication monitor when the snapshot is getting created, delivered and applied (all data copied and the non-clustered indexes are created). After it is getting applied and I have no more undistributed commands, I start some SQL Agent jobs that will make some adjustments to the non-clustered indexes. I've automated the start of the replication and I would like to automate also the start of the SQL Agent jobs, but I cannot find any way to query this.
Normally, when SQL Server will show something in a GUI, there is an underlying system table or system stored procedure that will contain or returns that same information. For instance for the number of undistributed commands I can use this: sp_replmonitorsubscriptionpendingcmds when scripting, but for whether the snapshot is still getting applied on the subscriber side or it is completed, I cannot find anything and it is driving me crazy, I've been searching for days, so at this point any kind of helped is welcomed.
I've already looked into scripting out the actions of the SQL Agent jobs and including them as a post-script in the replication with sp_addscriptexec, but due to internal rules and way of working I cannot change them.

I don't fully understand the part where you said:

but due to the internal rules and way of working I cannot change them

Could you elaborate on that a little more?...what can you not change, the actions of the Agent Job?
In any case, why not just start the existing SQL Agent jobs in a post-replication script using the system stored procedure sp_start_job?
You can specify the name or id of the Agent Job you want to run like this:
-- Executes the job by job id (unique identifier)
EXEC sp_start_job '53A3FA3E-2AAE-4FF1-A853-DEEC1FF494A2'

-- Executes the job by job name
EXEC sp_start_job 'AgentJobName'


How to measure the size of InnoDB's fulltext index How can I determine how much space MySQLs fulltext index is taking up, using innodb?

Solution would ideally be by querying an information_schema table rather than looking at the filesystem.

I don't know the answer; let me try to figure it out.  First, I don't see anything likely in information_schema or mysql.  In the filesystem, in the directory with same name as the database, look for files starting with FTS_.  If you have multiple tables with FULLTEXT indexes, segregate them by prefix, then sum up the sizes.  For one table, I see

SHOW CREATE TABLE fti

CREATE TABLE `fti` (
  `msg_id` mediumint(8) unsigned NOT NULL,
  `title` varchar(255) NOT NULL,
  `msg` longtext NOT NULL,
  PRIMARY KEY (`msg_id`),
  FULLTEXT KEY `title_msg` (`title`,`msg`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `msg` (`msg`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;

mysql> SHOW TABLE STATUS LIKE 'fti'\G

           Name: fti
         Engine: InnoDB
        Version: 10
     Row_format: Compact
           Rows: 175483
 Avg_row_length: 1766
    Data_length: 310050816
Max_data_length: 0
   Index_length: 3686400
      Data_free: 4194304

ls -l FTS* (blank lines added)

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 30408704 Jan 16  2017 FTS_0000000000000090_0000000000000107_INDEX_6.ibd

-rw-rw---- 1 mysql mysql   507904 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 13631488 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 11534336 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 14680064 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 10485760 Jan 16  2017 FTS_0000000000000090_000000000000010e_INDEX_6.ibd

-rw-rw---- 1 mysql mysql 37748736 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_1.ibd
-rw-rw---- 1 mysql mysql 79691776 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_2.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_3.ibd
-rw-rw---- 1 mysql mysql 50331648 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_4.ibd
-rw-rw---- 1 mysql mysql 83886080 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_5.ibd
-rw-rw---- 1 mysql mysql 29360128 Jan 16  2017 FTS_0000000000000090_0000000000000115_INDEX_6.ibd

-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_BEING_DELETED.ibd
-rw-rw---- 1 mysql mysql    98304 Jan 16  2017 FTS_0000000000000090_CONFIG.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED_CACHE.ibd
-rw-rw---- 1 mysql mysql    98304 Jan  9  2017 FTS_0000000000000090_DELETED.ibd

The files add up to about 340MB for the largest index (title+msg).

The total text size (SUM(LENGTH...) is 220MB; 310MB is about right once you add on the off-record storage overhead.  It seems reasonable for the FT index for a text column to be about the same bulk as the text.


Restore only one datafile in SQL Server 2008 from partitioned database We have a partitioned database that I'd like to get copied over on a semi-regular basis to another server for testing updates. Since the partitioning is based on a date column, the most current data exists in one file.

Is it possible to restore just one datafile from the backup to the other server?

Complicating issue: we use Symantec's BackupExec. If this is possible using plain ol' backup from SQL Server, I can probably get that done, though.

You can do a piecemeal restore of this data file if:

you are in full recovery mode (this is not clear in the question - I think there are many configuration options for BackupExec)
you have more than one filegroup (this is not clear in the question)
this data file is read/write and in its own filegroup (otherwise you will also need all of the other data files in this filegroup). I assume this is the case because, how else could you know that this partition exists only in this one file?
you can restore the primary filegroup and then this filegroup (in other words, the rest of the data is NOT in the primary filegroup - also not clear in the question)
you are on Enterprise Edition (again, unknown)

More details in the following articles on MSDN:

Performing Piecemeal Restores
Example: Piecemeal Restore of Only Some Filegroups (Full Recovery
Model)


Creating Windows login/user for Mixed Always on Environment+ Instance without Always ON How can i use by making changes to below  script to run in the environments with Always ON enabled/and not enbaled. Below seems to be working after some really helpful suggestions from Kin. However this code would certainly not be at its best when i need to present for deployment across 300-400 SQL servers with some having always on others not.

Please help me in modifying /check if instance has AG enabled and execute with success

UPDATE As requested by @Kin

SET NOCOUNT ON

 DECLARE @user_to_be_added sysname
 SET @user_to_be_added = 'Domain\user'

 DECLARE @sqltext nvarchar(max) = N''

     if (select serverproperty('IsHadrEnabled')) = 0

  BEGIN

           -- change  to your user to be added .. make sure that the login is already existing

       SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)

       FROM sys.databases where state_desc ='ONLINE' AND databaseproperty(name,'isReadOnly') = 0

       SELECT  @sqltext

 END

    Else

 BEGIN

      SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)

      FROM sys.dm_hadr_availability_replica_states ars
      inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
      where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
      and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

      if  (@sqltext <> '')
       begin
           select @sqltext
       end
      else
        begin
           print 'There is nothing to print - Probably its Secondary Replica of AG'
        end

 END

My understanding is that you are not approaching this in the right way. you just copy the login using the same sid to all the secondary server. Users get replicated since db is part of AG.

Basically you want to create/replicate logins that are created on primary server to all secondary servers in an AlwaysON availablity group topology.

You can just use dbatools Sync-DbaAvailabilityGroup that will take care of

  Syncs depdendent objects for availability groups. Such objects include:

SpConfigure
CustomErrors
Credentials
DatabaseMail
LinkedServers
Logins
LoginPermissions
SystemTriggers
DatabaseOwner
AgentCategory
AgentOperator
AgentAlert
AgentProxy
AgentSchedule
AgentJob

  Note that any of these can be excluded.

Edit : Below script will help you :

SET NOCOUNT ON
DECLARE @user_to_be_added sysname
DECLARE @sqltext nvarchar(max) = N''

-- change below to your user to be added .. make sure that the login is already existing
SET @user_to_be_added = 'Domain/Login'
SELECT  @sqltext += char(10)+ 'use '+quotename(name)+';'+char(10)+ 'CREATE USER '+quotename(@user_to_be_added)+' FOR LOGIN '+quotename(@user_to_be_added)
FROM sys.dm_hadr_availability_replica_states ars
inner join sys.databases dbs ON ars.replica_id = dbs.replica_id
 where dbs.state_desc ='ONLINE' AND databaseproperty(dbs.name,'isReadOnly') = 0
and ars.role_desc = 'PRIMARY' -- we want to filter only Primary AG where dbs are writable.

 if  (@sqltext <> '')
 begin
  select @sqltext
  end
  else
    begin
    print 'There is nothing to print - @sqltext is blank'
    end


How can I handle unwanted, non-erroneous situations better? When writing procedures, I occasionally encounter situations in which I want to abort the procedure, even if the situation didn't necessarily trigger an error.

Let's say, if I don't want John to be able to run this procedure, I'd do something like this:

IF @UserName = 'John'
BEGIN
    RAISERROR('John, get out', 16, 1);
    RETURN 1;
END

I don't really have a good reason for returning 1, it's mostly a remnant from shell scripting, and I'm open for better ways to do this.

Is there a better way of returning an error message and returning control to the caller for unwanted situations that aren't strictly speaking errors? I am not interested in reducing the amount of characters or lines of code. I just had a gut feeling saying "this is probably not the best way to solve this problem", and I was curious if there were smarter ways to do this.

One alternative could be by always using TRY/CATCH, since RAISERROR with a severity of 1-19 will hand control to the catch clause.

Example:

BEGIN TRY
    PRINT 'Before RAISERROR';
    RAISERROR('Time for errors', 16, 1);
    PRINT 'After RAISERROR'
END TRY
BEGIN CATCH
    DECLARE @Msg NVARCHAR(255) = ERROR_MESSAGE()
    PRINT 'Inside CATCH'
    RAISERROR(@Msg, 16, 1)
END CATCH

Output:

Before RAISERROR
Inside CATCH
Msg 50000, Level 16, State 1, Line 13
Time for errors

Con: Requires the presence of TRY/CATCH blocks.

Is there a more terse way of returning an error message and returning
  control to the caller for unwanted situations that aren't strictly
  speaking errors?

You can use THROW

  Raises an exception and transfers execution to a CATCH block of a
  TRY…CATCH construct ... If a TRY…CATCH construct is not available, the
  session is ended.

IF @UserName = 'John'
    THROW 50000, 'John, get out', 1;

The above is somewhat more terse as it is just a single statement and doesn't need the RETURN so you can also drop the BEGIN and END


How to properly troubleshoot Merge Replication False Conflicts We have SQL Server 2008 R2 Merge Replication setup.

Lately I started getting conflicts for some tables, and when I check the conflict viewer, I can see that the winner & the loser column values are identical!

I am sure that these data are being entered by only one subscriber.

I used sp_showrowreplicainfo to get more details about the conflicting row & I got 2 rows : one for the Publisher & one for the subscriber with both rows having the "version" as 1.

I also implemented Brandon's method: Auditing changes in Merge Replication, but it was showing only a normal insert!

Would you please tell me how to start troubleshooting this kind of conflict?

I found a solid answer to your question here on Database Administrators by Brandon Williams:
How to troubleshoot SQL Server Merge Replication Conflict?
Brandon said:

You need to inspect the Conflict Viewer and observe the conflict types and the conflicting row values. Something to note is that the Conflict Winner column will display the winning row values which are pulled from the base tables. If you think about it, these values are volatile and can change, so the values you see in the Conflict Winner column may not be the values they were when the conflict occurred, keep this in mind. The Conflict Loser column will display the losing row values which are pulled from the MSmerge_conflict_publication_article table and these values are static.
If you are unable to determine the root cause of the conflicts based on the Conflict Viewer, you may need to implement an auditing scheme. I have provided an example of how to do this in Auditing Changes in Merge Replication.
There is a small chance you are getting false conflicts but auditing should reveal whether or not that is the case.


Not able to attach database file without transaction log file Possible Duplicate:
  How do I attach a database in SQL Server?

I lost the transaction log file. I tried re-attaching the data file without the log file, to create a new log file, SQL Server is giving error:

This is the problem with detaching the database and deleting the transaction log file as a method of keeping the transaction log file short.  You can end up with corruption in the database that can't be resolved.

First try attaching the database using the sp_attach_db_single_file system stored procedure.  If you can't get it attached using that google for "hack attach sql server" and look at the first link from SQLSkills.com.  If that doesn't work then the database is lost and you won't be attaching it back to the SQL Server instance.

Next STOP DOING THIS.  Configure the databases for simple recovery so that the transaction log isn't kept any longer than needed.  While the transaction log appears to be just a log you can delete isn't actually a very important file that the SQL Server requires to keep the database up and running and in a consistent state.


Ensure integrity of other columns from foreign record I have 3 tables:

Race
 - id PK

Racer
 - id PK
 - rank

Winner
 - id PK
 - race_id FK(Race.id)
 - racer_id FK(Racer.id)
 - rank
 - prize
 UNIQUE CONSTRAINT (race_id, rank)

Racer.rank is a non-unique values, as some players can tie to a rank. I try to have records in Winner to have its rank match the Racer.rank that it references to. The unique constraint is to ensure that each race has only one 1st place.

The integrity between Winner.rank and Racer.rank is to enforcing if we later update Racer.rank, we will have to update the value in Winner as well, or remove that Winner row. How can I enforce that matching?

Other separate question:
- Is there a keyword that describe what I try to accomplish? I try to search with "referential integrity", "foreign constraint non-unique"... but none gets me to satified result.

Here is a rough sketch of the comments:

CREATE TABLE Race
( Race_id INT NOT NULL PRIMARY KEY  -- I prefer to use the same name through out the model
);

CREATE TABLE Racer
( Racer_id INT NOT NULL PRIMARY KEY  -- See above
, Rank INT NOT NULL
,   CONSTRAINT AK1_Racer UNIQUE (Rank, Racer_id) -- Super key
);

CREATE TABLE Result  -- Is not "Result" a better name than Winner?
-- I removed id
( Race_id INT NOT NULL
, Racer_id INT NOT NULL
, Rank INT NOT NULL  -- See below
, ...
,   CONSTRAINT PK_Result PRIMARY KEY (Race_id, Racer_id)
,   CONSTRAINT AK_Result UNIQUE (Race_id, Rank)
,   CONSTRAINT FK_Race FOREIGN KEY (Race_id)
                       REFERENCES Race (Race_id)
,   CONSTRAINT FK_Racer FOREIGN KEY (Rank, Racer_id)
                       REFERENCES Racer (Rank, Race_id)
);

EDIT:

I'm not sure why RANK is part of RACER. It seems like all kinds of possible ranks can be derived from WINNER (RESULT):

SELECT Racer_id
     , COUNT(1) as #races
     , AVG(Rank) as avg_rank
     , MIN(Rank) as lowest_rank
     , MAX(Rank) as highest_rank
     , ...
FROM Result -- Winner
GROUP BY Racer_id

If there is some other metric like score, time, etc that determines the rank per race, you may also consider storing that and derive rank from there. PostgreSQL and newer versions of MySQL have window functions like RANK() that is very useful for such calculations.


How to make iSQL/FreeTDS behave like a normal client? I have a third-party Sybase database, which my users would like to connect to and query from a Linux box, using iSQL. I have installed UnixODBC and FreeTDS. As it is third-party database however, I am not able to perform any GRANTs. The query fails with iSQL on Linux:

[42501][unixODBC][FreeTDS][SQL Server]ASA Error -121: Permission denied: you do not have permission to use the "CREATE PROCEDURE" statement

It appears that the actual SQL that is getting sent by iSQL is CREATE PROCEDURE <some temporary name> AS <the actual sql>. I have read the iSQL and FreeTDS manpages but there is no hint in there about how to disable this behavior.

This works OK in Python:

>>> import Sybase
>>> db = Sybase.connect('host:port', 'user', 'password', 'db')
>>> c = db.cursor()
>>> c.execute('select table_name from systable')
>>> len(c.fetchall())
570

So I believe I have installed the Sybase client libraries correctly. Before I recommend this solution instead, can I as a DBA do anything about fixing iSQL?

I have seen this before. According to the Sybase ASE Documentation:

  Adaptive Server Enterprise implements dynamic SQL using temporary stored procedures. A temporary stored procedure is created when a SQL statement is prepared, and destroyed when that prepared statement is deallocated...[a]s a consequence of this implementation, an application accessing Adaptive Server and using dynamic SQL is subject to the restrictions of Adaptive Server stored procedures.

However, I'm not sure if it's the unixODBC interface, or the isql tool, that is asking the ASE to treat these as stored procedures / dynamic sql.


postgres ERROR: target lists can have at most 1664 entries SQL state: 54000 In my query, I want to have more than 1700 columns but I got this error:

"ERROR: target lists can have at most 1664 entries SQL state: 54000"

I'm using postgres10 on Windows.

Has anyone come across this before? Any ideas for a solution?

Such column count is really huge. Without the schema/query details I cannot say, what's the reason, but I might guess there are many data columns not really required to be serialized there. Instead doing some concat magic I would suggest to merge such columns into JSON/JSONB objects which would be then easily accessible by the application. This is the classic NoSQL approach in SQL RDBMSes.


Multi-AZ failover - replica still read only after failover Setup:
I have a single RDS cluster.  This cluster has two instances, one in each zone.  Naturally, one "replication role" reader and one writer.

What happened:
I manually triggered a failover of the main instance from the RDS console "Instance actions" menu.  After doing so, the console eventually showed this change including the swap of reader and writer roles.  However, the replica instance remained read only so all writes were failing.  This replica instance's Parameter Group settings have read_only:TrueIfReplica.

Questions:
What other information can I provide to clarify my configuration or issue?
Any clues on why this replica instance stayed read only?
If this is the expected behavior of read_only:TrueIfReplica, where else should I look to fix my configuration?

Cheers!

One thing I need to answer this question. Which endpoint you are using in the application Instance or Cluster?

In Aurora there are two types of endpoints.
1. Instance endpoint
2. Cluster Endpoint

Instance Endpoint:

This endpoint will always points to that particular instance whether it is read or writer. It never care about the role, it'll point that particular instance.

Clusrer Endpoint:

Actually it has 2 endpoints. One is for Writer and another one os for reader. This is the endpoint we should use in the application. Because unlike Normal RDS, Aurora's failover and MultiAZ is different. So these cluster endpoints will point bases on the role.

Writer endpoint will always point to the writer role instance, after failover this endpoint will go to the new writer. Like the same for Read endpoint.

Answers for your three questions

What other information can I provide to clarify my configuration or issue?

  Check which endpoint you are using.

Any clues on why this replica instance stayed read only?

  If you are using instance endpoint, after the failover the application
  still refers the same instance for write. Thats why its failing.

If this is the expected behavior of read_only:TrueIfReplica, where else should I look to fix my configuration?

  Nothing, just again check which endpoint you are using.


Replication from MySQL 5.0 to MariaDB 10.2.8 - Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193 We are finally upgrading our MySQL 5.0.90 environment to MariaDB, and we would like to enable replication from MySQL 5.0 to MariaDB 10.2.8 and then cutover thus providing minimal downtime. I tried this originally with MariaDB 10.1.18 and it worked fine. MariaDB 10.2.8 became GA so we moved our upgrade process to that version. Now when I try to begin replication I get the following error in the MariaDB mysqld.err log:

Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193

When I execute 'show slave status' I see the following:

Slave_IO_Running: No
Slave_SQL_Running: Yes

Last_IO_Errno: 1595
Last_IO_Error: Relay log write failure: could not queue event from master

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

I've tried to do some research on this but have been unable to resolve the problem.

Here is the entire section of the log where replication is starting.

2017-11-20  8:26:43 140154253575936 [Note] Slave SQL thread initialized, starting replication in log 'mysql-bin.002735' at position 98, relay log './mysqld-relay-bin.000001' position: 4
2017-11-20  8:26:43 140154252973824 [Note] Slave I/O thread: connected to master 'repl@scvm50.dev.ut1.omniture.com:3306',replication started in log 'mysql-bin.002735' at position 98
2017-11-20  8:26:43 140154252973824 [Warning] Slave I/O: Notifying master by SET @master_binlog_checksum= @@global.binlog_checksum failed with error: Unknown system variable 'binlog_checksum', Internal MariaDB error code: 1193
2017-11-20  8:26:43 140154252973824 [ERROR] Slave I/O: Replication event checksum verification failed while reading from network, Internal MariaDB error code: 1743
2017-11-20  8:26:43 140154252973824 [ERROR] Slave I/O: Relay log write failure: could not queue event from master, Internal MariaDB error code: 1595
2017-11-20  8:26:43 140154252973824 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.002735', position 98

I found the solution/workaround. I need to set the variable slave_sql_verify_checksum=0 on the slave (MariaDB 10.2.8). I was trying this by dynamically setting the global variable on the slave but it wasn't working. Then I found bug #MDEV 8505 which indicates that resetting the variable dynamically does not work. Once I set it in the my.cnf and restarted the instance, everything started working as expected. Thankfully this is only a interim solution until we complete the upgrade!


Query for: view users and permissions (roles) from root security folder (MS SQL) On the Internet I found a lot of scripts how to display users and their permissions under a particular database (Security folder under table) - for example sp_helpuser.
Currently, however, I need to use a query to extract users and their permissions from the root of Security folder in Management Studio.

Can you please tell me how to extract users and permissions (roles) from this folder using the script?
Thank you all in advance for your advice.

It sounds like you are looking for a script similar to this one...

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [TEMPDB].[DBO].[DB_ROLES]([DBNAME] [SYSNAME] ,
       [USERNAME] [SYSNAME] ,
       [DB_OWNER] VARCHAR ,
       [DB_ACCESSADMIN] VARCHAR ,
       [DB_SECURITYADMIN] VARCHAR ,
       [DB_DDLADMIN] VARCHAR ,
       [DB_DATAREADER] VARCHAR ,
       [DB_DATAWRITER] VARCHAR ,
       [DB_DENYDATAREADER] VARCHAR ,
       [DB_DENYDATAWRITER] VARCHAR ,
       [DT_CREATE] [DATETIME] NOT NULL,
       [DT_UPDATE] [DATETIME] NOT NULL,
       [DT_REPORT] [DATETIME] NOT NULL CONSTRAINT [DF__DBROLES__CUR_DAT__3A179ED3]  DEFAULT (GETDATE()) ) ON [PRIMARY]
       GO
       INSERT INTO    [TEMPDB].[DBO].[DB_ROLES]
       EXEC SP_MSFOREACHDB 'SELECT''?'' AS DBNAME, USERNAME, MAX(CASE ROLENAME WHEN ''DB_OWNER''THEN ''YES'' ELSE ''NO'' END)
       AS DB_OWNER,        MAX(CASE ROLENAME WHEN ''DB_ACCESSADMIN ''   THEN ''YES'' ELSE ''NO'' END) AS DB_ACCESSADMIN ,
       MAX(CASE ROLENAME WHEN ''DB_SECURITYADMIN''  THEN ''YES'' ELSE ''NO'' END) AS DB_SECURITYADMIN,
       MAX(CASE ROLENAME WHEN ''DB_DDLADMIN''   THEN ''YES'' ELSE ''NO'' END) AS DB_DDLADMIN,
       MAX(CASE ROLENAME WHEN ''DB_DATAREADER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAREADER,
       MAX(CASE ROLENAME WHEN ''DB_DATAWRITER''        THEN ''YES'' ELSE ''NO'' END) AS DB_DATAWRITER,
       MAX(CASE ROLENAME WHEN ''DB_DENYDATAREADER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAREADER,
       MAX(CASE ROLENAME WHEN ''DB_DENYDATAWRITER'' THEN ''YES'' ELSE ''NO'' END) AS DB_DENYDATAWRITER,
       CREATEDATE, UPDATEDATE, GETDATE()
            FROM (SELECT B.NAME AS USERNAME, C.NAME AS ROLENAME, B.CREATEDATE, B.UPDATEDATE FROM [?].DBO.SYSMEMBERS A   JOIN [?].DBO.SYSUSERS  B  ON A.MEMBERUID = B.UID
            JOIN [?].DBO.SYSUSERS C ON A.GROUPUID = C.UID)S
            GROUP BY USERNAME, CREATEDATE, UPDATEDATE
            ORDER BY USERNAME'
            SELECT  SERVERPROPERTY('SERVERNAME') AS [SERVERNAME], B.NAME AS [LOGINNAME],
            CASE B.SYSADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SYSADMIN, CASE B.SECURITYADMIN WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SECURITYADMIN, CASE B.SETUPADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS SETUPADMIN, CASE B.PROCESSADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS PROCESSADMIN, CASE B.DISKADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS DISKADMIN, CASE B.DBCREATOR  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS DBCREATOR, CASE B.BULKADMIN  WHEN '1' THEN 'YES' ELSE 'NO'
            END AS BULKADMIN, B.DBNAME AS [DEFAULT_DBNAME],
            A.* INTO #LOGINS FROM [TEMPDB].[DBO].[DB_ROLES] A RIGHT JOIN MASTER..SYSLOGINS B ON A.USERNAME=B.NAME WHERE B.ISNTUSER=1
            SELECT * FROM #LOGINS ORDER BY [LOGINNAME]
            DROP TABLE  [TEMPDB].[DBO].[DB_ROLES]
            DROP TABLE  #LOGINS


PostgreSQL: Restoring 1 file from a directory format dump I've got a large'ish (> 700GB) PostgreSQL database.  I'm working on importing it into AWS RDS.  I realize there are modules that will cause problems, we're dealing with that separately, so for right now, I am just working on migrating the data to RDS.

I'm migrating to and from PostgreSQL 9.4.5 using pg_dump and pg_restore 9.4.5.

Several hours into the transfer (pg_dump to pg_restore with a directory format), I checked the log and realized there is one thing (possibly a table?) owned by the Postgres user I was unaware of.

I found the line in the toc.dat file that made the call and unpacked the .dat.gz file to find it only contains "\." (sans the quotes) in it.

So, my question is, short of stopping the transfer and starting over, is there a way to simply transfer that specific command and file once the import is completed?

Here are the relevant details.

The pg_dump command:

pg_dump -h localhost -p 5432 -d DBNAME -U USERNAME -F d -f /mnt/backup -j 7

The pg_restore command:

pg_restore --dbname=DBNAME --no-tablespaces --host=HOSTNAME --port=5432 --username=USERNAME --verbose -F d -j 6 /mnt/backup

The section from the toc.dat file.  I realize the directory format outputs in binary format, but I am hoping that the fact that this is identifiable means there is a way to go back and fix that one error.

^@^F^@^@^@public^A^A^@^@^@^@^H^@^@^@postgres^@^E^@^@^@false^@^C^@^@^@173^A^A^@^@^@^@    ^@^@^@10393.dat^@W<^@^@^@^A^@^@^@^@^A^@^@^@0^@^E^@^@^@26106^@^N^@^@^@search_name_66^@
^@^@^@TABLE DATA^@^C^@^@^@^@^@^@^@^@^@^@^@^@^@^@]^@^@^@COPY search_name_66 (place_id, search_rank, address_rank, name_vector, centroid) FROM stdin;

The actual error reported by pg_restore:

pg_restore: [archiver (db)] Error from TOC entry 10393; 0 16787 TABLE DATA spatial_ref_sys postgres

I found the line in the toc.dat file that made the call and unpacked
  the .dat.gz file to find it only contains "\." (sans the quotes) in
  it.

\. is not a command, it marks the end of data in a COPY stream.
If the corresponding data file inside the dump contains only that, then the source table was empty.

Also note that you can pg_restore --list to list the contents of a dump in directory or compressed format.

You may also restore the contents of an individual table into an SQL file rather than in the target database, then modify that file as convenient, and play it into the target. See the pg_restore manpage for more.


Performance Improvement went wrong in Production, worked fine in Test We have an application supported by vendor and there was a piece of code which was doing very heavy logical reads and time consuming inside a procedure, hence I proposed them to tweak the query a bit to have much lesser IO and time, it worked well in test in terms of data and performance however its failing in production and returning different data, we had to rollback the changes. Need your expert advise on this.

This was tested for data in Test environment for more than 3 months and we never had any data issue. Started failing sparingly in Production immediately after the deployment and was producing inconsistent data.

Existing Query:

SELECT @Ref= CAST(MAX(ISNULL(CAST(ref_clnt AS INT),0))+1 AS VARCHAR(10))
FROM table_name WITH(NOLOCK)
WHERE s_mode='value'

Proposed Query:

SELECT @Ref = ref_clnt+1 FROM table_name WITH(NOLOCK)
WHERE RefNo = (SELECT MAX(RefNo) FROM table_name WHERE s_mode = 'value')

The DDL of table is as below:

CREATE TABLE [dbo].[table_name](
    [RefNo] [dbo].[udt_RefNo] NOT NULL,
    [S_Mode] [varchar](10) NOT NULL,
    [ref_clnt] [varchar](50) NULL)
CONSTRAINT [PK_table_name] PRIMARY KEY CLUSTERED
(
    [RefNo] ASC
)

Providing only those columns from definition which is used in the query.

Udt_RefNo is a user defined datatype as:

CREATE TYPE [dbo].[udt_RefNo] FROM [char](16) NOT NULL
GO

Version of SQL Server: Microsoft SQL Server 2014 (SP3) Copyright (c) Microsoft Corporation  Enterprise Edition (64-bit).

Non-clustered index covering columns as shown below:

CREATE NONCLUSTERED INDEX [ncidx_table_name_1] ON [dbo].[table_name]
(
    [S_Mode] ASC,
    [S_Status] ASC
)
INCLUDE (   [ref_clnt])

Please find the query plans as requested:

Previous Query
Proposed Query

Comparison of Number of Reads after enabling statistics IO and time:

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

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 2732, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

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

(1 row affected)
Table 'table_name'. Scan count 1, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row affected)

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

Vendor has come back with suggestion as

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

Issue is - how to test them as all of them seems to work for most of the scenario however fails only in few cases. I am not very much aware of the application and its vendor supported one, so can't get much details like how does business logic work for underlying procedure.

Your rewrite does not have the same semantics as the original query, so different results are not surprising.

This is true without worrying about the NOLOCK hint, and even if it is (somehow) guaranteed that the highest ref_clnt value is associated with the row having the highest RefNo value. See this db<>fiddle example.

Precision is important when dealing with computers, so you need to think carefully about data types and edge cases. The maximum RefNo calculation will use string sorting semantics, so '999' sorts higher than '1000'. There are several other important differences between the queries. I'm not going to list all of them, but NULL handling is another example.

There are also numerous bugs in both versions of the code. The original will fail if any ref_clnt value of -1000000000 or lower is returned, because that will not fit in varchar(10). The sign makes the length 11.

The easiest way to safely improve the original version of the code is to add an index on a computed column:

ALTER TABLE dbo.table_name
ADD ref_cc AS
    ISNULL(CAST(ref_clnt AS integer), 0);

CREATE NONCLUSTERED INDEX i
ON dbo.table_name (S_Mode, ref_cc);

db<>fiddle demo

The execution plan can then seek directly to the highest ref_clnt row (sorted as integer) for the given S_Mode value:

The vendor's original SQL may still be of debatable quality, but at least it will run faster and produce the same results.

The vendor's new suggestion:

SELECT MAX(ISNULL(ref_clnt,0))+1 FROM table_name WITH(NOLOCK) WHERE S_Mode='value'

...is still problematic, at least in theory, because ISNULL uses the data type of the first parameter, so the integer literal 0 is implicitly cast to varchar(50).

  The value of check_expression is returned if it is not NULL; otherwise, replacement_value is returned after it is implicitly converted to the type of check_expression, if the types are different. replacement_value can be truncated if replacement_value is longer than check_expression.

The MAX still operates on a string, which may produce unexpected results. In any case the expression is still not seekable without a (different) computed column index.


Stored procedure not displaying data when null is passed into input parameter I recently started learning stored procedures in MySQL and created a procedure like this:
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_getAllDetails`(IN _student_no INT,
IN _class_id INT, IN _subject_name VARCHAR(50) )
BEGIN
IF _student_no IS NOT NULL AND _class_id IS NULL  AND _subject_name IS NULL THEN
        SELECT DISTINCT S.Student_No, S.Student_Name, C.Class_id, S.Class,
        C.Class_desc, M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
        INNER JOIN Marks M ON S.Student_No = M.Student_No
        INNER JOIN Class C ON M.Class_id = C.Class_id
        WHERE S.Student_No  = _student_no;

        ELSE IF _class_id IS NOT NULL AND _student_no IS NULL AND _subject_name IS NULL THEN
        SELECT DISTINCT S.Student_Name, C.Class_id, S.Class, C.Class_desc,
        M.Eng_marks, M.Maths_marks, M.Science_marks FROM Student S
        INNER JOIN Marks M ON S.Student_No = M.Student_No
        INNER JOIN Class C ON M.Class_id = C.Class_id
        WHERE  C.Class_id = _class_id;

        ELSE IF _subject_name IS NOT NULL AND _class_id IS NULL AND _student_no IS NULL THEN
        SELECT DISTINCT S.Student_Name, S2.Student_No, S2.Class_id, S1.Subject_Name,
        S1.Subject_id, S3.Eng_marks, S3.Maths_marks, S3.Science_marks
        FROM Subject1 S1
        INNER JOIN Subject2 S2 ON S1.Subject_id = S2.Subject_id
        INNER JOIN Subject3 S3  ON S3.Student_No = S2.Student_No
        INNER JOIN Student S ON S.Student_Name = S2.Student_Name
        WHERE S1.Subject_Name  = _subject_name;
    END IF;
    END IF;
    END IF;
END

It is displaying the desired output only when I call the procedure like this in the workbench:
call school.sp_getAllDetails(401, NULL, NULL);

However, if I enter the input parameters in the dialog box from the shortcut in the left-hand menu,
it is taking the same as:
call school.sp_getAllDetails(401, NULL, 'NULL');

This is not displaying any data as it is taking NULL as a string. Can I know where am I going wrong? It will be a great help.
Many Thanks
Edit:
Leaving the question here because anyone who's looking for the same may get some help here. Also, if anyone has a different answer can add to this.

Well, I figured out the problem by myself. I removed _subject_name IS NULL in both the first and the second IF condition and it worked as expected.


Query optimization with conditional group by and where I've a DB with events, workshops in each event (1:N), participants in each workshop/event(N:M), role of participant in each workshop, and a table (letture) with access control for each participant in workshop.

We create a unique qrcode for participant/workshop. With this qrcode we can identify who went to which workshop and we can calculate for how many hours he remained.

But not all workshops need this in/out (entrata=1/2) value, for some are sufficient check who are present (entrata=0).

To print the final certificate for every participant in every event/workshop I try to populate the table attestati_dati with this query :

TRUNCATE attestati_dati;
INSERT INTO attestati_dati
(id_participant,id_event,id_workshop,id_role,id_attestato,ore_totali)
SELECT * FROM
(
   SELECT
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
     SEC_TO_TIME(SUM(TIMESTAMPDIFF(SECOND,
             i1.datalettura,
             IFNULL(i2.datalettura, i1.datalettura)))) AS ore_totali
FROM
  letture AS i1
     LEFT JOIN
 letture AS i2 ON (i1.qrcode = i2.qrcode
     AND i2.datalettura > i1.datalettura
    AND i1.id_event = i2.id_event
    AND i1.id_sala = i2.id_sala
    AND i2.entrata = 2)
    LEFT JOIN
letture AS i3 ON (i3.qrcode = i2.qrcode
    AND i3.datalettura > i1.datalettura
    AND i1.id_event = i3.id_event
    AND i1.id_sala = i3.id_sala
    AND i3.entrata = 2
    AND i3.datalettura < i2.datalettura)
    CROSS JOIN
event_participants ep ON ep.qrcode = i1.qrcode
    AND ep.id_event = i1.id_event
    LEFT JOIN
participants p ON p.id_participant = ep.id_participant
    LEFT JOIN
roles r ON ep.id_role = r.id_role
    LEFT JOIN
events e ON i1.id_event = e.id_event
    LEFT JOIN
workshop_participant wp ON wp.id_participant = p.id_participant
    AND wp.id_workshop = i1.id_sala
WHERE
 i1.entrata = 1
     AND i3.datalettura IS NULL
     AND i1.id_Event = 52
GROUP BY i1.qrcode
UNION SELECT
     p.id_participant AS id_participant,
     i1.id_event AS id_event,
     i1.id_sala AS id_workshop,
     r.id_role AS id_role,
     1 AS id_attestato,
    SEC_TO_TIME(0) AS ore_totali
FROM
 letture AS i1
     CROSS JOIN
 event_participants ep ON ep.qrcode = i1.qrcode
     AND ep.id_event = i1.id_event
     LEFT JOIN
 participants p ON p.id_participant = ep.id_participant
     LEFT JOIN
 roles r ON ep.id_role = r.id_role
     LEFT JOIN
 events e ON i1.id_event = e.id_event
     LEFT JOIN
 workshop_participant wp ON wp.id_participant = p.id_participant
     AND wp.id_workshop = i1.id_sala
WHERE
    i1.entrata = 0 AND i1.id_Event = 52
GROUP BY i1.qrcode , DAYOFMONTH(i1.datalettura)
) A;

I'd like to improve this query because this is the best I can do to solve this problem, but I think that is optimizable, specially in the group by/where/joins part.

Show Create table:

  CREATE TABLE `attestati_dati` (
 `id_attestati_participants` int(11) NOT NULL AUTO_INCREMENT,
 `id_participant` int(11) DEFAULT NULL,
 `id_event` int(11) DEFAULT NULL,
 `id_workshop` int(11) DEFAULT NULL,
 `id_role` int(11) DEFAULT NULL,
 `id_attestato` int(11) DEFAULT NULL,
 `ore_totali` time DEFAULT NULL,
  PRIMARY KEY (`id_attestati_participants`)
 ) ENGINE=MyISAM AUTO_INCREMENT=25 DEFAULT CHARSET=latin1;

e and wp are not used, so remove the `LEFT JOINs to them.

Some composite indexes needed:

letture:  INDEX(id_event, entrata, qrcode, id_sala, datalectura)
ep:  INDEX(qrcode, id_event)  -- in either order

My suggested index for letture helps with all 3 uses of that table, and in different ways.  The order of the columns is critical, except that the first two can be swapped.

If AND  i2.datalettura > i1.datalettura is part of doing a "groupwise max", then it is especially inefficient.  This discusses much faster ways.

AND  i3.datalettura IS NULL and its LEFT JOIN can probably be replaced by NOT EXISTS ( SELECT 1 ... )

The CROSS JOIN is technically an INNER JOIN, but since MySQL ignores CROSS and INNER, either of those works the same.  Ditto for plain JOIN.

The index recommendations come from looking at the WHERE clauses, and noting that nothing can be done about the GROUP BY.  More discussion here.

If you need to discuss this further, please provide SHOW CREATE TABLE and EXPLAIN SELECT ....


Compare and auto fix conflicts between two databases T-sql Consider we have a database for an windows application it's for 2 years ago, now we have changed many things in database so there is differences and we don't know what are the differences.

I don't want to use compare tools like Red Gate compare tool.

What's the best way to see new columns are in new database :

Query result like :

New Db Name | Schema | Table Name | Column Name | Default Value | IsNullable | DataType
NewDb       | dbo    | myTable    | newColumn   | ''            | No         | nvarchar(30)

Thank you

Edit :
I need a query after this to fix the conflicts so both databases be the same :

Add NewDb columns to OldDb,delete OldDb columns that are not in NewDb and update all columns to NewDb version ( if two columns datatype or something is different in two databases. set the NewDb's to OldDb too.)

Columns in old database but not in new , or default value, nullable and data type changed:

SELECT t1.*,
       t2.*
FROM   [OldDb].[INFORMATION_SCHEMA].[COLUMNS] t1
       LEFT JOIN [NewDb].[INFORMATION_SCHEMA].[COLUMNS] t2
         ON t1.COLUMN_NAME = t2.COLUMN_NAME
WHERE  t2.COLUMN_NAME IS NULL
        OR ( t1.TABLE_NAME = t2.TABLE_NAME
             AND t1.TABLE_SCHEMA = t2.TABLE_SCHEMA
             AND t1.COLUMN_NAME = t2.COLUMN_NAME
             AND ( t1.DATA_TYPE != t2.DATA_TYPE
                    OR t1.IS_NULLABLE != t2.IS_NULLABLE
                    OR t1.CHARACTER_MAXIMUM_LENGTH != t2.CHARACTER_MAXIMUM_LENGTH
                    OR t1.COLUMN_DEFAULT != t2.COLUMN_DEFAULT ) )
ORDER  BY t1.COLUMN_NAME

Columns in new database but not in old :

You can change [OldDb] place with [NewDb] in query

I wrote this question and answer because I've needed this and I've spent so much of time on this.

So I hope everyone can find and use this easily :).
I'm looking forward for opinions for make this better.


TempDB Transaction log not releasing space Every time I complete a transaction in tempdb, the temp log grows, however the log doesn't seem to release the space once the transaction has completed.The log usage percent is 43.4%  but seems to increase in around 4% increments.

When I look for open transactions (sys.dm_exec_sessions), there are none. Yet the log_reuse_wait_desc in sys.databases shows 'ACTIVE_TRANSACTION'. When I query the sys.dm_tran_active_transactions DMV, it shows only work table.

I'm unsure of how I can release this space from the tempdb transaction log.

I've just run a manual checkpoint and that seems to free up the space so I'm not sure as to why the space isn't being freed up on it's own

The log_reuse_wait_desc is only updated when SQL Server attempts to clear the log.  In other words, it's not necessarily a "live" view of whether the log can be cleared or not.  Paul Randal explains that in his blog post "Why is log_reuse_wait_desc saying LOG_BACKUP after doing a log backup?".

In the case of tempdb, which uses the SIMPLE recovery model, SQL Server attempts to clear the log whenever a CHECKPOINT is issued.  This is why doing a manual CHECKPOINT frees up the space, as you mentioned.

SQL Server is a lot less aggressive about doing automatic CHECKPOINTs on tempdb, both because it uses the SIMPLE recovery model, and because it is by definition "temporary" information.  So there's no need to constantly persist it to disk.  This is a performance optimization specific to tempdb.

Per another blog post by Paul Randal, the details are as follows:

automatic CHECKPOINT operations on tempdb are only done when the log usage reaches 70%
dirty pages are not written to disk on automatic CHECKPOINT (its primary purpose it to clear the log)

The "work table" rows in dm_tran_active_transactions are normal behavior, and shouldn't prevent the log from being cleared.  You can read more about that in this Q&A:

SQL Server sys.dm_tran_active_transactions long running transaction worktable


Recursive Updates on large table I am desperately seeking a solution for a problem, which i will try to fully describe hoping that a somebody can come up with a clever solution.

The metadata :

At the moment i have the following set of tables (minified to remove chatter) :

Table "Recipe" has a Foreign Key to table "Item" on Recipe.OutputITemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Item" on RecipeIngredient.ItemId = Item.Id

Table "RecipeIngredient" has a Foreign Key to table "Recipe" on RecipeIngredient.RecipeId = Recipe.Id

A recipe contains a number of ingredients that represent items. The items representing these ingredients could again be produced by a recipe. This creates a structure of recipe's and their ingredients.

At the moment the list of recipes is about 8000, the list of ingredients about 25000. I have written a recursive CTE that returns the tree structure resulting in 144000 rows. A fair amount of recipe's are reused.

The problem : Calculating the total cost.

The cost of a recipe has to be calculated based on the total cost (SUM SellValue) of all the descendant recipes or, when no recipe available for the current item, the SellValue of that particular item.
On stale data, traversing through a CTE with a cursor, these values could easily be updated, though this is not an option IMHO. The reason for this is that the SellValue of an item is updated VERY frequently, read constantly. An update of a certain item would cause numerous recipe's to have their pricing recalculated (up to 100 recipe's).

I have tried using recursive triggers, even though i hate em, on Item and Recipe updating the recipe based on its descendants, to no good effect.

Apparently my knowledge of SQL, plus searching Google for quite a while, didn't provide a feasible solution.

Please help, i'm pulling out the few hairs i have left... ;)

UPDATE 1:

The use of nested/recursive triggers on the recipe table led to a common problem, deadlocks. For now i'll investigate the possibility of using a recursive CTE with SUMS on the respective values.

Your schema diagram is good. You should not put any total cost column either to Recipe or RecipeIngedient as it is not compliant to normalization. And so there is no need for triggers.
The total cost sum should be calculated on the fly in a query that you use to list recipes.


Return the most recent event in a series of same title events I need to be able to return the most recent event of a occurrence that may or may not have occurred more than once. For instance:

Each time a product is moved from position A to Position B, it is recorded (From_Function) / (To_Function) along with its operator and timestamp.
On rare occasions, re-work is required, and that unit will show the same from / To instance with different timestamps.

I am currently calculating age in each position with a datediff,  how do I return only the most recent in this scenario?

I got it down to if there is no group by clause it will return the date needed with a max(route_date_time); however, the total qry is many lines long with aggregate functions so a group by is necessary. How can I return only the one line that is needed with the most recent date?

Assuming an item moves between some type of state or location (From_Function, To_Function) and the timestamp of the move is in the route_date_time column, you could put your query in a common table expression, along with a ROW_NUMBER() function that calculates a sequence number, like so:

WITH cte AS (
    SELECT item_no,
           ToFunction AS current_function,
           route_date_time,
           ROW_NUMBER() OVER (
               PARTITION BY item_no
               ORDER BY route_date_time DESC) AS _rownum
    FROM some_complex_query
)

SELECT *
FROM cte
WHERE _rownum=1;

Because we've partitioned the ROW_NUMBER() function on item_no and ordered it by route_date_time descending, _rownum=1 will always return the most recent row for each item_no, which is what we're isolating using the WHERE clause at the end of the query.

If you need the time that each item has spent in a state, you could use LAG() (requires SQL Server 2012 or newer). Within the CTE, use the following expression:

    DATEDIFF(second,
             LAG(route_date_time, 1) OVER (
                 PARTITION BY item_no
                 ORDER BY route_date_time),
             route_date_time
            ) AS time_spent_in_function

This will return the difference in number of seconds between the previous route_date_time (the LAG() function) and the current route_date_time. Bear in mind the PARTITION BY clause, which defines how you partition your records.


How to store day and time in postgres? I have an events table in which various events could be happening in time slots each day. What I mean is each event has many time slots each day of the week. For example, event A can be have slots such as 1-2 pm, 5-6 pm on Mondays, 2-4 pm, 5-7 pm on Tuesdays etc.

What's a good way to structure this in postgres taking into account that queries could be on events by timings as well. For example, find all events on Monday which occur between 5 - 7 pm.

I was thinking of having a second table with event id as the first column, day of the week in the second column and then it's timing in the third column. So events having multiple timings in a day would be stored in individual rows for each timing on each day.

Alternatively, I was also thinking of a table with first two columns same as above, and a third column of array of timings, so events having multiple timings in a day would be stored in just one row for each day.

Which one is better or is there an even better way? If the former is better, in what scenario is use of arrays in databases useful? IMO, anything that can be done with arrays can be done with splitting tables or storing in different rows which are rather helpful when querying.

Also, how would I go about storing time such as 5-6 pm etc in the databse? I've a fair idea about storing the day of the week by using 7 bit boolean value. So Sunday would be 0000001, Sunday and Monday would be 0000011 etc, but how would I store the time ranges?

Thanks!

I would have your event table and use the event id as a foreign key to another table we might call event_times. This new table might look like this:

id  |  eventID  |  dayOfWeek  | startTime  |  endTime
--------------------------------------------------------
1      1           1            13:00:00      14:00:00

The day of week could be a smallint where 0 represents Sunday and 6 represents Saturday. Start time and end time would be the the time data type, as you only need to store the time of day (in 24-hour time).

To produce your output, your sample query would be:

SELECT eventID FROM event_times WHERE dayOfWeek = '1' AND startTime = '17:00:00' AND endTime = '19:00:00';

To get more information about the event, you would just need to join the eventID on your event table.


How to resolve foreign key references across different databases (iSeries and SQL Server) I've got a system that has a DB2 iSeries database. We are rewriting a portion of the system and implementing it using SQL Server and Entity Framework.

Originally, I was lead to believe that there wasn't much of a tie-in between the tables we are moving to SQL Server and the DB2 ones. However, I'm finding a few spots where there are foreign-key references across them.

Are there any standard ways of handling this kind of scenario, where we need to maintain foreign-key references across databases? I can think of a few options:

Duplicate some of the data from SQL Server to DB2 on iSeries, enough to satisfy the foreign key constraints. I hate this idea not only because of the duplication of data but also because if we only duplicated the necessary data it would only exist to satisfy a constraint, but we couldn't do anything useful on it like JOIN to it.
Remove the foreign key constraints from the iSeries and completely unlink the two databases. This has obvious issues, but thought I would throw it out there in case there are any benefits that I'm not seeing.
Use some kind of database link to keep the two in sync. I know not how this would work, how it would perform, or even if it is possible to have foreign key constraints across a link. Perhaps this would need to be done with a trigger as I imagine a regular foreign key reference would not work in this case?

Does anybody see any other options, and what would be the best approach?

I don't believe there is anything on the DB2 side that would ever need to read/write anything to the SQL Server side, or vice versa, so we could keep the databases accesses from the application separate. I believe it is just to keep the foreign key references.

If the foreign key constraints don't point to any other table inside of originating database (ie, if SQL Server doesn't point to SQL Server, etc.), then I would drop those foreign keys. They aren't useful.

If they do point to other tables, then perhaps you do have to copy what data is needed just to satisfy the constraint. (Ugh). Either that or re-consider that relationship and possibly drop the foreign key constraint again.

Another thing to look into (at least on DB2 for i) would be informational constraints. I know DB2 LUW had them. They were non-enforced constraints. They helped the optimizer when choosing query plans, but were not enforced as an actual constraint (you could violate them). Of course, a side effect of those is that sometimes you could miss data you wanted due to the informational constraint.

And not sure if SQL Server has something similar or not. I am still learning that platform.


MariaDB 10.2 InnoDB internal data dictionary problem I've recently upgraded my MariaDB installation on a Windows Server 2012 to a stable release 10.2.6.
However I'm having a problem with one particular table.

Each time MariaDB starts, it logs the following:

  2017-05-23  1:28:53 4180 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1

  2017-05-23  1:28:53 4180 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

I've tried REPAIR on this table, but it says the table does not exist in the engine.

If I try to drop this table, I get

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: SYS_TABLES::TYPE=1697 page_compression:0 page_compression_level:3 atomic_blobs:1
  2017-05-23  1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename in InnoDB data dictionary contains invalid flags. SYS_TABLES.TYPE=1697 SYS_TABLES.N_COLS=2147483655

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: incorrect flags in SYS_TABLES

  2017-05-23  1:58:07 5076 [ERROR] InnoDB: Table dbname.tablename does not exist in the InnoDB internal data dictionary though MariaDB is trying to drop it. Have you copied the .frm file of the table to the MariaDB database directory from another database? Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

Afterwards, MariaDB thinks this table does not exist, though InnoDB doesn't think so. When I try to rename a newly created table to the same name, I get:

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: Possible reasons:

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: (1) Table rename would cause two FOREIGN KEY constraints to have the same internal name in case-insensitive comparison.

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: (2) Table dbname.tablename exists in the InnoDB internal data dictionary though MySQL is trying to rename table dbname1.tablename to it. Have you deleted the .frm file and not used DROP TABLE?

  2017-05-23  2:03:30 5076 [Note] InnoDB: Please refer to http://dev.mysql.com/doc/refman/5.7/en/innodb-troubleshooting.html for how to resolve the issue.

  2017-05-23  2:03:30 5076 [ERROR] InnoDB: If table dbname.tablename is a temporary table #sql..., then it can be that there are still queries running on the table, and it will be dropped automatically when the queries end. You can drop the orphaned table inside InnoDB by creating an InnoDB table with the same name in another database and copying the .frm file to the current database. Then MySQL thinks the table exists, and DROP TABLE will succeed.

One of the solutions on the Internet is to recreate the whole database, which is not an option for me due to a long downtime.

So far I've ended up creating a table with the same structure using Aria engine. But how can I get it working with InnoDB?

Any tools out there to work with that InnoDB data dictionary?

Additionally, trying to SELECT from information_schema.INNODB_SYS_TABLES crashes the whole server.

Unfortunately, MariaDB 10.2 changed the data format in a way which breaks compatibility with 10.1 - issue MDEV-12873 - so although the upgrade will "succeed", you no longer have any access to your data.

Apparently 10.2.7 will fix this bug; in the mean time, it looks as if you just have to revert to your most recent backup. (To add insult to injury, the failed "upgrade" also prevents direct reversion, so you need to dig out a full backup and restore from that.) It's pretty troubling for me that this managed to slip in to a "stable release".


What is retrieved from disk during a query? Fairly simple question, probably answered somewhere, but I can't seem to form the right search question for Google...

Do the number of columns in a particular table affect the performance of a query, when querying on a subset of that table?

For example, if table Foo has 20 columns, but my query only selects 5 of those columns, does having 20 (versus, say, 10) columns affect query performance? Assume for simplicity that anything in the WHERE clause is included in those 5 columns.

I'm concerned about the usage of Postgres' buffer cache in addition to the operating system's disk cache. I have a very lose understanding of Postgres' physical storage design. Tables are stored across several pages (defaulting to 8k in size per page), but I don't quite understand how tuples are arranged from there. Is P.G. smart enough to only fetch from disk the data that comprises those 5 columns?

The physical storage for rows is described in the docs in Database Page Layout. The column contents for the same row are all stored in the same disk page, with the notable exception of TOAST'ed contents (too large to fit in a page). Contents are extracted sequentially within each row, as explained:

  To read the data you need to examine each attribute in turn. First
  check whether the field is NULL according to the null bitmap. If it
  is, go to the next. Then make sure you have the right alignment. If
  the field is a fixed width field, then all the bytes are simply
  placed.

In the simplest case (no TOAST'ed columns), postgres will fetch the entire row even if few columns are needed. So in this case, the answer is yes, having more columns may have a clear adverse impact on waster buffer cache, particularly if the column contents are large while still under the TOAST threshold.

Now the TOAST case: when an individual field exceeds ~2kB , the engine stores the field contents into a separate physical table. It also comes into play when the entire row doesn't fit into a page (8kB by default): some of the fields are moved to TOAST storage. Doc says:

  If it's a variable length field (attlen = -1) then it's a bit
  more complicated. All variable-length data types share the common
  header structure struct varlena, which includes the total length of
  the stored value and some flag bits. Depending on the flags, the data
  can be either inline or in a TOAST table; it might be compressed, too

TOAST'ed contents are not fetched when they're not explicitly needed, so their effect on the total number of pages to fetch is small (a few bytes per column). This explains the results in @dezso's answer.

As for writes, each row with all its columns is entirely rewritten on each UPDATE, no matter what columns are changed. So having more columns is obviously more costly for writes.


Encrypt data with DES_CBC_PKCS5 in DB2 I am using encryption algo DES/CBC/PKCS5Padding. How do I encrypt data using this in DB2?

In Pl/SQL I used DBMS_CRYPTO package with DES_CBC_PKCS5 from DBMS_CRYPTO Block Cipher Suites - https://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_crypto.htm

Version is DB2 v10.5.0.5 and it will run on a Linux box. Distro may be different. Query - SELECT service_level, fixpack_num FROM TABLE (sysproc.env_get_inst_info());

DB2 does not implement the DBMS_CRYPTO module in its Oracle compatibility code.  DB2 does still offer the ENCRYPT() and DECRYPT() UDFs, but I would recommend against these as they use a weak encryption algorithm and require the use of passwords in SQL.

So, you would have to implement your own solution (via UDFs) for application-level encryption like this.

It might make more sense to implement this in your application code rather than in the database to avoid having to send encryption keys across the wire to the DBMS.  This would certainly be a more platform-independent solution.


What is the best way to create a database diagram for SQL Azure without having to install SQL Server? I have a database on SQL Azure and want to create a database diagram for that, but don't want to have to install SQL server and copy the database etc.  Is there any way that I can do it that anyone knows of?

UPDATE: The database diagrams were added back into SSMS 18.1(link)

UPDATE: the database diagram feature

  Deprecated and removed features in 18.0 (GA)
  ...
  Database Diagrams
  https://docs.microsoft.com/en-us/sql/ssms/release-notes-ssms

The feature was also removed from Visual Studio 2019 SSDT.

Original Answer

The latest version of SSMS (as of v16) supports creating database diagrams for Azure SQL databases. You can download it here:

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


MySQL Master-Slave replication foreign key constraint error This is the cluster setup:

------       ------
| m1 |  ---  | m2 |
------       ------
  ||           ||
------       ------
| s1 |       | s2 |
------       ------

m1 and m2 run mariadb-server-10.1 version 10.1.18+maria-1~jessie.

m1 and s2 run mariadb-server-10.1 version 10.1.18+maria-1~wheezy.

Nodes m1 and m2 are in a Galera Cluster, and replication between these nodes works fine. There is also an arbitrator node running on a separate server.

s1 is a slave node to m1, and s2 is a slave node to m2. The replication to slave nodes is using classic MySQL replication.

Now, the issue here is that the slave nodes needed to be started from fresh state. This was the process to start the replication:

service mysql stop on slave node.
Empty /var/lib/mysql on slave node.
Run innobackupex -u user -p password on master node.
Run innobackupex -u user -p password --apply-log /path/to/backup on the master node.
Copy database backup files to slave server.
Run innobackupex --copy-back /path/to/backup on slave server.
Run chown -r mysql.mysql /var/lib/mysql/* on slave server to fix permissions.
service mysql start on slave server.

After this, I started mysql -u root -p password, and issued this command:

STOP SLAVE;
CHANGE MASTER TO MASTER_HOST='m1', MASTER_USER='repl', MASTER_PASSWORD='password', MASTER_LOG_FILE='mariadb-bin.NNNNNN', MASTER_LOG_POS=nnn;
START SLAVE;

Values for NNNNNN and nnn were retrieved from xtrabackup_binlog_info file from the backup files.

Now, this process worked perfectly between m1 and s1. However, with m2 and s2, it generates an error message (I split the error message to multiple lines for better readability):

Could not execute Write_rows_v1 event on table db.table;
Cannot add or update a child row: a foreign key constraint fails
(`db`.`table`, CONSTRAINT `FK3EBA76781F6AACF1` FOREIGN KEY (`job`)
REFERENCES `table2` (`id`)), Error_code: 1452; handler error
HA_ERR_NO_REFERENCED_ROW; the event's master log mariadb-bin.000710, end_log_pos 1523526

I have tried to repeat the process again a couple of times without any success.

I have also issued RESET MASTER on m2 to reset the replication log file in case it had some corruption.

I am running out of ideas what is happening here and how I can restart replication correctly. Any ideas?

I see that you have used a multi-master configuration for m1 and m2 and a master-slave config for m1-s1 and m2-s2. Currently, the multi-master configuration does not support write actions on the tables with foreign keys. Thus, when there is a write action on either m1 or m2, the error occurs.

test to debug: set up a master-slave config for m1-s1 and m2-s2 without connecting m1 and m2. If both of them work as expected, then you can confirm that the foreign keys issue is due to the multi-master m1-m2 config.


Restore Error: Could not upgrade the metadata I am trying to restore a database from a Prod environment (11.0.6020.0) to a lower environment server (11.0.3128.0).  I realize the lower has an older SP, we are working on correcting that now.  The restore should still work though, it has for months, but I'm getting the following error message.  We just recently enabled this database for CDC, I'm not specifying keep_cdc in the restore.  Anybody seen this error before and know how to resolve it?

  Msg 22841, Level 16, State 1, Procedure sp_cdc_vupgrade, Line 320
  [Batch Start Line 0] Could not upgrade the metadata for database
  'DBName' that is enabled for Change Data Capture. The failure occurred
  when executing the action 'alter cdc.change_tables index
  change_tables_unique_idx with (drop_existing = on)'. The error
  returned was 4922: 'line 102, state 9, ALTER TABLE ALTER COLUMN
  column_id failed because one or more objects access this column.'. Use
  the action and error to determine the cause of the failure and
  resubmit the request.

RESTORE DATABASE [DBName]
FROM  DISK = @FileToRestore WITH
MOVE  'DBName' to 'W:\Data\DBName\DBName.mdf',
MOVE  'AmexDB_log' to 'W:\Logs\DBName\DBName_Log.ldf',
NOUNLOAD, REPLACE , FILE = 1, STATS = 10
GO

We were able to resolve the issue by upgrading the lower environment to SP3.  Once that was done the restore completed without any errors.


Recommendations about deleting large set of rows in MSSQL I need to delete about 75+ million rows from a table everyday that contains around 3.5 billions of record.
Database recovery mode is simple, I have writen a code that deletes 15.000 rows in a while condition until all 75M records is deleted. (i use batch delete due to log file grow) However, with current deletion speed it looks like it will take at least 5 days, which means that amount of data required to be deleted is multiply faster than my deletion speed.
Basically what i'm trying to do is summarizing (in another table) and deleting data older than 2 months. There is no update operation in that table, only insert and delete.
I have an enterprise edition of MSSQL 2017
Any suggestions will be welcome.
Table definition: (for giving you clear view)
Table has 3 indexes, clustered bigint Id index, non-clustered (Datetime + bit + int) index and non clustered columnstore index that contains every column(this required by ui application), Straight forward way looks like creating partition scheme over CreateDate column that creates partitions for every day. Then drop that partition when its data marked to be deleted. However with these approach i have encountered major performance impact, almost entire system crashed.

I've had to do this a couple of times and this is my preferred method for transforming a very large table into a partitioned one with minimal downtime. It does however, rely on the fact that you are fine with waiting a few days/weeks for it to complete the process (don't panic - you will understand why it takes this long later on):

Create a new PARTITION FUNCTION if you do not have an existing one you can use.
Create a new PARTITION SCHEME if you do not have an existing one you can use.
Create a new PARTITIONED TABLE that will contain all new data.
Add a CHECK CONSTRAINT to the new table to ensure that no data with a date older than X is added.
Add a CHECK CONSTRAINT to the old table to ensure that no data with a date newer than X is added.
RENAME THE EXISTING TABLE to something to identify that it is old/archived data.
CREATE A PARTITIONED VIEW that combines the old and new tables with the correct name.
Allow the existing process of adding & removing data to continue.

All newly inserted data will be added to your newly partitioned table while the deletes will act upon the older table. Over time as the data is cleared out of the older table the deletes will become faster and have less of an impact on your system. Based on your estimate of 75 million rows per day, it will take about 6 weeks to get to the point where all new actions are performed against the partitioned table.
During this time you can develop a PARTITION SWITCHING strategy to use when deleting data from the newly partitioned table.
Once all of the data has been removed from the old table, you can DROP THE VIEW, DROP THE OLD TABLE and then rename your partitioned table correctly.
I have found this approach to be the least disruptive to a production environment because you never actually go back and partition the existing data - if you are happy and are permitted to wait the required amount of time. If it is a matter of urgency, then other approaches will require downtime while you partition the existing table.


How do I group results in batches of consecutive rows with the same 'module' I'm looking for a seemingly very simple SELECT.

I have a logging table LOGGING of which three columns are of interest to me: MODULE (text), PDATE (date), PTEXT (text).

Actually, I care only about the first two columns, i.e. which module has been called at all (which is the name of a procedure in a PL/SQL package). The date I only need for ordering (ASC) the result, and one could also use the ID column for the same purpose, which is an auto-incremented integer value (Oracle: sequence.nextval).

Since the logging produces waaaaayyyyy too much data, tens to hundreds of PTEXT entries for a single MODULE, I would like to condense the MODULE rows, which seems to call for a simple GROUP BY.

Maybe this illustrates the issue:

I start with the inner query below, which gives me the correct sequence of "module" calls, but does not aggregate them (example 1 above). So my idea was okay, I get the correct result in the correct order - so if I now select just the one column I'm interested in and use "group by" on this ordered(!) data I should get what I want - but adding the "group by" below gives me a COMPLETELY different order. Apparently my knowledge about what "group by" does is inadequate.

SELECT module FROM
(
  SELECT module, pdate FROM logging
  ORDER BY pdate asc
)
--GROUP BY module

Result without "GROUP BY" (first 12 lines):

set_option_value
set_option_value
set_option_value
set_option_value
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...

Result incl. "GROUP BY":

get_fun_AppOptionsSingleVal
get_option_value
get_parameter
get_usecase_customer
get_kungesId_FY_cusper
fill_missing_products
update_chart_budget_uc1
get_Berein
set_hierarchy
get_element_num_value
icas_dyn_create_chart_table
get_xmlTemplate
...

As one can see the "GROUP BY" produces a completely different order.

What I WOULD HAVE WANTED:

set_option_value
get_rights_usecase
get_fun_AppOptionsSingleVal
get_rights_usecase
...

Basically, what I want can be achieved very simply by taking the text output of

  SELECT module FROM logging
  ORDER BY pdate asc

and pipe it through "uniq" on the Unix command line. Now I'm just curious why this isn't as simple in SQL.

The question is which of the pdates for a module do you want to use for ordering purposes?

If you are happy to use the largest, you could use:

select module from logging order by max(pdate);

--- EDIT

I think I get it now - you probably want something like this:

select module
from( select module,
             sum(module_step) over (order by pdate rows unbounded preceding)
                 as batch_number,
             pdate
      from( select module,
                   case lag(module) over (order by pdate) when module then 0
                        else 1 end as module_step,
                   pdate from logging) )
group by module, batch_number
order by batch_number;


Why does PostgreSQL allow NULLs in domains that prohibit NULL? PostgreSQL allows NULLs in domains marked as NOT NULL. Why is this, the docs say this about it,

It is possible for a column that is nominally of the domain type to read as null despite there being such a constraint. For example, this can happen in an outer-join query, if the domain column is on the nullable side of the outer join.

Can this be better explained?

Well, for starters look at this,
CREATE DOMAIN mydomain AS int;

CREATE TABLE foo(bar) AS SELECT 42::mydomain;

SELECT f1.bar AS f1, f2.bar AS f2, pg_typeof(f1.bar), pg_typeof(f2.bar)
FROM foo AS f1
LEFT JOIN foo AS f2
  ON false;

That currently returns,
 f1 | f2 | pg_typeof | pg_typeof
----+----+-----------+-----------
 42 |    | mydomain  | mydomain

But what should it return? You're joining two tables and the only type in that table is mydomain which allows NULLs. Logically both columns returned should be of type mydomain. Now if you go back and run this,
CREATE DOMAIN mydomain AS int NOT NULL;

Should that return something different given the above commands? The answer is no -- for the same reason. The resultset is composed of two columns sourced from tables. If this is to return the same types as the source tables, like the above query, then it must return, however counter intuitively, a resultset with NULL. Note this isn't about INSERT it's just showing you that no matter what the type SQL can make it return NULL. That's how you get NULLs into a DOMAIN when the domain itself does not support NULL.
Possible Solutions
Four-valued Logic
How can you solve this the right way? You could graduate from Three-valued_logic (3vl) (which is true/false/null), to 4vl (which would be something like true/false/null/unknown). This would give SQL a primitive not available to the user that EVERYTHING would be parameterized over. It would look like,
// Definitions                              // Value Constructors on

// Two-value
type Bool = True | False;                   // True | False

// Three-value
type NonNullable<T> = T;                    // True | False
type Nullable<T> = Null | NonNullablle<T>;  // Null | True | False

// Four-value!
type Option<T> = Unknown | Nullable<T>;     // So over every column
type Option<T> = Unknown | NonNullable<T>;  // you can store Unknown

And, then every column returned by SQL as a result of a SELECT could be Option-ally Unknown a value the user could not choose and that every type must permit. That would solve this problem. It would also make SQL far harder to teach and grok. Imagine a four-value truth table.
Fatal SELECT
From Paul White another solution would be to make SELECT statements throw a fatal error if the value can't be coerced to or represented by the domain. Some SELECT statements already throw an error, like SELECT 1/0 or SELECT CAST('foo' AS int);

Self-answered to preserve this conversation in chat


check if linked server table column exists I have several access databases that I'm copying over to the sql server, connecting to as a linked server, and then pulling data from each night.

I need to be able to identify if the current access db the linked server is pointing too has a particular column.  If not I'll need to create the column before copying the next access db over.

Is it possible to check if an access column exists through a linked server connection?

You'll have to make a connection first using VBA and then check with sys.columns

SELECT COUNT(*)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID('MyTable') AND c.name = 'ExpectedCol'

Or use COLUMNPROPERTY

That is, check the metadata separately trying to query the column


Failover Cluster Manager > Role > SQL Server (Node1 is owner) > Resources (SQL Server & SQL Agent) I created 2 separate WFC, one for database (SQLClustr) and one for application (APPCluster). Each cluster has 2 nodes plus one disk witness in quorum. I can easily failover by rebooting the one of the nodes and the cluster manager perfectly handles the failover.
I have ip for each node, ip for each cluster, and ip for each resource/role.
I am trying to automate the failover, where if I turn off the application service in APPCluster, the other node fails saying that cannot connect to the database server. It hangs.
My question is, what goes in the connection string for the client application, the ip for the SQLCluster or the ip for the SQL resource?
Also, when I manually failover from Node1 to node2, using the the roles pane, the owner node changes and the services and shared disks move over to the node2. But, at the Cluster level, the current Host Server is still node1.
Both clusters are in a VM environment.
v/r;
Jawid

My question is, what goes in the connection string for the client
application, the ip for the SQLCluster or the ip for the SQL resource?

You should always refer to the Client Access Name or the IP Addresses you see on the SQL Server role properties.

Also, when I manually failover from Node1 to node2, using the the
roles pane, the owner node changes and the services and shared disks
move over to the node2. But, at the Cluster level, the current Host
Server is still node1.

That happens because one is the management for the Cluster and the other is the management for the Role. A role is related to the service that runs on one node of the cluster and it is somehow independent from the management of the Cluster itself. Therefore, sometimes you can see the owner for the Role and the owner for the Cluster not being the same node at some point.
Sometimes you're gonna see the Quorum disk owned by the node that's not currently the owner of the other disks.


Choosing Between SQL Server 2008 R2 and SQL Server 2012 - Which to learn about first? As the title suggests, I'd like to get advice on which version should I learn about first?

Most online courses now emphasize on 2012 and Microsoft no longer gives out exams on SQL Server older than 2012.

My gut feeling says go with 2012 and study the hell out of it.
However I'm currently using SQL Server 2008 R2 at work and the company has no near future plans of upgrading to 2012 yet.

What should I do?

Yes, The certification exams for 2008 R2 are retired, but you can start learning 2008 R2 and once you master the concepts, move to the new 2012 features.

Even if your company (current) has no upgrade plans, your future company may already have upgraded all their servers to SQL Server 2012.

Check out What's new in SQL Server 2012. Also check Top 5 SQL Server 2012 Features by Aaron Bertrand.


Adding a "position" number in Mysql I got the following query and result currently:

mysql> SELECT songname,COUNT(*) as count FROM etrstats WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)  GROUP BY songname ORDER BY count DESC LIMIT 5;
+--------------------------------------+-------+
| songname                             | count |
+--------------------------------------+-------+
| Meghan Trainor - All About That Bass |    67 |
| The Cars - Drive                     |    55 |
| Train - Drops Of Jupiter             |    54 |
| C.W. McCall - Convoy                 |    52 |
| The Script - Superheroes             |    48 |
+--------------------------------------+-------+

What I want to do is add a second, temporary field to the query that shows which position it is after the sort.
So the result would look like this:

+-----+--------------------------------------+-------+
| Pos | songname                             | count |
+-----+--------------------------------------+-------+
| 1   | Meghan Trainor - All About That Bass |    67 |
| 2   | The Cars - Drive                     |    55 |
| 3   | Train - Drops Of Jupiter             |    54 |
| 4   | C.W. McCall - Convoy                 |    52 |
| 5   | The Script - Superheroes             |    48 |
+-----+--------------------------------------+-------+

SET @x = 0;
SELECT (@x:=@x+1) Pos,* FROM
(
    SELECT songname,COUNT(*) as count FROM etrstats
    WHERE songname != '' AND `when` >= DATE_SUB(CURRENT_DATE, INTERVAL 1 MONTH)
    GROUP BY songname ORDER BY count DESC LIMIT 5
) A;

Give it a Try !!!

I do incrementing of temp variables in other posts

Jun 08, 2012 : Update rank on a large table
Jun 03, 2013 : Embedded select with rank


webscalesql vs mysql. Which to use? I am confused on what the difference is between webscalesql and mysql. From my understanding webscalesql is a branch of mysql. Does this mean that webscalesql is a subset of mysql, so it is better to download MySQL rather than webscalesql as the changes made in webscalesql will be merged? If so, why would anyone download webscalesql?

webscale (https://github.com/webscalesql/webscalesql-5.6) is a fork of mysql implementing, apparently, facebook's mysql patches. These same patches are slowly making into many of the mysql forks out there.


Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence Introduction
I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a buy order, the matching engine searches whether there's a sell order with that price or lower. If that's the case, a trade happen. A trade subtracts the quantities of the smaller order, and the remaining quantity is added to the order book for the next buyer/seller.
The context
When a user submits an order, the C++ program:

Reads the database to see whether the user has sufficient balance.
Performs the trade in memory (in the program)
Modifies the database where in one transaction, it reads the balance, reads the orderbook, modifies them and persists them + writes log entries in the database (2 entries).

As you see, for a trading program, there's zero tolerance for atomicity and consistency. Otherwise there will be double-spending.
However, I can tolerate caching with only one layer over the database, which will ensure consistency and atomicity (through programming) but will postpone persistence.
I'm using PostgreSQL for my program. Also I use indexes for all my accesses of the database. Changing indexing algorithm from the default only worsened the performance. There's very little normalization there. The only normalization I did is separating the balances table from the users table. Everything else is non-separable.
The problem
It's very slow. I can barely do 100 transactions/second on an Intel 4930k (8 core, 16 threads, overclocked to 4.5 GHz), and I need 1000 transactions per second at least.
I'm not sure where the problem is coming from, but I heard that databases are not so good at modifying data. So, how can this system be optimized without endangering ACID compliance?
My solution, about which I want to ask
I'd like to implement a software layer over the database, which is the only layer allowed to access the database directly. This layer will cache the database in memory and periodically flush all the changes to the database for persistence. But then doing this makes me feel I'm doing what the database is supposed to be doing.
Is this a common thing to be done? Am I planning the right solution?

Edits from comments:
I can afford to lose transactions that are in memory only. The reasoning is that a trade at any point of time should have happened or not happened. It doesn't matter if it happened in a certain moment or one second after that. My question is kind of checking whether caching this way is valid solution and whether it's worth it. It seems like a valid solution. I'm just not sure how much it might help and why databases don't do that (or aren't configurable to do that)?
Apparently I misunderstand the word normalization above. I understand normalization to be "splitting tables to make them more meaningful", so for performance, it's worse to normalize (again, I may have misunderstood it). The only table that is split is the one I mentioned above, so instead of writing to 4 tables I'm writing to 4 tables and reading the users table. That's why I'm not very convinced that splitting that table is the reason. But maybe I have to revisit the design. I'm not an expert there.
I'm not sure I can provide DML because I'm using ODB as ORM. I'll have to see whether I can extract them. I'll provide the DDL statements soon when I get where the system is (in a few hours).
Does using an ORM mean it's hopeless? If I lose 20-30% of performance, I could live with it (for now). Should I expect losing 10 times (from 1000 tps to 100) the performance compared to manually writing queries? Seems excessive.
pgbench -t 10000 results in 2350 transaction/s on a single thread (my application uses a single thread because every order book can take a single thread, which is fine).
Edit 2:
I chose the answer below because it implements the second layer solution I'm looking for. But I still don't have the best number of transactions per second, which means I still have to study the schemas and operations I have.

Your question seems to be logically inconsistent, as tolerating a latency in persistence and "Actually I can afford to lose transactions that are in memory only" by definition violates the "D" in "ACID".

If you really want to be just "ACI" compliant, you can turn off "synchronous_commit".

Note that this means you could report to the traders that their trade executed, but then later find out that oops, it actually didn't.  It will still be atomic and consistent, as both sides of the trade and both changes in cash balances will have disappeared together.  But this could still be a bad customer experience.

If you decide you can't run with "synchronous_commit" off, you should at least do the test with it turned off.  The outcome of the test will indicate how you should proceed for further optimization.


Always on viability/configuration for very specific purpose We need to know the risks/pitfalls of a particular AlwaysOn configuration. We want an AlwaysOn configuration with the following requirements(below).

My question is: Is this a sound configuration or am I missing something vital or is there a linchpin I'm missing that invalidates this setup?

Source SQL Server:
Runs on virtual machine A
Pharmacy application connects to a primary database with full access

Target/Replica SQL Server:
Runs on virtual machine B
Microsoft access connects to replica(of above) database with read-only access to run lots of reports

Witness:
File share

Note from Microsoft white paper (we want to connect this way for both nodes--node\instance-name versus listener):

You can connect to the secondary replica using node\instance-name: Configure the primary replica to PRIMARY_ROLE (ALLOW_CONNECTIONS = READ_WRITE) and use ‘ApplicationIntent=ReadOnly’ on your connection string for reporting workload. In this case, if you mistakenly connect your reporting workload to a physical node that is already running as a primary replica, the connection is denied.

Fail over:
Fail over is manual process. Should A go down, we'd manually bring up the replica server B as the main server--asynchronously for starters -- even though there would be some room for data loss.

The only possible concern in your setup is the Availability Mode and Failover option. If your virtual machines are in the same datacenter or relatively close geographically with a good connection between them you should look into using Synchronous-commit mode and Automatic Failover for the best recovery scenario with no data loss and minimal downtime, especially if it fails during a time it may be difficult to get someone to manually fail it over.

If they are they significantly geographically apart and connectivity isn't reliably 'good' then your setup is fine unless data loss is considered catastrophic. The Asynchronous route is primarily for use when performance is sporadic or far from guaranteed.

While specifying the connection has some value, it may be overkill in such a setting if for some reason your reporting application loses connectivity to the Secondary Replica. There is no reason it couldn't report off the Primary Replica aside from any possible performance concerns, but that may be a valid enough reason to insure the Primary denies the connection.


How can I prevent the SQL Server Query Optimiser splitting a remote query? I am using Microsoft SQL Server and I have a query which reads data from a remote server. This query reads data from 3 different tables across two different databases, all of which are on the same remote server.

When I run this query in isolation as a SELECT in SQL Server Management Studio, and inspect the execution plan - I can see that the entire query is being executed as a single remote query and the whole thing takes about 7 seconds to run.

If I attempt to INSERT the results of this SELECT into a temporary table, the execution plan completely changes. It runs 3 separate queries on the remote server, then merges the results locally - and takes nearly a minute to run.

Is there any way I can persuade the Query Optimiser to stick to the first, faster option!?

Edit:
Thanks for the requests for the execution plans - here they are:

SELECT Only: https://www.brentozar.com/pastetheplan/?id=rygMoFHM8

INSERT (using the same SELECT): https://www.brentozar.com/pastetheplan/?id=S1EZhYBGI

Without seeing the query plans we can't answer in detail. There is likely a query hint you can use to encourage the query planner to use the alternate plan.

Another option is to control it from the other side: create a view in the remote end that does the work and select directly from that at the local side.

Update Once Query Plans Presented:

The warnings in the plan are often good clues, that is why they are there. In this case: Type conversion in expression (CONVERT_IMPLICIT(varchar(20),[NET2-1\NET2].[Net2].[dbo].[Users].[Field14_50],0)) may affect "Cardinality Estimate" in query plan choice.

It would appear that a difference in types between the source [NET2-1\NET2].Net2.dbo.Users.Field14_50 and the target #Net2Data.UserID is causing confusion. Without knowing the source type I suspect it is NVARCHAR(<something>). Perhaps you could add an explicit cast in the SELECT clause or change the temporary tables definition to match so there is no need for conversion at all. Also, if the source column in question is an NVARCHAR make the literal in the filtering clause explicitly a multi-byte string (LIKE N'[a-z][a-z][a-z]%' in place of LIKE '[a-z][a-z][a-z]%').


Databases outside of AlwaysOn availability group stuck in RESTORING status I am testing the AlwaysOn feature of SQL Server 2012. I have 2 servers set up and can successfully create availability groups and fail them over between servers. However, any databases that DO NOT belong to an availability group are stuck in a status of "Restoring" on the secondary server. I have started from scratch several times, and the same thing happens to these databases.

Is this normal behavior?

I was assuming that databases that don't belong to an AG would simply not show up on the secondary server.

How did you setup your Availability Group?  It sounds like what happened is you blanketed the backups and restores from the primary replica server to the secondary replica server.

In other words, you did a:

backup database <DatabaseName>
to disk .... <so on and so forth>

to all databases on the primary and a

restore database <DatabaseName>
from disk ....
with norecovery

to all the backups aimed at the secondary replica server (proper operations for future AG databases).  If you don't join one of those databases to the availability group then it'll stay in that restoring state.

The main question here is, if Database1 (for example) on your primary replica server as not meant to be in the availability group, then why would it be restored/existing on the secondary replica server to begin with?


Is it possible to quickly create/restore database snapshots with PostgreSQL? First of all, I'm a developer, not a DBA or sysadmin; please be gentle :)

I'm working on an application workflow where a single user action will trigger complex changes in the database - creating hundreds of records in some tables, updating hundreds of records in others, etc. All in all, about 12 tables (out of ~100) are touched by this action. Due to the complexity, it's very hard for me to manually revert all of the changes before I can run another test. During most of my development time, I can simply insert a "ROLLBACK" statement near the end of the workflow, but when I get close to commiting my changes, I need to test the real thing.

I have a local copy of the production database to work with. In my case, dumping and restoring between tests is faster than writing a script to undo all the changes. It's faster, but it's still slowing me down a lot (the restore takes around 20 minutes on my ageing laptop). Is there any way I can save a snapshot of the current state of the database, and then quickly restore it?

I'm guaranteed to be the only user on the system, and I have root access. The database dump is ~100MB when tar'ed and gzip'ed. PostgreSQL version is 8.3.

Thanks in advance for any helpful ideas.

You could use file-system level snapshots, but that is often pretty cumbersome, needs special file systems, and is not always available, especially on aging laptops. ;-)

How about you create your base state as a database, and then create a new database from it for your test run, using the CREATE DATABASE ... TEMPLATE functionality.  After the test, you throw that database away.  Then your speed constraint is essentially only the time to cp -R the database directory.  That's about as fast as you're going to get without file system snapshot magic.