Should I be wary of ON DUPLICATE KEY UPDATE? I'm new to MySQL, but I have reasonable experience with SQL Server. I know in SQL Server I should be very cautious about using MERGE statements because they can lead to some problems. Are there any special concerns I should have when using ON DUPLICATE KEY UPDATE? Is it considered a best practice in the MySQL community to stick with separate INSERT/UPDATE statements like in SQL Server or is it fine to use ON DUPLICATE KEY UPDATE broadly for simple upserts?

First of all:

Yes, it is fine and common practice. For simple upserts, I would say it is best practice.

There are some rather rare cases to worry about and things that may bite you - especially considering that you come from the SQL Server world:

When you use it against a table with two or more UNIQUE constraints. In those cases, you can't easily set to have different behaviours for different constraint violations (while it is possible with the MERGE statement).
An INSERT that violates 1 of the 2 unique constraints will go to the UPDATE clause. If you want different behaviour depending on which constraint is violated, it will be hard to implement (possibly with some complex CASE expressions but may not be feasible in all scenarios).
A big difference with SQL Server (and all other major SQL implementations) is that UNIQUE constraints are checked after every row INSERT or UPDATE and not as the SQL standard says at the end of statement (and don't even think about deferred constraint checks).

The problem with that is for an INSERT ... SELECT ... ON DUPLICATE KEY UPDATE ...; statement, the order of how rows are processed matters. If the SELECT creates 2 rows to be inserted that conflict on a UNIQUE constraint, the first one to be processed will be inserted and the second will fall to the UPDATE clause (and the order of processing will of course depend on the execution plan). This means non-deterministic result on the state of the table after the statement.
So one has to be really careful when writing such statements (and any UPDATE statement really).
Related to the previous point, INSERT ... ON DUPLICATE KEY UPDATE statements are marked unsafe for statement based replication, so if you use them you are restricted to row or mixed based replication.


How to store array values? Maybe this question has been asked already, but I don't know if my approach is correct.

Once I was modelling a database for my university. It was meant to store information about the health of the students, and then analyze it to give the student a feedback.

For example: in one question it was asked who have or had diabetes, with multiple options as an answer, for example:

Mom
Dad
Student
Brothers

And the student could select one or many options. I needed to store that in the database.

First the values in the DB where stored in a string where values were separated by a comma. (I know, this isn't the best way).

So I thought about doing the next things:

[

I thought about creating a table for each field where the value was stored like a comma separated array.

Having the same fields, but just to identify which relative has/had the disease.

What I did was correct?

Student table:

id | name

In this table you will have the students.

Sickness table:

id | name

In this table will have the sicknesses. It will have a row for all the sicknesses (cancer, diabetes, and so on).

Relative table:

id | name

This table will have the relations: "mom", "dad", "brother" and so on.

The trick what you need is a bridge table, or associative entity.

These are used to map many-to-many relations. And.. much more.

Because in this case, you need more than a single many-to-many relation. In fact, you need a "many-to-many-to-many relation". Essentially, it is an associative entity between 3 entities.

It is uncommon, but possible and can be handled pretty well in the DB design.

So, you need to have also a fourth table, with the following columns:

student_id | relative_id | sickness_id

If the table has a row, for example, (3, 4, 5), it would mean that the relative with id 4 of student with id 3, had the sickness with id 5.

On this way, you don't need multiple tables for the different sicknesses, it would be a very bad design.

(student_id, relative_id, sickness_id) should be a primary key of this bridge table. The induvidual columns will be also foreign keys to the corresponding student, relative or sickness tables.


How can I change Referential Integrity from a Unique Key Constraint to a Primary Key? (without dropping foreign keys)

I have migrated a SQL Server CE database to SQL Server 2012, however, referential integrity was enforced via unique key constraints on the surrogate INT identity columns.

e.g.

create table Table1
(
   Table1ID INT NOT NULL,
   CONSTRAINT U_000001 UNIQUE(Table1ID)
);

CREATE TABLE Table2
(
   Table2ID INT NOT NULL,
   Table1ID INT NOT NULL,
   CONSTRAINT FK_T2_T1 FOREIGN KEY(Table1ID) REFERENCES Table1(Table1ID)
);

I would like to convert the unique key constraints to primary keys, for ERD diagramming and ORM reasons.

I have added the primary keys, but cannot drop the UKC, because RI of dependent columns is already 'baked into' the UKC despite the alternative PK now being available.

ALTER TABLE Table1 ADD CONSTRAINT PK_Table1 PRIMARY KEY (Table1ID);

ALTER TABLE Table1 DROP CONSTRAINT U_000001;

Fails with

  Msg 3725, Level 16, State 0, Line 1
  The constraint 'U_000001' is being referenced by table 'Table2', foreign key constraint 'FK_T2_T1'.

So my question is, is there any way to change the RI checks on foreign keys to use the PRIMARY KEY, and not the UKC as is currently the case, without having to drop the foreign keys on referencing tables first?

I have prepared a small SQL Fiddle here.

There's currently no way to modify which unique index a foreign key constraint is "attached to" because this is supposed to be an internal implementation detail, the same way that a unique constraint uses a unique index behind the scenes to enforce the constraint.

In fact, as you saw, if you have multiple candidate indexes, the one the foreign key attaches to seems non-deterministic (it might go by index id, index width, or something else; I haven't played with it in-depth), and it cannot be specified in the DDL statement either (memory says there's a Connect item requesting that feature, though).

So in this case, at least for now, you'll need to drop both the foreign key and the existing unique constraint before re-establishing the relationship, to ensure your change script works correctly in all scenarios.


Improve speed of GCP SQL Failover I've been trying out GCPs SQL high availability options. Is there a way to improve the fail over speed?

Attempting manual failovers through the UI takes literally over 5 minutes where mysql is completely unavailable.

I am using the smallest second generation 5.7 instances possible currently with no traffic hitting it.

It looks like it's actually stopping and starting instances. I'm having trouble seeing how this is an option for "HA" meaning minimal downtime.

There isn't anything you can do to improve the failover time, but you can add a read replica to have read access to your data while your instance is recovering.

If you aren't locked down to MySQL, you should also try the Postgres version of Cloud SQL - it uses a faster method for recovery and from my personal experience is generally down for ~30-60 seconds. Each version has it's own page on HA, which talks about how they work: MySQL, Postgres


SQL Server Configuration Manager 2012 - unable to set IP All = No I have a 4-node SQL Server 2012 SP1 cluster, and under Network Configuration -> TCP/IP -> Protocol (tab), I am able to switch Listen All from Yes to No. However, when I hit OK (or Apply), I get the message:

  Any changes made will be saved; however they will not take effect until the service is stopped and restarted.

OK, fine.

Now, if I re-open the TCP/IP properties, Listen All is back to Yes.

This happens even after I restart the SQL Server service, and it happens on all 4 instances.

I have checked the version of Configuration Manager - 2011.0110.2100.060. I've compared this with a (non clustered) SQL Server 2012 where I can flip the IP All field at whim, the versions are the same (as are the SQL Server versions, 11.0.3000.0 Build 7601).

I've also ensured that under IP Addresses, the one that I want to use has Enabled = Yes, all other IPs have Enabled = No.

Neither Books Online nor Google have any useful information for me.

Has anyone seen this before?

You cannot set IPAll to "no" on a failover cluster instance (as mentioned before), see:

How to configure SQL server to listen on different ports on different IP addresses?

More than likely, though, you are wondering how to secure your failover cluster instance of SQL Server to a certain IP address, or to verify that it isn't indeed listening on all IP addresses registered.

The failover cluster instance will be listening on the IP address(es) registered for the virtual network name in the cluster resource group with the Windows failover cluster.

But you want to verify which IP addresses SQL Server is listening on?

select
    ip_address,
    port,
    is_ipv4,
    type_desc,
    state_desc
from sys.dm_tcp_listener_states;

That will give you the real-time snapshot of what IP addresses SQL Server is listening on.  That'll be your verification.


how to split the chunks in sharding I have edited the question.
I was trying to run the sh.splitAt

mongos> sh.splitAt("test.zp",{"city": "1"})it worked.

But the problem is at sharding chunks.

when i have run the command sh.status().
It shows like this

databases:
{  "_id" : "admin",  "partitioned" : false,  "primary" : "config" }
{  "_id" : "test",  "partitioned" : true,  "primary" : "shard0000" }
test.zp
shard key: { "city" : 1 }
chunks:shard0000  34
too many chunks to print, use verbose if you want to force print.

i have three more shards but with different ip's.why sharding is not applied to the secondary shards. I have shards like

    {  "_id" : "shard0000", }
{  "_id" : "shard0001", }
{  "_id" : "shard0002", }
{  "_id" : "shard0003", }

How  chunks will be added to "shard0001" and remaining shards.I have added all shards to the test db. and my data is 1.2GB.

You need to enable sharding on the database test first:

sh.enableSharding("test");

Then you need to shard the collection and pick a shard key.  Based on what you were trying to split on, that would be:

sh.shardCollection("test.zp", {"city" : 1});

A couple of notes:

"city" is probably a poor shard key unless you are expecting an even distribution of records per city and you are going to have a lot of them.  Take a look at the docs on picking a key
If you have data in the collection already, you will need to create an index on the "city" field before you can use it as a shard key.  If the collection is empty, MongoDB will do it for you automatically when you use it as a shard key.


What security vulnerabilities are there with IDENTITY_INSERT and DBCC CHECKIDENT I'm writing some simple C# code through a website to take an excel file and update a table on a database. My system admin seems to not want to give me access to pretty much anything including the two things above stating that I need a Security+ certificate in order to run those commands.

I've been doing this a long time (15 years), first I've ever run into a problem like this, and so this seems really really weird and strange.

I already can update, insert, delete and do modifications to tables as I have been given some access to the database. So what if any are the security implications of these additional permissions? Can someone explain the reluctance of the SA to grant me these permission? Please enlighten me, because at this point I'm just scratching my head trying to figure out why the application cannot be granted these rights.

DBCC CHECKIDENT

  Caller must own the schema that contains the table, or be a member of the sysadmin fixed server role, the db_owner fixed database role, or the db_ddladmin fixed database role.

MSDN DBCC CHECKIDENT reference

IDENTITY_INSERT

  User must own the table or have ALTER permission on the table.

MSDN Set IDENTITY_INSERT reference

When a user can delete, update, insert, and select records, you are granting the user the ability to manipulate the data within the table.

When a user has alter, they can adjust the columns, data types, seeding information, and much more. Best practice dictates lowest amount of permission necessary to achieve a function so that there is less opportunity that can be exploited.

Now if you can already modify the table, I'd be interested to see how he locked the permissions down so you could not run at least the IDENTITY_INSERT commands.

The least amount of permission you need to achieve these goals is DB_DDLADMIN which can open some security implications across the database server. You would also need ALTER TABLE privileges, this is less powerful than DB_DDLADMIN since it can be isolated to a single table and not server wide, but not without potential vulnerabilities.


Calculate 12 months rolling / moving average, median, min, max, percentiles, etc. as single query in Postgres My question has two parts:

. Firstly, how to adjust the code below to calculate 12 month moving avg, median etc.? As is, the query will return a list of daily, weekly, monthly or annual values (depending on what is specified) but only for a single 'period'. That is, if 'year' is specified, summary stats will be calculated for whole years 2019, 2018, 2017, etc. however back the data goes. If 'month' then summary stats will be only for a given month, like Oct-2019, Sep-2019, Aug-2019, etc... But how to calculate eg. single values for 3 months Aug-2019 to Oct-2019, then Jul-2019 to Sep-2019, etc.?

. Secondly, what would be a generic version of the code to be able to use any time interval + number of periods, like '4 weeks', '6 months', '12 months', '2 years'?

SELECT
  date_trunc('year', t.time2), -- or hour, day, week, month, year
  count(1),
  percentile_cont(0.25) within group (order by t.price) as Q1,
  percentile_cont(0.5) within group (order by t.price) as Q2,
  percentile_cont(0.75) within group (order by t.price) as Q3,
  avg(t.price) as A,
  min(t.price) as Mi,
  max(t.price) as Mx

FROM my_table AS t
GROUP BY 1
ORDER BY date_trunc

Data table consists of a list of individual transactions (date -> time2 as timestamp; and price as bigint).

Generic code for calculating 1,2,3,4,..6,...12 years / quarters / months / weeks / days /hours moving average, median, percentiles, etc. summary stats where table contains a list of individual time records (like sales transactions,etc)

WITH grid AS (
      SELECT end_time, start_time
      FROM (

            SELECT end_time
          , lag(end_time, 12, 'infinity') OVER (ORDER BY end_time) AS start_time
            FROM (

                SELECT
                generate_series(date_trunc('month', min(time2))
              , date_trunc('month', max(time2)) + interval '1 month', interval '1 month') AS end_time
                FROM   my_table

                ) sub

           ) sub2

      WHERE end_time > start_time

)

SELECT
    to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d
  , count(e.time2)
  , percentile_cont(0.25) within group (order by e.price) as Q1
  , percentile_cont(0.5) within group (order by e.price) as median
  , percentile_cont(0.75) within group (order by e.price) as Q3
  , avg(e.price) as Aver
  , min(e.price) as Mi
  , max(e.price) as Mx

FROM grid a

LEFT JOIN my_table e ON e.time2 >= a.start_time

                   AND e.time2 <  a.end_time

GROUP  BY end_time
ORDER  BY d DESC

The answer looks 'simple' but it is very disappointing nobody was prepared to lend a hand with some code snippets to put me on the right track...

A note about time:

. The first section of the scrip generates a list of start-end time ranges to run queries for.

. The second part calculates the stats and outputs the results for each interval of time (eg. for "12 months median" the time shown in the first column will be "12 months to the end of the given month")

. The convention in PosgreSQL is that "end of the month" is actually "0 hour" of the next month (ie. end of Oct 2019 is "2019.11.01 at 00:00:00"). Same applies for any time range (eg. end of 2019 is actually "2020.01.01 at 00:00:00").

. So, for display purposes you may wish use the following:

 to_char(date_trunc('month',a.end_time - interval '1 month'), 'YYYY-MM') as d

Else, if you don't include "- interval '1 month' ", the 12 months moving stats ending October 2019 will be shown as "for" 1st November 2019 at 00:00:00 (trunkaded to 2019-11).


What is the meaning of "-x-icu" in PostgreSQL's "collates"? I had this query:

SELECT * FROM table ORDER BY label ASC;

Since the labels are not in English, they didn't get sorted in the right order (ones beginning with "ö" were not in the bottom/end).

I therefore tried:

SELECT * FROM table ORDER BY label COLLATE "sv-SE" ASC;
SELECT * FROM table ORDER BY label COLLATE "sv_SE" ASC;

These gave errors about those collations not existing, which baffled me.

After a bunch of searching, I figured out to do:

SELECT * FROM pg_collation;

Which revealed that it's supposed to be:

sv-SE-x-icu

It worked when I used that identifier, but what's with the "-x-icu" stuff? What's that all about? I hate that they always have to mess with the standard locale identifiers so you can never rely on just the standard "language_location" format.

The documentation, 23.2. Collation Support / 23.2.2.2.2. ICU Collations , states:

  Collations provided by ICU are created in the SQL environment with names in BCP 47 language tag format, with a “private use” extension -x-icu appended, to distinguish them from libc locales.

ICU is the "International Components for Unicode", which, according to their homepage, is:

  ICU is a mature, widely used set of C/C++ and Java libraries providing Unicode and Globalization support for software applications. ICU is widely portable and gives applications the same results on all platforms and between C/C++ and Java software.

  ICU is released under a nonrestrictive open source license that is suitable for use with both commercial software and with other open source or free software.

Unicode is a specification, not software, hence for many years each vendor has implemented it on their own and thus there is not nearly as much consistency anyone working with collations would appreciate. The ICU project is, quite fortunately, being implemented by more and more vendors, which is improving Unicode support and consistency. If available, I would expect the ICU collations to be a better choice than non-ICU collations.


Error when using general and specific GRANT I'm having troubles with GRANTs when trying to use particular permissions on specific database. There's an user that needs SELECT on all schemas starting with some text; and INSERT, SELECT, UPDATE, DELETE on one schema. The thing is, when I give him the permissions, MySQL is giving "UPDATE command denied".

After long research, trying everything that came to my mind, I found that when the order is:

+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
+----------------------------------------------------------------------------------------------------------------+

and I try (with user)

mysql> UPDATE schema_user.table SET field='some_text';
ERROR 1142 (42000): UPDATE command denied to user 'user'@'X.X.X.X' for table 'table'

I get the error... BUT if I do:

REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

And check again the GRANTS, the order changed and now it's first the "full" GRANT:

+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
+----------------------------------------------------------------------------------------------------------------+

And I can UPDATE now without error:

mysql> UPDATE schema_user.table SET field='some_text';
Query OK, 0 rows affected (0.09 sec)

The thing now is, everytime I do FLUSH PRIVILEGES; the order is back to the first and "user" can't UPDATE.

This is happening with MySQL Community Server 5.6.36 in Centos 7.3.1611.
I tried with my local machine in 5.6.34 and got the same error, but tried again in VM Centos 7.3.1611 and same MySQL version and worked fine.

Here's some script that you can use in order to "replay" my error:

As root:

CREATE DATABASE demo_priv;
CREATE TABLE demo_priv.tbl (id INT(5) NOT NULL AUTO_INCREMENT PRIMARY KEY, some_text VARCHAR(50));
INSERT INTO demo_priv.tbl(some_text) VALUES ('insert');
CREATE USER test IDENTIFIED BY 'Password';
GRANT SELECT ON `demo_%`.* TO test;
GRANT INSERT, SELECT, UPDATE ON demo_priv.* TO test;
FLUSH PRIVILEGES;
SHOW GRANTS FOR test;

As "test":

UPDATE demo_priv.tbl SET some_text='update';
ERROR 1142 (42000): UPDATE command denied to user 'test'@'X.X.X.X' for table 'tbl'

As root:

REVOKE SELECT ON `demo_%`.* FROM test;
GRANT SELECT ON `demo_%`.* TO test;

And then you should be able to UPDATE.

Can anybody help me? I don't know if it's a bug or I'm understanding wrong the privileges in MySQL. Any help would be appreciated.

You already found the answer to this problem. It all has to do with the order the grants appear.
Let's go back to your first grants display
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@%                                                                                |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX'                              |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%';                                                                    |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%'                                          |
+----------------------------------------------------------------------------------------------------------------+

Here is how the grants are stored:

Information from GRANT USAGE is stored in mysql.user
Information from the other two lines are stored in mysql.db

To see this order, please run this query:
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

This will display those grants in whatever order it was entered.
When you deleted the grants and reinserted them using
REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;

This reverses the order in the mysql.db table. To prove that, just rerun
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G

after doing the REVOKE and GRANT.
UPDATE 2017-08-02 13:20 EDT
In your comment, you said

I didn't knew of the mysql.db existence... But yes, it looks like it's the order; I suspected that already; but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here.

The problem here is MySQL will not merge them. For the sake of continuity, you should avoid using wildcards at the database level if your want to manage the grants of a single user. In that instance, you will have to manage the grants of  every single user the same way.


Is there a complete list of Online operations for each SQL Server version? As we are about to change some of the large tables in our 5TB database I found myself needing a list of the operations that can be performed online and the which require full locks to be held while they run. Ideally, this list would also contain information about which statements require the SCH-M lock to commit at the end.

While I know most of them from my time in Microsoft, I was surprised that I could not find a publicly available list of online operations as they evolved from SQL Server 2005 and all the way to 2014 CTP.

Does anyone have such a list available? If not, I may decide to create it.

Sounds like an undertaking. Online index operations are not available in every edition of Microsoft SQL Server, hopefully this can get you started (it's in table form):

SQL Server 2012:
http://msdn.microsoft.com/en-us/library/ms190981.aspx

SQL Server 2008R2: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.105).aspx

SQL Server 2008: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.100).aspx

SQL Server 2005: http://msdn.microsoft.com/en-us/library/ms190981(v=sql.90).aspx


Login fails even though it exists when failing over to mirror I have mirroring set up on a production database.  I have a login setup on both the database serving the database and the mirror.  It has identical login information and configuration.

When I failover the database, the ASP.NET application fails over to the second database (it has a failover partner identified), but the login stops working.  For some reason after a bit it starts working.  When I fail back over to the original, production database, the ASP.NET application is kicked out again--even though I know the login exists on production because the application was able to login originally.

Here's the chain of events:

Production + Mirror have same login
Fail over to Mirror, login rejected.  Starts working for some mysterious reason.
Mirror fails over to original production, login that was initially working now not working.

You need to ensure the SIDs of the logins are the same on both servers.  The only way to ensure they are the same is to script the login from the working server to the mirror server.   You cannot simply recreate the logins with the same name and password since the login will not match the user that exists in the database.  When you CREATE LOGIN you must use the SID = xxx option.

Perhaps the code below will help point you in the right direction.

/*
    Copy SQL login info for movement to another SQL server.
    Copies the SID and the Password hash.

    Run this piece on the source server, and copy the results to another query window
    connected to the destination server.  Run the results on the destination server to
    DROP and CREATE LOGINs with the same SIDs and PASSWORDS as the source server.
*/
DECLARE @txt varchar(max);
SET @txt = '';
SELECT @txt = @txt + 'DROP LOGIN ' + QUOTENAME(l.name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10) + 'CREATE LOGIN ' + QUOTENAME(name) + CHAR(13) + CHAR(10) + CHAR(9) + 'WITH PASSWORD = ' + CONVERT(varchar(max), l.password_hash, 1) + ' HASHED'  + CHAR(13) + CHAR(10) + CHAR(9) + ', SID = ' + CONVERT(varchar(max), l.sid, 1) + CHAR(13) + CHAR(10) + CHAR(9) + ', DEFAULT_DATABASE = ' + QUOTENAME(l.default_database_name) + ';' + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)
FROM sys.sql_logins l
WHERE l.principal_id > 1
    AND l.is_disabled = 0;
SELECT @txt;

/*
    Look for orphaned USERS with a matching LOGIN
    Run the below SQL code in the same window used to run the code generated by the above SQL text.

    This code will look for and correct orphaned users.  Relies on the LOGIN name and the USER name
    being identical.
*/
GO
DECLARE @dbname nvarchar(max);
DECLARE @txt nvarchar(max);
SET @dbname = '';
SET @txt = '';
DECLARE dbcur CURSOR LOCAL FORWARD_ONLY FOR
SELECT name
FROM sys.databases
WHERE database_id > 4;
OPEN dbcur;
FETCH NEXT FROM dbcur INTO @dbname;
WHILE @@FETCH_STATUS = 0
BEGIN
    SELECT @txt = @txt +
    '
USE ' + QUOTENAME(@dbname) + ';
GO
DECLARE @txt nvarchar(max);
SET @txt = '''';
SELECT @txt = @txt + ''ALTER USER '' + QUOTENAME(dp.name) + '' WITH LOGIN = '' + QUOTENAME(dp.name) + '';''
FROM sys.database_principals dp
WHERE dp.type = ''S''
    AND sid NOT IN (SELECT sid FROM master.sys.sql_logins)
    AND name IN (SELECT name from master.sys.sql_logins);
IF @txt <> '''' EXEC ' + QUOTENAME(@dbname) + '.sys.sp_executesql @txt;
    ';
    FETCH NEXT FROM dbcur INTO @dbname;
END
CLOSE dbcur;
DEALLOCATE dbcur;
SELECT @txt;


Memory optimized DLLs not being removed From BOL, my understanding is that DBAs do not need to administer DLLs created for memory optimized tables, or natively compiled stored procedures, as they are recompiled automatically, when the SQL Server service starts and are removed when no longer needed. But I am witnessing, that even after a memory optimized table has been dropped, and the service restarted, the DLLs still exist in the file system AND are still loaded into SQL memory and attached to the process. This can be witnessed by the fact that they are still visible in sys_dm_os_loaded_modules, and are locked in the file system if you try to delete them, whilst the SQL Service is running.

Is this a bug? Or are they cleaned up at a later date? If at a later date, what triggers the clean-up, if it isn't an instance restart?

Is this a bug?

No it is not a bug. Its by design. They are kept for troubleshooting and supportability
purposes.

From the SQL_Server_2014_In-Memory_OLTP White_Paper

  Database administrators do not need to maintain the files that are generated by native compilation. SQL
  Server automatically removes generated files that are no longer needed, for example on table and
  stored procedure deletion, and on drop database, but also on server or database restart.

I tried to repro your scenario on SQL Server 2014 + RTM + (Build12.0.2000.8) - Dev Edition server by creating a test memory optimized table and checking the dll loaded using

     SELECT name, description FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

After I dropped my table, the dll still appears in the output of above select statement and the files are still in the folder and after restart they are still there too.

From Books Online -

  No user interaction is needed to manage these files (.c, .obj, .xml, .pdb., .dll). SQL Server will create and remove the files as necessary.

SO I guess, we just have to follow what Microsoft says - SQL server will manage them for us :-)

