Breaking Semisynchronous Replication in MySQL 5.5 I've set up Semisynchronous Replication between two MySQL 5.5 servers running on Windows 7.

My application is running and updating the database of the master server and same is being updated in the slave database server.

But due to some unknown reasons sometimes, Replication breaks.

On running the command:

SHOW STATUS LIKE 'Rpl_semi_sync%';

It gives this status:

'Rpl_semi_sync_master_no_times', '0'
'Rpl_semi_sync_master_no_tx', '0'
'Rpl_semi_sync_master_status', 'ON'     <<-------------
'Rpl_semi_sync_master_timefunc_failures', '0'
'Rpl_semi_sync_master_tx_avg_wait_time', '338846'
'Rpl_semi_sync_master_tx_wait_time', '29479685'
'Rpl_semi_sync_master_tx_waits', '87'
'Rpl_semi_sync_master_wait_pos_backtraverse', '0'
'Rpl_semi_sync_master_wait_sessions', '0'
'Rpl_semi_sync_master_yes_tx', '3106'

Ideally, in semi synchronization, when the sync breaks the status should come as OFF since master is not able to receive any acknowledgement from the slave.
Please help us in this regard.

The one thing you can probably do is to increase the sensitivity of the acknowledgement

Please look for this variables in /etc/my.cnf

[mysqld]
rpl_semi_sync_master_timeout=5000

When rpl_semi_sync_master_timeout (Default is 10000 or 10 seconds) is set to 5000, Replication should switch a Master from SemiSync to Async if no acknowledgement is received from the Slave in 5 seconds (5000 milliseconds). You may want to lower this even less than 5000.

You may need to check your network performance. As long as you not doing geographic distance replication, you should the following: On a Separate NIC, use a crossover cable over 192.168.xx.xx so as not have faster replication response. Also, check the switch for dropped packets.

You should not have to restart the server every time. As a quick-and-dirty band-aid, make up this SQL script:

STOP SLAVE IO_THREAD;
SELECT SLEEP(5);
START SLAVE IO_THREAD;

Simply write a cronjob to execute these commands every 5 minutes. Again, this is a band-aid. Otherwise, due diligence on the networking side is in order.


Enabling write caching for TempDb data drives Recently, we had issues with slowness for some queries where TempDb latencies on their respective drives had spikes.

We already have 8 tempdb files and trace flags 1117 and 1118 are not enabled. We are planning to enable but do not know as of now what issues might occur due to them.

However our storage team has suggested something called enabling write caching for TempDb drives. We have no clue how this will effect overall performance.

Can someone provide any insight if enabling this is really a good idea for SQL server TempDb files?

