What are some good methods of structuring a MySQL audit trail to be both immutable, and perform well for usage? In one application, we have an audit table that uses the ArchiveEngine in conjunction with a few triggers to capture 100% of the changes that occur. The upside to this is that 20+ million rows occupies 145MB, not to mention that ArchiveEngine only supports INSERT and SELECT, rendering the trail immutable.

The downside comes when you want to access that data in order to give the user audit information. Since the data is stored in compressed form and there are no indices, selection time can be ridiculous:

mysql> select * from AUDIT_LOG WHERE Table_Name = 'myTable' AND Row_ID = 9024;
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| Table_Name | Row_ID | Field_Name  | Old_Value           | New_Value           | DB_User        | modified_by | date_modified       |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
| myTable    |   9024 | Flags       | 0                   | 1                   | user@localhost |         826 | 2011-06-10 22:05:11 |
| myTable    |   9024 | Status      | Unavailable         | Pending             | user@localhost |         826 | 2011-07-08 22:41:45 |
| myTable    |   9024 | Status      | Pending             | Processing          | user@localhost |         826 | 2011-07-08 22:41:49 |
| myTable    |   9024 | Status      | Processing          | Calculated          | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Fee_Paid    | NULL                | 185.00              | user@localhost |         826 | 2011-07-08 22:41:54 |
| myTable    |   9024 | Status      | Calculated          | Approved            | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved    | NULL                | 2011-07-08 22:47:06 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_TZ | America/Yellowknife | America/Vancouver   | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Approved_By | NULL                | 826                 | user@localhost |         826 | 2011-07-08 22:47:04 |
| myTable    |   9024 | Invoice_ID  | NULL                | 2372                | user@localhost |         825 | 2011-10-17 19:32:59 |
+------------+--------+-------------+---------------------+---------------------+----------------+-------------+---------------------+
**10 rows in set (13.54 sec)**

After conversion to InnoDB and adding a PK and indexing Row_ID and Table_Name, this same operation takes roughly 1/10th of a second.

What I'm considering is a leaving the triggers + Archive, and then using a cron job to mirror the Archive in InnoDB.

Are there other ways this has been dealt with?
Any drawbacks or trade-offs that are apparent?

Are there other ways this has been dealt with?

If you use MySQL Replication, you could have a Master/Slave that has the following:

AUDIT_LOG as InnoDB on the Master
AUDIT_LOG as ARCHIVE on the Slave

This would provide fast writing of the AUDIT_LOG on the Master and makes the data available for reads almost instantly. The Slave would have the backup as an ARCHIVE with a small disk footprint.

DO NOT SEND ANY ALTER TABLE SQL AGAINST AUDIT_LOG ONCE REPLICATION IS SET UP.  Otherwise, you lose the small disk footprint feature of the ARCHIVE Storage Engine.

  Any drawbacks or trade-offs that are apparent?

You must configure InnoDB

with enough RAM to accommodate data and indexes that are frequently queried : What are the main differences between InnoDB and MyISAM?
with tablespaces for table separate from ibdata1 : What is the best way to reduce the size of ibdata in mysql?
to access multiple CPUs (if using MySQL 5.5) : Possible to make MySQL use more than one core?


Should I install SQL Server 2012 SP3 when I'm already running SQL Server 2014? We've been running SQL Server 2014 for almost a year now, but SQL Server 2012 SP3 continues to appear in the Windows Update list (as an optional install). It seemed ridiculous to install a service pack from a previous version, so I've just ignored it.

However, we've recently started encountering an assertion failure (see below for more info), and one thing we're doing to try and resolve it is making sure the server is fully patched. We installed the latest cumulative update for 2014, but it hasn't solved the problem or made that SP3 item disappear from Windows Update. SP3 still wasn't on my radar, but then I found a Microsoft Product Support Services blog post that said to use the Best Practices Security Analyzer to try and troubleshoot assertion failures, and when I did it raised a warning that we hadn't applied that SP3.

So now I am considering applying that install, but I'm worried that I'm going to accidentally downgrade my server - not that we'd go back 2012 entirely, but that some of the cumulative updates might get reverted, or some other bizarre behvaior.

(This is not the exact assertion failure we've been receiving, but it's close. I cannot find a KB article that seems to describe the actual issue we're having - the KB linked in the previous sentence has to do with TOP N and ours has to do with a procedure using OFFSET X ROWS FETCH NEXT Y ROWS ONLY. The actual assertion failure is SQL Server Assertion: File: <op_ppqte.cpp>, line=12258)

Windows Update isn't offering you a Service Pack for SQL Server 2012 to apply to your SQL Server 2014 instance; there is something else on your machine that is triggering it. Could be dozens of things, but most commonly:

The SQL Server 2014 instance isn't the only instance on your machine. You may have SQL Express installed (on its own, or as part of Visual Studio, or as part of some other software), SQLLobalDB, or some other instance that is still at SQL Server 2012 < SP3.
You have other services like SSIS, SSRS, SSAS, etc. that are from 2012 and weren't upgraded as part of the move to 2014.
You have shared 2012 components - e.g. you upgraded your 2012 engine but you didn't upgrade the tools, or the upgrade to the tools failed, or you installed 2012 tools after the upgrade.

Here is what I suggest.

Go download the SQL Server 2012 SP3 standalone installer:

Download Service Pack 3 for Microsoft® SQL Server® 2012

When you run the installer, very early in setup, you'll get to a screen like this, which will tell you all of the components that are eligible for upgrade:

This will give you an indication of what components you need to either patch or remove.

I do not think you should ignore this. If you have other instances on this machine and don't want them there, clean them up! If you do need them, but leave them unpatched, it could be a security vulnerability waiting to happen. There is just no upside at all to ignoring the prompt to apply the service pack - you're getting it for a reason.

As for the assertion error you're getting, that has nothing to do with the SQL Server 2012 Service Pack you're being offered, either. It could be an indication that you are on RTM still and are missing a fix for that problem (in which case, go get the latest SP + CU, or that you've come across a bug that hasn't yet been fixed (in which case contact SQL Server support). Note that a fix may cover your issue even if your exact error message isn't listed explicitly in the knowledge base article.


How do you export a database schema from SQL Server Management Studio to Excel? As part of a web migration process, a vendor has asked for our database schema without the data records (for confidential purposes). They want a MS Excel file with each database table. I am completely new to working with databases. Is there an easy way to do this in SQL Server 2014 Management Studio?

You can link your server as Data Source in Excel and then run this query:

SELECT * FROM information_schema.tables WHERE TABLE_TYPE='BASE TABLE'

This will export data into Excel

How to connect SQL server to Excel:
https://support.office.com/en-us/article/Connect-a-SQL-Server-database-to-your-workbook-22c39d8d-5b60-4d7e-9d4b-ce6680d43bad

Or if database is not that big then just run it in Management Studio and copy-paste the result to Excel


Is the [NT Service\SQLSERVERAGENT] a supported type for SQL Server Agent service? Reading and re-reading  MSDN "Select an Account for the SQL Server Agent Service".

NT Service\SQLSERVERAGENT is not listed in the "Supported Service Account Types" section.
And, if not, what is it installed with SQL Server for?
What could this possibly mean, I wonder?
How should I better undesrstand it?

NT Service\SQLSERVERAGENT is the name of an account, not an account type.

The document refers to account types.  As @Jon-Seigel said, it qualifies as a 'Local non-administrative user'.


Need help with relationships in MS Access I am in need of some assistance.  I'm fairly new at access & dbs, and have managed to get a bit accomplished, but have run into a wall.  I'm not sure it is my inexperience or if access cannot accomplish what I want (or if it is just not easy...)

Anyhow, I need to create a couple relationships.  I’ve got a few, but now I’m getting in too deep for my level of experience, and can’t figure out how to get it to do what I want.

Basically I need to record the following:

•   Base/Parent Contract (BC)

   o    BC DOES have Line Items (CLINS)

   o    BC CAN have Modifications (Mods)

   o    BC CAN have Delivery Orders (DO)

•   Delivery Orders (DO)

   o    DO DOES have CLINS

   o    DO CAN have Mods

Example:

Contract: F12345-D-17-0001

 Delivery Order 0001

      CLIN 0001AA

      CLIN 0002AA

 Delivery Order 0002

      CLIN 0001AA

      CLIN 0002AA

      CLIN 0003AA

      Modification P00001

      **Modification P00002**

 Modification P00001

 Modification P00002

Each one of these bullets has its own set of data.  The contract number is unique by itself, but its number actually flows down to each of the others, as they get appended to it….  For example, the BOLD Mod above would actually be listed as “F12345-17-D-0001-0002-P00002”.  Under each base contract the Modifications, CLINs and Delivery Orders all basically start from 1 and increase, so you can see that there will be many of those records with the same number, so it is a different ID used as the key for each of those.

Anyhow, I have been able to establish these relationships:

 Subordinate DO to Parent Contract

 Subordinate Modification to Parent Contract

 Subordinate CLIN to Parent Contract

 POC Contact Info to Base Contract

What I am having problems doing is associating:  (I think because they are 2nd layer subordinate to the Base Contract)

 CLINs that go to Delivery Orders and

 Modifications that go to Delivery Orders

It all hinges off the Base/Parent Contract and cascades down from there.

Here is my relationship table.

And this is what my form looks like - how I want the info portrayed in the end if it helps.

You'll notice that I do not mention every table.  Hopefully that would not be necessary as you begin to understand the proper normalization patterns.  You should be able to analyze and setup relationships on those other tables using the same patterns.

Once you make these changes, you may find that you have questions about the proper way to get and display related data, for example the contract number for a given CLIN--especially if you've eliminated the redundant fields as I advise.  Look into crafting proper queries and using such queries as the Record Source for forms and reports, etc.  Further questions about such queries would require new questions.  Also, question regarding query specifics and SQL may be more well received on Stack Overflow.

The overall principle here should be proper database normalization.  Although it is often not practical or necessary to adhere to every level of normalization, it should at least a guide for the eventual table and relationship structure.  These concepts can be a bit abstract, but it is still best to study it.
Delete duplicate fields like [CLIN Info Table].[Contract Number], [CLIN Info Table].[Delivery Order Number], [Delivery Order Table].[Contract Number], etc.  The point of a relational database is to always get such information via the relationships.  If you want a [Contract Number] for a given CLIN, then you execute a query which returns the related value from the proper table.
Looking at the relationships as they are now defined (in the original image), you can see 1-to-many relationship between [Delivery Order Table] and the [Base Contract Data], but as I understand your description this relationship is backwards.  You want multiple Delivery Orders per contract, not the other way around.

For this relationship, I suggest 1) deleting the existing relationship between the two tables; 2) eliminating [Base Contract Data].[Delivery Order ID];  3) Creating an enforced relationship between [Base Contract Data].[Contract ID] (the primary key) to [Delivery Order Table].[Contract ID] (the foreign key).