ONLY FOR EDUCATIONAL PURPOSE :

I managed to clean up the old files by

Issuing a manual CHECKPOINT on the database.
Taking database offline and then bringing it online.

Ideally, you should not be restarting the server instance, just manual checkpoint and offline/online of the database will clear the files.

e.g. Repro :

USE master
GO
create database db1
GO
ALTER DATABASE db1 ADD FILEGROUP db1_mod CONTAINS memory_optimized_data
GO
-- adapt filename as needed
ALTER DATABASE db1 ADD FILE (name='db1_mod', filename='D:\SQLServer2014\MSSQL12.SQL2014\MSSQL\DATA\db1_mod') -- change here as per your need !!
 TO FILEGROUP db1_mod
GO
USE db1
GO
CREATE TABLE dbo.t1
(c1 int not null primary key nonclustered,
c2 int)
WITH (MEMORY_OPTIMIZED=ON)
GO

--- now check if the dll is loaded or not

SELECT name, description FROM sys.dm_os_loaded_modules
WHERE description = 'XTP Native DLL'

--- now drop table and do a manual checkpoint

use db1;
drop table dbo.t1;
checkpoint

Still the module is loaded in memory (even server restart will load the module sometimes)

The (.c, .obj, .xml, .pdb., .dll) are still present in the folder :

