SQL Server High Availability restore copy_only backups from secondary replica problem I've implemented the Ola Hallengren backup solution across our 12-instance farm with AlwaysOn High Availability. Typical setup of weekly full backups, daily differential backups and 4-hourly transaction log backups.

To leverage performance on the Primary, I've offloaded these backups to the secondary.  This makes the Full backups copy_only on the secondary. In theory the Diff backups shouldn't occur, but the job just runs on the primary with the Diff backups also marked as copy_only.  Log backups run on the Secondary.

Thought I'd better test the restore process.  Restored a copy_only Full backup WITH RESTORE and MOVE to a test database and that was fine.  Deleted the test database and restored the copy_only Full backup WITH NORESTORE and MOVE to a test database followed by a restore of the next copy_only Differential backup. This failed with

"the differential backup cannot be restored because the database has not been restored to correct earlier state."

Looking at sys.database_files it shows differential_base_time of 2017-02-15 and a differential_base_LSN of 467000002068600193.  I do not have this file!

Using RESTORE HEADERONLY my copy_only Full backup has a

  FirstLSN = 743000000530200000,

  LastLSN = 743000000569700000,

  CheckpointLSN = 743000000530200000,   DatabaseBackupLSN =
  467000002068600000

My copy_only Differential backup has

  FirstLSN = 788000000036000000

  LastLSN = 792000000047100000,

  CheckpointLSN = 792000000040600000,   DatabaseBackupLSN =
  467000002068600000

  DifferentialBaseLSN = 467000002068600000

Irrespective of the potential disaster of not having a Full backup covering LSN 467000002068600193 (or 467000002068600000...), I expected to see some connecting chain between LastLSN of my copy_only Full backup and the FirstLSN of my copy_only Differential backup.  This doesn't seem to be there.  Can someone explain the gap in my understanding please?

And can someone recommend my least painful way of getting my backup strategy back in line?

If offloading backups to a secondary replica makes all Full backups copy_only with no ability to capture the DifferentialBaseLSN and does not allow Differential backups at all (seemingly), can someone also explain any significant benefit of such offloading?

First things first, get rid of the differential backup. If you are performing full, copy only backups off of a secondary these will do nothing for you.

The differential backup works to backup changes from a full backup (not copy only). When a normal full backup runs it sets all of the pages in the database as being backed up. Going forward from that point any change to a page gets marked, and then the differential would come along and backup any page marked as changed. As time goes along the differential will get larger and larger. A copy only backup does not reset those flags, so things will just get worse.

At this point decide how you want to proceed with your backup strategy. If you want to continue to take differential backups, then also take full backups on your primary. If you no longer want to take the differentials for the database that are in the AG then set the copy_only flag to N in the Ola script. This will allow the differential job to run and take backups of databases not in the AG, but will ignore those that are in the AG (so long as the backup preference marks a secondary replica as the machine to backup from).

As regards the benefit of offloading backups to a secondary, this can help reduce resource contention on your primary replica, which is where most of your traffic can be. It can also be useful as a way to take database backups in a DR site, where you may have a secondary replica or two.


What is difference between internal and external load balancer listener with respect to azure (IaaS) As per below MSDN blog, I see there are 2 ways we can create load balancer listener .

https://azure.microsoft.com/en-gb/documentation/articles/virtual-machines-windows-classic-ps-sql-int-listener/

But I am NOT finding exact scenario where I need to use them? I would like external applications connect to my SQL server which is highly available (AlwaysOn). which load balancer i should configure?

Article here http://clusteringformeremortals.com/2015/01/01/step-by-step-how-to-configure-a-sql-server-failover-cluster-instance-fci-in-microsoft-azure-iaas-sqlserver-azure-sanless/  says that,

  Once the cluster is configured, you will need to create the internal load balancer(ILB) which will be used for all client access. Clients that connect to SQL Server will need to connect to the ILB instead of connecting directly to the cluster IP address. If you were to try to connect to the cluster directly at this point you would see that you cannot connect to the cluster from any remote system. Even SQL Server Management Studio will not be able to connect to the cluster directly at this point

Update on How to configure it

I am using below script to create internal load balancer and it got succeeded.

       // Define variables
       $ServiceName = "XYZ"
       $AGNodes = "ABC01","ABC02"
       $SubnetName = "MYSUBNETNAME"
       $ILBStaticIP = "10.249.XXX.XXX"
       $ILBName = "AGListenerLB"

       // Create the ILB
       Add-AzureInternalLoadBalancer -InternalLoadBalancerName $ILBName -SubnetName $SubnetName -ServiceName $ServiceName -StaticVNetIPAddress $ILBStaticIP

       // Configure a load balanced endpoint for each node in $AGNodes using ILB
       ForEach ($node in $AGNodes)
       {
           Get-AzureVM -ServiceName $ServiceName -Name $node | Add- AzureEndpoint -Name "ListenerEndpoint" -LBSetName "ListenerEndpointLB" -Protocol  tcp -LocalPort 1433 -PublicPort 1433 -ProbePort 59999 -ProbeProtocol tcp -ProbeIntervalInSeconds 10 -InternalLoadBalancerName $ILBName -DirectServerReturn  $true | Update-AzureVM
       }

Once above script is successfull then we need to run the below script

   # Define variables
   $ClusterNetworkName = "Cluster Network 1" # the cluster network name (Use Get-ClusterNetwork on Windows Server 2012 of higher to find the name)
   $IPResourceName = "DEVAG_10.249.XXX.XXX" # the IP Address resource name
   $ILBIP = “10.249.XXX.XXX” # the IP Address of the Internal Load Balancer (ILB)

     Import-Module FailoverClusters

  If you are using Windows Server 2012 or higher, use the Get-Cluster Resource command. If you are using Windows Server 2008 R2, use the
  cluster res command. Both commands are commented out. Choose the one
  applicable to your environment and remove the # at the beginning of
  the line to convert the comment to an executable line of code.

     Get-ClusterResource $IPResourceName | Set-ClusterParameter -Multiple @{"Address"="$ILBIP";"ProbePort"="59999";"SubnetMask"="255.255.255.255";"Network"="$ClusterNetworkName";"EnableDhcp"=0}
     # cluster res $IPResourceName /priv enabledhcp=0 address=$ILBIP probeport=59999  subnetmask=255.255.255.255

External - Uses the public Virtual IP address of the cloud service that hosts the Virtual Machines.     Use when you need to access the listener from outside the virtual network, including from the internet, i.e. public facing

Internal - Uses Internal Load Balancing (ILB) with a private address for the listener.  Use when you only access the listener from within the same virtual network. This includes site-to-site VPN in hybrid scenarios, i.e. not public facing


How to get count of multiple tables from linked and local servers How can I get records count of multiple tables from linked servers.

I have two servers named production and local. I need to write query to get result as mention in this picture.

I tried

SELECT 'TableA' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables, COUNT(*) AS 'QA' FROM [Database].[schema].[tablename]

How to display records count of linked server?

Use four-part-naming, like this:

SELECT 'TableA' AS Tables
    , COUNT(*) AS 'LinkedServer' FROM [Linked-Server-Name].[Database].[schema].[tablename]
UNION
SELECT 'TableB' AS Tables
    , COUNT(*) AS 'LocalServer' FROM [Database].[schema].[tablename]

Assuming your linked server is named Production, you could use:

SELECT Tables = 'TableA'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableA])
    , Local = (SELECT COUNT(*) AS FROM [Database].[schema].[tableA])

UNION
    , Tables = 'TableB'
    , Production = (SELECT COUNT(*) FROM [Production].[Database].[schema].[tableB])
    , Local = (SELECT COUNT(*) FROM [Database].[schema].[tableB])


In SQL Server and other systems that support ROW_NUMBER...

SELECT *,
    ROW_NUMBER() OVER (
        PARTITION BY GrpID
        ORDER BY PKID
    ) AS Idx
FROM YourTable;


MySQL Workbench not showing stored procedures that I have created I have a simple stored procedure that I have just created.

I have applied this script to my database

This script shows up in the my list of stored procedure

However, when I restart MySQL workbench, its not in the list anymore

Interestingly, the procedure still exists even though it is not shown.

I can call

CALL LoadBatch ('057753') ;

And get results

Whats going on, how can I see what stored procedure I have created?

I have tried clicking "Refresh All"

EDIT

As Vérace has suggested

I have tried calling the LoadBatch command from the commande line

This seems to be unresponsive to my stored procedure.

EDIT

After adding the ; character

I think this stored procedure call worked ok from the command line

Here is the warning

EDIT

I think this error message is important

I've just upgraded my MySQL server version and this problem is fixed.

It must have been an issue related to using MySQL Workbench with an older version of MySQL server.


Rename column in a partitioned table I want to rename a column in a partitioned table.
Will oracle do this for all partitions?
Is there anything special for this change?

Renaming a column is a simple dictionary update. Yes, it will be done for all partitions.

SQL> CREATE TABLE t1 (
       c1   NUMBER,
       c2   VARCHAR2(30),
       c3   NUMBER,
       c4   NUMBER, constraint t1_pk primary key (c1, c4) using index local)
  PARTITION BY LIST (c4) (
       PARTITION p10 VALUES (10),
       PARTITION p20 VALUES (20),
       PARTITION p30 VALUES (30,40));

Table created.

SQL> alter table t1 rename column c2 to c5;

Table altered.

SQL> alter table t1 rename column c4 to c6;

Table altered.

SQL> alter table t1 rename column c1 to c7;

Table altered.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C7                                        NOT NULL NUMBER
 C5                                                 VARCHAR2(30)
 C3                                                 NUMBER
 C6                                        NOT NULL NUMBER


Optimizing a simple query on a large table DB: MySQL 5.5.20 (WampServer, default configuration)
OS: Win 7
HDD: Western Digital 3TB Caviar Green, 3.5", IntelliPower, 64MB, Sata3 (WD30EZRX)
Memory: 8GB
MySQL my.ini: http://pastie.org/private/go9kaxlmlvirati2txbaa

Query in question:

  SELECT name.id AS name_id, name.name, cast_info.id,
  cast_info.role_id, cast_info.movie_id
  FROM cast_info
  LEFT JOIN name ON name.id = cast_info.person_id
  WHERE cast_info.movie_id = 1000000
  ORDER BY cast_info.movie_id ASC

It fetches all people who worked on a given movie. Problem is it can take anywhere from 0.1s to nearly 2.0s. That's too long. When the user needs to run it 10k times they might as well uninstall the application. Even I haven't had the patience to wait for it to finish.

edit: The time it takes to run the query is determined by the number of people who worked on it. Roughly 0.1s for each 10 people.

