Merge jsonb objects and sum their values over multiple rows I save visits to my database. In my database I have a table with aggregations of visits called visits_agg. This table has one row per hour per hostname. Both are fields in the table which will be used to query the rows needed for viewing statistics.

For example I will query for the visits in the last 24 hours:

SELECT * FROM visits_agg WHERE hostname = 'example.com' AND hour > NOW() - INTERVAL '24 hours'

To create the structure I used this query:

CREATE TABLE IF NOT EXISTS visits_agg (
  id          serial primary key,
  hour        timestamp without time zone default (now() at time zone 'utc'),
  hostname    text not null,
  visits      integer default 0,
  referrers   jsonb not null default '{}'::jsonb,
  paths       jsonb not null default '{}'::jsonb,
  widths      jsonb not null default '{}'::jsonb
);

With some dummy data:

INSERT INTO "visits_agg" ("hour", "hostname", "visits", "referrers", "paths", "widths") VALUES
('2019-01-16 09:00:00', 'example.com', '6', '{"example2.com": 4}', '{"/": 5, "/about": 1}', '{"1280": 5, "1283": 1}'),
('2019-01-16 14:00:00', 'example.com', '7', '{"google": 6}', '{"/": 1, "/about": 3, "/article/title1": 1, "/article/title2": 2}', '{"360": 3, "1398": 1, "1680": 1, "2560": 1}'),
('2019-01-16 15:00:00', 'example.com', '6', '{"google": 6}', '{"/about": 1, "/article/title1": 1, "/article/title2": 3, "/": 1}', '{"360": 3, "1280": 2, "1920": 1}'),
('2019-01-16 16:00:00', 'example.com', '6', '{"facebook": 1, "example2.com": 1}', '{"/": 4, "/about": 1, "/article/title1": 1}', '{"360": 1, "1440": 2, "1680": 2, "1920": 1}');

I would like to get the rows from a certain period and sum all referrers, paths, and widths.

So the result of the above records should be something like this

'example.com',
'25',
'{"example2.com": 4, "google": 12, "facebook": 1}',
'{"/": 11, "/about": 6, "/article/title1": 3, "/article/title2": 5}',
'{"360": 7, "1398": 1, "1440": 2, "1680": 3, "2560": 1, "1280": 7, "1283": 1, "1920": 2}'),

The keys in the jsonb objects can be any string and the values are always numbers. I tried to merge the objects, but can't figure out how to sum the values inside the jsonb objects.

As I pointed out in comments I think this job would be easy to accomplish working with a disaggregated data, because now IMHO you must disaggregate it again:

For example to get aggregated values for referrers:

SELECT
    hostname,
    r.key as ref_name,
    r.value::int as ref_value
FROM
    visits_agg
CROSS JOIN LATERAL
    jsonb_each(referrers) r;

hostname    | ref_name     | ref_value
:---------- | :----------- | --------:
example.com | example2.com |         4
example.com | google       |         6
example.com | google       |         6
example.com | facebook     |         1
example.com | example2.com |         1

Now you can get acumulated values grouped by ref_name, and then you must build the jsonb array again.

SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
          FROM   visits_agg
          CROSS JOIN LATERAL jsonb_each(referrers) r
          GROUP BY hostname, r.key) t1
GROUP BY hostname;

This is my approach, perhaps some Postgres guru can give you a better answer.

SELECT row_to_json(t0, true)
FROM
    (
    SELECT t1.hostname, t1.visits, t2.referrers, t3.paths, t4.widths
    FROM   (SELECT   hostname, SUM(visits) as visits
            FROM     visits_agg
            GROUP BY hostname) t1
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(ref_name, ref_value)) as referrers
            FROM     (SELECT hostname, r.key as ref_name, SUM(r.value::int) as ref_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(referrers) r
                      GROUP BY hostname, r.key) t1
           GROUP BY hostname) t2
           ON t1.hostname = t2.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(path_name, path_value)) as paths
            FROM     (SELECT hostname, p.key as path_name, SUM(p.value::int) as path_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(paths) p
                      GROUP BY hostname, p.key) t1
            GROUP BY hostname) t3
           ON t1.hostname = t3.hostname
    JOIN   (SELECT   hostname, jsonb_agg(jsonb_build_object(width_name, width_value)) as widths
            FROM     (SELECT hostname, w.key as width_name, SUM(w.value::int) as width_value
                      FROM   visits_agg
                      CROSS JOIN LATERAL jsonb_each(widths) w
                      GROUP BY hostname, w.key) t1
            GROUP BY hostname) t4
           ON t1.hostname = t4.hostname
    ) t0;

And this is the result:

{
 "hostname":"example.com",
 "visits":25,
 "referrers":[{"google": 12}, {"facebook": 1}, {"example2.com": 5}],
 "paths":[{"/about": 6}, {"/article/title2": 5}, {"/article/title1": 3}, {"/": 11}],
 "widths":[{"1680": 3}, {"1398": 1}, {"2560": 1}, {"360": 7}, {"1280": 7}, {"1440": 2}, {"1920": 2}, {"1283": 1}]
}

db<>fiddle here


Deadlock - Due to update causing Index page movement within an index I am still troubleshooting deadlock mentioned in this questions.  In that I got help in understanding why a select would hold on to a shared lock and I was able to test and confirm that also.

Now I am looking at second spid involved in the deadlock. That session does an update using a primary key(WHERE BatchId  = @BatchI , BatchId is the PK) and it needs IX on two pages.  Initially I couldn't figure out why it needed locks on two pages of a non clustered index when it was just updating one row.  Then I realized that the update to that row would change the location of the row in the non clustered index and that could be reason why it needs locks on two pages.

Is that correct?

Secondly, please help confirm my understanding of this deadlock is correct.
For simplicity sake lets assume that here are only two pages in that non clustered index.  Page1 and Page2.  So is this what is roughly happening

So did some testing and the deadlock is in fact caused by page movement within the non clustered index caused by an update which changes the position of the row in the index.

So initially Session 2 (update) takes out IX lock on Page2 where the index row is originally located.  Since the update would change the value and in turn change the position of the index row within the index, session 2 will also want to take out another IX lock on the target page where the index row will end up after the update.

For understanding the reason why a select would want to hold on to S lock please refer to this question.

REPO

set nocount on
create table deadlck ( a int , b int, c char (3), constraint PKA primary key (a))
create index idx on deadlck(b)
--creating a big enough table so that selects use page lock instead of row lock
declare @a int =1
declare @b int =57800000
while 1=1
begin
insert into deadlck select @a,@b,'111'
if (@a%521) =520 -- to make idx index pages fit one value in each page perfectly,  not necessary, just easier to simulate the issue while testing
set @b=@b-1
Set @a=@a+1
if @a=52100
break
end

ALTER INDEX idx ON deadlck
REBUILD WITH (FILLFACTOR = 90); -- same reason as above, i did the math with a fill factor of 90 to have the pages of idx index have one value per page and and also the pages needed to have some space so when we do the update the index entry can move to another page without have to do a page split

ALTER INDEX PKA ON deadlck
REBUILD WITH (FILLFACTOR = 90);

--session 1
while 1=1
begin
begin tran
update deadlck set b=57799999 where a=2
rollback
end

--session 2
while 1=1
begin
begin tran
select b from deadlck with(paglock) where (b =57799999 or b=57800000)
rollback
end


SQL Server 2012 - sp_executesql multiple parameter error I am receiving an error when attempting to execute the following code that builds a dynamic string to be executed by sp_executesql.

DECLARE @paramDefinition NVARCHAR(500);
DECLARE @ID1 NVARCHAR(20);
DECLARE @StageTable1 NVARCHAR(100);
DECLARE @StageTable2 NVARCHAR(100);

SET @ID1 = '111111111111111111';
SET @StageTable1 = 'TestTable1';
SET @StageTable2 = 'TestTable2';

DECLARE @sqlToExecute NVARCHAR(MAX)

SET @tableCreateSQL = 'SELECT @ID1
                       INTO   @StageTable1
                       FROM   @StageTable2 ';

SET @paramDefinition = '@ID1 NVARCHAR(20),
                        @StageTable1 NVARCHAR(100),
                        @StageTable2 NVARCHAR(100)';

EXEC sys.sp_executesql @tableCreateSQL,
                       @paramDefinition,
                       @ID1,
                       @StageTable1,
                       @StageTable2;

The error I receive states the following:

Incorrect syntax near '@StageTable1'.

Am I incorrectly constructing the SQL string, or is there an issue with the sp_executesql statement?

You can't parameterize column and table names, sorry. You'll need to do this the old way:

SET @tableCreateSQL = N'SELECT ' + QUOTENAME(@ID1) + N'
               INTO   dbo.' + QUOTENAME(@StageTable1) + N'
               FROM   dbo.' + QUOTENAME(@StageTable2) + N';';

Also see:

Protecting Yourself from SQL Injection in SQL Server - Part 1
Protecting Yourself from SQL Injection in SQL Server - Part 2


Reason for having additional "type" table of all possible string values? I've been looking over some sample schemas, and here's something I've seen done several times, but being new to MySQL I'm not sure why. Say I've got a table for items:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemID),
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemType) REFERENCES itemType(name)
) ENGINE = MyISAM;

Why would you need itemType to be a foreign key to another table that lists all the possible item types? Would it prevent typos if the entered value wasn't in the other table? The other table is this:

CREATE TABLE itemType (
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (name),
) ENGINE = MyISAM;
INSERT INTO itemType (name) VALUES ('gloves');
INSERT INTO itemType (name) VALUES ('boots');
INSERT INTO itemType (name) VALUES ('ring');

It seems the idea here is to restrict what itemTypes can be used in the item table to only those in the itemType table, while at the same time avoiding having to do a JOIN with the itemType table to get the actual value whenever you SELECT from the item table. It's a kind of denormalization in order to optimize SELECT statements. This comes at a cost of needing more storage space since every item record will have the full itemType name, potentially as long as 255 characters.

With a normalized approach you would have tables defined like this:

CREATE TABLE item (
  ...
  itemTypeID int unsigned NOT NULL,
  ...
  CONSTRAINT fk_item_itemType FOREIGN KEY (itemTypeID) REFERENCES itemType(itemTypeID)
) ENGINE = MyISAM;