Now take the database offline and then bring it online - the (.c, .obj, .xml, .pdb., .dll) are all gone ...


how to find the name of the distributor server and publisher server from the subscriber server/subscriber database Having in mind that I can add computed columns to the subscription tables
according to this link:

SQL Server : Transactional Replication Computed Column

That I am already doing, and also adding indexes to replicated tables (in the subscriber server)

However,

While I am doing it, I have to face too many locks and blocks to/from the replication as you can see on the picture below:

this is the replication command I was blocking while creating my index:

create procedure [sp_MSupd_dbotblBGiftVoucher]
        @c1 binary(24) = NULL,
        @c2 smallint = NULL,
        @c3 varchar(20) = NULL,
        @c4 int = NULL,
        @c5 varchar(20) = NULL,
        @c6 int = NULL,
        @c7 bit = NULL,
        @c8 char(1) = NULL,
        @pkc1 binary(24) = NULL,
        @bitmap binary(1)
as
begin
if (substring(@bitmap,1,1) & 1 = 1)
begin

update [dbo].[tblBGiftVoucher] set
        [strVoucherNumber] = case substring(@bitmap,1,1) & 1 when 1 then @c1 else [strVoucherNumber] end,
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
else
begin

update [dbo].[tblBGiftVoucher] set
        [sintMarketID] = case substring(@bitmap,1,1) & 2 when 2 then @c2 else [sintMarketID] end,
        [strIssuedBxOrderNo] = case substring(@bitmap,1,1) & 4 when 4 then @c3 else [strIssuedBxOrderNo] end,
        [lngIssuedUserID] = case substring(@bitmap,1,1) & 8 when 8 then @c4 else [lngIssuedUserID] end,
        [strReedemedBxOrderNo] = case substring(@bitmap,1,1) & 16 when 16 then @c5 else [strReedemedBxOrderNo] end,
        [lngRedeemedUserID] = case substring(@bitmap,1,1) & 32 when 32 then @c6 else [lngRedeemedUserID] end,
        [blnVoid] = case substring(@bitmap,1,1) & 64 when 64 then @c7 else [blnVoid] end,
        [strCheckDigit] = case substring(@bitmap,1,1) & 128 when 128 then @c8 else [strCheckDigit] end
    where [strVoucherNumber] = @pkc1
if @@rowcount = 0
    if @@microsoftversion>0x07320000
        exec sp_MSreplraiserror 20598
end
end

there are too many servers, and I am not sure of which server is the publisher,
where is the distributor database, and if there is a distributor server, which one is it?

Is there a way I can find the name of the publisher and distributor (server and database), from the subscriber?

some information is here:
How to restart the distributor agent of transactional replication?

but I am still struggling to pause the replication for at least one article, but I could pause it for the whole publication.

What I want to achieve is to pause the replication for my current subscription (the database where I am on)...

Is there a way I can find the name of the publisher and distributor
  (server and database), from the subscriber?

For push subscriptions from the subscriber you can query MSsubscription_agents to get the publisher and publisher database.

For pull subscriptions from the subscriber you can query MSsubscription_properties to get the publisher and distributor.  Have a look at the columns publisher, publisher_db, and distributor.  The distribution database name will likely be distribution.


Constraint - one boolean row is true, all other rows false I have a column: standard BOOLEAN NOT NULL
I would like to enforce one row True, and all others False. The are no FK's or anything else depending on this constraint. I know I can accomplish it with plpgsql, but this seems like a sledgehammer. I would prefer something like a CHECK or UNIQUE constraint. The simpler the better.
One row must be True, they cannot all be False (so the first row inserted would have to be True).
The row will need to be updated, which means I have to wait to check constraints until updates are done, since all rows may be set False first and one row True afterwards.
There is a FK between products.tax_rate_id and tax_rate.id, but it has nothing to do with the default or standard tax rate, which is user selectable to ease creating new products..
PostgreSQL 9.5 if it matters.
Background
The table is the tax rate. One of the tax rates is the default (standard since default is a Postgres command). When a new product is added, the standard tax rate is applied to the product. If there is no standard, the database must either do a guess or all kinds of unneeded checks. The simple solution, I thought, was to make sure there is a standard.
By "default" above, I mean for the presentation layer (UI). There is a user option for changing the default tax rate. I either need to add extra checks to ensure the GUI/user does not try to set the tax_rate_id to NULL, or then just set a default tax rate.

Variant 1
Since all you need is a single column with standard = true, set standard to NULL in all other rows. Then a plain UNIQUE constraint works, since NULL values do not violate it:
CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
 , standard bool DEFAULT true
 , CONSTRAINT standard_true_or_null CHECK (standard) -- yes, that's the whole constraint
 , CONSTRAINT standard_only_1_true UNIQUE (standard)
);