Delete [Delivery Order Table].[CLIN ID] to eliminate confusion.
Since [CLIN Info Table] will have records linked directly to [Base Contract Data] and directly to [Delivery Order Table], then it needs to have two separate "relationships".  This can be accomplished in at least two ways.

The first scheme involves having two foreign keys in [CLIN Info Table], one for each of the tables.  This scheme is only valid if a single [CLIN Info Table] can be linked to only one [Delivery Order ID] record or one [Base Contract Data] record.  If the answer to my comment question is that the exact same [CLIN Info Table] can be linked to multiple Delivery Orders or Contacts, then you'll need to skip to the alternative scheme.

One of them you already have established correctly: [CLIN Info Table].[Delivery Order ID] (foreign key) already related to [Delivery Order Table].[Delivery Order ID] (primary key).  Leave that as is.
Next remove the existing relationship between [CLIN Info Table] and [Base Contract Data] and delete [Base Contract Data].[CLIN ID].  You'll notice this is the same type of incorrect relationship as in #2 above.
Add a relationship between the existing fields [Base Contract Data].[Contract ID] (the primary key) to [CLIN Info Table].[Contract ID] (the foreign key).

The alternative scheme satisfies the next level of normalization.  It may also be necessary if the exact same [CLIN Info Table] can be linked to multiple other-table records.  It requires a third table for establishing each relationship.  It avoids the problem of having multiple null foreign keys.  I'm sure this topic could be heavily debated, but I personally think it is just a matter of preference.  Adding a third table in a relationship necessarily makes queries more complex.  Although this pattern is meant to eliminate certain other relationship problems, it also establishes the ability to have orphaned child records, which if a huge problem, you'd need some way of managing those.

Eliminate foreign key fields from [CLIN Info Table]
Create a new table named something like [Oder CLIN] with only two required fields:  [Delivery Order ID] and [CLIN ID].  You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Delivery Orders.
Create another new table named something like [Contract CLIN] with only two required fields:  [Contract ID] and [CLIN ID].  You need to create appropriate indexes for these field, depending on your answer to whether the same exact CLIN can be linked to different Contracts.
Create relationships between the foreign keys in the new tables to their corresponding primary tables.


Modeling Table-Per-Type in PostgreSQL I have read the following post How do you effectively model inheritance in a database? about inheritance in databases. Unfortunately I could not find anything in the PostgreSQL books from Packt.  And now I'm a bit confused if my approach is an overkill and doesn't even belong in the database or makes sense (see my considerations at the end).
First I wanted to implement the following example using Table-Per-Concrete. But then I read several times that you should only do this when you really have performance problems and you should use table per type as long as you have no performance problems.
So the following relations are given:
system_user (system_user_id, type, first_name, last_name, ...)

user (system_user_id) *Has no unique attributes but special relations accessible only for him, e.g. orders
customer (system_user_id, company_name, ...)
supporter (system_user_id, nickname, ...)

So far so good, but I was wondering how to ensure in the above example that:

The type cannot be changed.
A system_user has at most one entry in an extension.

My consideration:

A trigger that throws an error if the type is changed during an UPDATE.
One trigger for each extension, which checks before an INSERT, if an entry with this system_user_id already exists in another extension.

Does this make sense? Or am I going about it the wrong way? Is this even necessary? Should I create a separate relation for each entity directly?
Maybe I have too much of an object-oriented software developer view on it. Thanks a lot for your answers.

I think that you data model makes sense. I would add type to the "subclass" tables, create a unique constraint on (system_user_id, type) and create foreign key relationships pointing to system_user. Then add a check constraint to the subclass tables that makes sure that type has the correct value.
This avoids using a trigger.


Select only rows with minimum values from union table SQL Server 2005

Here is a sampling of the data from a union of two tables (named "vProducts_All"):

DBID    UPC             Price1          Price2
1       023614025559    689.0000        649.0000
2       023614025559    632.0000        602.0000
1       011457891333    252.0000        232.0000
2       011457891333    284.0000        254.0000
1       123451234512    199.0000        159.0000
2       123451234512    195.0000        162.0000

I want to return only the rows showing the minimum for Price1 field, IN ADDITION TO all of the other records that didn't have duplication, something like:

DBID    UPC             Price1      Price2
2       023614025559   632.0000     602.0000
1       011457891333    252.0000        232.0000
2       123451234512    195.0000        162.0000
+ All the other records that didn't have duplication in the UPC field

Have tried in vain using joins to derived tables, but nothing works. Any help appreciated

You can treat the union as a subquery.

SELECT TOP 1 DBID, UPC, Price1, Price2
FROM (SELECT DBID, UPC, Price1, Price2
    FROM Table1
    UNION
    SELECT DBID, UPC, Price1, Price2
    FROM Table2) sub
ORDER BY Price1 DESC


Table partition should not be selected I am looking to partition a table (nearly 30M rows) by date ranges
(mysql version : 5.1.73)

CREATE TABLE `TABLE_X` (
  `name_x` varchar(50) NOT NULL,
  `type_x` varchar(5) NOT NULL,
  `status_x` tinyint(1) NOT NULL,
  `date_x` date NOT NULL,
  KEY `xdate` (`date_x`)
)ENGINE=MyISAM DEFAULT CHARSET=latin1
PARTITION BY RANGE (TO_DAYS(`date_x`))
 (
    PARTITION p13q1 VALUES LESS THAN (TO_DAYS('2013-04-01')),
    PARTITION p13q2 VALUES LESS THAN (TO_DAYS('2013-07-01')),
    PARTITION p13q3 VALUES LESS THAN (TO_DAYS('2013-10-01')),
    PARTITION p13q4 VALUES LESS THAN (TO_DAYS('2014-01-01')),
    PARTITION p14q1 VALUES LESS THAN (TO_DAYS('2014-04-01')),
    PARTITION p14q2 VALUES LESS THAN (TO_DAYS('2014-07-01')),
    PARTITION p14q3 VALUES LESS THAN (TO_DAYS('2014-10-01')),
    PARTITION p14q4 VALUES LESS THAN (TO_DAYS('2015-01-01')),
    PARTITION p15q1 VALUES LESS THAN (TO_DAYS('2015-04-01')),
    PARTITION p15q2 VALUES LESS THAN (TO_DAYS('2015-07-01')),
    PARTITION p15q3 VALUES LESS THAN (TO_DAYS('2015-10-01')),
    PARTITION p15q4 VALUES LESS THAN (TO_DAYS('2016-01-01')),
    PARTITION p16q1 VALUES LESS THAN (TO_DAYS('2016-04-01'))
);

when i do a 'select' query to retrieve data between two dates from the same month, the result is correct

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-10-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 984405
        Extra: Using where; Using index

but when i try to retrieve rows between different months, i get this result

Eg 1:

explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2013-11-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 1562732
        Extra: Using where; Using index

this query should use only p13q4 partition

Eg 2:

    explain partitions select count(*) from TABLE_X where date_x between '2013-10-01' and '2014-01-27' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: TABLE_X
   partitions: p13q1,p13q4,p14q1
         type: index
possible_keys: xdate
          key: xdate
      key_len: 4
          ref: NULL
         rows: 2085003
        Extra: Using where; Using index

this query should use p13q4 & p14q1 partitions.

 I don't understand why it's using the p13q1 partition,What am I missing?

I cannot fully answer this question but I can give you some tips on what to look around for.

First thing I would do is check the dates inside each partition as follows

SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p13q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p14q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q1) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q2) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q3) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p15q4) GROUP BY date_x;
SELECT date_x,COUNT(1) datecount FROM TABLE_X partition (p16q1) GROUP BY date_x;

If dates are partitioned correctly, then you have a bug-infested mysql in relation to the query optimizer. If dates appear in a partition that do not belong, I would reload the table from scratch as follows:

CREATE TABLE TABLE_Y LIKE TABLE_X;
INSERT INTO TABLE_Y SELECT * FROM TABLE_X;
ALTER TABLE TABLE_X RENAME TABLE_XXXX;
ALTER TABLE TABLE_Y RENAME TABLE_X;

Then, retry your queries.

I noticed you are using MySQL 5.1. There are many patches to the partition code (such as ALTER TABLE ... REPAIR PARTITION). I would suggest installing MySQL 5.6 on a test server, copying the data into it and try out your queries on.


Why does CONTAINS not find matches at the end of the string? I have a stored procedure in Microsoft SQL Server 2008 R2 (SP3) which searches a table for anything matching a @Keywords parameter that I pass in. It was too slow using LIKE as the table has 155,000 rows, so I created a fulltext catalog and switched to using CONTAINS instead. I am not very familiar with this feature, but after researching it seemed all I needed to do was process the user's choice of words with:

SET @Keywords = '"' + @Keywords + '*"'

Adding the double quotes and asterisk worked well until someone discovered they had no results when searching for a string they knew definitely existed. It seems that the * is stopping CONTAINS from finding rows where the @Keywords are right at the very end of the string.

Analysis with very simple t-SQL:

--first I prove that I can find the one row which the user was searching for
--this column is a NVARCHAR(200)
SELECT * FROM News
WHERE Headline = '1120 days at sea and still sailing' --1 row returned (OK)
SELECT * FROM News
WHERE Headline LIKE '1120 days at sea and still sailing%' --1 row returned (OK)

