Which constraints can the PostgreSQL planner make use of? I'm interested in how the PostgreSQL 12 planner use constraints. I assume that it can use NOT NULL and UNIQUE, but what about foreign keys? Or CHECK?

For the most part, it does not use them.

The default setting of constraint_exclusion is 'partition', meaning it only tries to use NOT NULL or CHECK constraints to rule out partitions of partitioned tables.  If you change this to 'on' instead, then it will try to prove cases where a query over a non-partitioned table can't return any rows because the query condition conflicts with the NOT NULL or CHECK constraint.  These proof attempts are not exhaustive, however.  If you are wondering about a specific case, you should try it and see.

alter table pgbench_accounts add constraint foobar check (filler is not null);
explain select count(*) from pgbench_accounts where filler is null;
                                        QUERY PLAN
------------------------------------------------------------------------------------------
 Aggregate  (cost=751000.44..751000.45 rows=1 width=8)
   ->  Gather  (cost=1000.00..751000.43 rows=1 width=0)
         Workers Planned: 2
         ->  Parallel Seq Scan on pgbench_accounts  (cost=0.00..750000.33 rows=1 width=0)
               Filter: (filler IS NULL)

set constraint_exclusion TO on;
explain select count(*) from pgbench_accounts where filler is null;
                   QUERY PLAN
------------------------------------------------
 Aggregate  (cost=0.00..0.01 rows=1 width=8)
   ->  Result  (cost=0.00..0.00 rows=0 width=0)
         One-Time Filter: false

If you are left joining to a table using a unique or primary key, and are not actually using any columns from that table, then it will just not do the join because it will not change the number of rows returned per each row of the other table (it will always be one), and the contents of those unreferenced columns don't matter.  In theory it could do the same thing with an inner join if there is a foreign key constraint, but empirically it does not do this.

It can use foreign key constraints to affect the selectivity estimates, however.


"Table or view does not exist" on synonym I have a database and a USER administrating it. Then I have an additional USER_APP to whom I granted CRUD access to all tables in my oracle data modeler (and then applied these via generated script )

I created a synonym for every table from USER_APP account, with the following syntax:

create synonym USER_APP.CLIENTS for USER.CLIENTS;

And for every table generation is successful and commit made.
But when I try to use it anyhow on that account, for example:

select * from CLIENTS;

I get an error that CLIENTS do not exist, namely

ORA-00942 : table or view does not exist.

What can I do to fix that or where should I look to determine the cause/solution?

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.


Case Insensitive ORDER BY clause using COLLATE I have spent a long time looking for this, and I am getting mixed messages.
In other DBMSs (tested in SQLite, Oracle, MariaDB, MSSQL) I can override the default sort order using the COLLATE clause:
SELECT *
FROM orderby
ORDER BY string COLLATE … ;

--  SQLite:     BINARY | NOCASE
--  MariaDB:    utf8mb4_bin | utf8mb4_general_ci
--  Oracle:     BINARY | BINARY_CI
--  MSSQL:      Latin1_General_BIN | Latin1_General_CI_AS

I have pored over the documentation and searched high and low, but I can’t find anything so straightforward for PostgreSQL.
Is there a COLLATE clause value that would sort Case Insensitive?
I know there are many questions regarding case sensitivity, but (a) most of them are old and (b) none that I have seen relate to the COLLATE clause.
FWIW, I am testing on PostgreSQL 11.8. I have a test fiddle on http://sqlfiddle.com/#!17/05cab/1, but it’s only for PostgreSQL 9.6.
MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

If you were using PostgreSQL 12 or newer you would be able to create a new collation via the CREATE COLLATION command that specifies deterministic = false:
CREATE COLLATION ci (provider = icu, locale = 'en-US-u-ks-level2', deterministic = false);

Instead of sqlfiddle, I used db<>fiddle, which offers several versions of PostgreSQL, for the example:
https://dbfiddle.uk/?rdbms=postgres_12&fiddle=a8f4b330d04266947fb11e5c04fa4891&hide=2

This solution is similar to what worked for this related answer (also on DBA.SE):
PostgreSQL nondeterministic collations are not supported for LIKE
The first four db<>fiddle example queries with an ORDER BY clause show three different sort results:

the ORDER BY "string" COLLATE "POSIX" query returns rows in closer to a binary / ordinal ordering with all upper-case (A - Z) grouped before all lower-case (a - z).
the ORDER BY "string" and ORDER BY "string" COLLATE "en_US.utf8" queries return rows in what is effectively case-sensitive order with "A" and "a" grouped together, and lower-case of each letter coming before the upper-case of the same letter (aA bB ...).
the ORDER BY "string" COLLATE "ci" query returns rows in a truly case-insensitive order with "A" and "a" grouped together, but this time the cases of each particular letter are not in a predefined order and the two cases can even be intermixed within a letter. Since it is not guarantee to produce the same order each time, here is a sample of the result set that I'm currently seeing for this query:
-8    banana
-15   Banana
-9    bANana
1116  Banana

The following statement from the Collation Support documentation for version 12 explains that deterministic needs to be set to false:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), in order for such collations to act in a truly case- or accent-insensitive manner, they also need to be declared as not deterministic in CREATE COLLATION;... Otherwise, any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

But the same note from the same Collation Support documentation for version 11 explains that sorting is still effectively case-sensitive:

Note that while this system allows creating collations that “ignore case” or “ignore accents” or similar (using the ks key), PostgreSQL does not at the moment allow such collations to act in a truly case- or accent-insensitive manner. Any strings that compare equal according to the collation but are not byte-wise equal will be sorted according to their byte values.

Taking a closer look at the CREATE COLLATION documentation, it seems that the deterministic property (which defaults to true but needs to be set to false to do insensitive comparisons and sorting) was introduced in Version 12, which doesn't really help you. (@LaurenzAlbe recommended an alternate syntax in a comment that might work on older versions of libicu, and adjusting the db<>fiddle test linked above for that syntax and to run on PostgreSQL 11, it does not appear to work: https://dbfiddle.uk/?rdbms=postgres_11&fiddle=c3c48e111ed1837987524fee2c54a183&hide=2 )
That leaves you with two options:

lower() function
citext datatype (as mentioned by @a_horse_with_no_name in a comment on the question)

The citext datatype calls the lower() function internally, but has the following benefits (according to the documentation, linked directly above):

it applies to implicit indexes created by UNIQUE and PRIMARY KEY constraints
don't need to remember to add lower() to both sides of a comparison

The two main considerations are:

citext is not as efficient as text because the operator functions and the B-tree comparison functions must make copies of the data and convert it to lower case for comparisons. It is, however, slightly more efficient than using lower to get case-insensitive matching.

citext doesn't help much if you need data to compare case-sensitively in some contexts and case-insensitively in other contexts. The standard answer is to use the text type and manually use the lower function when you need to compare case-insensitively; this works all right if case-insensitive comparison is needed only infrequently. If you need case-insensitive behavior most of the time and case-sensitive infrequently, consider storing the data as citext and explicitly casting the column to text when you want case-sensitive comparison. In either situation, you will need two indexes if you want both types of searches to be fast.

It sounds like citext creates a lower-case copy of the data that takes up more space but is slightly faster due to the pre-calculation.
Also, citext needs to be installed. I modified the earlier example queries to make use of citext to show what can be done on PostgreSQL 11, but it seems that citext is not installed on db<>fiddle. I will put in a request for that. But here is are the test queries that should show how it works on your system if you have citext installed:
https://dbfiddle.uk/?rdbms=postgres_11&fiddle=7dd95430aad8f646bfa1b7e79e21871c&hide=2
All of that being said, it is important to note that case-insensitive ordering, for the only or right-most / final column, isn't much different than case-sensitive ordering: the same letters across both cases are going to group together. It's just that with case-sensitive ordering, each case within each letter will group together (i.e. all "A"s before or after all "a"s), while case-insensitive ordering allows for "A"s and "a"s to be intermixed (since neither has priority). Allowing different cases of the same letter to intermix becomes important when there is at least one additional sort criteria in the ORDER BY list, specified to the right of (i.e. after) the string column in question. The final two example queries in the db<>fiddle linked at the top of this answer shows the difference.
With all of this in mind, the main question is: what precisely are you trying to accomplish? The request, as it is currently stated in the question, is for a case-insensitive sort. Does this mean that the comparisons should still be case-sensitive? That would seem like an odd combination given that it is fairly common to have/want a case-insensitive comparison, but the sort is less important as there is often little difference between case-sensitive and insensitive sorting (case-sensitive is consistent, but case-insensitive will have random ordering of strings that differ only in casing, and that randomness sometimes results in the same ordering as what case-sensitive would return), unless there are additional fields being sorted on.
Comment from the O.P.:

MySQL/MariaDB and SQL Server default to case insensitive, and that would certainly make sense when sorting most text. Oracle and SQLite default to case sensitive, but have a relatively simple solution for a case insensitive sort. The default collation for my database is en_US.UTF-8. I’m trying to fill in a few gaps here.

It's still not clear why any "gaps" need to be filled, but it sounds like there is a slight issue with terminology: the term "sort" / "sorting" is being used, but I suspect that the main issue is with comparisons, given that there's often no visible difference between case -sensitive and -insensitive when it comes to sorting. And, without overriding a predicate or ORDER BY field/expression with a COLLATE clause, the same sensitivity (or lack thereof) would be applied to both. This is important because, if I'm correct that O.P. truly means both, then:

Performance-wise, I would guess that lower might actually be slightly better than casting to citext in this specific context because citext is going to call lower anyway but is also doing a datatype conversion. To be fair, I haven't done any testing around this so it's merely an edumacated guess, assuming that when used as a datatype for a column, citext is slightly better due to the values being pre-calculated, but in this case there's no opportunity to precalculate.
Using either citext or lower in the ORDER BY will only solve half the issue (and the less important half given, again, that the distinction is barely noticable)
Most likely you need to stop focusing on the ORDER BY clause and use citext as the datatype for the columns, which will take care of both comparisons and sorting.


How to insert into TABLE Variable? I want to store 2 coordinate points (latitude, longitude) in a table variable.

I have tried:

declare @coordinates table(latitude1 decimal(12,9),
                           longitude1 decimal(12,9),
                           latitude2 decimal(12,9),
                           longitude2 decimal(12,9))

select latitude,
       longitude into @coordinates
from   loc.locations
where  place_name IN ('Delhi', 'Mumbai')
select @coordinates

It's showing error:

  Msg 102, Level 15, State 1, Line 2
  Incorrect syntax near '@coordinates'.