DEFAULT is an optional reminder that the first row entered should become the default. It's not enforcing anything. While you cannot set more than one row to standard = true, you can still set all rows NULL. There is no clean way to prevent this with only constraints in a single table. CHECK constraints do not consider other rows (without dirty tricks).
Related:

Restrict two specific column values from existing at the same time

Add constraint to make column unique per group of rows

To update:
BEGIN;
UPDATE taxrate SET standard = NULL WHERE standard;
UPDATE taxrate SET standard = TRUE WHERE taxrate = 2;
COMMIT;

To allow a command like (where the constraint is only satisfied at the end of the statement):
WITH kingdead AS (
   UPDATE taxrate
   SET standard = NULL
   WHERE standard
   )
UPDATE taxrate
SET standard = TRUE
WHERE taxrate = 1;

.. the UNIQUE constraint would have to be DEFERRABLE. See:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

dbfiddle here
Variant 2
Have a second table with a single row like:
Create this as superuser:
CREATE TABLE taxrate (
   taxrate int PRIMARY KEY
);

CREATE TABLE taxrate_standard (
   taxrate int PRIMARY KEY REFERENCES taxrate
);

CREATE UNIQUE INDEX taxrate_standard_singleton ON taxrate_standard ((true));  -- singleton

REVOKE DELETE ON TABLE taxrate_standard FROM public;  -- can't delete

INSERT INTO taxrate (taxrate) VALUES (42);
INSERT INTO taxrate_standard (taxrate) VALUES (42);

Now there is always a single row pointing to the standard (in this simple case also representing the standard rate directly). Only a superuser could break it. You might disallow that, too, with a trigger BEFORE DELETE.
dbfiddle here
Related:

Constraint to enforce "at least one" or "exactly one" in a database

You might add a VIEW to see the same as in variant 1:
CREATE VIEW taxrate_combined AS
SELECT t.*, (ts.taxrate = t.taxrate) AS standard
FROM   taxrate t
LEFT   JOIN taxrate_standard ts USING (taxrate);

In queries where all you want is the standard rate, use (only) taxrate_standard.taxrate directly.

You later added:

There is a FK between products.tax_rate_id and tax_rate.id

A poor man's implementation of variant 2 would be to just add a row to products (or any similar table) pointing to the standard tax rate; a dummy product you might call "Standard tax rate" - if your setup allows it.
The FK constraints enforces referential integrity. To complete it, enforce tax_rate_id IS NOT NULL for the row (if that's not the case for the column in general). And disallow its deletion. Both could be put into triggers. No extra table, but less elegant and not as reliable.


Update rows in table with id from row before in PostgreSQL Say I have a table like this:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | null        |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | null        |
| 5  | 2     | 15         | null        |

(time_added is actually a date, but numbers are used here for readability)

I want to update the previous_id of each row so that it's equal to the id of the previous row in the same group grp sorted by time_added.

This means that after running the update query, the table above should look like:

| id | grp   | time_added | previous_id |
|----|-------|------------|-------------|
| 1  | 1     | 5          | null        |
| 2  | 1     | 8          | 1           |
| 3  | 2     | 9          | null        |
| 4  | 1     | 12         | 2           |
| 5  | 2     | 15         | 3           |

What I have so far is the following query:

UPDATE the_table r
SET previous_id = sub.id
FROM (
  SELECT id, time_added, grp
  FROM the_table
  ORDER BY time_added DESC
) sub
WHERE (
  r.grp = sub.grp
  AND sub.time_added < r.time_added
);

However, I suspect the ORDER BY of the subquery does not do what I want it to, and the previous_id is set to the id of a random row below it, not the one strictly below it.

See this fiddle for an example of what happens. Row 3 is given a previous_id of 1, when it should be 2.

You can get your desired result by using LAG() function.

select
    id,
    grp,
    time_added,
    lag(id) over (partition by grp order by grp, time_added) previous_id
from
    tbl;

You can update in this way:

with x as
(
    select
        id,
        lag(id) over (partition by grp order by grp, time_added) previous_id
    from
        tbl
)
update tbl
set
    previous_id = x.previous_id
from
    x
where
    x.id = tbl.id;

db<>fiddle here


Issue installing Firebird ODBC drivers -- driver eventually no longer found I am experiencing an issue with an SSIS job that connects to a Firebird DB using their ODBC driver (version 2.0.4.155).

The SSIS package runs flawlessly in Visual Studio 2008, which is installed on Windows 7 32-bit (don't ask!)
The package fails to execute when deployed to SSIS in SQL Server 2008 64-bit. The error:

  Data source name not found and no default driver specified.

This immediately made me think that a ODBC driver mismatch might be the issue, where SSIS is looking for the 32-bit drivers. So here is what I did:

Uninstalled the Firebird ODBC 64-bit drivers
Installed the Firebird ODBC 32-bit drivers
Modified the Job schedule in SQL Server so it executes in 32-vbit
Package runs successfully after that -- however there is a twist.

I will be able to execute the package again as long as I am still logged in to the SQL server (I logged in to install the Firebird drivers). I can execute it even from a remove SQL Server Management Studio connection -- but as long as I do not log off from the server.

If I log off from the server, then the SSIs job will no longer work (same error as before). This led me to think that this is not in fact a 32/64 bit mismatch, but perhaps some registry or environment variable is not being committed after I log off from thee server due to insufficient permissions (even though I was meant to be admin on that server).

So for my next test:

I uninstalled the 32-bit driver
Reinstalled the 64-bit driver
Remained logged into the SQL server
Executed the job time and again from a remote SQL Server Management Studio: It worked every time

As soon as I logged off from that SQL server, the behavior occurred again. This was hard to troubleshoot at first because the drive installs without throwing any errors and it even works at first.

But I am confident now that this has to do with permissions. Any ideas what I might be missing?

The issue here is that I had to create a System DSN, which is actually odd because the SSIS job is scheduled to run with the account for which the User DSN exists, and it does work as long as I am logged into the server with that account.
Anyway I am way too tired to try to understand why the User DSN didn't work. So I decided to go for a System DSN instead.

It is not that I am against System DSN (on the contrary!), but it wasn't me that set this User DSN and I do not know the password for the credentials used in order to create a System DSN. So I had to be a little creative.

I had to use Registry Editor and notepad to convert a User DSN into a System DSN. Here is how I did it:

Log in with the user that has the User DSN entries that you with to convert to System DSN
Open the Registry Editor and navigate to HKEY_LOCAL_CURRENT\SOFTWARE\Microsoft\ODBC\
Right-click ODBC.INI and select export to save it as a file on the desktop (or anywhere else you fancy)
Open the .reg file with a text editor such as Notepad
Replace the text HKEY_CURRENT_USER with HKEY_LOCAL_MACHINE. Save your changes
Double click the .reg file and proceed to import it into the registry.

The process outlined above basically copies all User DSN entries as System DSN entries. If you to have just one entry copied over, then you will need to delete their corresponding keys under .\ODBC.INI as well as their corresponding entries in .\ODBC.INI\ODBC Data Sources.


Wordcount in a field (all and unique) - is there a more elegant/optimal way? Answering this question,
Given this table (constructed from the question):
CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ('What a great day');
INSERT INTO wordcount (description) VALUES ('This is a product. It is useful');

produce this result:
     id  | word_count | unique_word_count |  Description
---------+------------+-------------------+---------------
     1   |  4         | 4                 | What a great day
     2   |  7         | 6                 | This is a product. It is useful

I gave the (correct) answer, which you can find here.
However in a comment, the OP then asked a further question - what if the string in question was  ['a', ' ', ' ', 'b'] and my solution broke down completely - for starters, the string wouldn't even INSERT into the table.
So, the question now is, how does one deal with strings like this - i.e. with apostrophes, square brackets &c. I'm going to give my own answer and also offer a bonus for a more elegant solution.
Solutions with multiple options will be highly prized as will those which show evidence of "thinking outside the box" (sorry for the cliché - but it fits here! :-) ). I'm also going to give a detailed explanation of my reasoning - that will earn kudos also! Options which mention other servers will also gain merit. Obviously, I can only award the bonus to one person, but I will upvote all decent answers.
I can only offer a bonus in two days - so I'll post my answer and will offer the bonus (+100) when I'm allowed to. Also, any solutions which deal with strings which my own can't deal with - haven't exhaustively tested yet.

First step obviously is to create the table and data (as per the question mentioned):

CREATE TABLE wordcount (id SERIAL NOT NULL PRIMARY KEY, description TEXT NOT NULL);

INSERT INTO wordcount (description) VALUES ($$What a great day$$);
INSERT INTO wordcount (description) VALUES ($$This is a product. It is useful$$);
INSERT INTO wordcount (description) VALUES ($$['a', ' ', ' ', 'b']$$);

The first "lifesaver" was the dollar quoting ($$) - a really neat
PostgreSQL feature. I was really floundering before I came across
this - couldn't even get the data into the table (trying
backslashes, double quotes &c.)

My final SQL looks like this (fiddle here):

WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT id,
       COUNT(word),
       COUNT(DISTINCT(word)),
       description
FROM cte1
WHERE LENGTH(word) > 0
GROUP BY id, description
ORDER BY id;

Result:

id  Word_count  Distinct_count  description
 1           4               4    What a great day
 2           7               6    This is a product. It is useful
 3           2               2    ['a', ' ', ' ', 'b']

Logic explained:

I decided not to bother with capitalisation - i.e. "It" and "it" are different words in this case - if this is an issue, the simple addition of an UPPER() function would solve that - it's not core to the question.

Step 1:

SELECT id, REGEXP_SPLIT_TO_TABLE(description, ',') FROM wordcount;
-- Keeping the id field helps clarity, even if superfluous.

Result:

id  regexp_split_to_table
1   What a great day
2   This is a product. It is useful
3   ['a'
3    ' '
3    ' '
3    'b']

Step 2 (remove all non-space, non-alpha)

SELECT id, REGEXP_REPLACE(REGEXP_SPLIT_TO_TABLE(description, ','), '[^a-zA-Z\s]', '', 'g')
FROM wordcount;

-- Remove all non-alpha, non-spaces. Otherwise the words "product" and "product." would
-- be counted as different! Again, keeping the id field makes things clearer,
-- even if not strictly necessary for purists

Result:

id  regexp_replace
1   What a great day
2   This is a product It is useful
3   a
3
3
3    b

Step 3 (put the strings into an array):

SELECT id, STRING_TO_ARRAY(REGEXP_REPLACE(
          REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')
FROM wordcount;
--              id again - not strictly necessary at this step.

Result:

id  string_to_array
1   {What,a,great,day}
2   {This,is,a,product,It,is,useful}
3   {a}
3   {"","",""}
3   {"","",""}
3   {"",b}

Finally, the answer itself - UNNEST and then select those words LENGTH > 0 grouping by id and description.

i.e. SELECT the necessary from the following cte (Common Table Expression) - the cte isn't strictly necessary - I could have used the UNNEST... throughout my final query, but that would have been horrible to read and debug. It's the reason Common Table Expressions were invented!

WITH cte1 AS
(
  SELECT id,
    UNNEST(STRING_TO_ARRAY(REGEXP_REPLACE(
    REGEXP_SPLIT_TO_TABLE(description, ','), '[^\w\s]', '', 'g'), ' ')) as "word",
    description
  FROM wordcount
)
SELECT blah... (see above)


How to prefix values shorter than X I have a table that uses an integer for an id. I need to change this to a String and prefix all entries shorter than 4 with zeroes. The database is MySql. I converted the field to a VARCHAR(6), but not need to convert the data in the column to have it prefixed. I tried the following code, but it did not update any of the rows. Any help as to what I am doing wrong would be appreciated.

UPDATE clients
SET clientId =
    CASE
    WHEN char_length(clientId) = 1 THEN '000' + clientId
    WHEN char_length(clientId) = 2 THEN '00' + clientId
    WHEN char_length(clientId) = 3 THEN '0' + clientId
    ELSE clientId END
WHERE
    char_length(clientId) < 4;

Use LPAD function.

LPAD(str,len,padstr)
Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

UPDATE clients
SET clientId = LPAD(clientId, 4, '0')
WHERE
    char_length(clientId) < 4;

For example:
+------------------+
| lpad('12',4,'0') |
+------------------+
| 0012             |
+------------------+


"Persisted" column is not used I have a table with the following structure:
ItemID           int
ItemType         char(1)
Language         char(2)
Localization     char(2)
Literal          nvarchar(4000)

which is used to translate the text of specific ItemID to Literal one. The Literal column may contain HTML tags. For a particular set of queries, these HTML tags needs to be removed and because the sanitization is done with SQL CLR over thousands of rows, I prefer not to perform such on read.
So, I have added a persisted column like this:
ALTER TABLE [dbo].[table]
ADD [LiteralSanitized] AS NULLIF(CAST(LTRIM(RTRIM([dbo].[fn_Utils_RemoveAllHtmlTags] ([Literal]))) AS NVARCHAR(4000)), '') PERSISTED;

The table has only one index (the primary key) with the following definition:
ItemID, ItemType, Language, Localization

So the query was faster, but I am seeing some additional reads for this table:
Scan count    - 2     vs 12,230
Logical reads - 3,234 vs 43,472

which might be normal as now I reading more data because of the column. So, I have added the following index:
(ItemID ASC, ItemType ASC, Language ASC, Localization ASC) INCLUDE ([LiteralSanitized])

but it is not used by the engine. So, I try to force the engine to use it:
UPDATE #temp
SET [QuestionText] = PSGQ.[LiteralSanitized]
FROM #temp PQD
INNER JOIN [dbo].[table_with_translations] PSGQ WITH(INDEX = [the_new_index])
    ON PQD.[ProtoQuestionID] = PSGQ.[ItemID]
WHERE PSGQ.[ItemType] = 'Q'
    AND PSGQ.[Language] = @language
    AND RTRIM(PSGQ.[Localization]) = ''
    AND PSGQ.[LiteralSanitized] IS NOT NULL;

but the engine is doing the following:

performs index scan (on my new index)
then performs nested loop and key lookup with the clustered index
extract the literal column

If my column is Persisted, why the engine continues to try to return the Literal column as it is even not needed?

The most likely answer to your question lies in this blog post from Paul White: Properly Persisted Computed Columns

The lack of optimizer cost model support means SQL Server assigns a small fixed cost to scalar computations, regardless of complexity or implementation. As a consequence, the server often decides to recompute a stored computed column value instead of reading the persisted or indexed value directly. This is particularly painful when the computed expression is expensive, for example when it involves calling a scalar user-defined function.

It sounds very much like SQL Server has decided that it would rather recompute the column value rather than read it from disk.
One solution, as mentioned by Paul, is to use trace flag 176 to disable computed column expansion.


How to recover .ibd and .frm files in MySQL I am developing a Java application with MySQL 5.6.13. A few days earlier I purchased a new laptop. I forgot to create a dump of application from previous laptop and instead I copied folder of my database(.ibd and .frm files) from datadir, when I installed MySQL 5.6.13 in new machine, and I had pasted my database(.ibd and .frm files) into datadir. The server is running fine. But in MYYOG IDE, when I open any of tables I am getting this error:

*.ibd files are not enough, they store data, but InnoDB needs a dictionary which is stored in ibdata1.

To recover the tables you'll need a linux box. Then follow instructions in http://www.mysqlperformanceblog.com/2011/05/13/connecting-orphaned-ibd-files/

Or you can try method described by Chris Calender http://www.chriscalender.com/?p=28

In the worst case you'd need to fetch records from ibd files and then reload into fresh InnoDB instance. See instructions here http://www.percona.com/docs/wiki/innodb-data-recovery-tool:mysql-data-recovery:start

UPDATE: Data recovery toolkit moved to GitHub


Grant permission to KILL connections (sessions) for certain databases only? For a SQL Server on a VM/physical box (not Azure SQL database!)

Is this possible to grant KILL permission, but in a way that grantee can kill connections only for certain databases ?

I would not want to grant this user ability to kill any connection, but just 1 database

Thanks!!

Granting highly selective / fine-grained permission is rather easy via module signing:

USE [master]
Create a stored procedure to do whatever you want the low-privileged Login(s) to be able to do, with the necessary checks, etc.
Create a Certificate
Create a Login from that Certificate
Grant that Certificate-based Login the minimum level of permissions required to accomplish what the Stored Procedure is coded to do (in this case it might just be ALTER ANY CONNECTION (according to @sepupic)
Sign that Stored Procedure with that Certificate using ADD SIGNATURE
Grant the low-privileged Login(s) EXECUTE permission on that Stored Procedure.

HOWEVER, figuring out what Session / SPID is affecting which DB(s) is not easy. The database_id reported in sys.dm_exec_sessions is the "current" database: either what was connected to / their default DB if not specified in the connection string / whatever DB was changed to via the most recent USE statement. But the "current" database isn't necessarily where the problem is. Anyone can execute code and run queries in other DBs using 3-part names (a query can reference 3 tables, each in separate DBs, and none of them being in the "current" DB). So, I'm not sure how you would reliably enforce the "only certain DBs" constraint.


selecting multiple instances of a record based on record lifespan over years I have a table of items that have a origin date and a life span. I want to create a procedure that selects all of the items that will need to be replaced in a specific duration. I feel like there is a math way of doing this in the where clause of a single select. The only way i can think of doing it is iterating though the years and appending the items that would be up for replacement... that seems super slow and inefficient. How should I approach this issue?

Details per comment requests:

Database Engine: SQL Server 2005

Source Table:

CREATE TABLE Items
(
    ID int,
    ItemName varchar(255),
    InstallYear int,
    UsefullLife int
);

Desired stored procedure format:

GetReplacementsWithinYearRange(startyear int, endyear int)

Desired output:

ItemID - ItemName - ReplaceYear

More details:

Sample Records:

1  item1  2010  5
2  item2  2011  6

Desired results for range between 2010 and 2030:

1  item1  2010
1  item1  2015
1  item1  2020
1  item1  2025
1  item1  2030
2  item2  2011
2  item2  2017
2  item2  2023
2  item2  2029

Am I over simplifying here?

CREATE TABLE Items (ItemID int, ItemName nvarchar(255), InstallYear int, Life int)

INSERT INTO Items VALUES (1,'test1',2010,2)
INSERT INTO Items VALUES (2,'test2',2011,2)
INSERT INTO Items VALUES (3,'test3',2012,5)
INSERT INTO Items VALUES (4,'test4',2013,3)

SELECT * FROM Items
GO

CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    SELECT *, InstallYear + Life AS ReplaceYear
    FROM Items
    WHERE InstallYear <= @EndYear AND (InstallYear + Life) >= @StartYear;
END
GO

EXEC GetItemLifetimeInfo 2011, 2022;

Returns:

ItemID  ItemName    InstallYear Life    ReplaceYear
1       test1       2010        2       2012
2       test2       2011        2       2013
3       test3       2012        5       2017
4       test4       2013        3       2016

This works:

CREATE PROCEDURE GetItemLifetimeInfo
(
    @StartYear int,
    @EndYear int
)
AS
BEGIN
    WITH ReplaceYears(ItemID, [Year])
    AS (
        SELECT ItemID, Life
        FROM Items
        UNION ALL
        SELECT Items.ItemID, Items.Life + ReplaceYears.Year
        FROM Items
            INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
        WHERE ReplaceYears.Year <= (@EndYear - @StartYear)
    )
    SELECT Items.ItemID, InstallYear + ReplaceYears.Year AS ReplaceYear
    FROM Items
        INNER JOIN ReplaceYears ON Items.ItemID = ReplaceYears.ItemID
    WHERE InstallYear + ReplaceYears.Year <= @EndYear
        AND (InstallYear + ReplaceYears.Year) >= @StartYear
    ORDER BY 1,2
END

EXEC GetItemLifetimeInfo 2011,2022;

ItemID  ReplaceYear
1   2012
1   2014
1   2016
1   2018
1   2020
1   2022
2   2013
2   2015
2   2017
2   2019
2   2021
3   2017
3   2022
4   2016
4   2019
4   2022


SQL Server - moved index behind PK constraint to different filegroup, but space not freed I have a large table Foo.  There are 4 filegroups (FG1, FG2, FG3, FG4)

FG2 is on a disk that is nearly full.  It has 2 indexes:

a) clustered index (on Date)
b) a unique non-clustered index (on ID)

There is a PK constraint on the table, on ID.

I wanted to move index (b) from FG2 to a different file group - FG4.

First I tried simply builiding a second index, like (b), on FG4, then dropping (b).  This failed - An explicit DROP INDEX is not allowed on index due to the PK.

So I went ahead and rebuilt it on the new filegroup:

CREATE UNIQUE NONCLUSTERED INDEX PK_Foo
ON Foo(ID)
WITH (DROP_EXISTING =  ON, PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
SORT_IN_TEMPDB = ON, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS  =
ON, ALLOW_PAGE_LOCKS  = ON)
ON [FG4]

This succeeded. I can see the index is now definitely on FG4, all is well.
However, no space has been freed up on FG2 at all. If I try to shrink FG2, it shows no space within it is free.

Where has my space gone?

Thanks!

Update - Index sys.data_spaces:

FileGropName    ObjectName  IndexType   IndexName   type_desc   Mb

FG3 Foo NONCLUSTERED    IX_Foo_CorrelationId    IN_ROW_DATA 193802

FG3 Foo NONCLUSTERED    IX_Foo_DeviceIntId  IN_ROW_DATA 97676

FG3 Foo NONCLUSTERED    IX_Foo_DrtId_RC_CorrelationId   IN_ROW_DATA 286386

FG2 Foo CLUSTERED   IX_Foo_LastResponse IN_ROW_DATA 468845

FG4 Foo NONCLUSTERED    PK_Foo  IN_ROW_DATA 104377

Space in FG2 used by objects:

SELECT DS.name AS FileGropName
      ,OBJ.name AS ObjectName
      ,IDX.type_desc AS IndexType
      ,IDX.name AS IndexName
      ,AU.type_desc
      ,AU.total_pages * 8 / 1024 AS Mb
FROM sys.data_spaces AS DS
     INNER JOIN sys.allocation_units AS AU
         ON DS.data_space_id = AU.data_space_id
     INNER JOIN sys.partitions AS PA
         ON (AU.type IN (1, 3)
             AND AU.container_id = PA.hobt_id)
            OR
            (AU.type = 2
             AND AU.container_id = PA.partition_id)
     INNER JOIN sys.objects AS OBJ
         ON PA.object_id = OBJ.object_id
     INNER JOIN sys.indexes AS IDX
         ON PA.object_id = IDX.object_id
            AND PA.index_id = IDX.index_id
WHERE DS.name = 'FG2'
ORDER BY AU.total_pages DESC


Selecting data from multiple rows into a single row I have a table that has columns like following. Batch number is a database design thing like row number

demand_id        batch_number    debit status     customer
     34               1             yes
     34               2                             jack
     35               1             no
     35               2                             kate

I want to create a query that would return this:

   demand_id         debit status      customer
     34                  yes             jake
     35                  no              kate

How can I do that?

Join the table to itself.

So:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.debit_status is not null
and yt2.customer is not null;

Or (if batch_number can reliably be used to fetch the 2 rows needed to make a single row:

select yt1.demand_id, yt1.debit_status, yt2.customer
from yourtable yt1, yourtable yt2
where yt1.demand_id = yt2.demand_id
and yt1.batch_number = 1
and yt2.batch_number = 2;

SQL fiddle.


ObjID in Page Header I am reading this article: Inside the Storage Engine: Anatomy of a page.

I have a database MyDB, and there is a table MyTable in the database.

I have the following questions:

If I do as follows:

(1) use the following query to find the object ID for a table:

Use MyDB;
select sys.objects.name, sys.objects.object_id from sys.objects where (name = 'MyTable');

(2) then use the following command to find all pages allocated to MyTable:

dbcc ind(MyDB, 'MyTable', -1);

(3) then in the result table, I choose one of the data pages (page type = 1), and use the following command:

DBCC TRACEON(3604);
DBCC PAGE (MyDB, 1, 17386, 3);

Then in the dumped content (page header) of step (3), the  m_objId (AllocUnitId.idObj) field should equal to the object ID obtained in step (1). Is that correct?

And whether that works for both user tables and system base tables, such as sys.syscolpars base table?

Based on my test, the above two conclusion are both correct.
What is the meaning of Metadata: ObjectId? In article, the ‘metadata: objectId’ <> m_objId. But from my own tests, the ‘metadata: objectId’  always equals to m_objId. Why? The original article does not explain the metadata clearly.

I am using SQL Server 2005, 2008, 2008 R2, 2012 and 2014

First of all, Paul mentions this about those "Metadata" fields in the blog post:

  Note that I didn’t include the fields starting with Metadata:. That’s because they’re not part of a page header. During SQL Server 2005 development I did some major work rewriting the guts of DBCC PAGE and to save everyone using it from having to do all the system table look-ups to determine what the actual object and index IDs are, I changed DBCC PAGE to do them internally and output the results.

So the "Metadata: ObjectId" field should always match the actual object_id from sys.objects.

The other part of the question was eventually answered in the linked blog post as follows:

  The m_objId is only the actual object ID for system tables with IDs less than 100, and for tables in databases upgraded from SQL Server 2000. For all other tables, the m_objId is part of the allocation unit ID.

So the m_objId will match for system tables (and upgrades from SQL Server 2000).  But for others, he says it's "part of the allocation unit ID."  That's a bit vague.

Running SQL Server 2017 here, I can create a user table with one index page and get the following for those two values from DBCC PAGE:

m_objId (AllocUnitId.idObj) = 178
Metadata: ObjectId = 901578250

I can confirm that 901578250 matches what's in sys.objects object_id for this table.

So where does 178 come from?

Upon further research, Paul has another post: Inside the Storage Engine: How are allocation unit IDs calculated?

In that, he discusses how the allocation_unit_id (from sys.allocation_units) is calculated from m_objId and another internal id field from the page headers (m_indexId).

So, essentially, after SQL Server 2000, the m_objId value is just a building block used to calculate the ID for the allocation_unit.  It's internal to the page and doesn't really map to something else (not in the same way that "Metadata: ObjectId" does anyway).


Initializing Transactional Replication From two Different Database Backup I have two different large Database (200GB for each) which I want to replicate their data to a central database (3rd DB). I followed this link and it was very useful for a single database.

But my problem is I have to restore two different backups to a single database. Is that possible at all?

I am working for a company which it is located in CityA. it has two branches in two different cities CityB and CityC. I have developed a software for them to insert the the customers' information and scan their documents. and I have developed another software for their boss to monitor the data inserted in CityB and CityC for the sake of doing some reports and analysis. until now I could do it by replicating the data form CityB and CityC to CityA with central subscriber topology and it was fine. But now the problem is the Databases in CityB and CityC have become very big (about 200GB for each). Now something happened and I had to establish the replication again. and it is now very cumbersome and time consuming and sometimes it gives strange and wired errors. the last problem I had was the snapshot agent is very very slow and it stops at the following message The process is running and is waiting for a response from the server which I asked here

I can replicate CityB to CityA a with Initialize using Backup but I don't know how to do the same for CityC?

another question is that do I have any better choice instead of replication at all?

please give me the practical advice or links as I am not a professional DB Admin and I am actually a programmer.

Instances are all sql server 2008R2, windows Servers are 2008R2 the connection between the cities are VPN over Internet.

The replication topology that you are describing is known as the Central Subscriber Model which is comprised of two or more Publishers replicating to one Subscriber.  The dilemma you are having is how to go about initializing the Central Subscriber, which is a common conundrum.

I have talked about this in detail in my post Central Subscriber Model Explained and have found through experience that the best way to initialize a Central Subscriber is with a snapshot, utilizing horizontal partitioning, static row filters, and setting the Action if name is in use article properties to Delete data. If article has a row filter, delete only data that matches the filter.

If you have anymore questions please let me know.  I hope this helps.


database inaccessible and stuck in startup state Situation

I have 7 databases which are inaccessible.
In the sql server error log, I see them starting up but they never
become available.
In sysprocesses, I see them stuck in IO_RETRY wait type.

select spid, lastwaittype,cmd, status from master..sysprocesses order by spid;

Effects

The above situation is causes CPU consumption which increases over time.

What do I want now?

Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods.

How did they end of in such a state

We used Idera Virtual restore tool to create these dbs from a backup
file.
We renamed those DBs using SSMS directly.
DBs were still
accessible and readable.
We altered some data on those dbs.
Now DBs
became inaccessible.
We kills spids and restarted sql server without
patience.
Now Idera virtual restore service cannot remove them,
neither can we drop them.
Sql server cannot bring them up.

What do I want now?
  Remove these databases in any way possible, so that they don't consume any resources. I don't mind trying unconventional methods.

To remove them just stop SQL Server and delete MDF and LOG files. I assume you have good backups.


What is table bloating in databases? Can someone explain what bloating means in database terms? For example what does it mean to say an index is bloated. I tried searching for it but there is no explanation of what bloating is, only what it causes or what it is caused by.

Due to how PostgreSQL handles transactions and concurrency, MVCC - Multi-Version Concurrency Control, you can get bloat. In PostgreSQL, when you do an UPDATE or DELETE, the row isn't actually physically deleted. For a DELETE, it simply marks the row as unavailable for future transactions, and for UPDATE, under the hood it's a combined INSERT then DELETE, where the previous version of the row is marked unavailable.

While the data is marked unavailable, it is still there, and the space cannot be used. To then mark the space as available for use by the database, a vacuum process needs to come along behind the operations, and mark that space available for the database to use. It isn't returned to the operating system, however. That only happens when there are no active rows in an entire page, which can be uncommon in some workloads. This can be a good thing for some workloads, because you can just simply update the space on the individual pages inside of the data files, without needing to add additional data files.

Problems come about with bloat when there are are excessively large numbers of dead tuples versus live tuples. Walking along and checking all of the visibility flags takes time, and having more data files for a relation results in additional unnecessary IO load. Bloat is especially noticeable on indexes, which can also have many dead tuples, sometimes many more than the table. Bloat can slow index lookups and scans, which will show up in slowly increasing query times and changing query plans.

You can restore space by using pg_reorg, pg_repack, CLUSTER, or VACUUM FULL. This will go through and reorganize the files, moving tuples and reorganizing to make sure that there are no dead tuples, which will eliminate the bloat.

Bloat can also be efficiently managed by adjusting VACUUM settings per table, which marks dead tuple space available for reuse by subsequent queries.

You can use queries on the PostgreSQL Wiki related to Show Database Bloat and Index Bloat to determine how much bloat you have, and from there, do a bit of performance analysis to see if you have problems with the amount of bloat you have on your tables.


Postgres insert query max length or number of VALUES parameters I'm trying to find a way to speedup population of my db.

I'd like to use COPY as suggested here, but I have only access to my db, not to the entire server. All other tips worked good for me, but unfortunately I need another speedup now.

So now I'm using just an INSERT ... VALUES without autocommit or any constraints.
Maybe I'm totally wrong, but I noticed that when I'm trying to execute lesser number of queries with more values in VALUES, population goes faster.

So I'd like to know what is the query length limit (or VALUES parameters limit).

The answer to your question is that it depends on your system.  Eventually your client or server will run out of memory trying to process a huge statement. typical unimpressive hardware can handle a million (very skinny) rows without trouble.

The answer to your problem is that most widely used libraries have a way to use COPY...FROM STDIN which can be run from the client.  If you use psql, that mechanism is \copy.  In Perl's DBD::Pg, it is pg_putcopydata.


SSAS queries time out when Windows File System Cache drops We have a SQL 2008 Analysis Services instance on Windows 2003 that is experiencing near-daily performance issues (query timeouts) during peak activity. A second identical instance queried from the same application (using round-robin IP to load balance) doesn't have the same issue. We've even switched the two instances, and the problem seems to "stay" with the physical server. We know it's not an SSAS configuration issue. The cube is rebuilt once per day.

Here's what we've been able to determine: the timeouts occur when the Memory\System Cache Resident Bytes perfmon counter drops unexpectedly (we've been calling this a "cache flush"). For example, this morning it dropped from about 14GB to 8.4GB within about 2 minutes. When this happens, the Memory:Page Reads/sec jumps from 0 up to 800-1200, and things start going sideways.

You can see the immediate impact on the MSOLAP counters: Storage Engine Query\Data reads/sec drops dangerously from 50,000 to 5,000 (these obviously weren't physical reads, they were reads from the system cache; this is confirmed by a similar drop to Process(msmdsrv)\IO Read Operations/sec). Storage Engine Query\Avg time/query climbs from 500-2000 up to 20,000-50,000 and queries start timing out. Threads\Processing pool busy threads jump from ~50 up to the max configured value of 240, and the Threads\Processing pool job queue length starts climbing.

Queries continue timing out over the next 30 minutes or so, until (I presume) the file cache is re-filled with the missing data. Memory\Page Reads/sec goes back down to near zero, and things appear to return to "normal".

During this entire process, there is no significant change to Process(msmdsrv):Working Set (~10.5GB) , Process(Total):Working Set (~13.9GB) or MSOLAP:Memory\Memory Usage KB (~50GB). This is a 48-core server with 128GB RAM, running only SSAS (the other server that isn't having trouble is a 24-core box, we haven't found any other significant differences between them).

Instance memory settings are as follows:

<TotalMemoryLimit>80000000000</TotalMemoryLimit>
<LowMemoryLimit>70000000000</LowMemoryLimit>
<PreAllocate>40000000000</PreAllocate>

Based especially on the Memory Usage KB, I don't believe that memory is an issue, other than figuring out why SSAS isn't using its internal memory to cache the entire cube (the cube is about 32GB).

We are currently working to optimize the cube and query design (which I highly suspect will make the problem disappear entirely).

In the meantime, we need to

understand this windows file cache behavior
figure out why its consistently a problem on one physical server and not the other

Here are my questions:

I assume this is "normal" behavior by SSAS? Is there any way to force the entire 32GB cube into SSAS memory, to eliminate the need to read from the windows file cache entirely?
Is there any way to determine what exactly is causing this cache flush, and why it doesn't occur on the (very similar) other host?
Any other ways to mitigate this problem??

I found some other references to SSAS and the windows file cache (the links found here for example), but these seem to imply that the file caching is a bad thing, and talk about a utility to deliberately flush the cache, which seems like the opposite of what I need.

Edit: Added a 100pt bounty

I believe you seeing the symptoms of an issue with Windows 2003 requiring contiguous memory and this causes the running processes including Analysis Service to trim their Working Set memory.  With large amounts of memory allocated this trim process can take a significant amount of time to complete, while this is running new allocations will be blocked, causing the process to stall.
This issue is fixed in Windows 2008 onwards this support article is for SQL Server but has all the links to the relevant pages http://support.microsoft.com/kb/918483 in particular this quote

  In Windows Server 2008, the allocation of physically contiguous memory
  is greatly enhanced. Requests to allocate contiguous memory are much
  more likely to succeed because the memory manager now dynamically
  replaces pages, typically without trimming the working set or
  performing I/O operations. In addition, many more types of pages—such
  as kernel stacks and file system metadata pages, among others—are now
  candidates for replacement. Consequently, more contiguous memory is
  generally available at any given time. In addition, the cost to obtain
  such allocations is greatly reduced.

I believe that with this issue you will either have to upgrade to Windows 2008 onwards or test granting Lock Pages In Memory to the Analysis Services account.  I have also had issues with memory allocation on Windows 2003 which requires a reboot to clear.

This SQLCAT blog compares the changes between Windows Server 2003 & 2008.

This blog mentions some of the issues of LPIM with Analysis Services (near the end)

Also this knowledge base article on working set trimming causing performance problems and this article shows how to Identifying Drivers That Allocate Contiguous Memory


How do I make the zero values show up in count? I have encountered a question with the following table:

Column Name
Type

user_id
int

spend_date
date

platform
enum

amount
int

The goal is to find the total number of users and the total amount spent using mobile platform only, desktop platform only and both mobile and desktop together for each date. For example, if on 2017-07-01 user '1' used mobile and desktop to make a purchase, this purchase would be accounted for "both mobile and desktop together". If user '2' used mobile to make a purchase but never used desktop, this purchase would show up in "mobile only."
My code looks like the following. There is no purchase by anyone using both mobile and desktop on 2017-07-02, thus there should be an extra row ['2017-07-02', 0,0]. However, this row does not show up. Can anyone please let me know what's wrong with my code:
with
mobile_users as (select distinct user_id
                 from spending
                 where platform = 'mobile'),
desktop_users as (select distinct user_id
                  from spending
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id in (select user_id from
                   desktop_users)
   and user_id in (select user_id
                   from mobile_users)
 group by spend_date)
order by spend_date

The thing is you are doing all pruning on Mobile or Desktop or both, you haven't add any condition for other than this values.
What I have done is other than this 3 Mobile or Desktop or both values in table, it will count the row & calculate amount if present.
with
mobile_users as (select distinct user_id
                 from spending
                 where platform = 'mobile'),
desktop_users as (select distinct user_id
                  from spending
                  where platform = 'desktop')
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from mobile_users)
 group by spend_date)
union all
(select spend_date, platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id
                       from desktop_users)
 group by spend_date)
union all
(select spend_date, 'both' as platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id in (select user_id from
                   desktop_users)
   and user_id in (select user_id
                   from mobile_users)
 group by spend_date)
 union all
(
  /* here the code begins */
select spend_date,  platform, count(user_id) as total_users, sum(amount) as total_amount
 from spending
 where user_id not in (select user_id from
                   desktop_users)
   and user_id not in (select user_id
                   from mobile_users)
 group by spend_date
)
order by spend_date


MySQL: Does the "length" setting affect table size in bytes? Regarding setting the length of allowable characters for a field (11) in the example:

Does this number affect the size of the table?

For example, I want to create a char type column and I only want to use three letter codes. Does setting the length to 3 make for a smaller byte size overall?

Is it a waste of space to set it to 16 when I know I'm only going to be using 3? Or is it purely for organizational purposes, i.e. nothing beyond three characters is allowed to be written?

As explained in this answer to a very similar question, the "length" is a hint, provided in result set metadata for applications that want it, of the "typical" maximum number of digits expected to be seen in the column.  As documented, this doesn't change the storage requirements and does not change the range of values that can be stored in a column.
As far as I know, this is essentially a holdover from the early evolution of MySQL when fixed-width terminals were common.

MySQL supports an extension for optionally specifying the display width of integer data types in parentheses following the base keyword for the type. For example, INT(4) specifies an INT with a display width of four digits. This optional display width may be used by applications to display integer values having a width less than the width specified for the column by left-padding them with spaces. (That is, this width is present in the metadata returned with result sets. Whether it is used or not is up to the application.)
The display width does not constrain the range of values that can be stored in the column. Nor does it prevent values wider than the column display width from being displayed correctly. For example, a column specified as SMALLINT(3) has the usual SMALLINT range of -32768 to 32767, and values outside the range permitted by three digits are displayed in full using more than three digits.
When used in conjunction with the optional (nonstandard) attribute ZEROFILL, the default padding of spaces is replaced with zeros. For example, for a column declared as INT(4) ZEROFILL, a value of 5 is retrieved as 0005.
— http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html


CDC Capture job failure/CT table column changes after applying 2014 SP2+CU4 We had an issue with CDC where the capture job failed and wouldn't restart itself.  It was in a DEV environment so no harm but has anyone seen these errors or know what exactly went wrong with CDC?

SQL 2014 EE 12.0.5540 - 2 node AG with SSISDB

  Procedure or function sp_batchinsert_1663605265 has too many arguments
  specified. For more information query the sys.dm_cdc_errors dynamic
  management view

  The Log-Scan Process failed to construct a replicated command from log
  sequence number (LSN) {00002d0d:0000f11f:0002}. Back up the
  publication database and contact Customer Support Services. For more
  information

  Log Scan process failed in processing log records. Refer to previous
  errors in the current session to identify the cause and correct any
  associated problems. For more information query the sys.dm_cdc_errors
  dynamic management view

We attempted to drop and recreate the capture job using sp_cdc_drop_job abd sp_cdc_add_job.  We also attempted failing over to the other node as well as server/service restarts.  Nothing seemed to let CDC restart itself.

We applied CU4 on Friday through AG rolling patching so we are on the latest and greatest.  This issue cropped up right after we patched and failed over.

Added Log query result

It looks like CU2+SP4 (12.0.5540) changes the structure of the underlying _CT system tables that get generated.  It adds the column [__$command_id] int null.

When you upgrade in place to the new CU, the capture jobs will start failing or go to 'between retries...' with the following errors:

Dropping and recreating the capture job does not work (using cdc_add_job).  I had to drop the capture agent CDC job, disable CDC (sp_cdc_disable_table) on the table and then re-enable CDC (sp_cdc_enable_table), which re-adds the job and successfully fires the job and begins collecting.

The issue is that, upon dropping and re-enabling CDC, you lose the previously existing _CT table and all of it's data.

The only information I ahve found about CU4 changing CDC is the following from MS:
https://support.microsoft.com/en-us/help/3030352

It says that they fixed the ordering issue, but do not list any structural changes to the CDC system tables.

UPDATE 1:
I've opened a paid support case with MS regarding this issue.  There's also a connect item open for it: https://connect.microsoft.com/SQLServer/Feedback/Details/3130381

UPDATE 2:
Per MS, using the command sp_vupgrade_replication after patching to CU4 will allow the capture agent job to start successfully.  It still doesn't address the jobs failing before the command is run.  It also does not address this added column not being documented anywhere.  Still waiting to see what the final verdict is.

UPDATE 3:
MS has officially posted to their blog and recognized this as an issue:
https://blogs.msdn.microsoft.com/sql_server_team/cdc-functionality-may-break-after-upgrading-to-the-latest-cu-for-sql-server-2012-2014-and-2016/

No word yet on if they will be re-releasing the CU4 patch to not require manual intervention to restart the CDC jobs or officially document the column change.


How to List All Permissions for SQL Server Fixed Database and Server Roles I am trying to list all current permissions for db_owner and sysadmin for SQL Server 2012. I found these SPs:
EXEC sp_srvrolepermission 'securityadmin'
EXEC sp_dbfixedrolepermission 'db_owner'

However, these are deprecated and only accurate as of SQL Server 2000. Is there an equivalent mechanism to accomplish the same thing today?

Neither of these roles has an enumerated list of permissions.  db_owner has all permissions in a database, and sysadmin is exempt from permissions checking on both the server and every database.  There are a handful of system procedures that are hardcoded to check for sa or perhaps dbo.


Very poor insert performance MySQL / Percona Server I'm running a Percona-Server instance off an Ubuntu server install.  I'm using an application that needs to access this database, and its having VERY poor performance.  Once the database has been established, the application goes in (upon install) and creates the schema.  It defaults everything to MyISAM, however I have converted the table engines back to InnoDB.  The issue that I'm having is VERY poor insert performance.  This application is very write-heavy, and it seems as though its writing each row 1 at a time to disk, not using any sort of buffers, however I'm unsure how to check or verify this.  Even a select(*) from one of the tables will take 2.4 seconds, and there is only 163,000 rows.  I'm kind of at a loss as to what else I can do.

Server has 8GB of ram, and CPU is almost entirely idle while this is occurring.

my.cnf:

[mysql]

# CLIENT #
port                           = 3306
socket                         = /var/run/mysqld/mysqld.sock

[mysqld]

# GENERAL #
user                           = mysql
default_storage_engine         = InnoDB
socket                         = /var/run/mysqld/mysqld.sock
pid_file                       = /var/run/mysqld/mysqld.pid

# MyISAM #
key_buffer_size                = 32M
myisam_recover                 = FORCE,BACKUP

# SAFETY #
max_allowed_packet             = 16M
max_connect_errors             = 1000000
skip_name_resolve
sql_mode                       = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate_is_now                 = 1
innodb                         = FORCE
innodb_strict_mode             = 1

# DATA STORAGE #
datadir                        = /mnt/Storage/mysql/

# BINARY LOGGING #
log_bin                        = /mnt/Storage/mysql/mysql-bin
expire_logs_days               = 14
sync_binlog                    = 1

# CACHES AND LIMITS #
tmp_table_size                 = 32M
max_heap_table_size            = 32M
query_cache_type               = 0
query_cache_size               = 0
max_connections                = 500
thread_cache_size              = 50
open_files_limit               = 65535
table_definition_cache         = 1024
table_open_cache               = 2048

# INNODB #
innodb_flush_method            = O_DIRECT
innodb_log_files_in_group      = 2
innodb_log_file_size           = 512M
innodb_flush_log_at_trx_commit = 0
innodb_file_per_table          = 1
innodb_buffer_pool_size        = 6144M
innodb_buffer_pool_instances   = 1
innodb_read_io_threads         = 64
innodb_write_io_threads        = 64
innodb_io_capacity             = 10000

# LOGGING #
log_error                      = /mnt/Storage/mysql/mysql-error.log
log_queries_not_using_indexes  = 1
slow_query_log                 = 1
slow_query_log_file            = /mnt/Storage/mysql/mysql-slow.log

desc of one table that is having poor insert performance:

mysql> desc parts;
+------------+---------------------+------+-----+---------+----------------+
| Field      | Type                | Null | Key | Default | Extra          |
+------------+---------------------+------+-----+---------+----------------+
| ID         | bigint(20) unsigned | NO   | PRI | NULL    | auto_increment |
| binaryID   | int(11) unsigned    | NO   | MUL | 0       |                |
| messageID  | varchar(255)        | NO   |     |         |                |
| number     | bigint(20) unsigned | NO   | MUL | 0       |                |
| partnumber | int(10) unsigned    | NO   |     | 0       |                |
| size       | bigint(20) unsigned | NO   |     | 0       |                |
| dateadded  | datetime            | YES  | MUL | NULL    |                |
+------------+---------------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

OBSERVATION #1

Since your buffer pool is 6G (6144M), the innodb_log_file_size should be 1536M (25% of 6G)

OBSERVATION #2

You have sync_binlog set to 1. This provides the safest ACID compliant setup. It can also slow things down dramatically. You say it seems as though its writing each row 1 at a time to disk. That's the case because each completed DML (INSERT, UPDATE, DELETE) and DDL (ALTER TABLE) statement gets written to the binary logs. The default for sync_binlog is 0. That let's the OS be responsible for flush binary log changes to disk.

OBSERVATION #3

You have innodb_io_capacity set at 10000. That's really 10000 IOPs you are expecting of mysql. Try lowering it.

There are some things to do in this respect

When it comes to sync_binlog

Comment it out of my.cnf
Set sync_binlog to 100 or 200 to pace the flushing of binary logs

You should make sure data disk has a battery-backed cache (with a good battery)

RECOMMENDATION

STEP 01) Set these options in my.cnf