--using the full headline
SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing*"') --no rows, just like my proc (WRONG)
SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing"') --1 row returned if asterisk omitted (OK)

--using part of the headline so we don't touch the end of the
SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still"') --lots of rows, since it is anything with 'days' and then 'sea' present (NOT USEFUL)
SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still*"') --no rows (WRONG)

I note that CONTAINS considers at/and/still to be 'noise' words and ignores them. Fair enough. The word sailing is the troublesome one at the end of the string.

My question:

How should I modify the incoming keywords to make sure than CONTAINS can find results regardless of the position of the string of keywords within the column value?

This has to do with how stoplist (or noise words) is/are treated when adding the asterix (*) in combination with CONTAINS().

A longer explanation on that here.

In short: Default <> Prefix searching

By default, when using the SYSTEM stoplist some words are not indexed and classified as stoplist words.

When adding the * + CONTAINS()they are not seen as stoplist words, and the exact phrase has to match.
But when using the default SYSTEM stoplist these 'stoplist words' are not added to the index, resulting in no matches found.

Question

  Are you saying that the intended behaviour, when an asterisk is
  present, is to return zero matches for a user who includes a noise
  word? This seems surprising, when you consider what most humans are
  likely to type!

Prefix matching in combination with contains from microsoft docs

  Specifies a match of words or phrases beginning with the specified
  text. Enclose a prefix term in double quotation marks ("") and add an
  asterisk (*) before the ending quotation mark, so that all text
  starting with the simple term specified before the asterisk is matched

Source

Keywords here are:

  so that all text
  starting with the simple term specified before the asterisk is matched

Meaning that we have to have precise matches when using prefix matching and CONTAINS().

Workaround #1 Disabling the stoplist

One workaround for this would be disabling the 'stoplist'

ALTER FULLTEXT INDEX ON News
SET STOPLIST OFF;

You do have to know that this will increase the words stored in the fulltext index, as the words 'and', 'or', 'still', ... are added to the index.

Performance might suffer from disabling the stoplist.

To reenable the system stoplist

ALTER FULLTEXT INDEX ON News
SET STOPLIST SYSTEM;

Alternatively, you could create your own stoplist

Testing this on your query:

ALTER FULLTEXT INDEX ON News
SET STOPLIST OFF;
INSERT INTO News(Headline)
VALUES('1120 days at sea and still sailing')

INSERT INTO News(Headline)
VALUES('zzz 1120 days at sea and still sailing')

SELECT * FROM News
WHERE CONTAINS(Headline, '"days at sea and still*"')

Result

id  Headline
1   1120 days at sea and still sailing
2   zzz 1120 days at sea and still sailing

For other workarounds you could look into using CONTAINS() or FREETEXT() without prefix matching, or prefix matching + CONTAINS() with one (non noise) word to get more accurate results

In your case, enbling transform noise words does not seem to work with prefix-matching

sp_configure 'show advanced options', 1;
RECONFIGURE;
GO
sp_configure 'transform noise words', 1;
RECONFIGURE;
GO

Again due to how prefix matching + contains works.

SELECT * FROM News
WHERE CONTAINS(Headline, '"1120 days at sea and still sailing*"')

-- No results

Inside the fulltext index (with stoplist = system).

SELECT display_term,column_id,document_count
FROM sys.dm_fts_index_keywords(DB_ID('MNGDB'), OBJECT_ID('dbo.News'));

result

display_term    column_id   document_count
1120                 2           2
days                 2           2
nn1120               2           2
sailing              2           2
sea                  2           2
zzz                  2           1
END OF FILE          2           2

'Still','at','and' are filtered out, when disabling the stoplist they will be added to the index

Testing without stopwords

ALTER FULLTEXT INDEX ON News
SET STOPLIST SYSTEM;

INSERT INTO dbo.News
VALUES('days sea sailing')
SELECT * FROM dbo.News
WHERE CONTAINS(Headline, '"days sea sailing*"')

id  Headline
4   days sea sailing

More on the asterix

  The presence of the asterisk enables the prefix-matching mode...
  If a phrase is provided, matches are detected if the column contains
  all the specified words with zero or more other characters following
  the final word

Source


Modeling a db that has products: What are the good practices/solutions? How to treat grammar variations in the words? The problem is:

A Company belongs to a sector.
A Company produces products.
A Sector contains products.
A Product belongs to sectors.

Three entities: Company, Sector and Product. And it's a ternary relationship, right?

The dilemma is:

Possibility 1

I follow the above modeling. But I don't know how much hassle I'll get in the implementation and in the day-to-day use of the database. I'm saying that because the user could register the product "chair", but also "chairs", but also "wood chairs", etc...

How would I treat that singular-plural-adjective-grammar-etc trouble, under that modeling?

How would I guarantee to the user that no matter how he types "chair" (singular, plural, with or without adjectives), he will get all the results that the database can provide?

Possibility 2

I do not follow the modeling. I skip the Product entity, and just make an attribute "products" in the parent entity, which is Company. That attribute would be a big string containing all the products.

Example: The company 'Wood Corporation' has the attribute "products" equal to "chair chairs woods wood metal metals ..." (space-separated values). It looks a very ugly solution and very prone to bad data input by the user.

What do you think? What are the common practices/solutions? How to treat the grammar variations?

Maybe I'm just looking too far forward (tables, implementation, use), because I'm just in the modeling phase. Yet, those concerns keep arising, I can't avoid it and it bites my neurons.

How would I guarantee to the user that no matter how he types "chair"
  (singular, plural, with or without adjectives), he will get all the
  results that the database can provide?

Wildcard queries could solve your immediate problem. For example, your products table may have the following entries:

productID    companyID    productType
--------------------------------------
1            1            chair
2            2            wooden chair
3            2            desk chairs

If you wanted to get all of the products with the word chair in it, you could run the following query (mysql):

select productID, productType
from products
where productType like '%chair%'

However, that design isn't optimal, because you will encounter a problem when a user types in recliner, or some other type of chair that doesn't explicitly have the word "chair" in it.

A better design would be to have a separate table that specifies the categories of products. For example, you could have a categories table that contained a fixed set of categories:

categories table

categoryID    categoryName
---------------------------
1             chair
2             office furniture
3             wooden furniture

Then, you would have a table to map categories to products. Products can have many categories, which will give your users more flexibility when searching. Here is the productCategories table.

productCategories table

categoryID    productID
-----------------------
1             1
1             2
1             3
1             4
2             3
3             2

Finally, you would have your normal products table, which would contain additional information about your products.

Products table

productID    companyID    productName              price
---------------------------------------------------------------
1            2            Plain chair              40.00
2            2            Modern wood chair        120.00
3            2            Black office chair       160.00
4            3            Leather recliner         800.00

With this model, finding all of the chairs is simple. You just need find all products assigned to the chair category.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName = 'chair'

This would return the recliner as well. Also, this model gives the user some flexibility for creating more specific searches. For example we can change only one line (see the last line of the query below) to fetch all of the wooden chairs.

select p.productID, p.productName, p.price
from products p
left join productCategories pc
on p.productID = pc.productID
left join categories c
on pc.categoryID = c.categoryID
where c.categoryName in ('chair', 'wooden furniture')


How can I seamlessly upgrade the major version of an AWS RDS postgres database? This morning I was involved in upgrading a PostgreSQL database on AWS RDS. We wanted to move from version 9.3.3 to version 9.4.4. We had "tested" the upgrade on a staging database, but the staging database is both much smaller, and doesn't use Multi-AZ. It turned out this test was pretty inadequate.

Our production database uses Multi-AZ. We've done minor version upgrades in the past, and in those cases RDS will upgrade the standby first and then promote it to master. Thus the only downtime incurred is ~60s during the failover.

We assumed the same would happen for the major version upgrade, but oh how wrong we were.

Some details about our setup:

db.m3.large
Provisioned IOPS (SSD)
300 GB storage, of which 139 GB is used
We had RDS OS upgrades outstanding, we wanted to batch with this upgrade to minimise downtime

Here are the RDS events logged while we performed the upgrade:

Database CPU was maxed out between about 08:44 and 10:27. A lot of this time seemed to be occupied by RDS taking a pre-upgrade and post-upgrade snapshot.

The AWS docs don't warn of such repercussions, although from reading them it is clear that an obvious flaw in our approach is that we didn't create a copy of the production database in the Multi-AZ setup and try to upgrade it as a trial run

In general it was very frustrating because RDS gave us very little information about what it was doing and how long it was likely to take. (Again, doing a trial run would have helped...)

Apart from that, we want to learn from this incident so here are our questions:

Is this kind of thing normal when doing a major version upgrade on RDS?
If we wanted to do a major version upgrade in the future with minimal downtime, how would we go about it? Is there some kind of clever way to use replication to make it more seamless?

This is a good question,
working in cloud environment is tricky sometimes.

You can use pg_dumpall -f dump.sql command, that will dump your entire database to a SQL file format, In a way that you can reconstruct it from scratch pointing to other endpoint. Using psql -h endpoint-host.com.br -f dump.sql for short.

But to do that, you will need some EC2 instance with some reasonable space in disk (to fit your database dump). Also, you will need to install yum install postgresql94.x86_64 to be able to run dump and restore commands.

See examples at PG Dumpall DOC.

Remember that to keep integrity of your data, it is recommended (some cases it will be mandatory) that you shutdown the systems that connect to the database during this maintenance window.

Also, if you need speed up things, consider using pg_dump instead pg_dumpall, by taking advantage of parallelism (-j njobs) parameter,
when you determine the number of CPUs involved in the process, for example -j 8 will use until 8 CPUs. By default the behavior of pg_dumpall or pg_dump is use only 1. The only advantage by using pg_dump instead pg_dumpall is that you will need to run the command for each database that you have, and also dump the ROLES (groups and users) separated.

See examples at PG Dump DOC and PG Restore DOC.