Trace flag 1117 deals with uniform auto-growth across multiple data files(https://www.brentozar.com/archive/2014/06/trace-flags-1117-1118-tempdb-configuration/), while 1118 deals with mixed versus dedicated extents. These trace flags are not limited to use on tempdb, so it will affect your entire instance.

I'd look at this article as well.

As far as write caching goes, dependent on the configuration of your storage array, it will probably delay the writes to disk briefly in cache until they commit to disk (ymmv though, I've seen issues with power outages affecting cache, thus losing the writes before they were committed to disk).

Have you examined the queries to know how they're using tempdb, and if they can be tuned to use it more efficiently?

I'd also look at how your storage layer is architected for your databases.  Do you have tempdb isolated on separate storage than your user database files?

Is the storage latency behavior repeatable each time the query is run?


Prevent updating table using procedure in different database This is my scenario:

Windows User Gabe has db_owner access of database DB1.
Windows User Gabe has db_reader on database DB2.
When the user executes UPDATE DB2.dbo.Tbl1 SET X = 1, they get "The UPDATE permission was denied.." as expected.
when user executes a proc DB1.dbo.uspUpdateTable containing the same UPDATE statement above, it succeeds.

I thought this might be due to database chaining or trustworthy settings but they are not enabled.

Is this the expected behavior for db_owner privilege?? And is there a way to prevent this?

SQL Server 2017

Below are cases where updates will be allowed via the stored stored procedure without the caller having update permissions on the table in the other database.

Ownership chaining

The databases involved have the DB_CHAINING option set or the 'cross database ownership chaining' option turned on at the server level. In the case of dbo-owned objects, the databases must also the same owner (i.e. dbo user maps to same login) to mainain an unbroken ownership chain.

EXECUTE AS

The stored procedure includes an EXECUTE AS USER specification and the user (mapped to same login) exists in the other database with update permissions. Note that the database must be TRUSTWORTHY in order for the EXECUTE AS context to be honored by other databases so this does not apply in your case.

Module Signing

The stored procedure is signed by a certificate and a user created from that certificate exists in the other database with permissions to update the table.

I see from your comment that 'cross database ownership chaining' was the culprit. This also implies the databases also have the same owner. I suggest you change the database owner for databases where non-sysadmin role members can create dbo-owned objects to mitigate security risk.


Copying MySQL schema, some tables not getting recognized! From the 'data' folder of my colleague I copied the entire 'MyDatabase' folder and pasted that in my own 'data' folder.Now, some tables are getting recognized while others are not.**All the tables are in .frm format.

Some random tables are not getting imported. All the tables that I have are InnoDB with only two MyISAM tables. Many InnoDB tables and both the MyISAM tables are working fine. It's just that some random tables are not working.

The ".frm" file holds table definition. You might want to check mysql file-types info.

Usually when you move data-base physically do following:

Shutdown mysql
Copy datafiles to datadir
make sure mysql has full permissions on it:
chown mysql:mysql datadir/ -R
start mysql

So, make sure that the tables which are not "visible" have proper permissions for mysql to access it.

Also, when you say you moved "database" do you mean all-databases-on-server or single-database?
If your database have innodb tables you cannot move them like this!


To check exactly how selective your filter is for your particular query, you could execute the following queries:

SELECT
   (SELECT count(*) FROM indi WHERE sex = '2')
   /
   (SELECT count(*) FROM indi)
   * 100
   as selectivity;

SELECT
   (SELECT count(*) FROM c_loc WHERE location_id IN (3,4,5,6))
   /
   (SELECT count(*) FROM c_loc)
   * 100
   as selectivity;

The first query will probably return something around 50% in most applications, the second will give you around 1%, if the locations are more or less equally distributed, but it can vary.

Having a low cardinality is not per se a bad thing, the problem is when you need to search only on that column in a efficient way with an index. Having low cardinality means that a full table scan may actually be faster. The limit depends on many factors, and the MySQL optimizer is a bit more complex than a simple percentage calculation, but here you have a real example of when an index is no longer useful:

When the selectivity is higher than around 20%, the index tends to be no longer useful for filtering.

There is no "real" solution modifying the query. For those cases, you may need a different method for indexing, different than the standard BTREE algorithm. In most cases, you can provide a solution on MySQL using partitioning (even doing it manually, storing on a different table males and females, for example).

For your particular case, the index is still being used, which means it may be still good enough, and you may not need to do any additional work, but I though it was important to understand the consequences of low selectivity, and playing around with indexes to see if you were getting an optimal query plan. However, if the final number of rows is relatively large, there is no good solution: if you select a lot of rows, it will take a lot of time to do it. If your final result set is relatively small, try to force the join order with the most selective clauses at the beginning, by using STRAIGHT_JOIN, if the logic allows it. This is dangerous without proper monitoring, because cardinality may change with time, and so does the selectivity for the same query just by using slightly different parameters.


Send results of autogrowth from default trace to application event log I've been asked to generate an entry in the event viewer, as opposed to an e-mail notification, whenever there is an autogrowth event. The problem I'm having is getting data from the default trace parameters into the message part of the event viewer.

To generate the event viewer entry, I'm using:

EXEC xp_logevent 60001, @msg, error

If I try to set @msg to say EventName I get an error message:

  Invalid colmun name 'EventName'

Right now an entry is created in the event viewer, but it doesn't state where the autogrowth happened, how much, etc. Without that data, someone who gets the alerts via azure log analytics will have to log in to the server to check it out every time. Here is my current query:

DECLARE @tracepath nvarchar(500)
SELECT @tracepath = path FROM sys.traces WHERE is_default = 1

--The trace automatically finds _n files, trim off the _nnn portion of the file name.
select @tracepath = substring(@tracepath, 0, charindex('\log_', @tracepath,0)+4) + '.trc'

print @tracepath

SELECT
DBName    = g.DatabaseName
, DBFileName   = mf.physical_name
, FileType   = CASE mf.type WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN 'FILESTREAM' WHEN 4 THEN 'Full-text' END
, EventName   = te.name
, EventGrowthMB  = convert(decimal(19,2),g.IntegerData*8/1024.) -- Number of 8-kilobyte (KB) pages by which the file increased.
, EventTime   = g.StartTime
, EventDurationSec = convert(decimal(19,2),g.Duration/1000./1000.) -- Length of time (in milliseconds) necessary to extend the file.
, CurrentAutoGrowthSet= CASE
     WHEN mf.is_percent_growth = 1
     THEN CONVERT(char(2), mf.growth) + '%'
     ELSE CONVERT(varchar(30), convert(decimal(19,2), mf.growth*8./1024.)) + 'MB'
    END
, CurrentFileSizeMB = convert(decimal(19,2),mf.size* 8./1024.)
, @tracepath
FROM fn_trace_gettable(@tracepath, default) g
cross apply sys.trace_events te
inner join sys.master_files mf
on mf.database_id = g.DatabaseID
and g.FileName = mf.name
WHERE g.eventclass = te.trace_event_id AND DATEDIFF(mi, g.StartTime, getdate()) < 360
and  te.name in ('Data File Auto Grow','Log File Auto Grow')
order by StartTime desc
IF @@ROWCOUNT > 0
BEGIN
PRINT ERROR_Message()
DECLARE @msg VARCHAR(100)
SELECT @msg = ERROR_MESSAGE()
EXEC xp_logevent 60001, autogrowthEvent, error
END

Well, if you're going to pull this information from the default trace, you need to (1) combine the info such as database name, size of growth, etc. into a single string to pass to xp_logevent, and (2) deal with multiple rows (since multiple events may have happened since the last time you checked). I'll assume that in the case of (2) you want a separate event logged for each growth event rather than one event per polling period.

DECLARE @tracepath nvarchar(500), @msg nvarchar(4000), @cr char(2) = CHAR(13)+CHAR(10);

SELECT @tracepath = substring(path, 0, charindex('\log_', path,0)+4) + '.trc'
FROM sys.traces WHERE is_default = 1;

DECLARE @db sysname, @fn nvarchar(max), @type varchar(10), @etype varchar(4),
  @eg varchar(20), @et char(17), @ed varchar(20), @cag varchar(32), @cfs varchar(20);

DECLARE c CURSOR LOCAL FAST_FORWARD FOR
SELECT
  g.DatabaseName,
  mf.physical_name,
  FileType = CASE mf.[type] WHEN 0 THEN 'Row' WHEN 1 THEN 'Log' WHEN 2 THEN
    'FILESTREAM' WHEN 4 THEN 'Full-text' END,
  [type] = CASE g.EventClass WHEN 92 THEN 'Data' ELSE 'Log' END,
  EventGrowthMB  = CONVERT(varchar(20),convert(decimal(19,2),g.IntegerData*8/1024.)),
  EventTime = CONVERT(char(8), g.StartTime, 112) + N' '
    + CONVERT(char(8), g.StartTime, 108),
  EventDurSec = CONVERT(varchar(20),convert(decimal(19,2),g.Duration/1000./1000.)),
  CurrentAutoGrowthSet= CASE WHEN mf.is_percent_growth = 1
     THEN CONVERT(char(2), mf.growth) + '%'
     ELSE CONVERT(varchar(30),convert(decimal(19,2),mf.growth*8./1024.)) END,
  CurrentFileSizeMB = convert(varchar(20),convert(decimal(19,2),mf.size* 8./1024.))
FROM fn_trace_gettable(@tracepath, DEFAULT) AS g
INNER JOIN sys.master_files AS mf
ON mf.database_id = g.DatabaseID AND mf.name = g.FileName
WHERE g.StartTime >= DATEADD(MINUTE, -360, GETDATE()) AND g.EventClass in (92,93)
ORDER BY g.StartTime; -- want to log the newest one *last*, right?

OPEN c;
FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
WHILE (@@FETCH_STATUS <> -1)
BEGIN
  SET @msg = @cr + N'Autogrow occurred (' + @et + N'): for '
      + @db + N' (' + @etype + N')' + @cr + @fn + N' ('
      + @type + N')' + @cr + N'Duration (sec): ' + @ed
      + @cr + N'Growth (MB):' + @eg + @cr + N'CurrentGrowthSetting (MB): '
      + @cag + @cr + N'CurrentSize (MB): ' + @cfs;

  EXEC sys.xp_logevent 60001, @msg, error;
  FETCH c INTO @db, @fn, @type, @etype, @eg, @et, @ed, @cag, @cfs;
END
CLOSE c; DEALLOCATE c;

Result on my system:


Attributes in relation after cross product I have a question regarding the resulting attributes in the cross product of two relations in relational algebra...

Generally, the cross product will result in a relation whose attributes is the sum of all the attributes, So number of attributes in R1 x R2 will be attributes in R1 + R2, so R1(a, b, c) x R2(d, e, f) → R(a, b, c, d ,e ,f)...

But, what happens if R1 and R2 have common attributes (attributes of the same name)? since a relation can not have duplicate attributes... so what would happen in the case R1(a, b, c) X R2(a, e, f)? thanks...

To express that relation in algebraic notation you actually need to alias the attribute.

The Cartesian product, by design, does not recognize common attribute names, so much like a single operation doesn't work in a self relation scenario, it's necessary to perform a rename within the notation.

The self join example:

  R( a, b, c );

  ≠ R ⋈ R ( a, b, c );

Using rename:

  R( a, b, c );

  S = ρa/b, b/b2, c/c2 R;

  ∴ R ⋈ S ( a, b, c, b2, c2 );

For instance:

  E( EmpID, ManagerID, EmpName );

  M = ρEmpID/ManagerID, ManagerID/ManagerEmpID, EmpName/ManagerName
  E;

  ∴ E ⋈ M ( EmpID, ManagerID, EmpName, ManagerEmpID, ManagerName );

The Cartesian product can be expressed using relational algebra in the same way:

  R1( a, b, c );

  R2( a, e, f );

  ≠ R1 × R2 ( a, b, c, e, f );

So then with rename:

  R1( a, b, c );

  R2( a, e, f );

  R3 = ρa/a2R2;

  ∴ R1 × R3 ( a, b, c, a2, e, f );


Store values in select as a temp variable and reuse them I am trying to clean up a query that is ugly but does the job. I am wondering if it is possible to create a temp variable inside of a SELECT statement. I have Googled and not found any help. Basically what I am doing with the query is comparing a DateTime field against the current time to see if the date is in the past or the future. Maybe I have over complicated something simple but from looking around on the internet this seems to be the only way to compare a DateTime field.

I want to be able to reuse the following repetitive parts of the query because I have to list them twice to get the floor value:

(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)

AND

(CAST(GETDATE() AS FLOAT)

Here is the query:

    SELECT
          bq.quoteID
        , bq.unsentNumber
        , bq.sendSubsequent
        , bq.unsentDelay
        , sr.contractorID
        , CASE
            WHEN (CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT)  - FLOOR(CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT))) <= (CAST(GETDATE() AS FLOAT) - FLOOR(CAST(GETDATE() AS FLOAT)))
                THEN 1
                ELSE 0
            END AS 'Add Bid'
        , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
        , GETDATE() AS 'Time Now'
        , e.email
        , e.name
    FROM tblCustomerBidQueue bq
    INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
    INNER JOIN tblContractors c ON c.ID = sr.contractorID
    INNER JOIN tblEmails e ON e.custom = bq.QuoteID
    WHERE bq.status = 'Queued'

Thanks in advance for any help!

Yes, you can do what you want.  It's not exactly a variable but it works the same.  It's one of several ways you can use CROSS APPLY.

SELECT
      bq.quoteID
    , bq.unsentNumber
    , bq.sendSubsequent
    , bq.unsentDelay
    , sr.contractorID
    , CASE
        WHEN (ComputedColumns.MyDateAdd  - FLOOR(ComputedColumns.MyDateAdd)) <= ((ComputedColumns.MyDate) - FLOOR(ComputedColumns.MyDate))
            THEN 1
            ELSE 0
        END AS 'Add Bid'
    , DATEADD(n, bq.markupDelay, sr.lastviewed) AS 'Send Time'
    , GETDATE() AS 'Time Now'
    , e.email
    , e.name
FROM tblCustomerBidQueue bq
INNER JOIN tblServiceRecords sr ON bq.QuoteID = sr.cartID AND sr.status = 'Bid' AND bq.status = 'Queued'
INNER JOIN tblContractors c ON c.ID = sr.contractorID
INNER JOIN tblEmails e ON e.custom = bq.QuoteID
CROSS APPLY (SELECT CAST(DATEADD(n, bq.markupDelay, sr.lastviewed) AS FLOAT) AS MyDateAdd,
                CAST(GETDATE() AS FLOAT) AS MyDate) ComputedColumns
WHERE bq.status = 'Queued'

Basically you CROSS APPLY your variables and then you can use them as if they are a the columns of a table.


Migrating or exporting a database from azure to SQL Server 2016 I am playing around with SQL Azure and in my learning curve. I Have migrated a database from my on premises instance to the Azure server. Now my question is, how do I move a database from Azure to on premises instance.

Source : Azure , Database name : Azuretest

Destination : Sql server 2016 on premises instance in my laptop.

Is Azure to On Premise database migration possible?

Export the Data-tier to local disk from the Azure database. It will save a bacpac file.

  Right click the database you need to export --> Task-->Export
  data-tier application and follow the steps until the bacpac file is
  created.

  Import the backpack file from the local disk to the On premises Instance by using the same utility from tasks.

  Right click the database branch in your on premise instance --> Import
  data tier application and now use the bacpac file saved in first step
  to create the database.


Can't create triggers in MySQL, no errors but no triggers either I'm trying to create triggers without much success I'm not getting any error but the triggers don't show up either... So what's wrong with this?

USE `wa3`;

CREATE TABLE `transactions` (
    `id` CHAR(36) NOT NULL,
    `account_number` INT(11) NOT NULL,
    `transactions` INT(11) NOT NULL,
    `debit` DECIMAL(16,2) NULL DEFAULT NULL,
    `credit` DECIMAL(16,2) NULL DEFAULT NULL,
    `balance` DECIMAL(16,2) NOT NULL,
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

DELIMITER //
CREATE TRIGGER `wa3`.`InsertCreditOrDebitNotNull` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END;
CREATE TRIGGER `wa3`.`UpdateCreditOrDebitNotNull` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END;
DELIMITER ;

DELIMITER //
CREATE TRIGGER `wa3`.`InsertBothCreditAndDebit` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END;
CREATE TRIGGER `wa3`.`UpdateBothCreditAndDebit` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END;
DELIMITER ;

SHOW TRIGGERS;

  /* Affected rows: 0  Found rows: 0  Warnings: 0  Duration for 2 queries: 0.015 sec. */

You have to use delimiter declared earlier to complete the statement:

DELIMITER // -- turn delimiter into "//" instead of ";"
CREATE TRIGGER `ZZZ`.`name` BEFORE INSERT ON `transactions`
FOR EACH ROW
BEGIN
   -- the code that will be stored for the future use,
   -- not the statements that will be executed right now
   SELECT something FROM ... ; -- inactive delimiter

END// -- redefined delimiter
DELIMITER ; -- again

Trigger declaration is the statement that contains the code. To tell the code from the statement the different delimiters are used. Redefined // for the containing statement and the default ; for the contained code.

That's how your queries should look like:

USE `wa3`;

CREATE TABLE `transactions` (
    `id` CHAR(36) NOT NULL,
    `account_number` INT(11) NOT NULL,
    `transactions` INT(11) NOT NULL,
    `debit` DECIMAL(16,2) NULL DEFAULT NULL,
    `credit` DECIMAL(16,2) NULL DEFAULT NULL,
    `balance` DECIMAL(16,2) NOT NULL,
    `created` DATETIME NULL DEFAULT NULL,
    `modified` DATETIME NULL DEFAULT NULL,
    PRIMARY KEY (`id`)
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;

DELIMITER //

CREATE TRIGGER `wa3`.`InsertCreditOrDebitNotNull` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL)
    THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END// -- first trigger

CREATE TRIGGER `wa3`.`UpdateCreditOrDebitNotNull` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NULL AND NEW.debit IS NULL)
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both be null';
  END IF;
END// -- second trigger

CREATE TRIGGER `wa3`.`InsertBothCreditAndDebit` BEFORE INSERT ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL)
    THEN
      SIGNAL SQLSTATE '45000'
         SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- third trigger

CREATE TRIGGER `wa3`.`UpdateBothCreditAndDebit` BEFORE UPDATE ON `transactions`
FOR EACH ROW BEGIN
  IF (NEW.credit IS NOT NULL AND NEW.debit IS NOT NULL)
    THEN
       SIGNAL SQLSTATE '45000'
          SET MESSAGE_TEXT = '\'credit\' and \'debit\' cannot both have a value';
  END IF;
END// -- fourth trigger

DELIMITER ;