The result of the select query:

select latitude,
       longitude
from   loc.locations
where  place_name IN ('Delhi', 'Mumbai')

is:

latitude    longitude
28.666670000    77.216670000
19.014410000    72.847940000

How can I store the values in table datatype?

I ran the query SELECT @@VERSION and got the result:

  Microsoft SQL Server 2016 (RTM) - 13.0.1601.5 (X64) Apr 29 2016 23:23:58 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows 10 Enterprise 6.3  (Build 16299: )

Use this one:

DECLARE @coordinates TABLE (
    latitude1 DECIMAL(12,9),
    longitude1 DECIMAL(12,9)
)

INSERT into @coordinates
SELECT
    latitude,
    longitude
FROM loc.locations
WHERE place_name IN ('Delhi', 'Mumbai');

SELECT * FROM @coordinates

Note:

You created 4 column with NOT NULL behaviors, but your inserting on
2 columns only. It will return an error.
Use INSERT INTO instead of SELECT INTO. Table is already created.
Use SELECT..FROM when calling DECLARE tables.


Connecting Remote Shares via Powershell I had a big problem which I just happened to solve, but it's a permissions issue I don't quite understand. Unfortunately I'm in a tight bind and no amount of googling in the past week has really helped, so perhaps someone could explain what happened here:

As part of an interface out to our customers we developed a Powershell script that maps a network drive on our web server WEB. The job step is run on a regular basis to validate the connection is active:

Connect Job

Owner: [JobsLogin]

Step 1: Connect Share

Type: PowerShell

Run as: Sql Server Agent Service Account

Command:

$Scripts\Map-Share.ps1 (My configuration values)

Map-Share.ps1

param (
    [string]$DriveLetter,
    [string]$Path,
    [string]$User,
    [string]$Password,
    [string]$Persistent="No"
)
if (!(Test-Path "${DriveLetter}:"))
{
    net use ${DriveLetter}: $Path /u:$User $Password /persistent:$Persistent
}

Then we run BCP to output the file to the mapped site:

declare @cmd varchar(2048)
SET @cmd = 'bcp "exec ' +
    @SPName + ' " queryout "' + @DataFolder + @FileName + '.tmp" ' +
    @BCPFlags
print @cmd

EXEC master..xp_cmdshell @cmd

At some point in the past week this mapping became unavailable and we started receiving the dreaded "BCP cannot open host-file" error message.

After an number of hours mapping network drives, deleting mapped network drives,  running diagnostic command shell, pleading with god and then satan, checking registry values, I started to suspect that the mapped drive was not mapped for the user executing sp_cmdshell.

I ran exec sp_cmdshell "cd W:" and found that it wasn't working. I ran exec sp_cmdshell "net use" and saw that the status of W was "Unavailable". Finally, progress!

And a

exec sp_cmdshell "net use /delete W:"

exec sp_cmdshell "Powershell $Scripts\Map-Share.ps1 (My configuration values)"

later, everything works again!

But now I'm stuck, because I can't figure out how to run these commands in the job as the correct user (I've tried the services account with no success). I could change the Connect share job to run the powershell command but that seems like a hack to me (But I'm not a database guy, so maybe that is right).

Anyway, what is really going on here? What user is my drive getting mapped in the connect share job?

Alright, so this solution has turned out to be pretty ugly. The end result is that I have to run the powershell command via the Powershell job step type and run it using xp_cmdshell in a sql job step type. Running it in both contexts guarentees the drive is mapped.

I then run a secondary step for both environments in which I test to make sure the drive is mapped using the same logic, ie:

Sql (CmdShell) Check

exec xp_cmdshell 'powershell if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}'

Powershell Check

if(!(Test-Path -LiteralPath "W:")) {

    throw ("W: is not mapped")

}


MySQL Load Balancing what do i need Replication or Cluster? So My MySQL database server is reaching it's limits with the number of queries selects, updates, inserts etc it can concurrently take resulting in high CPU usage more than what a single machine can take any more.

I have been seeking out my options and have come to the conclusion I should go about either setting up a Replication or Cluster.

But as with anything everyone's needs are different, So I will inform you about my current application. Joomla CMS, InnoDB database handler. (Pretty straight forward.)

Now the way I can think of it being the easiest setup for my needs is something like this. I know replication and clustering probably does not work like this but I ask this question to be corrected and to be pointed in the direction of the setup I should pursue.

I drew a lovely little bit of art in Windows Paint in order to show you what I am after.

I want to load balance all forms/types of queries across multiple machines and have them all somehow sharing the same database across a private network like Network Sharing mapped hard drives what ever.

That way instead of a single machine's CPU being utilized to select update and insert it will load balance between multiple utilizing multiple servers resources in order to return the query results.

Thanks in advanced for reading this much appreciated for any help that can be provided on what I should go with and type of setup I require.

That topology will help scale reads.  Is that what you need?

Writes must go to the Master, from which they will be 'replicated' to the Slaves.  The Slaves will be readonly.

So, send any write, together with associated reads, to the Master.  Send all other reads to a Slave in a round-robin way.

High CPU usually means you have a query that needs optimizing.  Let's see such a query, plus SHOW CREATE TABLE.

Depending on what you say to those comments, we may move into talk of Galera Cluster.


What is the behavior of SQL Server Maintenance Plan - Clean Up task for deleting old backups This seems pretty obvious, but it's remarkably hard to find an actual document that addresses this.

The scenario:

Full Backup - Weekly (Set to expire after 7 days)
Differential Backup - Daily (Set to expire after 7 days)
Transaction Log Backup - Hourly (set to expire after 3 days)

In this scenario there is a full backup and then 6 days of differential backups tied to the full backup, with several transaction logs in between.

Now, I want to create a cleanup task to remove old backups. If I set the cleanup task to remove backup files (bak) older than 7 days does it delete the full backup and essentially orphan the 6 differential backups?

Does the expiration policy on the backup task "lock" those files from being deleted before they expire? In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?

The goal here is to do a couple of things:

If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.
Keep at least one full week of intact backups. i.e. If I tell the cleanup task (on the 8th day) to delete files after 7 days and it deletes a FULL, then the remaining 6 days of differentials become invalid and now I only have 1 day of backups.

I would like to treat the transaction logs the same way, but I only want point in time recovery for 3 days. After that it will be expected to fall back to the most recent, suitable differential backup w/o transaction logs. So, I would delete transaction logs older than 3 days as well.

Does the expiration policy on the backup task "lock" those files from being deleted before they expire?

No. The Backup set will expire setting indicates when the backup set can be overwritten by another backup. It does not cause the file to be deleted, nor does it prevent the file from being deleted.

  In essence, could I set the cleanup task to delete files older than 1 day, and yet not delete backup files that haven't yet expired?

No. The cleanup task will delete the old files. And any backup sets within those files will be gone, regardless of their expiration setting.

  If a FULL backup is deleted, then all the differentials are useless, so they should be deleted as well.

You are correct that the differential backups are then useless. But the cleanup task that deletes the full backup doesn't know that, so it has no way to delete the differentials. You just have to wait until the cleanup task runs the next time, and the next time, and the next time, and it will eventually delete the differential backups, one at a time as they reach the cutoff age (7 days, in your example).


MySQL master to master race conditions, do I need to use transactions/locks for this code? I'm playing around with master to master MySQL replication hoping I can run two copies on my C# application on two different servers to help improve performance. Rather than the one copy on one server I have currently.

However I'm struggling to understand how MySQL prevents or handles race conditions and maintains the integrity of the database. For example my application has code similar to the below.

SELECT 100 TASKS TO PROCESS

UPDATE THE 100 TASKS TO SAY WE'RE WORKING ON THEM

INSERT THE RESULTS OF THE 100 TASKS INTO THE DATABASE

Because the code above runs so frequently eventually my application will try to work on a task that has already been "SELECTED" but not "UPDATED" yet by the other instance of my application and when it does that it will try and "INSERT" data that has already been inserted and this produces unique key errors which break the MySQL master to master replication.

I can see from this answer that the mysql instances seem very separate and appear to have no awareness of each others locks/transactions.

https://stackoverflow.com/questions/36026769/transactions-between-two-replicating-master-mysql-servers

I can see that I can disable unique key errors with the config below but I'm not sure if that will cause more problems than it will fix.

slave-skip-errors=1169

Is this something I can avoid with locks and transactions?

Or is this just part of master to master replication and I somehow have to design my application around this?

In short - yes, you will have to engineer your application in a way that makes it resistant to race conditions. Transactions won't help you with this.


Are snapshots generated for each sub query inside a Postgres READ_COMMITTED transaction? From what I understand - in a READ_COMMITTED Postgres transaction “the transaction obtains a snapshot whenever an SQL command is executed” source. Does this mean a snapshot will be obtained for each sub query in a nested query? Does the use of CTEs in place of sub queries affect this behavior?

The snapshot 'created' is per statement regardless of whether that statement contains subqueries or not. From the documentation:

... This level is different from Read Committed in that a query in a repeatable read transaction sees a snapshot as of the start of the first non-transaction-control statement in the transaction, not as of the start of the current statement within the transaction.


Why do I only see SYS and INFORMATION_SCHEMA tables? Access 2007 ODB to SQL Server 2008 I was finally able to create my ODBC connection but now when I go to External Data > ODBC > LINK TABLES using my ODBC connection in Access 2007 I get a huge list of tables such as

INFORMATION_SCHEMA.CHECK_CONSTRAINTS

INFORMATION_SCHEMA.COLUMNS

INFORMATION_SCHEMA.DOMAINS

etc.....

and a whole bunch of

sys.all_columns

sys.all_objects

sys.all_parameters

etc...

the only tables that i really need are tables such as

dbo.users

dbo.stats

dbo.customers

etc...

but none of those tables are on the list of table that I am able to choose from to link.

Does anyone know why I can't see or link to the DBO tables I need?

(by the way i created a new user login in order to create my ODBC connection)

Thanks to Jimbo and Shark's comments I was able to fix my problem by adjusting the permission for the tables I wanted to see. I did not have SELECT permission on the tables so I was not able to see them. Once I added the SELECT permission to the tables I was then able to see my data perfectly.


Does visual studio (2015 or above) needs to be installed in a server in order to transfer/create SSIS packages there? At work we have an old server and we're moving everything from there to our new server. We're a very small team, and one of my team-members says without visual studio 2015 in the new server he can not transfer/create SSIS packages there. Any thoughts on this?

