Concatenate string based on a condition I want to assign a value to a new column based on a condition for other rows associated with a user_id.
A user can see the same step with different devices at different times. However, only the first step must be taken into consideration.
e.g. step2 was seen by both mobile and desktop, but was first seen by mobile at 2021-03-16 14:03:16.
Likewise step4 was seen by both desktop and tablet, but first desktop.
To sum up, each step first seen by
step1 = mobile
step2 = mobile
step3 = mobile
step4 = desktop

Since the only device change happened at step4, then assign desktop > mobile to all records for that user_id.
How can I get the first step per user and device as in the expected result below?
Sample data:
+---------+-------+---------------------+---------+
| user_id | step  |     created_at      | device  |
+---------+-------+---------------------+---------+
| user1   | step1 | 2021-03-16 14:03:16 | mobile  |
| user1   | step2 | 2021-03-16 14:04:07 | mobile  |
| user1   | step2 | 2021-03-16 14:03:47 | desktop |
| user1   | step3 | 2021-03-16 14:03:55 | mobile  |
| user1   | step3 | 2021-03-16 14:04:00 | mobile  |
| user1   | step1 | 2021-03-16 14:04:02 | desktop |
| user1   | step2 | 2021-03-16 14:03:16 | mobile  |
| user1   | step3 | 2021-03-16 14:04:07 | mobile  |
| user1   | step4 | 2021-03-16 14:04:08 | desktop |
| user1   | step4 | 2021-03-16 14:04:09 | tablet  |
+---------+-------+---------------------+---------+

The expected result:
+---------+-------+---------------------+---------+---------------------+
| user_id | step  |     created_at      | device  | device_concatenated |
+---------+-------+---------------------+---------+---------------------+
| user1   | step1 | 2021-03-16 14:03:16 | mobile  | mobile > desktop    |
| user1   | step2 | 2021-03-16 14:03:16 | mobile  | mobile > desktop    |
| user1   | step3 | 2021-03-16 14:03:55 | mobile  | mobile > desktop    |
| user1   | step4 | 2021-03-16 14:04:08 | desktop | mobile > desktop    |
+---------+-------+---------------------+---------+---------------------+

Given the data as is (as of 11:38 UTC, 2021-04-08), this might help (see PostgreSQL fiddle here):
CREATE TABLE tab
(
  user_id TEXT NOT NULL,
  step    TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL,
  device  TEXT NOT NULL
);

Populate it (slightly rearranged from original data to put it in order - user_id, step & created_at - makes it easier to figure out what's going on):
INSERT INTO tab
VALUES

( 'user1', 'step1', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step1', '2021-03-16 14:04:02', 'desktop'),

( 'user1', 'step2', '2021-03-16 14:03:16', 'mobile'),
( 'user1', 'step2', '2021-03-16 14:03:47', 'desktop'),
( 'user1', 'step2', '2021-03-16 14:04:07', 'mobile'),

( 'user1', 'step3', '2021-03-16 14:03:55', 'mobile'),
( 'user1', 'step3', '2021-03-16 14:04:00', 'mobile'),
( 'user1', 'step3', '2021-03-16 14:04:07', 'mobile'),

( 'user1', 'step4', '2021-03-16 14:04:08', 'desktop'),
( 'user1', 'step4', '2021-03-16 14:04:09', 'tablet');

I'll show the steps that led me to the solution - might be helpful (remove surplus field as appropriate for your requirements):
SELECT
  user_id, step, created_at, device,
  FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at),
  CONCAT(
  (FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)),
  ' > ',
  (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS device_concatenated,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
FROM tab
ORDER BY user_id, step, created_at;

Result (better viewed on fiddle):
user_id step    created_at  device  first_value first_value lead    device_concatenated rn
user1   step1   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step1   2021-03-16 14:04:02 desktop mobile  2021-03-16 14:03:16     mobile >    2
user1   step2   2021-03-16 14:03:16 mobile  mobile  2021-03-16 14:03:16 desktop mobile > desktop    1
user1   step2   2021-03-16 14:03:47 desktop mobile  2021-03-16 14:03:16 mobile  mobile > mobile 2
user1   step2   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:16     mobile >    3
user1   step3   2021-03-16 14:03:55 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 1
user1   step3   2021-03-16 14:04:00 mobile  mobile  2021-03-16 14:03:55 mobile  mobile > mobile 2
user1   step3   2021-03-16 14:04:07 mobile  mobile  2021-03-16 14:03:55     mobile >    3
user1   step4   2021-03-16 14:04:08 desktop desktop 2021-03-16 14:04:08 tablet  desktop > tablet    1
user1   step4   2021-03-16 14:04:09 tablet  desktop 2021-03-16 14:04:08     desktop >   2

Then, we use the above result as a sub-query as follows:
SELECT user_id, step, created_ts, device, dc AS device_concatenated
FROM
(
  SELECT
    user_id, step, created_at, device,
    FIRST_VALUE(device)     OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    FIRST_VALUE(created_at) OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at) AS created_ts,
    LEAD(device)            OVER (PARTITION BY user_id, step
                                  ORDER BY     user_id, step, created_at),
    CONCAT(
    (FIRST_VALUE(device)   OVER (PARTITION BY user_id, step
                                 ORDER BY     user_id, step, created_at)),
    ' > ',
    (LEAD(device)            OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at)))
  AS dc,
  ROW_NUMBER() OVER (PARTITION BY user_id, step
                            ORDER BY     user_id, step, created_at) AS rn
  FROM tab
  ORDER BY user_id, step, created_at
) AS t
WHERE rn = 1
ORDER BY user_id, step, device, created_ts;

Result:
user_id   step  created_ts             device   device_concatenated
  user1  step1  2021-03-16 14:03:16    mobile       mobile > desktop
  user1  step2  2021-03-16 14:03:16    mobile       mobile > desktop
  user1  step3  2021-03-16 14:03:55    mobile        mobile > mobile
  user1  step4  2021-03-16 14:04:08   desktop       desktop > tablet

Which is pretty close to the desired result. To obtain the desired result, we need to know how/why for step3, mobile > desktop is valid and mobile > mobile is not? Same goes for step4 - why is desktop > tablet not correct? There isn't even a single entry for mobile for step4... On receiving an explanation, I will edit as appropriate!


Rebuilding an entire Microsoft SQL Server Database Is there a suggested way to rebuild an entire SQL Server database from another database in SQL Server 2012 SP2?

Let me explain... we have a database that we are trying to enable partial containment on and then add it to our AlwaysOn cluster. We are getting nothing but deadlock errors while trying to set the containment to partial. The database has gone from SQL Server 2008 to 2008R2 to 2012 (no SP, SP1, SP2) and had TDE for a while.

Microsoft support has been looking at it for a couple of days, but it's not looking very promising and I think we're now leaning towards some form of corruption. Our application and everything else seems to be able to still access it just fine, it's just setting this one little flag isn't working. Very strange issue.

The only reference I found online that shows the same exact issue is from a Technet forum post in Portuguese (translated link), but it was never resolved.

At this point, I can't wait any longer and I just want to rebuild this data in a new database. I suppose I could script the entire thing out, but this DB is around 20GB already, so that would be one nasty script.

Does anyone have a suggestion on how to recreate a new database based off of another - without the standard Backup / Restore? Is scripting it out the right way to go?

Use SSMS to generate scripts for the entire database DDL.

Use the script to create a new, empty database.

Use BCP to export the data from all tables.  Use BCP to import that data into the new database.

MAKE SURE YOU BACKUP THE ORIGINAL DATABASE before DROPing it (if you in fact intend to drop it at all).  MAKE SURE YOU TEST RESTORE THE BACKUP.


Most efficient way to set up a database handling millions of links? I'm looking at making a site that will potentially have up to a million users, and each user will have hundreds/thousands of links connected to a set of sites, as well as other info. I'm using mysql and was thinking of setting up the database with tables for each site in the set along with tables for the other info...

Table 1

-----------------------------
|UserID  |  Links for site 1|
-----------------------------
  512    |  www.site1.com/4564
  512    |  www.site1.com/1298
  512    |  www.site1.com/09312
  512    |  www.site1.com/126543
  512    |  www.site1.com/231
  6143   |  www.site1.com/32
  6143   |  www.site1.com/566478

Table 2

-----------------------------
|UserID  |  Links for site 2|
-----------------------------
  512    |  www.site2.com/click/45564
  512    |  www.site2.com/click/898
  512    |  www.site2.com/click/7312
  512    |  www.site2.com/click/56543
  512    |  www.site2.com/click/4331
  6143   |  www.site2.com/click/9832
  6143   |  www.site2.com/click/78

.......

Table 20

-----------------------------
|UserID  | Links for site 20|
-----------------------------
  512    |  www.site20.com/564
  512    |  www.site20.com/125498
  512    |  www.site20.com/479312
  512    |  www.site20.com/6543
  512    |  www.site20.com/2713
  6143   |  www.site20.com/32346
  6143   |  www.site20.com/6478

I probably won't be storing the whole link, just the bit on the end and then rebuilding the link before I display it... unless storing the whole link makes my site load faster.

Then of course:

-----------------------------
|UserID  |  referral links  |
-----------------------------
  512    |  www.site1.com/join/4564
  512    |  www.site2.com/register/1298
  512    |  www.site3.com/register/09312
  512    |  www.site4.com/join/126543
  512    |  www.site5.com/referral/231
  6143   |  www.site1.com/join/32
  6143   |  www.site2.com/register/566478

And

-----------------------------
|UserID  |  Profile Links   |
-----------------------------
  512    |  www.site1.com/profile/4564
  512    |  www.site2.com/user/1298
  512    |  www.site3.com/user/09312
  512    |  www.site4.com/profile/126543
  512    |  www.site5.com/user/231
  6143   |  www.site1.com/profile/32
  6143   |  www.site2.com/user/566478

And a few more for wishlists, About Me, Profile Pic, etc.

My question is, is this the most efficient way to do something like this?  Or would it be better to use 1 row for all of user 512's site1.com links, and 1 row for 512's site2.com links, etc, since I'll usually be pulling all of them almost every time I pull any of them?  If so, how would I do that?
Please explain your answers well, and links to more info would be appreciated, since I'm a complete newbie to databases.

Thanks!

More info...