SHOW TRIGGERS;


Refactoring out a boolean column I have a Pets table (cats, dogs, you name it) with over a million records.  For simplicity sake, it looks like the following:

Pets
Field   Type            Null    Key
id      int(11)         NO      PRI
name    varchar(256)    NO

For the front page on my site, I want to show off 5 random pets to display every month.

I was considering adding a new boolean column to the Pets table for identifying the 5 Pets I'll be showing each month.  But!  I'd have hundreds of thousands of records with false column values, just so that I could have 5 records with true values; which my gut was telling me, doesn't sound very normalized.

So!  I was wondering if it might make sense to refactor the selected records into a new table instead, like below.

PetDisplay
Field   Type            Null    Key
id      int(11)         NO      PRI
pet_id  int(11)         NO      MUL  <<< Foreign key to Pets Table

That way, the new table could just track the 5 selected records, instead of having a column with hundreds of thousands of empty values.  Does that make sense?  Would this actually perform better, or would it be an unnecessary abstraction?  Are there any general database rules around making refactoring decisions like this?

Thanks y'all!

Putting the flag directly in Pets is still normalized. It might not be high-performance, though.

Pulling the five rows into a separate store will undoubedly perform better. Is it normalized? Well, there are a great many normal forms, one of which puts each non-key attribute in its own table. So yes, its normalized. If you put non-key columns in the new table then you risk update anomolies, which means that table is no longer normalized. I'd wear that cost for the simplified run-time.

What other alternatives exist? An index involving the flag column may help at the cost of extra work for all writes. A filtered index or materialised view would be better, if only MySQL supported them.


Possible to run multiple Reporting Services (SSRS) instances on different machines with databases on same machine? With SQL Server 2012, can SSRS instances be setup on two different machines, with both instances using the same machine for their SQL Server databases?

Machine 1

SQL Server 2012 Database Server
SSRS instance 1

Machine 2

SSRS instance 2 pointed to machine 1's database server

There's this document describing installing multiple instances in 2005, but on the same machine. Can you point me to a better resource for our design? Is this still supported on 2012?  How are the SSRS databases (ReportServer, ReportServerTempDB) created for the 2nd instance?

Yes, you can have several Report Servers installed on a single SQL Server.  As you noted, the multi-instance support on a single machine is available, but not what you are after.

For multiple distinct ReportServers on different servers, but using a single database, you simply have to configure this in the Reporting Services Configuration Manager.

As noted by GShenanigan this is described in the Scale Out Deployment. http://technet.microsoft.com/en-us/library/ms159114.aspx (same link)

In that phase of installing a Report Server, you must choose the database server that you want to use.  Then you can separately use the RS Configuration Manager to either choose the existing ReportServer datbase or choose a new database named to whatever you want to call it.

When a new database is set up, the ReportServerTempDB will be named similarly.  For example, creating ReportServerHQ would have a ReportServerHQTempDB also created.


Full Text search returns duplicate keys I have a search like the example below. The problem is that the result returns a key muliple times if the term "beach" is in myTextColumn multiple times, for example "I went to the beach because the beach is cool". I need it to be returned only once.

  SELECT DISTINCT
    Src.[Key] AS MyID
FROM
    CONTAINSTABLE(MyTable, myTextColumn, N'FORMSOF(INFLECTIONAL,beach )',500) Src
ORDER BY
    MyID

It turns out that the problem was cause by a bad full text search index. It had to be rebuild with do not track changes, and the correct word breaker for the given language.


Create smaller row from alias and preserve column names Using Postgres:

SELECT users."name" AS "name"
    , array_to_json(array_agg(sites)) as sites
FROM remodel.users AS users
JOIN remodel.user_sites AS user_sites
    ON users.id=user_sites.user
JOIN remodel.sites AS sites
    ON sites.id=user_sites.site
GROUP BY "users".id
;

currently produces

"Toby";"[{"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
         {"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"}]"
"Amy";"[{"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"
"Anne";"[{"id":2,"name":"Manor","created":"2015-08-10T15:22:43.614551"},
         {"id":1,"name":"Village","created":"2015-08-10T15:22:36.622298"},
         {"id":3,"name":"Park","created":"2015-08-10T15:22:48.810872"}]"

however I would like not to have the "id" field in the JSON output.

changing the sites selection to

array_to_json(array_agg(row(sites."name", sites.created))) as sites

causes the fields to lose their names

"[{"f1":"Village","f2":"2015-08-10T15:22:36.622298"},
  {"f1":"Manor","f2":"2015-08-10T15:22:43.614551"}]"

and attempting (what would be a horrible) sub selection

, array_to_json(array_agg((SELECT "name", created FROM sites))) as sites

understandably throws an

  ERROR: relation "sites" does not exist

And removing the id with a select in the from clause prevents joining the tables.

I'm looking to preserve the column information as well as I can,  not just for conversion to JSON but for inclusion in much larger and complex queries,  so performing the array selection without using JSON is preferred.

Unfortunately, a ROW() expression does not preserve column names. Use a subselect instead.
And json_agg() is simpler and faster for the task:

SELECT u.id, u.name
     , json_agg((SELECT x FROM (SELECT s.name, s.created) x)) AS sites
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON s.site = us.id
GROUP  BY u.id;  -- id must be the PK

The question has been answered before - with detailed explanation and links:

Select columns inside json_agg

To preserve column names in a plain array (not JSON or hstore), you need to use a registered row type for the row, else column names are lost in any case.
Either you already have a matching row type, or you register one (temporarily):

CREATE TEMP TABLE tmp_site(name text, created timestamptz)  -- use your data types!

Then:

SELECT u.id, u.name
     , array_agg((s.name, s.created)::tmp_site) AS sites  -- or whatever you do with s
FROM   remodel.users      u
JOIN   remodel.user_sites us ON us.user = u.id
JOIN   remodel.sites      s  ON us.site = s.id
GROUP  BY u.id

Details in the linked answer.


Is it possible to get the last restore date of a MySQL instance? Is it possible to get the last restore date of a MySQL database?

I am going to restore the database from a .sql dump file, but I don't know how to get from the database, the last time when a restore happened.

I tried to query the information_schema.tables table in order to check the creation_time of each table, but they seem to be not updated after the restore.

I've just tested your solution about the "CREATE_TIME" field in information_schema and I think it's the good one, look :

[root@localhost] [MFO] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-03-05 15:15:22
    UPDATE_TIME: 2013-03-05 15:17:58
     CHECK_TIME: 2013-03-05 15:18:28
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.01 sec)

I did a mysqldump of MFO DB:

predator:~# mysqldump -uroot -p --databases MFO >/tmp/MFO.SQL

Then loaded it:

predator:~# mysql -uroot -p </tmp/MFO.SQL

Now the CREATE_TIME is today:

predator:~# mysql -uroot -p
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 86261
Server version: 5.0.51a-24+lenny5 (Debian)

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

[root@localhost] [(none)] mysql> select * from information_schema.tables where table_name = "myisammfo"\G
*************************** 1. row ***************************
  TABLE_CATALOG: NULL
   TABLE_SCHEMA: MFO
     TABLE_NAME: myisammfo
     TABLE_TYPE: BASE TABLE
         ENGINE: MyISAM
        VERSION: 10
     ROW_FORMAT: Fixed
     TABLE_ROWS: 2
 AVG_ROW_LENGTH: 7
    DATA_LENGTH: 14
MAX_DATA_LENGTH: 1970324836974591
   INDEX_LENGTH: 1024
      DATA_FREE: 0
 AUTO_INCREMENT: NULL
    CREATE_TIME: 2013-10-02 16:45:56
    UPDATE_TIME: 2013-10-02 16:45:56
     CHECK_TIME: NULL
TABLE_COLLATION: latin1_swedish_ci
       CHECKSUM: NULL
 CREATE_OPTIONS:
  TABLE_COMMENT:
1 row in set (0.00 sec)

Best regards.

Max.


Can SQL Server replication be used for Point-of-Sale (POS)? We are looking for a solution that will synchronize the databases of our POS.

The scenario says we have 3 POS with SQL Server Express installed on each machine.

We want these databases to be in-sync at all times. Let's say POS A goes down, then it will resync as soon as it is up. Also, while POS A is down POS B and C should still work and continue to sync their data.

Is SQL Server replication the right solution? If yes, what type of replication?

Updated

No central database
3/4 nodes/POS are down, remaining POS will still work
Databases will be restored if POS is online
Pretty much we are think like MongoDb replica set
Database should be updated as much as possible

You have 3 types of replication:

Snapshot
Transactional
Merge

Reference Link - Selecting the Appropriate Type of Replication

Per Microsoft, these are easily broken down into two broad categories:

Server to Server
Server to Client

In the scenario of Point of Sale (POS). You are recommended to use Merge Replication due to it's Server to Client category and scenario.

Reference Link - Merge Replication

  Merge replication, like transactional replication, typically starts with a snapshot of the publication database objects and data. Subsequent data changes and schema modifications made at the Publisher and Subscribers are tracked with triggers. The Subscriber synchronizes with the Publisher when connected to the network and exchanges all rows that have changed between the Publisher and Subscriber since the last time synchronization occurred.

Reference Link - Consumer Point of Sale (POS) Applications

POS applications typically have the following characteristics, which an appropriate replication solution must address:

Most data is entered and updated at the remote sites.
Remote users must be able to make updates independently, without
requiring a connection to the central site.
Data updated at a remote site is not updated at any other sites;
therefore conflicts typically do not occur.
Some data should only be updated at the central site; for example,
data in product description tables.
Users synchronize data at scheduled times (such as the end of the
business day).
The application must control how long a remote site can remain
unsynchronized.
Some tables require filtering so that each store receives different
data for one or more tables. For example, each store receives
information only for products it stocks.
The application might require custom business logic to be executed
when data is synchronized.
The application might require that data be synchronized over the
Internet rather than through a dedicated connection.

Hope that helps!


An ISO compliant query that will run on all major RDBMS without change - although the performance will not scale to large datasets.

    select a.tournamet, a.board, a.player, a.score,
           (
           (select count(*)
              from data b
             where b.tournamet=a.tournamet
               and b.board=a.board
               and b.score>a.score)
           + 1 + total -
           (select count(*)
              from data c
             where c.tournamet=a.tournamet
               and c.board=a.board
               and c.score<a.score)
           )/2 rank,
           total
      from data a
      join (select tournamet, board, count(score) total
              from data x
          group by tournamet, board) y
                on y.tournamet=a.tournamet
               and y.board=a.board
  order by tournamet, board, rank, player;

For MySQL specifically, subqueries are not allowed in the FROM clause, so the query is slowed down even more by putting the total count into yet another scalar, SELECT-level query:

select a.tournamet, a.board, a.player, a.score,
       (
       (select count(*)
          from data b
         where b.tournamet=a.tournamet
           and b.board=a.board
           and b.score>a.score)
       + 1 +
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board)
       -
       (select count(*)
          from data c
         where c.tournamet=a.tournamet
           and c.board=a.board
           and c.score<a.score)
       )/2 rank
  from data a

And below is a specific MySQL solution that only requires 2 table scans.  Some have mentioned that multiple MySQL variables at the same level doesn't always work, but it's worked everywhere I've tried, so I would be interested to see where it breaks someone has an example.

select d.tournamet, d.board, d.player, d.score, z.rank
from
(
  select tournamet, board, score, avg(rank) rank
  from
  (
    select *,
           @r := if(@t=tournamet and @b=board, @r+1, 1) rank,
           @t := tournamet,
           @b := board
    from (select @t:=null, @b:=null, @r:=0) x, data
    order by tournamet, board, score desc
  ) y
  group by tournamet, board, score
) z
join data d
  on d.tournamet = z.tournamet
 and d.board = z.board
 and d.score = z.score
order by d.tournamet, d.board, d.score desc

The derived table in the middle is a classic ranking query, which
1. resets the rank for each group (tournamet/board); and
2. increases the rank for each record within the group
The ORDER BY in this query causes MySQL to process the records and output the ranking in the right sequence.

The achieve your averaged ranking, we need to take this query and further create averages. Finally, the aggregated data is JOINed back to the main table again to properly attribute the ranking to the original table rows.  The final ORDER BY is added to display the data in a logical sequence.

Structure and some sample data

create table data (
  tournamet int, board int, player int, score int);
insert into data values (
    1      ,1,    2,       990),(
    1      ,1,    4,       980),(
    1      ,1,    6,       480),(
    1      ,1,    7,       450),(
    1      ,1,    9,       980),(
    1      ,1,   10,       510),(
    1      ,2,    2,       140),(
    1      ,2,    4,       110),(
    1      ,2,    6,       140),(
    1      ,2,    7,       110),(
    1      ,2,    9,        50),(
    1      ,2,   10,       120),(
    2      ,1,    3,       150),(
    2      ,1,    5,       150),(
    2      ,1,    7,       150),(
    2      ,1,    8,       130),(
    2      ,1,   10,       100),(
    2      ,1,   11,       400);

SQL Fiddle


Duplicate Inserted Row into the new table when running trigger I create 2 tables like this:

CREATE TABLE task
(
 task_name TEXT NOT NULL,
 duration INT,
 UNIQUE(task_name)
);
CREATE TABLE temptable
(
 task_name TEXT NOT NULL,
 duration INT  NULL,

);

Then I insert one row in 'task' table.

INSERT INTO public.task(
    task_name, duration)
    VALUES ('task1', 10);

I want to insert a new same row into temptable table every time I add new record into task table
So I create a trigger to insert automatically data in 'temptale' at the same time

CREATE OR REPLACE FUNCTION update_temptable()
RETURNS TRIGGER AS
$$
BEGIN
    -- insert temporary row
    INSERT INTO temptable(task_name, duration)
    SELECT NEW.task_name , NEW.duration FROM task;
   RETURN NEW;
END
$$
LANGUAGE 'plpgsql';
/* Create trigger call function*/
CREATE TRIGGER ins_new_record
    AFTER INSERT ON task
    FOR EACH ROW
    EXECUTE PROCEDURE update_temptable();

But when I insert a new row ('task 2',10) into 'task' table, the records inserted into 'temptable' table always duplicated like this.

task_name|duration|
|"task2" |10      |
-------------------

|"task2" |10      |

I don't know how to fix it. Can anyone help me? Thanks.

The statement in your trigger:

INSERT INTO temptable
   (task_name, duration)
SELECT NEW.task_name , NEW.duration
FROM task;

will insert as many rows into temptable as there are currently in task. The SELECT returns one result row for each row in task, and since the SELECT list contains only constants, all these result rows will look the same.

You want to insert a single row, so you should use

INSERT INTO temptable
   (task_name, duration)
VALUES (NEW.task_name , NEW.duration);


How to join to a single list of IDs from two different tables I have worked with T-SQL in multiple DBs, but I have never designed a database before. I am designing a new database in MySQL which will track humans and organizations, including financial transactions received from them. I would like to easily be able to treat them as one group for the purpose of joining to other tables, such as tables tracking charges applied to their balances, payments made by them, and so on.

I have seen other databases track both humans and organizations in a single table, but since they are so unlike, this always seemed sloppy to me. For example, a human should have a last name that is NOT NULL, but an organization has no last name, so you end up needing a column just to track whether it's a human or an organization and then code to enforce rules based on that column's value. Querying from such tables also requires more code.

I would like to create a human table with the PK being human.id_number and an organization table with the PK being organization.id_number. I want to then somehow funnel all of the id_numbers into a third table, let's just call it constituent for this question. I then want to join other tables in the DB to constituent, not to human or organization. For example, some constituent primary key, maybe constituent.id_number, would join to payment.constituent_id_number.

The problem is that I'm not sure how to funnel human.id_number and organization.id_number into a single table. I have two questions:

Is my method a good choice? Is there a better option I should use?
How can I accomplish what I've described above?

You contradict yourself.  First you state you would like to treat humans and organization as one group and then you state using one table would cause queries to require more code.

Three tables with one master:
With two tables rather than funnel to, the constituentID is the master the [human].[id_number] and [organization].[id_number] would just be FK.  So you would insert into constituent first to get the master ID.  With this it would be a pain to enforce the same ID not in both [human] and [organization].  If you are going to put a type in constituent to enforce the two other tables then you might as well combine in the first place.

Two tables:
You could have two independent [human] [organization] tables and combine them with with a view with a union but that would not be my preference.

One table:
Not having a last name is not a big difference.  They both have address,  phone number, and EIN/SSN.  I would share name / last name and let first name be null.  A person is still valid without a first name so do you really need to enforce it.  ID is the PK and is an identity.  Type - person or organization is not part of the PK.   Maybe SSN/EIN as PK but then that would not allow for one constituent to have more than one account.  Then you can have views for person and organization.

In summary I don't think a null (enforced or not) is that big of a deal.  Compared to separate tables.


Postgres vs SQL Server Can anyone share proper advantages-disadvantages? If possible, I am trying to search for really great benchmarks between both, so please share it with me.

Also, which will be best between them for Transacts(I know SQL server is specially made for T-SQL, but even if you choose Postgres over SQL Server, then why?)? I am trying to experiment.

Bent Ozar has a good article about why benchmarking is rarely seen based on Dr. David DeWitt.

  BENCHMARK TESTING. You must obtain Microsoft’s prior written approval
  to disclose to a third party the results of any benchmark test of the
  software.

But, one is free and one isn't so there is a big difference to consider among the laundry list of others (IDE, HA/DR, Auditing, Server operating systems, Supported laguages, etc).

There are also some older posts on Stackoverflow comparing the two, with many folks providing their opinions.

GIS: PostGIS/PostgreSQL vs. MySql vs. SQL Server?
DB2 vs PostgreSQL vs SQL Server
Brent Ozar: Two Important Differences Between SQL Server & PostgreSQL

Dr. Dewitt also has a great video about query optimization. which is slightly relevant but more so just a great watch.


How to see the value of a variable being used in an sql statement during a server trace? I have a stored procedure which has SQL statements that take variables. For example,

update customer set cust_id = @newcust_id where issuedate < @transferdate and cust_id = @oldcust_id

The values of the variables are assigned earlier in the SP.

How do I show the values of these variables during a server trace?

For debugging purposes you can trace them explicitly into the Profiler using a custom event, via sp_trace_generateevent:

declare @tracedata varbinary(8000);
set @tracedata = cast(@transferdate as varbinary(8000));
exec sp_trace_generateevent 82,
        N'@transferdate',
        @tracedata );
set @tracedata = cast(@oldcust_id as varbinary(8000));
exec sp_trace_generateevent 82,
        N'@oldcust_id',
        @tracedata);

You need to modify your Profiler session to monitor for the user User-Configurable Event Class, otherwise you won't see the generated events. I would also comment out the call to sp_trace_generateevent in production once the debugging is done. The call is not critically expensive (specially if there is no monitoring listening for it), but it should be removed none-the-less.

Updated

The exec call doe snot allow for a CAST to be inlined in the parameter list. I modified the code to show how to cast it before the exec. The value will be in the BinaryData column, in HEX representation (so 'daf' will show up as 0x646166).


Is combining independent tables called normalisation? So, at workplace, we currently store our audit logs in different tables, depending on what it is e.g. login/access info, configuration changes, etc.

These are all independent data with no foreign key or any relationship whatsoever.  Some of the columns are similar, e.g. ID (obviously), datetime, name of user making the change, etc, while others are different.

Recently, I'm asked to combine all the tables into one, where the similar columns will be preserved, while the different columns will be in JSON, stored in a CLOB column.

I'm being told that this process is called "normalization" - we're converting the many tables into "normal forms".

Now, I'm not a database expert, but that doesn't seem to match what I've learned about normalization in my Intro to Database class.  Or am I just ignorant?

No, the task you are being asked to fulfill —arranging (a) columns that belong in two or more distinct base tables in (b) a single base table— is not called normalisation (nor normalization for those who write it so).
Regarding the relational model, crated by Dr. E. F. Codd, normalization is a science-based procedure performed by a designer at the logical level of abstraction of a database which involves:

decomposing, by means of first normal form, a relation (table) with one or more attributes (columns) configured with nonatomic1 domains (types) so that, having all the relevant domains (types) fixed as simple or atomic, data manipulation and constriction are much easier to declare via the expressive facilities of the data sublanguage (e.g., SQL) provided by the database management system (DBMS for brevity) of use; and

getting rid of undesirable dependencies among the attributes (columns) of a relation (table), by virtue of split-ups based on the successive normal forms, in order to avoid update anomalies.

Of course, the designer has to take into account the meaning carried by the relations (tables) and attributes (columns) under consideration. The aforementioned meaning is assigned in terms of a modeling exercise that takes place at the conceptual level of abstraction, depending on the particular informational characteristics and rules of the business environment of interest. It is there, at the conceptual level, where the modeler carries out an integration of the structural aspects (entity types and properties) that hold business-related direct associations. Those conceptual elements are subsequently represented in a logical-level layout by way of the relations (tables) and attributes (columns) mentioned above, and normalization assists in testing the soundness of the logical-level representation.
Denormalization, in turn, is as well a science-based procedure applied at the logical level where, basically speaking, a designer takes a relation (table) that meets a certain normal form and produces a relation (table) that is in the normal form that is immediately “under” the previous one; e.g., a relation (table) that was in third normal form underwent a recomposition to its second normal form, so this procedure is sequential. Denormalizing a relation (table) that complies with first normal form generates an unnormalized one.
Although, in certain contexts, the term “denormalization” is informally (and ufortunately) used to denominate the approach in which a designer decides to put up —in an ad hoc manner— a table that includes, e.g., “summary” columns that retain calculated values and/or columns that belong and are also comprised in other tables, but that is decidedly a course of action which is not associated with the —theory-based— normal forms of the relational model. The task you describe (i.e., mixing in the same table some columns standing for aspects that are not directly associated at the conceptual level) seems to be compatible with this informal (and unfortunate) use of the term “denormalization”, but note that, for the record, I highly suggest not to use it when referring to this sort of approach because it only adds ambiguities and confusions.