EXPLAIN:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: cast_info
           type: ref
  possible_keys: idx_mid,mpi
            key: idx_mid
        key_len: 4
            ref: const
           rows: 15
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: name
           type: eq_ref
  possible_keys: PRIMARY,id_name_idx
            key: PRIMARY
        key_len: 4
            ref: imdb.cast_info.person_id
           rows: 1
          Extra:

Tables:

  CREATE TABLE cast_info (
   id int(11) NOT NULL AUTO_INCREMENT,
   person_id int(11) NOT NULL,
   movie_id int(11) NOT NULL,
   person_role_id int(11) DEFAULT NULL,
   note text,
   nr_order int(11) DEFAULT NULL,
   role_id int(11) NOT NULL,
   PRIMARY KEY (id),
   KEY idx_pid (person_id),
   KEY idx_mid (movie_id),
   KEY idx_cid (person_role_id),
   KEY cast_info_role_id_exists (role_id),
   KEY mpi (movie_id,person_id,id)
  ) ENGINE=MyISAM AUTO_INCREMENT=33261692 DEFAULT CHARSET=utf8

  CREATE TABLE name (
   id int(11) NOT NULL AUTO_INCREMENT,
   name varchar(110) NOT NULL,
   imdb_index varchar(12) DEFAULT NULL,
   imdb_id int(11) DEFAULT NULL,
   gender varchar(1) DEFAULT NULL,
   name_pcode_cf varchar(5) DEFAULT NULL,
   name_pcode_nf varchar(5) DEFAULT NULL,
   surname_pcode varchar(5) DEFAULT NULL,
   md5sum varchar(32) DEFAULT NULL,
   PRIMARY KEY (id),
   KEY idx_name (name(6)),
   KEY idx_imdb_id (imdb_id),
   KEY idx_pcodecf (name_pcode_cf),
   KEY idx_pcodenf (name_pcode_nf),
   KEY idx_pcode (surname_pcode),
   KEY idx_md5 (md5sum),
   KEY id_name_idx (id,name)
  ) ENGINE=MyISAM AUTO_INCREMENT=4287972 DEFAULT CHARSET=utf8

Thanks!

edit: MyISAM is used because this is a local database, used by one local application, by one user. Only one query is executed at the same time. Also because IMDbPy takes at least a month to build the database with InnoDB...

edit: Query EXPLAIN after converting to InnoDB:

  *************************** 1. row ***************************
             id: 1
    select_type: SIMPLE
          table: cast_info
           type: ref
  possible_keys: mpi
            key: mpi
        key_len: 3
            ref: const
           rows: 23
          Extra:
  *************************** 2. row ***************************
             id: 1
    select_type: SIMPLE
          table: name
           type: eq_ref
  possible_keys: PRIMARY,id_name_idx
            key: PRIMARY
        key_len: 4
            ref: imdb.cast_info.person_id
           rows: 1
          Extra:

It would run faster with InnoDB,

The PRIMARY KEY is 'clustered'; this makes the lookup into name faster.
A suitable setting of innodb_buffer_pool_size of, say, 5G (for your 8GB machine) would cache a lot of stuff, thereby minimizing the I/O for 10K operations.

You could also shrink the data (and decrease the I/O) by using MEDIUMINT UNSIGNED or SMALLINT UNSIGNED where appropriate.

There are redundant indexes that could (should) be removed; this would speed up the load.  Also name(6) is probably useless.


What caused the database [.mdf file] to grow suddenly I am trying to find the cause which have bloated the mdf of a database from 20 GB to 100 GB.

So far i tried checking the autogrow events to find time, but could not find none using standard reports and even default trace files.

We don't have 3rd party monitoring tools to confirm if any maintenance job like rebuild would have done or any other process.

How can i find what caused the growth on this mdf?

This will find all of the autogrow activities in all of the errorlog files still in active sequence, from my answer here:

DECLARE @path NVARCHAR(260);

SELECT
   @path = REVERSE(SUBSTRING(REVERSE([path]),
   CHARINDEX(CHAR(92), REVERSE([path])), 260)) + N'log.trc'
FROM    sys.traces
WHERE   is_default = 1;

SELECT
   DatabaseName,
   HostName,
   ApplicationName,
   [FileName],
   SPID,
   Duration,
   StartTime,
   EndTime,
   FileType = CASE EventClass WHEN 92 THEN 'Data' ELSE 'Log' END
FROM sys.fn_trace_gettable(@path, DEFAULT)
WHERE EventClass IN (92,93)
-- AND DatabaseName = N'AdventureWorks'
ORDER BY StartTime DESC;

Now, once you've identified a suspect autogrowth event, you can see information like the application name and host name that caused the event. It's possible that you might capture other activity by the same SPID, but you can't rely on this. Just take a look for things that started or ended within an arbitrary window - this looks at 5 minutes before and 5 minutes after, and hard-codes the SPID observed above:

SELECT * FROM sys.fn_trace_gettable(@path, DEFAULT)
  WHERE StartTime >= DATEADD(MINUTE, -5, '2018-03-19 11:41:16.970')
    AND EndTime   <  DATEADD(MINUTE,  5, '2018-03-19 11:41:16.970')
    -- AND TextData IS NOT NULL
    AND SPID = 63;

If you are rolling through 20 errorlog files per day, something is not configured correctly or you are performing way too much of something that is filling those log files with noise. IMHO.


Cursors and concurrent users I typically avoid cursor operations like the plaque, however, I have come across my first problem where using a cursor outperforms a query. So I am forced to use it.

I have created an elaborate reporting stored procedure that is used by many users. I use a cursor to iterate a list and insert data into a @TEMP_TABLE, finally selecting the temp table as result set.

The cursor is used as follows:

DECLARE HIGHLIGHTS_REPORT_CURSOR CURSOR LOCAL FOR
SELECT RowID, UserID,GradeID,ClassID,MenuSetID,CurrentSequence FROM @DATA
OPEN HIGHLIGHTS_REPORT_CURSOR
FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID,@UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
WHILE(@@FETCH_STATUS=0)BEGIN
    ...
    FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO @RowID, @UserID,@GradeID,@ClassID,@MenuSetID,@CurrentSequence
END
CLOSE HIGHLIGHTS_REPORT_CURSOR
DEALLOCATE HIGHLIGHTS_REPORT_CURSOR

So all is well, the report moved through QA and no problems until user based ramped up now I am getting exceptions logs sent to me sporadically with the following error:

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Stack Trace :    at System.Data.SqlClient.SqlConnection.OnError(SqlExceptionexception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)

I can state with 100% confidence there is not another cursor with the same name. There are no errant CLOSE\DEALLOCATE CURSOR calls between BEGIN and END.

I learned that SQL Server defaults the "default cursor" database property to GLOBAL during installation. Since the report procedures are the only SP's where cursors are used I have changed the "default cursor" to LOCAL.

