SHRINKFILE best practices and experience Preamble: In general it's a big no-no, but believe me that are rare cases when space is really needed. For example Express Edition is limited to 10GB. Imagine that you discovered that with a data type conversion (a blob column) you can free up significant amount of space. But after that the DB file still has the same size as we know, and the 10GB limit also didn't change magically. So some kind of SHRINK is needed. That was an example.

In my test environment I performed:

DBCC SHRINKFILE (DBFile, NOTRUNCATE)
DBCC SHRINKFILE (DBFile, TRUNCATEONLY)

That did the trick (I know that that minimizes the free space, in real world I would leave free space). it took many-many hours to finish. As we know it's a single threaded process Strange behaviour DBCC Shrinkfile, "it works as a series of very small system transactions so there is nothing to rollback." - Paul Randal http://www.sqlservercentral.com/Forums/Topic241295-5-1.aspx. We also know that it messes up the index fragmentation big time http://www.mssqltips.com/sqlservertip/2055/issues-with-running-dbcc-shrinkfile-on-your-sql-server-data-files/ and I can confirm that. I didn't experience log file grow though described in http://www.karaszi.com/SQLServer/info_dont_shrink.asp

I issued some INDEX REBUILD and REORGANIZE and those finished within seconds.

My questions:

What's the big deal about shrink if I can just fix the index fragmentation after the shrink within seconds? I don't understand. Here on DBA stackexchange the "shrink" topic (https://dba.stackexchange.com/tags/shrink/info) says "Pretty much the worst thing you could do to a SQL Server database. In short: It sacrifices performance to gain space." plus refers to another popular article about it. But index fragmentation can be fixed.
Why I didn't experience any log file growth?
What if REBUILD the index first after the space free-up operation. Can that substitute the first DBCC SHRINKFILE (DBFile, NOTRUNCATE) so I just need to DBCC SHRINKFILE (DBFile, TRUNCATEONLY)? I have a feeling that the two work on different logical level but I have to ask this.

Bottom line: I promise I won't do shrink regularly or anything. But this is a situation where a cap is hit and shrink is needed.

Answer to my 2nd question: I didn't experience transaction log file growth, because I was fiddling in a developer test environment. The transaction log file would grow in a production environment (assuming Full Recovery Model). You should mimic that in your testing environment too. The tr log file growth is actually bigger than the space you can free up inside the database file. In the end you not only want to shrink the database file, but also the transaction log (how and when to do that depends on the recovery model). Production environments I saw had so screwed up index fragmentation that it can be just better. My script reindexes every index which fragmentation level is above 30%.

To handle the growth, you may have to switch from full recovery to simple while you are doing the once-in-a-lifetime conversion. But that will break the backup chain.

Answer to my 3rd question: do the reindex/index rebuild after the DB file shrink, because the shrink messes up index fragmentation.

I haven't experimented how all of this affects query statistics.

Script for reindexing: see Example D of TechNet sys.dm_db_index_physical_stats (Transact-SQL). An article on incremental shrink, which will lead to other valuable content: http://www.mssqltips.com/sqlservertip/3178/incrementally-shrinking-a-large-sql-server-data-file-using-powershell

If you want to:

ignore all of the warnings from very smart people with a lot of experience in SQL Server;
assume that because in this case you didn't experience log growth and in this case fixing the fragmentation was quick and didn't cause any further growth, that will always be the case; and,
not confirm that this grow/shrink/grow/shrink cycle has any impact on query times;

Go ahead. Since you say you will only be doing this in rare scenarios, I'm not sure what answer you're looking for here. Do you want a big thumbs-up that you can ignore everything you've read about it and roll the dice?

I will suggest at least one thing: hitting the 10GB limit in Express is going to happen if you have 10GB of data. You avoid that by removing data in the data file (or spreading your data across multiple databases). If you free up a bunch of space inside the data file, that space will be re-used (if you dropped a table, this will be automatic; if you deleted rows or changed column structure, you will need to rebuild - which will also take some space at least temporarily). So if you free up 5GB of data in the file, just because the file is 9GB does not mean that it will try to become 11GB if you add 2GB - it will reuse space inside the file until it can't find any more. It will be a much more expensive operation to shrink the file to 4GB, then force it to grow to accommodate the new data. This is like washing an already clean towel that you're about to use to wipe up a mess...


sharding and replication on mongodb? My question is on the answer given at Difference between Sharding And Replication on MongoDB

I need to split thedata of 75GB into 3 shards of 25GB each with replication factor of 3. Answer depicts below picture

                            Sharded Cluster
             /                    |                    \
      Shard A                  Shard B                  Shard C
        / \                      / \                      / \
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+
|Primary| |Secondary|    |Primary| |Secondary|    |Primary| |Secondary|
|  25GB |=| 25GB    |    | 25 GB |=| 25 GB   |    | 25GB  |=| 25GB    |
+-------+ +---------+    +-------+ +---------+    +-------+ +---------+

and says we need at least 6 database servers organized in three replica-sets. Each replica-set consists of two servers who have the same 25GB of data.

My understanding is that each shard is a physical machine holding below two sets of data(primary and secondary) and host two separate mongod server/instance . One will hold primary data and second will hold secondary data which is back up of primary data on another shard

Primary data :- This is 1/3 of the 75GB data distributed over some partition key
Secondary :- This will hold secondary data which is back up of primary data on another shard

Application query will go only to primary data on a given shard based on partition key. It is possible that on a shard a secondary gets converted to primary if another shard primary goes down .  Is my understanding correct here ?

No of instances of Mongos and Arbiter

Also i believe i need to start only 1 mongos instance(which will be separate from mongod instance) ? Similarly I need to have single mongo arbiter for all 3 shards instead of 3 on each shard ? Both mongos and aribiter can be on separate machine or share the hardware of one of the shard depending on requirement ?

My understanding is that each shard is a physical machine. holding below two sets of data(primary and secondary) and host two separate mongod server/instance.

No, each replica-set member is a physical machine. In the above graphic, each shard consists of two physical machines. While you can technically run multiple members of a replica-set on the same machine, there is nothing to gain from that.

  . One will hold primary data and second will hold secondary data which is back up of primary data on another shard

No, the secondary will back up the data from the other member(s) of the same shard. But if you want to build the server-level equivalent of a mirroring RAID, you could of course put two members of different shards on the same physical machine.

  i believe i need to start only 1 mongos instance

You only need one, but to avoid a single point of failure, you might want to have more than one.

  Similarly I need to have single mongo arbiter for all 3 shards instead of 3 on each shard?

An arbiter can only be member of a single replica-set. So you need 3 arbiters. However, the arbiters are very lightweight. They don't hold any data and don't do anything unless there is a primary election. So you do not need a dedicated server for the arbiter processes.


Ensure data is physically deleted from a data file Consider the following:

You create a table space/data file for a table. You insert data into the table and this table has an index.

When you delete the data, it is possible that the data is not actually deleted from the data file. I wrote the program

#include <stdio.h>

int main()
{
   FILE *file = fopen("/some/file/name", "r");
   int ch;
   while ((ch = fgetc(file)) != EOF) {
     if (isprint(ch)) printf("%c", ch);
   }
}

This will display any printable characters. I have found that sometimes the data still exists in the data file (on the OS level)

Is there a way of getting Oracle to zero this data?

EDIT

The URL to the posted question does not solve the problem - as stated in one of the answers - This wouldn't work with indexes since updates are translated to delete+insert in a btree index.*

Where I work, we had this exact issue. I wrote code that read metadata tables and redacted information for people or companies on request. I also logged the id value for every person that we redact. Hence if we do bring back an old database we have the option of taking that metadata and redacting it in any backup that we restore. It would not be practical to bring back every backup, restore, redact people who want to be redacted, do a new backup and send the backup back offsite. We can think about the retention that were need for offsite backups. Ideally in the future we will separate the PII of people from the financial data that we need to keep longer. thus it would be easier to redact some data, but keep the data that we really need. You need more than a single answer. You need to build a framework on how to redact data and still know which data was redacted in case it needs to be redacted again.


Entity Relationship Model - double one to many ERD

A customer submits one or many  leads.
A lead belongs to one customer.
A lead is a request or an inquiry.
A lead belongs to one manufacturer.
A manufacturer has one or many leads.

Tables

Only focusing on manufacturer and lead I set up the tables like below.

manufacturer            lead
--------------          ---------------
MId                     LId
name                    MId
description             date
...                     ...

Adding customer table I add CustomerId to lead table.

manufacturer            lead                customer
--------------          ---------------     ---------------
MId                     LId                 CId
name                    MId                 phone
description             CId                 ...
...                     date
                        ...

Issue:

With all three entity types, is there a benefit to have an additional linking table instead adding ManufacturerId(MId) and CustomerId(CId)  as column to Lead table? Does this offend any best practice? Or should I even include two separated linking tables? Thank you for Explanation and 'further reading links'.

Your LEAD table is an intersection between MANUFACTURER and CUSTOMER.  That is a perfectly legitimate design.  Intersection tables don't have to be pure intersections.  They can have other attributes in their own right (such as your date or type).


sp_help_jobsteplog fails when run as a @query of sp_send_dbmail I have a SQL 2005 stored proc that sends an email with some job history:

msdb.dbo.sp_send_dbmail
 @recipients =   'me@here.com',
 @Subject =      'job history',
 @Body =         'check this out...',
 @Profile_name = 'PublicEmail',
 @query = 'EXEC msdb.dbo.sp_help_jobsteplog ''F85B07F2-BC89-4934-B997-...''',
 @attach_query_result_as_file = 1

(that's a full GUID, I just cut it off for the code window) I know there are other ways to get job history, but this method has worked until recently, and now I get the following error:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 495
Query execution failed: Sqlcmd: Error: Internal error at GetDataRowset
   (Reason: Not enough storage is available to complete this operation).
Sqlcmd: Error: Internal error at FormatRowset
   (Reason: Not enough storage is available to complete this operation).

I can run the sp_help_jobsteplog query all by itself with no problems. There is no shortage of space on the server, so I think the "not enough storage" part of the error message is probably not really what's going on. The documentation for sp_help_jobsteplog says

  By default, members of the sysadmin
  fixed server role can execute this
  stored procedure. Other users must be
  granted one of the following SQL
  Server Agent fixed database roles in
  the msdb database:

  SQLAgentUserRole
  SQLAgentReaderRole
  SQLAgentOperatorRole

So when I combine that with the fact that db_send_dbmail runs @QUERY statements in a separate session, I'm worried that its actually a permissions issue.

My question is: what user does sp_send_dbmail try to use to run its @Query? (I'm running sp_send_dbmail itself as a sysadmin, and if it carried through those credentials, it would work, right?) Does it depend on the profile used?

Any other ideas what might be going wrong here?

EDIT: So I still have no idea why this was happening, but I rebooted the server, and now everything appears to be back to its (normal) working state!

Did you try making the query 'EXEC msdb.dbo.sp_help_jobsteplog ''...'';' ? Chances are that internally there are other commands injected before the stored procedure is called, and if the procedure call is not the first in the batch, it must use EXEC or EXECUTE. I wish they would banish the ability to just say sp_anything without a preceding EXEC, it would make our lives far easier than the current savings of 5 keystrokes does.


What does "-" mean before a column in where I was seeing an Oracle view sys.dba_audit_trail, and I came across the following where clause:

where   aud.action#     = act.action    (+)
  and - aud.logoff$dead = spm.privilege (+)
  and   aud.logoff$dead = aom.option#   (+)
  and - aud.priv$used   = spx.privilege (+);

I would like to know what - means.

And if possible, know if I can do it with ANSI SQL.

It just negates the value in the column.

For example:

 with data1 as ( select 1 as col1 from dual)
 select -col1, col1
 from data1
 where -col1 = -1;

... returns -1,1.

Fiddle.


AlwaysON Availability group query very slow I am running below tsql to find the secondary AlwaysON servers from the primary replica :

set transaction isolation level read uncommitted
set statistics time, io on
SELECT
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM master.sys.availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN master.sys.availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off

The above query takes 12 mins and 38 secs to run and returns 34 rows. Below is the statistics IO and time output:

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

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

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

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

(34 row(s) affected)
Table 'Workfile'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysguidrefs'. Scan count 0, logical reads 212, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'sysclsobjs'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

(1 row(s) affected)

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

Here is the link to actual execution plan.

Anything that can be done to make it faster ?

Below is from Plan Explorer

Click to enlarge

Click to enlarge

Thanks !

Answering my own question that might be helpful to future visitors ..

Aaron's comment made me thinking if we dump the data into temp table would it speed up ?

Yes it indeed speed up as due to large number of Availablity groups 53 (I know thats a lot and we have plans to offload them soon), sys.availability_groups and sys.availability_replicas selects were taking considerably long time.

Below tsql runs in 1 min and 03 secs :-)

set transaction isolation level read uncommitted
set statistics time, io on

--- get data into temp tables

select * into #availability_groups from sys.availability_groups
select * into #availability_replicas from sys.availability_replicas

SELECT
ISNULL(agstates.primary_replica, '') AS [Primary_AG_Server],
dbcs.database_name AS [DatabaseName]
 ,ar.replica_server_name as [Secondary_AG_Servers]

   FROM #availability_groups AS AG
LEFT OUTER JOIN master.sys.dm_hadr_availability_group_states as agstates
   ON AG.group_id = agstates.group_id
INNER JOIN #availability_replicas AS AR
   ON AG.group_id = AR.group_id
INNER JOIN master.sys.dm_hadr_availability_replica_states AS arstates
   ON AR.replica_id = arstates.replica_id AND arstates.is_local <> 1 -- is_local is not 1 .. so we get secondary server names
INNER JOIN master.sys.dm_hadr_database_replica_cluster_states AS dbcs
   ON arstates.replica_id = dbcs.replica_id
    group by agstates.primary_replica, dbcs.database_name, ar.replica_server_name
    set statistics time, io off


mysql french documentation I am not able to find a translation for Mysql documentation

  http://dev.mysql.com/doc/

I read English quite well, but it is not the case for all my colleagues.

Here it is for MySQL 5.0
http://downloads.mysql.com/docs/refman-5.0-fr.a4.pdf

It's not completely new, but it might be helpful.


how to insert values in a table with foreign key field I am creating a database where I have a table with a foreign key. I am using the following command:

insert into ques_bank
    (q_no, uid, question)
values
    (NULL, select uid from user_info where username ='more','jdkjfsd dkskdsn skdjsd');

But it gives a syntax error.
I am having troubles with foreign key. Any help will be appreciated.

There is no information on the definition of your table (what foreign key?) but based on your SQL you should use a select to enter it:

insert into ques_bank
    (q_no, uid, question)
select null, uid, 'jdkjfsd dkskdsn skdjsd' from user_info where username ='more';

or:

insert into ques_bank
    (uid, question)
select uid, 'jdkjfsd dkskdsn skdjsd' from user_info where username ='more';


Table Design - Identical tables for different statuses I am developing a transactional System and the below is a simplified scenario of my problem.

Users enter JOBS in a system.
A User first enters a JOBS with a DRAFT status (and the Job is given a draft number)
When the user submits the JOB, the status is changed to SUMBITTED (it is given an actual number)
Finally when the JOB is completed, the status is changed to POSTED.

I was thinking of creating 3 separate identifical 'JOB' tables for it. when a Job changes from draft to submitted, I move the data.
This is mainly for performance reasons. Most queries would be on the current JOBS and this would ensure that its table is not cluttered with all the posted.
Furthermore such a division of tables would provide structure improvement because I can use the JOB number as the primary key. If there was a single table, the draft number would need to be changed to an actual number. And primary keys should never change.

Now, my question is - does my approach make sense? is it customary to have separate tables? If anyone had experience with this and can provide feedback, that would be great.

In my experiences with similar things (tables of scheduled jobs and tasks), everything is in one table. There is usually  a status column, but jobs of all statuses are in the same table (sometimes there's an index on status). As for primary key, why not just use a sequential ID? I've seen that work very well. You can also have as combined key of draft number AND actual job number, unless there's some other requirement I'm not understanding.

If you think this will improve performance, make sure you have measurable problems that can only be solved this way before you make any changes. Also try to prove that this method results in actual necessary performance gains.


SQL Agent PowerShell Syntax Error I am attempting to setup a PowerShell script within a SQL Agent job (SQL Server 2016). I'm using a third party module, DBATOOLS.psd1 which appears to be where I'm getting my syntax error. Currently my SQL Job has one line of code which is failing:

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1

Why is the job failing with the following error? This command works just fine when I run it through the PowerShell ISE.

Error Message: Unable to start execution of step 1 (reason: line(5): Syntax error).  The step failed.

If you are running this as a SQL Server Agent job step you will need to use the Operating system (CmdExec) step type, not a PowerShell type.

Within the CmdExec it would be best to create a file on the machine with all of the steps that you require and then call that.

For example create a file C:\PowerShellScripts\MyScript.ps1 which contains...

Import-Module e:\dbatools-master\dbatools-master\dbatools.psd1
Copy-SqlLogin -Source ServerA -Destination ServerB

And then call that script using powershell.exe "& 'C:\PowerShellScripts\MyScript.ps1'" in the CmdExec job step.


MongoDb BI connection issue I am trying to configure the MongoDB BI Connector.

I have installed mongodb-bi-win32-x86_64-v2.5.0.msi Mongodb version is 3.0.15

1.Started mongod

mongod --dbpath d:/data1 --auth

Started mongosqld
mongosqld --auth --mongo-username xxxx --mongo-password xxx --mongo-authenticationSource admin

I am getting the following error

E SAMPLER [schemaDiscovery] unable to initialize schema: error sampling collection: failed to execute aggregate: unrecognized fi eld 'readConcern'

Let me know how to resolve the issue

Your mongod version is too old... For 2.5 BI connector

As said here!

  The MongoDB Connector for BI and associated utilities are compatible
  only with MongoDB server version 3.2 or greater.


Transferring Mongodb database with rsync while working, is it okay? I need to transfer MongoDB data from another server with rsync with MongoDB running. Making a dump is not an option for me, as it takes too much time.

What problems can I face doing it? Or, if this is not an option, how can I transfer data (var/lib/mongdobd) from one MongoDB instance to another without stopping it?

I use MongoDB version 3.2.11 in production (one instance,no replicas) and I need to transfer data from MongoDB version 3.4.9

No, not directly! If your data is top of LVM, do a snapshot and copy that to another node, start it and connect it to the replica set. This only if you have lot's of data and normal initial sync (as @SqlWorldWide wrote) would take too long time.


Slow Postgres updates if OTHER columns are indexed? Certain updates take far too long on large Postgres tables. Given these conditions:

only one column is being updated, and it is non-indexed
the column already has data in it for every row due to a prior update
the data is not changing size (e.g., rewriting a boolean value)
there is no other column in this or any other table that depends on the value of the column being updates
there are no other queries being performed on the database (this is a personal research database on a workstation, not an enterprise database)
there are indexes on other columns
spinning drive (not SSD) with Bitlocker and fast PC with Windows 8.1 x64
the table has 10 million rows and 60 columns

...you'd think that the update would take a reasonable amount of time relative to expectations of spinning media with Bitlocker. We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten. The other indexes shouldn't need changes. Etc. Instead, after 20 hours of constant HDD grinding, I get tired of waiting and stop the query. If I drop all indexes on other columns and re-run the query, it takes only about 30 minutes.

Why do indexes on columns irrelevant to this query balloon the update time?

We're not creating more data, so existing data shouldn't need to be moved around on the HDD, it just needs to be overwritten

That's not the case.

In order to support rollback and crash-safety, PostgreSQL must write a new copy of every modified row, rather than modifying the row in-place. Twice, actually, because it must be written to WAL (a sequential log for crash recovery) then to the table.

PostgreSQL's autovacuum then comes along later and marks the old row versions as free space that can be re-used.

See the user manual for more information on concurrency control and MVCC.

  the data is not changing size (e.g., rewriting a boolean value)

Irrelevant, because the row must be rewritten anyway.

  only one column is being updated, and it is non-indexed
  there are indexes on other columns

This only matters insofar as it affects HOT updates, where PostgreSQL can potentially avoid writing new index entries for a row update if no indexed columns are modified and there's enough free space on the same disk page (8k block) to store a new copy of the row.

  Why do indexes on columns irrelevant to this query balloon the update time?

In most cases PostgreSQL must add new index entries even if you didn't modify indexed columns, because it has to write a new version of the row to a separate database page.

Setting a FILLFACTOR of 50 or less can help with this, as it encourages PostgreSQl to keep more free space for updates, at the cost of scans having to read and process more data.


Insert missing rows to subscriber of Transactional Replication I have established SQL Server Replication between SQL Server 2005 and 2008 R2. SQL Server 2005 is the publisher while SQL Server 2008 R2 is the subscriber.  The replicated database is about 40GB.

My replication was working most of time OK for few years. A few days ago I saw that I have missing rows inside few tables. When I check manually I see which rows I have missing. When I check replication monitor I see no errors and all looks nice. My trace tokens said that there is no latency.

But I know that there is no rows.

Now I want to manually transfer these rows from publisher to subscriber.

I do not want to reinitialize my replication because when I do that all my statistic go away and my users become to suffer with performance when start using fresh database.

Other thing that my publisher is very poor with performance and reinitialization took a long time to finish and I do not have enough time to stop my app during that process.

Is there an easy way to force the publisher to resend missing data to subscriber beside reinitializing the whole replication again?  Is there a way to keep my statistic and indexes on subscriber database after reinitializing that, so I do not lose my performance?

You will need to run a validation to determine how out of sync your publisher and subscriber are.  Then, use the tablediff utility or Red Gate SQL Data Compare to generate the script(s) necessary to get the subscriber back in sync.  Then apply the script(s) at subscriber to bring the data back into convergence.


How to Import an Oracle dump version 5.1 into 12c? I have .dmp files exported as version 5.1 and I need to import them on an Oracle 12c. I have no access to the original database to re-export setting the VERSION parameter.

This is the error I get:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "/media/sf_Data
/DUMP_MS/mydumfile.dmp"

What can I do?

Of course Oracle did not make this information public, but if you can access MOS, then go ahead:

Export/Import DataPump Parameter VERSION - Compatibility of Data Pump Between Different Oracle Versions (Doc ID 553337.1)

12.1 uses dump file sets of version 4.1. 12.2 uses dump file sets of version 5.1

You can not import a dump file created in 12.2 to 12.1, unless the VERSION parameter was specified to allow lower versions to import it.

You will need at least a 12.2 version database to import that dumpfile.


How can I fetch parent table's value within a trigger? I have a PARENT table and a CHILD table.

PARENT
------
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL

CHILD
-----
ID BIGINT PRIMARY KEY
NAME VARCHAR(255) NOT NULL
PARENT_ID BIGINT NOT NULL

I want to create a trigger which does

when a CHILD row inserted or updated
check the NEW.NAME
and if the NEW.NAME is NULL or EMPTY
use PARENT's NAME as NEW.NAME

How can I do that?

Tables:

create table PARENT
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL
);

create table CHILD
(
ID BIGINT PRIMARY KEY,
NAME VARCHAR(255) NOT NULL,
PARENT_ID BIGINT NOT NULL
);

Trigger:

DELIMITER $$
 CREATE TRIGGER namecheck BEFORE INSERT ON CHILD
   FOR EACH ROW
BEGIN
  DECLARE parentname VARCHAR(255);

  IF NEW.NAME IS NULL OR NEW.NAME=''
  THEN
    SELECT NAME
    INTO @parentname
    FROM PARENT
    WHERE ID=NEW.PARENT_ID;
    SET NEW.NAME = @parentname;
  END IF;
END;
$$
DELIMITER ;

Test:

mysql> insert into PARENT values ( 1,'phil' );
Query OK, 1 row affected (0.01 sec)

mysql> insert into CHILD values(1,'',1);
Query OK, 1 row affected (0.02 sec)

mysql> select * from CHILD;
+----+------+-----------+
| ID | NAME | PARENT_ID |
+----+------+-----------+
|  1 | phil |         1 |
+----+------+-----------+
1 row in set (0.00 sec)

mysql>

There's no error checking etc, but I'll leave that as an exercise for you.


how to refresh table in pg-admin 4? When i select table with view/edit data => all Rows then i try to refresh table for newly updated data in table from object tab. it seems it's not working.this works perfectly in pg-admin 3 but in pg-admin 4 it seems it's not working at all.is this an issue in pg-admin 4 or i configured something wrong when i installed i followed all the instructions in docs on how to install it on Ubuntu exactly.can someone help me on this matter or explain it ?

Right click on the table and choose 'refresh' from the drop down menu.
Or better, right click on the database selected and choose 'refresh' from the drop down.


Where can I get best resources to learn sql? I am very new to the databases just have basic knowledge about it.

I want to learn sql language and dont know where to start. Please help me to start with it.
And what is the difference between sql and PostgreSQL? What are the different languages to maintain databases.

Any help will be appreciated.

SQL is a declarative data query language. PostgreSQL is a database which supports SQL (as do all RDBMS's). Oracle supports SQL, so does MySQL &c. There are differences - PostgreSQL is very ANSI compliant, whereas Oracle and MySQL not so much.

If you want to learn, Google SQL tutorials and maybe use dbfiddle.uk (more servers) or db-fiddle.com (a bit easier) to test - or just download PostgreSQL/MySQL/Firebird. The three of these are Open Source and free to use for any purpose, bar MySQL: you can't distribute a closed-source application using MySQL without paying a licence fee to Oracle because of the dual GPL/commercial licencing.

From here:

  MySQL Commercial License for OEMs, ISVs and VARs Oracle provides its MySQL database server and MySQL Client Libraries under a dual
  license model designed to meet the development and distribution needs
  of both commercial distributors (such as OEMs, ISVs and VARs) and open
  source projects.

What this means is that basically, if you want to distribute MySQL based apps without paying a fee, your app must be under the GPL version 2.

You can also download Oracle XE for free (latest version as of 05/2018) is still 11g - limitations from here:

  Oracle Database XE can be installed on any size host machine with any
  number of CPUs (one database per machine), but XE will store up to
  11GB of user data, use up to 1GB of memory, and use one CPU on the
  host machine.

There's also:

Microsoft SQL Server Express with limitations 1GB RAM and 10GB data - from here and here.

Your question is very general (too broad) for this forum - you need to read a couple of books/tutorials and come back with specific questions (with what you've tried - use one of the fiddles above for your DDL for your sample data (CREATE TABLE...), DML (INSERT INTO blah VALUES (...);) in your questions.


MySQL wont start after modifying /etc/my.cnf I have Linux Red Hat 5, with Zend Server and MySQL Server installed.

I am trying to change the default MySQL port to 512.  However, after making this change, MySQL will no longer startup.  I get the error below:

Starting MySQL...The server quit without updating PID file
    (/var/lib/mysql/localhost.localdomain.pid).

How do I resolve this issue?

You say that you are trying to change the MySQL port from 3306 to 512.  If that is correct, then then problem is that all *nix (Linux, Solaris, AIX, etc) require you to run as root in order to bind to ports below 1024.

Try running it on port 3307 or something.


Is there a performance benefit to placing transaction log files on a separate drive? There are many blog posts and best practice articles extolling the virtues of placing the SQL Server data file on one hard drive and the transaction log on another. The reason given is that that the database file will be experiencing random reads and writes while the transaction log will only have sequential writes.

But what if you have hundreds of databases? Is there a true performance benefit to placing hundreds of transaction log files on a separate disk? If multiple transaction logs are being written to, then I would think the transaction log writes would be just as random as the database writes.

Correct. In theory, if you have 100s of DBs you need 100s of drives, one for each log. In practice though one does not care for such case, cause when you have 100s of DBs you obviously don't expect top-notch TPC performance for each DB. You will likely have some DBs with high throughput and stringent SLAs and you could have them each on separate spindles, while the many lower level SLAs cram on a few shared disks.


Do decimal separators behave differently for Money and Decimal data types? I've recently been troubleshooting some problems with a third party application around regional settings and decimal separators. The detail of these problems isn't too relevant except to say that the application is a web-service reading to a SQL Server column with the data type money and that it appears to multiply the value because a decimal separator is being interpreted as a thousand separator.

This leads me to the question: In the course of my investigations I noticed that if I open SSMS and execute the following I get "1,00" returned:

declare @money money;
set @money = 1;
select @money;

However, if I execute the following against the same server then I get "1.00" returned:

declare @decimal decimal(18,2);
set @decimal = 1;
select @decimal;

My Windows regional settings here are configured to use a comma as the decimal separator, so on the face of it the money value is displayed correctly and the decimal is not.

What's going on here? It's unclear why the behaviour is different for these two data types. Is this down to SQL Server or is this caused by the .Net framework? (The application utilizes the .Net framework and presumably SSMS is doing so in the same way...)

I found the following ticket which seems to refer to the same issue but doesn't seem to provide an answer: https://connect.microsoft.com/SQLServer/feedback/details/636074/some-datatypes-doesnt-honor-localization

The ToString() implementation in the SqlDecimal type in System.Data.Types contains this as the final step in the algorithm:

while (i > 0)
{
    if (i-- == (int)this.m_bScale)
    {
        array3[num2++] = '.';
    }
    array3[num2++] = array2[i];
}
return new string(array3);

Notice there is a hardcoded '.' in there? SqlMoney does not have this as it calls a specific format which uses the current cultureinfo.


How to find the query that is still holding a lock? Querying the sys.dm_tran_locks DMV shows us which sessions (SPIDs) are holding locks on resources like table, page and row.

For each lock acquired, is there any way to determine which SQL statement (delete, insert, update or select) caused that lock?

I know that the most_recent_query_handle column of the sys.dm_exec_connections DMV gives us the text of the last query executed, but several times other queries ran before under the same session (SPID) and are still holding locks.

I already use the sp_whoisactive procedure (from Adam Machanic) and it only shows the query that is on the input buffer at the moment (think DBCC INPUTBUFFER @spid), which not always (and in my case usually never) is the query that acquired the lock.

For example:

open transaction/session
exec a statement (that holds a lock on a resource)
exec another statement on the same session
open another transaction/session and try to modify the resource locked at step 2.

The sp_whoisactive procedure will point out the statement at step 3, which is not the responsible for the lock, and thus not useful.

This question came from doing an analysis using the Blocked Process Reports feature, to find the root cause of blocking scenarios in production. Each transaction runs several queries, and most of time the last one (that is shown on input buffer at BPR) is rarely the one holding the lock.

I have a follow-up question: Framework to effectively identify blocking queries

SQL Server doesn't keep a history of the commands that have been executed1,2.  You can determine what objects have locks, but you cannot necessarily see what statement caused those locks.

For example, if you execute this statement:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

And look at the SQL Text via the most recent sql handle, you'll see that statement does show up.  However, if the session did this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

You'd only see the SELECT * FROM dbo.TestLock; statement, even though the transaction hasn't been committed, and the INSERT statement is blocking readers against the dbo.TestLock table.

I use this for looking for uncommitted transactions that are blocking other sessions:

/*
    This query shows sessions that are blocking other sessions, including sessions that are
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS
(
    SELECT v.*
    FROM (VALUES
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.QueryText
    , agg.CountOfBlockedSessions
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;

If we setup a simple test-bed in SSMS with a couple of query windows, we can see that we can only see the most recently active statement.

In the first query window, run this:

CREATE TABLE dbo.TestLock
(
    id int NOT NULL IDENTITY(1,1)
);
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

In the second window, run this:

SELECT *
FROM  dbo.TestLock

Now, if we run the uncommitted blocking transactions query from above, we see the following output:

╔═══════════╦═══════════════════════════════╦════════════════════╦═════════════════════════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║                QueryText                ║
╠═══════════╬═══════════════════════════════╬════════════════════╬═════════════════════════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ BEGIN TRANSACTION                       ║
║           ║                               ║                    ║ INSERT INTO dbo.TestLock DEFAULT VALUES ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *                                ║
║           ║                               ║                    ║ FROM  dbo.TestLock                      ║
╚═══════════╩═══════════════════════════════╩════════════════════╩═════════════════════════════════════════╝

(I've removed some irrelevant columns from the end of the results).

Now, if we change the first query window to this:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

and re-run the 2nd query window:

SELECT *
FROM  dbo.TestLock

We'll see this output from the blocking transactions query:

╔═══════════╦═══════════════════════════════╦════════════════════╦════════════════════╗
║ SessionID ║           ItemType            ║ BlockedBySessionID ║     QueryText      ║
╠═══════════╬═══════════════════════════════╬════════════════════╬════════════════════╣
║        67 ║ Transaction                   ║                  0 ║ SELECT *           ║
║           ║                               ║                    ║ FROM dbo.TestLock; ║
║        68 ║ Session Request, Waiting Task ║                 67 ║ SELECT *           ║
║           ║                               ║                    ║ FROM  dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩════════════════════╩════════════════════╝

1 - not entirely true.  There is the procedure cache, which may contain the statement responsible for the lock.  However, it may not be easy to determine which statement is the actual cause of the lock since there may be many queries in the cache that touch the resource in question.

The query below shows the query plan for the test queries above since my procedure cache is not very busy.

SELECT TOP(30) t.text
    , p.query_plan
    , deqs.execution_count
    , deqs.total_elapsed_time
    , deqs.total_logical_reads
    , deqs.total_logical_writes
    , deqs.total_logical_writes
    , deqs.total_rows
    , deqs.total_worker_time
    , deqs.*
FROM sys.dm_exec_query_stats deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) t
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) p
WHERE t.text LIKE '%dbo.TestLock%'  --change this to suit your needs
    AND t.text NOT LIKE '/\/\/\/\/EXCLUDE ME/\/\/\/\/\'
ORDER BY
    deqs.total_worker_time DESC;

The results of this query may allow you to find the culprit, but be aware, inspecting the procedure cache like this can be quite demanding on a busy system.

2SQL Server 2016 and above offer the Query Store, which does retain complete history of queries executed.


Queries not Running in Prod I've an update statement which uses 2-3 tables. It runs in less than a second in QA but it takes forever in Prod. The number of records are exactly same and all the indexes are same too. In fact Prod server is an stand alone box while QA is a VM and have Dev,QA and Stage SQL Server instances on one server. So in that way Prod is better equipped than QA or Stage as it has more memory and more h/w.

And it's not just one update statement which has this issue. There are some other selects too which runs in lower environments but they don't move at all in Prod. I tried these codes to run when no one is on Prod.

My dba restored the prod copy to another instance. where no user has any access. And then he tried to run the same query. Again, it runs in QA but not in prod and not in the new instance. What could be the possible things which We should look for?

The update I'm running is -

UPDATE d Set Status = 'Active'
FROM DimesionTable D
INNER JOIN StageDB.dbo.StageTable a on d.PublicID = a.PublicID
WHERE EXISTS(SELECT 1 FROM StageDB.dbo.Table1 p
     INNER JOIN Table2 d ON p.PublicID = d.PublicID
     WHERE p.RetroFlag = ''
     AND p.AccountID = a.RowNumber
     AND d.IsActive = 1
     AND (p.CancellationDate is NULL
     OR p.CancellationDate > GETDATE()
     OR p.CancelFlag = 'Cancelled')
AND GETDATE() BETWEEN p.StartDate AND p.EndDate) AND d.AccountID > 0

I'm ruling out blocking as I tried in off hours in Prod as well as in the new environments where no one has any access.  I looked in sys.configurations, and the only differences I found are:

max degree of parallelism - Prod - 8      | QA - 0
max server memory (MB)    - Prod - 472064 | QA - 256000

Both servers have 8 CPUs. I double checked all the involved tables and all have the same indexes. Still the query plans are different. And the suggested index by SQL Server is different too. My understanding is - if the tables has exact same structure, then query plan and suggested indexes should be same.

Indexes and statistics are getting updated nightly. And data distribution is also exactly same. No difference at all. We restored the prod database on a separate instance and there too we have the same issue. In fact, it's not just this query, there are other queries too which is having the same problem. I'm more concerned about something wrong with the server rather than the query. All these queries run in just few secs or a min or two in QA but they won't finish at all in prod or the new test environment restored from Prod.

Tempdb configuration:

QA   - Size - 122880 MB, Available - 97277 MB
Prod - Size - 174080 MB, Available - 148476 MB

Total 8 files in each server.

QA - 1st one's initial size is 25600 and rest 10240, auto growth - 5120 MB unlimited
Prod - 1st one's initial size is 76800 and rest 10240, auto growth - 15% unlimited

Initially STG was created from QA and it used to run on STG. But when we created STG from PROD, it's not running there either. The compatibility is set to 120 on both the instances.

Two likely possibilities.
Blocking
Your production machine probably has a lot more going on than your test/dev machines.  You could simply be seeing blocking.  Run your query in one window and this in another.
SELECT * FROM sys.dm_exec_requests WHERE blocking_session_id > 0

If you are being blocked you may need to look at what's blocking you and how long it's taking to run.
Configuration differences
This one covers a LOT of possibilities.  It could be a difference in the max memory as Kin suggested or any number of other possibilities. See:
difference in execution plans on UAT and PROD server
For example if the max degree of parallelism is 1 or the cost threshold for parallelism isn't the same on both servers you could be getting a single threaded plan instead of getting a parallel plan.  It could even be the reverse.  I had one case where the parallel plan was quite a bit slower than the single threaded plan.
Compare the results of this query between the two servers:
SELECT * FROM sys.configurations

Check any differences and see if they are affecting your query.


Database design - Members & companies represented by members I'm struggling in database design problem and it seems I can't get it out properly.

I need some help on this. Maybe it's a dumb case but I'd like to validate or correct my scheme and then go ahead.

The app should behave like this:

a user can be part of multiple accounts and have different roles (admin or not)
an account can have one or many groups
each group can have one or many members
a member can be part of many groups
a member can be either an user or a legal entity (company)
if it's a company, then the company is represented by a user
a user can represent 0,1 or many companies in different groups

Here is my actuel scheme where I have a n-to-m relationship on Groups-User-Companies.

Here in GroupMembers, the users_id or companies _id will be filled either it's a legal entity or individual.

And the CompanyRepresentedBy table will contain the group_id, user_id and company_id

I'm not really sure of this scheme.

To have a better understanding, here is a diagram of the relationship Account-Group-Member-User

Thank you all!

PS: I can provide the MySQL Workbench file if needed

Why would you split into User and Account?

What is Group standing for? It's relating to Account and Company now, that I'm thinking to GroupType or separated tables AccountGroup and CompanyGroup

Above all, based on your current requirements, following my suggestions:

https://dbdiagram.io/d/5d6f2704ced98361d6de2d0d

a user can be part of multiple accounts and have different roles (admin or not)

  Tables: User (Id), Account (Id) and UserAccount (UserId, AccountId)

an account can have one or many groups

  Tables: Group (Id) and AccountGroup (AccountId, GroupId)

each group can have one or many members / a member can be part of many groups

  Tables: Member (Id) and MemberGroup (MemberId, GroupId)

a member can be either an user or a legal entity (company)

  Tables: Company (Id, MemberId), User (add MemberId)

if it's a company, then the company is represented by a user / a user can represent 0,1 or many companies in different groups

  Tables: Company (add RepresentUserId), Group (add RepresentUserId)

Please noted that this is for an idea, not a fullfill solution. I hope this help you much.


Configuring Oracle Data Guard or GoldenGate Standby DB replication while restricting DELETE DMLs How can I use Data Guard or GoldenGate to create a slave database where the master does not propagate delete queries to the slave database?

I want to have a replication/slave database to have all the records of the master but when a delete query is executed on the master database, it must not propagate to the slave database.

I'm using Oracle 12c

How can I achieve this requirement?

You can not do this with a physical standby, you will need some kind of logical replication.

With a logical standby, you can skip DMLs, but that would skip all DMLs.

With Oracle Goldengate, you can easily skip deletes, as you can specify the IGNOREDELETES option: IGNOREDELETES

Ok, now let's assume you have configured your system to ignore deletes. Imagine the following situation:

insert into mytable (unique_column, other_column) values (1, 1);
commit;

delete from mytable where unique_column = 1;
commit;

insert into mytable (unique_column, other_column) values (1, 2);
commit;

The above will break your replication because of the unique constraint violation. If you ignore the error, you will not have fresh data in the destination database.

It sounds to me you want to build a history table, but there is more to that than just ignoring delete operations, have a look at the below support note:

Oracle GoldenGate Best Practices: Creating History Tables (Doc ID 1314698.1)


SQL Server version numbers, when related to hotfixes, are they "inclusive"? I am looking at the list of version numbers from this page: Microsoft SQL Server Version List, and here's an excerpt:

9.00.3182   2005.90.3182.0  940128 FIX: You receive error 8623 when you run a complex query in SQL Server 2005  August 3, 2007
9.00.3179   2005.90.3179.0  938243 FIX: Error message when you run a full-text query against a catalog in SQL Server 2005: "The execution of a full-text query failed. The content index is corrupt."   July 30, 2007
9.00.3178   2005.90.3178.0  938086 FIX: A SQL Server Agent job fails when you run the SQL Server Agent job in the context of a proxy account in SQL Server 2005 August 22, 2007

(note, the question is not about what I should install for SQL Server 2005, it's about that list, and the hotfix version numbers)

Since all of these 3 are hotfixes, if my SQL Server reports version 9.00.3182 (the topmost, latest hotfix, of the 3 I extracted), does that imply that the previous 2 has been installed as well?

I see that a lot of later hotfixes or patches are named "Cumulative update package". Is that just a naming convention or was the earlier ones that aren't named like that individual hotfixes?

Concrete question example. With the above 3 hotfixes, and SQL Server reporting version number 9.00.3182, which of the 3 scenarios below is the most correct one:

All three hotfixes have been applied.
Hotfix 9.00.3182 has been applied, but definitely not the other 2.
Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.

The reason I am interested in this is that I'm creating a class library for .NET for reporting what is installed on the SQL Server, since there isn't a single week where someone doesn't ask how to check for a specific .NET version, Windows version, or SQL Server version on Stack Overflow. I thought I at least, for my own part, that I should do enough research to be able to build a class library that can answer it, and thus have a definite answer to give for those questions.

But from the list on that page, given a hotfix KB940545, which SQL Server will report as 9.00.3186, my question is thus if the version number is above that, can I report that KB940545 has been installed (either specifically, or as part of later hotfixes/patches), or is it simply not possible to know this information?

Unless you have access to the log files for every cumulative update install, the only thing you would know is #3:

  Hotfix 9.00.3182 has definitely been applied, there simply isn't enough information available to determine if the other 2 have been applied or not.

Cumulative updates are, well, cumulative. So are the builds within a CU branch. So 9.00.3182 will contain the fixes from 3179, 3178, 3165, 3122, 3068, etc. Microsoft used to release certain critical fixes individually, but now they have almost completely stopped doing that, opting for a ~60 day Cumulative Update delivery model (security fixes excepted).

The challenge comes when you pass a service pack boundary. There are cases where a fix is released in a cumulative update right before a service pack is released. The service pack code path was frozen at least 60 days earlier (since SPs go through more rigorous testing/regression etc). So, the service pack - even though released later - is missing fixes that won't be in that branch until the first cumulative update for that service pack. This is why you will often see a CU released out of band, almost immediately after a service pack is released.

Then, of course, there is the situation where multiple branches are being maintained, and multiple CUs come out for the lower service pack. There will be fixes there which weren't even known issues when the newer service pack was released.

This means that in many cases a newer build (say, 2005 SP4, 9.00.5000) is missing fixes from cumulative updates for SP3 (say, CU15, which was released several months after SP4). All of those fixes are usually also available in subsequent CUs for the later service pack, but just looking at version number alone wouldn't really tell you that unless you knew which fixes you are trying cross-reference. You'd need a database for that, not a numeric/string compare.

You're kind of playing with fire here, anyway. You're running SQL Server 2005, and not even on the most recent service pack. Unless you have gone through extended support, you are completely on your own. In fact 2008 and 2008 R2 both hit end of mainstream support in about 60 days.

My philosophy is to always be on the absolute latest service pack for any version I am running, and unless there is something prohibitive, always be on the latest CU for that service pack as well. So, rather than worry about whether the fixes for 3179 or 3178 were installed independently, I'd focus more on getting onto a more modern build (SP4 + MS12-070, putting you at 9.00.5324), or a more modern version (and one that isn't about to sunset).


Improving lots of Left Joins ( SQL Server 2008 - procedure ) This query is running with 8 minutes to complete.

I'm trying to help the programmer with this task.

I've created indexes and stats, update_stats and rebuild indexes but none of this is helping us.

What do you guys do, when you face a query like this??

declare @CodSegmentacao int,
        @CodOndaAtu int,
        @CodOndaAnt int,
        @CodJobTarget int

set @CodSegmentacao = 9
set @CodOndaAtu = 55
set @CodOndaAnt = 52
set @CodJobTarget = 10771

select
    TA.Ordem, TA.DescJobRel, TA.CodJob as CodJobAtu,
    isnull(TB.CodJob, 0) as CodJobAnt,
    isnull(TC.Quant, 0) as AmostraAtu,
    isnull(TD.Quant, 0) as AmostraAnt,
    isnull(TE.Perc, 0) as TGWTotalAtu,
    isnull(TF.Perc, 0) as TGWTotalAnt,
    isnull(TG.Perc, 0) as TGWOnlyAtu,
    isnull(TH.Perc, 0) as TGWOnlyAnt,
    isnull(TI.Perc, 0) as TGWDesigAtu,
    isnull(TJ.Perc, 0) as TGWDesigAnt,
    isnull(TK.Perc, 0) as DesvioAtu,
    isnull(TL.Perc, 0) as DesvioAnt,
    isnull(TM.Perc, 0) as DesvioSDAtu,
    isnull(TN.Perc, 0) as DesvioSDAnt,
    isnull(TP.Perc, 0) as DesvioDAtu,
    isnull(TQ.Perc, 0) as DesvioDAnt
from
    (select
         T2.CodJobRel, T2.DescJobRel,
         case when T1.FlagTotal = 1 then 0 else case when T4.CodJob = @CodJobTarget then 1 else 2 end end as Ordem,
         T4.CodJob
     from
         tblSegmentacaoJobRel T1 with (nolock)
     inner join
         tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
     inner join
         tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
     inner join
         tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
     where
         T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAtu) TA
left join
   (select
        T2.CodJobRel, T2.DescJobRel, T4.CodJob
    from
        tblSegmentacaoJobRel T1 with (nolock)
    inner join
        tblJobRel T2 with (nolock) on T1.CodJobRel = T2.CodJobRel
    inner join
        tblJobRelJob T3 with (nolock) on T2.CodJobRel = T3.CodJobRel
    inner join
        tblJob T4 with (nolock) on T3.CodJob = T4.CodJob
    where
        T1.CodSegmentacao = @CodSegmentacao and T4.CodOnda = @CodOndaAnt) TB on TA.CodJobRel = TB.CodJobRel
left join
    tblConsolidadoJob TC with (nolock) on TA.CodJob = TC.CodJob and TC.TipoCons = 'Amostra'
left join
    tblConsolidadoJob TD with (nolock) on TB.CodJob = TD.CodJob and TD.TipoCons = 'Amostra'
left join
    tblConsolidadoJob TE with (nolock) on TA.CodJob = TE.CodJob and TE.TipoCons = 'TGWTotal'
left join
    tblConsolidadoJob TF with (nolock) on TB.CodJob = TF.CodJob and TF.TipoCons = 'TGWTotal'
left join
    tblConsolidadoJob TG with (nolock) on TA.CodJob = TG.CodJob and TG.TipoCons = 'TGWOnly'
left join
    tblConsolidadoJob TH with (nolock) on TB.CodJob = TH.CodJob and TH.TipoCons = 'TGWOnly'
left join
    tblConsolidadoJob TI with (nolock) on TA.CodJob = TI.CodJob and TI.TipoCons = 'TGWDesig'
left join
    tblConsolidadoJob TJ with (nolock) on TB.CodJob = TJ.CodJob and TJ.TipoCons = 'TGWDesig'
left join
    tblConsolidadoJob TK with (nolock) on TA.CodJob = TK.CodJob and TK.TipoCons = 'Desvio'
left join
    tblConsolidadoJob TL with (nolock) on TB.CodJob = TL.CodJob and TL.TipoCons = 'Desvio'
left join
    tblConsolidadoJob TM with (nolock) on TA.CodJob = TM.CodJob and TM.TipoCons = 'DesvioSD'
left join
    tblConsolidadoJob TN with (nolock) on TB.CodJob = TN.CodJob and TN.TipoCons = 'DesvioSD'
left join
    tblConsolidadoJob TP with (nolock) on TA.CodJob = TP.CodJob and TP.TipoCons = 'DesvioD'
left join
    tblConsolidadoJob TQ with (nolock) on TB.CodJob = TQ.CodJob and TQ.TipoCons = 'DesvioD'
order by
    TA.Ordem, TA.DescJobRel

Since now, thanks you very much.

I was looking at the execution plan. I didn't see problems there.

If it's stored procedure, perhaps PIVOT tblConsolidadoJob table and insert results into hash table and then use very same hash table on a left join?

Something like this:

SELECT *
INTO #temporary
FROM (
    SELECT CodJob, Quant, TipoCons, Perc
    FROM tblConsolidadoJob
    ) AS SourceTable
PIVOT
(
    MAX(Perc)
    FOR TipoCoins IN ([Amostra],[TGWTotal],[TGWOnly],[TGWDesig],[Desvio],[DesvioSD],[DesvioD])
) AS Pivoted

and then use #temporary table on your left join instead of tblConsolidadoJob. I might be wrong tho. But this could be worth trying?


postgresql operator negator usage I'm wondering if the negator clause of CREATE OPERATOR specifies how the negator can be interpreted, or how it will be interpreted. For example, if I have an operator >= and I set the negator to the operator <, will NOT(x >= y) always be interpreted as x < y, or does it just mean that it will be interpreted that way when it's advantageous.

I'm asking because I'd like to be able to be able to use NOT(x op y) in my queries as something other than the complement of x op y.  Is this possible and/or would it be playing with fire?

Concretely, I have a composite type with a subcolumn match_id and a subcolumn second.  I want to be able to use, for example, NOT(x >= y) to mean x.match_id = y.match_id AND x.second < y.second, which would be encoded in the operator <.

Starting with PostgreSQL documentation about Negators:

36.13.2. NEGATOR
The NEGATOR clause, if provided, names an operator that is the negator of the operator being defined. We say that operator A is the negator of operator B if both return Boolean results and (x A y) equals NOT (x B y) for all possible inputs x, y. Notice that B is also the negator of A. For example, < and >= are a negator pair for most data types. An operator can never validly be its own negator.
[...]

The way the NEGATOR is supposed to behave does not match your intended usage. I guess this could result in some execution plans not giving the intended results.
Again, according to the docs:

Providing a negator is very helpful to the query optimizer since it allows expressions like NOT (x = y) to be simplified into x <> y. This comes up more often than you might think, because NOT operations can be inserted as a consequence of other rearrangements.

The way the text has been written, I understand that this implies that the planner might simpify a clause like NOT(a op b) into a negator_op b (or even the other way around, as commented out by @craig-ringer); but it also might not do it. The planner is allowed to make the simplification, but not coerced to or has an obligation to perform the conversion.
I suggest that, if you need different kind of comparisons, that you create other opeartors  (let's say <% >% <=% >=% or something like that), with corresponding negators if they make sense.


How does an index speed up insertion in a table How does index speed up insertion in a table?

I have an idea how it happens for retrieving the rows and selection of data, but how does it helps in insertion?

CREATE TABLE  persons(
id NUMBER ,
first_name VARCHAR2(50) NOT NULL,
PRIMARY KEY(id)
);

and if create an index on first_name

create index name_idx on persons(first_name)

how does it speed up the query below?

INSERT INTO suppliers (id,first_name) VALUES (01, 'SAI');
INSERT INTO suppliers (id,first_name) VALUES (02, 'CHRI');
INSERT INTO suppliers (id,first_name) VALUES (03, 'MASSEY');
INSERT INTO suppliers (id,first_name) VALUES (04, 'FREDDIE');
INSERT INTO suppliers (id,first_name) VALUES (05, 'ROGER');

An index like that with an INSERT like this only makes the INSERT slower.

An index could help if you performed an INSERT .. SELECT .. FROM ... and the index would speed up the SELECT part.


Fixing MySQL errors recorded in error logs Below is the extract of the most common lines of error being recorded in error logs on my PC:

110905 16:16:54 [Note] Plugin 'FEDERATED' is disabled.
110905 16:16:54 InnoDB: The InnoDB memory heap is disabled
110905 16:16:54 InnoDB: Mutexes and rw_locks use Windows interlocked functions
110905 16:16:54 InnoDB: Compressed tables use zlib 1.2.3
110905 16:16:55 InnoDB: Initializing buffer pool, size = 500.0M
110905 16:16:55 InnoDB: Completed initialization of buffer pool
110905 16:16:55 InnoDB: highest supported file format is Barracuda.
110905 16:16:57  InnoDB: Waiting for the background threads to start
110905 16:16:58 InnoDB: 1.1.8 started; log sequence number 14539513
110905 16:16:59 [Note] Event Scheduler: Loaded 0 events
110905 16:16:59 [Note] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: ready for connections.
Version: '5.5.15-log'  socket: ''  port: 3306  MySQL Community Server (GPL)
110905 16:46:29 [Warning] Aborted connection 1 to db: 'stockist' user: 'root' host: 'localhost' (Got an error reading communication packets)
110905 16:50:42 [Warning] Aborted connection 2 to db: 'dummy_db' user: 'root' host: 'localhost' (Got an error reading communication packets)

How to fix these issues? I am using MySQL Community Server 5.5.14. It is installed on a PC with 3 GB RAM on NTFS partion. OS is Windowx XP SP2.

Below is the my.ini file:

# MySQL Server Instance Configuration File
# ----------------------------------------------------------------------

# CLIENT SECTION

[client]

port=3306

[mysql]

default-character-set=latin1

# SERVER SECTION
[mysqld]
log-bin=E:/CRITICAL_MYSQL_LOGGING/AUTOMATED_BINARY_LOGS/MySqlBinLog

# The TCP/IP Port the MySQL Server will listen on
port=3306

#Path to installation directory. All paths are usually resolved relative to this.
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"

#Path to the database root
datadir="C:/Documents and Settings/All Users/Application Data/MySQL/MySQL Server 5.5/Data/"

# The default character set that will be used when a new schema or table is
# created and no character set is defined
character-set-server=latin1

# The default storage engine that will be used when create new tables when
default-storage-engine=INNODB

# Set the SQL mode to strict
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

# The maximum amount of concurrent sessions the MySQL server will
# allow.
max_connections=02

# Query cache is used to cache SELECT results and later return them
# without actual executing the same query once again.
query_cache_size=15M

# The number of open tables for all threads.
table_cache=256

# Maximum size for internal (in-memory) temporary tables.
tmp_table_size=9M

# How many threads we should keep in a cache for reuse. When a client
# disconnects
thread_cache_size=8

#*** INNODB Specific options ***

innodb_additional_mem_pool_size=20M

innodb_flush_log_at_trx_commit=1

innodb_log_buffer_size=1M

innodb_buffer_pool_size=500M

innodb_log_file_size=10M

innodb_thread_concurrency=8

log-queries-not-using-indexes

log-warnings

log=E:/CRITICAL_MYSQL_LOGGING/QUERY_LOGS/QueryLog

log-error=E:/CRITICAL_MYSQL_LOGGING/ERROR_LOGS/ErrorLog

log-slow-queries=E:/CRITICAL_MYSQL_LOGGING/SLOW_QUERY_LOGS/SlowQueryLog

log-bin-index=E:/CRITICAL_MYSQL_LOGGING/BinLogIndexFile

long_query_time=2

One of the silent killers of MySQL Connections is the MySQL Packet.

According to the MySQL Documentation

You can also get these errors if you send a query to the server that is incorrect or too large. If mysqld receives a packet that is too large or out of order, it assumes that something has gone wrong with the client and closes the connection. If you need big queries (for example, if you are working with big BLOB columns), you can increase the query limit by setting the server's max_allowed_packet variable, which has a default value of 1MB. You may also need to increase the maximum packet size on the client end. More information on setting the packet size is given in Section C.5.2.10, “Packet too large”.
An INSERT or REPLACE statement that inserts a great many rows can also cause these sorts of errors. Either one of these statements sends a single request to the server irrespective of the number of rows to be inserted; thus, you can often avoid the error by reducing the number of rows sent per INSERT or REPLACE.

RECOMMENDATION

Try raising the max_allowed_packet to a much larger number, since the default is 1M. I would suggest about 10 times the largest TEXT or BLOB field you have in your current dataset.

You can add it to /etc/my.cnf or my.ini

[mysqld]
max_allowed_packet=128M


Join table on some conditions I've got a table called activities. And a table messages. Activity has many messages, and one message has one activity.

This are the message columns :

id
sender_id
receiver_id
body
activity_id

I'm looking to get all the activities who have at least one message from both directions of message.

Here is what I mean, message id 1 :

id|sender_id|receiver_id|body   |activity_id
1 |20       |44         |hi     | 55

So for activity 55 there is only one directional messaging, if you take activity messages below :

id|sender_id|receiver_id|body     |activity_id
1 |20       |44         |hi       | 55
3 |44       |20         |hi there | 55
4 |44       |20         |who're u | 55

So above messages for activity 55 are "multi directional", meaning there are messages from both sides. In this case there are more than one message from 44 to 22 but thats ok, as long as there is at least one message from 44 to 22 and at least one message from 22 to 44 I want to get that activity_id.

I'd like to get all the activity_ids which have at least one or more message exchange from both sides.

I was thinking to do it something like this :

select distinct(activity_id) from messages
where sender_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
) or receiver_id in (
    select receiver_id from activities
    inner join messages on messages.activity_id = activities.id
    union
    select sender_id from activities
    inner join messages on messages.activity_id = activities.id
)

It's not really working at the moment, can someone point me to the right direction?

You don't need to include the table activities in the query at all. Only messages:
SELECT DISTINCT activity_id
FROM   messages m
WHERE  sender_id <= receiver_id   -- reduce rows
AND    EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    );

That's much like @dezso's query, just on the right table and with an additional feature.
The condition sender_id <= receiver_idis not strictly needed, but excludes half of the rows right away, increasing performance.
Index
The perfect index for the case should be a multicolumn index on all three columns in the WHERE condition of the EXISTS semi-join:
CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id);

Two squeeze out the last drop of read performance, you could make that a highly specialized partial index:
CREATE INDEX foo_idx ON messages (activity_id, sender_id, receiver_id)
WHERE receiver_id <= sender_id;

And adapt the query accordingly:
...
WHERE  EXISTS (
    SELECT 1
    FROM   messages
    WHERE  activity_id = m.activity_id
    AND    sender_id   = m.receiver_id
    AND    receiver_id = m.sender_id
    AND   receiver_id <= sender_id  -- redundant, just for partial index
    );
The added condition lets Postgres know, it can use the partial index.
This will only be moderately faster than the plain solution on top. Only worth the hassle if you need to optimize performance.
Alternatives
If there are many messages per activity, in particular also multiple message exchanges between different pairs, then this query will be faster:
SELECT activity_id
FROM   activities a
WHERE  EXISTS (
   SELECT 1
   FROM   messages m
   WHERE  m.activity_id = a.activity_id
   AND    sender_id <= receiver_id
   AND    EXISTS (
      SELECT 1
      FROM   messages
      WHERE  activity_id = m.activity_id
      AND    sender_id   = m.receiver_id
      AND    receiver_id = m.sender_id
      AND    receiver_id <= sender_id -- ?
      )
   );

Similar optimization possible as above. But you'd need another index:
CREATE INDEX foo2_idx ON messages (activity_id, sender_id, receiver_id)
WHERE  sender_id <= receiver_id;

In this case the simple index might prevail (instead of reading from two smaller indexes). Again, test.
And while you are retrieving the whole table I would also compare performance with INTERSECT:
SELECT DISTINCT activity_id
FROM (
   SELECT DISTINCT activity_id, sender_id, receiver_id
   FROM   messages m
   WHERE  sender_id <= receiver_id
   INTERSECT
   SELECT DISTINCT activity_id, receiver_id, sender_id
   FROM   messages m
   WHERE  receiver_id <= sender_id
   ) sub;

Might perform well, even without index. I would experiment with indexes and possibly GROUP BY instead of DISTINCT, or drop the DISTINCT in the subquery (depending on data distribution) to find the fastest variant.
All of these queries count a message to self as "exchange", since those qualify for both directions. If there are no messages with sender_id = receiver_id, replace all the <= with < ...


Master/Slave desyncronisation Some manual operations (writes) have been made on SLAVE instead of MASTER resulting in the replication being stopped.

Could not execute Delete_rows_v1 event on table xxx.newsletter;
Can't find record in 'newsletter', Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND;
the event's master log master-bin.000061, end_log_pos 30109220

I've manually recreated the row which has been deleted on the SLAVE.

Is there a way to easily "resume" it without stopping the master, exporting the DB, and starting the whole process again please ? (This happened months ago so the SLAVE is really out of date)

Running MariaDB 10.x
Thanks.

Assuming you have corrected the slave drift, you can just "start slave" again, but

You may need to skip some transactions, if they have been manually applied to the slave- you can do that with SET GLOBAL sql_slave_skip_counter
You probably have other differences between the master and the slave, because of the operations you mentioned or because of the skipping of transactions. In that case, using pt-table-checksum and pt-table-sync can help you resync the slave without having to reload the full contents.


Select MIN value from multiple columns I have a very basic database for keeping track of point scores for 6 different events. Each event has 4 events within it.

My Current SQL query is very basic, and looks very similar to what is below;

Select Name,
       SUM(ISNULL(p.e1_e1_points, 0)+ISNULL(p.e1_e2_points, 0)+ISNULL(p.e1_e3_points, 0)+ISNULL(p.e1_e4_points, 0)) AS Event1,
       SUM(ISNULL(p.e2_e1_points, 0)+ISNULL(p.e2_e2_points, 0)+ISNULL(p.e2_e3_points, 0)+ISNULL(p.e2_e4_points, 0)) AS Event2,
       SUM(ISNULL(p.e3_e1_points, 0)+ISNULL(p.e3_e2_points, 0)+ISNULL(p.e3_e3_points, 0)+ISNULL(p.e3_e4_points, 0)) AS Event3,
       SUM(ISNULL(p.e4_e1_points, 0)+ISNULL(p.e4_e2_points, 0)+ISNULL(p.e4_e3_points, 0)+ISNULL(p.e4_e4_points, 0)) AS Event4,
       SUM(ISNULL(p.e5_e1_points, 0)+ISNULL(p.e5_e2_points, 0)+ISNULL(p.e5_e3_points, 0)+ISNULL(p.e5_e4_points, 0)) AS Event5,
       SUM(ISNULL(p.e6_e1_points, 0)+ISNULL(p.e6_e2_points, 0)+ISNULL(p.e6_e3_points, 0)+ISNULL(p.e6_e4_points, 0)) AS Event6
       from points

I need to be able to add an additional column, which is the lowest value from columns (being Event1, Event2, Event3, etc).

Basically, this is about finding a row-wise minimum.

There is an elegant inline solution in the most upvoted answer to this Stack Overflow question:

SQL MAX of multiple columns?

According to that solution, if you have a table T of this kind:

C1  C2  C3
--  --  --
…   …   …
…   …   …

you can find a row-wise minimum like this:

SELECT
  C1,
  C2,
  C3,
  (
    SELECT MIN(C)
    FROM (VALUES (C1), (C2), (C3) AS v (C)
  ) AS MinC
FROM
  T
;

Basically you are arranging the values of C1, C2, C3 as a column and are applying a normal (column-wise) aggregate function to it to find the minimum.

Now in your case the C1, C2 etc. are expressions. Usually that is fine, you can use the VALUES row constructor with expressions. But in this case each expression already contains an aggregate function (SUM()), which prevents us from applying the method directly (VALUES expressions must not use aggregate functions).

However, that issue is easily resolved. You can use your current query as a derived table or a CTE and apply the method at the outer level, where the expressions will be just references, like this:

SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  (
    SELECT
      ... /* your current query */
  ) AS derived
;

And here is an identical solution but using a CTE:

WITH cte AS
  (
    SELECT
      ... /* your current query */
  )
SELECT
  Name,
  Event1,
  Event2,
  Event3,
  Event4,
  Event5,
  Event6,
  (
    SELECT MIN(Event)
    FROM (VALUES (Event1), (Event2), (Event3), (Event4), (Event5), (Event6)) AS v (Event)
  ) AS MinEvent
FROM
  cte
;


Select row if column data was not selected How can I selected rows from a table when a column data is different.

Table: id(pk), name, title, book_id, date

I want to get all the rows where book_id is uniq.

Data example:

test1, test2, 1, 2015-01-01
test2, test4, 1, 2015-01-02
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04
test5, test2, 5, 2015-01-05

Expected output:

test1, test2, 1, 2015-01-01
test2, test3, 1, 2015-01-02
test2, test2, 4, 2015-01-03
test4, test2, 5, 2015-01-04

This can easily be done using Postgres' distinct on operator:

select distinct on (book_id) name, title, bookd_id, date
from the_table
order by book_id, date;

This will pick the first row for each book_id based on the date column.


Does AE certificate include both master and encryption keys together? After setting up Always Encrypted, I can see a certificate in my Current User

when selecting
select * from sys.column_master_keys

under the key_path I see this location, but there is no location specified for encryption keys. So is just this certificate export enough? Does it contain both master and encryption keys together in the same certificate?
Thanks

After setting up Always Encrypted, I can see a certificate in my Current User

Correct, you've stated that the Column Master Key (CMK) will be created/stored there. Most likely this was done via the GUI to generate a new key and save it there. There are other options as well for the CMK.

  [...] under the key_path I see this location, but there is no location specified for encryption keys.

Correct, that was the location used when executing the metadata update to SQL Server for where the CMK exists. Since SQL Server doesn't go out and touch that Certificate (which shouldn't be in the server) it only knows what to send back to the client driver as metadata. The certificate holds an asymmetric key pair that is used to encrypt and decrypt the Column Encryption Key (CEK) which is stored inside of SQL Server in the same database.

  So is just this certificate export enough?

Assuming AE is setup correctly, the client only needs that certificate, yes. There are obviously database level permissions that are required to get said metadata but overall the process is moot if you don't have a copy of the CMK.

  Does it contain both master and encryption keys together in the same certificate?

No, as I stated, there is the CMK which is the certificate you've created and then there is the CEK which does the actual encryption and decryption of the data.

You'll want to go back and read a bit more about Always Encrypted and how it works as this is all explained in the documentation.


MongoDB - multi wirte data to same document I have a question, and its about how mongo will handle if 2 user at same time make a update request to update the data with the $set function like this

Update reuqest 1:

{
   $set: {
      'field1' : 'new data'
   }
}

Update request 2:

{
   $set: {
      'field2' : 'secound data'
   }
}

if i using mysql, its will update each field without eny issue, will mongodb do the same?

the next case can be i have 3 users and there will be a update like this

Update request 1:

{
   $set: {
      'field1' : 'data'
   }
}

Update request 2:

{
   $set: {
      'field1' : 'data',
      'field2' : 'data'
   }
}

Update request 3:

{
   $set: {
      'field2' : 'data'
   }
}

in this case if i do the same in MySQL its will request 2 overwirte the field1 from request 1 and the request 3 will overwirte the field2 data in request 2.

and i expect mongo work at the same way? or can i make a version control over a document so i can se changes over time?

i need to prevent this case, becures i'm building a huges API layer where evey thing use Mongo and i want to update product catalog on cross from platform to platform.

hope eny how can help me to understand this part in MongoDB

With (multiple) updates to (single) document, first come, first served. So, that client who is first (in time) get lock to that document, others who come later, will get "ticket" (queue number) and will wait their turn to update that document.

So, update 2 will override field1 value and add field2, update 3 then will override field2 value..


Diskspace usage of DB I have a production database that's consuming a significant amount more disk space than it should be.

Using sp_spaceused (only the interesting tables), I get.

Table Name    Reserved (KBs)    Data (KBs)    Index Size (KBs)
----------    --------------    ----------    ----------------
E              149600             142992         1136
H              184968             12104        162272
M               94104             29512         62480
T               25616             13544          9464
MH              16072             10640          5208
U             5399312           2984184       2414376
Total         5869672           3192976       2654936

However, the on disk size is a whopping 16 GB database and 6 GB log file.

The culprit is mainly Table U.  Most of the data is very static with just lots of UPDATE to existing rows.  Table U is the ony with loads of data being added constantly, and being archived weekly.

Table U's definition is,

[ID]      INT                         NOT NULL,
[RD]      DATETIME                    NOT NULL,
[UN]      CHAR (7)                    NOT NULL,
[A]       BIT                         NOT NULL,
[C]       BIT                         NOT NULL,
[S]       DATETIME                    NOT NULL,
[rowguid] UNIQUEIDENTIFIER ROWGUIDCOL NOT NULL

It receives about 5 million rows per week, and archives another 5 million rows per week.

It has not been partitioned / placed into separate file groups.

The system has been arround for approximatley 6 months, and I don't believe our DBA team have ever shrunk it / defragged it!

Questions

Why is SQL using so much more disk space than sp_spaceused says?
Is the churn responsible for most of this disk space issue?
How should I use (SQL commands please) partitions and file groups to better optimise this churn?

Assuming that you did your homework correctly and these tables listed are indeed the way space is used (ie. there is no other table consuming the space) then what you have now is state and you ask about history. The state of the database now is a 16GB file with ~6Gb accounted for and 10Gb unexplained. How did it end up in this state? we cannot answer. There are a number of avenues that could lead up to this:

the database could had been created with a 16Gb data file size.
there could had been one or more big tables once that now are dropped
some operation needed temporary space and could had grown the database to 16Gb size and leave it so

You could consult the history captured in the default administrative trace and in the ERRORLOG and see if some of the info captured can shed light on the mystery.

If I'd venture a guess I would say you have a maintenance task of some sort, perhaps a table rebuild, that runs periodically. Rebuild operations require a great amount of data space as all the data exist for a while duplicated (old index being rebuild and new index being created) and, when the default OFFLINE option is used, they require a similarly great amount of log space as the entire operation is a single transaction. A log of 6Gb, data of approximately 6Gb size and a DB with +10Gb space would point toward that ~6Gb data being rebuild, maybe once manually, or maybe periodically by an index rebuild maintenance task.

Now about your real question: is there a better way to do this? Not an easy one. Such pattern of high churn with large range deletes is indeed very unhealthy. If you can prove that it causes real problems (sorry, but a 16Gb data file is not really a problem, and fragmentation is more often than not a red herring) then there are solutions, the most effective being using a partitioning sliding window for efficient deallocation via switch-out+truncate (The link contains the exact how-to). This is a heavy impact change that would ripple everywhere, including the application code, so do consider all the aspects. See How To Decide if You Should Use Table Partitioning.


Restricting input from two other tables I'm trying to create the table CanTeach but I am getting an error ERROR 1005 (HY000): Can't create table 'mydb'.'CanTeach' (errno: 150 "Foreign key constraint is incorrectly formed"). Ultimately, I'm trying to create a table of "classes that teacher's can teach" verifying they work for the department that offers the class.
CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);
CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);
CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);
CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary>=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);
CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);
CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFor(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);