CREATE TABLE itemType (
  itemTypeID int unsigned NOT NULL,
  name VARCHAR(255) NOT NULL,
  PRIMARY KEY (itemTypeID),
) ENGINE = MyISAM;

However, when SELECTing from the item table you would now have to do a JOIN in order to display the itemType:

SELECT i.itemID, i.name, it.name "itemTypeName"
FROM item i
  INNER JOIN itemType it USING (itemTypeID);

Another solution to this is using an ENUM and avoid the itemType table altogether:

CREATE TABLE item (
  itemID CHAR(5) NOT NULL,
  name VARCHAR(255) NOT NULL,
  itemType ENUM('gloves', 'boots', 'ring') NOT NULL,
  PRIMARY KEY (itemID)
) ENGINE = MyISAM;

The drawback of this approach is that you would have to ALTER the table whenever you want to add a new itemType.


postgresql different configuration for different tablespace I'm configuring new server with both SSD/HDD disk with ubuntu 18.04. I've installed system in SSD and created two different tablespaces one in ssd and one in hdd. I would like for configuration purposes put:

HDD

random_page_cost 4
effective_io_concurrency 2

SDD

random_page_cost 1.1
effective_io_concurrency 200

for each correct tablespace. Is it possible or are there some suggestions?

See the docs for ALTER TABLESPACE.

Since version 9.6, you can set "effective_io_concurrency" on a per-tablespace basis. You can do so for "random_page_cost" long before 9.6.

These changes have to be made inside the database itself. For discoverability purposes, I would also add a comment inside postgresql.conf near the global settings, saying that they have been tweaked per tablespace.  Otherwise, it is not very obvious that these changes have been made.


Is there a way to see if database has been accessed through SQL Injection attack? I recently discovered that my website had a page with a SQL Injection vulnerability. Upon testing, it was easily exploitable with a pentesting tool like sqlmap. How do I figure out if the site database has been accessed with a SQL Injection attack, possibly exploiting this loophole? The server is running Apache/2.4.18 and MySQL Ver 14.14 Distrib 5.7.16.

Since you log all queries against the database, look for queries that don't belong.

Start with queries that failed. Assuming your app isn't riddle with bugs, there should be very few queries submitted to the DB that fail. Attackers typically have a set of queries they execute once they find a vulnerable app. If you apply the principle of least privileges on accounts used to access the DB, you should find a bunch of failed queries. There will also be queries that fail because a less sophisticated attacker might have a bunch of queries that are not compatible with your database edition, version or vendor.

Next, look for queries/commands that could not have been generated by your application. For instance, if the application should never accesses system tables, system functions, information schema, etc... but you see them in your log, that's a good indicator something might be amiss and warrants deeper investigation. Looking at your OS logs might be helpful here in case they tried or succeeded in planting a backdoor/rootkit.