That did not help:( Maybe "default cursor" is a setting the requires a restart. Instead of restarting I used the LOCAL attribute of the CURSOR declaration.

This ensures that my cursor is scoped to the SP but still getting the error above???

I am starting to think this is a concurrency issue with multiple connections hitting the SP at the same time. This would explain the sporadic behavior and why this was not caught during QA.

Would it be possible that two connections call the procedure at approximately the same time in which one connection hits the DEALLOCATE HIGHLIGHTS_REPORT_CURSOR just before the second connection hits the FETCH NEXT FROM HIGHLIGHTS_REPORT_CURSOR INTO block??

I assumed using LOCAL ensured each connection would have its copy but it seems to not be the case? Any ideas?

Updated. This is bizarre I got three exception messages stacked into one.
I wonder if a client connection calling the same sp in quick succession cause something similar.

Server Log Reference ID : b91a8f4a-b944-4355-bba3-2855fd126c2b
Message : A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
          A cursor with the name 'HIGHLIGHTS_REPORT_CURSOR' does not exist.
Source : HighlightsReport

Update 2 The part that makes me think this is a user concurrency issue is the fact that these errors always come in groups of two. I receive an exception report with the error noted above from two different users at the same time.

So this was an oddity and I have yet to get down to the nuts and bolts. In a nutshell I enabled MARS on sql server connections a while back to fix an issue I was having with IoC container connections.

This fixed an exception that was similar to "You must close the current command yaddayaddayadda". However, Multiple Active Result Sets seems to lead to a concurrency issue in my case. I actually fixed the original issue that should have been fixed instead of enabling MARS. The fix was to implicitly create a new Data Context per call.


ROWNUM equivalent in Centura SQLBase (aka Gupta) I have a Centura SQLBase (version 7, so it is over 10 years old; now apparently they changed the name back to Gupta) where I need to put a running number into a column while copying data into a temporary table.

The running number has to restart when a grouping code changes (so each group has its own counter), the data has to be ordered by a date column.

This is a conversion task. For testing (UAT) I only have to convert parts of the database (though it isn't really big, so I just want to convert all). For go-live, I will do a final conversion and the database will be disabled. So to be exact: it is a two-times task. Though not mission critical any outage is annoying for the users, as they have to switch to papers and then later key-in (instead of immediately key-in)

In essence I have this data:

employee_no,date,group_code

and I have to generate:

employee_no,date,group_code,group_running_number

The employee_no + date + group_code is the PK.

In Oracle, I would use an analytic function and get it done in one step, but in SQLBase I even cannot find something like "rownum".

I considered using the SYSDBSequence.nextval sequence, but it stayed the same for a single insert (which probably makes sense).

I considered using their ROWID (which contains a unique row number and I know how to pick that out), but I cannot use the ORDER BY in the INSERT-SELECT combo. (When creating views ORDER BY isn't allowed either)

One last resort, if I cannot solve this with SQL, would be using a spreadsheet. There are only about 400,000 rows in total, but it certainly would be rather slow and error prone. On the final conversion day I want to have as few steps as possible.

If I cannot find a rownum equivalent I might try writing a procedure in sqltalk doing RBAR processing.

One way to emulate row numbers in DBMS that do not have window function is using a triangular self join. It's usually not very efficient though.

Given that the primary key is (employee_no, date, group_code):

SELECT
    a.employee_no, a.date, a.group_code,
    COUNT(*) AS group_running_number
FROM
    tableX AS a
  JOIN
    tableX AS b
      ON  a.group_code = b.group_code
      AND ( a.date > b.date
         OR a.date = b.date AND a.employee_no >= b.employee_no
          )
GROUP BY
    a.group_code, a.date, a.employee_no ;

The order used for the assigning of row numbers above is: (date ASC, employee_no ASC)

An index on (group_code, date, employee_no) will improve efficiency I think.


Migrating a SQL Server 2014 AG to a new 2017 AG Our current production server runs on SQL Server 2014 AlwaysOn AG. We are planning to upgrade it to SQL Server 2017 AlwaysOn.

Due to the configuration issues with the present cluster, we can not use the existing cluster.

The production server has 52 databases with a total size of 2 TB. The databases servers an online system, so minimum downtime is our core requirement.

Our initial plan is to go for a side by side approach.

Provision 3 SQL Server 2017
Create new cluster
Logs from the existing 2014 AG Primary server to all the servers in the new 2017 group.

On the switchover day,

Disconnect all applications from the existing server.
Do a final logshipping.
Apply logs to all servers in the new group WITH NORECOVERY, except WITH RECOVERY on Primary_new
Create AG and add all database to AG
Change all applications to point to the new cluster.
Shutdown old cluster.

Is this a good approach? Are there any other approaches?
If I am missing anything, please guide me.

Thanks.

The plan sounds great.
You just need to include a deep UAT (User Acceptance Test) as it is possible that the way that the new version interprets the sentences is a bit different.
For example, every version is going to be more strict with ending each sentence with ;
We made similar migration almost one year ago from 2008 to 2014 and the sentences like:

USE ProductionDB;
INSERT INTO Archive_DB.dbo.Archive_Tbl1 (Field1, Field2)
SELECT Field1, Field2
FROM ProductionDB.dbo.Tbl1;

We had detected several issues because sometimes the DB was ommited and the new version made a different assumption than the old version. Fortunately we could fix all during the UAT.
Good luck !!!


GUID vs Identity on a table performance My question is using a guid(data type unique identifier) versus using identity specification(data type int). Are there any articles where it talks about why you would use one versus another? I'm at a new company using guid and it seems alot slower but maybe its my imagination. Amazingly this is the first place I've seen it used for the uniqueness of a row. Any help or thoughts you have as to why use one versus the other?

There are certain situations where a GUID might be needed, but in general I'd advise against it.   The main reason is that GUIDs are, by their nature, random.  So using them as an identity column essentially means you're storing your rows randomly.  With a typical incremental primary key, the data is sequential.   Table scans are usually more efficient, etc.

In the end, there are tradeoffs with either situation.   Brent Ozar has an older article on the topic:  https://www.brentozar.com/archive/2014/08/generating-identities/


Alter table add column with no default blocks tl;dr

The following query blocks the postgres server when executed

ALTER TABLE "image_data" ADD COLUMN "user_status" varchar(30) NULL

engine version 10.6

# of rows 1.5 mils

Hi,

I have a somewhat odd problem with my production database server (I am unable to reproduce on my local server). As part of the deployment process I wanted to add a new column with a default value of null. When I ran the above query (which was produced by dajngo's migration process), It failed and in the process locked the database and blocked other queries (non-locking selects) from executing. Since it was a production environment, I rolled back after a few minutes (approximately 10). What I find most unusual is that this same query executes within less than a second on my local db replicate (which has the exact same data as the production).

Edit:
When I run this transaction, it blocks the access to the database for all other select queries - which I think (please correct me if I'm wrong) implies that the database is in fact blocked by this transaction.

The problem is not your ALTER TABLE, but other transactions in the database.

You must have some long running transactions that hold any lock on the table (the ACCESS SHARE lock from a SELECT is sufficient to bloch the ACCESS EXCLUSIVE lock of an ALTER TABLE).

Your ALTER TABLE has to wait until this long running transactions are gone, and any later transactions that are trying to access the table have to queue behind the ALTER TABLE.

Close the long running transactions, and the statement will complete in a jiffy.


DB user restricted permissions vs Docker? As far as I know, it's usually considered a good practice when developing websites to store in website config DB credentials of the user without ALTER/DROP permissions (so user may only select, insert, delete rows, but cannot modify DB structure).

But nowadays more and more sites using Docker for deployment and usually DB images like mysql:latest grant all permissions for the given database to the given user.

So my question is - should I manually go to MySQL DB as root during deployment and restrict the permissions or should I just keep it as is?

Given a brutal super user command or DROP TABLE / DROP DATABASE as likely to be the SQL injection commands for the malicious hacker, you may as well defend yourself against those. No point invoking a data recovery procedure pre-emptively.


Quickly updating table from large tsv, retaining a timestamp Consider this tsv file with millions of rows, where uid is a unique identifier of any possible value.

  1  audi    a3      blue
 42  ferrari f60     red
 23  porsche 911     green

It should be used to initially create a MySQL table, and then update it periodically:

imported | uid | type   | model | color
---------+-----+--------+-------+------
monday       1   audi     a3      blue
monday      42   ferrari  f40     red
monday      23   porsche  911     green

Now, observe the following requirements:

If uid exists, the row should be updated, except imported, which should be retained.
If uid doesn't exist, new values should be inserted, and imported set to current timestamp.

Thus, the table might look like this later:

imported | uid | type   | model | color
---------+-----+--------+-------+-------
monday       1   audi     a3      blue
monday      42   ferrari  f60     white
monday      23   porsche  911     purple
thursday     6   bugatti  veyron  yellow

In other words, the procedure should update the table, but keep a distinction of when a row was imported for the first time. Doing this programatically is easy, but will be slow.

So how can I do this using only mysqlimport, load data infile or a similar very fast method? Using --delete and --ignore do not seem to offer a way to achieve the desired effect.

It's 4 statements:

CREATE TABLE Staging ...;  (and a DROP or TRUNCATE)
LOAD DATA ... INTO Staging ...;
INSERT INTO Real (...)  SELECT ... FROM Staging LEFT JOIN Real ...;
UPDATE Real JOIN Staging ON ... SET ...;

The LOAD is very fast for getting the TSV/CSV into a table.  staging is purely for this periodic 'update' process.

The SELECT finds the 'new' rows and feeds them to the INSERT to do the rest add new rows.

The UPDATE is to update all the rows for which you have new data, and to update imported.

If uid is AUTO_INCREMENT, I deliberately avoided INSERT ... ON DUPLICATE KEY UPDATE ... because the latter would 'burn' ids, thereby threatening to eventually overflow the range of uid.

For more discussion, see my blog on ingestion .  That section of the blog details how to augment a Normalization table with one INSERT and one UPDATE.  Your case is very similar.  The rest of the blog focuses on 'continual' ingestion, unlike your 'periodic' ingestion.

If your incremental load is bigger than a million rows, there are other aspects we need to discuss.


SQL Cannot patch the latest CU4 for SQL 2014 SP3 I cannot patch the latest CU4 for SQL 2014 SP3. Checked "view installed program", 4500181 is already installed.

but when I use "Select @@version" against my SQL instance, it still shows "12.0.6259.0" which is SP3-CU3. Has anyone encountered this before and how to fix it?

Thanks,

I am not 100% sure this is the cause of your issue, but this is too long for a comment.

This can occur if SQL Server components are installed similar to the following:

Install SQL Server Database Engine
Install a service pack
Install Reporting Services

At this point, the installer will refuse to install a post-SP CU due to the RTM version of Reporting Services.  Based on my investigation, a certain registry value gets reset to the RTM version when Reporting Services is installed, and this is what the installer is referring to.

The solution is to install the service pack again (SP3, in your case), which will update Reporting Services, and then you will be able to install the post-SP3 CU.

I would assume this would be the case for other components such as Analysis Services, but I have only run into this issue with Reporting Services.


MongoDB + Compass exporting strings and numbers "columns" with value of 1 as "true"(boolean) and 2 or null as "false" to CSV When I export my MongoDB collection to a CSV, the String and Number "column" data types values are converted to "true" for 1 and "false" for 2 and null - how do I retain the values as numbers, strings and integers on export to CSV from Compass?

While I have not been able to completely eliminate the possibility of MongoDB Compass exporting 1's and "true" (bool) or 0's and "false" (bool) nor remove NaN in some "columns" with NULLs mixed with numbers, using "Int32" and "Int64" data types for non-decimal values reduces the number of "false bools", significantly in my MongoDB Compass exports (but not completely - enough to greatly reduce the number of manual/script changes).
The best workaround I have for these quirks of Compass is to switch to Robo 3T (Formerly known as Robomongo) which seems much more consistent and less "auto-magic" happening in the background. It's just a better tool for MongoDB all-around, IMPO.
Link: https://robomongo.org/download
Note: Download just the 'Robo 3T: "The Hobbyist GUI"' and not the Studio 3T package unless you want to spend money for it in a few weeks.

This is just one example of the MANY inconsistent behaviors I've observed while using Compass in Linux with a Gnome GUI instead of the mongoshell or Robo 3T on the same machine.
You can just use the mongoshell commands inside of Robo 3T to perform your CSV exports:
Example: mongoexport --db=PetHotel --collection=pets --type=csv -- fields=_id,name,type,weight --out=data/pets.csv


What are some databases that are schema-full but not relational What are some databases/datastores that are schema-full, but not relational?

In other word I'm after some NoSql databases that are not schema-less.

By schema-full I mean that, like in a traditional relational sql database, all rows/entities/values of a given 'table' should all have the same structure, which is defined upfront and checked by the datastore.

I'm interested in object database but I'm a believer in schemas / strongly typed data...

Thanks

Neo4j is a schema-optional graph database.  You could choose to enforce a full schema and reap the benefits.

MongoDB v3.2 has  validation of structure and data type.


SQL query based on foreign key relationship I have two tables, a Project and an Action:

Project has the following fields:

project_name

Action has the following fields:

action_name
project
status

There is a one-to-many relationship between Action and Project.

I need a query that returns all the Projects that ONLY have actions with statuses greater than 1 OR Projects with no actions at all.

I've never written a query that is dependent on a relationship.  How might this be constructed?

Your solution seems correct. Here's another way that may be more efficient as it doesn't do a grouping. It can't show the MIN(status) though:

SELECT
    "id", "name"
FROM
    "testapp_project" AS p
WHERE
    NOT EXISTS
        ( SELECT *
          FROM "testapp_action" AS a
          WHERE p."id" = a."project_id"
            AND a."status" <= 1
        ) ;


Expanding system tablespace I need to add new data file into the system tablespace because its running out of space. Are there any precautionary measures I should consider since this is a production environment?

I'm assuming you're not using ASM.

Either set the current datafile to AUTOEXTEND:

alter database datafile
   '/full/path/to/system01.dbf'
autoextend on
next 128m
maxsize 8192m;

Or, add a new datafile:

alter tablespace SYSTEM
  add datafile '/full/path/to/system02.dbf'
size 512m;

Obviously, alter the numbers and path as needed.

Adding a datafile impacts your backups. Take a full backup immediately after adding it - this is the only thing you really have to worry about.

4Gb is HUGE for a SYSTEM tablespace. I'd first investigate whether there are some large objects being stored in the tablespace that shouldn't be there (DBA_EXTENTS is your friend).

Also, note that you cannot remove a datafile once it has been added to the SYSTEM tablespace.


MSSQL - Should I change min server memory to 0 I Works as a DBA and "inherited" about 50 instances (all server has about 8GB-24GB of RAM), all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

Should I let SQL server to acquire and release by itself (Min Server
Memory = 0)?
Is it possible to get CPU overload by MSSQL Dynamic Memory
Manager?

all of them are configured Min Server Memory equal to Max Server Memory, and 4GB RAM reserved to OS.

Barring few cases keeping min and max server memory same can be counter productive. In such configuration when low memory flag is signaled SQL Server will not trim down its memory consumption below min server memory value. If memory pressure is severe SQL Server process may be paged to disk causing immense SQL Server slowness. To make this configuration worse if you have min and max server memory same and you on top of that provide LPIM to SQL Server service account this would be tantamount to SQL Server saying "do what you can I am not going to release memory". Such configuration can cause paging of OS process which requested memory or caused severe memory pressure, and even may lead to unexpected shutdown of OS.

Should I let SQL server to acquire and release by itself (Min Server Memory = 0)?

Please leave it to default value. On a system having multiple SQL Server instances and if you are biased to one of the system and want that its memory consumption, once reached, should not go below certain value then you can set min server memory to this threshold. On all other cases let min server memory be default.

Is it possible to get CPU overload by MSSQL Dynamic Memory Manager?

No that I know, never heard or came across such scenario. The memory code works to balance memory across SQL Server process and this not overload CPU. Even when SQL Server is growing its memory consumption you would hardly notice CPU rising because of growth, it may be due to other processes running.


How to inner join some tables and outer join others? I'm certain I'm going to be embarrassed by the eventual answer, but I've been googling around and banging my head against the wall and can't figure it out.

I have four tables described below. I'm trying to get the query that shows all the products related to users (through the user_products table) AND all the categories.

Table definitions

CREATE TEMPORARY TABLE categories (
   id INT PRIMARY KEY NOT NULL
   , cat_name TEXT
 );

 CREATE TEMPORARY TABLE products (
   id INT PRIMARY KEY NOT NULL
   , category_id INT
   , prod_name TEXT
 );

 CREATE TEMPORARY TABLE users (
   id INT PRIMARY KEY NOT NULL
   , user_name TEXT
 );

 CREATE TEMPORARY TABLE user_products (
   id INT PRIMARY KEY NOT NULL
   , product_id INT
   , user_id INT
   , quantity INT
 );

INSERT statements

INSERT INTO categories VALUES (1, 'cat1')
                             ,(2, 'cat2')
                             ,(3, 'cat3')
                             ,(4, 'cat4');

 INSERT INTO products VALUES (1, 1, 'prod1')
                            ,(2, 1, 'prod2')
                            ,(3, 2, 'prod3')
                            ,(4, 3, 'prod4')
                            ,(5, 4, 'prod4');

 INSERT INTO users VALUES (1, 'user1')
                         ,(2, 'user2')
                         ,(3, 'user3');

 INSERT INTO user_products VALUES (1, 1, 1, 4)
                                 ,(2, 2, 1, 3)
                                 ,(3, 3, 1, 3)
                                 ,(4, 2, 2, 3)
                                 ,(5, 2, 2, 2)
                                 ,(6, 5, 2, 2);

I've tried several options, and this certainly feels like an outer join scenario. The most promising query THAT DOES NOT WORK is:

SELECT up.*, p.*, c.*, u.*
FROM user_products AS up
  JOIN users AS u ON u.id = up.user_id
  JOIN products AS p ON p.id = up.product_id
  RIGHT OUTER JOIN categories AS c ON c.id = p.category_id
WHERE u.id = 1 OR u.id IS NULL

Result from above query

 id | product_id | user_id | quantity | id | category_id | prod_name | id | cat_name | id | user_name
----+------------+---------+----------+----+-------------+-----------+----+----------+----+-----------
  1 |          1 |       1 |        4 |  1 |           1 | prod1     |  1 | cat1     |  1 | user1
  2 |          2 |       1 |        3 |  2 |           1 | prod2     |  1 | cat1     |  1 | user1
  3 |          3 |       1 |        3 |  3 |           2 | prod3     |  2 | cat2     |  1 | user1
    |            |         |          |    |             |           |  3 | cat3     |    |

The reason it does not work is that it fails to return 'cat4' from the categories table. Obviously this is because the WHERE clause is selecting it out of the result set. I'm at a loss to as to get a result set that includes all categories + the details of the products and quantities for which there are records in the specified user_products table.

The query should

return all categories
return data populated for user info iff the user has that product (a record exists in user_products)
not return rows with other user ids

It sounds like you are looking for a FULL [OUTER] JOIN. Per documentation:

  FULL OUTER JOIN

  First, an inner join is performed. Then, for each row in T1 that does not satisfy the join condition with any row in T2, a joined row
  is added with null values in columns of T2. Also, for each row of T2
  that does not satisfy the join condition with any row in T1, a joined
  row with null values in the columns of T1 is added.

SELECT *
FROM   users           u
JOIN   user_products   up ON u.id = up.user_id
                         AND u.id = 1
JOIN   products        p ON p.id = up.product_id
FULL   JOIN categories c ON c.id = p.category_id;

This returns all categories and also returns all user-product combinations for the given user_id.

If you are enforcing referential integrity with foreign keys and products.category_id is defined NOT NULL, so that every product is assigned to an existing category (not in your question), you can replace the FULL JOIN with a RIGHT JOIN.

Plus, either way, the condition to select a specific user has to move to a subquery or (simpler) to a JOIN condition between the first three tables. It has to be applied before the last table categories is joined.

SQL Fiddle.


Daily and Differential Backups Is there a program with which I can create daily MySQL, PostgreSQL and MariaDB backups with these requirements:

The first time, make a full backup.
The second time, only the changes since the last backup, and so on.

Only with some software or script, not PHP.

If you're using MySQL with InnoDB tables, then you could take incremental backups with Percona's xtrabackup, or talk to Oracle about enterprise licensing, which would net you MySQL Enterprise Backup.

Xtrabackup is a brilliant piece of software, but it behaves very differently from traditional MySQL backups using mysqldump, and it's worth spending some time studying and testing it before you look at rolling it into production.

If you're using MySQL with MyISAM, you're pretty much out of luck. You could perhaps do something logically, like maintain "last modified" timestamps on your tables that are maintained by triggers, but there's manual maintenance and several caveats that might make that unwise.

Recent versions of Xtrabackup should also work with MariaDB, though again you'll probably want to test this yourself.


How do I write a hex literal in PostgreSQL? How do you write a hex literal in PostgreSQL? Like say I want 0xCC, if I do;

SELECT 0xCC;
 xcc
-----
   0
(1 row)

So PostgreSQL is parsing the xcc as an alias.

Arbitrary Data / Integers (int)

You can write the hexidemical byte using Bit-string constants

SELECT x'CC';  -- same as b'11001100'

Which is essentially the same as bit x'CC' returning a Bit String Type but there is a cast available to int so you can do x'CC'::int * 5

UTF-8 Byte Sequence

If the byte sequences is a valid UTF-8 character, you can also use E'' with a backslash-escape sequence (single backslash \)

SELECT E'\x41';
 ?column?
----------
 A
(1 row)

If the sequence is invalid, you'll get an error

# SELECT E'\xCC';
ERROR:  invalid byte sequence for encoding "UTF8": 0xcc

bytea-specific.

PostgreSQL has a variable-length binary data type that allows arbitrary bytes. This type is called bytea. You can move into this format using the bytea hex format. In this we either use,

Use a double backslash \\
Use the cast to bytea from from the hex representation.

Here are both syntax,

SELECT bytea E'\\xDEADBEEF', bytea '\xDEADBEEF';
   bytea    |   bytea
------------+------------
 \xdeadbeef | \xdeadbeef
(1 row)

Storing large numbers

If you only need to store large numbers, rather than storing them as bytea I would check out pg_bignum which stores them using the openssl implementation of Big Numbers.


3rd Party Platform Unique Identifier for Joins vs Internal Identifiers What is the general practice when it comes to choosing a unique identifier? In my case I have the option to use the eBay unique listing ID (Item ID) or my own internal ID for use in table joins and general identification. The argument could be made that some 3rd party identifiers are non-numeric and I would understand this proposition but in my specific case the identifier is numeric. Thanks for any advice or personal experiences in advance!

To be safe you'll want both. Internally you should create and use your own unique IDs to maintain a clean relational model with FKs between your tables while keeping a reference to the eBay unique ID in an appropriate table. If you need to reference something by the eBay ID you will have it available, but should the eBay unique ID rules change it does not affect the consistency of your existing table relations and architecture.


Replication Issue - Changing the replication-do-db filter I have a server that someone has setup which are acting as a master on both database. Since they have been configured as both master on the same database, changes one on end has caused all sorts of issues on the other.

On one of the master is displaying two databases in the replication-do-db when I do show master status which is rather odd.

Here's the entry in my system that's of concern:

Replicate_Do_DB: db1,db2,d1,db2

MySQL Version is 5.1, I'm looking for a way to change the replication filter. CHANGE REPLICATION FILTER seems to only apply to version 5.5.

An you explain to me what I need to do to recover from this scenario? Thank you.

If I understand correctly: You have two DB's in a Master <-> Master setup, but one of them has the replicate-do-db rules meaning they are now (potentially) out of sync?

At present the DB with the replicate-do-db options is only replicating (processing) statements where the database concerned is in the list (the precise rules vary depending on whether you are using Statement Based or Row Based Replication).

http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-do-db

Unfortunately the CHANGE REPLICATION FILTER option has only just been introduced in MySQL 5.7, prior to that you will need to find the set of entries in the my.cnf file for your database with the replicate_do_db = statements.

You will need to remove / hash (#) these out, and then restart MySQL.

After that the DB will replicate all statements that are passed across from the other master.

The final thing you will probably want to do is run something like pt-table-checksum to check the consistency of the two DB's. If needs be you may need to dump/recreate the second DB to make it consistent with the first one.


What is "query containment"? Any one knows what is "query containment" and where do we use it please?
As far as I can search on Google, I only found one here.

Although, I've got some idea on the subject but I'm finding it difficult to understand the use case of it.

Many thanks,

The linked paper has a definition:

  In the relational model, a query Q1 is said to be contained in the query Q2 if Q1 produces a subset of the answers of Q2, for any given database.


Restore a SQL Server 2012 backup file in SQL Server 2016/2014 I have SQL Server 2012 backup files and want to restore these to SQL Server 2016/2014. Will it work?

I need to find this out before I uninstall SQL Server 2012.

Yes this will work.  You can always go forward, you can't go backwards, at least not with backup and restore.  With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie go from 2000 to 2008 to 2014, but that shouldn't be a problem for you.

Be aware when you restore your databases they will have the older database compatibility level.


Count 3 hours overtime with in a given date time window I have to implement an access control system for a company, that will be able to report the access of each employee in a specific area of the building in order to provide a report.
One of the requirements is to provide a list of the employees with more than 3 hours overtime with in a given date time window.

The table schema of the table that holds the required information is the following.
ACTION states if the employee entered or left a specific level of the building. The rest are the time, the employee and the level.

create table access_action
(
    ID          bigint auto_increment primary key,
    ACTION      int      null,
    TIMESTAMP   datetime null,
    level_id    bigint   null,
    employee_id bigint   null,
    constraint FK_access_action_employee_id
        foreign key (employee_id) references employee (ID),
    constraint FK_access_action_level_id
        foreign key (level_id) references level (ID)
);

a sample result set

ID  ACTION      TIMESTAMP       EMPLOYEE_ID
1   IN  2017-05-10 09:28:00 3   9
2   IN  2017-04-24 07:49:00 3   8
3   IN  2017-02-27 07:08:00 1   9
4   IN  2017-04-26 06:15:00 1   25
5   IN  2017-02-02 09:37:00 3   24
6   OUT 2017-05-29 08:03:00 3   7
7   IN  2017-04-25 07:07:00 1   1
8   IN  2017-01-09 08:54:00 3   8
9   IN  2017-05-12 07:57:00 2   1
10  OUT 2017-02-09 08:03:00 3   30
11  IN  2017-03-09 08:56:00 3   30
12  OUT 2017-02-03 11:26:00 3   5

I believe I know how I can estimate the overtime considering the sum up of the time each employee spent inside the premises of the building which will give the total hours. But I only can imagine how I could do it in the application layer.

Is there a way I can estimate the overtimes purely with SQL?

EDIT

According to the comments there must be a strict sequence among the enter - leave actions therefore a created the following data set.

Based on @Akina s comment, I created two sets one for the IN actions and another for the OUT, which I joined them and subtract the time spent inside the premises. Therefore I can calculate the total working hours for each employee in any particular day.

I came up with the following query.

SET @row_number = 0;
SET @row_number1 = 0;

select SEC_TO_TIME( SUM( TIME_TO_SEC(abb.overtimes) ) ) overtimes, employee_id
from
    (select TIMEDIFF(ab.working_time,'08:00:00') overtimes, ab.employee_id from
        (select SEC_TO_TIME( SUM(aaaa.timespent) ) working_time, accessDate, aaaa.employee_id
            from
            (select aaa.OUTtime as OUTtime, aaa.INtime, TIME_TO_SEC( TIMEDIFF(OUTtime, INtime) ) as timespent, DATE(OUTtime) as accessDate, aaa.level_id, aaa.employee_id
             from
                 (select aa1.TIMESTAMP as OUTtime, aa2.TIMESTAMP as INtime, aa1.level_id, aa1.employee_id
                  from (select
                            (@row_number:=@row_number + 1) row_num,
                            a1.TIMESTAMP, a1.employee_id, a1.level_id
                        from access_action a1
                        where a1.ACTION = 'OUT'
                        order by employee_id asc, TIMESTAMP desc) aa1
                           INNER JOIN
                       (select
                            (@row_number1:=@row_number1 + 1) row_num,
                            a2.TIMESTAMP, a2.employee_id, a2.level_id
                        from access_action a2
                        where a2.ACTION = 'IN'
                        order by employee_id asc, TIMESTAMP desc) aa2
                       ON aa1.row_num = aa2.row_num) aaa) aaaa
        group by accessDate, aaaa.employee_id
        having accessDate >= '2017-05-26' and accessDate <= '2017-05-30'
        AND working_time > '08:00:00') ab) abb
group by employee_id
having overtimes > '03:00:00';

Performing the query in the data the result is the following.

overtimes   employee_id
03:50:00    4


What are the necessary course requirements to become a DBA? Before some of you say it is related to other posts, let me tell you that I am more interested about schooling and getting my Master's degree not necessary learn SQL and such.

I just graduated with a Bachelor's of Science degree in Computer Science and am now working in the automotive field. It's not too bad, but I'm just not fond of the automotive industry. I mainly got this job because I had experience with automotive software as an intern.

Anyway, one of the courses I had to take as an undergrad was Intro to Database Systems and I found it very interesting. I would like to get a master's degree in Database Administration or Architecture. Where I am currently located, I have very limited options. The university that is close to me is Oakland University, Michigan and here is the degree information and requirements.

My question is, this program doesn't really seem like it is geared towards a specific topic but rather a few general topics. It states that I need to take a total of 16 credits from the specialty group courses (which is in the link). I'm assuming Database Systems 1 and 2 is a must, but what other courses would benefit me? As I was saying, they have project planning, networking, and other courses which (to me) seem unrelated to what I need to get a job in this field in my future.

If someone experienced could briefly explain what their education was like that helped them get into the DBA field that would be great too and I would really appreciate it.

Thank you.
(I apologize for not adding the correct tags because I'm not sure which one to use besides dba, so feel free to add more if necessary)

EDIT: Forgot to mention, if you can tell me the necessary certificates for this field as well that would be great!!

I've discovered my education as a DBA and as an IT professional came in three tiers.

What I learned on my own. Basically from each job I was hired for, I learned how each shop operated, listened to business requirements and learned on my own how to complete the assignments. A lot of reading the manual (now Googling) and trial-and-error is involved but it's ultimately how we all learn.
What I learned from certifications. One time I picked a textbook for my favorite programming language and discovered that I was only using a fraction of the techniques described on the job. I wanted to learn as many aspects as I could. In the back of the book they had mentioned the certification program for my language (Microsoft Certified Professional in this case). It's a structured way to learn all the major features of the platform you're focusing on for your employment. There's a lot of studying and practice involved (My MCSD took four tests and my MCDBA and MCITP:DBA were comparable). You'll learn about features you might not end up using on a particular job site, but being aware of their existence helps shape you decisions on how to tackle a business requirement.
What I learned from the community. This by far, is the most valuable. You learn firsthand that you're not alone in your profession. You also learn what your peers have learned on the job and through their own research, and it's great to share what you've learned. You'll find your mentors here as well. There's a large of community of masters, MVPs and fellow DBAs who share their knowledge in-person, in print and online. Search for them in Google, attend a local user group, have your employee fund a trip to your favorite products' annual convention. I've seen at SQL PASS twice and the knowledge shared there is amazing.

One book that I've personally found best describes the calling of a DBA is DBA Survivor: Become a Rockstar DBA. It goes beyond the technical and talks about your relationship to your customers and your responsibilities to the tradecraft. It helped reaffirm my belief that the DBA profession is an essential one that is challenging, honorable and rewarding.


Multi Master replication vs Single-Master performance I'm trying to wrap my head around a hypothetical scenario.

Imagine we have a very write-heavy distributed database and sharding is not an option.

I wonder if / how is multi-leader replication (write-write) more efficient than single-leader scenario (write-read) since write-write has overhead to sync databases and propagate writes to the other master(s) ending up with the same number of write operations, ultimately.

In which cases is multi-leader replication for write-heavy applications considered more performant than single-leader and in which cases it is not?

I understand the question is broad and nuanced but would be happy to read some thoughts on the subject.

Every write is performed on every machine, whether it is a Master or a Slave.  The corollary to that is that there is a limit to write scaling with traditional Replication.  Even Galera and Group Replication are just variants on Master(s) and Slave(s).  Each server has all the data, and it is kept as up-to-date as is practical.

Hence, Sharding is the only way to get write scaling.

OK, let me try to answer your question anyway.

With RBR (Row-Based-Replication), the Slave can usually do a write (INSERT/UPDATE/DELETE/etc) with less effort than the Master.

That implies that having multiple Masters will spread the "Master effort" out somewhat.  Hence, having more Masters helps in scaling, and moving more reads off to Slaves help, too.

Some configurations, and my comments:

M <-> M "Dual Master" -- If you are writing to both (as implied above), then it is a somewhat fragile system.

M1 -> M2 -> M3 -> M1 "Circular Masters" -- very fragile.  If one server goes down, it is a nightmare to repair.

3+ Masters, each talking to each other "Galera Cluster" -- This solves the above fragilities, but suffers if there is large latency between Masters.  (latency to get geographic separation to get HA.)  This is about the best config for some write scaling today.

"Group Replication" -- a competitor of Galera when it comes to HA.  I'm not sure about write scaling.

In all of the above, Slave(s) can hang off any or all Masters to provide read scaling.

For write scaling (and not HA), I would go with 3 Galera nodes in the same server room.  If there is enough reads to worry about, then tack some readonly Slaves on.

Note:  Going past 3 nodes in Galera (or maybe it is 5), you risk some degraded performance.  This is due to the fact that each node must talk to each other node during each COMMIT.


Concurrency Issues when multiple users uses Portal (MySQL and PHP) We have set up a portal using Yii Framework in Apache, PHP and MySQL. In the portal, there is a feature to export the data in dbf format, the extract takes around 7 minutes, but in those 7 minutes no other user can do anything in portal, not even login.

All tables are in MyISAM Engine.

I cannot provide you the link as its in our internal server.

Server is 4core Xeon Processor with 32 GB Ram.

The framework calls somewhere, somehow the command mysqldump, and there you need to give him the --lock-tables=false option.


mysql - calculate traffic for tmp tables I'm thinking of placing tmpdir to SSD - to increase speed of working with sorting data in tmp tables on disk. Our base creates a lot temporary tables on disk during sorting, merging, joining an so on. So, placing tmpdir to SSD is good idea, or it will die with our heavy load within 2 or 3 monthes?

I simply want to get to calculate "traffic", being written by DB to tmpdir during some period of time (hour, day, etc), and using this value calculate "average" life of SSD.

How can I do this?
Kingston SSD SH103S3/240G
mysql-5.6 (900+ tables, 140Gb size of database) Debian-9

You can go 2 ways: logical and physical calculation.

PERFORMANCE_SCHEMA database has detailed io information about tables and file io. You may need to enable the right monitor metrics to get what you want.

For a physical path, there are multiple tools available. Separate your tmp dir on a different device (a temporary disk, a separate petition or if you cannot do anything else, a loop back device) and you can get detailed stats with some of the many standard Linux utilities or any of the higher level monitoring tools like Prometheus.


Big difference between send_request_date and sent_date in sysmail_sentitems after running sp_send_dbmail, A send DB mail process was executed on Friday:

placed on the mail queue at 2016-03-04 12:21:22.937,
it wasn't sent until 2016-03-04 16:00:01.000, 1311 seconds later.

Usually DATEDIFF(SECOND,send_request_date,sent_date) is at most a few seconds. What could have caused this massive time gap?

Diagnosed via

SELECT *, DATEDIFF(SECOND,send_request_date,sent_date)
FROM msdb.dbo.sysmail_sentitems;

Network Communication or a problem with the mailserver would be my guess. Check the log for the Mail server during this period. Chanses are you'll find it was unavailiable.
Regards Marten


Speed up an aggregate query on an 11 million row table I have a query that I want to speed up:

SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      CASE WHEN Message = 'OK'  then 1 else 0 end as Success
    , CASE WHEN Message <> 'OK'  then 1 else 0 end as [Error]
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1,  DATEADD(yy,
        DATEDIFF(yy,0,getdate()), 0)) then 1 else 0 end as FlagYTD
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1 ,
        convert(varchar(10), getdate(), 101)) then 1 else 0 end as FlagDTD
    FROM
      [Channels4].[dbo].[NotificationResult]
) Cnts

I thought maybe I could create a view or indexed view based on the subquery. However, upon testing I can't create an indexed view because "the view uses an implicit conversion from string to datetime or smalldatetime".

I tried using a traditional view, but that gave no performance enhancement at all. My next thought would be to maybe rewrite the entire query. What are everyone's thoughts?

Plan:

https://www.brentozar.com/pastetheplan/?id=rJqGY7iZW

Table Structure:

CREATE TABLE [dbo].[NotificationResult]
(
    [IdNotificationResult] [bigint] IDENTITY(1,1) NOT NULL,
    [ApplicationGuid] [nvarchar](48) NOT NULL,
    [MessageGuid] [nvarchar](48) NOT NULL,
    [IdNotificationResultTypeStatus] [int] NOT NULL,
    [MessageStatusCode] [int] NULL,
    [Message] [varchar](max) NULL,
    [ExceptionStatusCode] [int] NULL,
    [ExceptionMessage] [varchar](max) NULL,
    [Subject] [varchar](max) NULL,
    [From] [varchar](max) NULL,
    [Timestamp] [datetime] NOT NULL,
    [IdCreatedBy] [bigint] NOT NULL,
    [IdLastUpdatedBy] [bigint] NOT NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateLastUpdated] [datetime] NOT NULL,
  CONSTRAINT [PK_NotificationResult] PRIMARY KEY CLUSTERED
  (
    [IdNotificationResult]
  )
);

CREATE NONCLUSTERED INDEX [IX_NotificationResult_DateMessage]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC ) INCLUDE ( [Message]);

Doing a quick count for YTD, and DTD, I get this number for both: 11739267. The number of rows that are 'OK': 11782564.

As written you technically haven't asked a question here. I assume that you want to improve the performance of your query, but keep in mind that defining an acceptable response time is sometimes an important part of performance tuning. If a query runs once per day and takes a minute to finish is it really worth 8 hours of your time to make it run in 1 second?
More important than performance is correctness. It doesn't matter so much how long the query takes if it returns the wrong results, although of course taking a long time to return the wrong results is worse than taking a short time to return the wrong results. Depending on your time zone the UTC conversion stuff might not work out as you expected it. If there's any data affected by daylight savings time then you can't use the current hour difference between local time and UTC time to convert old data.
Setting all of that aside, I'm going to try to show you a few ways to speed up the query in the question. You have a covering index which is a good start, especially because it avoids reading the unrelated blob data. However there are still ways to speed up the query. I'm deliberately ignoring the clues that you gave about data distribution because I want to make this a more general answer that could help others and perhaps could be more helpful to you if your data changes in the future.
I mocked up 10 million rows with half of them having "OK" for the message and the other half having a long string. The dates are spread out over a few years. WARNING: this code takes up around 60 GB of space and ran in around 10 minutes on my machine.
CREATE TABLE [dbo].[NotificationResult]
(
    [IdNotificationResult] [bigint] IDENTITY(1,1) NOT NULL,
    [Message] [varchar](max) NULL,
    [DateCreated] [datetime] NOT NULL,
    [DateCreatedUTC] [date] NOT NULL,
    [Filler] VARCHAR(1000) NOT NULL,
CONSTRAINT [PK_NotificationResult] PRIMARY KEY CLUSTERED
  (
    [IdNotificationResult]
  )
);

INSERT INTO [dbo].[NotificationResult] WITH (TABLOCK) ([Message], [DateCreated], [DateCreatedUTC], [Filler])
SELECT CASE WHEN RN % 2 = 1 THEN 'OK' ELSE REPLICATE('Z', 3000) END
, DATEADD(SECOND, 11 * RN, '20140101')
, CAST(DATEADD(SECOND, 11 * RN, '20140101') AS DATE)
, REPLICATE('FILLER', 166)
FROM
(
    SELECT TOP (10000000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) RN
    FROM master..spt_values t1
    CROSS JOIN master..spt_values t2
    CROSS JOIN master..spt_values t3
) t;

CREATE NONCLUSTERED INDEX [IX_NotificationResult_DateMessage]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC ) INCLUDE ( [Message]);

If I run the query in the question I get the same query plan as you. It took 38 seconds. Here are some performance stats for the execution:

Table 'NotificationResult'. Scan count 5, logical reads 2509562, physical reads 0, read-ahead reads 2499799
SQL Server Execution Times: CPU time = 20626 ms,  elapsed time = 37663 ms.

The first opportunity to improve performance is that you have an implied WHERE clause predicate in your CASE statements. The query optimizer isn't smart enough to realize that any rows from previous years won't contribute to the totals. We know that 24 hours will always be longer than the difference between local time and UTC time so adding a filter like this shouldn't change the results:
WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))