this runs in MySQL as in Oracle 18c
So you must have somewhere a typo in your code

CREATE TABLE Department (
    Name varchar(255) NOT NULL PRIMARY KEY
);

✓

CREATE TABLE Person (
    SSN CHAR(9) NOT NULL PRIMARY KEY,
    LastName varchar(255),
    FirstName varchar(255)
);

✓

CREATE TABLE Professor (
    SSN CHAR(9),
    PRIMARY KEY(SSN),
    FOREIGN KEY (SSN) REFERENCES Person(SSN)
);

✓

CREATE TABLE WorksFOR (
    SSN CHAR(9),
    Salary float NOT NULL,
    DepName varchar(255),
    CONSTRAINT WorksFor_Salary CHECK (Salary>=0),
    CONSTRAINT WorksFor_pk PRIMARY KEY(SSN,DepName),
    CONSTRAINT WorksFor_ssn_fk FOREIGN KEY(SSN) REFERENCES Professor(SSN),
    CONSTRAINT WorksFor_dep_fk FOREIGN KEY(DepName) REFERENCES Department(Name)
);

✓

CREATE TABLE Course (
    CourseDepartment varchar(255) NOT NULL,
    CourseNumber varchar(8) NOT NULL,
    CONSTRAINT Course_pk PRIMARY KEY(CourseDepartment,CourseNumber),
    FOREIGN KEY(CourseDepartment) REFERENCES Department(Name)
);