Endnote
1 Domain (which is somewhat similar to a type) atomicity has much to do with the way in which an attribute (column) of a relation (table) will be utilized in the concerning database. A nonatomic domain is one that comprises one or more sub-structures that have their own specific significance and require separate constraints and/or will be involved in data manipulation operations accessing value sub-parts enclosed in said sub-structures (e.g., SELECT or UPDATE operations with WHERE clauses incorporating DBMS functions —let us say, SUBSTRING()— to transgress domain atomicity by “touching” value sub-parts).
In his seminal 1970 paper entitled A Relational Model of Data for Large Shared Data Banks, Dr. Codd expounds examples of relations (tables) with domains that accept relations (tables) as values; in other words, the concerning relations (tables) contain “nested” relations (tables) in some of their attributes. In practice, this kind of domain entails columns set up with, let us say, a TABLE data type which, if not handled as atomic, involves evident complexity (recursive “nesting” of columns, constraints, rows and the corresponding operations). To free those relations from said complexity, he presents normalization, the procedure by virtue of which a designer breaks down nonsimple (i.e., nonatomic) domains into atomic ones, obtaining relations (tables) in a more convenient (first normal) form. It is worth to note that, in agreement with the prior paragraph, attributes (columns) defined with domains accepting relations (tables) as values are not the only ones that can be considered nonatomic.
In this way, if each of the columns taking part in the combination process that you mention will always be constrained and used in value manipulation as a single indivisible unit, i.e., never with respect to value sub-parts, then their types will be managed atomically; if not, such a combination process would yield unnormalized tables, i.e., tables that do not comply with first normal form and, hence, not with the further normal forms either, so genuine relational normalization would be called for.


Change server name for SQLLocalDB instance Is there any way to change the instance name of LocalDB of SQL Server 2012 from (LocalDB)\xxxx to just xxxx?

I need it for an application that needs SQL Server DB instance name to be MSSQLDEFAULT.

No, there is no way to remove the (LocalDB)\ prefix from LocalDB instance names. LocalDB, which is a user-process, operates differently than "regular" SQL Server which normally operates as an NT service. So, at least in terms of client connectivity libraries / software goes, that (LocalDB)\ prefix is actually functional: it directs the client to another, LocalDB-specific, DLL for the connectivity. That LocalDB-specific DLL handles things such as looking for the existence of a currently running process for the instance being requested, and if not present, then it will actually start that instance and then connect to it (end result being that it just works).


Design for CE Events / Training Database I'm working on a design for a continuing education training database.  Users will register for a CE course/event that could be one day or multiple days.  Courses are taught multiple times per year.
The part I wanted some review on is the calendar part of the DB.
Here is the design criteria:

Multiple events taught on varying schedules throughout the year.
Classes/Events can be part of a day, one day or multiple days.
Times can be different across days for the same Instance.  Ex. "Summer xyz Training" held 9am-5pm Monday, 9-noon Tuesday, etc.

Here is my design for this part of the DB so far:
Event
Class, event or course... can be scheduled multiple times... taught at various times, dates, and locations repeating throughout the year.

Event_ID - Could be called Class_ID. Could be class or event.
I went with Event_ID to handle either.
Name - Ex. "xyz Training"
Description -
Objectives - yes - hidden
Outline - yes - hidden
Duration_hours  ( Break up by Class Schedule )
Notes - Hidden from user

Event_Instance
Instance of an event.  Can be across multiple days.  Event_Instance_Schedule for the Instance must add up to Event.Duration_Hours

Event_Instance_ID
Location_ID - Tie to Location table for where (could be physical or on line)
Description - Ex.  "Summer xyz training"

Event_Instance_Schedule
Block of time as part of Event_Instance that adds to total Duration_Hours.

Event_ID
Event_Instance_ID -
Event_Instance_Schedule_ID
Instructor_ID?
Start_Date_Time - Ex. "1/1/2017 8am"
End_Date_Time -  Ex.  "1/1/2017 5pm"
Notes

I don't really need to setup Google Calendar type re-occurrence, thankfully.  My thinking was that the user chooses a class, gives that instance a title like "Summer xyz training", and then schedules times until they reach the Duration_hours number.
I think it's fairly simple. Just looking for feedback or "have you thought about this... " feedback, or "gotchas" I'm not considering.
I have reviewed a number of event / class schedule DB designs, but none quite fit what I'm doing...too much or too little.  The question is meant to be at a high level regardless of the DB technology (MySQL, SQL Server, etc.)
The DDL is the easy part in my mind, but I should probably do that based on a good design.  I just wondered if anyone had done an Event DB kind of thing before and if they had feedback based on my initial concept.

My wife is a nurse educator who had no even management platform. I developed one in a VERY similar fashion that you outlined here. The logic and thought process was the same.

You should be safe with this overall schema.


Should these two indexes suggested by the SSMS missing index feature be combined? I'm trying to improve the performance of a specific query that is automatically generated by Entity Framework.  I have run the query through SSMS, and it has suggested creating two missing indexes.  The table in question:

CREATE TABLE [dbo].[PackageEvents]
(
[EventID] [int] NOT NULL IDENTITY(1, 1),
[PackageID] [char] (24) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDescription] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[EventDate] [datetime] NOT NULL,
[UserName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Notes] [varchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[IsSynchronized] [bit] NOT NULL CONSTRAINT [DF_AmazonPackageEvents_IsSynchronized]
  DEFAULT ((0)),
[LastSyncDate] [datetime] NULL,
[Version] [timestamp] NOT NULL
)

SSMS has suggested the following two indexes:

CREATE NONCLUSTERED INDEX [IX_IsSynchronized] ON [dbo].[PackageEvents]
([IsSynchronized]) INCLUDE ([PackageID])

CREATE NONCLUSTERED INDEX [IX_Covering] ON [dbo].[PackageEvents] ([PackageID])
INCLUDE ([EventDate], [EventDescription], [EventID], [IsSynchronized], [LastSyncDate],
[Notes], [UserName], [Version])

I'm not posting the query I'm optimizing for because it is horrendous (generated by Entity Framework) and is almost unreadable.  In general the query is looking for any rows where IsSynchronized = 0 and returning those rows.

Is there a way to combine these two indexes into one index that would provide the same or better performance benefit?  Is this question impossible to answer without the exact query?

EDIT: The only existing index is the clustered index on the primary key, EventID.

I searched through the generated Entity Framework query.  The following where clause occurs 4 times, but always in the same form:

 WHERE  EXISTS (SELECT
            1 AS [C1]
            FROM [dbo].[AmazonPackageEvents] AS [Extent46]
            WHERE ([Project30].[PackageID] = [Extent46].[PackageID]) AND
           (0 = [Extent46].[IsSynchronized])
        )

Which seems to boil down to

WHERE PackageID=@PackageID AND IsSynchronized=0

Well, you could consider a filtered index - if you're always looking for rows where IsSynchronized = 0 and this number should be relatively small, then instead of those two indexes, consider this instead:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized]
  ON [dbo].[PackageEvents] ([PackageID])
  INCLUDE ([EventDate], [EventDescription], [EventID],
    [LastSyncDate], [Notes], [UserName], [Version])
  WHERE IsSynchronized = 0;

Of course you may want to make that even smaller and test to see the difference in impact if the query has to look up the data (should be pretty efficient if the number of rows is small), so - assuming PackageID is the clustering key:

CREATE NONCLUSTERED INDEX [IX_NotSynchronized]
  ON [dbo].[PackageEvents] ([PackageID])
  WHERE IsSynchronized = 0;

The overhead of maintaining this index may very well be worth the space savings compared to a full index, especially if it's only being used to optimize this query (or query pattern, at least).

Filtered indexes are not magic, though; JNK brought up some limitations below:

  Caveats with filtered indexes - stats may not stay up to date without maintenance, and you need to use "standard" values for some settings like QUOTED IDENTIFIER and ANSI NULLS. These are small issues but if you have the settings wrong in a session that inserts into the index, the insert will fail.

Also you'll want to read these posts:

How filtered indexes could be a much more powerful feature
Optimizer Limitations with Filtered Indexes
An Unexpected Side-Effect of Adding a Filtered Index

If you don't want to use a filtered index, you can probably test variations of these:

CREATE NONCLUSTERED INDEX [IX_Covering_try1] ON [dbo].[PackageEvents]
  ([PackageID], IsSynchronized)
INCLUDE ([EventDate], [EventDescription], [EventID],
  [LastSyncDate], [Notes], [UserName], [Version]);

CREATE NONCLUSTERED INDEX [IX_Covering_try2] ON [dbo].[PackageEvents]
  (IsSynchronized, [PackageID])
INCLUDE ([EventDate], [EventDescription], [EventID],
  [LastSyncDate], [Notes], [UserName], [Version]);

