Backup and Restore of master KEY and Certificate are not working on same server but different DBs. I have SQL Server 2012

The problem here is that your symmetric keys are not the same key, so it cannot be used in the destination database to decrypt the database on another database/instance/machine. Take a look at this link which describes the encryption hierarchy in SQL Server.

Basically, what is happening is you're creating a symmetric key on Source and using that to encrypt the column data. That symmetric key is protected (encrypted) by the certificate, the certificate is protected (encrypted) by the database master key and the DMK is protected (encrypted) by the service master key.

When you restore the DMK and Certificate to the destination database and create the new symmetric key, this doesn't allow you to decrypt the original data because the encryption was done by a different symmetric key.

In order to decrypt the data, you need to create an identical symmetric key. You achieve this by specifying the KEY_SOURCE and IDENTITY_VALUE properties of the symmetric key at creation time. By default, these values are randomly generated unless you specify explicit values.

By providing explicit values, an identical key is created and can be used on a different database/instance/machine to decrypt data encrypted by the source symmetric key. You can even use different certificates and database master keys to protect your destination symmetric key, it won't affect the decryption because the symmetric key is identical to the source key and once opened will decrypt the data.


Tolerating SQL errors (orderby not in distinct, select not in group by) in MySQL 5.7.13 vs 5.7.11

Not quite what you are saying:

5.7.5 changelog says

"Queries of the form SELECT DISTINCT col1 ... ORDER BY col2 qualify as forbidden by SQL2003 (hidden ORDER BY columns combined with DISTINCT), but were not rejected with the ONLY_FULL_GROUP_BY SQL mode enabled. "

Perhaps you should plea to bugs.mysql.com for a 'mode' to violate SQL2003 on the grounds that there is no way to get what you want.


Any issue encountered after implementing SQL Server 2017 CU22?

Couple of weeks ago we patched to SQL2017 CU 22 in our all test and staging environment to begin with.
So far we did not see any issues. We were planning to go in prod by this weekend but from SQL 2019 CU 7 horror we prefer to make a change in our process to push updates to prod by at least a month now.
So as of now we have not see any issues on 2017. As a general guidance I will say patch to all lower environments and may be wait for some time , you decide that interval , and move ahead with the decision to patch.


Separate license needed for Visual Studio 2012 SQL Server database projects?

You don't need a special SQL Server license to use Visual Studio database projects. As mentioned in srutzky's answer the database projects are driven by SSDT which is a free download.

If you are willing to use a newer version of Visual Studio than 2012, and you want to use a database project within Visual Studio then you should consider Visual Studio 2015 Community Edition. As you can see in the "Muliple languages" section you can choose a "SQL Server" project type/template which is the database project.

As far as I recall none of the free versions of Visual Studio 2012 allowed you to create database projects. That means if you're committed to using Visual Studio 2012 to create/manage/etc. your database project then you'll need to buy an appropriate license for the version of Visual Studio 2012 you want to use.

Note the licensing restrictions are at the bottom of the Visual Studio 2015 Community Edition that I linked to above. The cliff notes version at the time of writing this answer is:

Individuals

  Any individual developer can use Visual Studio Community to create their own free or paid apps.

Companies

  An unlimited number of users within an organization can use Visual Studio Community for the following scenarios: in a classroom learning environment, for academic research, or for contributing to open source projects.

  In non-enterprise organizations, up to five users can use Visual Studio Community. In enterprise organizations (meaning those with >250 PCs or >$1 Million US Dollars in annual revenue), no use is permitted beyond the open source, academic research, and classroom learning environment scenarios described above.

The full license terms are found here.


Foreign Key referencing multiple tables in a Question Bank scenario

Is QuestionNumber a unique identifier somehow for table Questions? Or is the QuestionBank designed to have, say, 5 different sets of questions with numbers 1 through 25?

If Questions.QuestionNumber is a mere non-unique ordering value for a given BankId, then there's no way to know, when saving a QuizQuestionStudent record, what QuestionBank a particular question belongs to without presuming that the answer is for a question in the proper bank. When QuestionNumber = 5, you'd have no idea how to check its BankId if multiple banks had a fifth question. You could presume it belongs to the same BankId as the Quiz, but then what is left for the database to check?

Now, if QuestionNumber is unique, you have a different problem. You want the Question's BankId to be required to be the same as the Quiz's BankId. Repeating the BankId in the QuizQuestionStudent table wouldn't help, because that value is coming from the app and it's what you're trying to check. You want to make sure data in two other tables conforms not just to QuizQuestionStudent values, but also to each other's values. You can't do that with a foreign key. (A significantly different design for all of the tables might eliminate the issue, but I wasn't able to come up with one.)

However, it can still be strongly enforced at the database layer. You can create an "indexed view" or "materialized view" and then constrain its values. The view can reference multiple tables. It would pull the BankId from two different paths and make sure they are the same.

CREATE VIEW QuizValidityCheck
WITH SCHEMABINDING
AS
(
SELECT
1 AS DupeCheckValue
FROM QuizQuestionStudent qqs
INNER JOIN Quizzes qz ON qqs.QuizId = qz.QuizId
INNER JOIN Questions qu ON qqs.QuestionNumber = qu.QuestionNumber
CROSS JOIN (VALUES (0),(1)) AS RowExploder(ExploderRow) -- this forces there to be 2 rows any time there's a BankId mismatch, quick causes the unique constraint below to fail
WHERE qu.BankId <> qz.BankId
)
GO

CREATE UNIQUE CLUSTERED INDEX IX_CHECK_BankIdMismatch ON QuizValidityCheck(DupeCheckValue)
GO

You can also use a UDF to enforce a custom CHECK constraint on a column, which could go and check the other. But there are some scenarios where that falls apart.


Difference between functional dependency and join dependency

Join Dependencies can be considered a generalization of Multivalued Dependencies, following the fact that a Multivalued Dependency X →→ Y in a relation R can be seen as another way of writing a binary Join Dependency ⨝[XY, X(U − Y)], where U is the set of all the attributes of the relation R.

For instance, in a relation describing employees together with their salary history as well as other attributes R(empNo, salary, year, name, role), we can say that there is a MultivaluedDependency empNo →→ salary, year or, equivalenty that there is a Join Dependency ⨝[{empNo, salary, year}, {empNo, name, role}] (note that this makes clear that in R holds also the “dual” Multivalued Dependency empNo →→ name, role).

You are asking which relations there are between Functional Dependencies and Join Dependencies. The question is reasonable, since there is a relation between Functional Dependencies and Multivalued Dependencies.

In Chapter 8 of Abiteboul, S. “Foundations of Databases.” Reading, Mass: Addison-Wesley, 1995. you can find an answer which is also proved:

  Proposition 8.3.3. Let U be a set of attributes, {X, Y, Z} be a partition of U, and Σ be a set of Functional Dependencies over U. Then Σ ⊨ ⨝[XY, XZ] if and only if either Σ ⊨ X → Y or Σ ⊨ X → Z.

In other words, if a Functional Dependency X → Y holds in a relation schema R, then this implies that the binary Join Dependency ⨝[XY, XZ] holds, and viceversa. Note that this is equivalent to say that you can perform always a lossless decomposition of a relation R{X,Y,Z} with a functional dependency X → Y in R1{XY}, R2{XZ}.

So, for instance, in the previous example, since empNo → name, the Join Dependency ⨝[{empNo, name}, {empNo, salary, year, role}] holds and {R1(empNo, name), R2(empNo, salary, year, role)} is a lossless decomposition.


Defragmentation - Rebuilding Indexes SQL Server 2005

Yes you can rebuild while the database is online with active users. It's definitely better to do it off-peak if possible.
During the rebuild your queries will run slower, mostly due to the I/O overhead of rebuilding the index. How noticeable this is depends on the specifics of your system. The performance penalty is only during the rebuild - not forever after.
Again, the duration of the rebuild depends on your specific setup. You should try running this on an equivalent development or staging server.
No other associated side-effects. Once the rebuild is complete you'll be back up and running as usual.


Scenario to automate the promotion of a read replica when the master db is not responding

Beware of Split Brain.

The tough problem in promoting a Slave to be Master happens when the problem is in the network.  With a dead network, most types of monitoring will mistakenly think that the Master is dead and promote the Slave.  Now you would have both servers alive and thinking they are the 'Master'.  This condition is called the dreaded "split brain".  Pretty quickly, clients insert duplicate keys and/or generate duplicate auto_increment ids.  The data corruption can be terrible to unravel.

MHA half solves it by promoting a slave when it is sure that it is safe.  If the problem might be a network glitch, it punts.