Highest cost section in Actual Execution Plan doesn't show any logical reads? In the following query plan - https://www.brentozar.com/pastetheplan/?id=H1dwOCHed (it has 3 sections, the last one is the problem), there's a node at the very bottom that consumes 37% of the resources. This is the actual query plan, not the estimated query plan.
However, the table it references does not show up at all, when looking at logical reads via Set Statistics IO. This includes the 2 sections for ActivityFeedTeamCache and ActivityFeedCache.
In the past, I've been able to use Logical Reads as a pretty good proxy for performance. Reduce the logical reads, and you've done the right thing for performance.
However, since in this case, there are no logical reads for the chunks that are the worst (ActivityFeedTeamCache, ActivityFeedCache, Locales), what is a good proxy for testing performance? Just that the % cost in the Actual Execution Plan goes down?

The % cost is still just an estimation, even in an Actual Execution Plan. You need to look at the row source level statistics here to tell you how much time each step took.
Judging by the XML, 274ms was taken up to the point of the hash join at node 7, 72ms of that was just the cluster scan of Pk_TeamMembers (node 8) and 35ms was up to the the Left Semi Join (node 9). This means that a whopping 167ms was taken just to execute the Hash Join step (likely assigning memory for the 263,600 rows from Pk_TeamMembers).
It looks like you've already tried to target this join before:
LEFT JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID -- Overloaded to help SQL find indexes

You probably want to do an (Outer) nested loop to this index instead. The query planner has decided to use a hash join because it thinks that you will get 59000 rows from the other side of this join, when in fact you get 3.
You have choices

Hint so that a nested loop join is used
Figure out why the planner thinks it will have so many rows and give it some help (statistics, simplify the filtering)

The hint would look like
LEFT LOOP JOIN TeamMembers tm on AF.CompletionUserID = tm.UserID AND AF.TeamID = tm.TeamID AND tm.TeamID = t.TeamID

The second choice will be particularly hard because of the complexity of your driving filters. One way to do this would be to insert the rows that match your main driving filters, and then join to this temp table in your main query. Here, it looks like you need to join 3 of the 5 tables together first in order to utilize your @userId@ filters fairly:
FROM ActivityFeed AF
    JOIN ActivityType A ON AF.ActivityType = A.ActivityType
    LEFT JOIN Teams t on AF.TeamID = t.TeamID
WHERE
    AF.SearchIndexUpdateDate IS NULL -- Only get items that aren't in the search index
    AND
    AF.Active=1
    AND
    A.Active=1
    AND
    (A.AdminOnly = 0 OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.IsAdmin=1))
    AND
    (
      (
        A.TeamOnly=1
        AND AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE A.HideWhenOwnerless = 0 OR x.IsOwnerless = 0)
        AND (A.ShowSelf=1 OR AF.CompletionUserID <> @UserID)
        AND ISNULL(t.IsDeleted,0) = 0 AND t.active=1
        AND (A.IncludeOnsite = 1 OR EXISTS(SELECT 1 FROM @tmpFindAdminFlagsForTeamsMembershipsForUser x WHERE x.TeamID = AF.TeamID AND x.IsOnsiteOwner = 0))
       )
       OR
      (
        A.UserOnly=1
        AND AF.UserID=@UserID
      )
    )
    AND
    -- Don't included moderated activity (owner moderation actions are in Group activity view API_GetActivityFeedByTeam)
    AF.Moderated = 0
    AND
   -- don't show completions or plan completions to other team members if opted out (tm.ShowUserActivityInFeed)
    (ISNULL(A.ShowOnlyWhenUserActivityActive,0) = 0 OR (A.ShowOnlyWhenUserActivityActive = 1 AND tm.ShowUserActivityinFeed = 1))
    AND
    (AF.TeamID IS NULL OR AF.TeamID IN (SELECT TeamID FROM @tmpFindAdminFlagsForTeamsMembershipsForUser as x))


Is it safe to make a Unique Index the Primary Key? I have a vendor database that I want to use Change Data Tracking against but cannot because none of their tables have Primary Keys defined.  The tables do have Unique Indexes but they are not defined as PK Constraints.  It seems to me that making them Primary Keys would be a safe thing to do (i.e. will not interfere with or change the software operation) given that they are already Unique Indexes.

Can anyone tell me if my understanding is correct or if I am missing something?

No it's not safe.

From a query perspective it might look OK, but if you don't know how the application handles primary keys you shouldn't change that without checking with the vendor first.

You could break their error handling, their future update scripts etc.

Just to give you an example, during a future upgrade they may want to change some tables where you added CDC and they will receive an error. What does your contract say about support in that case?

If you are using a vendor database it's up to them to support it. If you go around making changes you are basically putting your system in a state they never tested so they would (rightfully so) probably tell you you have an unsupported configuration when things go wrong.

We don't know what application or vendor you are talking about, but if it doesn't support customizations you probably shouldn't customize the database without asking them first.


What is the minimum required compatibility level to use parallel SELECT INTO? What is the minimum required compatibility level to use parallel SELECT INTO in SQL Server 2014?

In SQL Server 2014 parallel DML for the SELECT INTO statement requires compatibility level 120.

110:

  A SELECT INTO statement always creates a single-threaded insert operation.

120:

  A SELECT INTO statement can create a parallel insert operation. When
  inserting a large numbers of rows, the parallel operation can improve
  performance.

Note, that other resources on the web state false values for the compatibility levels. These statements are quoted directly from Microsoft documentation and have been verified experimentally by me.


SQL Server Stored Procs not parameterized, leading to high compilations? We're experiencing about 300 SQL Compilations per second, and we use very little dynamic SQL.  However, I noticed that in SQL Profiler our calls look like:

exec cid.dbo.uspCIDLog '9425','19155852234',2 -- null

And I think they should look like this if properly parameterized:

exec cid.dbo.uspCIDLog @sFrom='9425', @sTo='19155852234', @userId=2

Will this make a difference?

It shouldn't really make a difference if the parameters are specified or if parameter location is assumed.  The compiling happens to the statements within the stored procedure.

That said, it's best practice to pass in the values properly parametrized so that if a parameter is added in the middle of the list nothing will break.


Should dbatools be installed on all SQL Servers I'm looking to leverage dbatools powershell module for our SQL Server management needs.  The install process does not lend itself for installation across a large number of servers.  Is it really necessary or even a best practice to install dbatools across all SQL Servers in the environment or leverage a server we use for our database management as a central place to install to and manage from there?  Seems like a lot of the commands I've looked at can be used remotely.  Thank you.

In general you should be able to use most of the dbatools functionality from your laptop (or whatever host you use to connect to your server via SSMS). If you're the kind of admin who prefers to RDP onto your servers and use SSMS locally, then maybe try to break that bad habit; but in general you should be able to use dbatools alongside the rest of your administrative toolset.

If you've never used powershell at all in your environment before, then you may need to coordinate with your sysadmins or networking team to debug some PS Remoting  growing pains or open up ports here and there. As well, if you want to use dbatools functions as a part of SQL Server Agent Jobs, you will need to install (and upgrade!) dbatools locally (or in a way that the module can be loaded by the agent). I recommend this guidance

And as always - for any specific issues, the SQL Community dbatools slack channel is the bee's knees.


Return an error where the ID matches, but the owner_id doesn't I've seen a common pattern like this used to update a record:

UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234

However, what if there is a requirement that a non-existentent ID is differentiated from a owner_id mismatch?

The only easy solution I know of is to fetch the row first, compare owner_id in the client (app server), then proceed with the actual update. Alternatively, one could attempt the update, then if no rows were affected, try selecting the ID to see whether the ID doesn't exist or if the owner_id just didn't match.

Is there a way to communicate an owner_id mismatch so that extra round-trip to the DB can be skipped?

Try this query. I don't know if its gonna work, please let me know. My idea is to select the owner in a A table and the id in a B table. If you get the owner_id and not the id, that means the id is missing. If it returns no data, that means there was no owner.... Maybe you can try different joins like FULL OUTER JOIN and see what happens... Good luck!

select A.owner_id ,
B.id
from(select distinct owner_id from  posts where owner_id=234) A left outer join
(UPDATE posts SET (foo=bar) WHERE id=123 AND owner_id=234 RETURNING owner_id, id) B on A.owner_id = B.owner_id


Import Postgres 8.3 database in Postgres 10.5 I have a database in Postgres 8.3 (and PostGIS 4.2), which I need to import in Postgres 10.5.

I am a DB newbie and would appreciate any pointers to get started. Would you recommend a particular book or series of blogs, or is the process fairly simple?

According to the manual, you have to use pg_dump:

24.4.1. Migrating data via pg_dump
To dump data from one major version of PostgreSQL and reload it in another, you must use pg_dump; file system level backup methods will not work. (There are checks in place that prevent you from using a data directory with an incompatible version of PostgreSQL, so no great harm can be done by trying to start the wrong server version on a data directory.)

(emphasis mine, this is no different for version 10 than for version 9).
You might be able to get this trick to work; it's a simple one-liner but I've never used it myself.


I want to know if SQL Server 2012 protect msdb.dbo.sp_send_dbmail parameters? I use this system stored procedure dbo.sp_send_dbmail to send email from my database, I want to know if this procedure is protect against SQL Injection?

And if you have some reference this will be util for me.

Thanks in advance.

You can SQL Inject it without much issue, sadly.  Here's a simple test I just ran with an elevated account with the results included:

USE [master]
GO

DECLARE @msgbody NVARCHAR(4000)
SET @msgbody = N'Test to see if this SP can suffer from SQL Injection' + CHAR(13) + CHAR(10) + CHAR(13) + CHAR(10)

EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
    , @recipients = 'john.eisbrener@Contoso.com'
    , @reply_to = 'john.eisbrener@Contoso.com'
    , @subject = 'SQL Injection Test'
    , @query = 'CREATE TABLE MyDB.dbo.johnSITest
(
      ID INT IDENTITY(1,1)
    , VAL VARCHAR(50)
)

INSERT INTO MyDB.dbo.johnSITest
    (VAL)
VALUES
    (''yes''), (''you''), (''can'')'
    , @body = @msgbody
GO

Mail (Id: 36756) queued.

SELECT *
FROM MyDB.dbo.johnSITest
GO

ID          VAL
----------- --------------------------------------------------
1           yes
2           you
3           can

(3 row(s) affected)

DROP TABLE MyDB.dbo.johnSITest
GO