Once you're done with that, look for queries with unusual/unexpected behavior. For instance, queries that return significantly more data than normal. If you see queries that access all tables around the same time or ran show tables then accessed each table in order, there's a good chance you have an attacker that tried (or succeeded) to exfiltrate data. Even something seemingly innocuous like SELECT * without a WHERE clause is suspicious (assuming your app doesn't do this). Note that this applies to any possible query, not just SELECT. The attacker might be some disgruntled employee and decided to randomly DELETE or UPDATE data to get you in trouble with your auditors or worse.

Looking at IP addresses is often not helpful even if you're looking at the web server logs unless you have a narrow and/or well known band of IP addresses your users connect from. Few attackers do it from their home PC anymore. Bots and proxies are found all over the world plus IPs are easily spoofed. Unless you get your ISP or Telco provider involved, it's unlikely you'll get very far looking at your own logs. One better indicator is your corporate firewall or network logs to find unusual outbound IP targets. An attacker that exfiltrates data has to send it to some real endpoint. That is usually to a place that isn't cooperative and/or moves slowly when foreign companies or law enforcement comes knocking.


Repairing Broken Binary Replication in PostgreSQL 9.0 I have a binary replication that was stopped for so long that the WALs were removed and as a result, it ended up being old. I'm trying to reestablish the replication and the best way I've found so far is following the steps on the PostgreSQL wiki:

Issue select pg_start_backup('clone',true); on master
rsync everything except for pg_xlog from master to slave
Issue select pg_stop_backup(); on master
rsync pg_xlog

But the database is too big (300 GB), my connection is not really amazing (like 800 kB/s) and the files in base keep changing. So I was wondering if there's a more optimal way to do this.

Database files and transaction logs tend to be very fat , so compression makes a big difference try rsync -z or compress the data directory beforehand. Try with  xz (former lzma) you wont believe the compression ratio for postgres files.


SQL 2005 find duplicate indexes I've created the following script to pull up duplicate indexes on my DB but I need to find a way to implement it across all the databases on the SQL server - any suggestions?`

with indexcols as
(

select object_id as id, index_id as indid, name,
(select case keyno when 0 then NULL else colid end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by keyno, colid
for xml path('')) as cols,
(select case keyno when 0 then colid else NULL end as [data()]
from sys.sysindexkeys as k
where k.id = i.object_id
and k.indid = i.index_id
order by colid
for xml path('')) as inc
from sys.indexes as i
)
select
object_schema_name(c1.id) + '.' + object_name(c1.id) as 'table',
c1.name as "index",
c2.name as "exactduplicate"
from indexcols as c1
join indexcols as c2
on c1.id = c2.id
and c1.indid < c2.indid
and c1.cols = c2.cols
and c1.inc = c2.inc;

I would avoid msforeachdb it has been known to miss databases, which I have experienced first person.

Either create your own cursor(opportunity to learn something new!), or use Aaron Bertrand's reliable version. sp_ineachdb


SQL Server: Multiple Availability Groups, one cluster, how do I keep the primary cluster node as primary? I have a database server setup where there are three SQL Server database instances set up on three nodes of a Windows Server Failover Cluster (WSFC).

The sysadmin, who is responsible for the Windows Cluster, is accusing me of manually setting the primary cluster node as something other than what it is.

Can failing Availability Groups over to secondary replicas, to include during reboots when they automatically take over, also cause the underlying Windows Server Failover Cluster to switch the primary node and not switch it back?

This has the potential to become a distracting issue, keeping me from getting work done.  I'm hoping to be pointed in the right direction to keep this from happening again.

Converted from comments:

  Are you referring to the core cluster resources? If yes, the answer is no, failover of the cluster role for the availability group would not impact the core cluster resources. The core cluster resources will of course fail over to another node if the current owner is rebooted or loses connectivity, but that behavior is not impacted by what anything that happens with the AG resources. – Tony Hinkle


Moving SQL Server instances from one datacenter to another I am trying to find some workaround for moving SQL Server 2005/2008 instances from one data center to another data center without changing database configuration settings at application end, because application team is not sure where server name and database name are hardcoded. I know this is bad practice but the application is developed by a third party and we don't have control of application code.

So the challenge here is to keep the server name, database name, and IP the same.

Note: OS does not support Image Backup so this option is ruled out.

Please let me know your thoughts.

So the challenge here is to keep the server name, database name, and IP the same.

I've done this many times in the past without any issue. I always get another SQL Server built and get everything defined over on it as is currently on the SQL Server I'm migrating from such as linked servers, credentials, proxies, logins with matching sids and password hashes, SQL Agent jobs, and on and on.

Be sure the Server OS on the new SQL Server has a "different" host name before installing SQL Server like ServerNameNew and a different IP Address, get the SQL objects defined or copied over (e.g. first paragraph above), tested and confirmed as best you can.

When it's time for the transition you will:

Change the hostname and IP Address on the original server to
something different
Rename the new SQL Server OS to the original server host name
Assign the same IP Address(es) back to the NIC(s) on the new SQL Server
Change SQL meta data database name

  To rename a computer that hosts a stand-alone instance of SQL Server

  For a renamed computer that hosts a default instance of SQL Server, run the following procedures:

sp_dropserver <old_name>;
GO
sp_addserver <new_name>, local;
GO

  Restart the instance of SQL Server.

  For a renamed computer that hosts a named instance of SQL Server, run the following procedures:

sp_dropserver <old_name\instancename>;
GO
sp_addserver <new_name\instancename>, local;
GO

  Restart the instance of SQL Server.

  source

If you get the new SQL Server built and define all the objects to match what's defined on the old SQL Server, and then test as much as you can until you feel comfortable with the testing results, make the changes as mentioned and everything should work as expected assuming you do not overlook anything in your test setup.

Important: Have other people help test once you get the DB objects defined, etc. and run through your rounds of DBA testing or whatever. The more eyes and people testing the new setup, the better.

Further Resources

Why are you still using sp_help_revlogin?
sp_help_revlogin (legacy)
SQL Server @@SERVERNAME returning old machine name?


DBD::mysql complains "Character set 'latin1' is not a compiled character set" I think this boils down to "Am I doomed to upgrading mysql hoping the problem goes away?". I've seen a few of the same issues around the internet but with no guidance on a fix other than "Did you turn it off and on again?"

I've install Perl's DBI::mysql, but most of the tests fail because there's a character set problem:

 t/10connect.t ........................ Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file

I wrote a script to investigate further:

use DBI;
use DBD::mysql;

print "Perl $^V\n";
print "DBI ", DBI->VERSION, "\n";
print "DBD::mysql ", DBD::mysql->VERSION, "\n";

$args{host} //= 'localhost';
$args{port} //= 3306;
$args{user} //= 'root';

my $dsn = "dbi:mysql:db=$args{database};host=$args{host};port=$args{port}";

my $db = DBI->connect( $dsn, $args{user}, $ARGV[0] );

The output has the same error from the tests:

Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'latin1' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set latin1 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.

I thought turning on UTF-8 in the connect would bypass the Latin-1 problem, but it's the same problem:

Perl v5.18.0
DBI 1.631
DBD::mysql 4.027
Character set 'utf8' is not a compiled character set and is not specified in the '/usr/local/share/mysql/charsets/Index.xml' file
DBI connect('db=;host=localhost;port=3306','root',...) failed: Can't initialize character set utf8 (path: /usr/local/share/mysql/charsets/) at test.pl line 14.

I think this is related to the MySQL bug Bug #59243:Character set 'latin1' is not a compiled character set and is not specified, but MySQL begs off responsibility for this and says it's a FreeBSD problem (and that's where I'm working!). This might be more appropriate for ServerFault if that's true.

This is MySQL:

$ mysql --version
 mysql  Ver 14.14 Distrib 5.5.8, for FreeBSD8.0 (amd64) using  EditLine wrapper

The MySQL docs (10.5 Character Set Configuration) say that I have to specify this set in Index.xml, and that's what the error message says, but Latin-1 is already in that file (and the file is at the path in the error messages):

<charset name="latin1">
  <family>Western</family>
  <description>cp1252 West European</description>
  <alias>csisolatin1</alias>
  <alias>iso-8859-1</alias>
  <alias>iso-ir-100</alias>
  <alias>iso_8859-1</alias>
  <alias>iso_8859-1:1987</alias>

They suggest getting a new Index.xml (although they don't say from where), so I copy the one I have on OS X (where all of this isn't a problem). That file was updated in 2012 whereas the one I had on FreeBSD was last updated in 2003. That didn't change the error message.

There's a FreeBSD bug ports/153588: Character set 'latin1' is not a compiled character set and is not specified, but it dies without any user guidance. I'd hate to take down everything to reinstall a FreeBSD port if this is a programming issue.

For what it's worth, this is only a problem because I'm installing on this on a different machine since my MySQL slave where I normally work is having all sorts of weird replication issues. I hopefully thought going right to the master (without creating a new user with the grants I need from where I am) would be easier than rebuilding the slave. Yeah, how's that working for me? :)

At MySQLBugs they suggested trying with binaries available at MySQL Community Downloads .

Downloaded:

mysql-5.5.8-freebsd8.0-x86_64.tar.gz

Extracted to:

/usr/local/mysql-5.5.8-freebsd8.0-x86_64

Linked to

/usr/local/mysql

And re-compiled/re-installed p5-DBD-mysql against it by doing the following:

export PATH=/usr/local/mysql:$PATH
cd /usr/ports/databases/p5-DBD-mysql
make cleanenter
make deinstall
make install

This is supposed to fix your perl module.

Are you still following FreeBSD Bugzilla – Bug 153588 ?

You may also refeer to http://www.freebsd.org/cgi/query-pr.cgi?pr=150959*

Hope you can keep going from here!


Is it better to have large tables or many tables (MySQL) In terms of performance, is it advisable to have only a few tables with a lot of rows or a lot of tables with only a few rows? In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to? Which version would be better in terms of scalability? I am using PHP to access the data and I only need to get data for one or two groups per page load. I am mainly concerned with the speed of the system when there is a lot of data being stored. Thanks.

In my instance, I have groups that I am keeping data separately for. Is it better to create a new table for every group or keep all the information for the groups in one table (that will get very, very large) and have a column that identifies which group it belongs to?

Define "very, very large".
Millions of rows?  That's large[-ish].
Anything less than that really isn't.

If you went with multiple tables, how would you identify them?
You don't want to use Group Names as Table Names, because someone will create a Group containing a character that can't be used as such and break everything.
So you'd wind up with another table that links each table name to its Group name using some other, arbitrary identifier.

Assuming you did this; how would you then write any sort of query that goes across all of those tables, which you might need to do for Administrative purposes.
Let's assume that the database is powerful enough to be able to join or union that many tables in one query?  (MySQL, for example, used to be limited to 61 such tables).

Basically, the table-per-thing model is generally a Bad Idea and should be avoided.

Create a single Groups table, with a unique identifier for each Group and index it sensibly for the queries that you intend to run against it.


How to properly implement compound greatest-n filtering Yep, more greatest-n-per-group questions.

Given the a table releases with the following columns:

 id         | primary key                 |
 volume     | double precision            |
 chapter    | double precision            |
 series     | integer-foreign-key         |
 include    | boolean                     | not null

I want to select the compound max of volume, then chapter for a set of series.

Right now, if I query per-distinct-series, I can easily accomplish this as follows:

SELECT
       releases.chapter AS releases_chapter,
       releases.include AS releases_include,
       releases.series AS releases_series
FROM releases
WHERE releases.series = 741
  AND releases.include = TRUE
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST LIMIT 1;

However, if I have a large set of series (and I do), this quickly runs into efficiency issues where I'm issuing 100+ queries to generate a single page.

I'd like to roll the whole thing into a single query, where I can simply say WHERE releases.series IN (1,2,3....), but I haven't figured out how to convince Postgres to let me do that.

The naive approach would be:

SELECT releases.volume AS releases_volume,
       releases.chapter AS releases_chapter,
       releases.series AS releases_series
FROM
    releases
WHERE
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499,
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137,
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768,
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255,
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606,
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY
    releases_series
ORDER BY releases.volume DESC NULLS LAST, releases.chapter DESC NULLS LAST;

Which obviously doesn't work:

ERROR:  column "releases.volume" must appear in the
        GROUP BY clause or be used in an aggregate function

Without the GROUP BY, it does fetch everything, and with some simple procedural filtering it would even work, but there must be a "proper" way to do this in SQL.

Following the errors, and adding aggregates:

SELECT max(releases.volume) AS releases_volume,
       max(releases.chapter) AS releases_chapter,
       releases.series AS releases_series
FROM
    releases
WHERE
    releases.series IN (12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499,
                        556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137,
                        1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768,
                        1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255,
                        2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606,
                        2634, 2636, 2695, 2696 )
  AND releases.include = TRUE
GROUP BY
    releases_series;

Mostly works, but the issue is that the two maximums aren't coherent. If I have two rows, one where volume:chapter are 1:5, and 4:1, I need to return 4:1, but the independent maximums return 4:5.

Frankly, this would be so simple to implement in my application code that I have to be missing something obvious here. How can I implement a query that actually satisfies my requirements?

The simple solution in Postgres is with DISTINCT ON:

SELECT DISTINCT ON (r.series)
       r.volume  AS releases_volume
     , r.chapter AS releases_chapter
     , r.series  AS releases_series
FROM   releases r
WHERE  r.series IN (
    12, 17, 44, 79, 88, 110, 129, 133, 142, 160, 193, 231, 235, 295, 340, 484, 499
  , 556, 581, 664, 666, 701, 741, 780, 790, 796, 874, 930, 1066, 1091, 1135, 1137
  , 1172, 1331, 1374, 1418, 1435, 1447, 1471, 1505, 1521, 1540, 1616, 1702, 1768
  , 1825, 1828, 1847, 1881, 2007, 2020, 2051, 2085, 2158, 2183, 2190, 2235, 2255
  , 2264, 2275, 2325, 2333, 2334, 2337, 2341, 2343, 2348, 2370, 2372, 2376, 2606
  , 2634, 2636, 2695, 2696)
AND    r.include
ORDER  BY r.series, r.volume DESC NULLS LAST, r.chapter DESC NULLS LAST;

Details:

Select first row in each GROUP BY group?

Depending on data distribution there may be faster techniques:

Optimize GROUP BY query to retrieve latest record per user

Also, there are faster alternatives for long lists than IN ().

Combining an unnested array with a LATERAL join:

SELECT r.*
FROM   unnest('{12, 17, 44, 79, 88, 110, 129}'::int[]) t(i)  -- or many more items
     , LATERAL (
   SELECT volume  AS releases_volume
        , chapter AS releases_chapter
        , series  AS releases_series
   FROM   releases
   WHERE  series = t.i
   AND    include
   ORDER  BY series, volume DESC NULLS LAST, chapter DESC NULLS LAST
   LIMIT  1
   ) r;

Is often faster. For best performance you need a matching multicolumn index like:

CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST);

Related:

Extremely slow query on indexed column

And if there are more than a few rows where include is not true, while you are only interested in the rows with include = true, then consider a partial multicolumn index:

CREATE INDEX releases_series_volume_chapter_idx
ON releases(series, volume DESC NULLS LAST, chapter DESC NULLS LAST)
WHERE include;


Users Characteristics I'm really battling an issue where I have a Users table that has a growing number of user characteristics (regligion, smoking preferences, etc). The strategy I've used thus far has been to add a column for each preference that keys off onto another table.

For example, if User XYZ has a RelgionId of 3, that could mean they're Christian. At runtime, if I need their religion, I join onto another table.

This strategy has worked so far. However, I'm getting concerned about the number of columns in the tables as the number of preferences is increasing. Also, this strategy leads to many joins if I need to get all values for a single user.

I'd like to find out the most normalized way of representing this data. Anybody have any ideas?

You can do this sort of thing by creating what are essentially property bags of these values.   This is not an approach that I am a huge fan of, but as with everything in SQL Server it has its place and your scenario might well be that case.

This is essentially the EAV approach, and Aaron Bertrand has a great writeup on this here to describe the pros and cons in more detail

CREATE TABLE Users
(
    UserID int identity(1, 1) primary key clustered
    ,UserName varchar(200)
);

CREATE TABLE PreferenceType
(
    PreferenceTypeID int identity(1, 1) primary key clustered
    ,PreferenceName varchar(200)
);

CREATE TABLE PreferenceValue
(
    PreferenceValueID int identity(1, 1) primary key clustered
    ,PreferenceValueName varchar(200)
    ,PreferenceTypeID int
);

CREATE TABLE UserPreference
(
    UserPreferenceID int identity(1, 1) primary key clustered
    ,UserID int
    ,PreferenceValueID int
);

ALTER TABLE PreferenceValue
ADD CONSTRAINT  FKValue_Type FOREIGN KEY (PreferenceTypeID) REFERENCES PreferenceType(PreferenceTypeID);

ALTER TABLE UserPreference
ADD CONSTRAINT  FK_UserPreference_User FOREIGN KEY (UserID) REFERENCES Users(UserID);

ALTER TABLE UserPreference
ADD CONSTRAINT  FK_UserPreference_Value FOREIGN KEY (PreferenceValueID) REFERENCES PreferenceValue(PreferenceValueID);

insert into Users (UserName)
Values ('User1' ) --tblUserID = 1
, ('User2' );   --tblUserID = 2