Galera fully solves it by requiring a quorum of nodes to vote that they have the majority.  This, however, requires 3 nodes, preferably in 3 geographic locations.  (Think flood, hurricane, earthquake, etc.)  All nodes can be writable (that's another feature of Galera).  Galera is built into MariaDB and PXC.


Can a primary key of one table be a foreign key of another table?

Hi and welcome to the forums. A simple Google led me here.

CREATE TABLE employee
(
  Employee_ID INT,
  First_name VARCHAR(25),
  Last_name VARCHAR(25),
  PRIMARY KEY (Employee_ID),  -- Must be present - otherwise you cannot create the FOREIGN KEY
  Employee_designation VARCHAR(10)
);

CREATE TABLE joining_vital
(
  Employee_ID INT(10),
  Joining_ID INT(10),
  Joining_date DATE,
  FOREIGN KEY (Employee_ID) REFERENCES employee (Employee_ID)
);

Take a look here and also here - help us to help you! These forums are a great resource and you will get more out of them if you take the trouble to follow the guidelines.


SQL Server mirroring for more than 10 databases

Database Mirroring is a legacy technology that has been superseded by AlwaysOn Availability Groups

  Maximum number of availability groups and availability databases per computer:

  The actual number of databases and availability groups you can put on
  a computer (VM or physical) depends on the hardware and workload, but
  there is no enforced limit. Microsoft has tested up to 10 AGs and 100
  DBs per physical machine, however this is not a binding limit.
  Depending on the hardware specification on the server and the
  workload, you can put a higher number of databases and availability
  groups on an instance of SQL Server.

Prerequisites, Restrictions, and Recommendations for Always On availability groups


You need to run DESC state_master;. This will show you all the columns in that table.

Fine the correct column name for the state name. Evidently, from the error, it is not State_name. Then, use that correct column name in the SQL Statement.

If State_Name is the actual column name, then try lowercase on all column names

$result = mysql_query("SELECT state_Id FROM state_master WHERE state_name='$statename'") or die(mysql_error());
$row=mysql_fetch_assoc($result);
return $row['state_Id'];

Give it a Try !!!


Distributed transaction and Read Committed Snapshot

Yes RCSI is supported for use with distributed transactions as documented in Books Online:

Choosing Row Versioning-based Isolation Levels


Sp_forEachDB ' DBCC Check DB' - why does it only check the first DB?

Question: What's the right way to do sp_foreachdb 'dbcc checkdb'?

Don't use sp_msforeachdb. If you're dead set on using it, though (given the many known drawbacks and issues) then you'll need to set the database context before attempting to run checkdb without a database name, or use the database name parameter.

Assume: exec sp_MSforeachdb 'SELECT DB_NAME()'

The output of this will be 'master' however many times as the number of databases that reside on the instance. Simple put, the stored procedure gives you the database name and will run it (hopefully for each database, though known to skip) but not set the context.

Output: master, master, master, master,...

Assume: exec sp_MSforeachdb ' use [?]; SELECT DB_NAME()'

The output of this will be the names of the actual databases (again, hopefully it didn't skip any) on the instance.

If you'd like to run CheckDB this way, you could use:

exec sp_MSforeachdb ' use [?]; DBCC CHECKDB()'
OR
exec sp_MSforeachdb 'DBCC CHECKDB([?])'

  Answer: From SpBlitzErik - use Ola hallengren's scripts, you dummy.

Also valid and a much better solution.

  1.How often do I need to run it? It needs to run at the same interval that I keep my backups, right? If I keep the backups for 1 day, I need to run it every day?

IMHO totally depends on the environment and the performance hit. Regardless I'd say once a week (for a total check, not necessarily all at once) is the minimum. Once a day is ideal but may be overkill, especially on large databases where a good portion of the data is read only/archive.

  2.Can I restore the databases to my test environment and checkDB there?

Yes, it'll test your backups and the database but won't test the primary server. This means you might not have any issues on the test server but the production server may have intermittent IO issues - this won't show that. Still better than nothing and you're testing backups :)

  3.If I've got lots of sql servers, but they all use the same SAN, am I going to want to stagger my CheckDB's?

Depends on how much you like/dislike/abhor your SAN admins. If you want to piss them off, fire them all off at the same time and listen to them complain about their enterprise class SAN not being able to handle an enterprise class workload. Otherwise, yes, stagger them.


Are there any way to stop typing 'go' in sqlcmd?

No, you need to type a batch separator. The default is GO. However, you can change that by using the -c switch.

For example:

C:\>sqlcmd -SServername -cFOO

1> select @@version

hit enter

2> FOO

hit enter

This will run.

Can't get rid of the intermediate enter for you. Sorry.


The insertion of records from the RAW table into the dbo table got
  rolled back; BUT
  The truncation of the RAW table did not roll back.

No, there was no rollback at all, and here is the repro.

With xact_abort off that is your default set option I created 2 tables, I then open transaction end do 2 inserts one of which(the second one) fails, I added select @@trancount and select from both tables so you can better see what happens:

--set xact_abort on

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;

select @@trancount as [@@trancount after commit];

select *
from dbo.t1;

select *
from dbo.t2;

As you see, no rollback was made, only your commit. You insert (-1) into dbo.t2 and this row is permanently there. This is because the error

  Msg 515, Level 16, State 2, Line 18 Cannot insert the value NULL into
  column 'col1', table 'dbo.t2'; column does not allow nulls.

is statement terminating only. The second statement fails so no row were inserted, but insert of (-1) was not rollbacked, and as you see after the error your transaction is still open. It's your commit that commits insert of -1 and table truncation.

Now the second test: uncomment set xact_abort on, this will make statment terminating only error be batch aborting, all the statements within transaction will be rolled back and execution will be interrupted as soon as the error occurs.

So t1 table will never be truncated and the insert of (-1) in t2 will be rolled back.

And now how your code should be written:

set xact_abort on;

if object_id('dbo.t1') is not null drop table dbo.t1;
if object_id('dbo.t2') is not null drop table dbo.t2;
go

create table dbo.t1 (col1 int);
insert into dbo.t1 values(1), (null);

create table dbo.t2 (col1 int not null);
go

begin try
begin transaction

    insert into dbo.t2
    values(-1);

    insert into dbo.t2
    select col1
    from dbo.t1;

    select @@trancount as [@@trancount before truncate];

    truncate table dbo.t1;

commit transaction;
end try

begin catch
    select @@trancount as [@@trancount in catch before rollback];
    if @@rowcount > 0 rollback;
    throw;
end catch;

Your code should always set xact_abort on and it should have try..catch block.

You should do rollback from catch ad throw the error


Self referencing tables can be used to store hierarchical data.  For example, see my answer to this question.

You can also use self referencing tables to record non-hierarchical relationships which are one-to-one.  For example, let's say you have a PERSON table and you want to have a relationship is currently married to.  You might model this with an intersection table, or you might use a simpler self referencing relationship.


SQL Server 2008: How to send an email when a step in a SQL Server agent job fails, but overall job succeeds

You could create a Stored Proc for steps 1-4, with error handling that notifies you via email on failure.

Something like:

CREATE PROCEDURE TestEmailOnFail
AS
BEGIN
    BEGIN TRY
        /*
            Perform some action that might fail
        */
        SELECT 0/0; --THIS WILL FAIL
    END TRY
    BEGIN CATCH
        DECLARE @subject nvarchar(max) = 'Job Failure Notification';
        DECLARE @body nvarchar(max) = 'TestEmailOnFail Job Failed'
            + CHAR(10) + CHAR(13) + 'Error Number:  ' + CAST(ERROR_NUMBER() AS nvarchar(max))
            + CHAR(10) + CHAR(13) + 'Error Message: ' + ERROR_MESSAGE();
        DECLARE @to nvarchar(max) = 'somebody@email.com';
        DECLARE @profile_name sysname = 'SQLMailProfileName';
        EXEC msdb.dbo.sp_send_dbmail @profile_name = @profile_name,
            @recipients = @to, @subject = @subject, @body = @body;
    END CATCH
END

I just tested this using my SQL Mail Profile, and my email address and got the following email:

TestEmailOnFail Job Failed
Error Number:  8134
Error Message: Divide by zero error encountered.


MySQL ignores foreign keys constraints in a table representing a many to many relationship

Worth checking that you are using a Storage Engine that supports Foreign Key Constraint checking.

SELECT TABLE_NAME,
       ENGINE
FROM   information_schema.TABLES
WHERE  TABLE_SCHEMA = 'dbname';

The MyISAM Storage engine for instance does not support checking of Foreign Key Constraints.

If so and you need Foreign Key checks, try changing the storage engine to InnoDB:

ALTER TABLE my_table ENGINE = InnoDB;

If you need this to be the more standard behavior, look at the --default-storage-engine option in your startup.


Non clustered index on partitioned table

Your job most likely blew up because you added the non-clustered index to the base table, but did not add it to the staging table required for switching partitions in and out.
There are a significant number of things to consider when using partition switching - please read Transferring Data Efficiently by Using Partition Switching.  From that post.

Nonclustered indexes must be defined and must be identical. Any
nonclustered indexes that are defined on the target table are also
defined on the source table and are structured identically in terms of
uniqueness, subkeys, and the sorting direction (ASC or DESC) for each
index key column. Nonclustered indexes that are disabled are exempt
from this requirement.


When using underscores, should they be reserved to junction tables?

If you want to use underscore in that way, try using CamelCase the rest of the time.

Then you'd have CompanyType , and if a company could have many types, you could have a junction table called Company_CompanyType


I am getting an error while trying to download binary for mysql 5.5.11

I'm guessing that you got that link from http://www.ovaistariq.net/490/a-step-by-step-guide-to-upgrading-to-mysql-5-5/?  At any rate, MySQL is up to 5.5.20 now and I don't think they offer older versions of 5.5.x.  If you want an older GA release, you'd have to download 5.1.61.

If you visit http://www.mysql.com/downloads/mysql/ in your browser, you can get a correct URL to a current version.


Edited: look at the last example is a better way

Yes, but you must send the two sentences within a transaction and add the "for update" clause in the select sentence.

Python

If you send then sentences one by one you can write something like this (in Python the connection handles transactions)

conn = psycopg2.connect("dbname=suppliers user=postgres password=postgres")
cur = conn.cursor()
cur.execute("select port from socketport where available = 't' limit 1 for update")
row = cur.fetchone()
port = row[0]
cur.close();
cur = conn.cursor()
cur.execute("update socketport set available = 'f' where port = %s", (port))
cur.close();
conn.commit();

node.js

If your language does not support transaction you must include manually:

const { Client } = require('pg')
const client = new Client(connOpts)
try{
  await client.query("BEGIN TRANSACTION");
  var result = await client.query("select port from socketport where available = 't' limit 1 for update");
  var port = result.rows[0].port;
  await client.query("update socketport set available = 'f' where port = $1", [port]);
  await client.query("commit");
} catch (e) {
  await client.query('ROLLBACK')
  throw e
} finally {
  client.release()
}

pure postgresql

You can use direct postgres. Any block is within transaction

do $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
end;
$SQL$;

looking for a better solution

using a function

You can do something like this. A postgres function that does the job and returns the port, then you can call the function from python (or whatever you want).

create or replace function get_avaialbe_port() returns integer
  language plpgsql
as $SQL$
declare
  v_port integer;
begin
  select port into v_port from socketport where available = 't' limit 1 for update;
  update socketport set available = 'f' where port = v_port;
  return v_port;
end;
$SQL$;

then in your backend you can call:

select get_avaialbe_port()

All code inside a function runs inside a transaction. The "for update" is stil needed, because without it two functions can run simultaneously and can take the same value of v_port prior to the update statement.

only one sentence

Also you can update the selected value in one sentence

update socketport
  set available = 'f'
  where port in (select port from socketport where available = 't' limit 1)
  returning port;

You can call it directly from python or node.js. You can see a postgres running example at: http://rextester.com/OKMC68108

UPDATE sentence with RETURNING clause acts as SELECT you can query it and fetch the values. UPDATE ... RETURNING is like a SELECT that updates.

NOTES

Watch at LIMIT 1 in the examples. In the postgres version it is mandatory because of SELECT INTO. In python and node.js version it is recommended because in that way only one row is locked. Then the table can be updated with releasing ports or taking others using some kind of "order by random"


History from SQL Agent Jobs (SQL Server 2005)

You need msdb.dbo.sysjobhistory and a few JOINs of course to read the data

To change how SQL Server Agent uses this table, use this stored procedure with suitable values

EXEC msdb.dbo.sp_set_sqlagent_properties
    @jobhistory_max_rows = 1000,
    @jobhistory_max_rows_per_job = 100


When the old execution plans changed in plan cache

The are a lot of caveats about execution plan generation and reuse, so the old saying 'it depends' definitely comes into play.  I think you will find some very valuable information from these links (they include examples of various tests):

https://www.brentozar.com/archive/2015/01/updating-statistics-cause-recompile-no-data-changed/

https://technet.microsoft.com/en-us/library/ms181055(v=sql.105).aspx

https://www.brentozar.com/archive/2013/12/recompile-hints-and-execution-plan-caching/


Extracting week from date seems to give incorrect value at the start of the year

I think I actually have an answer to this (typically mere minutes after asking my question), I was being severly thrown off by other extract() functionality.

2016-01-03 was a sunday. select extract(dow from '2016-01-03'::date) returns 0.

I was assuming that because extracting the day-of-week of a Sunday returns 0, then extract would consider weeks to start from Sunday, when in fact it considers weeks to start from Monday, as evidenced by this:

with
    __steps as(
        select unnest(array['2016-01-03', '2016-01-04']::date[]) as step_date
    )
select
    extract(week from step_date) as week,
    extract(dow from step_date) as dow,
    to_char(step_date, 'Day') as dow_name
from
    __steps
order by
    step_date asc

Returning

week    dow     dow_name
----    ---     --------
53      0       Sunday
1       1       Monday


Authentication failed when deploy an Analysis Services project

I was trying to deploy from my computer to a server on another domain

The connection to SSAS to deploy always uses Windows Authentication, and always uses the account for Visual Studio.  To deploy to an SSAS server in another domain (or no domain), launch SSAS with 'runas /netonly' providing the target domain credentials.

This will launch Visual Studio without impersonation, but using the specified credentials for all network access.


Figured it out, I just have to put the sequence's nextVal in quotes in the control file:

append into [tablename]
(
   [column name] "[sequence name].nextVal"
   ...
)


Once someone executes INSERT DELAYED, a system thread will open in order to track and mitigate INSERTs of this kind. MySQL 5.6 deprecates it and 5.7 does not support it.
What is more, in terms of MySQL Replication, Slaves will completely ignore an INSERT DELAYED command from a Master and execute them as a standard INSERT.
Please read the MySQL 5.5 Documentation on this for further info.
UPDATE 2016-07-05 11:30 EDT
Since you are interested in process/client, which originates this "INSERT DELAYED" query, you should look at the thread states. The MySQL 5.5 Documentation describes them well:

States associated with a connection thread that processes an INSERT DELAYED statement from the client:

allocating local table : The thread is preparing to feed rows to the delayed-insert handler
thread.

Creating delayed handler : The thread is creating a handler for DELAYED inserts.

got handler lock : This occurs before the allocating local table state and after the waiting for handler lock state, when the connection thread gets access to the delayed-insert handler thread.

got old table : This occurs after the waiting for handler open state. The delayed-insert handler thread has signaled that it has ended its initialization phase, which includes opening the table for delayed inserts.

storing row into queue : The thread is adding a new row to the list of rows that the delayed-insert handler thread must insert.

waiting for delay_list : This occurs during the initialization phase when the thread is trying
to find the delayed-insert handler thread for the table, and before attempting to gain access to the list of delayed-insert threads.

waiting for handler insert : An INSERT DELAYED handler has processed all pending inserts and is waiting for new ones.

waiting for handler lock : This occurs before the allocating local table state when the connection thread waits for access to the delayed-insert handler thread.

waiting for handler open : This occurs after the Creating delayed handler state and before the got old table state. The delayed-insert handler thread has just been started, and the connection thread is waiting for it to initialize.

States associated with a delayed-insert handler thread that inserts the rows:

insert : The state that occurs just before inserting rows into the table.

reschedule : After inserting a number of rows, the delayed-insert thread sleeps to let other threads do work.

upgrading lock : A delayed-insert handler is trying to get a lock for the table to insert rows.

Waiting for INSERT : A delayed-insert handler is waiting for a connection thread to add rows to the queue (see storing row into queue).


Inserting few millions of rows: Does indexing happens along with the insert operation

Everything will be populated on insert, it is also one of the reasons why having too many indexes is considered bad practice on tables that have a lot of CRUD operations.

In your case, you only have a CLUSTERED and a NON CLUSTERED index (SQL Server creates a CLUSTERED index for a PK by default even if you don't specify it). Non clustered indexes have something called a "clustering key" which is basically a pointer to the clustered index.

A clustering key consists of the values of the clustered index keys from the row being pointed at and the Uniquifier (if present). With every insert, the clustered key has to sort the values in memory, and in turn each one of those pointers have to move.
Also keep in mind that clustered indexes in SQL Server immediately make the table a B-Tree structure (unless it's a column store index), and this is one of the biggest causes for fragmentation. As the rules of the B-Tree data structure could force pages to split and therefor change the entire structure of the B-Tree (it's like a cascading effect).

In order to avoid some fragmentation on insert, you should sort the data with a query before doing so. This post explains how it works and why.

Fun fact, a nonclustered index will point at something called an RID (Row Identifier) when no clustered index is present. This is then called a heap table.


How to return values from "upsert" query in case when UPDATE has been performed?

You need to use a second CTE for the INSERT:

WITH upsert AS (
  UPDATE tbl SET a = 2
  WHERE a = 1
  RETURNING tbl.*
), inserted AS (
  INSERT INTO tbl (a)
  SELECT 1 WHERE NOT EXISTS( SELECT * FROM upsert )
  RETURNING *
)
select *
from upsert
union all
select *
from inserted


MySQL service stops after trying to grant privileges to a user

I think your problem boils down to a misalignment of grants.

When you run this query

SELECT COUNT(1) column_count FROM information_schema.columns
WHERE table_schema='mysql' AND table_name='user';

You should the following number

If you get 43, MySQL 5.6
If you get 42, MySQL 5.5
If you get 39, MySQL 5.1
If you get 37, MySQL 5.0

It simply means you forgot one more upgrade step

# mysql_upgrade --upgrade-system-tables

This should fill in the missing columns.

I discussed this before

May 01, 2013 : Can I find out what version of MySQL from the data files?
Apr 24, 2014 : mysql: Restore All privileges to admin user (StackOverflow)

Since you said that mysql.user has 39, than means you did not run mysql_upgrade --upgrade-system-tables when you went from MySQL 5.1 to MySQL 5.5.

To be honest with you, I do not know if running mysql_upgrade --upgrade-system-tables in MySQL 5.6 will successfully realign mysql.user two versions back.

I would suggest making a copy by doing the following

net stop mysql
cd D:\MySQL\mysql
copy user.frm user1.frm
copy user.MYD user1.MYD
copy user.MYI user1.MYI
net start mysql

After making the copy, go ahead and run mysql_upgrade --upgrade-system-tables. Login to mysql and run SELECT COUNT(1) column_count FROM information_schema.columns WHERE table_schema='mysql' AND table_name='user'; again. Hopefully it will read 43.

If it does not, copy the user files back like this

net stop mysql
cd D:\MySQL\mysql
del user.*
move user1.frm user.frm
move user1.MYD user.MYD
move user1.MYI user.MYI
net start mysql

If you want to change the design manually, try the following:

Create mysql.user_new with MySQL 5.6 layout

CREATE TABLE mysql.user_new (
  `Host` char(60) COLLATE utf8_bin NOT NULL DEFAULT '',
  `User` char(16) COLLATE utf8_bin NOT NULL DEFAULT '',
  `Password` char(41) CHARACTER SET latin1 COLLATE latin1_bin NOT NULL DEFAULT '',
  `Select_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Insert_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Update_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Delete_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Drop_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Reload_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Shutdown_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Process_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `File_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Grant_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `References_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Index_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_db_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Super_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tmp_table_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Lock_tables_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Execute_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_slave_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Repl_client_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Show_view_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Alter_routine_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_user_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Event_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Trigger_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `Create_tablespace_priv` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  `ssl_type` enum('','ANY','X509','SPECIFIED') CHARACTER SET utf8 NOT NULL DEFAULT '',
  `ssl_cipher` blob NOT NULL,
  `x509_issuer` blob NOT NULL,
  `x509_subject` blob NOT NULL,
  `max_questions` int(11) unsigned NOT NULL DEFAULT '0',
  `max_updates` int(11) unsigned NOT NULL DEFAULT '0',
  `max_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `max_user_connections` int(11) unsigned NOT NULL DEFAULT '0',
  `plugin` char(64) COLLATE utf8_bin DEFAULT '',
  `authentication_string` text COLLATE utf8_bin,
  `password_expired` enum('N','Y') CHARACTER SET utf8 NOT NULL DEFAULT 'N',
  PRIMARY KEY (`Host`,`User`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_bin COMMENT='Users and global privileges';

Move the Grants from Old to New

INSERT INTO mysql.user_new
(Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections)
SELECT
Host,User,Password,Select_priv,Insert_priv,Update_priv,Delete_priv,
Create_priv,Drop_priv,Reload_priv,Shutdown_priv,Process_priv,File_priv,
Grant_priv,References_priv,Index_priv,Alter_priv,Show_db_priv,Super_priv,
Create_tmp_table_priv,Lock_tables_priv,Execute_priv,Repl_slave_priv,
Repl_client_priv,Create_view_priv,Show_view_priv,Create_routine_priv,
Alter_routine_priv,Create_user_priv,Event_priv,Trigger_priv,ssl_type,
ssl_cipher,x509_issuer,x509_subject,max_questions,
max_updates,max_connections,max_user_connections
FROM mysql.user;

Update the Create_tablespace_priv for root

UPDATE mysql.user_new SET Create_tablespace_priv='Y' WHERE user = 'root';

Flip the Switch

ALTER TABLE mysql.user RENAME mysql.user_old;
ALTER TABLE mysql.user_new RENAME mysql.user;
FLUSH PRIVILEGES;

Give it a Try and See if everything works !!!


Why Azure site recovery may not meet RPO?

Looking at Microsoft’s “About Azure Recovery”, we can see this:

RTO and RPO targetsKeep recovery time objectives (RTO) and recovery point objectives (RPO) within organizational limits. Site Recovery provides continuous replication for Azure VMs and VMware VMs, and replication frequency as low as 30 seconds for Hyper-V. You can reduce RTO further by integrating with Azure Traffic Manager.

Data “inside SQL Server” would be data that has not yet been replicated elsewhere, be it Azure, a replication distributor, or a local mirror. Losing 30 seconds of data for most organizations is a tolerable amount of failure, but any company that requires real-time loading and querying will be chomping at the bit about that 30-second gap between the time the primary instance went down and the replicated secondary in Azure picked up


Sort table by column with column ordered by earliest instance of another column

You can use a subquery / derived table to get the earliest timestamps and then join;

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
  JOIN
    ( SELECT foo, MIN(timestamp) AS min_timestamp
      FROM tbl
      GROUP BY foo
    ) AS m
  ON  t.foo = m.foo
ORDER BY
    m.min_timestamp,
    t.foo,
    t.bar ;

In MariaDB (and in MySQL version 8, when it is released), you can also use window functions:

SELECT t.foo, t.bar, t.timestamp
FROM tbl AS t
ORDER BY
    MIN(t.timestamp) OVER (PARTITION BY t.foo),
    t.foo,
    t.bar ;


InnoDB create table error: "Row size too large"

The answers to your questions are complex, because they vary by InnoDB file format.  Today, there are two formats, called Antelope and Barracuda.

The central tablespace file (ibdata1) is always in Antelope format.  If you use file-per-table, you can make the individual files use Barracuda format by setting innodb_file_format=Barracuda in my.cnf.

Basic points:

One 16KB page of InnoDB data must hold at least two rows of data.  Plus each page has a header and a footer containing page checksums and log sequence number and so on.  That's where you get your limit of a bit less than 8KB per row.
Fixed-size data types like INTEGER, DATE, FLOAT, CHAR are stored on this primary data page and count toward the row size limit.
Variable-sized data types like VARCHAR, TEXT, BLOB are stored on overflow pages, so they don't count fully toward the row size limit.  In Antelope, up to 768 bytes of such columns are stored on the primary data page in addition to being stored on the overflow page.  Barracuda supports a dynamic row format, so it may store only a 20-byte pointer on the primary data page.
Variable-size data types are also prefixed with 1 or more bytes to encode the length.  And InnoDB row format also has an array of field offsets.  So there's an internal structure more or less documented in their wiki. [EDIT] Dead link - here looks better now.

Barracuda also supports a ROW_FORMAT=COMPRESSED to gain further storage efficiency for overflow data.

I also have to comment that I've never seen a well-designed table exceed the row size limit.  It's a strong "code smell" that you're violating the repeating groups condition of First Normal Form.


Performance of a self join in a temporal table with a single day's difference between end_date and start_date

SELECT *
FROM self AS current
LEFT OUTER JOIN self AS previous
ON previous.employee_id = current.employee_id
AND DATEADD(d, 1, previous.end_date) = current.start_date

and previous.start_date <> current.start_date

or if has to be less

and previous.start_date <  current.start_date

or if can isolate on prior

and previous.start_date = LAG (current.start_date, 1, 0) OVER (PARTITION BY current.employee_id ORDER BY current.start_date DESC)


Database design - groups chat - seen messages

An alternative would be to not add those rows right when the message is created, but add a row whenever a user actually reads the message. Then you can omit the seen flag – the existence of the row is enough to know that the user saw the message.

That has two advantages:

fewer table rows
no extra work when a user joins a group

With your design, adding a user to a group requires adding a row for every message written by someone in the group.


How to detect performance issues in function working on temporary tables?

You can use the auto_explain extension to explain slow queries automatically from their native environment.  It will have no trouble seeing the temp tables.
You could manually load the extension if you are a superuser.  But this module is useful enough I just always put it (as well as pg_stat_statements) into shared_preload_libraries in postgresql.conf, that way it is always available.  The syntax for activating it in a specific client connection would be then be set auto_explain.log_min_duration = '150ms'; and set auto_explain.log_analyze=on;.  The output goes to the database server's log file.  You can also have it sent to the client, by doing set client_min_messages TO log;, but I don't know what C# will do with such messages once it receives them through that channel.


Does oracle client 12c requires Java on windows

Oracle Client - the "thick" one - has own JRE embedded, it is used to run GUI management tools. If you do not need it, download and install Oracle (thin) Instant client


What is the purpose of keeping filegroup or filestream backup in MSSQL?

So I tried to restore only filegroup of filestream to other database, but I get an error message

As Laughing Vergil originally mentioned in a comment, your question is answered in this great Stack Overflow post: Restore filegroup to different database

The gist of the answer is that you always have to start by restoring the database with the PRIMARY filegroup.  Then you can restore additional filegroups (like the filestream one) after that.  This called a "piecemeal restore" and is described in the documentation.

  Is it possible to restore only filestream to other database

No, as mentioned above it needs to be restored along with the PRIMARY filegroup.

  or restore only an old version of filestream to the current database of FILESTREAM?

Tibor responded to this part in a comment.  The basic answer is "no."  This is because of the way database recovery works - you can't restore different parts of the database that are from different points in time.

As a side note, there is a restriction specifically related to filestream filegroups:

  If a partial restore sequence excludes any FILESTREAM filegroup, point-in-time restore is not supported.


You can try to use the REPAIR TABLE myTable USE_FRM

"Probably" your .MYI file is missing or have corrupted headers

  This option tells MySQL not to trust the information in the .MYI file
  header and to re-create it using information from the .frm file

However before proceed check the documentation of your MySQL 5.1 version and make a backup copy of your .MYI,.frm, and .MYD file in order to avoid loss of data


We have had this answer back from AWS Support which shows where the bottleneck is occurring:

If you would like to reach 250MB/s, the maximum throughput of r3.4xlarge, you can change the volume to Provisioned IOPS(io1). IO1 has maximum throughput of 320 MiB/s. Another approach is to build RAID 0 array with 2 gp2 EBS volume. You can see the guidance from link [2].


How do i copy my mongodb data-files to mongodb server

With MMAPv1 you could copy f.ex. local.* files to some new mongod instance and instance saw those. With wiredTiger, you cannot do it anymore.


SQL Server 2019 running 100% on only 1-2 cores with MAXDOP 8 to 14

One row at a time is read in from flat file and converted info a format that ADO will accept to add/update into SQL Server.

You are going to have a very hard time leveraging multiple SQL Server cores with this approach.  Each single-row insert will be executed by one core, sequentially.  And each insert is very likely to keep using the same core, because each insert will be coming from the same connection.  From Microsoft SQL Server 2012 Internals by Kalen Delaney, et al:

Each SPID has a preferred scheduler, which is the one that most recently processed a request from the SPID.

If it's not a lot of trouble, you might get a "quick win" improvement in throughput by splitting the file into multiple files, and then having multiple instances of your C++ application loading each file concurrently.  Of course, you might run into other bottlenecks there, but it's worth a shot.
A side note: Dan Guzman mentioned:

The slowly degrading performance is symptomatic that the add/update query is performing a scan. Index/query tuning may avoid that and provide more linear and better performance overall.

If your table has additional indexes or foreign key / unique constraints, SQL Server will have to check more and more data as you load the table.  You could optimize to get seeks for these checks, or load an unindexed heap staging table first with your app, and then have an additional step that moves data to the real table using T-SQL.

Overall, to leverage more cores, you need to improve the concurrency of your loading application.  Either by using bulk loading APIs, or inserting from multiple clients concurrently. For more stable throughput, you'll need to avoid costly table scans, possibly by inserting into a staging table rather than your app table directly.


How to partially merge a row in Postgres?

You can simple use lag window function to get the value of the previous row and compare with the current one, then you use CASE to conditionally format as you please. Mapping the "merged" values as NULL (so it can work for any type), it would be:

SELECT
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.first_name
  END AS "Name",
  CASE
  WHEN customers.id = lag(customers.id) OVER w_ordered THEN
    NULL
  ELSE
    customers.last_name
  END AS "Last Name",
  items.name AS "Item",
  items.price AS "Price",
  COUNT(items.id) AS "Amount",
  SUM(items.price) AS "Total"
FROM
  items
INNER JOIN
  purchases ON items.id = purchases.item_id
INNER JOIN
    customers ON purchases.customer_id = customers.id
GROUP BY
  customers.id, items.id
WINDOW
  w_ordered AS (ORDER BY customers.last_name)
ORDER BY customers.last_name

Notice that I used the same ORDER BY clause in the window function as the full query, this is really important because you do want to verify the "merging" following the same rule.

Now, this is not a presentation mode, as in SQL you only have a table as a result, if you wish to do a real row merging in your presentation layer, you can use another column to just inform if it should merge the rows or not (although it shouldn't be hard to do the full logic there).


It depends from the distribution of your inserts if moves going to occur and how many.

1) you start with one chunk {minkey,maxkey} which lives on primary (lets say shard000)

2) as you insert data a split will occur. Might be

2.1) {minkey, US} {US,maxkey} -> moves the second chunk to shar001

2.2) {minkey, TR} {TR,maxkey} -> moves first chunk to shard001

2.3) {minkey,OTHER} {OTHER,maxkey} -> moves second shard to shard002