Now if the account executing it doesn't have elevated rights, this should limit the exposure of the SP.  The minimum permissions needed for an account to execute this SP are as follows:

  Permissions

  Execute permissions for sp_send_dbmail default to all members of
  the DatabaseMailUser database role in the msdb database.
  However, when the user sending the message does not have permission to
  use the profile for the request, sp_send_dbmail returns an error
  and does not send the message.

The Principle of least privilege should always dictate your security approach, but again, yes it can be SQL Injected much like anything that ingests Dynamic SQL.

EDIT: In response to your follow-up question in the comments, you can further lock this down by wrapping a call to sp_send_dbmail within another stored procedure where the @query parameter is statically defined or more tightly controlled.  This new SP can then take advantage of impersonation (ref1, ref2) and you would then only need to grant EXECUTE rights to explicit users that you want to expose this functionality to.  This should lock things down pretty well and depending on how you limit the use of the @query parameter being passed to sp_send_dbmail, you may be able to completely eliminate the potential for SQL Injection occurring from normal accounts.  Again, any elevated account that can directly call sp_send_dbmail will still have the ability to extort the potential for SQL Injection, but this approach will lock it down about as good as you can.

Because I typed this up hastily, here's also an example in hopes it shows the approach a bit better:

-- CREATE THIS PROCEDURE USING AN ACCOUNT THAT HAS SUFFICIENT ACCESS TO THE DATA AND SP_SEND_DBMAIL
CREATE PROCEDURE [dbo].[limited_sp_send_dbmail]
      @messageBody NVARCHAR(MAX)
    , @recipientList VARCHAR(MAX)
    , @reply_to_address VARCHAR(MAX)
    , @subject_line NVARCHAR(255)
WITH EXECUTE AS SELF
AS
    -- does not allow the usage of @query parameter
    EXEC msdb.dbo.sp_send_dbmail @profile_name='MyDBMailProfile'
        , @recipients = @recipientList
        , @reply_to = @reply_to_address
        , @subject = @subject_line
        , @body = @messageBody
GO

EXECUTE [limited_sp_send_dbmail] @messageBody = N'Hello World!', @recipientList = 'john.eisbrener@Contoso.com', @reply_to_address = 'john.eisbrener@Contoso.com', @subject_line = N'Hello World!'
GO

Mail (Id: 36757) queued.


Does a numeric field always use the same disk space? When you have a numeric field in MySQL(MYISAM) (E.g. Integer) will it always use the same disk space, no matter what number is actually stored?

MySQL's integers take 4 bytes, regardless of the value you store.  (Data storage requirements)

MyISAM supports compression, but only with a compressed row format, which makes the table read-only. I don't think I've ever worked on an application where a read-only table was appropriate.


Using EXECUTE IMMEDIATE inside PL\SQL Block Using EXECUTE IMMEDIATE inside PL\SQL block makes the whole block commit immediately.

begin

INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx');

EXECUTE IMMEDIATE 'CREATE TABLE Shop
(
  GUID         NUMBER(16),
  STATUS       NUMBER(1),
  NAME         VARCHAR2(50 BYTE),
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING';

DBMS_OUTPUT.PUT_LINE('DONE:');

EXCEPTION  -- exception handlers begin
  WHEN OTHERS THEN  -- handles all other errors
  DBMS_OUTPUT.PUT_LINE('Error occured, rollback...');

 ROLLBACK;
end;

As you realized I do not even use COMMIT. About above code,

"Insert into" statement works, but "create table" statement throws exception because there is already a table with the same name in the database.

Both I did not have any commit statement and code block fell exception and rolled back when I looked at the database I saw that insert had worked and there was a new row. It was expected that it should not have been there because there is no commit and also rollback worked..

How can I make rollback when exception occurs.

In Oracle when you create a table or execute an alter statement there is an implied commit. You may want to create the table in a separate function returning a boolean value with "PRAGMA AUTONOMOUS_TRANSACTION"  then the calling procedure would get a true/false response as to whether or not the table was created. you will then be able to commit or rollback your insert.


SQL Server Replication - FTP Snapshot: Policy requires SSL SQL Server 2014 on both Publisher & Subscriber.
Publisher & Subscriber are across the internet.
Replication is Transactional Replication

Error messages:

  The process could not connect to FTP site '' using port 21. (Source:
  MSSQL_REPL, Error number: MSSQL_REPL20032)

  Get help:
  http://help/MSSQL_REPL20032

  220 Microsoft FTP Service

  534 Policy requires SSL.

  (Source: MSSQL_REPL, Error number: MSSQL_REPL12003) Get
  help: http://help/MSSQL_REPL12003

Is there a way around this without relaxing SSL policy for the FTP Server ?

Yes you can. You open the FTP site in IIS and change the SSL Settings from Require to Allow.

You have to check with the owners of the FTP site if this is allowed and I would recommend to use https if possible as this way you are transmitting the username and password using cleartext


Native MariaDb encryption and clustering Is native (table/Column/tablespace) database encryption possible in a clustered environment. The success criteria would be:

Encrypted Logs
Encryption in transit for all data protocols
Native DB encryption
High availability for 2 sites with ~30 ms latency between each
1-2 local copies at each site for Backups/reporting etc
Sub 30 minute site failover time
Minimal application re-engineering
Assume AWS or Vault KMS

I just need a yes or a no at this stage.
Thanks

Encrypted Logs: You can encrypt the InnoDB redo log and binary log. See the limitations on which types of logs are not encrypted.
Encryption in transit for all data protocols: You can enable secure communications, see e.g. Securing Communications in Galera Cluster
Native DB encryption: Yes, assuming you're using the InnoDB storage engine, you can encrypt all tablespaces, individual tables, or everything excluding individual tables.
High availability for 2 sites with ~30 ms latency between each: You can have a Galera cluster on each site, and have asynchronous master-slave or master-master replication between them. The latency depends on the link between the sites.
1-2 local copies at each site for Backups/reporting etc: Galera cluster has a full copy of the databases in each node.
Sub 30 minute site failover time: Failover can be automated with a database proxy such as MariaDB MaxScale or ProxySQL, or a more general proxy like haproxy.
Minimal application re-engineering: MariDB Galera cluster works much like a single MariaDB server instance from the application's point of view, but note that every table must have a primary key (a single or compound key), and that only the InnoDB storage engine is supported. Also note that unless you're only ever writing to a single node at a time (e.g. read-write splitting which can be configured e.g. with MaxScale), your application needs to take into account that transactions can fail both on individual statements within the transaction and after the commit - see Galera’s big gotcha for MySQL users for details.
Assume AWS or Vault KMS: I don't know.

You may want to look into a solution such as SeveralNines' ClusterControl which I believe can automate a lot of this.


Incremetal backup using mysqldump in MySQL I wanted to take an incremental backup using mysqldump for a MySQL database. I have created a script and had taken backup using crontab. It took backup but not incremental; it only takes a full database backup. I didn't find any way to take incremental backup.

My code is given below:

backup.sh Script

#!/bin/bash
YEAR=$(date +%Y)
MONTH=$(date +%m)
DAY=$(date +%d)
HOUR=$(date +%H)
mkdir -p $YEAR/$MONTH/$DAY/$HOUR
mysqldump -uroot -pRedhat@123 mydb > $YEAR/$MONTH/$DAY/$HOUR/backup.sql

Crontab

crontab -e
# Minute   Hour   Day of Month       Month          Day of Week        Command
# (0-59)  (0-23)     (1-31)    (1-12 or Jan-Dec)  (0-6 or Sun-Sat)
30        *         *            *                1-6       exec  '/bin/bash /home/backup.sh'

Backup Files Listing

bash backup.sh

[root@master 17]# ls -lh backup.sql
-rw-r--r--. 1 root root 49M Jun 15 17:05 backup.sql

[root@master 17]# ls -lh backup.sql
-rw-r--r--. 1 root root 362M Jun 18 17:23 backup.sql

I want that it will take only changed blocks backup not full backup like above example.

Additional Information

I am using the free version of MySQL. No enterprise features available.

As pointed out in my previous answer, mysqldump does not provide incremental backup/dump capabilities:

  The mysqldump client utility performs logical backups, producing a set of SQL statements that can be executed to reproduce the original database object definitions and table data. It dumps one or more MySQL databases for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.

Reference: 4.5.4 mysqldump — A Database Backup Program (MySQL Documentation)

mysqldump doesn't contain any option(s) that would allow you to perform an incremental dump.

Reference: Option Syntax - Alphabetical Summary (MySQL Documentation)

Solution

To have an incremental backup without being able to use the mysqlbackup program you would have to perform the following task.

Physical (Raw) Backup

You can perform a backup of the MySQL \data directory to have a current backup of the databases in your MySQL instance. It is recommended to close/shutdown the instance to have a consistent state:

  Backups can be performed while the MySQL server is not running. If the server is running, it is necessary to perform appropriate locking so that the server does not change database contents during the backup.

(emphasis mine)
Reference:  7.1 Backup and Recovery Types (MySQL Documentation)

Incremental Binary Log Backup

  MySQL supports incremental backups: You must start the server with the --log-bin option to enable binary logging; see Section 5.4.4, “The Binary Log”. The binary log files provide you with the information you need to replicate changes to the database that are made subsequent to the point at which you performed a backup. At the moment you want to make an incremental backup (containing all changes that happened since the last full or incremental backup), you should rotate the binary log by using FLUSH LOGS. This done, you need to copy to the backup location all binary logs which range from the one of the moment of the last full or incremental backup to the last but one. These binary logs are the incremental backup; at restore time, you apply them as explained in Section 7.5, “Point-in-Time (Incremental) Recovery Using the Binary Log”. The next time you do a full backup, you should also rotate the binary log using FLUSH LOGS or mysqldump --flush-logs. See Section 4.5.4, “mysqldump — A Database Backup Program”.

Reference: 7.2 Database Backup Methods (MySQL Documentation)

FLUSH LOGS

  Closes and reopens any log file to which the server is writing. If binary logging is enabled, the sequence number of the binary log file is incremented by one relative to the previous file. If relay logging is enabled, the sequence number of the relay log file is incremented by one relative to the previous file.

Reference: FLUSH LOGS

An Example:

Once a week at 8pm:
 a. Create a copy of the MySQL /data directory while the database is down.
 b. After "FULL" Backup: Restart the database.
Every other day at 8pm:
 a. execute a FLUSH LOGS command
 b. create a backup copy of the binary logs directory

You can further optimise the incremental backup by storing the name of the last n-1 binary log file as an additional flag-file and starting off from there. That depends on your requirements.

  DISCLAIMER
  Please test your backup and restore procedure before you implement this in a productive environment. No guarantee given that this procedure will work for you.


SETVAL in a Window Function I'm attempting to do an INSERT into multiple tables using the same key field. According to Sequence Manipulation Functions:

  Important: Because sequences are non-transactional, changes made by setval are not undone if the transaction rolls back.

The above makes me worried that something else might update the sequence during my transaction.

So here is my current query:

BEGIN;
--[other queries and stuff here]
CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*) OVER ()) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;