Insert into PreferenceType  (PreferenceName)
Values ('Religion') --Type1
, ('Smoker'); --Type 2

Insert into PreferenceValue (PreferenceValueName, PreferenceTypeID)
Values ('Christian', 1) --Value 1
, ('Muslim', 1) --Value 2
, ('Non Smoker', 2)  --Value 3
, ('Smoker' , 2); -- Value 4

--User 1 is a Christian Smoker
--User 2 is a Muslim Non-Smoker
Insert into UserPreference (UserID, PreferenceValueID)
Values (1, 1)
, (1, 4)
, (2, 2)
, (2, 3);

Visually:

So that when you want to get the information for User 1 you would run this select:

select u.UserID, u.UserName, pv.PreferenceValueName, pt.PreferenceName
From Users u
join UserPreference up on u.UserID = up.UserID
join PreferenceValue pv on up.PreferenceValueID = pv.PreferenceValueID
join PreferenceType pt on pv.PreferenceTypeID = pt.PreferenceTypeID
Where u.UserName = 'User1';

You would get the following results in the format of

UserID, UserName, PreferenceValue, PreferenceType

1, User1, Christian, Religion

1, User1, Smoker, Smoker

To include Date values within a schema similar to this, playing off of the tables that were already created but adding two new ones, you could do something like the following:

CREATE TABLE DateType
(
    DateTypeID int identity(1, 1) primary key clustered
    ,DateTypeName varchar(200)
);

CREATE TABLE UserDateType
(
    UserDateTypeID int identity(1, 1) primary key clustered
    ,UserID int
    ,DateTypeID int
    ,DateValue datetime
);

CREATE UNIQUE NONCLUSTERED INDEX
    uq_UserDateType on UserDateType (UserID, DateTypeID);  --Optional. To only allow one row per type.

ALTER TABLE UserDateType
ADD CONSTRAINT  FKDateType FOREIGN KEY (DateTypeID) REFERENCES DateType(DateTypeID);

ALTER TABLE UserDateType
ADD CONSTRAINT  FKUserDate_UserID FOREIGN KEY (UserID) REFERENCES Users(UserID);

Insert into DateType (DateTypeName)
Values ('BirthDate') --Type1
, ('Anniversary'); --Type 2

Insert into UserDateType (UserID, DateTypeID, DateValue)
Values (1, 1, '01/01/1980') --User 1 has a Birthday of 01/01/1980
, (1, 2, '01/01/2000')  --User 1 has an Anniversary of 01/01/2000
, (2, 1, '02/01/1995');  --User 2 has an Birthday of 01/01/2000

By having these kinds of tables you can dynamically add the new date types and relate them to a user in this way.

As mentioned above, I am not sure that this is the ideal approach, as it certainly has its flaws, but this is a way that you can do this sort of thing with Date values.


What would cause an elastic pool to exhibit high "Sql Server process core percent" metricsj, but low DTU / CPU Backstory:
We have an azure sql elastic pool with several databases in it and around 750GB of data.
We've been having some sporadic outages which correlate with the metric "Sql Server process core percent" hitting a max of 100% for a duration.
Normally, the process core percent metric trends with the cpu percentage metric pretty closely. However, during these outage scenarios, the correlation is actually inverse. The CPU metric (max aggregation) bottoms out, when the process core percent (max aggregation) is peaking.
If I use average for the aggregation, the trend of them correlating is maintained.
TL;DR:
What would cause the "Sql Server process core" metric to max out independent of the CPU metric in an Azure Sql Elastic Pool?

So DTU actually combines three different things into one (CPU, Memory and Disk) and even with Disk you get a fixed Disk Size but also a max limit of IOPS and Throughput. I would recommend to check all these metrics out and see what all of these are showing you. Also look at connection failures other connection related metrics as well. For a time trend average stat is a good view but for a time period when you would like to know what happened at that minute then look at both average and max.
More details on DTU model can be found here.


How do I fix this corrupted page? My database is in simple recovery mode. One page got corrupted and I only have backups created using simple backup.

When I run this:

DBCC PAGE ('myDB', 1, 952663, 3)

I get:

DBCC PAGE error: Invalid page type - dump style 3 not possible.

SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:952663; actual 2668:-1594740640). It occurred during a read of page (1:952663) in database ID 5 at offset

Msg 8909, Level 16, State 1, Line 1
Table error: Object ID 0, index ID -1, partition ID 0, alloc unit ID 332114207834112 (type Unknown), page ID (1:952663) contains an incorrect page ID in its page header. The PageId in the page header = (2668:-1594740640).
CHECKDB found 0 allocation errors and 1 consistency errors not associated with any single object.
Msg 8928, Level 16, State 1, Line 1
Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data): Page (1:952663) could not be processed.  See other errors for details.
Msg 8976, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data). Page (1:952663) was not seen in the scan although its parent (1:1616550) and previous (1:2296409) refer to it. Check any previous errors.
Msg 8978, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 1, partition ID 72057595034861568, alloc unit ID 72057595204796416 (type In-row data). Page (1:1743775) is missing a reference from previous page (1:952663). Possible chain linkage problem.
Msg 2511, Level 16, State 1, Line 1
Table error: Object ID 1131971209, index ID 25, partition ID 72057595034796032, alloc unit ID 72057595204730880 (type In-row data). Keys out of order on page (1:3474431), slots 237 and 238.
CHECKDB found 0 allocation errors and 4 consistency errors in table 'TransactionEntry' (object ID 1131971209).
CHECKDB found 0 allocation errors and 5 consistency errors in database 'DsPointOfSale'.
repair_allow_data_loss is the minimum repair level for the errors found by DBCC CHECKDB (DsPointOfSale).

Can I still repair this database?

So it is the clustered index affected. If it was me I'd probably try DBCC PAGE ('myDB', 1, 952663, 2); on both the restored backup and the broken DB first to see and compare in Winmerge to see if I could infer anything from that (e.g. if both very similar but just some corruption in the page header then you could use DBCC with style 3 on the restored DB to get a good idea of specific rows to restore after REPAIR_ALLOW_DATA_LOSS. Otherwise you'll just need to do a data compare between the two tables and try and see if you can figure out rows that need restoring that way. – Martin Smith


How to dynamically add/remove role members from SSAS 2016 Tabular model I am developing a number of tabular models, which currently have a limited number of users assigned to roles within each. At the moment, with the exception of 1 or 2 cases, the majority of the users are "full read" users in all the reports. I am in the process of implementing row-level security in to a number of these roles and I am getting to the point now where, before these models get deployed in to live I think I should be developing a security model to be able to easily add/remove members to/from these roles.

My question is, how can I deploy a model that allows for dynamically adding and removing users, without having to deploy the solution each time.

In my mind my options are as follows:

Do this manually... not dynamic!
Create user groups in AD and add the user groups to the roles. This will be ok for "full read" users (e.g. Directors), or "manager" roles (e.g. sales manager for sales reports), but would not work for users that will be using row-level security (e.g. salesperson).
Create a "global security" table to add to the model, which contains the 'variable' for read access permissions for "full read" access (e.g. Directors and Managers). I will also have another security table that will hold the data for row-level security for other users (e.g. salesperson filters). Then each role in the model will essentially use row-level security based on these security tables.
Use TMSL to process each deployment in the SSAS instance and "createOrReplace" each role deployment where this user exists/needs to be added... unfortunately I don't know any TMSL so I don't know how hard this is to learn?

I want to maintain a clean environment, so when somebody looks at the model in future they don't see users who left years back. Ultimately, when somebody leaves their AD account will be deactivated so this is less of an issue and more of an OCD. However, new members will need to be added.

I'm leaning towards option 3, but are the above options my only choices? Has anybody deployed a different model? Am I missing something obvious!?!?!? I hope not.

Thanks in advance for any help/tips/advice.

EDIT: I have also just thought of using PowerShell to add/remove users. Based on what David said, I could get one of my guys to maintain this instead.

Typically only the role definitions are part of the project.  The role memberships are different by environment (dev,test,prod), are usually not administered by the model developers, and so are maintained separately.  Then only row-level security is driven from a table in the model.


fn_trace_gettable() reports an error The following code returns some rows with [Error] = 2

SELECT
 v1.[name] as EventName, tr.TextData, tr.HostName, tr.ApplicationName, tr.LoginName, tr.SPID, tr.Duration,
 tr.StartTime, tr.EndTime, tr.Reads, tr.Writes, tr.CPU, tr.ServerName, tr.Error, tr.ObjectName, tr.RowCounts
FROM
 sys.trace_events as v1
 INNER JOIN fn_trace_gettable(@v_path, DEFAULT) as tr ON tr.EventClass = v1.trace_event_id
WHERE
 tr.TextData IS NOT NULL AND
 (tr.EndTime > @v_maxEndTime OR @v_maxEndTime IS NULL)
 OPTION(RECOMPILE);

Where can I see the list of error codes for fn_trace_gettable to understand what this one means?

The most common error codes when tracing regular query execution will be 0, 1 or 2.

0 = OK, 1 = Error, 2 = Abort

Source

In your case, error code 2 = abort, meaning that a query was cancelled due to such things as a timeout or a user action.

You can test this by starting a trace, running a query and stopping it before execution end:

The official definition shows that other error messages might be shown dependent on which events you select:

The same is true for the docs on traces.

These errors and their definition can be found in:

SELECT *
FROM sys.sysmessages;

Testing this could be done by selecting error events:

Running a divide by zero query to produce an error:

Finding the exception error number:

And then querying sys.sysmessages:

SELECT *
FROM sys.sysmessages
where error = 8134;

Result:


Update value equal to the count appearance on the last X records I have a MySQL table everyday time a new value is inserted I need to count look at the last 100 previous records and count how many times the inserted value appears.

You can assume the structure of my table is:

num (int, primary, auto-increment) | value (varchar) | last_100_repetitions (int)

I need to update last_100_repetitions based on how many times value appears on the last 100 inserted records (where num indicates the order of the records).

I find the construction rather error prune, what if a row is deleted, or value is updated, should last_100_repetitions also be updated then? That said, a trigger like:

delimiter //
create trigger trg
before insert on t
for each row
    set new.last_100_repetitions = (select count(1)
                                    from (
                                        select value
                                        from t
                                        order by num desc
                                        limit 100
                                    ) as x
                                    where value = new.value )
//
delimiter ;

may work. If you are only interested in the last last_100_repetitions for each value you may concider moving this attribute to a separate table.

Test:

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.05 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
+----+-------+----------------------+
4 rows in set (0.01 sec)

MariaDB [test3]> insert into t (value) values ('A'),('A'),('B'),('C');
Query OK, 4 rows affected (0.06 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [test3]> select * from t;
+----+-------+----------------------+
| num| value | last_100_repetitions |
+----+-------+----------------------+
| 12 | A     |                    0 |
| 13 | A     |                    1 |
| 14 | B     |                    0 |
| 15 | C     |                    0 |
| 16 | A     |                    2 |
| 17 | A     |                    3 |
| 18 | B     |                    1 |
| 19 | C     |                    1 |
+----+-------+----------------------+
8 rows in set (0.00 sec)


percent match within the same table Below I have an MySQL database where "id" is just an id for each row. "question" shows the id of the question. There are four questions, 11, 12, 13 and 14. For every question the user has four answer options (1,2,3 and 4) which is stored in the "answer" column. The "user" column indicates what user who answered. In this example we have user 10, 11 and 12

id      question    answer      user
1           11          2       10
2           12          2       10
3           13          3       10
4           14          4       10
5           11          2       11
6           12          2       11
7           13          4       11
8           14          1       11
9           11          2       12
10          12          2       12
11          13          1       12
12          14          1       12

Let's say that user 10 is the reference user which means that I want to know how well user 10 matches with the others. Using SQL and/or php code how can I match the answers of the users such that I get the matches in percent with the highest percent shown first. So in this example I'm looking for something like.

The Matches of user 10

        user     percent
1       11       50%
2       12       75%

You need to have a subquery (q1) to count how many answers of every user coincide with those of user 10 (number_of_coinciding_answers). Then, have a second subquery (q2) that counts the number_of_answers.

Then compute 100.0 * number_of_coinciding_answers / number_of_answers:

Everything put together just looks like:

SELECT
    q1.user,
    100.0 * q1.number_of_coinciding_answers / q2.number_of_answers AS percent
FROM
    (SELECT
        t.user,
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t
        JOIN t AS good ON good.question = t.question
                      AND good.user = 10
                      AND good.answer = t.answer
    WHERE
        t.user <> 10
    GROUP BY
        t.user
    ) AS q1
    CROSS JOIN
    (SELECT
        count(*) AS number_of_answers
    FROM
        t
    WHERE
        t.user = 10
    ) AS q2
ORDER BY
    q1.user ;

You'll get

user |  percent
---: | -------:
  11 | 50.00000
  12 | 50.00000

Note that the JOIN condition is taking care of just finding matching answers between the current user and the reference user (user 10).

Note also that the numbers retrieived are 50% and 50%, which are the correct ones, as pointed out by Rick James.

You can write it in a slightly more compact fashion (that MySQL actually seems to handle better) by using:

SELECT
    q1.user,
    100.0 * q1.number_of_coinciding_answers /
          (SELECT count(*) FROM  t WHERE  t.user = 10) AS percent
FROM
    (SELECT
        t.user,
        count(good.answer) AS number_of_coinciding_answers
    FROM
        t
        JOIN t AS good ON good.question = t.question
                      AND good.user = 10
                      AND good.answer = t.answer
    WHERE
        t.user <> 10
    GROUP BY
        t.user
    ) AS q1
ORDER BY
    q1.user ;

All these queries are helped by having a multi-column covering index on columns (user, question, answer). That is, in MySQL, you'd define your table

CREATE INDEX idx_t_uqa
    ON t (user, question, answer) ;

You can see a more step-by-step approach and all the settings at dbfiddle here


Developing Access DB to SQL? I built a database several years ago in Access to track over 4K pieces of equipment in and out of a shop, where it was going, current status, etc. It's dying. I would like to upgrade it, but unsure if a split Access DB or a SQL Server 2008 back with Access front would be better/sufficient. I'm leaning towards the latter.
At most 35 simultaneous users, but only on rare occasions.
Would like to track (log) changes to items each time data/status changes.
Print reports, etc.
Keep it SIMPLE for users.

How large is your database? If you expect that your database will not grow to larger than 10 GB, and you are familiar with SQL Server, then you can use SQL Server Express Edition. If you don't need any of the enterprise features (page/row compression, HA/DR, encryption, table partitioning, etc), it's a great way to manage smaller amounts of data...and its free!

I've never personally used a set-up with Access ADP as a front-end, but I've heard it works fine for straight-forward data management. If you have the time/resources, creating a simple web-based front end for management (or buying something out of the box that sits on SQL Server) might allow you to reduce the complexity even further.


Delete all row from all table from MYSQL DB with some ID clause Is it possible to delete all rows from all tables in MYSQL DB with some id = '123'.

Actually i needed this because there is lots of junk data in others table but in main table rows are deleted, i have the IDs.

Now the new logic is implemented in the right way but i have to delete previous rows. Is it possible in a single MYSQL query?

Thanks in advance

Quick Solution is to populate the ids to temp table and use Delete records from the table with Join to temp table.


How to SELECT multiple COUNT(*) columns with each GROUP BY having a different WHERE clause? I have the following schema:

CREATE TABLE Person (
  PersonId int PRIMARY KEY
)

CREATE TABLE Action (
  ActionId int PRIMARY KEY,
  PersonId int NOT NULL FOREIGN KEY REFERENCES Person(PersonId),
  ActionTime datetime NOT NULL
)

and the following data:

INSERT INTO Person (PersonId) VALUES
(1),
(2),
(3),
(4)

INSERT INTO Action (ActionId, PersonId, ActionTime) VALUES
(1, 1, '2014-02-01'),
(2, 1, '2014-02-02'),
(3, 2, '2014-02-02'),
(4, 3, '2014-03-05')

I would like to run a query which shows the number of actions each person performs between the 15th of each month. For example, I am trying the following:

SELECT
    Person.PersonId,
    COALESCE(GroupA_Actions_Made, 0) AS GroupA_Actions_Made,
    COALESCE(GroupB_Actions_Made, 0) AS GroupB_Actions_Made
FROM
    Person
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupA_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-01-15 00:00:00' AND '2014-02-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupA ON GroupA.PersonId = Person.PersonId
    RIGHT OUTER JOIN (
        SELECT
            PersonId,
            COUNT(*) AS GroupB_Actions_Made
        FROM
            Action
        WHERE
            ActionTime BETWEEN '2014-02-15 00:00:00' AND '2014-03-14 23:59:59'
        GROUP BY
            PersonId
    ) GroupB ON GroupB.PersonId = Person.PersonId

However, the query I am trying is returning the following:

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
(null)     0                     1

but I would like

PersonId | GroupA_Actions_Made | GroupB_Actions_Made
1          2                     0
2          1                     0
3          0                     1

(I do not want the results to return anything for people who have not made actions.)

How can I obtain the results in the desired format?

UPDATE

Each of the answers works except that I had to wrap them as follows:

SELECT
    PersonId,
    GroupA_Actions_Made,
    GroupB_Actions_Made
FROM (
    -- (answer)
) t
WHERE
    GroupA_Actions_Made > 0
    OR GroupB_Actions_Made > 0

Using SQL Server Profiler, the accepted answer seems to have the fastest query time on large data sets.

This will give you the result you wanted but I'm not sure if it's the most flexible piece of code.

SELECT p.PersonId,
    SUM(CASE
          WHEN a.ActionTime >= '2014-01-15 00:00:00'
            AND a.ActionTime < '2014-02-15 00:00:00'
          THEN 1
          ELSE 0
        END) AS GroupA_Actions_Made,
    SUM(CASE
          WHEN a.ActionTime >= '2014-02-15 00:00:00'
            AND a.ActionTime < '2014-03-15 00:00:00'
          THEN 1
          ELSE 0
        END) AS GroupB_Actions_Made
FROM
    Person p
JOIN
    Action a on p.PersonId = a.PersonId
GROUP BY p.PersonId


Huge slowdown to SQL Server query on adding wildcard (or top) I've got a zoo of 20 million animals which I track on my SQL Server 2005 database. About 1% of them are black and about 1% of them are swans. I wanted to get details of all the black swans and so, not wanting to swamp the results page I did:

select top 10 *
from animal
where colour like 'black'
and species like 'swan'

(Yes, inadvisedly those fields are freetext but they are both indexed). It turns out we have no such animals as the query returns an empty set in about 300 milliseconds. It would have been about twice as fast if I'd used '=' rather than 'like' but I have a premonition the latter is about to save me some typing.

It turns out the head zookeeper thinks he may have entered some of the swans as 'blackish' so I modify the query accordingly:

select top 10 *
from animal
where colour like 'black%'
and species like 'swan'

Turns out there are none of those either (and in fact there are no 'black%' animals except 'black' ones) but the query now takes about 30 seconds to return empty.

It seems it's only the combination of 'top' and 'like %' causing trouble though because

select count(*)
from animal
where colour like 'black%'
and species like 'swan'

returns 0 very quickly, and even

select *
from animal
where colour like 'black%'
and species like 'swan'

returns empty in a fraction of a second.

Does any one have any idea why 'top' and '%' should conspire to cause such a dramatic loss of performance, especially in an empty result set?

EDIT: Just to clarify, I'm not using any FreeText indexes, I just meant that the fields are freetext at the point of entry, i.e. not normalized in the database. Sorry for the confusion, poor wording on my part.

This is a decision of the cost based optimiser.

The estimated costs used in this choice are incorrect as it assumes statistical independence between values in different columns.

It is similar to the issue described in Row Goals Gone Rogue where the even and odd numbers are negatively correlated.

It is easy to reproduce.

CREATE TABLE dbo.animal(
    id int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    colour varchar(50) NOT NULL,
    species varchar(50) NOT NULL,
    Filler char(10) NULL
);

/*Insert 20 million rows with 1% black and 1% swan but no black swans*/
WITH T
     AS (SELECT TOP 20000000 ROW_NUMBER() OVER (ORDER BY @@SPID) AS RN
         FROM   master..spt_values v1,
                master..spt_values v2,
                master..spt_values v3)
INSERT INTO dbo.animal
            (colour,
             species)
SELECT CASE
         WHEN RN % 100 = 1 THEN 'black'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END,
       CASE
         WHEN RN % 100 = 2 THEN 'swan'
         ELSE CAST(RN % 100 AS VARCHAR(3))
       END
FROM   T

/*Create some indexes*/
CREATE NONCLUSTERED INDEX ix_species ON  dbo.animal(species);
CREATE NONCLUSTERED INDEX ix_colour ON  dbo.animal(colour);

Now try

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black'
       AND species LIKE 'swan'

This gives the plan below which is costed at 0.0563167.

The plan is able to perform a merge join between the results of the two indexes on the id column. (More details of merge join algorithm here).

Merge join requires both inputs to be ordered by the joining key.

The nonclustered indexes are ordered by (species, id) and (colour, id) respectively (nonunique non clustered indexes always have the row locator added in to the end of the key implicitly if not added explicitly). The query without any wildcards is performing an equality seek into species = 'swan' and colour  ='black'. As each seek is only retrieving one exact value from the leading column the matching rows will be ordered by id therefore this plan is possible.

Query plan operators execute from left to right. With the left operator requesting rows from its children, which in turn request rows from their children (and so on until the leaf nodes are reached). The TOP iterator will stop requesting any more rows from its child once 10 have been received.

SQL Server has statistics on the indexes that tell it that 1% of the rows match each predicate. It assumes that these statistics are independent (i.e. not correlated either positively or negatively) so that on average once it has processed 1,000 rows matching the first predicate it will find 10 matching the second and can exit. (the plan above actually shows 987 rather than 1,000 but close enough).

In fact as the predicates are negatively correlated the actual plan shows that all 200,000 matching rows needed to be processed from each index but this is mitigated to some extent because the zero joined rows also means zero lookups were actually needed.

Compare with

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Which gives the plan below which is costed at 0.567943

The addition of the trailing wildcard has now caused an index scan. The cost of the plan is still quite low though for a scan on a 20 million row table.

Adding querytraceon 9130 shows some more information

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 9130)