Now instead of reading and aggregating 10 million rows from the index, SQL Server only has to process 1.4 million rows. The savings that you get with this optimization will depend on how the data is distributed in the plan. If all of your data is in the current year then performance won't improve yet. For my data, the query now finishes in 5 seconds and performance is greatly improved:

Table 'NotificationResult'. Scan count 5, logical reads 352033, physical reads 1, read-ahead reads 350073
SQL Server Execution Times: CPU time = 3062 ms,  elapsed time = 5354 ms.

We can do better than that. We're storing a VARCHAR(MAX) column in the index when really all that we need to know is if the column value matches "OK" or not. Without changing the table definition we can create smaller indexes to seek or scan against by creating three filtered indexes:
CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_OK]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] = 'OK';

CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_NOT_OK]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] <> 'OK';

CREATE NONCLUSTERED INDEX [IX_NotificationResult_Date_NULL]
  ON [dbo].[NotificationResult] ( [DateCreated] ASC )
  WHERE [Message] IS NULL;

The idea here is that these indexes have the data that we need but are much smaller on disk than the existing IX_NotificationResult_DateMessage index. Getting the query optimizer to use the filtered indexes required a query rewrite and an index hint (not sure why). Here's one way to rewrite the query:
SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      Success
    , [Error]
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1,  DATEADD(yy,
        DATEDIFF(yy,0,getdate()), 0)) then 1 else 0 end as FlagYTD
    , CASE WHEN DateCreated >
      dateadd(HOUR, datediff(hh,GetUTCDate(), GetDate())*-1 ,
        convert(varchar(10), getdate(), 101)) then 1 else 0 end as FlagDTD