The average user (95%) will probably have 500-10,000 links in each table (if I don't decide to limit it) and there will be probably between 60-100 site tables. I don't see ever having over a million users, and I'll probably be lucky to have 20,000, but better to plan ahead just in case.

Not all users will have links for all sites.

Data usage-

Most of the time I'll be doing something along the lines of:

Display all site3.com links for user 512.

I'd also like to be able to display a random set of (for example) all site5.com links.

I assume this means indexing both columns of my table?

(Not an answer, but some pitfalls that make it difficult to design this for efficiency.)

More than half the users will have only one link.
Some users will each have over 100K links.
More than half the sites will have only one page.
Some sites will each have over 100K pages.

What does it mean?  It means that any form of indexing, compression, etc, needs to be flexible enough to handle such extremes.

(I experimented with a similar experiment, quitting after a million or so links.  Those are extrapolations of what I encountered.)


Installing SQL Server 2008 R2 Service Pack 1 I have SQL Server 2008 R2 installed on my computer. I want to install Service Pack 1 for SQL Server 2008 R2. Do I need to select upgrade option or new installation during installation of service pack or remove current installation and install SQL Server again.

I am trying to select "new installation or add new features" on first page of installation and then  selecting current instance on next page but It is giving some error like registry value invalid.

It sounds like you're running the wrong file.

Download one of the files from http://www.microsoft.com/download/en/details.aspx?id=26727 and then run that. It shouldn't prompt you for "new installation or add new features", it should prompt you for which instance you want to apply the service pack to.


How can I return top 10 rows from each state How can I return the top 10 rows for each category?

Say I have a table named xyz. It has a column called state, in the political unit sense of the word.

I want the top 10 rows from each state. Do I use case so that one can see from which state the row is?

I am using SQL Server 2012.

The top 10 would be from FCFS, the order in which they are entered.

Columns are

FirstName, LastName, Address, city, state, zip, phone etc.

For example, I have rows in my table from states AL, CT, NY etc. I want the top 10 rows based on FCFS from each state.

In case of postgresql:

You didn't write which version you have from postgresql. You can use LATERAL JOINs (available from 9.3) or window functions.

Example for window function:

select *, row_number() over (partition by category_id order by [whatevercolumn]) as rownum
 from records r
 join category c on c.id = r.category_id
 where c.id IN (1, 2, 3, 4) -- If you want to filter
) a
where rownum <= 10
order by category_id;

Example for LATERAL JOIN:

select r.* from category
join lateral (
    select * from records
    where category_id = category.id
    order by [whatevercolumn]
    limit 10
) r on true
where category.id IN (1, 2, 3, 4) -- If you want to filter
order by category.id;

The performance difference can be significant. For more info you can take a look at this benchmark: Group by limit per group in PostgreSQL

In SQL Server:

I'm not a user of SQL server but as far as I know it supports both. The syntax might be a bit different but the same idea applies.


Transferring large amount (84 million rows) of data efficiently I have about 84 millions rows.  Of those all of them needs to be transferred to a separate database on the same server, then I delete to delete about 60 millions rows from the source database.

The 84 million rows are all in the same table.  That table alone accounts for 90% of the whole database.

So...
Source: 84 million rows -> 24 millions rows
Destination: 0 rows -> 84 million rows

Source is running full recovery mode, destination will be running simple.

I am wondering what would be the most efficient way to do this?

Plan A:

1) INSERT INTO destination SELECT * FROM source

2) TRUNCATE source

3) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan B:

1) Restore a backup of source database as the destination database

2) Drop every tables except the one needed on the destination database

3) TRUNCATE source

4) INSERT INTO source SELECT * FROM destination WHERE keep_condition = 1

Plan C:

1) INSERT INTO destination SELECT * FROM source

2) DELETE source WHERE keep_condition = 0

or something else?

Thanks

I would add that, however you decide to approach this, you'll need to batch these transactions. I've had very good luck with the linked article lately, and I appreciate the way it takes advantage of indexes as opposed to most batched solutions I see.

Even minimally logged, those are big transactions, and you could be spend a lot of time dealing with the ramifications of abnormal log growth (VLFs, truncating, right-sizing, etc.).

Thanks


Difference between 'GrantedMemory' and 'MaxQueryMemory' attributes in Showplan XML Following new attribute to the Showplan XML schema SQL Server 2014 or 2016 was introduced by KB3170112.

MaxQueryMemory under MemoryGrantInfo: Maximum amount of memory
available for individual query grant in KB, if the query requires
memory to run.

Attribute GrantedMemory existed before above was announced. Now both are there in Showplan XML.
Here is an example:

From above picture if I add the value of RequestedMemory and RequiredMemory total is =62,864 KB. Where is the value 295,344 KB  for MaxQueryMemory coming from or what does that value really mean?
I also looked at 2017 Schema, which has below definition.

MaxQueryMemory: Maximum memory in KB allowed for single query.

It's a snapshot of the same information that you can get from sys.dm_exec_query_resource_semaphores. I believe that it's target_memory_kb for the regular resource semaphore multiplied by the maximum query grant percent for the Resource Governor group that the query is executing in. If RG is not enabled then just use the default of 25%.

On the servers that I looked at, I generally see about 28% of server memory not available for query memory grants. So once I've run a workload the max query grant that's available for a query is 0.25 * 0.72 * MaxServerMemory.

Here's a query that you can run that finishes almost instantly but asks for a large memory grant:

DECLARE @zero INT = 0;

WITH CTE AS (
    SELECT high
    FROM master..spt_values
    WHERE @zero = 1
)
SELECT *
FROM CTE t1
CROSS JOIN CTE t2
CROSS JOIN CTE t3
ORDER BY t1.high + t2.high + t3.high;

If I force that query to timeout then I get the following in the actual plan:

MaxQueryMemory does not change as a result of the timeout. It isn't directly affected by other queries currently executing and using query memory. It's indirectly affected by anything that changes target memory KB for the resource pool.

This information could be useful if you have a bunch of different Resource Governor pools and groups and want to validate that a query goes to the correct place. It can also provide context to the total size or configuration of a server that you don't have access to. It's another way to tell if a query timed out waiting for a memory grant, but there are already other ways to do that. The intended use case may be to give more information about performance problems caused by target memory changing quite a bit on servers, but I've never seen that happen personally. Maybe it can happen on servers with multiple instances of SQL Server running?


MySQL Insert Values Into Normalized Relational Database (multiple tables) at the same time I have normalised my database to 4NF. How do I now insert values at the same time when records have been split into multiple tables? most of the child tables reference their parent table CUSTOMER column customer_ID as their foreign keys. The primary key in the parent table is auto-incremented.
There are many tables set up this way, but I thought the following 3 samples would sufficiently paint the picture:

CREATE TABLE customer (
  CustomerID INT(11) NOT NULL AUTO_INCREMENT,
  CusCatID INT(2) NOT NULL DEFAULT 1 COMMENT 'This is supposed to reference the customer catergpries table. But its giving a foreihn key/ index probpem?',
  `First Name` VARCHAR(50) NOT NULL,
  LastName VARCHAR(255) NOT NULL,
  EmailAddress VARCHAR(50) NOT NULL,
  GENDER BIT(1) NOT NULL,
  DOB DATE NOT NULL,
  Cus_Status_ID INT(11) NOT NULL DEFAULT 1,
  PRIMARY KEY (CustomerID)
)

ALTER TABLE customer
  ADD CONSTRAINT FK_customer_CusCatID FOREIGN KEY (CusCatID)
    REFERENCES customer_category(CusCategoryID) ON DELETE NO ACTION;

ALTER TABLE customer
  ADD CONSTRAINT FK_customer_Cus_Status_ID FOREIGN KEY (Cus_Status_ID)
    REFERENCES status(StatusID) ON DELETE CASCADE ON UPDATE CASCADE;

CREATE TABLE customer_accounts (
  Cus_ID INT(11) DEFAULT NULL,
  AccountNo INT(11) NOT NULL
)

ALTER TABLE customer_accounts
  ADD UNIQUE INDEX AccountNo(AccountNo);

ALTER TABLE customer_accounts
  ADD CONSTRAINT FK_customer_accounts_AccountNo FOREIGN KEY (AccountNo)
    REFERENCES account(AccountNo) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE customer_accounts
  ADD CONSTRAINT FK_customer_accounts_Cus_ID FOREIGN KEY (Cus_ID)
    REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

 CREATE TABLE customer_authentication (
  auth_id_cusId INT(11) NOT NULL,
  Username VARCHAR(255) NOT NULL,
  Password VARCHAR(255) NOT NULL,
  PIN INT(6) NOT NULL,
  PRIMARY KEY (auth_id_cusId)
)

ALTER TABLE customer_authentication
  ADD UNIQUE INDEX Username(Username);

ALTER TABLE customer_authentication
  ADD CONSTRAINT FK_customer_authentication_auth_id_cusId FOREIGN KEY (auth_id_cusId)
    REFERENCES customer(CustomerID) ON DELETE CASCADE ON UPDATE CASCADE;

You'll want to use LAST_INSERT_ID() to get the identity value that was generated after the INSERT for the PrimaryKey of the Parent table then use it for the ForeignKey to INSERT into the children tables.
Per the documentation:

With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit)
value representing the first automatically generated value
successfully inserted for an AUTO_INCREMENT column as a result of the
most recently executed INSERT statement. The value of LAST_INSERT_ID()
remains unchanged if no rows are successfully inserted.

You can see more information in this StackOverflow post.


MySQL repair deleted data - Any chance of recovery? Well, this has been a long Saturday morning. MySQL was throwing an error when dumping backups. That error was...

mysqldump: Error 1030: Got error 5 from storage engine when dumping table mytable at row: XXXXXXXX

This is a MyISAM table with 300 million rows of data. It's become corrupt in the past, and MySQL repair would always resolve the issue. Aside from the error during mysqldump, the data appeared normally on the production site.

I assumed it was a similar issue to before, and the table needed to be repaired. The repair command ended unusually fast with the result...

database.mytable    repair  warning Number of rows changed from 303970410 to 22082981

database.mytable    repair  Error   Error reading file './database/mytable.MYD' ...

database.mytable    repair  status  OK

And just like that, 93% of the data was gone, with the MYD and MYI files replaced. Any idea what might have went wrong here? Any chance of recovering data? I'm assuming no, and I'll have to go with the most recent valid backup, but I'm scratching my head. Appreciate any help.

Edit:

MySQL 5.1.73
CentOS 6.5

myisamchck did its best job, so that may be the maximum what you can get out of mytable.MYD.