(For a long time I thought that including BIT columns in the key was wasteful but Martin Smith demonstrated a case where it worked quite well - worth a try. I can't find the post now.)

Without your full schema, data, query patterns etc. we can only guide you and have you test our suggestions in your environment. We can't say, "Ding! This is the one that will work for you!"


Avg write latency spikes for disk more than usual. Need help finding the culprit We recently had increased in disk write latency reported. Though from our monitoring team it was not confirmed from which drive but doing some dmv query I see tempdb around avg 32 ms wait on write. Also over the week tempdb usage I mean available free space has come down under 50 GB from 300 GB.
I am suspecting this spike could be from tempdb. The environment is a mix of OLTP and warehouse people querying data for large select when looking for real time data in case not available from LS standby.
My question is how can I check what in tempdb if it is would have caused this spike?
Also can those select queries also contribute to tempdb write latency? These select queries are run on tables over TBs of data and user can have part of queries like order by and group by from what I have seen. But I am not sure how to prove they are contributing to sudden spike.

My question is how can I check what in TempDB if it is would have
caused this spike?

To analyze what impact those queries have on tempdb you could use sp_whoisactive since one of the info it outputs is the use of tempdb:

The most confusing of these columns are those related to tempdb. Each    of the columns reports a number of 8 KB pages. The
[tempdb_allocations] column is collected directly from the
tempdb-related DMVs, and indicates how many pages were allocated in
tempdb due to temporary tables, LOB types, spools, or other
consumers. The [tempdb_current] column is computed by subtracting the
deallocated pages information reported by the tempdb DMVs from the
number of allocations. Seeing a high number of allocations with a
small amount of current pages means that your query may be slamming
tempdb, but is not causing it to grow. Seeing a large number of
current pages means that your query may be responsible for all of
those auto-grows you keep noticing.

Another way to show that you have a tempdb related problem is having sp_BlitzCache™ Result: TempDB Spills. As that page says:

To find the queries that are causing spills, you have several options
– you can capture and examine actual execution plans, or you can
collect exchange spill events through extended events. Paul White
covers your options in his reply to What frequency of hash/sort
spills into tempdb is
concerning?

Also can those select queries also contribute to TempDB write latency
. These select queries are run on tables over TBs of data and user can
have part of queries like order by and group by from what I have seen.

Certainly yes. If you check the tempdb database doc, you'll see it stores the following objects:

Internal objects that the database engine creates. They include:
Work tables to store intermediate results for spools, cursors, sorts,    and temporary large object (LOB) storage. Work files for
hash join or    hash aggregate operations. Intermediate sort
results for operations    such as creating or rebuilding indexes (if
SORT_IN_TEMPDB is    specified), or certain GROUP BY, ORDER BY, or
UNION queries.


Want to combine two rows into one row Result right now I am getting is:

EmployeeNo  Column1   Column2
================================
1           |  NULL       | Column2
1           |  Column1    |   NULL

I want like

EmployeeNo  Column1   Column2
================================
1          | Column1  |   Column2

Kindly provide any help or way to do this

Thanks in advance

The simplest way to merge rows is with an aggregate function such as MIN/MAX. These functions will ignore nulls (see MSDN) and can operate similarly to ISNULL/COALESCE with aggregation. For example:

SELECT
  EmployeeNo,
  MAX(Column1) AS Column1,
  MAX(Column2) AS Column2
FROM MyTable
GROUP BY EmployeeNo

This will return the sample desired results. If your only goal is to merge columns with null values into rows with data in those columns, it doesn't matter whether you use MIN or MAX - the result set will be the same.


Is there a configuration setting for how many pages ANALYZE will scan? Is there a setting that controls how many records/pages are scanned by the PostgreSQL ANALYZE command? It looks like by default it scans 30,000 pages and 30,000 records. The PostgreSQL Server Configuration Documentation lists tons of options, but I don't see anything specific to ANALYZE.

Each column of a table has an attstattarget property (stored in pg_attribute) that tells how much data should be stored for it from the statistical sample gathered by ANALYZE.

It defaults to default_statistics_target, which itself defaults to 100.

In Statistics Used by the Planner, the doc says:

  The amount of information stored in pg_statistic by ANALYZE, in
  particular the maximum number of entries in the most_common_vals and
  histogram_bounds arrays for each column, can be set on a
  column-by-column basis using the ALTER TABLE SET STATISTICS command,
  or globally by setting the default_statistics_target configuration
  variable

The reason behind the 30,000 pages and rows is that the sample size in rows considered by ANALYZE is 300 times the maximum value of attstattarget for the sampled table, which would be the default 100.

The 300 comes from a statistical formula mentioned in the source code
src/backend/commands/analyze.c:

    /*--------------------
     * The following choice of minrows is based on the paper
     * "Random sampling for histogram construction: how much is enough?"
     * by Surajit Chaudhuri, Rajeev Motwani and Vivek Narasayya, in
     * Proceedings of ACM SIGMOD International Conference on Management
     * of Data, 1998, Pages 436-447.  Their Corollary 1 to Theorem 5
     * says that for table size n, histogram size k, maximum relative
     * error in bin size f, and error probability gamma, the minimum
     * random sample size is
     *      r = 4 * k * ln(2*n/gamma) / f^2
     * Taking f = 0.5, gamma = 0.01, n = 10^6 rows, we obtain
     *      r = 305.82 * k
     * Note that because of the log function, the dependence on n is
     * quite weak; even at n = 10^12, a 300*k sample gives <= 0.66
     * bin size error with probability 0.99.  So there's no real need to
     * scale for n, which is a good thing because we don't necessarily
     * know it at this point.
     *--------------------
     */
    stats->minrows = 300 * attr->attstattarget;

As for the number of pages, since rows don't span across pages, at most N pages are going to be read to get N rows. I believe that ANALYZE intentionally aims at fetching this maximum of pages to get the best sample. It makes sense since rows stored in a same page are more likely to be correlated.


Why does MySQL Workbench let me add unique indexes to primary keys? I'm using MySQL Workbench 8.0.12
Reproduce:

Create a table
Create the first column id, select it as a primary key (PK)
Under indexes, PRIMARY appears
Back under columns, also select id as unique (UQ)
Under indexes, id_UNIQUE appears

Confusion:

From reading MySql - Is primary key unique by default?, I understand that the primary key implies uniqueness by default. This is in line with how the guy in this video skips the UQ after checking the PK. Finally, this answer confirms having both PK and UQ on the same column is redundant:

If you declare a column as UNIQUE as well as PRIMARY KEY, MySQL (Workbench?) oddly creates a second index over the same columns. This is truly redundant and only takes up extra storage space and causes a bit of overhead when you insert/update/delete rows. It doesn't give any benefit.

If this truly is detrimental for performance, and from my previous experience with MySQL Workbench double-checking and cross-referencing whatever I had been doing, I would imagine the developers should have made it fool-proof by now to not let users select UQ on top of PK. For example, communicate by disabling the UQ checkbox on a column already having the PK selected. Since they didn't, is it because there could be a scenario where someone may actually need both PK and UQ on the same column?

To make this more confusing, this (upvoted) answer says:

Use UNIQUE CONSTRAINT to state a fact. Use UNIQUE INDEX when you have an index which happens to be unique, for instance because you add the primary key to it.

Which implies that these are two independent states. However, that contradicts the MySQL Workbench behaviour, where checking UQ on a column will automatically create an index, and if you go ahead and delete that index, it automatically deselects the UQ.

So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

Answer compiled from comments by:
Akina:
No reason for to restrict index creation when the statement is syntactically correct. The most common issue - CREATE TABLE t (id SERIAL PRIMARY KEY); which creates both UNIQUE index (due to SERIAL definition) and PRIMARY KEY (due to explicit specifying). So excess UNIQUE key must be removed by an additional statement.

To make this more confusing, this (upvoted) answer says:

This question deals with SQL Server and cannot be applied to MySQL. Check such moments carefully.

So could there be edge cases, or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

If you have PK then its expression is always unique, and you do not need in "think about UQ on a column that's already PK". Additionally you may be sure that none column mentioned in PK expression definition is NULL (by default all such columns are defined as NOT NULL implicitly if there is no explicit option in column definition - check SHOW CREATE TABLE for this, fiddle).

...and Vérace:

or should I just cement in my memory to never think about UQ on a column that's already PK, and move on?

Yes, move on! Typically a UNIQUE index is for a natural primary key - and your PRIMARY KEY is for a surrogate. Purists aren't too keen on this approach, but it's more or less an industry standard.
There are cases where having a surrogate slows down the system.  A prime example is with many-to-many mapping tables where a composite PK is demonstrably better.
Just to add a word - the UNIQUE index for a natural primary key should include a NOT NULL for the relevant fields - one can have a UNIQUE index with NULL fields - because each NULL is neither equal to nor not equal to any other NULL or any other value. Take a look at this fiddle.
Would it be good Workbench were to suggest or enforce "best practice"?  If you think so, file it as an enhancement at bugs.mysql.com
Another "suggestion":  INDEX(a) is redundant and wasteful if you also have INDEX(a,b).
A contrived use case:  SELECT id FROM tbl; would be more efficiently executed by scanning the UNIQUE index's BTree instead of scanning the PRIMARY KEY's BTree.  The former contains only id, the latter contains all the columns of the table.  (If you tack on a WHERE clause the references any other columns, then the PK's BTree will be the optimal.)


Order of Columns in Index I have following query which has ON clause on different column, Where clause on different column and Selection on different columns so my question is what should be Order of Columns in Index as according to my knowledge order of columns matter.

 SELECT     AA.[Column7],
            AA.[Column2],
            AA.[Column3],
            ISNULL(AA.[Column23], 0) AS [Column23],
            BB.[Column2] + '. ' + BB.[Column3] + ', ' + BB.[Column4] AS UserName,
            AA.[Column1],
            AA.[Column4],
            AA.[Column5],
            AA.[Column6],
            AA.[Column8],
            ISNULL(AA.[Column9], 0) [Column9],
            ISNULL(AA.[Column10], 0) [Column10],
            ISNULL(AA.[Column11], 0) [Column11],
            ISNULL(AA.[Column12], 0) [Column12],
            AA.[Column13],
            AA.[Column14],
            AA.[Column15],
            ISNULL(AA.[Column19], '') [Column19],
            CC.[Column1]
    FROM    Table1 AA
            INNER JOIN Table2 BB ON AA.[Column6] = BB.[Column1]
            LEFT JOIN Table3 CC ON AA.[Column22] = CC.[Column1]
    WHERE    AA.[Column5] = @Vailable1
    ORDER BY AA.[Column2] DESC,
             AA.[Column1] DESC

So far I have tried below strategy but I am not sure whether it’s according to best practice or not.

CREATE NONCLUSTERED INDEX [IX_IndexName] ON [dbo].[Table1]
(
    [Column5] ASC,
    [Column2] DESC,
    [Column1] DESC,
    [Column22] ASC
)
INCLUDE (
            [Column3],
            [Column4],
            [Column6],
            [Column7],
            [Column9],
            [Column10],
            [Column11],
            [Column12],
            [Column13],
            [Column14],
            [Column19],
            [Column23],
            [Column15],
            [Column8]
       )

EDIT:
During research on order of columns in index I found a link where there is a statement

  Very often you'll find that a better index is on (MostSelective)
  include (SecondMost, Least) or on (MostSelective, SecondMost) include
  (Least)

How do I judge what are MostSelective columns, SecondMost and Least? Is this depending on data?

Columns specified in WHERE and JOIN clause predicates are the candidates for index keys.  ORDER BY columns are also candidates if it can avoid an expensive SORT operator for many rows.  Columns specified only in the SELECT clause are candidates for non-clustered index INCLUDED columns when the cost of the additional storage and maintenance outweigh the elimination of the key lookup in the plan.  This may be the case for critical queries that is executed frequently.

Selectivity depends entirely on the data (i.e. high COUNT(DISTINCT column) compared to number of rows in table). The generality about most selective columns first is true when all of the keys are used seek predicates in the same index seek operator.  You might find separate indexes more useful than a single composite index.

Generally speaking, indexing is most optimal when only those rows actually needed by the query are touched and expensive operators (e.g. large SORTs) are avoided.  Tradeoffs are often involved.


Difference between two recordsets I'm looking to calculate the difference between two record sets in MySQL.

Say I have the following table:

colname type attrbutes
id      uInt PK
date    date PK
count   uInt

With this data:

id   date      count
1    2000-1-1  15
1    2000-2-1  20
2    2000-1-1  20
2    2000-2-1  30

I'm trying to get the following result (if the user selects dates 2000-1-1 and 2000-2-1):

id  count
1   5
2   10

What is a good way to do this?

Using the same table structure and data

use test
DROP TABLE IF EXISTS test_dba;
CREATE TABLE `test_dba` (
  `id` int(11) DEFAULT NULL,
  `date_en` date DEFAULT NULL,
  `count_rec` int(11) DEFAULT NULL
) ENGINE=InnoDB;
INSERT INTO test_dba VALUES
(1,'2000-01-01',15),(1,'2000-02-01',20),
(2,'2000-01-01',20),(2,'2000-02-01',30),
(3,'2000-01-01',20),(3,'2000-02-01',17);
SELECT * FROM test_dba;

Here is that data loaded

mysql> use test
Database changed
mysql> DROP TABLE IF EXISTS test_dba;
Query OK, 0 rows affected (0.19 sec)

mysql> CREATE TABLE `test_dba` (
    ->   `id` int(11) DEFAULT NULL,
    ->   `date_en` date DEFAULT NULL,
    ->   `count_rec` int(11) DEFAULT NULL
    -> ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.41 sec)

mysql> INSERT INTO test_dba VALUES
    -> (1,'2000-01-01',15),(1,'2000-02-01',20),
    -> (2,'2000-01-01',20),(2,'2000-02-01',30),
    -> (3,'2000-01-01',20),(3,'2000-02-01',17);
Query OK, 6 rows affected (0.20 sec)
Records: 6  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM test_dba;
+------+------------+-----------+
| id   | date_en    | count_rec |
+------+------------+-----------+
|    1 | 2000-01-01 |        15 |
|    1 | 2000-02-01 |        20 |
|    2 | 2000-01-01 |        20 |
|    2 | 2000-02-01 |        30 |
|    3 | 2000-01-01 |        20 |
|    3 | 2000-02-01 |        17 |
+------+------------+-----------+
6 rows in set (0.00 sec)

mysql>

Please note that I added id 3 to demonstrate negative differences

Here is my proposed query

SELECT
    id,ABS(diff) diff,
    IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
FROM
(
    SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    C.count_rec - B.count_rec diff
    FROM
    (
        SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
        WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ) A
    INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
) AA;

Notice I used the following:

MAX() / MIN() on the dates rather than on the counts
ABS() to get absolute value of difference

Here is the output

mysql> SELECT
    ->     id,ABS(diff) diff,
    ->     IF(diff>0,'Increase',IF(diff<0,'Decrease','NoDifference')) delta
    -> FROM
    -> (
    ->     SELECT A.id,B.count_rec firstcount,C.count_rec lastcount,
    ->     C.count_rec - B.count_rec diff
    ->     FROM
    ->     (
    ->         SELECT id,MIN(date_en) mindate,MAX(date_en) maxdate FROM test_dba
    ->         WHERE date_en BETWEEN '2000-01-01' AND '2000-02-01' GROUP BY id
    ->     ) A
    ->     INNER JOIN test_dba B ON A.id=B.id AND A.mindate=B.date_en
    ->     INNER JOIN test_dba C ON A.id=C.id AND A.maxdate=C.date_en
    -> ) AA;
+------+------+----------+
| id   | diff | delta    |
+------+------+----------+
|    1 |    5 | Increase |
|    2 |   10 | Increase |
|    3 |    3 | Decrease |
+------+------+----------+
3 rows in set (0.00 sec)

mysql>

Give it a Try !!!


What is the SQL Server equivalent for Oracle character set AL32UTF8? While porting data from oracle to Azure sql database I am facing the following issue where, empty data in Oracle is converted as ➞➞➞ in SQL Server
Pic1: Data as in Oracle DB;

Pic2: Data as in Sql server DB;

Pic3: Data when pasted in Notepad++.

The character set in Oracle is AL32UTF8 and that of target SQL Server database is SQL_Latin1_General_CP1_CI_AS. I have tried changing the datatype to NVARCHAR / NCHAR and changing the collation of the column to SQL_Latin1_General_CP850_CI_AS, but no solution. What is the reason for this difference and is there any solution?

NVARCHAR is always UTF-16.
You can try Latin1_General_100_CI_AS_SC_UTF8 with a VARCHAR column, but that doesn't look like an encoding problem. It really shouldn't matter what the source encoding / collation was as long as you correctly indicate the encoding used to transfer the data.
Either way, those 3 bytes / characters could be a bug in the import tool/process or the export tool/process. How are you transferring the data? If the image you are showing from Notepad++ is from the file being used to migrate the data and the field is supposed to be empty, either the export messed up, or perhaps the field in Oracle is not truly empty.


Does a DDL in Oracle affect other transactions? I understand that DDL statements in Oracle have an implicit commit before and after the statement, which obviously affects the current transaction.

However, does a DDL statement in any way affect other transactions running against that table (assuming the change is compatible with the DML running against the table)?

For example, if I decide to modify a varchar2(10) columnn to varchar2(20), or add a new column altogether, or drop an obsolete, no longer used column -- are other transactions going to be happy about it, or is there going to be any sort of contention or interruption of running transactions?

This article explains how DDL statements work and how they need exclusive table locks, which plays out nicely when there are transactions running against the table. To summarize:

If the table cannot be locked due to another transaction having a lock on the table

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

would be thrown. If the DDL manages to acquire the lock, obviously, any other transaction will block at the point where it needs a lock on the table, until the DDL finishes.

Additionally, since 11g, it is possible to enqueue DDL statements in FIFO order with other statements to get a lock on a table:

declare
begin
      -- We really want to drop that column and so wait for 1800 seconds.
      execute immediate 'alter session set ddl_lock_timeout=1800';
      execute immediate 'alter table t1 drop column n5 ';
exception
  when others then
     dbms_output.put_line(' No luck ');
     dbms_output.put_line(' SQL code'||sqlcode||','|| 'error '||sqlerrm );
end;
/

In older versions of Oracle, it could be a challenge to perform this task on a heavily used table.


Wrong sharding on mongoDB 3.0 I have a mongos with 2 shard servers (and 3 config servers)

sh.status()
--- Sharding Status ---
  sharding version: {
        "_id" : 1,
        "minCompatibleVersion" : 5,
        "currentVersion" : 6,
        "clusterId" : ObjectId("55ae2ddab9e8641e489ce39b")
}
  shards:
        {  "_id" : "shard0000",  "host" : "192.168.212.182:27018" }
        {  "_id" : "shard0001",  "host" : "192.168.212.106:27018" }
  databases:
        {  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
        {  "_id" : "Vision_1282",  "partitioned" : true,  "primary" : "shard0001" }
                Vision_1282.Group
                        shard key: { "groepNr" : 1, "valId" : 1 }
                        chunks:
                                shard0001       1
                        { "groepNr" : { "$minKey" : 1 }, "valId" : { "$minKey" : 1 } } -->> { "groepNr" : { "$maxKey" : 1 }, "valId" : { "$maxKey" : 1 } } on : shard0001 Timestamp(1, 0)
        {  "_id" : "db",  "partitioned" : false,  "primary" : "shard0000" }

I made the shard key as follows:

db.runCommand({ shardcollection : "Vision_1282.Group", key : {groepNr : 1, valId : 1}})

I insert about 10.000 records, where the groepNr is 1, 2, 3 or 4 and a valId can be between 1 and 4001.

When I look at the shardDistribution I see that shard001 only has records where groepNr = 1 and valId = 1.  All other records are in shard002.

I thought that the value behind the key value (here groepNr and valId) is 1 for ascending and -1 for descending.

PS. in PHP code, where I do the inserts I also ensure to use the index.
$collection->ensureIndex(array("groepNr" => 1 , "valId" => 1));

Did I do something wrong with creating the shardkey?

I have lowered the chunksize to 1 MB:

 { "_id" : "chunksize", "value" : 1 }

I inserted now about 10.000 documents.

If I look at the sh.status(), I see this:

Shard shard0000 at 192.168.212.182:27018
 data : 1.33Mb docs : 5761 chunks : 4
 estimated data per chunk : 343kb
 estimated docs per chunk : 1440

Shard shard0001 at 192.168.212.106:27018
 data : 1017kb docs : 4249 chunks : 3
 estimated data per chunk : 339kb
 estimated docs per chunk : 1416

Totals
 data : 2.33Mb docs : 10010 chunks : 7
 Shard shard0000 contains 57.41% data, 57.55% docs in cluster, avg obj size on shard : 243b
 Shard shard0001 contains 42.58% data, 42.44% docs in cluster, avg obj size on shard : 245b

Also I run the scripts AllChunkInfo("Vision_1282.Group"), with this result:

mongos> AllChunkInfo("Vision_1282.Group")
ChunkID,Shard,ChunkSize,ObjectsInChunk
Vision_1282.Group-groepNr_MinKeyvalId_MinKey,shard0001,83504,301
Vision_1282.Group-groepNr_"1"valId_"2000",shard0001,1920,8
Vision_1282.Group-groepNr_"1"valId_"2002",shard0001,974144,4012
Vision_1282.Group-groepNr_"2"valId_"1",shard0000,524384,2138
Vision_1282.Group-groepNr_"3"valId_"4",shard0000,498704,2031
Vision_1282.Group-groepNr_"4"valId_"4",shard0000,292800,1220
Vision_1282.Group-groepNr_"4"valId_"6",shard0000,113520,473
***********Summary Chunk Information***********
Total Chunks: 7
Average Chunk Size (bytes): 355568
Empty Chunks: 0
Average Chunk Size (non-empty): 355568

So you are right, the records were to small and the chunksize was to big.

Tx.


Running MySQL Backups to network location We have a MySQL server with several databases for development work and we want to back up some of the DBs to a NAS device.
These have been backed up previously but the backups have not worked for some time I have found.
There is a scheduled task in the Windows scheduler to run a batch script, this script should run the backup for the specified databases and save them to the NAS. When I run the task, I can see the folders and files being created on the NAS but the resulting backup file is always 0 KB so there is clearly something not working correctly.
I've queried the sizes of the DBs on the server and have seen that the DBs I'm trying to back up are not 0 KB.
I'm thinking it must be something in the script which is not working correctly so would appreciate some assistance.

del \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day1 /Y
rmdir \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day1 /S /Q
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day2 Day1
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day3 Day2
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day4 Day3
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day5 Day4
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day6 Day5
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day7 Day6
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day8 Day7
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day9 Day8
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day10 Day9
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day11 Day10
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day12 Day11
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day13 Day12
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day14 Day13
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day15 Day14
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day16 Day15
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day17 Day16
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day18 Day17
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day19 Day18
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day20 Day19
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day21 Day20
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day22 Day21
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day23 Day22
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day24 Day23
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day25 Day24
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day26 Day25
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day27 Day26
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day28 Day27
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day29 Day28
ren \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30 Day29
md \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30
mysqldump -u[user] -p[password] -databases [db1] [db2] [db3]> \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30\vmmysqldevw7.sql

rem Send mail to alerts.infrastructure on completion
mailsend -sub "Backup of VMmySQLdevW7 Completed" < InputFile.txt

The email alerts appear not to work either...
The "inputFile.txt" file contains the mail server's IP address, domain name, MySQL server's name and the alerts email address.

The problem was the missing double hyphen in front of the databases switch. The line in question should have read like this:

mysqldump -u[user] -p[password] --databases [db1] [db2] [db3]> \\[NAS_IP]\Backups\MySQL\VMmySQLdevW7\Day30\vmmysqldevw7.sql


Copy Remote Database MongoDB First off let me say I am a newB when it comes to Mongo, so excuse my ignorance. With that being said:

Using db.copyDatabase() to copy a remote database:

What impact does this have on the database being copied. Please note the the database being copied is currently being use by a live application.

Would db.copyDatabase() lock the database?

What about performance, would it make the database run slower?

I don't really mind if while coping some new data is being written to the DB. The copy does not have to be exact per say, it just need to be as close as possible to the live DB. But I am really concern about performance issue, is it safe to do this on a live DB?

The docs for MongoDB discuss the behavior of copyDatabase in detail:
  http://docs.mongodb.org/manual/reference/method/db.copyDatabase/#behavior

Specifically, copyDatabase reads the data from the source DB the same way something like a backup or full collection query would read it - that means that it may impact the performance of that DB (for example, by bringing more data into RAM which may displace some other data) but the impact would be about the same as running mongodump on that DB.

The docs do explicitly mention that the copied database does not get locked.

If you are running a replica set, you can copyDatabase from a secondary node


String comparison without CASE statements? Is there a shorter more terse way to do a string comparison in SQL Server without wrapping it in a CASE WHEN ... THEN 1 END? In PostgreSQL, I can do

SELECT c, c='foo'
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In SQL Server, I have to do

SELECT c, CASE WHEN c='foo' THEN 1 END
FROM ( VALUES ('foo'),('bar') ) AS t(c);

In the abstract, is there an easier more SQL-Servery way to throw an expression into boolean context? Or, is there a better way to write strcmp without rolling your own case statement?

I'm not sure that you'd call it shorter, but you could use the Logical Function IIF. From the docs,

  IIF is a shorthand way for writing a CASE expression. It evaluates the Boolean expression passed as the first argument, and then returns either of the other two arguments based on the result of the evaluation.

Per the example,

SELECT c, iif(c='foo',1,0)
FROM ( VALUES ('foo'),('bar') ) AS t(c);


query returns the same value multiple times I have following command :

select *
from Books
JOIN Keys ON Books.Bnumber = Keys.Bnumber
where (
    Books.Author1 like '%,%' and
    Books.Author1 like '%,John%' or (
        Books.Author2 like '%,John%' and
        Books.Author2 like '%,%'
    )
) and
    Name1 like '%' and
    Keys.Keyword like '%'
order by NAME1 ;

The query returns the same record multiple times.

The repeating parts in query (Books.Author1 like '%'...) are needed, for my application requires it. ( I think it should have nothing to do with my problem)

What could cause this?

The query returns the same record multiple times.

Variants:

one of source tables (or even both) contains duplicates;
the records are NOT duplicates, there exists at least one field (in a record pair which seems to be duplicates for you) where values differs.

  the table Keys contains multiple equal values of Bnumber, but that is required because 1 Book has multiple keywords.

It is variant 2.

You make a mistake while thinking that the records are duplicates. They differs - at least in Keyword field taken from Keys table.

You may "compress" the output obtaining one book per recodset by grouping and concatenating all keys into one field with keys list separated by comma:

SELECT Books.id,
       Books.Bnumber,
    /* another fields from Books table */
       GROUP_CONCAT(Keys.Keyword) Keys_list
FROM Books
JOIN Keys ON Books.Bnumber = Keys.Bnumber
/* all WHERE conditions which you need */
GROUP BY Books.id,
         Books.Bnumber,
      /* another fields from Books table */


Calculate values in two different ways - A or B? My question is about code and database design. How should I design my code to calculate simple variables (stored in sqlite db) like the balance of a wallet?

Example:
We have tables for customers, wallets, transactions. We have methods to fill these tables. When receiving money from a customer, we add the amount to the balance of a wallet and submit a transaction (date, amount, ...) into the transactions table.

I have 2 approaches to design my code and database - which one is more appropriate and bugfree?

A)
When doing a transaction I add / subtract the amount to my the balance of a wallet and submit a transaction into a table transaction with information like wallet_id, amount, date...

B)
I just submit the transaction to the transactions table and recalculate the balance of a specific wallet by checking for columns with the matching wallet_id and add up the wallet_balance from there.

Which is the right way? For me A) looks more prone to bugs and safe code requires more effort. I would go with B) which would be much easier then I would add more features like deleting transactions (+ recalculating balances) and so on.