✓

CREATE TABLE CanTeach (
    SSN CHAR(9) NOT NULL,
    Department varchar(255) NOT NULL,
    CourseNum varchar(8) NOT NULL,
    CONSTRAINT CanTeach_pk PRIMARY KEY(SSN,Department,CourseNum),
    FOREIGN KEY(SSN,Department) REFERENCES WorksFOR(SSN,DepName),
    FOREIGN KEY(Department,CourseNum) REFERENCES Course(CourseDepartment,CourseNumber)
);

✓

db<>fiddle here


PURGE BINARY LOGS taking time We are observing high latency for some queries, digging further we found that every 5 minutes the queries take long time by examining the slow query logs.

We observed that

PURGE BINARY LOGS TO 'mysql-bin-changelog.041045';

Running every 5 mins and taking more than 1000ms. At the same time queries like

SET timestamp=1492673425; commit;

Also taking time and

SELECT @@session.tx_isolation;

We are using MySQL 5.7.11 on Amazon RDS. Any reason why PURGE BINARY LOGS will take longer and causing the other queries to also stuck at the same time?

This is a rather nasty bug in MySQL 5.7 that I have experienced outside of RDS 2 months back. There is a strange mutexing behavior involving the act of purging binary logs.
My workaround involved manually deleting some of the binary logs in the OS as listed in bin-log.index and executing PURGE BINARY LOGS BEFORE CURDATE() + INTERVAL 2 HOUR; (Nightly purge every 2AM). It sped up it up some because it had less files in the OS to examine. When it came to the actual binlogs still in existence, running PURGE BINARY LOGS ... simply dragged. That's why I did manual smoke-and-mirrors stuff.
YOUR ACTUAL SITUATION
In your particular instance, you are using RDS. Since you cannot go to the OS, there is nothing else you can do to the binlogs because max_binlog_size cannot be changed in an RDS Parameter Group. You could change the max_binlog_cache_size if you have large or many transactions that need to cached ahead of its writing to the binlogs, but this may or may not help depending on how frequent you write in your app.
RECOMMENDATION
This issue does not exist in MySQL 5.6.
You may have to migrate your DB back to MySQL 5.6.27 RDS.
EPILOGUE
From my experience, I was victimized by this bug in MySQL 5.7.12. You informed me that the bug was addressed in MySQL 5.7.14. I wish I had upgraded like I wanted. Since your situation came up MySQL 5.7.11, I knew someone besides me noticed this problem. Go for your upgrade to 5.7.16 ASAP. Have a good day !!!