It can be seen that SQL Server reckons it will only need to scan around 100,000 rows before it finds 10 matching the predicate and the TOP can stop requesting rows.

Again this makes sense with the independence assumption as 10 * 100 * 100 = 100,000

Finally lets try and force an index intersection plan

SELECT TOP 10 *
FROM   animal WITH (INDEX(ix_species), INDEX(ix_colour))
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

This gives a parallel plan for me with estimated cost of 3.4625

The main difference here is that the colour like 'black%' predicate can now match multiple different colours. This means the matching index rows for that predicate are no longer guaranteed to be sorted in order of id.

For example the index seek on like 'black%' might return the following rows

+------------+----+
|   Colour   | id |
+------------+----+
| black      | 12 |
| black      | 20 |
| black      | 23 |
| black      | 25 |
| blackberry |  1 |
| blackberry | 50 |
+------------+----+

Within each colour the ids are ordered but the ids across different colours may well not be.

As a result SQL Server can no longer perform a merge join index intersection (without adding a blocking sort operator) and it opts to perform a hash join instead. Hash Join is blocking on the build input so now the cost reflects the fact that all matching rows will need to be processed from the build input rather than assuming it will only have to scan 1,000 as in the first plan.

The probe input is non blocking however and it still incorrectly estimates that it will be able to stop probing after processing 987 rows from that.

(Further info on Non-blocking vs. blocking iterators here)

Given the increased costs of the extra estimated rows and the hash join the partial clustered index scan looks cheaper.

In practice of course the "partial" clustered index scan is not partial at all and it needs to chug through the whole 20 million rows rather than the 100 thousand assumed when comparing the plans.

Increasing the value of the TOP (or removing it entirely) eventually encounters a tipping point where the number of rows it estimates the CI scan will need to cover makes that plan look more expensive and it reverts to the index intersection plan. For me the cut off point between the two plans is TOP (89) vs TOP (90).

For you it may well differ as it depends how wide the clustered index is.

Removing the TOP and forcing the CI scan

SELECT *
FROM   animal WITH (INDEX = 1)
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'

Is costed at 88.0586 on my machine for my example table.

If SQL Server was aware that the zoo had no black swans and that it would need to do a full scan rather than just reading 100,000 rows this plan would not be chosen.

I've tried multi column stats on animal(species,colour) and animal(colour,species) and filtered stats on animal (colour) where species = 'swan' but none of these help convince it that black swans don't exist and the TOP 10 scan will need to process more than 100,000 rows.

This is due to the "inclusion assumption" where SQL Server essentially assumes that if you are searching for something it probably exists.

On 2008+ there is a documented trace flag 4138 that turns off row goals. The effect of this is that the plan is costed without the assumption that the TOP will allow the child operators to terminate early without reading all matching rows. With this trace flag in place I naturally get the more optimal index intersection plan.

SELECT TOP 10 *
FROM   animal
WHERE  colour LIKE 'black%'
       AND species LIKE 'swan'
OPTION (QUERYTRACEON 4138)

This plan now correctly costs for reading the full 200 thousand rows in both index seeks but over costs the key lookups (estimated 2 thousand vs actual 0. The TOP 10 would constrain this to a maximum of 10 but the trace flag prevents this being taken into account). Still the plan is costed significantly cheaper than the full CI scan so is selected.

Of course this plan might not be optimal for combinations which are common. Such as white swans.

A composite index on animal (colour, species) or ideally animal (species, colour) would allow the query to be much more efficient for both scenarios.

To make most efficient use of the composite index the LIKE 'swan' would also need to be changed to = 'swan'.

The table below shows the seek predicates and residual predicates shown in the execution plans for all four permutations.

+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
|                 WHERE clause                 |       Index       |                         Seek Predicate                         |              Residual Predicate              |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+
| colour LIKE 'black%' AND species LIKE 'swan' | ix_colour_species | colour >= 'black' AND colour < 'blacL'                         | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species LIKE 'swan' | ix_species_colour | species >= 'swan' AND species <= 'swan'                        | colour like 'black%' AND species like 'swan' |
| colour LIKE 'black%' AND species = 'swan'    | ix_colour_species | (colour,species) >= ('black', 'swan')) AND colour < 'blacL'    | colour LIKE 'black%' AND species = 'swan'    |
| colour LIKE 'black%' AND species = 'swan'    | ix_species_colour | species = 'swan' AND (colour >= 'black' and colour <  'blacL') | colour like 'black%'                         |
+----------------------------------------------+-------------------+----------------------------------------------------------------+----------------------------------------------+


Read after write in Galera I am facing a issue with 3 node galera cluster where the read doesn't return any records even when the write has been commited. Below are the steps which can happen from multiple servers.

1>Server A writes a record at 12:00:00 AM

2>Server B reads the same record after 10 seconds at 12:00:10 AM but doesn't get any record.

The bin log on all 3 server confirms the commit at 12:00:00 AM.

What could be the issue here ?

If you are reading from a node other than then node where the transaction was commit, there is a finite response time on the other nodes from the time it is certified, to the time it is applied on the other node.

In theory you should be able to wait to ensure its committed.


Database going to Suspect mode I have a frequent problem, that is one of my databases goes to suspect mode.

Many times I have recovered it with:

ALTER DATABASE Your_Db SET EMERGENCY
DBCC checkdb('Your_Db')
ALTER DATABASE Your_Db SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Your_Db', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Your_Db SET MULTI_USER

It continues to become suspect after. Please provide me the permanent solution.

It continues to become suspect after. Please provide me the permanent
  solution.

The permanent solution is to figure out WHY/WHAT is causing the database to go suspect. Is it a filter drivers, bad hardware, etc. We don't know and can't tell you, this is something you'll need to inspect on your own. You'll want to get your sysadmins involved as well.

  Many times I have recovered it with: ALTER DATABASE Your_Db SET
  EMERGENCY DBCC checkdb('Your_Db') ALTER DATABASE Your_Db SET
  SINGLE_USER WITH ROLLBACK IMMEDIATE DBCC CheckDB ('Your_Db',
  REPAIR_ALLOW_DATA_LOSS) ALTER DATABASE Your_Db SET MULTI_USER

Please, don't ever use this as the first line to attack - unless you don't like your job and are looking elsewhere for employment.

Check to see what is being corrupted, how bad it is, the extent. If you aren't sure, hire a consultant that specializes in corruption.


Upgrade SQL Server, not user databases I have one machine we've been fighting to do an in place upgrade of SQL Server from 2016 SP2 CU3 to 2017. It keeps failing, but that's another topic. My question is, is there a way I can run the upgrade, but force it to omit upgrading user databases to 2017?

If I can get the system databases upgraded and the server running I'll manually upgrade user databases at another time.

No. As soon as you attach a database to SQL Server, assuming it is compatible, the internal system tables will be upgraded.

While this process is a one-way operation, it takes a few seconds at most.

If you do not wish to upgrade your user databases, don't upgrade to the latest SQL Server.

If you want to keep your databases on a previous compatibility level, that can be done from T-SQL code or inside SQL Server Management Studio, but for all intents and purposes, the internal version of the database will match that of the instance it is installed on.


Can queries that users ORDER BY using date to sort data be non deterministic? I have a table in my database where I store dates using the following standard: YYYY-MM-DD. As you can see, I'm not saving the hour. The first time I exported the same table to a csv file I got the following records in this order:

AAA@outlook.com
BBB@gmail.com
CCC@gmail.com
DDD@hotmail.com

But when I ran the same query to export the data to the same CSV, I received the records in this following order:

AAA@outlook.com
111@hotmail.com
222@gmail.com
333@gmail.com

So, the conclusion that I reached is when sorting data as this can be non deterministic. Isn't? This is my query to export the data from my database:

SELECT id AS "_id", cpf, email, name, agency, account, "createdDate" FROM "NextUsers", "Users" WHERE "userId"="Users".id ORDER BY "createdDate" OFFSET $1 LIMIT 500;

Where $1 starts at 0 and it is incremented with 500 per iteration.
OBS: The returned data from both queries are correctly sorted but not following in the same order as you can see.