FROM
    (
    SELECT 1 Success, 0 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_OK))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] = 'OK'

    UNION ALL

    SELECT 0 Success, 1 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_NOT_OK))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] <> 'OK'

    UNION ALL

    SELECT 0 Success, 1 Error, DateCreated
    FROM
    [dbo].[NotificationResult] WITH (INDEX (IX_NotificationResult_Date_NULL))
    WHERE DateCreated > DATEADD(DAY, -1, dateadd(YEAR, datediff(YEAR, 0, getdate()), 0))
    AND [Message] IS NULL
    ) t
) Cnts;

Now the query finishes in less than a second:

Table 'NotificationResult'. Scan count 10, logical reads 3874, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 2499 ms,  elapsed time = 890 ms.

(the plan below is missing one of the indexes)

It's true that we read more rows from the indexes than before but the indexes are in total about 100 times smaller than the original index.
If that query still isn't fast enough you can consider an indexed view. If you have a UTC date column in the table then it is straightforward to create a view which is eligible to be indexed:
CREATE VIEW [NotificationResult_indexed]
WITH SCHEMABINDING
AS
SELECT
 [DateCreatedUTC]
, COUNT_BIG(*) AS CNT_BIG
, SUM(CASE WHEN Message = 'OK'  then 1 else 0 end) as Success
, SUM(CASE WHEN Message IS NULL OR Message <> 'OK'  then 1 else 0 end) as [Error]
FROM dbo.[NotificationResult]
GROUP BY [DateCreatedUTC];