Does SETVAL get called once for each row in my_temp_table or does it get called once?

Would it be better to have a separate query prior to creating my_temp_table to "reserve" the values before running? For example:

BEGIN;
--[other queries and stuff here]

CREATE TEMPORARY TABLE my_temp_sequence_value ON COMMIT DROP AS
SELECT
    (SETVAL('seq_A',(SELECT "last_value" FROM seq_A) + COUNT(*)) AS seq_val
FROM
    some_other_table;

CREATE TEMPORARY TABLE my_temp_table ON COMMIT DROP AS
SELECT
    (SELECT seq_val FROM my_temp_sequence_value) - (ROW_NUMBER() OVER (ORDER BY some_other_table.field1 DESC))) AS primarykeyid,
    some_other_table.morefields
FROM
    some_other_table;
--[INSERTs into multiple different tables with primarykeyid here]
COMMIT;

Your usage of setval() is not what one usually expects.  It is used for resetting the sequence.  Feeding new values to IDs and other stuff is done by nextval().  Its function is described so:

  nextval()   Advance sequence and return new value

Important to note that this is done so that two concurrent transactions would never get the same number when calling nextval().  Also, it is not really suitable for producing gapless series - the reason is already cited in your question.


What is the best and most performant MySQL cloud solution? I'm looking for a solid solution (~1GB of DB space). Speed is crucial and I'm happy to spend up to $100 a month on a decent solution. The database will be used by a series of desktop applications developed on .Net.

I've already tested both Xeround and Amazon EC2 solutions, but the results were similar and pretty disappointing.

I would highly appreciate any suggestion based on your experience.

Update:

I've recently tested an EC2 m1.xlarge and a RDS db.m1.large. Similar results as with micro instances. Tests were done from Europe and America. Results:

Europe to EC2/RDS - 22 min.
America to EC2/RDS - 8 min.
local to local - 10 sec.

I'd like to chime in on what has been said in the question and in the comments.

XEROUND

Exactly one year ago today, I wrote an answer about the Xeround DB Service : Database as a service (DbaaS) over dimensions--performance, scalability, & reliability?

Granted, you have the following:

Ability to create a Cloud Database and immediately put data into it without any strings attached
Access to a Whitepaper on the Xeround Storage Engine (Overview) from Feb 2012
There is a good FAQ section provided
There is an API for it

I just instantiated a Xeround Free Version Sevice and ran SHOW ENGINES; and got this

mysql> show variables like '%innodb%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| have_innodb           | NO    |
| ignore_builtin_innodb | OFF   |
+-----------------------+-------+
2 rows in set (0.01 sec)

mysql> show engines;
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
| Engine  | Support | Comment                                                   | Transactions | XA   | Savepoints |
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
| Xeround | DEFAULT | Xeround MySQL storage engine                              | YES          | NO   | NO         |
| MyISAM  | YES     | Default engine as of MySQL 3.23 with great performance    | NO           | NO   | NO         |
| MEMORY  | YES     | Hash based, stored in memory, useful for temporary tables | NO           | NO   | NO         |
+---------+---------+-----------------------------------------------------------+--------------+------+------------+
3 rows in set (0.01 sec)

mysql>

No InnoDB ??? Not much has changed.

How about variables for the Storage Engine?

mysql> show variables like 'xeround%';
+------------------------------------+-------------------------------------------+
| Variable_name                      | Value                                     |
+------------------------------------+-------------------------------------------+
| xeround_alloc_pages                | 1                                         |
| xeround_alloc_stats                | 0                                         |
| xeround_conditional_filtering      | off                                       |
| xeround_conf_dir                   | etc                                       |
| xeround_database_schema_size_limit | 2                                         |
| xeround_database_size_limit        | 10                                        |
| xeround_enable_client_socket_check | ON                                        |
| xeround_index_stat_is_approximate  | ON                                        |
| xeround_instance_id                | 35644                                     |
| xeround_maintenance_mode           | OFF                                       |
| xeround_query_row_cache            | 0                                         |
| xeround_record_count_adjust        | 12                                        |
| xeround_records_in_range_mode      | off                                       |
| xeround_schema_id                  | 115                                       |
| xeround_throughput_credit          | 100                                       |
| xeround_throughput_credit_seconds  | 10                                        |
| xeround_transaction_memory_limit   | 128                                       |
| xeround_transactional_ddl          | OFF                                       |
| xeround_xdapc_ip                   |                                           |
| xeround_xdapc_socket               | /opt/xeround/sys_35412/xdapc/xdapc_socket |
+------------------------------------+-------------------------------------------+
20 rows in set (0.01 sec)

mysql>

Looking at the API, I do not believe you can tune anything. You are basically at the mercy of the Storage Engine.

I have also tried loading data into it and using the INFORMATION_SCHEMA database to report back to me space usage. For the XEROUND Storage Engine, I get back nothing. MyISAM is OK.

There are still other limitations with Xeround (Oct 12, 2012) you have to read and consider.

Amazon RDS

While the service bears the hallmarks of a well-trusted brand name, the service must still be evaluated in terms of two things:

Cost
Configuration
Performance

While cost is at one's discretion, I will discuss the other aspects

CONFIGURATION

You have no access to a my.cnf
Any variables to be configured beyond default requires an API call to the DB Parameters Group followed by a restart of the RDS Instance.
There are two things you cannot change no matter what Memory Class you choose

max_connections
innodb_log_file_size

See my post from Aug 02, 2012 : Local database vs Amazon RDS

PERFORMANCE

This goes hand-in-hand with CONFIGURATION. Any tuning aspects you wish to make are subject to the limitations and functionality of the RDS API. Not all Performance enhancements for InnoDB are possible because of Amazon's policies meant to provide a share-everything environment for normal RDS users (unless you can come up with more $$$ for more IOPs and things of this nature).

EPILOGUE

Amazon EC2 may be your best option if your want more flexibility/autonomy for

configuring MySQL
voluntarily paying

RAM
Disk
IOPs

tuning InnoDB

Given the times you just reported in the question, nothing short of due diligence is required for tuning (possibly rearchitecting) your app for Cloud Usage.


Installing SQL Server Integration Services on Server Core I have Windows Server 2008 R2 Core on virtual machine and want to install SQL Server Integration Services. On the D:/ drive SQL Server 2014 Enterprise .iso is mount.

I am reading a book where it is said that:

  You can use the Setup.exe command-line utility to add SSIS to an
  existing installation on both traditional and Server Core deployments
  of SQL Server 2012. For example, to add the SSIS feature to the
  default MSSQLSERVER instance, run the following command:

Setup.exe /qs /Action=Install /Features=IS /InstanceName=MSSQLServer
/IAcceptSQLServerLicenseTerms

but I am getting the following errors:

Could anyone tell what I am doing wrong?

This is from the Administering Microsoft SQL Server 2012 Database book. Make sure you have the initial SQL Server engine installed. Your syntax is exactly right though. I went through it again 10 minutes ago.

Also your ISO is from SQL Server 2014. Try mounting the SQL Server 2012 version.


User based payment system table designing We've been developing user based payment system.
It's pretty much metering how much users use some contents per hour.
And we send the bill at the end of every month.

I'd love to know how user based payment system table can be implemented.
Any documentation or resources?

[CF]

This is meant for database deign not algorithm.
But in my mind there are two ways to achieve it.
Also if you have an experience to implement something like this, please give me some advice.

First, event status model (  standing for "on/off" status )

whenever you create contents, 1 will be inserted, on the other hand -1 will be inserted if you destroy that.
And we can only calculate from 1 to -1 part.

It's just good because we can skip start point if you already started.

Ex.

create  create create  destroy
    1            1        1           -1
     <=================>

So there are many events inserted in one table (start/stop)

Secondly, contents model

It would be more simple.
contents has columns "created_at" and "destroyed_at".
Just whenever you create/destroy some content, the time will be inserted for each column.

Which is more efficient or any other ways?

Personally the second is little cleaner, but i'd also love to hear some opinions.

Hi, i think this model can fit you needs.


TSQL Why is Top faster with a variable? Morning all,

I have been working on some moderately complex sql to 'obtain' some data from a third party products database, to display it in our own in house applications.

I added in a select to obtain the top record from a table within a subquery (if thats makes sense)

the query took nearly 3minutes to return a final result set of 100 records using

SELECT TOP 1 ...

I had a look online for improvements to what I was trying to achieve and it was suggested that I change my select to use a variable, as below

DECLARE @topCount INT
SET @topCount = 1

SELECT TOP (@topCount) ...

This took the same query from the 3minutes down to 1second, which is great!

But can anyone explain why this is so.

When you do top 1 the query optimizer will build a plan that is built to fetch 1 row as fast as possible.

When you use a local variable the value of the variable is unknown to the optimizer and instead builds a plan that is optimized to fetch 100 rows as fast as possible.

In your case the query plan generated with a row goal of 100 is the better plan to use even when you only want one row.

To verify you can try to add option (recompile) to your query with the variable. In that case SQL Server will use the current value of @topCount as a row goal and since that is 1 you should get the slow plan.