[mysqld]
innodb_fast_shutdown = 0
innodb_log_file_size = 1536M
sync_binlog = ( you choose 0 or 100 )

STEP 02) Remove innodb_io_capacity. Let it be set to default.

STEP 03) Run this command in mysql

mysql> SET GLOBAL innodb_fast_shutdown = 0;

STEP 04) Apply settings

cd /mnt/Storage/mysql
service mysql stop
mv ib_logfile0 ib_logfile0.bak
mv ib_logfile2 ib_logfile1.bak
service mysql start

This should speed things up


Should we need to use Barriers on a production database (MySQL/InnoDB)? Even though we are using a journaling filesystem (EXT3) with barrier enabled, is this still safer and recommended?

e.g

mount -o barrier=1 /dev/sda /mntpnt

Reference:

http://lwn.net/Articles/283161/
https://serverfault.com/questions/403891/journaled-filesystems-and-power-failure

Yes it is always safer. Thought the chance of actually getting corruption is low. When the filesystem gets corrupted it is very likely that repairing it will be successful. ACID compliant databases like InnoDB also do fsyncs/barriers to make sure committed changes are permanently stored on disk.

Don't forget that in a production environment you should be using a high quality server using redundant PSU and or UPS so the chance you would actually ever need the journal is already fairly small.