The structure of MyISAM record is pretty simple, integers are stored in packed format, strings are prefixed with their length(1 or 2 bytes). So you can write a C program with pre-defined pattern that would try to fetch records of out the MYD file. This approached worked for me  on wp_comments table.

Two major problems here:

A record may be fragmented. Recollecting the fragmented record in a corrupt MYD seems to be extremely difficult if not impossible problem.
Table structure should allow to identify a good record in a stream of bytes.

P.S.

InnoDB is recommended storage engine for a reason.


Switching replication master in cascading replication scenario Imagine I have 3 PostgreSQL 9.3 databases (A, B, and C) set up in a cascading, streaming replication scenario (i.e. am NOT manually shipping WAL files, am having Postgres ship them over TCP internally) like so:

A --> B
      |
      V
      C

where A is the replication master for B, and B is the replication master for C.

If B fails (goes offline but doesn't failover) and I need to make C use A as replication master as quickly as possible, is it safe for me to skip rebuilding C from A and just set up A as C's replication master? I would do so by turning off the Postgres service on C, modifying the master host in the replication.conf file on C to point to A, and then starting the Postgres service on C. I've done this in practice and C replicates off of A without any data loss in my tests, but are there some cases where doing this could result in data corruption on C?

Intuitively, this should be a safe operation. Since neither B nor C do a failover, they will remain on the same timeline as A, and since the new master for C is "upstream" of the previous master, C can never be ahead of the WAL position of the new master.

Followup question (to be answered iff the previously described operation is deemed "safe"):
Imagine that I perform the above steps, B comes back online, I rebuild it from A and start binary streaming replication from A such that I now have the following scenario:

A --> B
|
V
C

where A is the replication master for both B and C.

I want to make B become C's replication master again, and like before, I want to minimize C's downtime. Can I stop the Postgres service on C, ensure that the WAL playback position on B is further along than that of C (waiting for B to catch up with C if C was "ahead" of B in WAL playback), then switch the replication master host for C to point at B in the recovery.conf file and start the Postgres service?

Once again, I've done this in practice and it seems to work, but I want to be sure that there aren't any scenarios that result in data corruption on C. In this second case, assume that replication from A to B is verified to be working correctly when Postgres on C is turned off (if replication from A to B breaks while C is still replicating from A, there can be divergence between C's and B's databases, which will obviously cause problems). Is this a safe operation?

Yes, it's safe. B is an identical copy of A that must always be at or behind it in time, so it is safe to switch replication from B to A.

Personally, though, I'd use WAL shipping from A as my fallback. I'd leave streaming replication configured to use B. If it's unreachable, C will try the WAL archives, and it'll find the WAL archives from A and replay from them, periodically retrying B until it can connect again.


MariaDB audit plugin logs all queries instead of DML only I was testing the "server_audit" plugin of MariaDB, which I configured like this:

plugin-load-add = server_audit
server_audit_logging = ON
server_audit_events = QUERY_DML
server_audit_output_type = FILE
server_audit_file_path = /path/to/audit.log
server_audit_query_log_limit = 1048576
server_audit_file_rotate_size = 1073741824
server_audit_file_rotations = 1

and I am currently running MariaDB 10.1.29 with MariaDB Audit Plugin version 1.4.3.

Based on the docs, the value QUERY_DML should be

  Same as QUERY, but filters only DML-type queries (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements)

emphasis mine.

After restarting the server, the log files has correctly been created, however it is logging also other queries, like for example plain SELECTs.

Is my configuration somewhat incorrect, or is it perhaps an error on MariaDB side?

As of version 1.4.4 of the plugin, there is now a QUERY_DML_NO_SELECT type that should give you what you are expecting.

  Same as QUERY_DML, but doesn't log SELECT queries. (since version 1.4.4) (DO, CALL, LOAD DATA/XML, DELETE, INSERT, UPDATE, HANDLER and REPLACE statements)

This was introduced in MariaDB 5.5.42, MariaDB 10.0.17, and MariaDB 10.1.4.


Show table records based on user condition in SQL Server I have a web application backed by SQL Server database. It has a report, that combines students of selected semesters and a specific way. the following image show the interface for selecting semester and number of students.

The user selects the semesters and enters numbers of students.
To be clear, suppose a user enters 3 students and selects 1th and 2th semesters. The system should return all students which are enrolled in 1th and 2th semester as following:

------------------------
Id  StudentName Semester
------------------------
1       A          First
4       D          First
7       G          First
2       B          Second
3       C          Second
5       E          Second
9       I          First
10      J          First
12      L          First
6       F          Second
8       H          Second
11      K          Second

The dataset is as follow:

Student Table

-----------------
Id  StudentName
-----------------
1       A
2       B
3       C
4       D
5       E
6       F
7       G
8       H
9       I
10      J
11      K
12      L

Semester Table

-----------------
Id  Semester
-----------------
1       First
2       Second
3       Third

Enrollment Table

----------------------------
Id  StudentID   SemesterID
----------------------------
1      1            1
2      2            2
3      3            2
4      4            1
5      5            2
6      6            2
7      7            1
8      8            2
9      9            1
10     10           1
11     11           2
12     12           1

Any idea on how to show the records of the above table as mentioned?

One way to approach this is to start with the Enrollment table. Using the ROW_NUMBER ranking function, you can assign row numbers in groups of SemesterID:

SELECT
  *,
  RN = ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC)
FROM
  dbo.Enrollment
;

For your example you will get output like this:

Id  StudentID  SemesterID  RN
--  ---------  ----------  --
1   1          1           1
2   2          2           1
3   3          2           2
4   4          1           2
5   5          2           3
6   6          2           4
7   7          1           3
8   8          2           5
9   9          1           4
10  10         1           5
11  11         2           6
12  12         1           6

Now subtract one from RN and then divide the result by the required number of students. If the number of students is 3, then you will find that the aforementioned calculation turns a sequence of

1, 2, 3, 4, 5, 6, ...

into

0, 0, 0, 1, 1, 1, ...

That is to say, you will get a sequence consisting of groups of repeating numbers, three per group (because we divided by three). You can use that result as the primary sorting criterion.

Choose SemesterID as your second criterion and Id or StudentID as the third, so now the query will look like this:

SELECT
  *,
  GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
FROM
  dbo.Enrollment
ORDER BY
  GroupID ASC,
  SemesterID ASC,
  Id ASC
;

And this will be its output based on the sample provided:

Id  StudentID  SemesterID  GroupID
--  ---------  ----------  -------
1   1          1           0
4   4          1           0
7   7          1           0
2   2          2           0
3   3          2           0
5   5          2           0
9   9          1           1
10  10         1           1
12  12         1           1
6   6          2           1
8   8          2           1
11  11         2           1

You can see that the order of rows already matches the expected output, you just need to replace StudentID and SemesterID with their descriptions and get rid of the GroupID. Declare the above query as either a derived table or a CTE and then join tables Student and Semester to it. For my answer I chose the CTE option, and this is what I got:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student AS st ON rd.StudentID = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

Output:

Id  StudentName Semester
--  ----------- --------
1   A           First
4   D           First
7   G           First
2   B           Second
3   C           Second
5   E           Second
9   I           First
10  J           First
12  L           First
6   F           Second
8   H           Second
11  K           Second

As you can see, the query we have so far works perfectly for the provided example: the results now match your expected output precisely. But the query is not finished yet: the filter on semester is missing.