3) I cant analyze all paths but for 2.1)

{US,maxkey} is unmovable and all the chunks come from it as well

{minkey, US} depends if it going to move according to the next split

....


Can inserting new rows make the RDB not use an index?

First, let me say that you haven't listed the version of your RDBMS, so I'm going to assume you're running a version that utilizes the Cost-Based Optimizer (CBO), as opposed to the Rule-Based Optimizer. The CBO was introduced in the 10g series of databases, so I think this is a safe assumption. If you're still running a 9i or earlier version, my answer won't help you.

So your question is "will inserts stop my RDB from using an index?". The easy answer here is no, they won't. Your database will continue to use the index it used before, as a_horse_with_no_name mentioned in the comments that the rows will be maintained automatically in the index without any extra effort from you.

However, there is a chance that the volume of changes could impact the performance of using that index, which is something that you need to be aware of. How?

Take the total row count of all rows that could be included in an index (remember we cannot index nulls). Then consider the number of inserts you've done - is this number >= 10% of the existing rows? If the answer is yes, then your statistics are now considered out-of-date. Luckily, by default Oracle's later versions installs an overnight job (usually running around 10pm local server time) that will automatically gather statistics on any table & indexes where it senses a 10% or greater change in the count of rows.

But, if you are doing something outside of ordinary processing, say adding a significant amount of rows due to a one-time data load, you may very well need to gather stats yourself sooner than the automatic job. Also, in my experience, the automatic job only has a certain window of time in which to gather stats, so it's possible that it won't be able to gather stats on all objects if large amounts of data was manipulated across many objects.