How to check if a column in a query can produce NULL? I am scheming how to implement a type generator for arbitrary SQL queries.
I found that for any query, the simplest way to get the types of every column is to create a temporary table and then inspect that table. However, this tells nothing about which columns can be NULL.
I am interested to find a solution to identifying which columns can be NULL for any arbitrary query with the goal of generating types for that query.
What’s the way to go about it?

Since SQL (with recursive CTEs) is Turing complete, figuring out if a column in the result could be NULL or not is probably as complex as the halting problem and cannot be computed.
So either run the query and examine the result or restrict yourself to very simple queries (but even then the problem will be non-trivial).
My personal recommendation is to give up.


Creating a relationship between a document and a collection in MongoDB I have no idea if this is even possible but I'd like to find out if there's some way to create a relationship between a document and a whole collection. I have a user schema that looks something like this:

var userSchema = new Schema({
    fname: {type: String, required: true},
    lname: {type: String, required: true},
    email: {type: String, required: true, unique: true},
    password: {type:String, required: true}
});

To achieve my desired goal, I'm thinking my schema would need to look a bit like this:

var userSchema = new Schema({
    fname: {type: String, required: true},
    lname: {type: String, required: true},
    email: {type: String, required: true, unique: true},
    password: {type:String, required: true},
    collections: [{type: Schema.Types.ObjectId}] // where ObjectId would be the ID to a collection

});