CREATE UNIQUE CLUSTERED INDEX CLU_NotificationResult_indexed
    ON [NotificationResult_indexed] ([DateCreatedUTC]);
GO

I believe that this query roughly captures your intent although I probably got some details wrong:
SELECT
  sum(case when FlagDTD = 1 then Success else 0 end)   as SuccessDTD
, sum(case when FlagDTD = 1 then [Error] else 0 end)   as ErrorDTD
, round(sum(case when FlagDTD = 1 then Success else 0 end) * 100.0 / sum(FlagDTD),2)
    as RateDTD
, sum(case when FlagYTD = 1 then Success else 0 end)   as SuccessYTD
, sum(case when FlagYTD = 1 then [Error] else 0 end)   as ErrorYTD
, round(sum(case when FlagYTD = 1 then Success else 0 end) * 100.0 / sum(FlagYTD),2)
    as RateYTD
FROM
(
    SELECT
      Success
    , [Error]
    , CASE WHEN [DateCreatedUTC] > dateadd(YEAR, datediff(YEAR, 0, getdate()), 0)
       then 1 else 0 end as FlagYTD
    , CASE WHEN [DateCreatedUTC] > CAST(GETDATE() AS DATE)
       then 1 else 0 end as FlagDTD
    FROM
      [dbo].[NotificationResult_indexed]
      WHERE [DateCreatedUTC] > dateadd(YEAR, datediff(YEAR, 0, getdate()), 0)
) Cnts;

It finishes in 66 ms:

Table 'NotificationResult_indexed'. Scan count 1, logical reads 7, physical reads 0, read-ahead reads 0
SQL Server Execution Times: CPU time = 0 ms,  elapsed time = 66 ms.