My point here is that changing data volumes significantly in a short period of time can lead to performance problems not because your indexes will be bad, but because in a Cost-Based world, the Optimizer will have bad information, and will make bad decisions.

I bring this up because you seem focused on indexes, which are only one tool in a much larger toolkit to having a well performing database.

HTH.


Create an SQL file with the content that sets the desired linesize, e.g:

login.sql

set linesize 200

Then in SQL Developer, go to Tools/Preferences/Database, and set this file at Filename for connection startup script.


"Table or view does not exist" on synonym

Synonyms have nothing to do with privileges.  They are simply a way to simplify naming.

The error you are getting appears to indicate that user_app does not have privileges on the user.clients table.  You'd need to grant that

GRANT SELECT, INSERT, UPDATE, DELETE
   ON user.clients
   TO user_app;

Of course, in your actual system, I'm guessing that there is a role that you would grant privileges to and that role would be granted to user_app.


SQL Server transaction replication ignores DML which does not change data

Tranactional Replication works by reading the transaction log.

This kind of "non updating update" often won't generate any transaction log records.

See Paul White's article The Impact of Non-Updating Updates for more about this.


Here is a more concise way to write this query without conditionals (SQLfiddle demo, but I guessed on the data type):

SELECT
  PMT_AMOUNT = -1 * CONVERT(NUMERIC(20,2), STUFF(o.PMT_AMOUNT,LEN(o.PMT_AMOUNT)-1,0,'.'))