ORA-01031 while creating a view as DBA I'm trying to create a view under my DBA schema on Oracle XE. I already did that on the productive database and it worked with a user with a lot lesser privileges than the DBA role. But now I always get ORA-01031 errors.
Here is my query:

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instanz = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
;

And this is the output I get:

Line: 10 Column:20
SQL-Error: ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to change the current username or password
           without the appropriate privilege. This error also occurs if
           attempting to install a database without the necessary operating
           system privileges.
           When Trusted Oracle is configure in DBMS MAC, this error may occur
           if the user was granted the necessary privilege at a higher label
           than the current login.

I even tried it with the SYS account, but with the same result. Am I missing something important?

create table usr_t_user_reg
(username varchar2(30),
db_instance  varchar2(30),
primary key(username,db_instance)
)
/

CREATE OR REPLACE VIEW usr_v_user_not_reg AS
  SELECT username "User", db_instance "Instance",
    (
      CASE
        WHEN username IN
        (
          SELECT username
          FROM sys.dba_users
          MINUS
          SELECT username
          FROM usr_t_user_reg
        ) THEN 'not registered'
        WHEN username IN
        (
          SELECT username
          FROM usr_t_user_reg
          MINUS
          SELECT username
          FROM sys.dba_users
        ) THEN 'no longer present'
      END
    ) "Status"
  FROM usr_t_user_reg
  WHERE db_instance = 'TEST_DB'
    AND username NOT IN
    (
      SELECT username
      FROM usr_t_user_reg
      INTERSECT
      SELECT username
      FROM sys.dba_users
    )
/

Running the script with sqlplus gives the following error message
Table created.

          FROM sys.dba_users
               *
ERROR at line 8:
ORA-01031: insufficient privileges

this is one of the errors where the error description of the Oracle Database Error Messages 11g Release 2does not give a usefule hint

ORA-01031: insufficient privileges
Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges. When Trusted Oracle is configure in DBMS MAC, this error may occur if the user was granted the necessary privilege at a higher label than the current login.
Action: Ask the database administrator to perform the operation or grant the required privileges. For Trusted Oracle users getting this error although granted the the appropriate privilege at a higher label, ask the database administrator to regrant the privilege at the appropriate label.

In the Oracle Database SQL Language Reference 11g Release 2 you can find the following prerequisite for creating a view

The owner of the schema containing the view must have the privileges necessary to either select, insert, update, or delete rows from all the tables or views on which the view is based. The owner must be granted these privileges directly, rather than through a role.

Neither SELECT_CATALOG_ROLE role nor DBA role is sufficient for creating your view.
So issue a
GRANT SELECT ON DBA_USERS TO your_user;

and now your view will work.
Instead of the object privilege SELECT ON DBA_USERS you can grant the system privilege SELECT ANY DICTIONARY. In  contrast to SELECT_CATALOG_ROLE this is not a role but a system privilege.
Note that the error message spots to line 8 of the CREATE VIEW statement that is the line that actually contains the object that causes the error.


Safe way to perform DB script If I am reading the documentation correctly, issuing this command

psql -v ON_ERROR_STOP=1 database < script.sql

where script.sql starts with BEGIN

BEGIN;
ALTER TABLE...
some other DDL...

COMMIT;

should mean that if one of the commands in script.sql fails, the whole script is rolled back?

(I am looking for a way to issue a DB upgrade that atomically succeeds or fails.)

Yep, that's basically right. FYI, the ON_ERROR_STOP business is just a hint to psql that you want it to stop (and, in your case, effectively rollback by closing the connection) immediately upon an error, instead of flooding you with a bunch of messages like "ERROR:  current transaction is aborted" for each subsequent command in the transaction.

There are a few exceptions, see some hints in this answer. But if you try to stick a command in your script.sql which is not allowed to run inside a transaction block, PostgreSQL should give you an error like "... cannot run inside a transaction block" and your transaction should get rolled back.

Of course, you'd be well advised to try your migration script on a test/backup instance (you do test your backups somewhere, right?) first.


SQL Server : low disk space impacts CPU? Is there any relation between disk space and CPU utilization?

We experienced the issue with very high CPU utilization on SQL Server 2008. At the same time, we were in lack of free space on the server. Once we cleaned up some space - CPU utilization was back to norm.

Is this a coincidence or there is a documented explanation for this?

NTFS is susceptible to low disk space. When you fall below 10% file system drivers may start creating various bottlenecks (not only with CPU).


Full scans/sec metric seems very high, dropped after killing 2 queries Our app today (java spring web application) suffered a major slump and Investigating the SQL Server metrics I found what seems to me a very high full scans/sec value.

I killed the 2 SPIDs that where responsible for the query (a big report that after the execution is written in Jasper Report) and the value dropped instantly.

Those are two screenshots before and after the kill:

Before:

After:

I'm not very expert in SQL Server since I'm not the DBA, but that values looks very worrying to me, can you confirm it?

Instance is SQL Server 2014.

Thanks

These metrics often have to be looked at in parallel with other metrics.  Simply doing a table scan is not a bad thing in itself.  Often the query engine will pick a table scan even if an index exists if it's faster, for example on a tiny table.  You need to look at other metrics, such as Disk Read/Sec & Disk Write/Sec, along with PLE and others.

Note that your memory metrics did not change at all, and you're still at 100% cache hit ratio, assuming you waited before taking the screenshot.  What it did affect was the CPU usage, which I didn't notice you mentioning as a concern, but if you are concerned about it, there it is.  Note that you reduced 3 ms of latch wait times, which means most likely you had some sort of writes or a high isolation level.

If your table scans are causing performance issues it'll probably show up in the other metrics or in your blocking reports based on your isolation level.  If you look at your PLE it didn't seem like the drops in scans did anything to it.  Maybe you need to give it more time but look to see if that drastically rises.  That'll be 1 indication that there was pressure because of the scans.  Collect the other metrics and review them before and after.

Also I'm sure you already know but be very careful about using the kill command.  In this case it was a read only query, in a write it will not perform a rollback thus voiding your ACID compliance.  Consider a DBCC INPUTBUFFER('spid') first if you're not already using that.


The order of columns in a table From what I've seen so far, when we define a table, normally we put the primary key as the first column. If there are miscellaneous columns that are common in all tables, they come at the bottom of the table definition. For example: Id (the pk) the first column and IsActive and timestamp at the bottom.

I am wondering if this practice has got more benefits other than only consistency across the database. Does the order of the columns affect the performance? How? I need to justify my opinion in the team that placing the primary keys and the common keys such as IsActive (Boolean column shows whether the record is active or not), randomly in the tables is not a good practice!

The developers create the entities in entity model in the Visual Studio 2010 and if they add a new attribute to an existing entity apparently VS does not put them in a right order. That's their excuse to overlook the order of the columns in a table. Does it affect the performance?

I appreciate your thoughts.

The on-disk structure of a table is independent of the order of columns the CREATE TABLE. This means that performance is not affected.

Each entry (this applies to both data and index) looks like

Header
Fixed length columns
Null bitmap
Variable length columns
Row version

For more, see Inside the Storage Engine: Anatomy of a record

There are a few cases where it can matter. But you won't usually find them day to day: normaly in repro scripts you'll find here or in blogs. For example, see comments to my SO answer here: https://stackoverflow.com/a/6121952/27535 and the link in comments below by @SQL Kiwi

However, from a consistency, maintainability and readability, I'd have PK columns first with columns in the same order as the actual PK key columns. Saying that, I don't normally rebuild tables to ensure that columns are in the "correct" place. It really does make little difference except to my OCD.


COUNTING results per row in self referenced table I have a table called Clients. And at this table, I have an idFather to know who added this row to the table.

Sample:

id | user    | idFather | createdAt
---+---------+----------+-----------
01 | raphael | null     | 01-06-2016
02 | jose    | 01       | 03-06-2016
03 | ryan    | 01       | 02-06-2016
04 | alyssa  | 03       | 04-06-2016

I'm trying to count how many clients each client invited to the system this month.

I'm using this query to count, but I'm doing the counting using PHP. I want to do it in MySQL.

SELECT COUNT(*) FROM client
    WHERE idFather = 1
    AND MONTH(createdAt) = MONTH(CURRENT_DATE - INTERVAL 1 MONTH)

For now, I'm doing it like this:

Get all results from database, and for each client, executing the query above. I'm looking to execute a query and get it counted from the database without the need look at each one with PHP. Today I have just 60 clients in the table, but next month, it can be 1 thousand, or more. It will not work properly with php.

Thanks for the help!

EDIT:
I'm looking to get a query result like this:

id | user    | idFather | createdAt  | totalInvited
---+---------+----------+------------+-------------
01 | raphael | null     | 01-06-2016 | 2
02 | jose    | 01       | 03-06-2016 | 0
03 | ryan    | 01       | 02-06-2016 | 1
04 | alyssa  | 03       | 04-06-2016 | 0

Your answer is on the right track but needs several adjustments to be correct and efficient:

The INNER JOIN will only give you clients with 1 or more children, not the ones with 0 children. You need an outer join for that: client c LEFT JOIN client p ...
If there is a UNIQUE constraint on client (user), then the group by is ok. Otherwise you may have 2 users with same name (but different id). To differentiate them, GROUP BY c.id, c.user is needed, which is not bad to have anyway for efficiency.
The date math and conditions are not entirely correct, will fail on January dates. They need correction. Even better to make the conditions sargable so indexes on createdAt may be used.
If the date conditoins are to be checking the children createdAt timestamps, then they need to be move to the ON clause.

The query improved with all the above points:

SELECT
    COUNT(p.idFather) AS totalInvited,
    c.user AS clientName
FROM client c
  LEFT JOIN client p
    ON  p.idFather = c.id
    AND p.createdAt >= LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 2 MONTH
    AND p.createdAt  < LAST_DAY(CURRENT_DATE) + INTERVAL 1 day - INTERVAL 1 MONTH
GROUP BY c.id, c.user
ORDER BY totalInvited DESC ;


Network Packet Payload Error when Trying to Create Distributed Availability Group I am attempting to create a distributed AG on SQL 2016 SP1, but keep getting the following errors.  I have tried this in a VirtualBox (local desktop) and actual VMWare virtual environment but getting the same errors each time.