The problem with this approach is, I have no way to get the ID of a collection. I'm unsure if that's even a thing. Any suggestions? Is this even possible?

Any help is appreciated.

Collections do not have IDs. I would suggest you just use the collection names. You can already dynamically access collections by name - so enumerating stored names in this collections array to access the actual DB collections would be no problem.


First you have to use CTE to find ROW_NUMBER() which will sort record then use LEFT OUTER JOIN to achive your result

-- declare temp table
DECLARE @t1 TABLE (
  StudentID bigint,
  SolutionDate date,
  SolutionTime time,
  SongID int
)

-- insert the data into table
insert into @t1 values (0824616,'2015-09-20','00:07:00',01)
insert into @t1 values (0824616,'2015-09-20','00:05:00',02)
insert into @t1 values (0824616,'2015-09-21','00:07:40',01)
insert into @t1 values (0824616,'2015-09-21','00:10:00',03)
insert into @t1 values (0824616,'2015-09-23','00:04:30',03)
insert into @t1 values (0824616,'2015-09-23','00:11:30',03)

-- use CTE to find ROW_NUMBER()
 ;WITH CTE AS
(   SELECT
*,ROW_NUMBER() OVER(PARTITION BY SongID ORDER BY SongID,SolutionTime) AS ColumnNumber
FROM @t1
)

-- then JOIN between CTE and Temp table
SELECT diSTINCT
t.StudentID,t.SongID
,t1.SolutionTime AS TimeA,t2.SolutionTime AS TimeB,t3.SolutionTime AS TimeC
FROM @t1 t
LEFT OUTER JOIN CTE t1 ON t.SongID=t1.SongID AND t1.ColumnNumber=1
LEFT OUTER JOIN CTE t2 ON t.SongID=t2.SongID AND t2.ColumnNumber=2
LEFT OUTER JOIN CTE t3 ON t.SongID=t3.SongID AND t3.ColumnNumber=3
ORDER BY t.SongID ASC