A reason not to enable it might be performance. Of course, this only affects write performance. But these kinds of things should be measured as they are highly dependent on your hardware. For instance when running with battery-backed write cache on a raid controller the performance penalty will be as good as zero because of the writeback caching of the controller.

So all in all I believe the risk to be very small but it depends on what is at stake. Personally, I think that if the risk is high enough to warrant this, you should be looking at replication to a second server. You already are making regular backups of course.

BTW: fsync is inefficient on ext3 as it always syncs all files! ACID compliant databases tend to do a lot of fsyncs so it is better to use ext4 if you can.


Help me to create correct ER Diagram I want to create a database where one user can either be a Doctor or a Patient (but not both). So that, I can get all doctors or patients from doctor or patient table as well as I can search doctor or patient credentials details from user table. I really confused about which way I use. Please suggest me which diagram I use.

Diagram 1:

Diagram 2:

As User, Doctor and Patient have very different attributes best you can do is split into 3 tables. As User is the first object you are accessing (registration-login), best Users will function as a father to Doctors and Patients tables.

For example -
Users - UserName, FirstName, LastName, EMail, PhoneNumber, Company/Address/Active (Many keep encrypted Password in the DB as well).

You can add Role - 1 for Doctor, 2 for Patient (3 for Nurse, 4 for Clinic Manager and so on).
Doctor and Patient can have the same Ids as User or different so you need to add DoctorId/PatientId in Users or add UserId in Doctors/Patients. Again, as Users come first, it's best to go for an option where the Doctor Id can be found from the User.
For easier setup, you can create different Ids for Doctors and Patients, as Doctors have little and Patients many, you can just keep 1000 values to Doctors and 1001+ for Patients, or you can use different sequences / smart increment value, so Doctors can be 1,3,5 and Patients 2,4,6.
Now Doctors can have additional tables such as Skill Sets where Patient have Patient Records.
Note you can download many medical key-value static tables online, as updates are keep coming in the industry, best is to integrate your system with reliable services.
From experience, I believe managing the patient record and the doctor mail box will be your hardest task, as Doctors need to query the full patient history and every day, they also need the latest updates on their patients.


Best Practice to Store an Admin Configured Variable Some background:

Users submit a form that has a category.

If (category has manager) {
send manager email
}
else {
send 'default' manager an email
}

For the sake of argument, manager_category has 2 columns; Manager's Email and Category

Admins can update the 'default' manager.

Whats the best practice for storing the 'default' manager?

Imo, adding another row in manager_category with 'default' as the Category is best.

Another option is to create a new table with 1 row and 1 column.

What is the best way to store this value?

EDIT: I should add that there's 4000+ categories. Most of them don't have rows.

I have worked with databases that had a settings table, which was table with one row and a lot of columns, where each column represented a value that was configurable, and should hold true everywhere in the application.

As I believe most DBMSes have an upper limit on the number of columns a table can have, there are limits on this, but it can be a practical solution for setting what amount to configurable global constants for the application.

That said, given that there is a logical place to store this information, I would agree that your suggestion (store it with the rest of the manager data, and a special category (default)) makes perfectly good sense, and should make queries a bit easier to write, and to follow later.