Visual Studio does not need to be installed on the SQL Server.  SSIS packages built with SQL Server Data Tools can be exported to .ispac files, which can then be deployed to servers using SQL Server Management Studio.

See Deploy Integration Services (SSIS) Projects and Packages for more details.

One thing to note is that I've found that deploying .ispac files to SQL Server 2016 servers requires SSMS 2016 for some packages.  Packages deployed with later versions of SSMS end up with versioning errors (Source - ODBC is not compatible with this version of the DataFlow).


I suggest you create a calendar table with a couple of years of data. I'll just use one with sufficiently many rows for your example:

CREATE TABLE CALENDAR (d date not null primary key);
INSERT INTO CALENDAR (d) VALUES ('2019-05-01'),...,('2019-08-01');

While we are at it, let's create some usable sample data instead of ascii-art tables:

create table items
( item_id int not null primary key
, startdate date not null
, enddate date not null
, capacity int not null);

insert into items (item_id, startdate, enddate, capacity)
values (1, '2019-05-01', '2019-06-01', 8)
     , (2, '2019-06-21', '2019-07-01', 13)
     , (3, '2019-07-11', '2019-08-01', 4);

To determine the capacity for each date:

select cal.d, i.item_id, coalesce(i.capacity, 0) as capacity
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;

Now you can subtract your userdata from that, perhaps easiest to do with a sub-select

select cal.d, i.item_id
     , coalesce(i.capacity, 0) - coalesce((select sum(number)
                                           from userdata u
                                           where u.dt = cal.d
                                             and u.item_id = i.item_id),0) as availible_items
from calendar cal
left join items i
    on cal.d between i.startdate and i.enddate;


How to get the last not-null value in an ordered column of a huge table? I have the following input:

 id | value
----+-------
  1 |   136
  2 |  NULL
  3 |   650
  4 |  NULL
  5 |  NULL
  6 |  NULL
  7 |   954
  8 |  NULL
  9 |   104
 10 |  NULL

I expect the following result:

 id | value
----+-------
  1 |   136
  2 |   136
  3 |   650
  4 |   650
  5 |   650
  6 |   650
  7 |   954
  8 |   954
  9 |   104
 10 |   104

The trivial solution would be join the tables with a < relation, and then selecting the MAX value in a GROUP BY:

WITH tmp AS (
  SELECT t2.id, MAX(t1.id) AS lastKnownId
  FROM t t1, t t2
  WHERE
    t1.value IS NOT NULL
    AND
    t2.id >= t1.id
  GROUP BY t2.id
)
SELECT
  tmp.id, t.value
FROM t, tmp
WHERE t.id = tmp.lastKnownId;

However, the trivial execution of this code would create internally the square of the count of the rows of the input table ( O(n^2) ). I expected t-sql to optimize it out - on a block/record level, the task to do is very easy and linear, essentially a for loop ( O(n) ).

However, on my experiments, the latest MS SQL 2016 can't optimize this query correctly, making this query impossible to execute for a large input table.

Furthermore, the query has to run quickly, making a similarly easy (but very different) cursor-based solution infeasible.

Using some memory-backed temporary table could be a good compromise, but I am not sure if it can be run significantly quicker, considered that my example query using subqueries didn't work.

I am also thinking on to dig out some windowing function from the t-sql docs, what could be tricked to do what I want. For example, cumulative sum is doing some very similar, but I couldn't trick it to give the latest non-null element, and not the sum of the elements before.

The ideal solution would be a quick query without procedural code or temporary tables. Alternatively, also a solution with temporary tables is okay, but iterating the table procedurally is not.

A common solution to this type of problem is given by Itzik Ben-Gan in his article The Last non NULL Puzzle:

DROP TABLE IF EXISTS dbo.Example;

CREATE TABLE dbo.Example
(
    id integer PRIMARY KEY,
    val integer NULL
);

INSERT dbo.Example
    (id, val)
VALUES
    (1, 136),
    (2, NULL),
    (3, 650),
    (4, NULL),
    (5, NULL),
    (6, NULL),
    (7, 954),
    (8, NULL),
    (9, 104),
    (10, NULL);

SELECT
    E.id,
    E.val,
    lastval =
        CAST(
            SUBSTRING(
                MAX(CAST(E.id AS binary(4)) + CAST(E.val AS binary(4))) OVER (
                    ORDER BY E.id
                    ROWS UNBOUNDED PRECEDING),
            5, 4)
        AS integer)
FROM dbo.Example AS E
ORDER BY
    E.id;

Demo: db<>fiddle


MySQL Commited data not seen to select query Context: The framework used is Spring and all queries are run with JdbcTemplate. Mysql Server version is 5.6.19. The table is an InnoDB table and defaults like auto commit and isolation level repeatable-read is set.

Problem: An Insert happens inside a transaction and a select which reads the same data inserted does not see the data. The select runs after the insert and after the insert transaction has commited.

I've enabled bin log as well as general log in mysql. Relevant logs below

bin-log:

SET TIMESTAMP=1438265764/*!*/;
BEGIN
/*!*/;
# at 249935389
#150730 14:16:04 server id 1  end_log_pos 249935606 CRC32 0xa6aca292    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265764/*!*/;
insert into user_geo_loc_latest(user_id, lat, lng) values(x,y,z) on duplicate key update lat=y, lng=z
/*!*/;
# at 249935606
#150730 14:16:06 server id 1  end_log_pos 249936255 CRC32 0x2a52c734    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265766/*!*/;
INSERT INTO table(txnid) VALUES ('885851438265675046')
/*!*/;
# at 249936255
#150730 14:16:06 server id 1  end_log_pos 249936514 CRC32 0x6cd85eb5    Query   thread_id=40    exec_time=0     error_code=0
SET TIMESTAMP=1438265766/*!*/;
INSERT INTO table2(x) VALUES (y)
/*!*/;
# at 249936514
#150730 14:16:06 server id 1  end_log_pos 249936545 CRC32 0xceb9ec56    Xid = 9406873
COMMIT/*!*/;

Query Log

150730 14:16:04    40 Query ...
....
40 Query     select count(*) from table where txnid = '885851438265675046'
                   40 Query     select @@session.tx_read_only
                   40 Query     INSERT INTO table(txnid) VALUES ('885851438265675046')
                   40 Query     select @@session.tx_read_only
                   40 Query     INSERT INTO table2(x) values(y)
                   40 Query     commit
....
150730 14:16:07    36 Query     select pp.*, b.create_date from table pp left join bill b on pp.bill_id = b.bill_id where pp.txnid = '885851438265675046'

Curiously, First insert (249935389) should not be part of the transaction at all. It is a separate API call and completely unrelated. It could be spring mixing it with the transaction or I am reading the log wrong? AFAIK since it's on the same thread it implies the insert is in the transaction.

The next two inserts are part of the transaction and it looks like it commits. (249936514). Now the select query (last one in general log) runs after the commit and it does not see the data. It returns 0 rows. How can this happen considering the data is committed? Or is the commit not on the thread 40? Since it does not have the thread id.

To summarize I have two questions.