FROM dbo.OPS_PMT_RECON_CONV AS o;

Or even simpler, as @ypercube suggests:

SELECT
  PMT_AMOUNT = -0.01 * CONVERT(NUMERIC(20,2), o.PMT_AMOUNT)
FROM dbo.OPS_PMT_RECON_CONV AS o;

A couple of suggestions:

Store the number in the database, instead of this awkward string representation. What is the point of persisting the number this way, even if you can't control how it comes to you? Converting to a number first, and storing it that way, will prevent you from storing junk in your database and end users getting burned for it.
Specify both precision and scale when defining numeric. This post is primarily about varchar, but applies equally to other types that accept parameters.
Always specify the schema.
Always terminate statements with semi-colons.


Try this command to SKIP the offline and corrupted datafiles:

BACKUP DATABASE
    SKIP INACCESSIBLE
    SKIP OFFLINE;

  SKIP INACCESSIBLE: Inaccessible datafiles. A datafile is only considered inaccessible if it cannot be read. Some offline datafiles can still be read because they exist on disk. Others have been deleted or moved and so cannot be
  read, making them inaccessible.

  SKIP OFFLINE: skipping Offline datafiles;

Details: https://docs.oracle.com/cd/B12037_01/server.101/b10734/rcmbackp.htm


ORA-00937 when calculating a sub-value in an aggregate select statement

Your additional calculated value:

(select count(t1.num) from T t1)

Is a scalar subquery, which is a dynamic rather than static expression.  As such it's treated the same as a column as far as the aggregate is concerned and needs to be included in the group by clause to avoid the ORA-00937: not a single-group group function error

However, oracle does not allow subqueries as part of the group by clause and trying to include the scalar subquery and/or the whole case statement:

group by (case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

just results in an ORA-22818: subquery expressions not allowed here error.

The only ways around this are to either convert your scalar subquery to an aggregate value like so:

max(case (select count(*) cnt from t t1) when 0 then 1 else 0 end)

or

(case max((select count(*) cnt from t t1)) when 0 then 1 else 0 end)

or rewrite your query to move the unaggregated scalar subquery out of the aggregated query:

select (case (select count(*) cnt from t t1) when 0 then 1 else 0 end) * sum
  from (select sum(t3.num) sum from t t3) t2;

or precompute your scalar subquery so it can be used in the group by clause:

select case t1.cnt when 0 then 1 else 0 end * sum(t2.num)
  from t t2
     , (select count(*) cnt from t) t1
 group by case t1.cnt when 0 then 1 else 0 end


SSMS 2016, Availability Groups, database is encrypted error

Problem with this message is that none of the databases use any encryption.

That is probably true :) However, that's not what the error is saying...

  "This database is encrypted by database master key, you need to provide valid password when adding it to the availability group."

The error is saying, in plain English: You have a databases that has, inside of it, a database master key. That database master key is encrypted using a password. I need you to give me that password so that I can decrypt any objects that might be encrypted using it.

Here is a super quick repro to give you the "message":

CREATE DATABASE PasswordTest;
GO

USE PasswordTest;
GO

CREATE MASTER KEY ENCRYPTION BY PASSWORD = ‘$up3r$tr0ng’
GO

SMSS isn't saying it can't use the database, it's asking for the password to open the master key.

What's the fix?

You have a few options, but here are the best two:

Click to the right of the message, you can actually put in the password and the wizard will let you continue without issue (since it can now open the master key).
If you're not using the master key... Drop It.

  It turned out that database did have master key.

Yes, as the error specified. This was not in doubt :)

  screenshots in my initial question are from the very same 2012 instance. Why SSMS 2012 and 2014 wizard did not mention anything about master key, but SSMS 2016 wizard did?

It was added in SSMS 2016... Not sure what you were looking for here as items are added to new versions.

  And also SSMS 2016 did not have any edit box to enter password.

I dunno - works in mine...

  I know exactly what you refer to, but there is nothing there in my case, just empty space under "Password" column, which added to confusion. Do you think there might be a bug in SSMS 2016 or you have some explanation for this behavior?

See the screenshot above - I think it's working as intended, at least for me... It might not be intuitive, but it works.

The end result is the same. If you're not a fan of the GUI giving you grief then there is always PowerShell and T-SQL :) If you feel that this isn't working as intended or you don't like it, I'd ask you to post feedback on Connect.


Deduplicate SELECT statements in relational division

I see a possible minor simplification with a CTE (available in Redshift) for the repeated subquery with identical predicates:

WITH p2 AS (
   SELECT event_id
   FROM   params
   WHERE  key = 'utm_source' AND value = 'facebook'
   )
SELECT e.visitor_id, '1'::text AS filter
FROM          p2
JOIN   params p1 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign' AND p1.value = 'campaign_one'
GROUP  BY e.visitor_id

UNION ALL
SELECT e.visitor_id, '2'::text AS filter
FROM          p2
JOIN   params p1 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign' AND p1.value = 'campaign_two'
GROUP  BY e.visitor_id;

Plain joins may also be faster than multiple IN semi-joins.

This multicolumn index should allow index-only scans on params:

CREATE INDEX foo_idx ON params (key, value, event_id)

Add another index on just (event_id) if you don't have one, yet.

Arsenal of available query techniques for relational division under this related question on SO:

How to filter SQL results in a has-many-through relation

Avoid multiple SELECTs

As commented by @Andriy, we can squeeze out some more:

WITH p2 AS (  -- repeated, immutable filter
   SELECT event_id
   FROM   params
   WHERE  key = 'utm_source' AND value = 'facebook'
   )
, p3 (value, filter) AS (  -- values for variable filter
   SELECT text 'campaign_one', text '1'
   UNION ALL SELECT 'campaign_two', '2'
   )
SELECT e.visitor_id, p3.filter
FROM          p3
JOIN   params p1 USING (value)
JOIN          p2 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign'  -- repeated for p1
GROUP  BY 1, 2;

In Postgres we could use a shorter, faster VALUES expression, but that feature is not currently supported in Redshift:

...
, p3 (value, filter) AS (
     VALUES
     (text 'campaign_one', text '1')
   , (     'campaign_two',      '2')
   )
...

For just two SELECT in the UNION, this won't buy much. But it should be a substantial improvement for more - like you mentioned.

2nd query without CTE

The CTE is not necessary for the second query. You can simplify to:

SELECT e.visitor_id, p3.filter
FROM  (
   SELECT text 'campaign_one' AS value, text '1' AS filter
   UNION ALL SELECT 'campaign_two', '2'
   ) p3                            -- values for variable filter
JOIN   params p1 USING (value)
JOIN   params p2 USING (event_id)
JOIN   events e ON e.id = p2.event_id
WHERE  p1.key = 'utm_campaign'     -- repeated, immutable filters
AND    p2.key = 'utm_source'
AND    p2.value = 'facebook'
GROUP  BY 1, 2;

About CTEs

Resources for Common Table Expressions (as requested in the comment):

The manual.
Tag info for CTE.

Data-modifying CTEs are particularly useful. Example:

How do I insert a row which contains a foreign key?

Basics with an added advanced example on this related answer on SO:

Are SELECT type queries the only type that can be nested?


Add a column to a table and then "insert into" is invalid in a stored procedure

You cannot add a column and use it in the same batch; in Management Studio you are able to insert "go" between them so it works, in stored procedure you can insert your table within EXEC like this:

exec('insert table..(old_cols, new_col)...')