Assuming that semesters will be provided as a comma-separated list of names/descriptions, what you can do is turn that list into a row set, with one name/description per row. You can then match that row set against the Semester table to include just the selected semesters. For the purpose of this answer I am assuming that you are using SQL Server 2016 or later version, so that the built-in SPLIT_STRING function can be used to help implement the semester filter:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / 3
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT('First,Second', ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

In an earlier version of SQL Server you will have to provide your own method of turning a string list into a row set of substrings. Thankfully, solutions abound on the Internet, including the many questions and answers dedicated to this problem both here and on Stack Overflow. Aaron Bertrand discusses various methods in his blog article, Split strings the right way – or the next best way, which can be a good starting point.

As for our query, there remains one last step to take: parametrisation. Both the number of students and the list of selected semesters were hard-coded in the above queries. The final solution will need to use parameter references instead of the hard-coded values, something like this:

WITH
  RankedData AS
  (
    SELECT
      *,
      GroupID = (ROW_NUMBER() OVER (PARTITION BY SemesterID ORDER BY Id ASC) - 1) / @NumberOfStudents
    FROM
      dbo.Enrollment
  )
SELECT
  rd.Id,
  st.StudentName,
  sm.Semester
FROM
  RankedData AS rd
  INNER JOIN dbo.Student  AS st ON rd.StudentID  = st.Id
  INNER JOIN dbo.Semester AS sm ON rd.SemesterID = sm.Id
WHERE
  sm.Semester IN (SELECT value FROM STRING_SPLIT(@SemesterList, ','))
ORDER BY
  rd.GroupID ASC,
  rd.SemesterID ASC,
  rd.Id ASC
;

A live demo of this solution is available at db<>fiddle.uk.

Disclosure: this answer borrows partly from the now deleted answer by Josh Darnell.


SqlPackager.exe what is SqlPermissionStatement? I am using SqlPackager to determine the changes between two databases as  described here.

What I cannot figure out is what the SqlPermissionStatement is referring to? Is it a byproduct of the missing user?

In other words I don't know how to make the change to my target database to make this disappear from the deployment report.

I identify one reason of such entry in my project(SSDT).
I have following entry in code:

GRANT EXECUTE ON OBJECT::[dbo].[up_Foo] TO [$(Domain)\$(DBUserName)] AS [dbo];

Then in Deployment script I can find this:

GRANT EXECUTE ON OBJECT::[dbo].[up_Foo] TO NULL AS [dbo];

And related:

<Operations>
    <Operation Name="Create">
        <Item Value="Permission" Type="SqlPermissionStatement" />
    </Operation>
</Operations>

Therefore I believe I have some problem with CMDVARs substitution. What generates grant execute to null. And grant execute to null is always interpreted as new grant.


Backup cut-off time for Full, Differential, Log, File, Partial backups My understanding was that all backups contain data up to the time/point when backup operation was completed.

Administering a SQL Database Infrastructure - Exam Ref 70-764 says:

  Full: This contains the entire contents of the database and any changes made to the database during the backup operation. Consequently, a full backup represents the database at the point in time when the backup operation finished.

(emphasis mine)

However, for differential backups, the book implies something different:

  Differential: This contains only the differences between the last full database backup and the point in time when the differential backup operation was executed.

Is this correct? Or is this simply imprecise language? Does "executed" here mean the time the differential backup started or completed?

How about for other backup types (Log, Partial, File backup) the book doesn't say this exactly?

See this related question for the answer regarding full backups. The definitive technical source referenced there is a 2009 article by Paul Randal (who was a member of the Microsoft SQL dev team):

  A full database backup provides a complete copy of the database and provides a single point-in-time to which the database can be restored. Even though it may take many hours for the backup process to run, you can still only restore the backup to a single point (effectively at the end of the backup, but I'll discuss exactly what that point is later in this article). A full backup does not allow recovery to any point in time while the backup was running.

If you want to pick apart what he means by "effectively" here, you have to understand the steps a full backup goes through:

Checkpoint the database and note the current log sequence number
Start reading the data files
Stop reading the data files, and note the current log sequence number
Read enough of the log file to capture transactions completed between steps 1 and 3.

A full backup contains the proper data to restore the database to the point between steps 3 and 4. And since step 4 typically completes quickly (unless there was tremendously high activity during the backup), this is effectively the end of the backup.

Regarding Differential backups, Randal (in the same article linked above) says:

  A differential backup performs the same operations as a full backup, but only contains all the data that has changed or been added since the previous full backup.

He then goes through the detail of how exactly SQL identifies which pages have changed since the last full, but that only changes how steps 2-3 above operate, it doesn't change anything else. I have to assume that implies the same answer, that restoring a full then a differential backup will restore to a point very near the end of the differential backup, with the caveats we've described above.

I'll look for an authoritative source about log backups.

Sorry, I don't have any detail about file or partition backups, I would guess they operate similar to a full, just with a different set of data pages.


The benefits of ALGORITHM=INPLACE in ALTER TABLE queries For some reason I can't find any information on this.
A colleague insists me to use that on an Alter Table query but can't really say why.
It is a small table concerning the application's basic configurations with a very small amount of rows.
What are the benefits of ALGORITHM=INPLACE in a query that adds one MEDIUMINT NOT NULL type of column to a table which will have 10 rows at most?
The database is MySql.

A 10-row alter will be so fast that the "Algorithm" won't make a noticeable difference.  You probably won't see a diff for 1000 rows.
Some history:
Originally ALTER had exactly one way of being performed under the hood.  This made the code easy, but the performance not as good as it could be.

Create a new table like the existing table
make the modifications to that empty table
INSERT ... SELECT ... to copy all the data over
Juggle table names and drop the old data.

Beginning with 5.6, there was a concerted effort to optimize specific cases -- mostly to avoid the full table copy.  This led to a variety of syntaxes and options.  Confusing.
Fortunately, if you ask for an algorithm that is not applicable to the action in question, it will spit at you.  Simply change the algorithm and try again.
I think, without proof, that ALTER TABLE will use the best Algorithm available for the use case in hand.  And the Algorithm options are there in case something goes wrong and you need to force a different algorithm.
So why have ALGORITHM?  I think it is like a lot of VARIABLES -- if something goes wrong, the end-user has the ability to "fix" the problem by turning off something.  This does come into play in various evaluation optimzations.  A visible case is FORCE INDEX.
Alas, the documentation of ALGORITHM is wimpy.


SSIS Fault Tolerant Data Connection I feel like a may be standing too close to this one.
I've inherited an SSIS package that has two data connection objects. On OLE DB connection (to perform work) and an ADO.NET connection (to a different server) for logging.

The server on the far end of the ADO.NET connection can be flaky and I need to make the logging fault tolerant. The logging task is already set not to fail the package, nor the parent, on failure. However the process isn't getting that far.
The package fails because the connection fails, not because the task fails.

The logic path of this package is Log Start > Do Stuff > Log Completion. In this particular example Log Start succeeded, as did Do Stuff. Log Completion failed because the server was suddenly unavailable.

The error message at SSIS report level is

  Log Status Completion Error: Failed to acquire connection 'Logging
  ADO NET'. Connection may not be configured correctly or you may not
  have the right permissions pm this connection.

All suggestions are welcome. (Thanks and Merry Christmas)

Not sure if this is a solution or a work-around. Either way, it solved the problem.
I disabled the SQL Task and created a Script Task. The connection happens within a try/catch. If it fails, the flow goes to a "do-nothing" catch block. The package sees the completion as success and progress continues. A screen image of the new Script Task and its code are included.

#region Namespaces
using System;
using System.Data;
using System.Data.SqlClient;
using Microsoft.SqlServer.Dts.Runtime;
using System.Windows.Forms;
#endregion

namespace ST_4ddaab95fb4d46ef9521d651f5227575
{
[Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]
    public partial class ScriptMain :
Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase
    {
    public void Main()
    {
        Package pkg = new Package();
        Variables vars = null;
        VariableDispenser vd = pkg.VariableDispenser;
        SqlConnection LoggingConn;
        SqlCommand LoggingCommand;
        vd.GetVariables(ref vars);
        try
        {
            LoggingConn = (SqlConnection)Dts.Connections["Logging ADO NET"].AcquireConnection(null);
            LoggingCommand = new SqlCommand("[etl].[usp_LogPackageStatus]", LoggingConn);
            LoggingCommand.CommandType = CommandType.StoredProcedure;
            LoggingCommand.Parameters.AddWithValue("@PackageID", vars["System::PackageID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@PackageName", vars["System::PackageName"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@MasterExecutionID", vars["$Package::MasterExecutionID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@ExecutionID", vars["System::ExecutionInstanceGUID"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@Status", vars["User::Status"].Value.ToString());
            LoggingCommand.Parameters.AddWithValue("@StartTime", vars["User::StartTime"].Value.ToString());
            LoggingCommand.ExecuteNonQuery();
        }
        catch (Exception ex)
        {
            //Do nothing

            //For Debugging
            //MessageBox.Show(ex.Message);
        }
        Dts.TaskResult = (int)ScriptResults.Success;
    }

    #region ScriptResults declaration
    enum ScriptResults
    {
        Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,
        Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure
    };
    #endregion
}

}


Performance of rare SELECT vs. frequent INSERT in timeseries data I have a simple timeseries table

movement_history (
    data_id serial,
    item_id character varying (8),
    event_time timestamp without timezone,
    location_id character varying (7),
    area_id character varying (2)
);

My frontend developer is telling me that the cost is too high if he wants to know where an item is at a given timestamp because he has to sort the table. He wants me to add another timestamp field for the next event so he doesn't have to sort. Yet that is going to more than double the cost of my code to insert a new movement as I will need to query for the previous entry for the item, update that and then insert the new data.

My inserts of course far outnumber his queries in frequency. And I have never seen a timeseries table which included an entry for the time of the next event. He's telling me my table is broken because his infrequent query requires a sort. Any suggestions?

I don't know what query he is using but I would be doing this:

select * from movement_history
where event_time <= '1-15-2015'::timestamp
and item_id = 'H665AYG3'
order by event_time desc limit 1;

We currently have about 15K items they are at most entered into the database once a day. However we will soon have 50K of items with sensor data that is updated every 1 to 5 minutes.

I do not see his query being performed very often but another query to get the current status of the pallets will be.

select distinct on (item_id) *
from movement_history
order by item_id, event_time desc;

This server is currently running 9.3 but it could be running on 9.4 if it needs to.

Create an index on (item_id, event_time).

It will jump to the specified item_id, jump to the specified event_time for that item_id, and then move back one.  No sorting involved.


Find common attributes among given products in SQL Server I am trying to find all attributes that are common to a given set of products (Stock_ID numbers). I have a table called Stock_Attributes that contains product info. It looks something like this:

+----------+--------------+------------+
| Stock_ID | Attribute_ID | Value      |
+----------+--------------+------------+
|     1001 |            2 | Farmhouse  |
|     1001 |            8 | Blue       |
|     1001 |            9 | Bronze     |
|     1001 |           10 | Metal      |
|     1001 |           10 | Wood       |
|     1002 |            2 | Industrial |
|     1002 |            9 | Chrome     |
|     1002 |           10 | Metal      |
|     1003 |            2 | Industrial |
|     1003 |            8 | Red        |
|     1003 |           10 | Glass      |
|     1003 |           10 | Metal      |
+----------+--------------+------------+

Stock_ID and Attribute_ID are both have INT as the data type. Value is a sql_variant since some attributes are Dates or Bits. As you can see, any product can have any combination of attributes and more than one attribute of the same type. The desired result for the above sample set would be one just one row, since these products only have one attribute in common (typically there will be several in common):

+--------------+------------+
| Attribute_ID | Value      |
+--------------+------------+
|           10 | Metal      |
+--------------+------------+

I do not need to return the Stock_ID numbers in this query since we are starting with that information in a temporary table called #TempItemNumbers. I simply need to return a set of Attribute_IDs and Values that are common to all Stock_ID numbers listed in #TempItemNumbers, which is generated via user input.
I do not typically use cursors or dynamic SQL so I am not very familiar with these methods, but I'm thinking this problem will require one or both.
Please help!

Use a COUNT() on the rows in your temp table to simplify your query, e.g.:

SELECT
    sa.Attribute_ID,
    sa.Value,
    Count(DISTINCT sa.Stock_ID) as Matches
FROM Stock_Attributes sa
INNER JOIN #TempItemNumbers tin
    ON tin.Stock_ID = sa.Stock_ID
GROUP BY sa.Attribute_id,
    sa.Value
HAVING Count(*) = (SELECT Count(*) FROM #TempItemNumbers)

Only those rows where all the items have matching attributes will have the same count as the number of items in your temp table. Making the COUNT() be of the distinct Stock_IDs handles duplicate entries for a given Attribute/Value pair for an item - just in case. You can replace that with a simple COUNT(*) if you are guaranteed not to have duplicates.


Postgres query slow despite index being used I have the following tables:
The main lead table with close to 500M rows:
create table lead
(
    id                  integer,
    client_id           integer,
    insert_date         integer  (a transformed date that looks like 20201231)
)

create index lead_id_index
    on lead (id);

create index lead_insert_date_index
    on lead (insert_date) include (id, client_id);

create index lead_client_id_index
    on lead (client_id) include (id, insert_date);

And then the other tables
create table last_activity_with_client
(
    lead_id       integer,
    last_activity timestamp,
    last_modified timestamp,
    client_id     integer
);

create index last_activity_with_client_client_id_index
    on last_activity_with_client (client_id) include (lead_id, last_activity);

create index last_activity_with_client_last_activity_index
    on last_activity_with_client (last_activity desc);

create index last_activity_with_client_lead_id_client_id_index
    on last_activity_with_client (lead_id, client_id);

create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);

create table lead_last_response_time
(
    lead_id            integer,
    last_response_time timestamp,
    last_modified      timestamp
);

create index lead_last_response_time_last_response_time_index
    on lead_last_response_time (last_response_time desc);

create index lead_last_response_time_lead_id_index
    on lead_last_response_time (lead_id);

create table date_dimensions
(
    key                      integer,  (a transformed date that looks like 20201231)
    date                     date,
    description              varchar(256),
    day                      smallint,
    month                    smallint,
    quarter                  char(2),
    year                     smallint
    past_30                  boolean
);

create index date_dimensions_key_index
    on date_dimensions (key);

I try running the following query on different client_id and it is always slowed down by the bitmap index scan on client_id in the lead_table
EXPLAIN ANALYZE
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
    from lead
             left join (select * from last_activity_with_client where client_id = 13189) last_activity_join on
        lead.id = last_activity_join.lead_id

             left join lead_last_response_time lead_last_response_time on
        lead.id = lead_last_response_time.lead_id

             join date_dimensions date_dimensions on
        lead.insert_date = date_dimensions.key

    where (date_dimensions.past_30 = true)
      and (lead.client_id in (13189))
),
     TempCount AS (
         select COUNT(*) as total_rows
         fromt TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

A few results:
explain analyze result 1
explain analyze result 2
As you can see, it's using the index but it's quite slow. Always more than 50 seconds. What can I do to make this query run faster? I have some freedom to change the query and the tables too.

with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189),
     TempCount AS (
         select COUNT(*) as total_rows
         from TempResult
     )
select *
from TempResult, TempCount
order by last_response_time desc NULLS LAST
limit 25 offset 1;

Possibly "TempCount" can be dropped and replaced by a window function "count() over ()".
with TempResult AS (
    select DISTINCT lead.id AS lead_id,
                    last_activity,
                    last_response_time
      from lead
      left join last_activity_with_client last_activity_join
                 on  lead.client_id = last_activity_join.client_id
                 and lead.id = last_activity_join.lead_id
      left join lead_last_response_time lead_last_response_time
                 on lead.id = lead_last_response_time.lead_id
    where lead.insert_date >= to_char(current_date-30,'YYYYMMDD')::integer
      and lead.client_id = 13189)
select *, count(*) over () TempCount
from TempResult
order by last_response_time desc NULLS LAST
limit 25 offset 1;


How can i detect whom a person reports to using a self-referencing employee table? I have a typical self-referencing employee table. The data is hierarchical and I use the following UDF to encapsulate a simple CTE to derive an output of a particular person's direct and indirect reports. That is to say I can pass in a single employee and get back a listing of them and everyone under them.

I want to create a similar function that would allow me to take a boss and employee param and detect if the employee reports into the boss (or the boss param is indeed the boss - direct or indirect of the employee)

whats the best way to accomplish this logic using the DB structure outlined below as what I have today?

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] NOT NULL,
    [managerId] [int] NULL,
    [FirstName] [nvarchar](50) NOT NULL,
    [LastName] [nvarchar](50) NOT NULL,
    [FullName] [nvarchar](100) NOT NULL,
    [CorpEmailAddress] [nvarchar](510) NULL
) ON [PRIMARY]

ALTER FUNCTION [dbo].[fnGetEmployeeHierarchy]
(
  @EmployeeId int = null
)
RETURNS TABLE
AS
RETURN
(
  WITH yourcte AS
  (
    SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name
    FROM Employees
    WHERE EmployeeId = isnull(@EmployeeId,EmployeeId)
    UNION ALL
    SELECT e.EmployeeId, e.ManagerID, e.AMRSNTID, e.FullName--, e.Name
    FROM Employees e
    JOIN yourcte y ON e.ManagerID = y.EmployeeId
  )
SELECT EmployeeId, ManagerID, AMRSNTID, FullName--, Name
FROM yourcte
)

Since to me it's already a well known and solved problem, and since the commentary on here seems to agree with me:

https://stackoverflow.com/questions/3859882/sql-recursive-function-to-find-managers

Are you wanting to return the results for each lowest employee in the set, and that's what's making it hard?


How this simple Stored Procedure ended up in Deadlock Thanks for all and this forum is really helpful.

After few minutes of system running we get “Try restarting the transaction.”
And its end up in deadlock.

We use MySQL 5.5.28 and the Java code calling this Stored Procedure is in synchronised block. However, I am still we getting this deadlock. When I ran, show engine innodb status;, I see the following query in deadlock:

INSERT INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
        VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);