How many deadlocks am i really getting? system health or deadlocks/sec? I'm trying to look at deadlocks on a troublesome server, and when I look at the system_health xel file, I see 80 records for "xml_deadlock_report" in about 16 hours.  I assume these are all different deadlocks?

Perfmon and system_health don't really seem to match up.

which is more reliable, perfmon deadlocks/sec (hours * 60 * 60) * deadlocks/sec = @ 4000 or the 80 rows in the xml deadlock report? (I'm viewing the system_health xel via enterprise manager 2014)
is my method for the math totally wrong? I realize it is a generalization, but, really...that's a lot.

SQL 2012 Standard Edition
Windows 2012

I'm also using the following from Jon Kehayias
http://www.sqlservercentral.com/articles/deadlock/65658/

but it doesn't give me the time, and although the post is old I imagine it is pulling from the same .xel file as the system_diag so is missing events (as he mentions, if I'm reading his posts correctly)

From a reliability standpoint of deadlocks per second the perfmon counter will be more accurate.  The system_health monitor is based on ring buffers which can overflow over time or miss events if there is enough activity within a brief period in order to facilitate reliability.  There is a file that backs this which you can look at which will give you more history but if you are experiencing as many deadlocks as you are indicating there is a distinct possibility that you may not have full history within these.  This would explain your discrepancy between the two measurements, you aren't doing anything wrong from what I can see.

The files will be located in your SQL Server install directory under %Program Files%\Microsoft SQL Server\MSSQL11.[Instance Name\MSSQLSERVER]\MSSQL\Log\system_health_*.xel.


MySQL MSI appears to be installing wrong (latest) MySQL version For compatibility reasons with other software, I'm trying to install MySQL 5.6.40 on Windows 10. Any version from 5.7 onward is incompatible with my other programs.

I've been trying to use the MSI installer but - no matter what version of 5.6 I try to install - the only server option I get is 8.0.

The installer has the correction version in its name (e.g. (mysql-installer-web-community-5.6.41.0.msi) but when I run it, I only get version 8.0.11 appearing for installation. I've gone into the Add screen, and there's no other versions showing under server. There doesn't seem to be the ability to point to a different version during installation.

I get the same problem if I try the normal download site or the archive site.

I would prefer to use the MSI installer rather than downloading the zip files, as I am not confident with installing a database from a zip.

I've been searching to see if this has been reported by others, and I haven't found any comments on this.

I would appreciate any help.

Have you fully removed previous MySQL version (including all components, folders and registry keys) before install? Do it.

And temporary disable network connection to Inet during install... it prevents offering to install up-to-date version.


You're on your way and understand CTEs and Window Functions so here's an option:

With AvgSize As
(
SELECT
     AVG(Data_Wanted) As AveragePartitionSize
    ,[partition_id]
FROM Tble
GROUP BY [partition_id]
)

,LatestValue As
(
SELECT
      surrogate_id
     ,collection_ts
    ,ROW_NUMBER() OVER(PARTITION BY surrogate_id ORDER BY collection_ts DESC) RowNumber
FROM example_table
)

SELECT
    *
FROM BaseTable BT
JOIN AvgSize
    ON BT.[partition_id] = AvgSize.[partition_id]
JOIN LatestValue LV
    ON BT.surrogate_id = LV.surrogate_id
    AND BT.collection_ts = LV.collection_ts
    AND LV.RowNumber = 1
WHERE
    BT.Data_Wanted > AveragePartitionSize

CTE AvgSize establishes the average for the partition ID.

CTE LatestValue is your provided CTE restricted to necessary columns to establish the latest row.

The final step is to bring them all together. The CTEs restrict the query to the latest row and provide the partition average. The only thing left to do is compare the latest row partition size to the average partition size and you're left with your result set.

You could get a little more advanced with your variance check with something like:

ABS(AveragePartitionSize - BT.Data_Wanted) > 20


Import indexes from MongoDB v2.4 to MonDB v3.6.4 I have migrated the collection.bson file from MongoDB 2.4 to MongoDB 3.6.4 as collection.json.

But their is 1 more file collection.metadata.json in MongoDB 2.4.

How can I import this to MongoDB 3.6.4?

content of collection.metadata.json is as below;

{
  "options": {
    "create": "collection"
  },
  "indexes": [
    {
      "v": 1,
      "name": "_id_",
      "key": {
        "_id": 1
      },
      "ns": "emgda.collection"
    },
    {
      "v": 1,
      "name": "customerId_1_elementId_1_siteId_1_createdTimestamp_1",
      "key": {
        "customerId": 1,
        "elementId": 1,
        "siteId": 1,
        "createdTimestamp": 1
      },
      "ns": "emgda.collection",
      "background": true,
      "sparse": true
    }
  ]
}

  mongorestore version is as below,

emgda@ubuntu:~/trial$ mongorestore --version
mongorestore version: r3.6.4
git version: d0181a711f7e7f39e60b5aeb1dc7097bf6ae5856
Go version: go1.7
   os: linux
   arch: amd64
   compiler: gc
OpenSSL version: OpenSSL 1.0.2g  1 Mar 2016

  NOTE: When I use mongoretore -d emgda auditing.bson, I get below error for the collection.

checking for collection data in auditing.bson
reading metadata for emgda.auditing from auditing.metadata.json
Failed: emgda.auditing: error creating collection emgda.auditing: error running create command: BSON field 'OperationSessionInfo.create' is a duplicate field

I had to manually remove "options": { "create": "collection" }, from *.metadata.json file and rest worked fine.


SQL Server Facets: "Linked Server" exist but "Registered Server" doesn't - why? I'd like to monitor multiple SQL Server instances.
Something similar to what is described in "Monitor Your SQL Server Linked Server Configurations with Policy Based Management" but instead of using "Linked Servers" facet to employ a "Registered Servers" one.

But there is no "Registered Server" facet.
Why? I'd like to grasp this absence from conceptual p.o.v.?
and how to make the same as described in the cited article using registered instead of "linked" servers?

SQL Server 2012 R2, Windows Server 2008 R2

There is no such thing as a registered server in SQL Server itself.

Registered Servers can be added into SQL Server Management Studio to simplify managing multiple servers.

Policy Based Management, and facets, cannot be used against Registered Servers since they don't actually exist in the database engine.


mysql CREATE TRIGGER after insert on Update column with count it gives error. I don't understand why.

CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

  ERROR 1064 (42000): You have an error in your SQL syntax; check the
  manual that corresponds to your MySQL server version for the right
  syntax to use near '' at line 1

-EDIT-

agian error...

mysql> DELIMITER ; CREATE TRIGGER `estatecat_piece` AFTER INSERT ON `estate`  FOR EACH ROW  BEGIN  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id); END; DELIMITER ;
mysql>
mysql> show triggers;
Empty set (0,00 sec)

_ LAST ATTEMPT'S ERROR _

mysql> DELIMETER $$                                                                 -> CREATE TRIGGER estatecat_piece
    -> AFTER INSERT ON estate FOR EACH ROW
    -> BEGIN
    -> UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id) where estatecat_id=new.estatecat_id;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMETER $$
CREATE TRIGGER estatecat_piece
AFTER INSERT ON estate FOR EACH ROW ' at line 1

The immediate issue with your first statement is that it has BEGIN without a corresponding END. In this case, END should be at the very end of the CREATE TRIGGER statement.

Once you add the END keyword, the statement becomes technically correct, but you will have trouble executing it in MySQL. The reason is that semicolons, while being standard statement delimiters in SQL, are also treated by MySQL in a special way. MySQL splits the input text at semicolons and sends each part of the text to the server separately. The server thus receives incomplete, syntactically incorrect pieces and returns an error. See here for more details:

Defining Stored Programs

So, to prevent MySQL from doing that, you need to instruct it to use a different symbol as a delimiter – that is what the DELIMITER command is for. Consequently, your second attempt should be something like this:

DELIMITER $$

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
BEGIN
  UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);
END$$

DELIMITER ;

The first DELIMITER command tells MySQL to parse the input text until $$ is encountered from that point on. Your CREATE TRIGGER should, therefore, end with the $$ symbol so that MySQL can consume it in its entirety and send the whole statement to the server. Finally, the second DELIMITER command restores the standard delimiter for MySQL to resume normal processing of your commands.

There is also a simpler solution, but it works only in cases similar to yours. The trigger body in your case consists of a single statement. That allows you to omit the keywords BEGIN and END from your statement and, as a result, avoid using the DELIMITER command at all:

CREATE TRIGGER `estatecat_piece`
AFTER INSERT ON `estate`
FOR EACH ROW
UPDATE estate_category set piece = (Select count(*) from estate where estate.estatecat_id=new.estatecat_id);

The above trigger will work exactly the same.

On a different note, your UPDATE statement may have a flaw: as currently written, it will update the piece column in every row with the same row count – that of the category matching the inserted row. If the table has multiple rows, each for a different category, you probably need to introduce a filter on new.estatecat_id to your UPDATE statement, similar to the filter in the subquery, in order to update only the corresponding piece value:

UPDATE
  estate_category
SET
  piece = (
    SELECT
      COUNT(*)
    FROM
      estate
    WHERE
      estate.estatecat_id=new.estatecat_id
  )
WHERE
  ... /* specify the necessary column name */ = new.estatecat_id
;


CONNECT BY using 3 tables(2 DIM and 1 FACT to get the Level and dependencies) Below is my scenario and any help in this regard would be highly appreciated.
I need to get the list of Managers and reporting persons to that manager with LEVEL at ID level.
But the Fact table doesnt have hierarchial relationship with itself. Needs to get the id's from key's from 2 different dimension tables to generate the list.
I am not familiar with CONNECT_BY and JOINS to get list.

Fact table:

Person_ky    Mngr_ky
100          3
50           6
2000         9999
60           3
150          4
20           2

In DIM Person table

Person_ky    Pers_Id
100          x
50           y
2000         aaaa
60           a
150          m
20      q

In DIM Manger table

Mngr_ky    Mngr_id
3           m
6           a
4           q
2           t
999         bbb

In the above scenario,
pers_ky 100 (Id -'x') is reporting to mngr_ky 3(Mngr_id - 'm') and  person_ky 150 ('m') is reporting to mngr_ky 4 ('q') and person_ky 150 ('q') reporting to mngr_ky 2('t')..
So Need to have for person x, his managers are 'm','q' and 't' in the result.

pers_id mgr_id level
x       m        1     --> for pers_ky 100
x       q        2
x       t        3
y       a        1     --> for per_ky 60
y       m        2
y       q        3
y       t        4
aaaa    bbb      1     --> for pers_ky 2000
a       m        1     --> for pers_ky 60
a       q        2
.
.