The table structure is checked if a table exists so the procedure cannot be compiled when you use a new column, but it will not check a dynamic code that compiles on its own and when it will be compiled the column will be already there


This will be tough to do with the built-in schedule UI, unless you want to create 12 different schedules, but you can easily build this into your job step. Assuming it runs every night:

IF DAY(GETDATE()) <= 8 OR DAY(GETDATE()) >= 28
BEGIN
  -- do backups
END

On other days (9th -> 27th), the job will still run, but everything inside the IF conditional will not. So other than having an entry in sysjobhistory, it will be as if it didn't run at all.

Another idea would be to have a different job that runs every morning, checks the date in a similar way, then enables / disables the backup job accordingly. Or similarly with two schedules (one on the 9th to disable, and one on the 28th to enable).


Insert with different key values to avoid contraint violation

I'd think it's probably not a best practice to use EXCEPTION to do logic/handling, such as duplicate keys.  Here's an idea: write a stored proc.  Also create a sequence that has your alternate values that will be used in cases of duplicate keys.  You might want to start this sequence with a fairly high value like 1,000,000 (or much higher, just depends on your actual data).  Then do your inserts via this stored proc whenever you want the dupe-sub functionality.

CREATE TABLE components (
    id_component INTEGER PRIMARY KEY
  , code         VARCHAR2(255)
  , description  VARCHAR2(255)
  , model        VARCHAR2(255)
  , resp         VARCHAR2(255)
);

CREATE SEQUENCE components_alt_id_seq
  START WITH 1000000;

CREATE OR REPLACE PROCEDURE component_ins
  (  p_id_component IN components.id_component%TYPE
   , p_code         IN components.code%TYPE
   , p_description  IN components.description%TYPE
   , p_model        IN components.model%TYPE
   , p_resp         IN components.resp%TYPE )
AS
  v_is_duplicate INTEGER;
BEGIN
  SELECT count(*) INTO v_is_duplicate FROM components WHERE id_component = p_id_component;
  IF v_duplicate = 0 THEN
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (p_id_component, p_code, p_description, p_model, p_resp);
  ELSE
    INSERT INTO components (id_component, code, description, model, resp)
      VALUES (components_alt_id_seq.NEXTVAL, p_code, p_description, p_model, p_resp);
  END IF;
END;
/

EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39823, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39824, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (39822, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40015, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')
EXEC component_ins (40016, '101087632', 'COMPONENT TEST', 'TEST', 'ADMIN')

And then checked the result. Notice that the duplicate input values were automatically subbed with a value taken from the sequence.

SELECT id_component FROM components;

ID_COMPONENT
------------
       39822
       39823
       39824
       40015
       40016
     1000005
     1000006

7 rows selected.


Does innodb guarantee unique data in unique indexes even with UNIQUE_CHECKS=0?

Normally, index changes are put into the "change buffer" for later writing to disk.  This happens for non-unique indexes.  (This also applies to UPDATEs that change a UNIQUE index, and DELETEs, which necessarily remove a 'row' from the index.)

For UNIQUE indexes (and UNIQUE_CHECKS=1), each row being inserted is immediately verified as not being a dup.  That way, the row can be rejected quickly.  The drawback is that it must fetch the index block that contains the value in question.  For huge tables, this may involve a read from disk, which can be relatively slow.  And that disk read is blocking, so the INSERT must wait.

For a batch load of rows, it would be faster to load all the rows, populate the change buffer, and postpone having to actually look at the disk index.

UNIQUE_CHECKS was implemented in the days before fast SSDs and when RAM was small.  That is, it used to be an important optimization.

The "Change Buffer" is a portion of the buffer_pool (default: 25%).  It holds secondary index changes awaiting consolidation and insertion into the disk copy of the index.

When an index lookup is needed, it must check both the change buffer and the real index.

The real index is a bunch of 16KB blocks in a B+Tree organization based on the secondary index.  Those blocks are cached (one-by-one, as needed) in the other 75% of the buffer_pool.

With UNIQUE_CHECKS=ON, the uniqueness test can be immediate; with it off, I suspect that the test is not done until either the bulk INSERT is finished, or the change buffer needs some flushing.

The change buffer is written to disk in the background.  Note:  This is one explanation for I/O when no queries are running.  Logically, the CB is sorted; a block from disk is read; entries are merged into that block; then the block is left in the buffer_pool as "dirty".  Eventually, another thread worries about writing dirty blocks to disk (another cause for strange I/O).


"The Data Model Resource Book", Volume 1, has a whole chapter on dealing with contacts and solves those kinds quite extensively. As does any CRM btw.

Basically:

Sorry, the userRole approach sucks - it is widely accepted - by anyone with experience as a mistake. So, your staff NEVER EVER BUY? Nice products. Roles and entity information are separate.
There are obviously relationships between entities - a is staff of b (from time to time, btw.). This is a m:n relationship and requires a second table.

Btw., I really hope you do not plan to store the password in the password field - beginner mistake, violation if basic security principles. Never ever store a password, only a salted hash - hashed many times. Read up on that topic.


Adding an index very slow...is there a mysql cmd to get an ETA or show progress?

When mysql alters a table, it essentially makes a copy of of it and then swaps the copy in. This way if you cancel the update in the middle, the table is still in a stable state. So you can look in the mysql data directory (/var/lib/mysql/ ?) to see how large the new file is, that will tell you how far along it is. This is a bit more difficult with Innodb, but there is a tmp table being created somewhere.

You can significantly reduce the amount of a time an index takes by increasing your sort buffer variables (myisam_sort_buffer_size, sort_buffer_size). Make those as large as you can with memory you have. You could cut a few days off of the modification time, even get it down to a few hours depending on how much memory you have. I've done a 150M record table in about 3 hours.


Which full backup will be differential one based on?

Will be based off your most recent full backup, a differential backup is simply a backup of changed pages since your last full backup. Hope that helps.


No, you cannot reliably use VMDK files for shared storage in VMware-virtualized Windows Server Failover Clustering.  RDMs (Raw device mappings) are the only supported method of provisioning disks in this scenario. Use RDMs in physical compatibility mode (pass-through RDM). You cannot use virtual disks or RDMs in virtual compatibility mode (non-pass-through RDM) for shared storage.  You can use VMDKs for the non-shared drives.  See Setup for Failover Clustering and Microsoft Cluster Service from VMware for more details.

Since you have Veeam, you should use the SQL Server backup tools included with that package; see Veeam's help center for vSphere backup and restore for more details.


Using floats for PKs. Is it a good idea?

To answer your question directly: No. Using floats for a primary key is not a good idea. Why? Because floats can run into problems with rounding and precision (Is 1.20 the same as 1.2? Because mathematically it is.)

Personally I would create a primary key on a single field. Preferably an integer. Integers are typically more efficient than floats and there is no ambiguity about interpretation as with floats and strings.

The most important function of a primary key is to uniquely identify a single row in a table. It doesn't have to have any meaning or purpose beyond that. If it serves another purpose thats a bonus.

In your case, (as your title implies)  I wouldn't use the distance as the primary key (on its own) because it may be possible that 2 combinations of stores are the same distance apart.

You could use it as part of a compound key. But in this example it doesn't make sense unless you wanted to store multiple distances between the same 2 stores.

To go a bit further: This looks to me like a spatial query issue. If you store an XY coordinate with each store, you can then do things like a radius search. "Give me a list of pizza shops with 1 mile, 2, 5 , 10 miles" etc. Or even better from a customer address you could determine which shop is closest.

If a new store is added it would automatically be included in the result set without having to manually calculate the distances. You could also calculate the distances between every shop.

The only shortcoming of this approach i can think of is that the distances are straight line and may not accurately reflect the distance you may have to travel between shops by road.

Depending upon what you are trying to achieve it may be useful to store the distance rather than recalculate it, but i personally prefer to derive values as needed.


Add Active Directory User For Azure-SQL-DB

When provisioning users from external Azure Active Directory instances that are federated with your Azure subscription, you need to use the underlying "guest" email address created for the Azure subscription, not the "actual" email address.

i.e. The Microsoft scenario mentioned here as "Imported members from other Azure AD’s who are native or federated domain members".

So, instead of:

CREATE USER [your.user@example.com] FROM EXTERNAL PROVIDER

One needs to use the following convention. This is Microsoft's way of storing a guest / federated user from another Azure active Directory.

CREATE USER [your.user_example.com#EXT#@<yourAzureSubscriptionPrefix>.onmicrosoft.com] FROM EXTERNAL PROVIDER

Alternatively, using groups makes this far more intuitive and manageable.

Create a group (say SqlUsersFromExternalDirectory) in the Azure subscription's default Azure Active Directory.
Add the external users you want to access the SQL Warehouse or DB to to the group.
Add the group as an external user in the target database

CREATE USER [SqlUsersFromExternalDirectory] FROM EXTERNAL PROVIDER

This works fine, the external users can then sign in, admins can GRANT permissions etc, etc.


Postgres 9.6.6 on Ubuntu 16.04, lots of errors

Having talked about this on freenode (thanks Andrew!) I modified two of the default settings in postgresql.conf, and that made the issues go away without causing other ill effects.

autovacuum_vacuum_cost_delay = 0ms
deadlock_timeout = 5s

I think the consensus is still that pscheduler's attempts to get table locks so frequently is poor design, but the workaround above seems to be doing the trick for the time being.


Will an AD Account inherit all the User Mappings to databases that it's parent AD Group is mapped to?

Yes, a Windows account will have all of the permissions that are assigned directly to the login as well as any permissions that are assigned to any groups the account is a member of.

Permissions are cumulative, but DENYs will override.  It would be difficult or impossible to accommodate some security needs if they were not.

See Effective SQL Server permissions when user is in several AD groups.  The same principals apply to permissions assigned to a user and to a group they are a member of.

In general, DBAs that have been around a while will advise you to never start giving permissions to individual user accounts, as you can then end up with a long list of logins, and when employees leave you have to go clean them up.  The preferred way is to only assign permissions to groups, and then when an employee leaves, or is hired, the DBA doesn't have to do anything as the request for access is granted by whoever manages the AD groups.


SQL equivalent of the "relocate all files to folder" option?

There is no shortcut, no. The backup contains the original path to each and every file - a restore will try to put each file in its original location, unless you override it explicitly, and you have to do that for each file using WITH MOVE.


Replace special characters in a column with space

If you are guaranteed to only ever use the 26 letters of the US English alphabet (both upper-case and lower-case versions) then sure, you can get away with using LIKE and/or PATINDEX with the simple range notation of [a-z] (you wouldn't need to use an upper-case "Z" when using a case-insensitive Collation).

But, if you might get characters not found in the en-US alphabet yet available in various Code Pages / Collations for VARCHAR data (e.g. Þ = Latin capital "Thorn" = SELECT CHAR(0xDE)), then you might need to include those in the character class: [a-z0-9, Þ]. Of course, what those extra characters would be is on a per-Code Page basis.

Also, please be aware that both Collation type (SQL Server vs Windows) and sensitivity settings (case, accent, etc sensitive vs insensitive) will affect which characters are included in a particular range. For example, the SQL Server Collations sort upper-case and lower-case letters in the opposite order as the Windows Collations. Meaning, assuming a case-sensitive Collation for both types of Collations, one will do AaBb... and the other will do aAbB.... The effect will be that a will be within the range of A-Z for one of them, but not the other. And the range of a-Z won't match any characters in a binary Collation (one ending in either _BIN or _BIN2, but don't use _BIN) given that the value of A is 65 and a is 97, hence it is an invalid range of 97 through 65 ;-). There are far too many variations to give examples for here so I will try to post a detailed explanation on my blog sometime soon (and then will update this with the link to it). However, if you are going to be strict about only accepting US English characters (even if you might get valid letters from other languages) then your best option will probably be to use the following pattern and Collation:

LIKE '%[^A-Za-z0-9, ]%' COLLATE Latin1_General_100_BIN2

Now, if you are supporting NVARCHAR data and can get "word" characters from various languages, then T-SQL will not be of much help as it has no real way to differentiate these things. In this case, you should use a Regular Expression (RegEx) -- specifically the Replace method / function -- and those are only available through SQLCLR. The following shows an example of replacing several "special" characters, yet leaving all that are valid letters in at least one language:

DECLARE @Test NVARCHAR(500);
SET @Test = N'this$is%a<>TEST,;to}⌡↕strip╞╟╚══¶out_ç_ƒ▀ special-ĳ-೫-chars-舛-დ-א-B';
SELECT SQL#.RegEx_Replace4k(@Test, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL);

Returns:

this is a  TEST, to   strip      out ç ƒ  special ĳ ೫ chars 舛 დ א B

The RegEx expression means:

\W = a RegEx "escape" meaning "any non-word character"
\p{Pc} = a Unicode "category" of "Punctuation, Connector" (this is needed for the match only because this "category" is specifically excluded by the \W escape)
-[,] = class subtraction (this is needed to exclude commas from matching as "special" since they are included in the \W escape)

You can do an update of a table simply by issuing:

UPDATE tbl
SET    tbl.field = SQL#.RegEx_Replace4k(tbl.field, N'[\W\p{Pc}-[,]]', N' ', -1, 1, NULL)
FROM   tbl
WHERE  SQL#.RegEx_IsMatch4k(tbl.field, N'[\W\p{Pc}-[,]]', 1, NULL) = 1;

Please note that for these examples, I used two functions available in the Free version SQL# library of SQLCLR functions, which I created (but again, these are free). Also note that I used the "4k" versions which are faster due to using NVARCHAR(4000) instead of NVARCHAR(MAX) parameter types. If your data is using NVARCHAR(MAX), then just remove the "4k" from the function names.

Please also see:

RegEx Character classes


60GB in version store in short amount of time, allow snapshot isolation is disabled

What other types of activity could use so much version store so fast?

If the instance was acting as a readable secondary replica for an availability group, then snapshot will be used for read queries against that database - regardless of the settings or specific isolation levels requested.  If there are a lot of data changes on the primary for that AG database, or really long running transactions, then the version store can grow very quickly.

Note that this could be a different database and AG from the one where performance issues were occurring - since prior to SQL Server 2019, all user databases share the same version store in tempdb.

Regarding the waits you encountered, one thing I learned recently that's not very intuitive is that hash spills to tempdb incur the SLEEP_TASK wait type.  This shouldn't cause the version store to grow, but it lines up with the disk issues you mentioned with tempdb (disk queue being 1,000, etc).


Does Postgres ever require a naked field without single quotes?

With overloaded functions (same name but different arguments), there can be cases where they mean different things. But you should probably avoid creating such cases.

create function j(int) returns int language SQL as $$ select 1-$1 $$;
create function j(text) returns int language SQL as $$ select 7-$1::int $$;
select j('5');
 2
select j(5);
 -4


jdbc: does setAutoCommit(true) commits past executions?

From the Connection.setAutoCommit docs:

  NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.

But I don't think it's very readable/obvious in your code. You should probably simply commit before switching back to autocommit mode. Makes the intent clear.


There would be marginal difference if any between both the queries, however, the second query is much simpler, so if it were me, I would prefer the second one!

Another way to do this would be:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
WHERE events.user_id = 1

Oh, and if you want to get the results of all user collectively, this query would help:

SELECT MAX(events.id), events.user_id, events.event_name
FROM events
GROUP by events.user_id

The output would be something like:

-------------------------------------
MAX(events.id) | user_id | event_name
-------------------------------------
       5       |    1    |    A
       6       |    2    |    F
       4       |    3    |    C


Postgres: Find previous and next row or existing value created by SERIAL colum for separate query

Seeing the loop in pseudo-code, it seems like you want a SQL cursor.
it's instantiated with DECLARE associated to a query with an ORDER BY if needed, and you can use MOVE to move forward or backward, and FETCH to fetch the current row, or several rows, and finally CLOSE when you're done.
All these are SQL statements and independent from any client-side language.


Should I combine these tables into one, or keep them seperate?

I do think you need two tables - there are things in thread that apply to the entire conversation, not just the initial "question". It makes a lot of sense to have everything that represents a user-entered message of some sort stored in one place. There may be some differences in formatting for the initial post vs. follow-ups, but presumably most things that would apply to rows in post would also apply to parallel fields in thread.

I would, however, look at eliminating redundancy between thread and post.

A thread must have a title, and can have a body; all follow ups do not have individual titles, but must have a body. You have multiple options here; a couple are:

If only a title is provided for the starting post, store that in post.body; if a separate title and body are provided, store the title in thread.title, and the body in post.body.
Allow for a NULL or empty body if and only if post.parent_id is NULL.

I'll assume that upvotes, dnvotes, replies, and views are basically counts. If those are distinct between a thread and the posts that follow-up to the thread, then the only question is should we track these numbers for both the thread and the initial post.

While it's a little trickier to set up, I would actually be inclined to put the post_id of the initial post in the thread table. It should be true (I expect) that the post row with the desired thread_id and where parent_id is NULL should be the initial post. However, if users are allowed to delete posts, and if one user's follow-up to the initial post can be the parent for another user's post, then to delete a post with children, you'd have to set the children's parent_ids to the parent_id of the deleted post; you wouldn't have the option to leave the reply posts in the thread without having a parent_id.

If the initial post's ID is part of thread, then fields like upvotes and dnvotes would not exist at the thread level - the thread would simply pass on whatever values were tied to that initial post row.


Converting to MyISAM, `foreign_key_checks = 0` Not Working

Here's a method you can use without dumping all your InnoDB tables.

You will have to drop the foreign key constraints before you can alter the tables. You can generate the drop statements using the INFORMATION_SCHEMA this way:

mysql> SELECT CONCAT('ALTER TABLE `', table_schema, '`.`', table_name,
    '` DROP FOREIGN KEY `', constraint_name, '`;') AS _sql
  FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
  WHERE referenced_table_schema = 'mydb' -- replace with your database name
    AND referenced_table_name = 'catalog_category_entity'
    AND ordinal_position = 1;

The output should be one or more a fully-formed DDL statements. Run the statements, and then you can change the storage engine for your parent table.


Failed to allocate BUFs during DBCC CHECKDB

While I can't tell from the OLDSERVER data, it looks like NEWSERVER has at least 2 NUMA Nodes whereas I'm not sure if OLDSERVER has multiple NUMA Nodes or a single node.
Assuming, for the moment, that the OLDERSERVER only had a single NUMA Node, it does seem you're hitting a known issue which was fixed in SQL Server 2016 SP2 CU5, whereas you're currently on SQL server 2016 SP2 CU3.

An "Out of Memory" error can occur when a Database Node Memory (KB) becomes less than 2 percent of the target size, and it cannot discard database pages on the node anymore to get free pages.

If you look at the MEMORYSTATUS output, it does fall into this with about 1.59% of pages available, at least on NUMA Node 0.
I'd apply, at a minimum, SP2 CU5 and run the load again.


Orphaned users with their respective database name

A very elaborate way around your problem if you have to use sp_change_users_login

By using a cursor and inserting into the temp table inside the stored proc

use master;
GO

ALTER PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000) ;
DECLARE @dbname nvarchar(500);

CREATE TABLE #temptable (
    ID INT IDENTITY(1,1),
    [Dbname] nvarchar(500),
    [user] nvarchar(1000),
    [User_sid] sql_variant
);
DECLARE c CURSOR LOCAL FAST_FORWARD FOR select name from sys.databases where database_id > 4 and state_desc = 'ONLINE';
OPEN C
FETCH NEXT FROM C INTO @dbname;
WHILE @@FETCH_STATUS =0
    BEGIN
    SET @command = '
    use '+QUOTENAME(@dbname)+'

    EXEC sp_change_users_login ''Report'' '
    INSERT INTO #temptable([user],[User_sid])
    EXEC(@Command);
    IF @@ROWCOUNT >0
    BEGIN
    ;WITH CTE  AS
    (
        SELECT TOP(1) * FROM #temptable
        ORDER BY ID DESC
        )
        UPDATE CTE
        SET [Dbname] = @dbname;
    END
    FETCH NEXT FROM C INTO @dbname;
    END
SELECT * FROM #temptable;
END

I would advise against placing procedures / data in the master datatabase if you don't need to.

Running the proc

EXEC orphaned_u

Returns my two orphaned users