If the date you are sorting by has repeat values in it and are not sorting by any other columns the order you get them in will be arbitrary.

Result set 1

email|date
aaa@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
ccc@gmail.com|2019-01-01

Result set 2

email|date
ccc@gmail.com|2019-01-01
bbb@gmail.com|2019-01-01
aaa@gmail.com|2019-01-01

If you just have an order by on date the database can return either result set as is just ordering by the date field and pulling in records as it gets them regardless of order.

If you need a consistent order in the results you are going to need to add more fields to the order until you can get a more unique set of fields to order by.

For your query I would update it to also order by _id so you will have a consistent result set

SELECT id AS "_id",
       cpf,
       email,
       name,
       agency,
       account,
       "createdDate"
FROM "NextUsers",
     "Users"
WHERE "userId"="Users".id
ORDER BY "createdDate",
         id
OFFSET $1
LIMIT 500;


Database 'msdb' cannot be opened. it has been marked suspect by recovery I'm using SQL Server 2008. I got suddenly today an error while i was accessing a database from Visual Studio. The error is:

  Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. Error: 926

I don't know what to do to get out from this type of error.

The fastest way is to restore the MSDB database, but if it's your first time doing that, here's an easier shortcut.

Restore the MSDB backup onto an existing (working) database server, but use a different database name than MSDB. The restore should go quickly (because MSDB is typically very small), and you can then verify that your objects are in there.
Detach the database, and copy the mdf/ldf files over to the broken instance.
Move the broken mdf/ldf files somewhere for safekeeping, and replace them with your newly restored mdf/ldf files.

Start the SQL Server instance again, and you're set.

One gotcha - in step 1, make sure you're restoring onto the same major version of SQL Server. If the broken server is SQL Server 2005, don't do the restore on SQL Server 2012, because the SQL 2005 instance won't be able to attach databases that have been touched by a newer version of SQL Server.


Why search by date field requires engine to touch all records? Why search by date field requires engine to touch all records? Is there any way to avoid it?

Following query

EXPLAIN select * FROM playdays where realdate>=Date('2010,01,01') ;

shows that amount of rows required to generate the result is 2725 which is higher than total amount of records in the table (2349).

Question #1: Why amount of rows to touch is higher than amount of records?

Question #2: Why amount of rows to touch is so high even though there is an index on the 'realdate' field? Is that really means query will touch all records? Should I optimize it? If yes, how?

Please advise! Any feedback is welcome! Thank you!

P.S. I use MYSQL 5.1.43 version, my table has realdate field, its type is 'DATE', table has totally 2349 records.

P.P.S. Answering to one of comments: selecting only realdate field reduces amount of touched records to 1362... if I vary date value used in search, amount of touched records changes depends of amount records left in the selections...

Hope, that additional information will help.

As @Razor commented, the number reported by EXPLAIN is only an estimate, and it can be off from your true row count by +/- 10% or even more.

MySQL prefers not to use an index at all if the portion of the table selected by the query conditions is too large. Think of the index at the back of a book. Why don't they index the word "the"? Because it would just list every page in the book. If the word occurs on too many pages in the book, it's actually easier to just read the book cover-to-cover instead of using an index.

Similarly, in MySQL if the optimizer estimates that the values you are searching for are found on a significantly high number of rows (in my experience "significant" is at least 16-18%) then MySQL skips the use of the index and just does a table-scan instead.

Sometimes this is the wrong choice, and you can give MySQL a hint that a table-scan is actually much more expensive than the preferred index choice:

EXPLAIN select * FROM playdays FORCE INDEX(realdate) where realdate>=Date('2010,01,01') ;

Be careful about using index hints too liberally, because you effectively hard-code your index choices into your queries by using them, and that may become the wrong strategy as your data changes over time. They should be used in exception cases, when you can demonstrate that the optimizer make the wrong choice.


Remote Scan when updating using functions Why does using ISNULL() when updating through a linked server use Remote Scan to retrieve all rows and filter locally instead of filtering through Remote Query?
UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = '123'

UPDATE LINKEDSERVER1.database1.dbo.table1 WITH(ROWLOCK)
SET number = number
WHERE accounts = '123'

The execution plan is as below:

The table does have a nonclustered unique index for the accounts column, but ISNULL() is not used on the accounts column and the index should still be usable. Am I missing anything here?

Am I missing anything here?

Not really.  You are discovering that distributed query processing has lots of tricky limitations like this.
So the rule of thumb for using Linked Servers is to always use passthrough queries if you want remote execution.
exec (
'
UPDATE database1.dbo.table1 WITH(ROWLOCK)
SET number = ISNULL(number,0)
WHERE accounts = ''123''
'
) at LINKEDSERVER1


Include constraint for daterange type Here is the table

CREATE TABLE date_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER,
  start_at DATE,
  end_at DATE,
  is_exception BOOLEAN
);

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH &&)
WHERE (NOT is_exception);

I have a table with working ranges and here is a flag is_exception which indicates that this range is an exception from the regular range and I want to make a constraint that all regular ranges do not overlap each other and exception ranges belong to the regular range.

I tried to use <@ operator but have found that it is not commutative.

ALTER TABLE date_ranges ADD CONSTRAINT date_ranges_bounds
EXCLUDE USING gist (user_id WITH =, daterange(start_at, end_at, '[]') WITH <@)
WHERE (is_exception);

Is there any option to do this? Shall I create custom operator for this case?

UPDATE

Test cases:

user_id     start_at        end_at      is_exception    is_day_off
1           2019-03-01      2019-03-05  FALSE           FALSE       // is fine
1           2019-03-01      2019-03-05  FALSE           FALSE       // FAIL. Range intersects with range above
1           2019-03-02      2019-03-03  TRUE            FALSE       // is fine since exception is inside "regular range above"
1           2019-03-04      2019-03-04  TRUE            FALSE       // is fine since exception is inside "regular range above" & does not intersect another exception
1           2019-03-03      2019-03-03  FALSE           TRUE        // is fine since day off is inside "regular range above"
1           2019-03-05      2019-03-05  FALSE           TRUE        // is fine since day off is inside "regular range above" & does not intersect another day off

1           2019-03-02      2019-03-03  TRUE            FALSE       // FAIL. Exception should NOT intersect with another one
1           2019-03-03      2019-03-03  FALSE           TRUE        // FAIL. Day off should NOT intersect with another one

1           2019-03-15      2019-03-15  TRUE            FALSE       // FAIL. Exception is NOT inside "regular range above"
1           2019-03-10      2019-03-10  FALSE           TRUE        // FAIL. Day off is NOT inside "regular range above"

The (not overlapping) constraint between "regular" ranges can be enforced with an EXCLUDE constraint, as you it defined in the question.

The (not overlapping)  constraint between "exception" ranges can be enforced in the same manner and so can the constraint between "day off" ranges.

What is not trivial is the constraint between "regular" and "exception" ranges (and between "regular" and "day off" ranges):

  For an exception there should be a regular range that the exception range is contained inside the regular one.

This is not commutative, so it cannot be done with EXCLUDE constraints. What you really need is a FOREIGN KEY constraint, a slightly more complicated one than a common foreign key constraint.

As an example, a foreign key constraint defined n table B:

CONSTRAINT a_b_fk
    FOREIGN KEY (aid)
    REFERENCES a (aid)

enforces that:

  for any aid value in table B, there exists a row in table A with equal value in column aid

which is quite similar to what you have. The problematic difference is that foreign key constraints can use only the equality (=) and not any other operator. To solve this problem, we'll have to duplicate the columns that need to be checked with the different operator ("is contained by", <@).

As a result, we'll need to split the table into two, "regular" and "exception" ranges (lets forget the "day off" for a bit) and apply our solution:

For the "regular" ranges table, we'll only need an additional UNIQUE constraint, for the foreign key to be defined (and we can skip the is_exception and is_day_off columns):

CREATE TABLE date_regular_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  CONSTRAINT date_regular_ranges_uq
    UNIQUE (user_id, start_at, end_at),
  CONSTRAINT date_regular_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      )
);