What is your advice?

BEGIN;
INSERT INTO transactions ... (info about the transfer of funds)...;
UPDATE accounts SET balance = balance - $amt WHERE acct_id = $me;
UPDATE accounts SET balance = balance + $amt WHERE acct_id = $you;
COMMIT;

That guarantees that either all 3 actions happened, or none of them, even in the case of a power failure.

Yes, this violates the don't-store-redundant-data rule, but it is standard practice, and much more efficient.

Another accounting note:  Do not ever DELETE or UPDATE transactions, only INSERT more rows to fix problems -- this leaves an audit trail of what happened and when.


I can not find any document regarding to shared_heap_size I am new to Postgres and recently I ran one command

show all;

It returned me a csv file with three columns name, settings, description. There are more than 200 results and out of which one result is

name : shared_heap_size
settings : 1GB
description : Sets the size of the shared memory segment that is used
    for Aurora storage metadata and communications.

I could not find any document related to this. Can anyone please elaborate?

Output for select version();

PostgreSQL 9.6.3 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.9.3, 64-bit,

Based on the description, this is related to Amazon Aurora product, and not PostgreSQL by itself.

See https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Aurora.AuroraPostgreSQL.html

  Amazon Aurora PostgreSQL is a fully managed, PostgreSQL-compatible, relational database engine that combines the speed and reliability of high-end commercial databases with the simplicity and cost-effectiveness of open-source databases. Aurora PostgreSQL is a drop-in replacement for PostgreSQL