ID  Dbname       user   User_sid
1   my_test      test   0x4B0387CBECE23440B207E17B9F01160F
2   my_test_old  test2  0xF6F3F720927731449B6A9409F1248743


There are various high availability technologies.
See the following and resources below:

Replication is a set of technologies for copying and distributing data and database objects from one database to another and then synchronizing between databases to maintain consistency. Use replication to distribute data to different locations and to remote or mobile users over local and wide area networks, dial-up connections, wireless connections, and the Internet.

Transactional replication is typically used in server-to-server scenarios that require high throughput, including: improving scalability and availability; data warehousing and reporting; integrating data from multiple sites; integrating heterogeneous data; and offloading batch processing. Merge replication is primarily designed for mobile applications or distributed server applications that have possible data conflicts. Common scenarios include: exchanging data with mobile users; consumer point of sale (POS) applications; and integration of data from multiple sites. Snapshot replication is used to provide the initial data set for transactional and merge replication; it can also be used when complete refreshes of data are appropriate. With these three types of replication, SQL Server provides a powerful and flexible system for synchronizing data across your enterprise. Replication to SQLCE 3.5 and SQLCE 4.0 is supported on both Windows Server 2012 and Windows 8.

Log shipping
Like Always On availability groups and database mirroring, log shipping operates at the database level. You can use log shipping to maintain one or more warm standby databases (referred to as secondary databases) for a single production database that is referred to as the primary database. For more information about log shipping, see About Log Shipping (SQL Server).

Database mirroring. Note! This feature is in maintenance mode and may be removed in a future version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature. We recommend that you use Always On availability groups instead.
Database mirroring is a solution to increase database availability by supporting almost instantaneous failover. Database mirroring can be used to maintain a single standby database, or mirror database, for a corresponding production database that is referred to as the principal database. For more information, see Database Mirroring (SQL Server).

Backup/Restore:

The SQL Server backup and restore component provides an essential safeguard for protecting critical data stored in your SQL Server databases. To minimize the risk of catastrophic data loss, you need to back up your databases to preserve modifications to your data on a regular basis. A well-planned backup and restore strategy helps protect databases against data loss caused by a variety of failures. Test your strategy by restoring a set of backups and then recovering your database to prepare you to respond effectively to a disaster.

https://docs.microsoft.com/en-us/sql/relational-databases/replication/sql-server-replication?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/sql-server/failover-clusters/high-availability-solutions-sql-server?view=sql-server-2017

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/back-up-and-restore-of-sql-server-databases?view=sql-server-2017


The query you want is the following

SELECT headline,SUM(number) as sum_number
FROM table WHERE user = '$login_session'
GROUP BY headline;

You can make SQL echo the result by putting that query in a subquery

SELECT
    CONCAT('$login_session has ',headline,' ',sum_number,'</BR>') as result
FROM
(
    SELECT headline,SUM(number) as sum_number
    FROM table WHERE user = '$login_session'
    GROUP BY headline
) A;


Best way to transfer a few tables from reserve server to production

I think linked server and MERGE would be the best way to do it.  Set it to run at the appropriate interval and go for it.  Take care with the merge statement so that it properly detects when to do an update or not.  But since they are on the same subnet, it should perform adequately.

But linked servers have their own share of risks as well, make sure you set them up correctly (pass through security, etc.).

If you already have SSIS configured in your environment, that may be the more manageable approach.


Restrict insertion of new row if a row exists with certain field values

Answer left in a comment by matigo:
Two ways to do this would be through a BEFORE INSERT trigger or via a stored procedure. With a trigger, you can be sure that every application that writes directly to the database must follow the prescribed rules. With a stored procedure you can (potentially) simplify the entire process if there are multiple steps that must be followed in succession.


Installation of Microsoft SQL Server Data Tools Hangs

I experienced exactly the same error, right down to the pixel.  I figured out the solution by continuing the broken installation using the standard VS 2017 installer (it will recognize that the SSDT installer error'd out).  Resuming this installer reported more information via error logs.  The errors were folder/file access permissions being restricted by McAfee Anti-virus

tldr; disable your anti-virus.


MySQL: How to avoid case sensitivity in SQL queries when migrate from Windows to Linux?

In MySQL the commands themselves are not case sensitive, however depending on the OS, file structure variables might be case sensitive.

Check if your OS is case sensitive, and if this is the case, find out the correct case for the tables, and make sure your query is consistent in naming.
Don't forget though that you'll have to rewrite a lot of the queries to make sure they work in MySQL.

References:

MySQL docs: Identifier Case Sensitivity


Inserting in PostgreSQL Foreign Table Violates Primary Key Constraint10

Can you ignore ID field in the Foreign_Table script and try inserting? It should use the current serial value in the Foreign Table.

{
CREATE FOREIGN TABLE Foreign_Table (name varchar) server
    some_server options(schema_name 'public', table_name 'Actual_Table')
}


How can I optimize my mysql setup to create my index faster?

For starters, I would not touch the buffer sizes just yet. The sizes youhave in the question are monstrously too big.

Here is another observation: You have BLOB data. Ouch, your temp table is going to eat space rather quickly. You could do somehting like this:

Create a 32GB RAM Disk called /var/tmpfs by adding this line to /etc/fstab

none                    /var/tmpfs              tmpfs   defaults,size=32g        1 2

Next, create a folder called /mysqltmp and mount the RAM disk on it

mkdir /mysqltmp
chown mysql:mysql /mysqltmp
mount /mysqltmp /var/tmpfs

Add this to my.cnf and restart mysql

[mysqld]
tmpdir=/mysqltmp

Now, any tmp table made via DDL lands in the RAM disk.

Here is yet another observation: Why not create a separate table that keeps the BLOB data away from the unique names?

CREATE TABLE `data_store_name` SELECT id,uniqname FROM `data_store` WHERE 1=2;
ALTER TABLE `data_store_name` ADD PRIMARY KEY (id);
ALTER TABLE `data_store_name` ADD UNIQUE KEY (uniqname);
ALTER TABLE `data_store_name` ADD INDEX name_id_ndx (uniqname,id);
INSERT INTO `data_store_name` SELECT id,uniqname FROM `data_store`;

This will prevent any moving around of BLOB data when indexing.

From here, you would have to always join data_store using its name like this:

SELECT
    A.uniqname,B.data
FROM
    (SELECT * FROM data_store_name WHERE uniqname = 'mydataname') A
    LEFT JOIN
    data_store B USING (id)
;

Making these changes will sidestep this whole mess of dealing with keycache, RAM disks,  and tmp tables.

Give it a Try !!!


As mysqld spawns a new thread per connected slave to dump binary logs, I do not think adding a slave would have significant impact on node's performance.


Too many columns, how to reduce it? User events, ratings, scores storing for each lesson

You should turn each of those repeated columns into a separate row. Instead of a table design

QuizId
Item1
Item2
...
Item500

The table should be

QuizId
ItemNumber   -- values are 1 through 500
ItemValue

Then instead of having hundreds of columns there will be thousands, or even millions, of rows. That's fine. RDBMS is designed to handled that many without concern.


I do not know what you mean by the "Microsoft" way, but Exadata uses ASM.

Administering Oracle ASM Disk Groups on Oracle Exadata Storage Servers

http://www.oracle.com/technetwork/database/availability/maa-exadata-upgrade-asm-2339100.pdf

ASM is not specific to Exadata or any hardware, you can use it on any supported platform, even on Windows, so you handle database files just as in any other environment with ASM.


Why is my @@Version unchanged after upgrading?

I found the problem: The installer I had was just for SQL Server Management Studio, as @MarkSinkinson pointed out.

If you have this same problem, verify you have the correct installation file (not SSMS only, or an incompatible Edition).

Other helpful things to check, from comments originally left on the question:

You may have multiple instances installed, upgraded one of them, and connected to another. Or the upgrade didn't take (this can be easy to dismiss without noticing in the final step of the upgrade wizard). Please note that 2008 and 2008 R2 are completely separate, independent releases.
Make sure that you are connected to right instance in any case.
Check you didn't uncheck the database engine installation option


What does repair and optimize tables in PHP MyAdmin actually do?

Someone told me Optimize is...

Do you remember when we used to defrag HDDs? Table optimization is something similar to that. It restructures a table so that data pages are next to each other and releases unused pages. If you ever delete a large part of the table data, optimization shrinks the table file down to the proper size.


Dividing data from one table to multiple storage

Create a new table Table_A_Archive with exactly the same schema as Table_A_Changes.  Partition both these tables by, say, one-month date ranges.  As data becomes stale in Table_A_Changes swap that partition out and swap it into Table_A_Archive.  Your data retention policy can be efficiently enforced this way, too.

A view which unions the two tables can make querying simpler.


SQL Server Database Design for Reporting

We did some performance tests and found that - with scenarios that are typical for our application domain - it is 10-20 times faster to query data when stored in a regular "tabular" format.


SQL Server giving intermittant connection failures

“I'm wondering if there is some odd setting that is killing older, not active connections?” – short answer is no.

This is probably network issue, or SQL Server being under big performance impact or DNS or something similar.

Is your application on the same machine as SQL Server ? Do you have any other issues? Can you run SQL Server from Enterprise manager w/o issues?


Seems similar to this Bug #69166:  Wrong result of FOUND_ROWS() in MySQL 5.6:

  Description:

  Executing FOUND_ROWS() after SELECT SQL_CALC_FOUND_ROWS ... returns wrong result under certain conditions.
  Might be related to Bug #68458

  See steps to reproduce. ...

and of Bug #69119: Wrong FOUND_ROWS() on MySQL 5.6.11

  Description:

  FOUND_ROWS() return is wrong count.

It seems they have not been fixed.

In any case, it would be a good idea to upgrade to latest 5.6 (5.6.33 currently). Both of your installations use very old versions. After upgrading check again and if the error persists, report it in the above bug item (69119).

Related to the upgrade: I don't think you have to restore for an upgrade in same major version. I's good idea to take a backup first, before the upgrade procedure, just in case something goes wrong.


"Remote" connection to a local MYSQL database on LAN

Issue solved - Windows firewall had to be turned ON and then have the ports forwarded. Windows firewall being off was filtering the ports.