For the "exception" ranges table, we'll need the FOREIGN KEY constraint and a CHECK constraint for the `contained" operator:

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,

  -- we don't need the UNIQUE, as the EXCLUDE covers it
  -- CONSTRAINT date_exception_ranges_uq
  --   UNIQUE (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

For the "day off", since we identified that are similar to "exception" ranges, we can either add another table (with identical structure as the date_exception_ranges) or keep only that and add a boolean column to identify whether it is an "exception" or a "day off".

(The following assume that "exceptions" can overlap "day off" ranges. If they cannot, it's actually much simpler):

CREATE TABLE date_exception_ranges (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL,
  start_at DATE NOT NULL,
  end_at DATE NOT NULL,
  regular_start_at DATE NOT NULL,
  regular_end_at DATE NOT NULL,
  -- TRUE: is_exception, FALSE: is_day_off
  is_exception BOOLEAN NOT NULL,

  CONSTRAINT date_exception_ranges_bounds
    EXCLUDE USING gist
      ( user_id WITH =,
        (is_exception::int) WITH =,
        daterange(start_at, end_at, '[]') WITH &&
      ),

  CONSTRAINT date_regular_exception_ranges_fk
    FOREIGN KEY
      (user_id, regular_start_at, regular_end_at)
    REFERENCES date_regular_ranges
      (user_id, start_at, end_at),

  CONSTRAINT date_exception_ranges_ck
    CHECK ( daterange(start_at, end_at, '[]')
         <@ daterange(regular_start_at, regular_end_at, '[]')
          )
);

And finally, the above tested at dbfiddle.uk


SQL Server : GRANT REFERENCES Fails With Msg 15151 as Windows User with sysadmin role and also as 'sa' On SQL Server 2008 R2, I execute this command:

GRANT REFERENCES ON ASYMMETRIC KEY ::CorpKey to XYZZYEXT;

I get this result:

  Msg 15151, Level 16, State 1, Line 1
  Cannot find the asymmetric key 'CorpKey', because it does not exist or you do not have permission.

I've checked a bunch of articles, including

Can't grant role permissions under sysadmin login

But I'm just not seeing the problem.

This is not an ASYMMETRIC KEY, it is a SYMMETRIC KEY.
Change

GRANT REFERENCES ON ASYMMETRIC KEY

to

GRANT REFERENCES ON SYMMETRIC KEY


updating n number of records mysql database I want to update n number of records in table using a query like below, but it will return an error since in mysql, it is not allowed to use value after the limit key
update producedItem pi
inner join productionOrderLine pol on pol.id = pi.productionOrderLineId
set pi.isReserved = 1
limit floor(pol.quantity - pol.availableQuantity);

what would be the better alternative?

Write a Stored Procedure that 'constructs' the query, specifically by putting a computed value after LIMIT.


Is there a way to prevent "reset role" in PostgreSQL Is there a way to prevent a user from running "reset role" in PostgreSQL, and thus gaining permission he may not have ?

I have an architecture with many databases on a single server. An API connects to several databases on a local PostgreSQL server. I had planned to use Pgbouncer but it sounds like I would have to map one database/user in pgbouncer to the same database/user in PostgreSQL which sounds counter productive. Instead I would like to be able to reuse connections but have different authorization for different user upon connection.

1
RESET ROLE would just "return" you back to the role you used to login. here
also SET ROLE rolename

  must be a role that the current session user is a member of

so " gaining permission he may not have" is not the case.

2
pgbouncer indeed will require such mapping.

3 to "reuse connections but have different authorization for different user upon connection." you can use set authorization which is very similar to SET ROLE.

There is no session pooler that would do it for you I believe...


Multiple Publications for Merge Replication? I am designing a system which will have one SQL server 2012 central database.

Each client will user merge replication to take an offline copy of their data to edit on the road.

If I add a publication I can add filters to that publication. The thing I can't work out though is that is only one set of filters.

For each client I have they will have a different set of filters to filter data that relates to them only. This is quite important from a security point of view.

How is this achieved with only one publication?

This is done in Merge Replication by using Parameterized Row Filters.  They allow for different partitions of data to go to different Subscribers without requiring multiple publications.


On Load sub which checks records causing problems I have a checkbox called 'Added to Schedule' on a form (booking sheet) which is supposed to be ticked if the job appears on another table (schedule). It usually works but sometimes if the job is then unscheduled or moved around, this checkbox does not get unticked.  It can also be vice versa, where the box is not ticked even though the job is on the schedule.

So I wrote an On Load event to check if the job is on the schedule (using the unique job number in a DLookup). If it finds the job on the schedule, it ticks the box. If it doesn't, it unticks.

It works well, but the only problem is, when I look at a booking sheet that has no jobs added to it yet. The booking sheet record has several required fields, such as 'Date' and 'Specific Job No' (between 1 and 99).

When the On Load event runs, it seems to recognise the top empty line as a record to check, and Access then recognises it as an incomplete record. When you then try to close the booking sheet or click somewhere else, it's waiting for you to enter info in the rest of the fields and gives you a validation error ('please enter the date', etc).

How do I get around this? I don't want my Form_Load event to run if there are no lines on the current booking sheet (which is a subform on a form which holds some of the information, such as the account manager).

Private Sub Form_Load()
Dim strJob As String
Dim strSpec As String

strSpec = Format(Me.Specific_Job_No.Value, "00")
strJob = Left(Me.Parent.JobRef.Value, 18) + strSpec
    If DCount("*", "tblWebSchedule", "[JobNo] = " & """" & strJob & """") = 1 Then
        Me![Added to Schedule] = True
        Me![Added to Schedule].Locked = True
    Else
        Me![Added to Schedule] = False
        Me![Added to Schedule].Locked = True
    End If
End Sub

Don't worry, I've got it! I prefixed it with:

Set rst = Me.RecordsetClone
On Error Resume Next
rst.MoveLast
On Error GoTo 0
Me.txtCount.Value = rst.RecordCount
intCount = Me.txtCount.Value

Then if the value was zero, it would skip the check code.


How can I search the full text of a stored procedure for a value? I use the following script to search the text of all stored procedures when I want to find specific values.

SELECT ROUTINE_NAME, ROUTINE_TYPE
FROM INFORMATION_SCHEMA.ROUTINES
WHERE ROUTINE_DEFINITION LIKE @searchText
ORDER BY ROUTINE_NAME

I recently discovered that ROUTINE_DEFINITION stops after 4000 characters, so some procedures were not getting returned when they should have been.

How can I query the full text of a stored procedure for a value?

Use one of

sys.sql_modules
Red Gate SQL Search (free!)
OBJECT_DEFINITION

Never use

INFORMATION_SCHEMA
sys.syscomments


Is it possible to backup a database that is not ONLINE? We have a job which backs up all ONLINE databases nightly, but for some reason it didn't back up msdb. So the issue is at that time msdb was in some status was other then 0. So my question is : is it possible to back up a database if its status is not online (either RESTORING, RECOVERING, RECOVERY_PENDING, SUSPECT, EMERGENCY)?

It appears that no you can not backup a database in an offline state based on this comment from BOL.  Offline and Emergency appear to be the exceptions.

Without the NO_TRUNCATE option, the database must be in the ONLINE state. If the database is in the SUSPENDED state, you might be able to create a backup by specifying NO_TRUNCATE. But if the database is in the OFFLINE or EMERGENCY state, BACKUP is not allowed even with NO_TRUNCATE. For information about database states, see Database States.

However that being said the chances of the database state being the reason for your backup problem are slim.  As said in the comments by @StrayCatDBA MSDB is required to do backups (among other things).  You can look at the error for a backup by right clicking on your maintenance plan (assuming you are using one) and selecting view history.

Once you are in the Log File Viewer you can see what errors you have.  Click on the + to drill down to details and then select each of the lines with the red X in turn to see what the current error is.

In this particular case we had a maintenance plan that was trying to back up databases that no longer existed.  Once they were removed from the plan we stopped getting the errors.


Migrate SQL Server backup to MySQL for a Wordpress site I have a client with an old .Net site that used SQL Server. For lots of reasons they decided to switch to Wordpress. They had a bunch of news articles on that old site that they want turned into posts or custom post types on their new Wordpress site (the standard post is probably easier?)

I reached out to their hosting company and got a .bak file of their database and through online research it seems that MySQL workbench is the best route to go as far as getting this file into a MySQL DB. This is where I'm getting stuck. I don't deal with databases a ton besides backing up Wordpress MySQL databases to move them during local to production development or changing url structures.

Do I need to upload this file to a server somewhere like my personal production site's server to make this migration happen?

My plan was copy the Wordpress site over to my localhost and migrate the SQL Server backup file to my local MAMP server somehow as a test to see if it works correctly. I see this question was sort've asked before but there wasn't a ton of dialogue on that so was hoping anyone with new ideas could help me out since I gave a little more context on my goal. I'm on a mac by the way.

There are 1745 articles with categories and subcategories that won't match the current configuration in the Wordpress database. I asked my server side engineer here at work and he said a possible option is to export the database to CSV and use a CSV to Wordpress plugin to import it. Does this sound like a possibly easier solution?

Good luck.  You'll need to do way more work than just a "restore".

Essentially, you'll need to create a custom pipe to convert the data in the SQL Server database into the table structure used by WordPress.

Even if the table structure was identical, and it isn't, you can't "restore" a .bak file into a MySQL database.  The .bak does not contain SQL statements; it is a binary representation, page by page, of the SQL Server database.

Presuming there aren't a huge number of articles, it is generally considered easier to manually transfer them into WordPress using the interface.  Welcome to the thrill of vendor lock-in.

If you have access to a SQL Server, you might try restoring the .bak file then exporting the desired data as SQL Statements using the "script database" facility.  You might then be able to do a transformation on the SQL Statements to make them "fit" the design of the WordPress database.  I'd be very skeptical that this would be easy, however at a certain point this will take less time than manually recreating the stories in WordPress.


Many one-to-many relationships between two tables In the process of creating a small database for a therapeutic boarding school with Access 2013, I have a problem when designing the relational database model.

Two times at least, I ran into a situation where there were many one-to-many relationships between two tables.

There are two one-to-many relationships between Parents and Children because
a) some parents have more than one child in the school; b) the father and the mother often have different addresses and we have to contact them separately.
There are three (actually even more) one-to-many relationships between Staff and Children because a) every child has an educator of reference (Ref_Educ); b) every child has a person of reference in the board of directors (Ref_Dir); c) every child has a "godfather" (Ref_Godf). But these rules are soft. Sometimes, the person of reference Ref_Dir is not a member of the board of directors. Sometimes, the educator of reference is also the "godfather" of the child, and so on.

So it appears to me that the idea of setting many one-to-many relationships between these tables is somewhat logical, but on the other hand:

I cannot easily set them in the Relationships window of Access, which makes me fear that I have poorly designed the relationships.
In this similar question, it was suggested to "fix the database design". (However, I think that my problem is actually different, as a given person in the Staff table can be linked several times to the same Children entry.)

Could you give me some piece of advice? Thanks in advance.

Edit 2014-06-26
I followed your pieces of advice and updated the schema as follows:

As you can see, I added join tables, and lookup tables for the consistency of status.

It surely makes more sense and is less limited for the case we would want to add some features in the future.

But in the other hand, is it compatible with the fact that for now, there are a finite number of status (father/mother, ref_educ/ref_dir/ref_godf) that are meant to be individually hardcoded in our "Cartotheque" form?

To make things clearer, I draw a mock-up preview of how it should look:

It is a one to many from the "Children" table; one child to many staff, one child to one or more "ResponsibleAdults" (who could be any familial relationship). If you change Father & Mother fields to be Responsible1, Responsible2 and add Responsible1Relationship, Responsible2Relationship to "Children", you'll solve that problem. Alternatively, you can create a join or link table using just childID, ResponsibleID and relationship (1 or more records per child ID). You'll want a "FamRelationship" lookup table for consistency in your data entry (might be parent, aunt, grandparent, guardian, etc". This way Child Joe can have Parents Mike and Mary, while his cousin child Jane can have Uncle Mike and Aunt Mary. Both Children records would reference the same 2 what you're currently calling "Parents" records.

If a "staff" member is not a member of Board of Directors and it's important to note that, add a tinyint indicator to "staff" (true/false, 1/0).