So Amazon added some settings.

You will need to contact them directly and ask since their documentation does not provide a result when doing a search, https://docs.aws.amazon.com/search/doc-search.html?searchPath=documentation-guide&searchQuery=shared_heap_size&x=0&y=0&this_doc_product=Amazon+Relational+Database+Service&this_doc_guide=User+Guide&doc_locale=en_us#facet_doc_product=Amazon%20Relational%20Database%20Service&facet_doc_guide=User%20Guide gives Your search for "shared_heap_size" returned no results.


Prevent duplicate rows with many columns Here is the shortened version of my table:

   CREATE TABLE [dbo].[Ability_Main](
    [Ability_ID] [smallint] NOT NULL,
    [Ability_Text] [nvarchar](150) NOT NULL,
    [Flag_Conditional] [bit] NOT NULL,
    [Flag_Choose] [tinyint] NOT NULL
--around 20 flag columns, bit type
 CONSTRAINT [PK_Ability_Main] PRIMARY KEY CLUSTERED
(
    [Ability_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Ability_Main]  WITH CHECK ADD  CONSTRAINT [CK_Ability_Main_Choose] CHECK  (([Flag_Choose]>=(0) AND [Flag_Choose]<=(2)))
GO

ALTER TABLE [dbo].[Ability_Main] CHECK CONSTRAINT [CK_Ability_Main_Choose]
GO

My question is - I want to prevent adding a duplicate entry that has the same flags - do I need to add a unique constraint on every single column (so 20+ constraints) or is there an easier way?

Here's an example where a the unique constraint is part of the table creation command...

CREATE TABLE dbo.Ability_Main (
    Ability_ID SMALLINT NOT NULL,
    Ability_Text NVARCHAR (150) NOT NULL,
    Flag_Conditional BIT NOT NULL,
    Flag_Choose TINYINT NOT NULL,
    F1 BIT NOT NULL,
    F2 BIT NOT NULL,
    F3 BIT NOT NULL,
    F19 BIT NOT NULL,
    F20 BIT NOT NULL,
    CONSTRAINT UQ_AbilityMain_BitFlags
        UNIQUE (F1, F2, F3, F19, F20)
        --WITH (...) ON [FILEGROUP],
    CONSTRAINT PK_Ability_Main
        PRIMARY KEY CLUSTERED (Ability_ID ASC)
        WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY];
GO

Or added after the fact, in a separate command...

ALTER TABLE dbo.Ability_Main ADD CONSTRAINT UQ_AbilityMain_BitFlags UNIQUE (F1, F2, F3, F19, F20)
--WITH (...) ON [FILEGROUP];
GO


Grouping date's as 1 count if difference is 5 days I am trying to think of a way of grouping some dates to make a count if they are more then 5 days apart.

For Example:

ID - DATE
22   15/05/2016 08:57
22   15/05/2016 10:50
22   15/05/2016 12:01
22   16/05/2016 01:12
22   22/05/2016 02:25

So for this I would currently get a count of 5 as there are 5 occurrences however I want to only get a count for 2 due to the fact that the first 4 have sequential dates and the final one is 5 days after the previous date so would need that counted as a new one.

Hopefully that makes sense!

If I understand your question, the following is the query that you are looking for.

SQL> select * from mytest;

        ID DT
---------- ---------
        22 15-MAY-17
        22 15-MAY-17
        22 15-MAY-17
        22 16-MAY-17
        22 22-MAY-17
        23 23-MAY-17
        23 25-MAY-17
        23 30-MAY-17

8 rows selected.

SQL> select id, count(diff)+1 count
     from(
          select id, dt - lag(dt) over(order by dt) diff
          from mytest
          )
     where diff >= 5
     group by id;

        ID         COUNT
---------- -------------
        22             2
        23             2

Here is the fiddle.

For more about LAG()

Documentation: LAG