Does the BEGIN in the binlog being before the INSERT INTO user_geo_loc (which is not part of the transaction), is this a bug with spring/Jdbc or MySql simply does this as it knows this transaction has already committed (as transactions are written to binlog when they've succeeded) and therefore would never be rolled back.
Given the commit happens before the select (commit is at 14:16:06 and select is at 14:16:07) how is it that the select does not return the row inserted by the transaction?

This is extremely perplexing. Any help would be appreciated

Note: The queries in both the bin and query log have been edited to remove sensitive information. But the essence of the queries remain the same

Edit: Updated with general log and query log with a detailed example.

I try to make an hypothesis about the second question:

  Given the commit happens before the select (commit is at 14:16:06 and
  select is at 14:16:07) how is it that the select does not return the
  row inserted by the transaction?

Transactions are managed by Spring. So it would be possible that before running the select spring has raised a start transaction or it has already used the connection for running an another query.

I start a first session where I simulate an insert into a table t:

session1> create table t (i int auto_increment primary key);
Query OK, 0 rows affected (0,32 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

session1> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

session1> start transaction;
Query OK, 0 rows affected (0,00 sec)

session1> insert into t values();
Query OK, 1 row affected (0,00 sec)

I create a new session, session2, where autocommit is set to 0. Into this new session, a transaction is implicitly started when running a select.

session2> set autocommit = 0;
Query OK, 0 rows affected (0,00 sec)

session2> select * from t;  -- this starts a transaction
+---+
| i |
+---+
| 1 |
+---+
1 rows in set (0,00 sec)

Move to session1 to commit the insert.

session1> commit;

Now move again to session2:

session2> select * from t;
+---+
| i |
+---+
| 1 |
+---+
1 row in set (0,00 sec)

Session2 cannot see the row just inserted. If a commit is raised in session2 we can see new row inserted in session1

session2> commit
1 row in set (0,00 sec)

session2> select * from t;
+---+
| i |
+---+
| 1 |
| 2 |
+---+
2 rows in set (0,00 sec)

The general log looks like :

150804 14:04:10     2 Query select * from t

150804 14:04:30     1 Query start transaction
150804 14:04:39     1 Query insert into t values ()
150804 14:04:44     1 Query commit
150804 14:04:51     2 Query select * from t

150804 14:05:07     2 Query commit
150804 14:05:10     2 Query select * from t

The first row is related to session 2. It is when session 2 opens the transaction.

I don't know if this is what happen in your case. You could check in your general log if the connection_id 36 was used for other queries. Let we know.


Creating groups and members Is this a good idea to manage members of a group like so:

*Assuming members can belong to one group and no other

*Simplified model

Group:
  name
  someid

User:
  name
  ForeignKey(Group)
  state #integer 0-no group 1-waiting for approval from a group admin 2-belong to an group

Something like facebook groups

Having state in User table doesn't look good to me. It introduces dependency between User.group and User.state - group should be null if state equals 0, and non-null if state is 1 or 2. Also, column name is very confusing (I'd expect User.state to reflect user status (say active/inactive), or part of address (e.g. California/New York) , not state of relationship between user and group).

I'd put value in User.group only when user belongs to group, and handle requests in separate table user_group_request (user_group_request_id (PK),user_id, group_id, request_date, request_text, request_state,response_date). When user wants to join group, you add a new record into user_group_request. Setting User.Group should be part of approval routine. Also, depends on your model, you may want to add unique constraint on user_group_request(user_id, group_id), so one user cannot send multiple requests.


SQL Server TDE - add master key and certificate I want to add multiple master database encryption keys and certificates.

Can I do that?

From my knowledge, you cannot have multiple encryption database encryption keys as the Server stores a Server Master key automatically when SQL Server is first installed. It will use this for decryption (among other things) of database keys.

When you have created a database key (which is implicit) it will store a copy in the Master db, if you issue another create statement that will replace the existing key.

You should be creating a backup of the DMK and storing this somewhere secure, there is nothing to stop you having multiple copies of this key backup.

MSDN should tell you everything you need to know in greater detail.

http://technet.microsoft.com/en-us/library/bb964742.aspx

you will be able to create multiple certificates for external software etc. you can generate these keys yourself or get them from external providers

http://msdn.microsoft.com/en-us/library/ms187798.aspx


Perhaps I'm misunderstanding the objective but, to take the data that you helpfully provided in the INSERT and determine both the period when the record changed and the most recently paid period, you can do this:
SELECT
  tn.[name],
  tn.[home],
  tn.[current],
  tn.[effective_date],
  tn.[not_to_exceed_date],
  tn.[flag],
  tn.[grade],
  TRIM(tn.[position]) as [position],
  MIN(tn.[last_pay_period_paid]) as [first_pay_period_paid],
  MAX(tn.[last_pay_period_paid]) as [recent_pay_period_paid]
FROM
  [dbo].[tableName] tn
GROUP BY
  tn.[name],
  tn.[home],
  tn.[current],
  tn.[effective_date],
  tn.[not_to_exceed_date],
  tn.[flag],
  tn.[grade],
  TRIM(tn.[position])

This will give you the following output:

Note that there are two records for ACCOUNTANT. This is because there are two distinct values in home. Not sure if this is due to a typo in the sample data or if the column is even necessary. Either way ... this may give you the information you seek.


How the first IAM page is found by SQL Server Today, just of curiosity, I was checking the logs when the first allocation for a new table in a new database is made.

I can see that GAM was used to find a free extent and then a page from this extent is allocated to be used as a data page. Rows 8-12.
And then it allocates another page which isn't from the extent used before and uses this page as IAM. Row 13-16.
My question is, from where SQL Server finds this page and make it as IAM. Why this page is not allocated from an extent which is accessed by GAM.

I can see that GAM was used to find a free extent and then a page from
  this extent is allocated to be used as a data page. Rows 8-12.

This extent allocation seen in your transaction log file is a uniform extent allocation for your allocation unit.

  And then it allocates another page which isn't from the extent used
  before and uses this page as IAM. Row 13-16.

This page allocation seen in your transaction log file is a page that is part of a mixed extent, as you noted this page is used by your IAM page.

  from where SQL Server finds this page and make it as IAM. Why this
  page is not allocated from an extent which is accessed by GAM.

Mixed extents

IAM pages are always part of mixed extents.

Proof of IAM pages always being part of mixed extents by Paul S. Randal on:

Inside the Storage Engine: IAM pages, IAM chains, and allocation units

  A couple more things to note about IAM pages:

  There are themselves single-page allocations from mixed extents and
  are not tracked anywhere

And another article by Paul S. Randal:

Inside the Storage Engine: Anatomy of an extent

  Mixed extents (SQL Server 2016 onward)

  The use of mixed extents is disabled by default, and the only pages
  that are mixed pages are IAM pages, and this cannot be disabled.

How are IAM pages allocated

In Pages and Extents Architecture Guide

  IAM pages are allocated as required for each allocation unit and are
  located randomly in the file. The system view,
  sys.system_internals_allocation_units, points to the first IAM page
  for an allocation unit. All the IAM pages for that allocation unit are
  linked in a chain.

IAM pages are located randomly in the file but are chained together per allocation unit.

Dependent on version and traceflag 1118

With this information at hand and knowing that pre sql server 2016 (without TF 1118) tables could get mixed extents for the first 8 pages allocated, one extent could in fact hold both IAM pages & data pages.

Again proven in the previously mentioned article: Inside the Storage Engine: Anatomy of an extent

  This means a mixed extent may hold a variety of page types
  too, including IAM, data, index, or text pages.

By default starting with SQL Server 2016 (or with TF 1118 enabled) data pages will be part of uniform extents and IAM pages will be part of a different, mixed extent.

This should explain why your IAM page is mapped differently from your data page and it's uniform extent that was allocated.

Testing mixed extent allocation

Creating some test tables & inserting one row per table on a SQL Server 2012 instance without TF 1118.

CREATE TABLE dbo.IAM1(id int)
INSERT INTO dbo.IAM1(id)
VALUES(1); -- IAM2,IAM3,IAM4, ... tables created

We can then use DBCC IND to map these allocated uniform pages, in the 150 - 180 range on my end.

If we then check the SGAM settings for the database that only has one SGAM page allocated, on page id 3:

DBCC TRACEON (3604);
DBCC PAGE ('database', 1, 3, 3);

We get this information for our page ranges

(1:152)      - (1:168)      = NOT ALLOCATED       --> 2 FULL uniform extents
(1:176)      -              =     ALLOCATED
(1:184)      - (1:256)      = NOT ALLOCATED

We see that two uniform extents, from 152 to 168 are full.

We could then check the PFS to see if these are IAM pages or not.

DBCC TRACEON (3604);
GO
DBCC PAGE ('database', 1,1, 3);

This is the result

(1:152)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:153)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:154)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:155)      -              =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:156)      - (1:157)      =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext
(1:158)      - (1:162)      =     ALLOCATED   0_PCT_FULL                     Mixed Ext
(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext
(1:164)      -              =     ALLOCATED   0_PCT_FULL           IAM Page  Mixed Ext

5 IAM pages, not enough to get a full uniform extent of only IAM pages.
With one data page of one of the tables:

(1:163)      -              =     ALLOCATED  50_PCT_FULL                     Mixed Ext

And 10 System data pages.

You will also see these single page allocations when uniform extents are used by reading the transaction log file:

Operation               AllocUnitName           Description
LOP_FORMAT_PAGE         dbo.IAM3
LOP_BEGIN_XACT          NULL                    AllocFirstPage;0x0105000000000005150000002204bf2bf6b1f23aeef85d6e8f150100
LOP_MODIFY_ROW          dbo.IAM3                Allocated 0001:00000076
LOP_MODIFY_ROW          Unknown Alloc Unit      Allocated 0001:00000077
LOP_FORMAT_PAGE         dbo.IAM3
LOP_HOBT_DELTA          NULL                    Action 0 (HOBT_PAGE_COUNT) on rowset 72057594039173120. Leaf page delta: 0, Reserved page delta: 1, Used page delta: 1
LOP_MODIFY_ROW          dbo.IAM3
LOP_CREATE_ALLOCCHAIN   NULL
LOP_LOCK_XACT           NULL
LOP_COMMIT_XACT         NULL

EDIT

  Thanks for replying, but my question was how does the SQL Server
  decide the page 0001:00000135 for IAM. It should have looked somewhere
  in the GAM or SGAM or even PFS for free space check but it doesn't. I
  created a new database and a new table multiple times. And found out
  that the first IAM page is always the same and also the same data page

IAM / Mixed page allocation

Below quote that explains tempdb contention when using mixed pages should have the same application for user databases and it's IAM page allocation.

  When SQL Server searches for a mixed page to allocate, it always
  starts the scan on the same file and SGAM page. This causes intense
  contention on the SGAM page when several mixed-page allocations are
  underway. This can cause the problems that are documented in the
  "Symptoms" section.

Source

Correct me if im wrong but the mechanism it looks like it uses to allocate IAM pages is to check the SGAM page to get a mixed extent with free pages (SGAM bit = 1) and then uses the PFS page to find the first unallocated mixed extent page to allocate the IAM page.

(If there are mixed extents with free pages, if not, it also has to search the GAM page).

This statement is built upon the understanding that allocating a new IAM page should be the same as allocating new mixed extent data pages.

Managing space used by objects

  For each extent, the SQL Server Database Engine searches the PFS pages
  to see if there is a page that can be used

And  Paul S. Randal Why PFS pages cannot be repaired

  The answer is that the is-this-page-allocated-or-not information is
  not duplicated anywhere else in the database, and it’s impossible to
  reconstruct it in all cases.

Managing Extent Allocations

  To find a mixed extent with free pages, the SQL Server Database Engine
  searches the SGAM for a 1 bit.

  To allocate a mixed extent, the SQL Server Database Engine searches
  the GAM for a 1 bit, sets it to 0, and then also sets the
  corresponding bit in the SGAM to 1.


How many databases does a sybase instance have? I'm a newbie to sybase and could not find the clear answers on Sybase tutorials/docs

How many databases can one Sybase instance have? Oracle has one
instance which corresponds to only one database, is it the same with
Sybase ?
What is a master database, is it a db that holds metadata? Does each
instance have one master database ?
What is a user database ? Is it similar to a tenant db in sap hana ?

ASE can have up to 32K dbs (includes various system dbs).

The master db holds server wide metadata (eg, logins, roles, configs, databases) and can also hold user data (though this is discouraged).

Other system databases include sybsystemprocs (collection of system stored procs and functions), tempdb (temporary scratch space; DBA can create additional temporary dbs), model (template used when creating a new database), sybsystemdb (used to manage distributed txns); there are several other system dbs that can exist depending on options/features the DBA enables.

Any other databases would fall under the heading of user databases.

And yeah, a user db would be similar to a HANA tenant db, though all ASE databases are accessed via the same host:port combo (HANA tenants have their own port if I recall correctly).


Sharded DB accidentally created during update of MongoDB from 2.4.4 to 2.6.4 I am upgrading mongo version 2.4 to 2.6
I was able to upgrade my complete cluster except one primary member of my replica set
during this period my auto running script created a sharded db.

I can not delete this db as it has important data. I wanted to confirm that i can still continue with the upgrade process for last remaining server will it create an issue.
I have completed upgrade of all mongos and config server before this.

Please let me know the impact if it will be there.

If all the other components are upgraded, then there will be no impact to the sharded database caused by upgrading the remaining primary.  The important pieces for the sharded database/collection are mongos and the config servers rather than the individual members in this case, and you have already done that piece.

If you want to be particularly careful, you can leave the primary on 2.4, step it down and allow a 2.6 secondary to step up to primary (this would be the recommended process anyway, you should always step down a primary to secondary before you upgrade it).  Doing it this way also means you have a very simple way to roll back the upgrade if necessary.  Once you verify no issues, upgrade the old priamry (now secondary) and you are done.

One caveat - stepping down a primary will force close all active connections (mongos, config servers mainly) to that primary, and they will re-establish connections to the new primary once it is elected.  This is expected (and necessary in fact) behavior, but can cause a brief spate of errors (for example for a long running query from that shard, which would error).


This isn't going to be a query that scales very well, no matter which approach you take, since text matching anywhere in a column except at the left edge of an indexed column will require a full table scan.

I'm not familiar with OTRS ... I downloaded it and looked at the source scripts that create the database schema and it looks like the column you're querying is the result of denormalization and they're using the %% symbols to do language localization... so querying the way you're doing it is not likely to be, as some people like to say, very "performant."

Bear in mind also that % is a wildcard character for the MySQL LIKE operator, so what you're actually matching on, as written, could lead to false positives, because your expression is evaluated by MySQL as trying to match *raw*[space]*new* and *close.

What you appear to be actually searching for is %\%Raw\%% %\%new\%% where % means anything and \% means a literal %, not a wildcard.  Also, you want %\%Close, or possibly %\%Close% if the '%Close' can be found anywhere other than at the end of a line.

Importantly, I suspect there is a better way to get the data you are looking for from OTRS, so you should review the schema and consider whether this is a possibility.  For example, if you are looking for tickets that have been closed, it looks like to me that this might be tickets WHERE ticket.ticket_state_id = 2 or possibly tickets WHERE ticket_history.state_id = 2?  Similarly, wouldn't a ticket with ticket_history.queue_id = 2 mean "Raw"?

Those literal "2" values may not be correct, depending on your version and precisely how OTRS uses its database, but understanding the schema and querying the database in a more optimizer-friendly fashion will get you a substantially better-performing query than searching every row of the table with LIKE.

As your query is written, and also in the example below, every row of the ticket_history table will have to be scanned at least once, no matter what you do.

So I would not begin to suggest that the following is "the right way to do it" but it adds in the logic your query is missing.  If a row contains one pattern, we verify whether the ticket_history contains a row for the same ticket that matches the other pattern, and if so, we keep that row ... and if not, we discard it.

SELECT `ticket`.`id`,
       `th`.`ticket_id`,
       `th`.`id`,
       `th`.`name`,
       `th`.`create_time`
  FROM `ticket_history` th
  JOIN `ticket` ON  `th`.`ticket_id` =  `ticket`.`id`
 WHERE (
        `th`.`name` LIKE  '%\%Raw\%% %\%new\%%'
        AND EXISTS (SELECT * FROM ticket_history th1 WHERE th1.ticket_id = th.id AND th1.name LIKE '%\%Close')
       )
    OR (
        `th`.`name` LIKE  '%\%Close'
        AND EXISTS (SELECT * FROM ticket_history th2 WHERE th2.ticket_id = th.id AND th2.name LIKE '%\%Raw\%% %\%new\%%')
       )
 ORDER BY `ticket_history`.`ticket_id`, `ticket_history`.`id` ASC;


Can not connect to SQL Server from Ubuntu sqlcmd client I'm trying to connect to a SQL Server Express server from a Linux Ubuntu 16.04 computer. I Installed all SQL commad line utilities for the linux machine and follow instructions from Microsoft SQL Server on Linux docs.

When I run

sqlcmd -S ip/SQLEXPRESS -U SA -P 1234

the command always returns the following error :

  Microsoft ODBC Driver 13 for SQL Server : Login timeout expired.
  Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2AF9

I was able to run the same command from a Windows 10 computers and it works with no flaws.

The linux computer can communicate to the server machine and SQL Server 1433 port. I test this with the linux nc command (netcat).

Linux nc command results:

nc -z -v -w5 192.168.0.1 1433

  Connection to 192.168.0.1 1433 port [tcp/ms-sql-s] succeeded!

Any suggestions or something missing?

I have tried changing the ports, parameter order etc... and Linux version of sqlcmd doesn't work.

I also tried including extra '/' character, because it is special on Linux. The new command line is sqlcmd -S ip//SQLEXPRESS -U SA -P 1234 but it doesn't work. It works faster but returns other error:

  Microsoft ODBC Driver 13 for SQL Server : TCP Provider: Error code 0x2746.

Instance name doesn't work neither. sqlcmd -S SERVER-NAME\\SQLEXPRESS -U SA -P 1234 or sqlcmd -H ipaddress -S SERVER-NAME\\SQLEXPRESS -U SA -P 1234 The command recognizes quotes so sqlcmd -S "SERVER-NAME\SQLEXPRESS" -U SA -P 1234. Nothing works.

The correct syntax is sqlcmd -S ip\\SQLEXPRESS -U SA -P 1234 with double '\'. It doesn't connect anyway. I have several days struggling with it and could not find a way to make this thing work.

I think the right command should be:
sqlcmd -S <IP>\\SQLEXPRESS,1433 -U SA


Sum by Implicit Group I'm sure I'm missing an obvious solution on this, but I'm trying to summarize values for groups that are defined by an explicit group number and an implicit ordering.  I'm sure that doesn't make this much clearer, so let's say I have this example source heap table:

GroupID     Value
----------- -----------
1           5
1           5
1           3
2           4
2           1
1           4
2           3
2           5
2           2
1           1

I would like a query that provides me with the following results:

GroupID     Values
----------- -----------
1           13
2           5
1           4
2           10
1           1

The implied ordering is the challenge that I've just not found a way to get around... yet.  Any help would be appreciated.

I was hoping I could create a deterministic row ordering with a query similar to the following:

SELECT    *
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

I am hoping this forces an allocation order scan which would then give me a deterministic row order.

Sadly, I'm stuck with the data as is. I've got no other indicators here such as date, etc. that would provide any set order.  My hope was the trick outlined above would suffice, but I'm not entirely certain it will.

EDIT: Just to close this up as I'm know there were questions around why I was asking this, I had a series of heap tables, named by month/year, that contained line-item amounts that the business wanted to be summed up by day (which they correlated to the implied groups in my question).  Because it doesn't look feasible to do this effectively, we've settled on aggregating at the month (e.g. table) level, so this post helped me justify the alteration to the business requirements.  Thanks for everyone's input!

The "implicit" group you mention seems to be based on the row order.  Unlike a spreadsheet or text file, a relational table is logically an unordered set of rows regardless of whether it is stored as a heap or has a clustered index. It will not be possible to write a query to provide the desired results unless you have another column to facilitate the grouping.

  I was hoping I could create a deterministic row ordering with a query similar to the following:

SELECT      *
        , ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RowNum
FROM Table WITH(TABLOCK)
OPTION (MAXDOP 1)

Sorry to be the bearer of bad news, but ordering by a literal will not guarantee deterministic ordering (and even if it did, you'd need an ORDER BY clause there). If it appears to do so, it is only by happenstance. Any incremental column will work, though.

  I am hoping this forces an allocation order scan which would then give me a deterministic row order.

An allocation-ordered scan is no more deterministic than any other implementation; you're just (unsafely) relying on a different observed behaviour.


Set recovery mode in mysql database I've been reading about different recovery modes you can set on sql databases, for example here: https://www.mssqltips.com/sqlservertutorial/2/sql-server-recovery-models/

So I've been trying to find the option to set this recovery mode or check what recovery mode I am using in mysql but I cannot find it anywhere. I've been googling around as well, and I also cannot find an answer anywhere.

Is this related only to certain database platforms that use sql (Microsoft only?) that's not mysql or can I use this or a similar function in mysql and postgresql?

This is an administration task, and the sql standard does not define such things; so a DBMS is not required to have this. But since it is obviously very useful to be able to make a backup, most systems do have similar capabilities, although they might be implemented or named in a different way.

The corresponding function to the "simple" recovery model is a mysqldump or a pg_dump.

An equivalent to the transaction log backups which will be created in the mssql-"full" recovery model are the mysql binary log and the WAL-log for postgres. All methods will allow you to continuously backup and (more or less comfortably) recover a database to a point after the last complete backup. Many technical details, e.g. on how to recover, if the file created by the "simple" backup can be used for it, the file formats, or how or if the files used internally (e.g. after a crash) differ.

These methods also resemble each other in that they will be used (and are required) for replication purposes in all three database systems. For such a functionality, a different server needs to be continuously informed about all the changes that oocured, which is exactly what these logs contain.

For the MSSQL-"Bulk logged" recovery model, there is no direct equivalent; it is a special version of the "full" model that does not log some bulk operations.


MariaDB: ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails This question has already been asked before, but none of the answers suggested were applicable to my situation.

Issue:

I am prevented from inserting data into a column because of a foreign key violation. However, the foreign key is a valid reference.

Failing query:

INSERT INTO `insert-table`(`not-important`, `foobar-id`) VALUES ('whatever', 'This exists in both tables');

Error:

  ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (mydb.insert-table, CONSTRAINT FK_foobar FOREIGN KEY (foobar-id) REFERENCES foobar-table (foobar-id))

SELECT COUNT(1) FROM `foobar-table` WHERE `foobar-id` = 'This exists in both tables';
1

Schema:

mysql> SHOW CREATE TABLE `insert-table`;
CREATE TABLE `insert-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  `not-important` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`,`not-important`),
  CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

mysql> SHOW CREATE TABLE `foobar-table`;
CREATE TABLE `foobar-table` (
  `foobar-id` varchar(190) COLLATE utf8mb4_unicode_ci NOT NULL,
  PRIMARY KEY (`foobar-id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Troubleshooting:

the data is there (no typo, see the error section)
both columns have the same type declaration (see above)
the engine used is already InnoDB (some websites recommend to switch to it)
I tried to remove the column names from the statement in case a bug was making MySQL ignore that (since they're both of the same type)
SELECT VERSION(); 5.6.10
SHOW VARIABLES LIKE 'innodb_version'; 1.2.10
Run (with the PROCESS privilege):

SHOW ENGINE INNODB STATUS;

------------------------
LATEST FOREIGN KEY ERROR
------------------------
2018-01-10 15:24:39 2b1f59283700 Transaction:
TRANSACTION 17469656069, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
1 lock struct(s), heap size 376, 0 row lock(s)
MySQL thread id 147376, OS thread handle 0x2b1f59283700, query id 152047035 123.45.67.89 theDbUser update
INSERT INTO `insert-table` VALUES (‘This exists in the other table’, ‘whatever’)
Foreign key constraint fails for table `mydb`.`insert-table`:
,
 CONSTRAINT `FK_foobar` FOREIGN KEY (`foobar-id`) REFERENCES `foobar-table` (`foobar-id`)
Trying to add to index `PRIMARY` tuple:
DATA TUPLE: 4 fields;
0: len=22; bufptr=0x2b2ea459a812; hex= 546869732065786973747320696e20626f7468207461626c6573; asc This exists in both tables;;
1: len=25; bufptr=0x2b2ea459ab0c; hex= 7768617465766572; asc whatever;;
2: len=6; bufptr=0x2b3a97464610; hex= 00041145cc05; asc    E  ;;
3: len=7; bufptr=0x2b3a97464618; hex= 00000000000000; asc        ;;

But the parent table `mydb`.`foobar-table`
or its .ibd file does not currently exist!

Related:

StackOverflow: MySQL 5.5 foreign key constraint fails when foreign key exists : links to bug below
https://bugs.mysql.com/bug.php?id=60309 fixed in 5.5 (2011), should not apply to MySQL 5.6; only for OSX
Foreign Key Constraint fails : suggests to temporarily disable foreign key checks. Not sustainable in my case.
"Cannot add or update a child row" when deleting a record? : no accepted answer
SQL Error 1452 : Cannot add or update a child row: a foreign key constraint fails : same

Obviously this doesn't happen on all foreign keys so we suspect there was some corruption affecting that foreign key.

The solution was to drop and recreate the foreign key.


Are there any inherent shortcomings in this schema? This is derived from the SQL generated by sqldbm.com that I simplified because it wasn't generating foreign keys correctly and it seems to me that there's no need to pull in every foreign key from the parent anyway (perhaps I'm mistaken!)

Is there significant value in pulling in every foreign key from the parent and making them part of the child's primary key? Is there a way to automate setting these additional keys? (I.e. a ticket row being a child of an event row which is a child of a location row and the ticket row also includes location_id and needing to make sure that gets set correctly.)

Does anyone see anything in the upper schema diagram that might shoot me in the foot down the road? Not pictured but desired is an additional many-to-many mapping to event questionnaire responses also attached to a ticket (i.e. a registrant providing answers to experience levels in a sport discipline, gear questions, etc as part of their ticket purchase). I am already planning on removing the multiple locations for costs and having option value be the only place cost is found and always having an option value supplied for each event.

Unfortunately, it's very difficult to just cast one's eye over a schema and say, "Yep that's great" or "Yes, that's going to work".

Schema and system design is an iterative process - you start with a few ideas, implement them and test to see how it's working. Then you rejig the bits that aren't working and see - now the rejigging will (in all likelihood) break some bits that were working previously and that you hadn't changed - so you tweak again, and hopefully you asymptotically approach (nirvana...) a decent working app.

I can only advise you to

visit databaseanswers.org - check out databaseanswers - there's lots of links related to tickets and events, and
check out your search engine of choice for "open source reservation systems" (or similar) and download some systems and check out their schemas.

Sorry not to be able to be of more help, but it's analagous to asking "I'm thinking about writing a book, here are the chapter titles, will it be good?".

Start out your system and if you have more specific questions, come back here and ask them - p.s. welcome to the forum.


What causes SQL error 41106? I get an error when I try to join the secondary replica to the Availability group thru Powershell while following the below Microsoft Documentation.

Join-SqlAvailabilityGroup -Path "SQLSERVER:\SQL\<SecondaryComputer>\Instance" -Name "<AG Name>"

Error:

  Failed to join local availability replica to availability group ''.  The operation encountered SQL Server error 41106 and has been
  rolled back.  Check the SQL Server error log for more details.  When
  the cause of the error has been resolved, retry the ALTER AVAILABILITY
  GROUP JOIN command.

https://docs.microsoft.com/en-us/sql/database-engine/availability-groups/windows/create-an-availability-group-sql-server-powershell?view=sql-server-2017#ExampleConfigureGroup

I tried the T-SQL command thru SSMS as well.

USE MASTER
ALTER AVAILABILITY GROUP <AG Name> JOIN;

Same error.

The default database for my login was set to 'TempDB'. Changed that to 'Master'. And there was a DNS registration issue with the first node. So the secondary replica wasn't able to communicate with the primary by computername. Correcting these two resolved the issue.

Join-SqlAvailabilityGroup worked properly.


Using 2 order by in procedure I need to use 2 order by in my procedure.

When I use only the second order by is working and not the first one.

What mistake am I doing?

Here is my procedure

BEGIN

SELECT
    sd.ShopID,
    @dist:= ( 6371 * acos( cos( radians(lat) ) * cos( radians( sd.Lat ) ) * cos( radians( sd.Lng ) - radians(lng) ) + sin( radians(lat) ) * sin( radians( sd.Lat ) ) ) ) AS distance
    FROM
        shop_details sd
    WHERE
        sd.Status = '1'
    Order by
        sd.Status Asc,
        distance Asc;
    END

(Don't care about the @dist formulae, as it will return the distance)

In the end I want to display the status as Ascending and distance as Ascending.

While I use

Order by
    sd.Status Asc,
    distance Asc

I got only distance as Asc and I got the sd.Status is not in ordered. I tried it vice versa but I got the same issue.

How can I do this without using a separate table like in this Question?

I tried this way.

Your use of ORDER BY is correct. However, You need to know that it has to go in order by how you specified! So your query will first order sd.Status FIRST, then it will order by distance SECOND.

For example This is how you're expecting it to look (Just my random data I gave)

 sd.Status   distance
 ---------   --------
     A         231
     A         245
     A         657
     A         987
     B         123
     B         432
     D         465
     D         576
     S         555

Note how it is ORDERED.


Why has my root password changed after I updated MySQL from 5.7 to 8.0? I have set my root account passwords like this:
mysql -u root
SET PASSWORD FOR 'root'@'localhost' = PASSWORD('pswd1');
SET PASSWORD FOR 'root'@'127.0.0.1' = PASSWORD('pswd2');
SET PASSWORD FOR 'root'@'::1' = PASSWORD('pswd3');

I had MySQL 5.6.47 Then I updated to 5.7.31 and everything was ok. Then I updated to 8.0.21 and immediately faced with 2 problems:

MySQL wouldn't start but end up with 1053 error. The problem was in missing Visual C++ Redistributable dll file.
In order to login into my root account I have to type the pswd3 password which had been set for the 'root'@'::1' account. pswd1 and pswd2 won't work anymore.

What may be the problem here?

MYSQL changed the default
If you chosen

Use Strong Password Encryption for Authentication (RECOMMENDED)

mysql 8.x uses the new authentication based on improved, stronger SHA256-based password methods.
https://dev.mysql.com/doc/mysql-installer/en/server-authentication-method.html
when you and your apps don't support it, you have to choose the legacy mode
But you can use user with the old authentficationwhen you use
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '<password>';

You have to add native password to all your  roo
'root'@'::1'   refers ipv6 localhost
'root'@'127.0.0.1'
And
'root'@'localhost'  zto ip V4
As you connect via 1pv6 change this apsword as well but use 'root'@'::1'`
localhoszt and 127.0.0.1 only work on a computer itself not over a network
how you can bind  the server to ipv4 or ipv6 is described here https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html
And as last poit see upgrade instructions in mysql, you can't only exchange the exe https://dev.mysql.com/doc/refman/8.0/en/upgrading.html


SQL Server query slow when paginated I am seeing some strange behaviour with the following T-SQL query in SQL Server 2012:
SELECT Id
FROM dbo.Person
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name

Executing this query alone gives me about 1,300 results in less than two seconds (there's a fulltext index on Name)
However, when I change the query to this:
SELECT Id
FROM dbo.Person
WHERE CONTAINS(Name, '"John" AND "Smith"')
ORDER BY Name
OFFSET 0 rows
FETCH NEXT 10 ROWS ONLY

It takes more than 20 seconds to give me 10 results.
The following query is even worse:
SELECT Id
FROM (
    SELECT ROW_NUMBER() OVER (ORDER BY Name) AS RowNum, Id
    FROM dbo.Person
    WHERE CONTAINS(Name, '"John" AND "Smith"') ) AS RowConstrainedResult
WHERE RowNum >= 0 AND RowNum < 11
ORDER BY RowNum

It takes more than 1.5 minutes to complete!
Any ideas?
Slow plan

Fast plan

As you just want the TOP 10 ordered by name it thinks it will be quicker to work down the index on name in order and look to see if each row matches the CONTAINS(Name, '"John" AND "Smith"') ) predicate.

Presumably it takes many more rows to find the 10 matches required then it expects and this cardinality issue is compounded by the number of key lookups.

A quick hack to stop it using this plan would be to change the ORDER BY to ORDER BY Name + '' although using CONTAINSTABLE in conjunction with FORCE ORDER should also work.


Select count date from multiple where conditions for each day I have lot of coupons. I would like to learn for each day how many coupons from each campaign have been received by users. But I cannot write something like assigned_date for each select row

SELECT count(id)                                                  as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')                     as date,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 1 AND assigned_date=THIS MUST BE SOMETHING) as campaign_1,
       (SELECT COUNT(id) FROM coupon WHERE campaign_id = 2 AND assigned_date=THIS MUST BE SOMETHING) as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC

So the result will be something like. How can I achieve this result?

+-------------------+------------+-------------+-----------+
| number of coupons |    date    |  campaign_1 | campaign2 |
+-------------------+------------+-------------+-----------+
|               156 | 12-10-2019 |        6980 |       100 |
|               177 | 11-10-2019 |        6980 |       100 |
|                44 | 10-10-2019 |        6980 |       100 |
|                94 | 09-10-2019 |        6980 |       100 |
|                93 | 08-10-2019 |        6980 |       100 |
+-------------------+------------+-------------+-----------+

SELECT count(id)                                       as number_of_coupons,
       DATE_FORMAT(assigned_date, '%d-%m-%Y')          as date,
       SUM(CASE WHEN campaign_id=1 THEN 1 ELSE 0 END)  as campaign_1,
       SUM(CASE WHEN campaign_id=2 THEN 1 ELSE 0 END)  as campaign_2
FROM coupon
GROUP BY DATE_FORMAT(assigned_date, '%d-%m-%Y')
order by STR_TO_DATE(date, '%d-%m-%Y') DESC


How can I send mail from a contained database is SQL Server? How can I send mail with a user within a contained database is SQL Server using Database Mail?

I tried using msdb.dbo.sp_send_dbmail but since any users local to the contained database do not see msdb, therefore msdb.dbo.sp_send_dbmail is unreachable as well. Then I tried creating certificates and signing procedures, first going straight to msdb and then re-routing the whole stuff through the master database (I know, it's not a 'very good idea') but even that does not work (I suppose it would break the containment, hence not allowed).

Is there a way do that at all?

The following solution involves the creation of a certificate to sign a newly created stored procedure in a contained database that emulates the functionality of msdb.dbo.sp_send_mail.  This has worked successfully in my testing and hopefully, it will work for you.

-----------------------------------------------------------------------------
--This example expects an empty directory called C:\ContainedDatabaseExample
--Make sure no files exist in the target directory first
-----------------------------------------------------------------------------

USE [master]
GO
--Cleanup from previous runs of this process
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
--===========================================================================
--Create a new 'contained' database for testing
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
IF  EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'Contained')
    DROP DATABASE [Contained]
GO
CREATE DATABASE [Contained]
 CONTAINMENT = PARTIAL
 ON  PRIMARY
(
NAME = N'Contained',
FILENAME = N'C:\ContainedDatabaseExample\Contained.mdf' ,
SIZE = 1024KB ,
MAXSIZE = UNLIMITED,
FILEGROWTH = 1024KB
)
 LOG ON
(
NAME = N'Contained_log',
FILENAME = N'C:\ContainedDatabaseExample\Contained_log.ldf' ,
SIZE = 1024KB ,
MAXSIZE = 1024GB ,
FILEGROWTH = 1024KB
)
GO
ALTER DATABASE [Contained] SET COMPATIBILITY_LEVEL = 130
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [Contained].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [Contained] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [Contained] SET ANSI_NULLS OFF
GO
ALTER DATABASE [Contained] SET ANSI_PADDING OFF
GO
ALTER DATABASE [Contained] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [Contained] SET ARITHABORT OFF
GO
ALTER DATABASE [Contained] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [Contained] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [Contained] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [Contained] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [Contained] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [Contained] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [Contained] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [Contained] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [Contained] SET  DISABLE_BROKER
GO
ALTER DATABASE [Contained] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [Contained] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [Contained] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [Contained] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [Contained] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [Contained] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [Contained] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [Contained] SET RECOVERY FULL
GO
ALTER DATABASE [Contained] SET  MULTI_USER
GO
ALTER DATABASE [Contained] SET PAGE_VERIFY CHECKSUM
GO
ALTER DATABASE [Contained] SET DB_CHAINING OFF
GO
ALTER DATABASE [Contained] SET DEFAULT_FULLTEXT_LANGUAGE = 1033
GO
ALTER DATABASE [Contained] SET DEFAULT_LANGUAGE = 1033
GO
ALTER DATABASE [Contained] SET NESTED_TRIGGERS = ON
GO
ALTER DATABASE [Contained] SET TRANSFORM_NOISE_WORDS = OFF
GO
ALTER DATABASE [Contained] SET TWO_DIGIT_YEAR_CUTOFF = 2049
GO
ALTER DATABASE [Contained] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [Contained] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [Contained] SET DELAYED_DURABILITY = DISABLED
GO
ALTER DATABASE [Contained] SET QUERY_STORE = OFF
GO
USE [Contained]
GO
ALTER DATABASE SCOPED CONFIGURATION SET MAXDOP = 0;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET MAXDOP = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET LEGACY_CARDINALITY_ESTIMATION = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET LEGACY_CARDINALITY_ESTIMATION = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET PARAMETER_SNIFFING = ON;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET PARAMETER_SNIFFING = PRIMARY;
GO
ALTER DATABASE SCOPED CONFIGURATION SET QUERY_OPTIMIZER_HOTFIXES = OFF;
GO
ALTER DATABASE SCOPED CONFIGURATION FOR SECONDARY SET QUERY_OPTIMIZER_HOTFIXES = PRIMARY;
GO
ALTER DATABASE [Contained] SET  READ_WRITE
GO

--Use the 'contained' database
USE Contained
GO

--Create a new user in the contained database
CREATE USER [ContainedUser] WITH PASSWORD=N'BaconLover1', DEFAULT_SCHEMA=[dbo]
GO

--Create a new role that allows for easy additions of users to
--execute the 'contained' database new email procedure
CREATE ROLE [SendMailFromContainedDatabase]
GO

--Add the new contained database user to the database role
ALTER ROLE [SendMailFromContainedDatabase] ADD MEMBER [ContainedUser]
GO

--Create a new procedure in the Contained database that
--emulates all of the functionality of msdb.dbo.sp_send_dbmail
--My example below doesn't take any parameters and hardcodes
--enough information to send a test email.
--The actual procedure would need all of the normal parameters
--that msdb.dbo.sp_send_dbmail uses

CREATE PROCEDURE ContainedSendMail
    (@ValidEmailAddress varchar(100))
    WITH EXECUTE AS OWNER
AS
BEGIN
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SqlServerEmailProfile'
        ,@recipients = @ValidEmailAddress
        ,@subject = 'From Contained Database'
        ,@importance = 'High';
END
GO

--Create a certificate in the contained database that will be
--used to sign the new email stored procedure
CREATE CERTIFICATE [DatabaseMailFromContainedDatabases]
    ENCRYPTION BY PASSWORD = 'Password#1234'
    WITH SUBJECT = 'DatabaseMailFromContainedDatabases';
GO

--Sign the new email stored procedure with the certification
ADD SIGNATURE TO OBJECT::[ContainedSendMail]
BY CERTIFICATE [DatabaseMailFromContainedDatabases]
WITH PASSWORD = 'Password#1234';
GO

--Remove the PRIVATE KEY because it's not really needed
ALTER CERTIFICATE [DatabaseMailFromContainedDatabases]
REMOVE PRIVATE KEY;
GO

--Grant permissions to the database role to execute the
GRANT EXECUTE ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO
GRANT VIEW DEFINITION ON ContainedSendMail TO [SendMailFromContainedDatabase]
GO

--Backup the newly created certificate from the contained database
BACKUP CERTIFICATE [DatabaseMailFromContainedDatabases]
TO FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--Now, we need to import the 'contained' certificate into master
USE master
go
IF (select Count(*) from sys.certificates where name = 'DatabaseMailFromContainedDatabases') = 1
    DROP CERTIFICATE DatabaseMailFromContainedDatabases;
create CERTIFICATE [DatabaseMailFromContainedDatabases]
from FILE = 'C:\ContainedDatabaseExample\DatabaseMailFromContainedDatabases.CER';
GO

--We need to associate the new certificate with a server login
--and grant permissions
USE [master]
GO
IF EXISTS (SELECT loginname FROM master.dbo.syslogins
    WHERE NAME = 'DatabaseMailFromContainedDatabases')
    DROP LOGIN [DatabaseMailFromContainedDatabases]
GO
CREATE LOGIN [DatabaseMailFromContainedDatabases]
FROM CERTIFICATE [DatabaseMailFromContainedDatabases];
GRANT AUTHENTICATE SERVER TO [DatabaseMailFromContainedDatabases];
GO

--Add the new login to the MSDB DatabaseMailUserRole
USE [msdb]
GO
IF  EXISTS (SELECT * FROM sys.database_principals WHERE name = N'DatabaseMailFromContainedDatabases')
    DROP USER [DatabaseMailFromContainedDatabases]
CREATE USER [DatabaseMailFromContainedDatabases] FOR LOGIN [DatabaseMailFromContainedDatabases]
ALTER ROLE [DatabaseMailUserRole] ADD MEMBER [DatabaseMailFromContainedDatabases]
GO

At this point, you 'should' be able to connect as the 'Contained' database user id (that was created in the example script above) and run

exec ContainedSendMail @ValidEmailAddress = 'YourEmailAddress'

making sure you provide a valid email address.  After a minute or so, I received the test email.


Does Write-Ahead-Logging allow me to make concurrent changes to a table? In Sqlite3, Write-Ahead-Logging mode allows one process to read a table, while another is writing to it. It doesn't say anything about one process writing and another deleting/updating.

Is this possible in Sqlite3?

A DELETE and/or an UPDATE are both writes!

Only one process can write at any one time - that is an INSERT, an UPDATE or a DELETE at the same time. You can SELECT with as many processes as you like, but only one of the others at a time!


Set Backup Compression on SQL Server 2008R2 on by default I can't seem to find the option to set the server property that will enable compression on SQL database backup to be ON by default, so  I don't have to manually set it every time.

Can anyone point me at that please.

Technet Online says:

  To view or configure the backup compression default option

  In Object Explorer, right-click a server and select Properties.

  Click the Database settings node.

  Under Backup and restore, Compress backup shows the current setting of the backup compression default option. This setting determines the server-level default for compressing backups, as follows:

  If the Compress backup box is blank, new backups are uncompressed by default.

  If the Compress backup box is checked, new backups are compressed by default.

  If you are a member of the sysadmin or serveradmin fixed server role, you can also change the default setting by clicking the Compress backup box.

from: http://msdn.microsoft.com/en-us/library/bb933863.aspx


auto_increment incremented on non-affecting queries When performing the following query on 'sessions':

INSERT IGNORE INTO sessions (user_id, archived) VALUES (3, 0)

while:

CREATE TABLE `sessions` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `user_id` bigint(20) unsigned NOT NULL,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `date_modified` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `archived` tinyint(3) unsigned NOT NULL,
  `valid_until` datetime NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`),
  UNIQUE KEY `user_id_UNIQUE` (`user_id`),
  KEY `fk_sessions_users1_idx` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

with trigger:

CREATE TRIGGER before_insert_on_sessions BEFORE INSERT ON `sessions`
FOR EACH ROW SET new.valid_until = IFNULL(new.valid_until,DATE_ADD(NOW(), INTERVAL 1 DAY));

the first time you execute the query, everything is fine and the following is inserted:

id  user_id date_created    date_modified   archived    valid_until
1   3       ...             ...             0           ...

now enter the same query two more times, and it will say:

No errors, 0 rows affected, taking 8,0ms

Now change the query to:

INSERT IGNORE INTO sessions (user_id, archived) VALUES (4, 0)

So basically you only changed the value of user_id.

You'd expect the next row to be:

id  user_id date_created    date_modified   archived    valid_until
**2**   4       ...             ...             0           ...

instead, you get:

id  user_id date_created    date_modified   archived    valid_until
**4**   4       ...             ...             0           ...

So basically what's happening, is that the unique_key (id)'s auto_increment is being updated on every query, even though there are no actual new rows being made.

This shouldn't be right, should it?

Server version:     5.6.22 Homebrew

This shouldn't be right, should it?

It's right.

Databases (not just MySQL) generally ensure that sequences will only ever increase (once a transaction is committed), but not that they will be without gaps. For instance, TFM says:

  You may see gaps in the sequence of values assigned to the AUTO_INCREMENT column if you roll back transactions that have generated numbers using the counter.

and:

  As of MySQL 5.1.22, InnoDB can avoid using the table-level AUTO-INC lock for a class of INSERT statements where the number of rows is known in advance, and still preserve deterministic execution and safety for statement-based replication. Further, if you are not using the binary log to replay SQL statements as part of recovery or replication, you can entirely eliminate use of the table-level AUTO-INC lock for even greater concurrency and performance, at the cost of permitting gaps in auto-increment numbers assigned by a statement and potentially having the numbers assigned by concurrently executing statements interleaved.

Consider the case where two uncommitted transactions exist in an MVCC system like InnoDB. They may both need a unique primary key before being committed, but the first transaction may fail. Hence you will end up with a gap.

You can't avoid this behaviour, so embrace your gappy primary keys :)

https://dev.mysql.com/doc/refman/5.1/en/innodb-auto-increment-handling.html


Skewed Clustered Index I have a SQL Server database that has a pretty important table (join wise) that contains just over 2.1 million rows. For the VARCHAR column that defines the clustered index, 17.5k rows contain unique values, the remaining rows all contain exactly the same non null value. There are some queries that start at this table restricting by this column, expecting to get just one row, so that probably helps performance, but for many queries we are accessing via other fields and nearly always joining by a unique, non nullable (BIGINT) primary key (which is obviously non clustered).

I guess my question is, in light of anything else is it a no-brainer to change the clustered index to be the primary key (in light of there not being a more suitable candidate). Is there any check that I could do to try and predict the outcome (spinning up an exact replica and testing in a real world way is not an option)

I know that any changes I make will require a table rebuild (if I'm changing the clustered key)

Primary Key and Clustered Index are really separate concepts and, although many tables put both of these attributes on the same constraint or index, this is not a requirement.

I do not see how changing the Clustered Index to the make it also the Primary Key will particularly help your performance.

It sounds like you have a Primary Key (BIGINT) already and that it is physically implemented through a unique non-clustered index.  This should be good for fairly quick lookups of the Primary Key since it would be a relatively narrow index.

As you have it defined now, the skewed Clustered Index is mostly the same special non-NULL value, with about 17.5K rows.

Since you identify the Clustered Index as a likely pain point, you might look into whether a Filtered Index or an INDEXED VIEW would provide the needed performance improvement.

Sample of a Filtered View based on MSDN, like this:

CREATE NONCLUSTERED INDEX YourNonClusteredFilteredIndex
    ON dbo.YourTable (ClusterKeyCol)
    WHERE ClusterKeyCol IS NOT NULL; -- Or <> {special value}
GO

Sample of an Index View based on MSDN, something like this:

SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
CREATE VIEW dbo.YourClusteredView
WITH SCHEMABINDING
AS
    SELECT ClusterKeyCol, PrimaryKeyCol
    FROM dbo.YourTable
    WHERE PrimaryKeyCol <> {special value};
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX YourClusteredIndex
    ON dbo.YourClusteredView (ClusterKeyCol);
GO

If you have a more detailed problem description, as John M asked, then perhaps some more targeted help can be provided.


Can mysqldump dump triggers and procedures? Is there any way of making a mysqldump which will save all the triggers and procedures from a specified db?

Some time ago I read that mysqldump will also save my triggers, but it doesn't look like it. My second related question is how can I check in a sql file if triggers exists?

I normally do not separate triggers from the tables they were meant for. I dump like this:

mysqldump -u... -p... --no-data --routines --triggers dbname > DBSchema.sql

Check for presence of routines and triggers like this:

SELECT COUNT(1) FROM mysql.proc;

SELECT COUNT(1) FROM information_schema.triggers;

SELECT * FROM information_schema.triggers\G

If you want to get this done to all DBs in the MySQL Instance, do this:

mysql -u... -p... -A -N -e"SELECT schema_name FROM information_schema.schemata WHERE schema_name NOT IN ('information_schema','mysql')" > /tmp/dblist.txt
for DB in `cat /tmp/dblist.txt`
do
    mysqldump -u... -p... --no-data --no-create-info --routines dbname > ${DB}-routines.sql
    mysqldump -u... -p... --no-data --triggers dbname > ${DB}-schema-triggers.sql
done

That way, stored procedures go in a routines dump for the DB, while the schema and triggers go in another dump.


How to automatically backup a MySQL/Amazon RDS database? I would like to get automatically MySQL backups to sync them to another provider as secondary backup.

The RDS Snapshots itself are not useful in this case because the are "hidden" in AWS and can only be restored in RDS.

Maybe there is already a working solution to get MySQL dumbs to S3.

This is not possible using RDS snapshots.  AWS RDS Snapshots are "hidden" (though almost certainly in S3).  They are not accessible to you, the user, at all.  They can only be restored to RDS.

The only way to get data out from RDS MySQL instances is to export it using mysqldump (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Importing.SmallExisting.html is a guide to importing, but works equally well for exporting), or by configuring a MySQL replica (http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html).


Why would a procedure sometimes hang executing SELECT...INTO #Temp2 FROM #Temp1 JOIN LocalTable...? We have several stored procedures, called by jobs, that follow a specific pattern (designed to minimize blocking). Some of these procs tend to hang on occasion, running up the CPU, tempdb allocations, reads, and writes indefinitely until killed. The pattern is:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
CREATE #RemoteTemp...;
INSERT INTO #RemoteTemp EXEC MyLinkedServer.DbName.SchemaName.ProcName;
SET TRANSACTION ISOLATION LEVEL SNAPSHOT;
SELECT...INTO #MixedTemp FROM #RemoteTemp JOIN LocalTable...;

We then make use of #MixedTemp. However, that last statement is the one that sometimes runs in less than one second and sometimes hangs even though the row counts are the same at every execution. What might cause that?

Additional info:

1117 and 1118 are enabled.
8 tempdb files for this 8 core instance.
No tempdb autogrowth is occurring.

During the hang:

Sch-S locks on the local tables.
On the temp table being read:

Lock resource_type="HOBT.BULK_OPERATION" request_mode="S"
request_status="GRANT" request_count="1" Lock resource_type="OBJECT"
request_mode="X" request_status="GRANT" request_count="1"

On the temp table being inserted into:

Lock resource_type="OBJECT"
 request_mode="X" request_status="GRANT" request_count="1"

From sp_lock (6 and 7 are databases being joined to the temp table, 2 is tempdb):

spid   dbid   ObjId       IndId  Type Resource                         Mode     Status
------ ------ ----------- ------ ---- -------------------------------- -------- ------
96     6      0           0      DB                                    S        GRANT
96     7      0           0      DB                                    S        GRANT
96     6      702625546   0      TAB                                   Sch-S    GRANT
96     2      -1219105587 0      HBT  [BULK_OPERATION]                 S        GRANT
96     7      98099390    0      TAB                                   Sch-S    GRANT
96     2      -1219105587 0      TAB                                   X        GRANT
96     2      -1203105530 0      TAB                                   X        GRANT
96     7      115775811   0      TAB                                   Sch-S    GRANT
96     7      194099732   0      TAB                                   Sch-S    GRANT

The bad plan XML showplan provided shows that two tables in the query have an indicated zero rows (other objects in the plan are fine):

captured from SQL Sentry Plan Explorer's Plan Tree view

This may be due to a problem automatically updating statistics, or perhaps due to some quirk of running under snapshot isolation. There really isn't enough information yet to say. In any case, this is something you should investigate.

If the table & index statistics really are missing (or empty), manually building these will will almost certainly resolve the problem you are seeing.

Given incorrect information, the query optimizer generates a plan featuring nested loop joins:

This plan has the potential to execute for a long time when the runtime row counts greatly exceed those planned for by the QO. The query isn't blocked; it is simply continuing to execute using a flawed strategy.

The 'good plan', generated when reasonable statistics are available, produces an execution plan that uses hash joins and parallelism:


Improve performance of sys.dm_db_index_physical_stats During a maintenance job, I'm trying to get a list of fragmented indexes. But the query is extremely slow and takes over 30 minutes to execute. I think this is due to a remote scan on sys.dm_db_index_physical_stats.

Is there any way to speed up the following query:

SELECT
    OBJECT_NAME(i.OBJECT_ID) AS TableName,
    i.name AS TableIndexName
FROM
    sys.dm_db_index_physical_stats(DB_ID(), NULL, NULL, NULL, 'DETAILED') phystat
    INNER JOIN sys.indexes i
        ON i.OBJECT_ID = phystat.OBJECT_ID AND i.index_id = phystat.index_id
WHERE
    phystat.avg_fragmentation_in_percent > 20
    AND OBJECT_NAME(i.OBJECT_ID) IS NOT NULL
ORDER BY phystat.avg_fragmentation_in_percent DESC

I'm not a DBA and could be making an obvious mistake in the query above, or maybe there are some indexes or statistics that would help? Maybe it's just the size of the database (around 20Gb with about 140 tables).

The reason I ask is that we only have a very small window for maintenance during the night and this is taking up most of the time.

'DETAILED' implies a full scan of every single page in the index (or heap). Do this for every table and every secondary index, the result means you are doing a full database scan, end to end, and not a very efficient one (ie. not nearly as fast as backup would read it, for instance). The time is driven by:

how big your database is
how fast your IO subsytem is to read the entire database
aditional concurent load competing for the IO throughput

Basically, if all you have is a straw (your IO throughput) it takes 30 minutes to drink a bucket (your database size). Buy faster IO, reduce the size of your data, or use SAMPLED scans.

That being said... 20Gb is quite small. 30 minutes to read 20Gb is a lot of time. Is you IO subsystem that slow? Did you deploy on 7200 RPM consumer 1TB drives?


ORA-00379 no free buffers I'm trying to do collection_manager in OVPI but I'm getting db error

  ORA-00379 no free buffers available in buffer pool default for block
  size 32k
  oracle 10.2

Thanks for any help in advance

This is happening because your default database block size is set to something other than 32k, and as a result there's no 32k cache buffers available.

To fix this, add:

#change 1024M to the desired amount of cache
db_32k_cache_size=1024M

... to the init.ora for your database instance & then restart the database.

You may also be able to change this without restarting (as sysdba from sqlplus):

ALTER SYSTEM SET DB_32K_CACHE_SIZE=1024M;