The AGs creates successfully on both WFCs, but when I try to "ALTER AVAILABILITY GROUP [Dist AG Name] JOIN" on the primary replica of the 'remote' AG, the command completes successfully, but I immediately get this error in the log:

  A connection timeout has occurred while attempting to establish a
  connection to availability replica 'AG1' with id
  [FCAA4083-D6B8-1BAC-9E8C-6AECE34513E6]. Either a networking or
  firewall issue exists, or the endpoint address provided for the
  replica is not the database mirroring endpoint of the host server
  instance.

On the primary replica of the 'local' AG, I start getting these errors in the log repeatedly:

  Length specified in network packet payload did not match number of
  bytes read; the connection has been closed. Please contact the vendor
  of the client library. [CLIENT: 192.168.56.104]

These are the bytes that show up in Event Viewer event:

0000: AC 45 00 00 14 00 00 00   ¬E......
0008: 14 00 00 00 41 00 47 00   ....A.G.
0010: 31 00 53 00 45 00 52 00   1.S.E.R.
0018: 56 00 45 00 52 00 31 00   V.E.R.1.
0020: 5C 00 41 00 47 00 31 00   \.A.G.1.
0028: 49 00 4E 00 53 00 54 00   I.N.S.T.
0030: 31 00 00 00 00 00 00 00   1.......

In both environments, I am only dealing with a single subnet and a single domain.  I've also tried removing all special characters from VM hostnames, instance names, AG names, and listener names during troubleshooting.

Wondering if anyone has ran into this before.  I don't see anything in the SP2 release notes that suggests its a bug, but I'm willing to try an SP2 upgrade if anyone thinks its worthwhile. (Unfortunately I am not in a position to be able to upgrade my prod environment for some time which is why I haven't tried it yet.)

I can also post full endpoint/AG/listener creation scripts if anyone is willing to try to reproduce or provide troubleshooting tips.

Thanks!

Well I finally found the problem and it was my own dumb mistake.  When you create the distributed availability group, for the LISTENER_URL clauses, you have to specify the listener DNS names, but the mirroring endpoint ports.  I was specifying the listener DNS names and the listener ports.

For example, here is my endpoint definition (note port 5022):

CREATE ENDPOINT [Hadr_endpoint]
    STATE=STARTED
    AS TCP (LISTENER_PORT = 5022, LISTENER_IP = (192.168.159.102))
    FOR DATA_MIRRORING (ROLE = ALL, AUTHENTICATION = WINDOWS NEGOTIATE
, ENCRYPTION = REQUIRED ALGORITHM AES)
GO

Here is my listener definition (note port 1435):

ALTER AVAILABILITY GROUP [AG1]
ADD LISTENER N'AG1LISTENER' (
WITH IP
((N'192.168.232.203', N'255.255.255.0')
)
, PORT=1435);
GO

And here is the CORRECT way to define the distributed AG (note listener DNS name but endpoint port):

CREATE AVAILABILITY GROUP [DistAG]
WITH (DISTRIBUTED)
AVAILABILITY GROUP ON
    'AG1' WITH
    (
    LISTENER_URL = 'tcp://AG1LISTENER:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    ),
    'AG2' WITH
    (
    LISTENER_URL = 'tcp://AG2LISTENER:5022',
    AVAILABILITY_MODE = ASYNCHRONOUS_COMMIT,
    FAILOVER_MODE = MANUAL,
    SEEDING_MODE = AUTOMATIC
    );
GO

This is explained very clearly in the huge purple note highlighted in this article that I somehow completely overlooked before:  https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/configure-distributed-availability-groups?view=sql-server-2017


Insert Into Statement With OpenQuery I have constructed the following syntax but I keep getting an error of:

  Msg 156, Level 15, State 1, Line 26
  Incorrect syntax near the keyword 'INSERT'.

What do I need to change in my syntax so this executes appropriately?

    SET @SQL = N';WITH CTE AS
        (SELECT *
         FROM OPENQUERY(XXX.XXX.X.X,
        ''Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempid = jb.arempid
               WHERE xr.hiredate BETWEEN ('+ CHAR(39) + CHAR(39)
               + CAST(@startdate AS NVARCHAR(500)) + CHAR(39) + CHAR(39) + N')
               AND (' + CHAR(39) + CHAR(39) + CAST(@enddate AS NVARCHAR(500)) + CHAR(39)
            + CHAR(39) + N') GROUP BY cm.empName'')
            INSERT INTO Data (EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE';

            EXEC sp_executesql @sql;

This is actual syntax:

    ;WITH CTE AS
                    (SELECT *
                     FROM OPENQUERY(XXX.XXX.X.X, ,
        ''Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.empname')
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;

Insert is occurring on the local server. The table named Data exists on the same server I am executing the syntax on.

Your dynamic SQL should end up looking like this.

   ;WITH CTE AS
         (SELECT * FROM OPENQUERY(XXX.XXX.X.X,
        'Select
        cm.empName EmpName
        ,case when wp.empCat IN (1,2) THEN CAST(COALESCE(ep.annSal,0) AS DOUBLE PRECISION) else 0 end As AnnualSal
        ,case when wp.empCat = 10 THEN CAST(ep.annSal AS DOUBLE PRECISION) else 0 end As AnnSal2
        FROM empPay ep
        INNER JOIN empcodes ac
        ON ep.empID = ac.empID
        LEFT OUTER JOIN empcat wp
        ON wp.empCat = ac.empCat
        Left join rst xr
        ON ep.userID = xr.userID
        LEFT JOIN site jb
        ON ep.userID = jb.userID
        INNER JOIN emp cm
        ON cm.arempID = jb.arempID
               WHERE xr.hiredate BETWEEN BETWEEN (''01/01/2017'')
               AND (''01/31/2017'') GROUP BY cm.arcustname'
            )--End OPENQUERY
        ) --END CTE
            INSERT INTO Data(EmpName,AnnualSal,AnnSal2)
            Select * FROM CTE;


Possible to rewrite this query as a join? I'm fairly certain it's possible to rewrite this as a join query but I'm just not sure how to approach it:

SELECT
   events.*,
   (SELECT MAX(updated_at)
    FROM   events e
    WHERE  e.user_id = events.user_id
           AND type = 'follow'
           AND e.updated_at < events.updated_at
           AND events.type = 'unfollow'
   ) AS last_follow_date
FROM   events
WHERE  user_id = 1;

Database structure:

CREATE TABLE `events` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `created_at` timestamp NULL DEFAULT NULL,
    `updated_at` timestamp NULL DEFAULT NULL,
    `user_id` int(10) unsigned NOT NULL,
    `type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `follower_pk` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    KEY `events_user_id_foreign` (`user_id`),
    CONSTRAINT `events_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`)
 ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Some data:

INSERT INTO `users` (`id`, `name`, `email`)
VALUES (1, 'Aydin Hassan', 'email@website.com');

INSERT INTO `events` (`id`, `created_at`, `updated_at`, `user_id`, `type`, `follower`, `follower_pk`)
VALUES
(1, '2015-01-01 00:00:00', '2015-01-01 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(2, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'unfollow', 'annie.leibovitz', '10'),
(3, '2015-04-04 00:00:00', '2015-04-04 00:00:00', 1, 'follow', 'edward.weston', '11'),
(5, '2015-10-10 00:00:00', '2015-10-10 00:00:00', 1, 'follow', 'annie.leibovitz', '10'),
(4, '2017-01-01 10:10:00', '2017-01-01 10:10:00', 1, 'unfollow', 'annie.leibovitz', '10');

The table stores events regarding followers of a user on a makeshift social platform. The query is adding (if the event is a unfollow type) the last date that the follower started following the user.

The correct results should be:

+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
| id |     created_at      |     updated_at      | user_id |   type   |    follower     | follower_pk |  last_follow_date   |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+
|  1 | 2015-01-01 00:00:00 | 2015-01-01 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
|  2 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-01-01 00:00:00 |
|  3 | 2015-04-04 00:00:00 | 2015-04-04 00:00:00 |       1 | follow   | edward.weston   |          11 | NULL                |
|  4 | 2017-01-01 10:10:00 | 2017-01-01 10:10:00 |       1 | unfollow | annie.leibovitz |          10 | 2015-10-10 00:00:00 |
|  5 | 2015-10-10 00:00:00 | 2015-10-10 00:00:00 |       1 | follow   | annie.leibovitz |          10 | NULL                |
+----+---------------------+---------------------+---------+----------+-----------------+-------------+---------------------+

As a bonus question: Is there maybe a better way to store and query this data?

You can get what you want with the following JOINed query, which ressembles very much your original one:

SELECT
    e.id, e.created_at, e.updated_at, e.user_id, e.type, e.follower, e.follower_pk,
    max(e2.updated_at) AS last_follow_date
FROM
    events e
    LEFT JOIN events e2
        ON      e2.user_id = e.user_id
            AND e2.type = 'follow'
            AND e2.updated_at < e.updated_at
            AND e.type = 'unfollow'
WHERE
    e.user_id = 1
GROUP BY
    e.id
ORDER BY
    e.id, e.updated_at ;

With regard to performance, your queries will benefit from an index such as:

-- Index to improve performance
CREATE INDEX idx_events_user_id_type_updated_at
    ON events (user_id, type, updated_at) ;

The three columns user_id, type and updated_at appear in the JOIN condition. The order in which you want them is user_id (you make an = comparison, and is supposed to have high cardinality --i.e.: very many values--), type (you make an = comparison, cardinality = 2), updated_at checked with an inequality... MySQL is not good enough to use it as part of the index seek, but will make the index a covering one, and that will save from checking the original table.

Alternative: a slight twist on your original query, might save some time:

SELECT
   e1.*,
   case when type = 'unfollow' then
      (SELECT MAX(e2.updated_at)
       FROM   events e2
       WHERE
                  e2.user_id = e1.user_id
              AND e2.type = 'follow'
              AND e2.updated_at < e1.updated_at
       )
   else NULL end AS last_follow_date
FROM   events e1
WHERE  e1.user_id = 1;

You can check everything at dbfiddle here