Please refer below for entire Stored Procedure and the table it's updating:

CREATE PROCEDURE sp_CreateCtrMsgTagIfNotExist ( siteId INT, logTimestamp DATETIME, IntervalId INT, digestMatched TINYINT(1), OUT returnpk INT )
BEGIN

   SET returnpk = 0 ;
   START TRANSACTION;

   SELECT Site_Msg_Tag_Id INTO returnPK FROM Site_Msg_Tag WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
   AND Interval_Id = IntervalId ;

   IF (returnpk IS NULL OR returnpk = 0  )
   THEN /*1*/
        INSERT INTO Site_Msg_Tag (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
        VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);

        SELECT LAST_INSERT_ID() INTO returnpk;
    END IF;/*1*/

    COMMIT;

END |

DELIMITER ;

Table :

| Site_Msg_Tag | CREATE TABLE `Site_Msg_Tag` (
  `Site_Msg_Tag_Id` int(11) NOT NULL AUTO_INCREMENT,
  `Unit_Id` int(11) NOT NULL,
  `Log_Timestamp` datetime NOT NULL,
  `Interval_Id` int(11) NOT NULL,
  `Digest_Matched` tinyint(1) DEFAULT '0',
  PRIMARY KEY (`Site_Msg_Tag_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Ts_Unit` (`Unit_Id`,`Log_Timestamp`,`Interval_Id`),
  UNIQUE KEY `ix_Site_Msg_Tag_Unit_Ts` (`Log_Timestamp`,`Unit_Id`,`Interval_Id`),
  KEY `FK_Site_Msg_Tag_Interval_Id` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Interval_Id` FOREIGN KEY (`Interval_Id`) REFERENCES `Interval` (`Interval_Id`),
  CONSTRAINT `FK_Site_Msg_Tag_Unit_Id` FOREIGN KEY (`Unit_Id`) REFERENCES `Unit` (`Unit_Id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |

I saw in this thread Will these two queries result in a deadlock if executed in sequence?

in that they solved the issue by writing the query in more efficient way any suggestions ? . Thanks in advance.

Instead of checking to see if the data already exists and then doing an INSERT, try doing an INSERT IGNORE and seeing if the LAST_INSERT_ID() came back with the same value or not on two calls.

CREATE PROCEDURE sp_CreateCtrMsgTagIfNotExist
(
    siteId INT, logTimestamp DATETIME, IntervalId INT,
    digestMatched TINYINT(1), OUT returnpk INT
)
BEGIN

   DECLARE rpk INT;

   SELECT LAST_INSERT_ID() INTO rpk;
   START TRANSACTION;
   INSERT IGNORE INTO Site_Msg_Tag
       (Site_Msg_Tag_Id,Unit_Id,Log_Timestamp,Interval_Id,Digest_Matched)
       VALUES (NULL,siteId,logTimestamp,IntervalId,digestMatched);
   SELECT LAST_INSERT_ID() INTO returnpk;
   IF returnpk = rpk THEN /*1*/
       SELECT Site_Msg_Tag_Id INTO rpk FROM Site_Msg_Tag
       WHERE Unit_Id = siteId AND Log_Timestamp = logTimestamp
       AND Interval_Id = IntervalId ;
       SET returnpk = 0 - rpk;
   END IF;/*1*/
   COMMIT;

END;

Checking LAST_INSERT_ID()

If two calls produced different values, you should make the stored procedure set returnpk as is to indicate that the row was newly inserted.
If two calls produced the same value, then go query for the Site_Msg_Tag_Id. You should make the stored procedure set returnpk as negative to let you know the Site_Msg_Tag_Id was already there.

Give it a Try !!!


SQL and XML insert new Node I have an SQL Query to make an XML Document. Everything works except one thing:

A short insert of my SQL Query at this time is:

select OITM.ItemCode, OITM.ItemName, OITM.Price

Output in XML:

<DocumentElement>

      <ItemCode>12345</ItemCode>
      <ItemName>test</ItemName>
      <Price>20.00</Price>

</DocumentElement>

I want this Structure:

<DocumentElement>

          <ItemCode>12345</ItemCode>
          <ItemName>test</ItemName>
          <Prices>
               <Price>20.00</Price>
          </Prices>

    </DocumentElement>

How can i insert the <Prices> tag?

If you are using FOR XML PATH you can specify nested elements using forward slash (/), like this:

DECLARE @yourTable TABLE (
    ItemCode    INT,
    ItemName    VARCHAR(10),
    Price       MONEY
)

INSERT INTO @yourTable VALUES
    ( 12345, 'test', 20 )

SELECT
    ItemCode,
    ItemName,
    Price AS "Prices/Price"
FROM @yourTable
FOR XML PATH('article'), ROOT('DocumentElement'), TYPE


Reuse alias that shares an existing column name In MySQL I know I can reuse an alias in a query like this:
SELECT
  value_a + value_b as value_sum,
  (SELECT value_sum) + 5 as padded_sum

Is there any way to make this work if padded_sum is also the name of an existing column in one of the joined tables?
http://sqlfiddle.com/#!9/48c2c9/6

For that you need user defined variables

SELECT
  item_id,
  @sum1 := value_a + value_b as value_sum,
  CONCAT('VALUE SUM: ', @sum1 + 5) as label,
  @sum2 := value_a + value_b as value_sum2,
  CONCAT('VALUE SUM: ', @sum2) as label2
 FROM item i
   LEFT JOIN item_sums isu USING (item_id);

Result
item_id     value_sum   label           value_sum2  label2
1           4           VALUE SUM: 9    4           VALUE SUM: 4
2           5           VALUE SUM: 10   5           VALUE SUM: 5
3           11          VALUE SUM: 16   11          VALUE SUM: 11

sample
  SELECT
      item_id,
      value_sum,
      CONCAT('VALUE SUM: ', value_sum + 5) as label,
      value_sum2,
      CONCAT('VALUE SUM: ', value_sum2) as label2
  FROM
  (SELECT
      item_id,
      value_a + value_b as value_sum,
      value_a + value_b as value_sum2
   FROM item i
       LEFT JOIN item_sums isu USING (item_id)) t1;

sample2


SQL Server 2016 Availability Group - 2 Nodes as Primary & Readable Secondary, 1 Node as DR I want to create 3 nodes availability group on SQL Server 2016.

Node 1 and Node 2 is my primary server and stays in primary data center. Each nodes set to readable secondary replica.
Node 3 stays in DR Data Center. I wanna use node 3 when servers in primary data center (node 1 and node 2) are down.

How can I set up this configuration? Should I use 2 quorum disks and uncheck the server in preferred owners (in WSFC configuration)?

I would set up your configuration as follows...

Node and File Share Majority quorum model

Node 1, Node 2 and file share witness at primary dc

Node 3 at DR dc

1 vote to each node in the primary data center & fileshare witness

0 vote to node in the DR data center

preferred owners node 1 & node 2

AG synchronous mode between node 1 & 2 automatic failover

AG asynchronous mode to node 3 manual failover

primary AG on node 1 with readable secondary replicas at node 2. Since you plan to use readable secondary replica, you will have to license node 2 as well as node 1. Node 3 will be covered by software assurance. I assume this is a production environment.


Mongo shell 3.2 has different results on sh.isBalancerRunning() as mongo shell 3.4 I have a mongo shards cluster and a mongos instance. These servers are running on MongoDB 3.4.0. When I use mongo shell 3.4.0 to connect to the mongos, I got below output from sh.isBalancerRunning(),

mongos> sh.isBalancerRunning()
false

but I got a different result when I use mongo shell 3.2.13:

mongos> sh.isBalancerRunning()
true

Is this something not compatible between mongo versions? If yes, why other commands work fine just this command doesn't?

The difference is that 3.4 balancing work is done by config server primary and at 3.2 it was done by one of the mongos services.

To prevent "older" (<3.4) mongos services to start balancing work, config server (>=3.4) makes "lock" document to the local collection, what "states" that one of the mongos services have "balancing lock" set.

That document doesn't prevent (>=3.4) config server to do balancing. That is the reason why older mongos "report" that there is always balancing ongoing!


return scope_identity() question environment sql server 2005

In my stored proc i am calling another stored proc to insert several rows into several tables. The last row inserted i want to pass the identity of that row to the original stored proc. I am doing this by using return scope_identity(); My understanding is return is only used for error messages/status codes. Is this a "good" way of doing what I need to do, or is there another "better" way?

All procedures have an integer return value.  Your method would work, there is no hard and fast rule against it.

Alternatively, you could return an output parameter.

Example:

create procedure YourProcedure
  @ID output
as
  insert into YourTable (bunch of values)
  select @ID = scope_identity()
go

You then call your procedure as follows:

declare @TheID int
exec YourProcedure @TheID output
select @TheID


How to normalize a table with common and non-common fields? and thanks in advance.

I am trying to normalize a table "People", where each entry (person) belong to one of many different categories (e.g. "lawyer", "client", "judge"). Some fields are common across categories (e.g., firstName, lastName).

However, some (set of) fields are unique to each category: for instance, a "client" will likely not have a "LawyerBoardID" number. Therefore, an entry will have a value in that field if and only if the person belongs to the "lawyer" category; in other words, that field will be NULL for all other categories.

This has implications for child tables. One person, many entries in the "Address" table - textbook example. If I split my "People" table in many tables - namely, one per category - then I will have to put as many FK in the "Address" table, right? Therefore, if I have 10 categories, there will be 9 NULL FKs.

Any suggestion? Links to relevant web pages are also very welcome.

Your case looks like an instance of class/subclass (or, if you prefer, type/subtype).  You can find many answers like this one in SO or here in DBA.


Strategies/Resources for implementing data lineage in an ETL process? I'm struggling to articulate my need here.

I've been tasked with developing a significant ETL process dealing with warehouse item costs in a major grocery chain using the full Microsoft Stack.

I'd like to be able to provide our finance team and auditors with the ability to trace the final results back through to the source systems, ideally with metadata like which cost calculations were used.

The Kimball Group and the Data Warehouse Toolkit book all hint at lineage (Subsystem 29!), but I am finding very few resources that help guide an actual implementation. There are a plethora of sites indicating that it is important and should be considered with data governance, and a few sites that are purely academic and go into the science of it but don't really provide practical guidance.

I'm likely not following best practices with this question, but I don't know how else to phrase it. As I said earlier, we are full Microsoft Stack, but I'm open to any resources even if for other tools.

Any and all help is appreciated. Thank you.

Lineage is sometimes implemented in the ETL tool, but the Microsoft stack doesn't really. Since you want to offer the insight to your end users it's probably not even a lot of help if your development environment offers it.

One way you could go about it, and something that I've used successfully in the past (even if it violates a few design principles) is to add a field to my fact tables indicating the source of the fact, which can then be exposed via drilldown or possibly even a fact dimension.

For example if you have a fact table which combines forecasts from erp as well as a CRM system you could have a column "factsource" or whatever which contains a concatenated field with sourcesystem|table|primarykey like this:

 sourceid        | customer    | amount
 ----------------------------------------------------
 CRM|forecast|41 | C001        | 1000
 ERP|quotation|6 | C002        | 5000

Then when a user uses the drilldown functionality in the cube client or some other mechanism to see the facts behind the transaction they could immediately see that the 5K comes from quotation number 6 in the ERP system.

Off course you would need to construct that value in your ETL system while loading the fact table.


How do you identify InnoDB table corruption? I have some tables that are partitioned and have several indexes on a replicated slave. After copying the snap shot (verified safe) to a new slave and upgrading mysqld from 5.1.42 to 5.5.15 and restarting replication, I'm getting InnoDB crashes with the error message "Invalid pointer ..."

These errors have happened across 2 servers with different hardware and O/S. After running:

ALTER TABLE .... COALESCE PARTION n;

the problem goes away for that table.

My question is larger in scope, though, and that is "How do you identify InnoDB table corruption?" or rephrased "How do you assess InnoDB table health?" Is "CHECK TABLE" the only tool available to identify problems pre-crash?

Not sure if it matters, but the crashes occurred running:
Version: '5.5.15-55-log'  socket: '/opt/mysql.sock'  port: 3306  Percona Server (GPL), Release rel21.0, Revision 158

Morgan gives a hint in his comment that InnoDB is constantly checking for corrupted pages by doing checksums on the pages it reads. If InnoDB finds a checksum mismatch, it will crash stop the server.

If you want to speed that process up (instead of waiting for InnoDB to read the corrupted page), you can use innochecksum:

  Because checksum mismatches will cause InnoDB to deliberately shut down a running server, it can be preferable to use this tool rather than waiting for a server in production usage to encounter the damaged pages.

An interesting caveat:

  innochecksum cannot be used on tablespace files that the server already has open. For such files, you should use CHECK TABLE to check tables within the tablespace.

So yes, for  an online table CHECK TABLE is probably the tool (or as pointed out in another answer mysqlcheck if you want to do more than a single database at a time.)

If you can shut down your database you can force it the checksums using innochecksum

Anecdotal:
On a innodb tablespace of 29GB  (with innodb_file_per_table=1), this script took about 2 Minutes

#!/bin/bash
for i in $(ls /var/lib/mysql/*/*.ibd)
do
  innochecksum -v $i
done

As a bonus, though, since you are running Percona, they implemented a new method for fast innodb checksum. I've never used it, but it might speed up the process.


select subqueries with join I am using sub queries using joins. For 11946 rows, the query is taking 18 seconds.

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
Coalesce(Error1, '')
+ CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
+ Coalesce(Error2, '')
+ CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
+ Coalesce(Error3, '') as ErrorList
From(
select [Date], ClientName,
CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END as Error2,
CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END as Error3
From
(Select
       [Date],
       s.clientID,
       c.DisplayName [ClientName],
       si.PayorID [SchedPayor],
       ce.PayorID [EligPayor],
       stf.DisplayName [StaffName]
From Schedule s
       INNER JOIN ScheduleItem si ON s.ScheduleID = si.ScheduleID and s.AccountID = si.AccountID and si.Owner = 1
       INNER JOIN Client c ON s.ClientID = c.ClientID and s.AccountID = c.AccountID
       OUTER APPLY                                                                 (Select ClientID, PayorID From ClientEligibility ce               Where s.ClientID = ce.ClientID and s.[Date] BETWEEN ce.StartDate and ce.EndDate) as ce
       LEFT JOIN Staff stf ON s.StaffID = stf.StaffID and s.AccountID = stf.AccountID
WHERE
       s.accountID = 1) as a) as b

Is there any other way to write query to optimize performance?

This is just formatting to try and understand

So far that OUTER APPLY is what I would look at

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
       Coalesce(Error1, '')
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
       + Coalesce(Error2, '')
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
       + Coalesce(Error3, '') as ErrorList
From ( select [Date], ClientName,
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3
              From
             ( Select s.[Date],
                      s.clientID,
                      c.DisplayName   [ClientName],
                      si.PayorID      [SchedPayor],
                      ce.PayorID      [EligPayor],
                      stf.DisplayName [StaffName]
                 From Schedule s
                 JOIN ScheduleItem si
                   ON s.ScheduleID = si.ScheduleID
                  and s.AccountID  = si.AccountID
                  and s.accountID  = 1
                  and si.Owner = 1
                 JOIN Client c
                   ON s.ClientID  = c.ClientID
                  and s.AccountID = c.AccountID
                OUTER APPLY
                      ( Select ClientID, PayorID
                          From ClientEligibility ce
                         Where s.ClientID = ce.ClientID
                           and s.[Date] BETWEEN ce.StartDate and ce.EndDate
                      ) as ce
                 LEFT JOIN Staff stf
                   ON s.StaffID   = stf.StaffID
                  and s.AccountID = stf.AccountID
             ) as a
     ) as b

My guess is this may get you what you need

Select CONVERT(VARCHAR(10), [Date], 101) as schedDate, ClientName,
       Coalesce(Error1, '')
       + CASE WHEN Error1 is not null and Error2 is not null THEN ' | ' else '' END
       + Coalesce(Error2, '')
       + CASE WHEN Error2 is not null and Error3 is not null THEN ' | ' else '' END
       + Coalesce(Error3, '') as ErrorList
From ( select [Date], ClientName,
              CASE WHEN SchedPayor <> EligPayor THEN 'Payor No Match' END as Error1,
              CASE WHEN EligPayor IS NULL THEN 'No Eligibility' END       as Error2,
              CASE WHEN StaffName IS NULL THEN 'Invalid Staff' END        as Error3
              From
             ( Select s.[Date],
                      s.clientID,
                      c.DisplayName   [ClientName],
                      si.PayorID      [SchedPayor],
                      ce.PayorID      [EligPayor],
                      stf.DisplayName [StaffName]
                 From Schedule s
                 JOIN ScheduleItem si
                   ON s.ScheduleID = si.ScheduleID
                  and s.AccountID  = si.AccountID
                  and s.accountID  = 1
                  and si.Owner     = 1
                 JOIN Client c
                   ON s.ClientID  = c.ClientID
                  and s.AccountID = c.AccountID
                 left join ClientEligibility ce
                   on s.ClientID = ce.ClientID
                  and s.[Date] BETWEEN ce.StartDate and ce.EndDate
                 LEFT JOIN Staff stf
                   ON s.StaffID   = stf.StaffID
                  and s.AccountID = stf.AccountID
             ) as a
     ) as b


Will TDE encryption protect my SQL database from hacker in this scenario I'm a bit understand that TDE encryption protect the database from being stolen and read by a hacker. However, I don't understand if it can help me in this scenario: If a hacker got into my server and he got the Administrator privilege. Now he want to access the database to stolen information. He won't need to copy the entire database file which being encrypted. But he can reset the SQL SA password or read the connection string from a website .config file , and use tool like SQL Server Management Studio to access the database from that user and password, finally he use few SQL query to view the data or export it to disk. Is that possible?

[…]Is that possible?

Yes. The scenario where a server is compromised is not what is being targeted by TDE. TDE is targeting data at rest scenarios such as lost/stolen backups, hard drives that were discarded and not scrubbed, etc., but not entire server compromises.

In this case I'd advocate for you to upgrade off of 2008R2 (which only has <6 months of support left) and onto something much newer. There you have options such as column level encryption through HSMs and Always Encrypted (which can also use HSMs) which would be two main scenarios that can potentially (depending on external factors) stop the attack you've specified from being able to steal any extra information not already on the server and unencrypted.


Unsigned Int datatypes Does Microsoft SQL Server support unsigned integer data types?

I am trying to learn a bit more about the data type choices and helping a friend in his database project, I know that MySQL does.

No. The numeric data types are all signed.

For example an int is from approx. -2^31 to +2^31.

If you have a value just shy of 2^32 you will have to use a larger data type such as bigint. There are finer points here, but out of scope of this discussion.

Related Q & A:

Why aren't unsigned integer types available in the top database platforms?

Documentation:

int, bigint, smallint, and tinyint (Transact-SQL)
Data Types (Transact-SQL)


ORA-12516: when accessing another database via link I need to bring some data (blobs) from another database. I created global links to enable bidirectional communication. The links work. When I try to bring some data from tables that have BLOB columns I get intermittently the ORA-12516:

  TNS:listener could not find available handler with matching protocol stack

Here's what I run to bring BLOBs to my schema:

CREATE TABLE TEMP_MY_TABLE AS
SELECT * FROM SOME_REMOTE_TABLE_WITH_BLOB_COLUMN@dblink_source;

Oracle 11 r2. Blobs have different sizes.

Number of PROCESSES is set to 1000 and only half is utilized during the run. Sessions = 1528.

Both source and target are identical in hardware and software including patches.

The links work fine for hundreds of other queries. I checked and they are resolved properly.

Most probably you are reaching the maximum connections allowed. Make sure that old sessions are being closed properly and try increasing the processes and sessions parameters.


Having issues with postgresql, what should I log? Here's the error message I am receiving.

could not translate host name "localhost" to address: hostname nor servname provided, or not known

From my searching, it seems like postgresql has crashed due to an error. If a proper shutdown is initiated, the message is different.

FATAL:  the database system is shutting down

So I know it's not a proper shutdown of the database service that is occurring.

I am logging to syslog on a FreeBSD system. The log is very basic and essentially only shows me shutdown and startup attempts.

LOG:  database system was shut down at 2017-05-08 09:20:16 EDT
LOG:  database system is ready to accept connections

I am currently using default logging options. What do I need to change in order for PostgreSQL to log errors?

Requested Information

/etc/hosts:

127.0.0.1               localhost localhost.my.domain

DB connection code:

char conninfo[] = "host=localhost port=xxx dbname=xxx user=xxx password=xxx";
conn = PQconnectdb(conninfo);
if (PQstatus(conn) != CONNECTION_OK) {
print error
}

could not translate host name "localhost" to address

Implies a failure in your DNS resolver. You can see the error generated in libpq's fe-connect.c :1661. There is a call to pg_getaddrinfo_all which is a wrapper for getaddrinfo which is provided by glibc.

See man getaddrinfo.

Does your local /etc/hosts file have an entry for localhost, or are you relying on a remote DNS server to resolve localhost to 127.0.0.1? What are you providing in PGConn to connectDBStart?


mysqldump Not Exporting All Rows or mysql Not Importing I have a table that contains almost 4 million rows. I exported it with mysqldump and transferred it via scp to another server and imported it with the mysql command but now it's missing several hundred thousand rows. I've repeated this process several times and each time, it's missing a different amount of rows.

I have tried mysqldump --compatible=ansi because on one export, there was an error in the syntax for some reason. That dump wound up having the most rows on import but still missing hundreds of thousands.

Edit: I tried the -f option but it still prints out a mysql error before I'm returned to the command prompt:

ERROR 1064 (42000) at line 41458: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '286,'54.227.215.70','http://example.com/'),(81547367,'CbLc4lXH',1501560286,'54.7' at line 1

I'm not sure if it exited because of the error or not but I do know I'm missing about a million records after import. The file is 40GB in size and I don't have access to the previous mysql server anymore to do another export. I have all the data I need. I just can't get it imported.

Sometimes this can occur because of a combination of a certain sequence of characters and the character set of the MySQL client at the time of the import.

You could have trapped such errors without additional tools by doing the following

mysql -u dbuser -p -f -D thedb < importfile.sql 2>errors_encountered.txt

This would have recorded your trouble spots into errors_encountered.txt

What could have been done with the mysqldump to begin with ???

SIMPLIFY EXPORT CONTENTS

You could have used the --hex-blob options to convert the characters into a hexadecimal representation. This could have eased any character set misinterpretations or misunderstandings on the part of the MySQL client program.

DUMP ONE ROW AT A TIME

Even when using --force for the MySQL client, this can still result in hundreds or thousands of rows not being inserted. Why ?

By default, mysqldump has --opt enabled. This sets the following

  --opt               Same as --add-drop-table, --add-locks, --create-options,
                      --quick, --extended-insert, --lock-tables, --set-charset,
                      and --disable-keys. Enabled by default, disable with
                      --skip-opt.

Notice that one of the options is --extended-insert. This causes mysqldump to set up inserts in chunks of hundreds or thousands of rows at a time.

If just one row in a chunk of rows had an issue with being imported and you are using --force, the entire chunk of rows is not inserted.

How do you get a hold of all the good parts of a chunk. For a mysqldump created with --extended-insert, there is no simple way. What can you do when this happens ? I have good news and bad news.

GOOD NEWS: You have to launch a new mysqldump with --skip-extended-insert. That forces a mysqldump to create an INSERT for every row. That way, when using --force during import, an invalid INSERT due to whenever circumstance will not affect surrounding rows.

BAD NEWS #1 : This makes the resulting mysqldump file much larger.

BAD NEWS #2 : Importing the resulting mysqldump file takes much, much longer.

This is something I recommended Aug 09, 2013 (Backup / Export data from MySQL 5.5 attachments table keeps failing!)

SUGGESTION

To nullify the size problem this creates, you could do the following

gzip the mysqldump while doing mysqldump

nohup mysqldump ... | gzip > importfile.sql.gz &

ungzip the mysqldump and pipe it to mysql for import

gzip -d < importfile.sql.gz | mysql -u dbuser -p -f -D thedb

GIVE IT A TRY !!!


How many database licenses do we need for setting 2 servers (nodes) into a cluster? Suppose we have a server that has only 1 CPU with 1 core, so we need 1 Oracle database license for installing a database into it. Now, if we would need to add a second server, combine them into cluster and install database into it, then how many licenses do we need - 2 database licenses and 1 Clusterware licenses or 1 database license and 1 cluster license? I'm not sure, how Oracle treats joined into Oracle cluster servers - as one server or still as separate servers? Because we would need to buy expensive Oracle cluster software, then it would be logical for Oracle to treat joined servers as one.

Oracle database licenses are quoted per processor, not per machine. They don't care whether you have two machines with two cores each or four machines with one core each. Both equate to 4 licenses you need to purchase.

For clustered installations, you need to purchase the appropriate database licenses and the RAC licenses.


Oracle sequence generation performance during persists of large data sets In the context of an Oracle sequence, when persisting > 200 million records, is there a performance hit on the database if the sequence cache is the default value of 10000?  That is to say does modifying this value to something around 1,000,000 give me any benefit during a transaction where > 200 million sequences will be used?

The larger the CACHE on the sequence, the fewer times that you'll need to wait for Oracle to acquire the latch to be able to update the data dictionary when a new set of values need to be generated.  There is definitely at least the potential for some performance benefit by increasing the size of the cache.  On the other hand, if the CACHE is already at 10,000, you're only going to need to update the data dictionary 20,000 times in the course of a 200 million row data load (assuming one call to the sequence.nextval per row).  Without seeing your system, it seems unlikely that these 20,000 updates would account for a meaningful fraction of the time you're spending in a 200 million row data load.  You would potentially shave a couple of seconds off the load by increasing the size of the cache but that probably isn't meaningful given the amount of time you're likely spending on I/O.


InnoDB missing from MySQL I have no idea of what I have done here, but my InnoDB engine seems to have gone from my MySQL  server. I recently upgraded it from the dotdeb repository, then installed mysql-server.

There is no mention of InnoDB in my my.cnf except some comments which explain InnoDB is enabled by default, which I don't understand. There is also no mention of InnoDB in SHOW ENGINES.

Is there something I'm missing here?

If it matters, my MySQL server version is: 5.5.24-1~dotdeb.1 (Debian).

EDIT: SHOW ENGINES:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | DEFAULT | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
8 rows in set (0.00 sec)

Data dir contents:

root@slone:/var/lib/mysql# ls
debian-5.1.flag  ib_logfile0.bk  ibdata1  mysql_upgrade_info  test
debian-5.5.flag  ib_logfile1.bk  mysql    performance_schema  tstore

Since you have done the following:

SHOW VARIABLES LIKE 'innodb%';
No InnoDB-related variables exist in my.cnf

and nothing comes up, please place this in my.cnf
[mysqld]
innodb

and start mysql and see if it comes up
UPDATE 2012-05-15 15:59 EDT
Since it fails to start up, you may need uninstall MySQL 5.5.24 and go back to MySQL 5.5.23 or whichever version you were last running with.
UPDATE 2012-05-15 16:06 EDT
Please run tail -30 on the mysql error log and paste that in the question.f it is not configured, go to the data folder and do ls -l *.err to find it.
UPDATE 2012-05-15 17:54 EDT
Based on the display you pasted in, you evidently had MySQL 5.1 running before. It  just so happens that MySQL 5.1 has a table called mysql.plugin. It may have been populated before. It probably had something concerning the InnoDB plugin. MySQL 5.1.38 introduced the InnoDB plugin which has new features that are now native to MySQL 5.5's InnoDB. In other words, MySQL 5.5 does not need any plugin to run the new InnoDB.
Please run this query:
SELECT COUNT(1) FROM mysql.plugin;

If you get 1, that's probably the MySQL 5.1 InnoDB Plugin interfering. Try moving it over like this:
CREATE TABLE mysql.plugin_new LIKE mysql.plugin;
ALTER TABLE mysql.plugin RENAME mysql.plugin_old;
ALTER TABLE mysql.plugin_new RENAME mysql.plugin;

Afterwards, please restart mysql and see if InnoDB comes back


Failed transaction replicated and thrown errors on remaining Percona XtraDB cluster nodes I am using Percona XtraDB cluster 5.5.37. I have run one alter command (to add unique constraint) on node1. But due to duplicate keys,alter command failed on it. But that failed statement passed to remaining nodes 2,3 and error logged like below.

160524  6:08:21 [ERROR] Slave SQL: Error 'Duplicate entry '176-60-FFP-1' for key 'constraint_name'' on query. Default database: 'db'. Query: 'alter table table_name add constraint constraint_name unique (col1, col2, col3,col4)', Error_code: 1062

My question is why its passed to other nodes, when it has failed on first node itself. It should not passed right. Please let me know where i am thinking wrong.

ALTER Table is executed using TOI which executed independently on all node parallely w/o waiting for it to complete on base node and then being replicated (this applies only for DML).

Also, I see you are using 5.5 which in general had lot of issues in past.
It is good if you can upgrade your setup to 5.6.


Deterministic function for getting today's date I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table):

CREATE VIEW lc.vw_dates
WITH SCHEMABINDING
AS

SELECT DATEADD(day, DATEDIFF(day, 0, GETDATE()), number) AS SettingDate
FROM lc.numbers
WHERE number<8

GO

CREATE UNIQUE CLUSTERED INDEX
idx_LCDates ON lc.vw_dates(SettingDate)

lc.numbers is simply a table with 1 column (number) which is incremented by row 1-100.

However, I keep getting the error:

  Column 'SettingDate' in view 'lc.vw_dates' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

I realize that GETDATE() is non-deterministic. But, is there a way to make this work without drawing a calendar table?

You cannot use the function GETDATE() as the basis for a persisted view since the output of the function changes moment-by-moment.  This what SQL Server means in the error "because it is non-deterministic."  Function results MUST be predictably the same every time they are called in order for SQL Server to persist the results.

Luckily, there is an easy way for you to persist this data once-per-day using a scheduled job.  Perhaps using SQL Server Agent, or Windows Job Scheduler, etc.

Here I'm creating the numbers table, and the table to hold the next 7 days:

CREATE TABLE dbo.Numbers
(
    Number INT NOT NULL
        CONSTRAINT PK_Numbers
        PRIMARY KEY CLUSTERED
);

;WITH cte AS
(
    SELECT TOP(100) Number = ROW_NUMBER() OVER (ORDER BY t1.num, t2.num)
    FROM (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t1(num)
        , (VALUES (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)) t2(num)
    ORDER BY t1.num * t2.num
)
INSERT INTO dbo.Numbers (Number)
SELECT cte.Number
FROM cte;

CREATE TABLE dbo.Next7Days
(
    SettingDate DATETIME NOT NULL
        CONSTRAINT PK_Next8Days
        PRIMARY KEY CLUSTERED
);

Schedule this to happen once per day:

TRUNCATE TABLE dbo.Next7Days;

INSERT INTO dbo.Next7Days(SettingDate)
SELECT SettingDate = DATEADD(DAY, n.Number, DATEDIFF(DAY, 0, GETDATE()))
FROM dbo.Numbers n
WHERE n.Number < 8;

Since the dates of the next 7 days only changes once per day, this solution should work well.

The dbo.Next7Days table contains the following after I ran the above code:


In SQL Server 2008, how can I get all the columns that are marked as nullable in their schemas even though no records contain NULL for those columns? In my legacy database, a lot of the time the schema needlessly allows null entries for particular columns. To help find out which columns in particular, I need to do some querying of SQL Server 2008 that beyond my level of expertise. My aim is to tighten up the schema a bit and save myself from dealing with the null case in my code.

A crude way (option 1) to help me solve my problem is to just get all records (and select *) that contain at least one null entry, then eye scan for columns that contain no null entries. I could of course just use Where columnA IS NULL OR columnB IS NULL OR columnC IS NULL, but this gets tedious for tables with lots of columns. Some tables in this database contain more than twenty columns! Also, there's about 500 tables in total. So, general solutions are best.

Here are three options for answers that would satisfy me:

Option 1 : "get me all the records where at least one of the columns is null". I will eyescan for columns with no null entries.
Option 2 : An even better answer would be some script that gets a list of column names that contain at least one null entry. Then I will see which nullable columns are not in this list manually.
Option 3: The best answer would get me a list of column names that are marked as nullable in their schema even though no records exist with a null entry in those columns.

Thanks!

Martin Smith's answer will serve very well to get you all the columns you need for an entire database in SQL 2008. Very nice!

Here is how I did it in the days before SQL had CTEs and PIVOT. This will be compatible with older versions of SQL where Martin's solution won't work, and still works in 2008 as well, but with poorer performance than his solution.

USE MyDB

SET NOCOUNT ON

CREATE TABLE ##nullable  (
    ID INT IDENTITY(1,1),
    SchName VARCHAR(128),
    TblName VARCHAR(128),
    ColName VARCHAR(128),
    hasNulls BIT,
    PRIMARY KEY(ID)
    )

DECLARE @currTbl VARCHAR(128)
DECLARE @currCol VARCHAR(128)
DECLARE @currSch VARCHAR(128)
DECLARE @limit INT
DECLARE @i INT
DECLARE @sql NVARCHAR(4000)

INSERT INTO ##nullable (
    SchName,
    TblName,
    ColName,
    hasNulls
    )
SELECT
    c.TABLE_SCHEMA,
    c.TABLE_NAME,
    c.COLUMN_NAME,
    0 AS hasNulls
FROM INFORMATION_SCHEMA.COLUMNS c
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON c.TABLE_CATALOG = t.TABLE_CATALOG
AND c.TABLE_NAME = t.TABLE_NAME
AND c.TABLE_SCHEMA = t.TABLE_SCHEMA
WHERE c.IS_NULLABLE = 'YES'
AND t.TABLE_TYPE = 'BASE TABLE'

SET @limit = (SELECT MAX(ID) FROM ##nullable)
SET @i = 1

WHILE @i <= @limit
BEGIN
    SELECT @currSch = SchName,
        @currTbl = TblName,
        @currCol = ColName
    FROM ##nullable
    WHERE ID = @i

    SET @sql = 'UPDATE ##nullable
        SET hasNulls = 1
        WHERE ID = ' + CAST(@i AS VARCHAR(20)) + '
        AND EXISTS (SELECT 1 FROM ' + QUOTENAME(@currSch) + '.'+ QUOTENAME(@currTbl) + '
        WHERE ' + QUOTENAME(@currCol) + ' IS NULL)'

    EXEC(@sql)

    SET @i = @i + 1
END

SELECT DISTINCT * FROM ##nullable
WHERE hasNulls = 0

DROP TABLE ##nullable


How to optimize time based data? I need help optimizing time-based queries on a relatively small table.

The table looks like this:

time int | tag varchar(21) | val decimal

I also have a index like such

create index _index on table (time, tag)

And queries looks like this:

select * from table where time >= 1554599241 and time <= 1554599251 and tag = ANY('{aaa,bbb,ccc}')

Is there any way for me to reduce query times?

Example

select * from table where time >= 1554020803 and time <= 1554625603 and tag = any('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}')

produces this explain:

Bitmap Heap Scan on table (cost=20795.15..520342.18 rows=81781 width=69) (actual time=72.530..674.270 rows=224012 loops=1)
  Recheck Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
  Filter: ((tag)::text = ANY ('{ryeb1UQ2Gm,HkGM18QnGX,rkxE1872fQ,rybS18XnGm,B1f8JUmnzX,BkuJ873M7,HyO6Z9OTMQ,BkmCbqOaG7,HJpCWcdpfm,rkGgAb9upGm,ry7JM9Oaz7,BkOkf5OTzm,B1ejy8Xhz7,Syp1G9uTzm,HkMlJMcupzQ,rycJLm3zQ,SyxFJLmnGm,Sy_RZ9u6Gm,BkfJy8QnMQ,Sy76-qupGm}'::text[]))
  Rows Removed by Filter: 896063
  Heap Blocks: exact=7229
  ->  Bitmap Index Scan on _test_index  (cost=0.00..20774.71 rows=817814 width=0) (actual time=71.430..71.430 rows=1120075 loops=1)
      Index Cond: (("time" >= 1554020803) AND ("time" <= 1554625603))
Planning time: 0.137 ms
Execution time: 682.200 ms

Try an index on (tag, time).  With B-tree indexes, you generally want the equality-constrained columns to be in the index before the inequality-constrained columns.