This appears to be a straightforward connect by where you're getting the next person based on the prior manager. The only difference being you want to get the top-level (root) person listed for each row it applies to.

This can easily be found using the connect_by_root function (docs), which will return you the value of the column listed at the root node in each hierarchy.

This gives you a query like:

SELECT connect_by_root p.pers_id per, m.manager_id, level
FROM   fact_tab f
join   dim_per p
on     f.person_ky = p.person_ky
join   dim_mgr m
on     f.manager_ky = m.manager_ky
connect by  p.pers_id = prior m.manager_id

For a full example, see this fiddle.


How to copy MySQL Database from Linux to Windows? I would like to copy a MySQL database form Linux to Windows, from a little research I found this url http://www.cyberciti.biz/tips/howto-copy-mysql-database-remote-server.html where the author says we could copy MySQL Database using the following command in Linux

Linux to Linux

mysqldump -u username -p 'password' db-name | ssh user@remote.box.com mysql -u username -p 'password' db-name

Is there a command for copying MySQL database from Linux to Windows?

Linux -> Windows you have two obvious options.

Setup a SSHD on your Windows machine and use the above command (mysql binary would need to be in your search path under Windows).
Configure your root (or similar privileged account) to access your Windows MySQL host over network then do:

mysqldump -u username -p 'password' db-name | mysql -h windowsip -u username -p 'password' db-name

Unfortunately with #2, if you have complex indicies (or huge tables) you'll have issues with net_read_timeout on your Linux host. For most situations I would expect this to just work, though.


Latin1 to UTF8 on large MySQL database, minimal downtime Let me lay out the basic scenario for the problem I'm up against.

I'm running MySQL 5.1 and have a single database with 2 large MyISAM tables (9M and 20M rows respectively).
Each of these tables has several VARCHAR and TEXT columns which are currently set to the latin1 character set and latin1_ci collation.
These tables are actively read / written to, so we want to avoid as much downtime as possible.

I'm confident in actually converting the data between the two character sets (using ALTER TABLE to convert columns to their binary counterparts, then back to the correct original column type in the correct charset). What I'm not sure about is how to go about the conversion without locking the entire table for the duration (which could take hours / days due to the number of rows and indexes).

One idea is to set up replication to a slave; turn off the replication; convert everything; re-enable replication and let the slave catch up; then finally promote the slave to master.

However, this doesn't solve the issue of how to set up replication in the first place. From what I've read, even mysqlhotcopy & xtrabackup still require table locks for MyISAM tables.

Is there another option I've missed, or am I going to have to turn off my entire application in order to set this up? Thanks for any advice.

Promoting a slave would probably be my preferred route.  As you pointing out any selects on MyISAM tables would require table level locks.  There is one tool that might be able to help, pt-table-sync.  It's primary purpose is to find gaps and differences in existing master slave relationships.

A nice thing about it is it does this in nice "chunks".   Think of it kind of like antilock breaks.  The chunk size is configurable but you could, for example go through doing 1000 rows at a time, minimizing lock times and letting things flow through.  I haven't used it to fully repopulate a slave from scratch though although I'd give that a look.  Once you have a full copy, do another run to catch new stuff, updates that have come in.  Do a flush table with read locks, do a final table sync.  run show master status to get the binary log position to start slaving from, unlock tables.

Oh, if you don't have binary logging you'll at least need a master bounce and cnf change unfortunately.

Another approach that might be much simpler depending your write downtime tolerance.  You're all myisam,  you gave the size in rows by what's the disk footprint in MB or GB?   Figure out how long it would take to transfer that size between your machines (hopefully there both in the same local network).  You could do flush table with readlock, again still show master status, then just rsync the .MY* files over to your new DB's datadir.

One final alternative, depending on how you're setup: Can you do an LVM or other kind of filesystem snapshot? This would be the best way to minimize downtime.  You flush tables with readlock, show master status, start snapshot, then unlock the tables to allow full read write activity to flow through.  The difference here is you'll copy the snapshot you started.  You'll just need to feel confident the write activity won't exceed the snapshot size you allocate before the copy finishes.

What ever method, before promotion I would verify the character set conversions went as desired.  They can be a real pain to reverse.

I would also recommend upgrading to innodb if possible to make it possible to use xtrabackup in a non blocking fashion in the future.


Select row with MAX(), or choose one if no MAX() exists I have a MySQL InnoDB table with the following columns:

id: UNSIGNED bigint(20) AUTO_INCREMENT (PRIMARY KEY)
name_id: UNSIGNED bigint(20)
time_stamp: decimal(26,9)
event: enum('add', 'init', 'update', 'disconnect', 'remove', 'stopped')
value: longtext
alarm_status: enum('NO_ALARM','READ','WRITE','HIHI','HIGH','LOLO','LOW','STATE','COS','COMM','TIMEOUT','HWLIMIT','CALC','SCAN','LINK','SOFT','BAD_SUB','UDF','DISABLE','SIMM','READ_ACCESS','WRITE_ACCESS')
alarm_severity: enum('NO_ALARM', 'MINOR', 'MAJOR', 'INVALID')

Each row can be uniquely identified by either its id or the combination of its name_id, time_stamp and event.

There can be two rows with the same name_id and time_stamp if one of them has an event of 'add', 'init', 'update', 'disconnect' or 'remove' and the other one has an event of 'stopped'.

I am trying to put together the following query:

For a given time_stamp t0:

For each name_id, of the rows with an event not equal to 'stopped', find the one that has the largest time_stamp less than or equal to t0. (1)

For each name_id, of the rows with an event equal to 'stopped', find the one that has the largest time_stamp less than t0. (2)

For each name_id, return the row from (1) or (2) that has the largest time_stamp. If the time_stamps are the same, return the row from (2).

I believe the following queries work for (1) and (2), but I'm not sure how to do the last step to conditionally select from them:

(1)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp <= t0
AND data.event <> 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event <> 'stopped';

(2)

SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp < t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';

I would also be interested in any suggestions for improving the table structure.

It is possible with UNION ALL. Basically, you have three criteria, therefore you need three SELECT statements. To simplify the select statements, lets create views from your selects as follows:

CREATE VIEW data1 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp <= t0
AND data.event <> 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event <> 'stopped';

CREATE VIEW data2 AS
SELECT data.* FROM (
SELECT data.name_id, MAX(data.time_stamp) AS time_stamp
FROM data
WHERE data.time_stamp < t0
AND data.event = 'stopped'
GROUP BY data.name_id ) d1
JOIN data
ON data.name_id = d1.name_id AND data.time_stamp = d1.time_stamp AND data.event = 'stopped';

Now, the select statement will be as easy as this one:

select data2.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp = data2.time_stamp
UNION ALL
select data1.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp > data2.time_stamp
UNION ALL
select data2.*
from data1 inner join data2
on data1.name_id = data2.name_id and data1.time_stamp < data2.time_stamp

SQL Fiddle (I used INT datatype for time_stamp column in the SQL Fiddle, but it shouldn't matter, as the principle is the same for timestamp datatype)

You do not need to use VIEWs, you can embed you queries into the above statement as subqueries. I have used the views just to demonstrate the technique


Is a partial restore of data from backup possible? Apologies if I haven't used the correct terminology, I am not a DBA, but right now, I am all that is here!

Situation is last week, a database server was misbehaving (it was about 6 years old). The hosting company built a new server, and moved all the current data across to the new server, which is running well.

However, somehow, the new server is missing 36 hours of data. The hosting company has provided us with a backup that contains this data, amongst other things.

As some of the tables have auto generated keys (type integer) is there any way of restoring just a set of data from a date range back into my live database? I obviously can't just cut and paste, as all the integrity of the data will be lost.

Thoughts?

Windows Server and Microsoft SQL Server 2008.

No, you cannot do what you mentioned in your question.

I am sure there are many ways to recover the data you need.  One way is to:

Restore the backup as a different(name) database.
Reconcile data using this source and your current live database.  How you do that? Will entirely depend upon with what you have lost and your business logic.
You can surely take advantage of auto generated integer keys (at least for the tables that has this). You might have to do some plumbing here depending on where is the gap in your current live database.

I know it is a very generic answer. If you need specific help with individual tables, you will need to post table schema, sample data and details of gaps.

On a side note you probably want to reconsider using this hosting company going forward.


my.cnf for 16 GB Ram Server [MariaDB] I am very new to this stuff. I just wanted to ask if somebody has a good my.cnf for my MariaDB database because every time i have more than  1000 connections to my website it starts to have hight load time up to 16 Seconds under this value it loads instand.

My mysql settings are very default i only changed the max_connections.

max_connections        = 10000

Server Specs:

CPU: Intel i7-4790K 4c / 8t 4GHz
RAM: 16 GB DDR3
Debian 9
PHP Version 7.0.33
Apache 2.4.25
MariaDb 10.1.41

max_connections depends on RAM because each connection uses RAM for read/join/sort buffers. You can not set max_connections arbitrary high. In general the RAM consumption can be represented that way:

+------+-----------------------+---------------------+-------------+
|  OS  | MyISAM/InnoDB buffers | Connections buffers | Third party |
|      |                       +---------------------+             |
|      |                       | | | | | | | ...     |             |
+------+-----------------------+---------------------+-------------+

Host's OS need some RAM for the proper operation - usually 1-2GB+.
Third party services (apache/php/etc) requirements should be
considered.
DB engines are used RAM for caching. MyISAM caches
indexes only while InnoDB caches both data and indexes.
All active connections consumes a lot of RAM.

For maximum performance InnoDB should have innodb pool big enough to fit all the tables and their indexes in the RAM. max_connections should be tuned along with the read_buffer, join_buffer and sort_buffer values.

But the main attention should be paid to the queries optimization. You can't get a good overall peformance if the single query is slow. Even if you have the host with 256-512 GB of RAM it can be slow if the frequenlty executed queries are slow.


Reverse engineer SQL Server diagram? I've been trying to find an option similar to MySQL Workbench 'Reverse engineer' for MSSQL Server 2012. I want to generate the SQL script from a diagram I've created. Is such option available for SQL Server? Or would I have to use a third party solution for this?

I tried googling for a solution but I couldn't find anything related.

Thank you.

I probably overlooked this so I'm sorry if I posted this question in vain but I consider this solved. I Expanded the 'Tables' folder of my database and found out that the tables were already there. I'm not sure if they're automatically generated as you build the diagram or if they're created once you generate a backup. All I did was to generate a .bak and a .sql file through the 'Tasks' menu, using the 'Back up...' and 'Generate Scripts...' sub-menus. Again I probably overlooked this before generating a backup and they were already there.