mysqldbcompare object definitions differ I am using a Galera cluster to replicate MySQL 5.7 databases on Ubuntu 16.04 Server. I am using mysqldbcompare to verify the consistency of the replication. I think my data is consistent because the Data Checks and Row Count checks pass.

I think "Object definitions differ..." means that there is something different about the CREATE statements. My output shows the Auto Increment fields having some differences.

My question is: Will the inconsistencies in the object definitions affect the consistency of my replicated databases as a whole (even though data check passes)?

I would love to know what causes the inconsistencies and how to fix them.

# Checking databases study1 on server1 and study1 on server2
#
#                                                   Defn    Row     Data
# Type      Object Name                             Diff    Count   Check
# -------------------------------------------------------------------------
# TABLE     admin_log                               pass    pass    -
#           - Compare table checksum                                pass
# TABLE     clists                                  pass    pass    -
#           - Compare table checksum                                pass
# TABLE     data1                                   FAIL    pass    -
#           - Compare table checksum                                pass
#
# Object definitions differ. (--changes-for=server1)
#

--- `study1`.`data1`
+++ `study1`.`data1`
@@ -236,4 +236,4 @@
   `sys_pagetime_29` int(11) DEFAULT NULL,
   `sys_pagetime_30` int(11) DEFAULT NULL,
   PRIMARY KEY (`sys_RespNum`)
-) ENGINE=InnoDB AUTO_INCREMENT=303 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC
+) ENGINE=InnoDB AUTO_INCREMENT=301 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC

Is this the only table with AUTO_INCREMENT?

How many nodes?

SELECT MAX(id) from each node.

I'm pretty sure there is no data inconsistency -- each replicated statement will be done using RBR, so it will include the actual id, and not look at that setting on the CREATE TABLE.

(I do not know exactly where the 303 and 301 come from.)


Does my table qualify for Clustered Columnstore index? I have a large table with 30 columns in MSSQL 2014.
The data will be inserted in bulks several times a day and the data should be kept for a month.

The purpose of it is to select aggregated queries. So it seems that Clustered Columnstore index is perfect for this.
Except that one column is varchar and holds URL values that usually don't repeat.
Will it work well with Columnstore? I worked with it , but only on data that hold numbers or repeating strings (like domain).

UPDATE
1. For purpose of importing and purging the table will be partitioned.
2. I don't have the exact DDL right now, because we are at the design stage.
Thanks

You may find that a clustered columnstore index on your table works well but I would really want to avoid having that VARCHAR column in a columnstore index. One of the problems this can cause is dictionary pressure.

As you are looking to partition the table and alter data via partition switching I would suggest building a non-clustered columnstore index on your table that does not include the VARCHAR column that contains URLs. This will make the table read-only but that doesn't stop you loading data in and out via partition switching.

The way I have done this in the past is to bulk load the data into a staging table, do any post load processing there and then switch the whole table into an empty partition in the main table. It can be fiddly but very effective.

The other option is to use SQL Server 2016 which allows updateable non clustered columnstore indexes.


Where do Database Snapshots store the information what pages have been copied to the snapshot files? Database snapshot files are sparse files. Only when a page is modified the sparse file is being written to. Reads of the snapshot must be able to know whether the sparse file contains a copied page or not. How does SQL Server know where to read from? Where is this information stored?

From the Microsoft SQL Server 2012 Internals book:

The snapshot files contain only the data that has changed from the source.  For every file, SQL Server creates a bitmap that is kept in cache, with a bit for each page of the file, indicating whether that page has been copied to the snapshot.  Every time a page in the source is updated, SQL Server checks the bitmap for the file to see if the page has already been copied and if hasn't it is copied at that time.  This operation is called a copy-on-write operation.
The bitmap is stored in cache, not the file itself, so it is always readily available.  When SQL Server shuts down or the database is closed, the bitmaps are lost and need to be reconstructed at database startup.

When performing reads, SQL Server checks the snapshot bitmap to determine where to read from.


Java version shipped with DB2 v11? I can't seem to find any information of which Java version that will be used in the soon to be released DB2 V11.1. Does anyone else know?

It is Java version 1.8.0.
General availability date of Db2 LUW 11.1 is today by the way....


Help setting up Galera Arbitrator I am testing a MariaDB Galera Cluster set up with only 2 nodes, I have three servers haproxy, node1 and node2 . I was able to set it up and it worked fine until testing phase.
There are two types of test that I made:

The main node fails
The second node fails

While testing I came across this page which describes the problems.
So I was thinking of setting up garbd on both servers as what the guide said, but I don't think it is working(?)
Here is my garbd config:
group="galera-testing"
address="gcomm://10.10.10.16,10.10.10.17"
options="gmcast.listen_addr=tcp://0.0.0.0:4444"
log="/var/log/garbd.log"

If I run sudo garbd --cfg /etc/garbd.cnf the logs stop at: Note: I started this on both servers
INFO: (b363d915-923d, 'tcp://0.0.0.0:4444') turning message relay requesting off

And I can't continue using the terminal without killing it.
Now, I'm thinking of two options:

Setting garbd on my HAProxy Server making it act as the arbitrator or

Setting garbd on both nodes (im confused as the guide says to set it on both servers? what??)

Can anyone point me to the right direction? Thanks!
Note: I was able to test Galera Clusters with three nodes before + HAProxy, and it went better than with two nodes. But to satisfy my curiousity, I kinda want to learn this one works too.

Two node galera is a fundamentally broken design as it cannot maintain uptime without a quorum and the ability of a node to go down to aid recovery.
garb is a toy test thing and not a production tool.
Use 3 nodes all as full galera member nodes.
Use haproxy in a redundant configuration on all nodes. Use a different IP addresses for haproxy and galera without overlap.
If it can't be reliable in test then it won't be reliable in production.


Audit login trigger for permission changes sql server In my office some one changed the permissions of users on production database. so after that i have decided to write a ddl kind of trigger to audit permission changes. I have googled for this but i couldn't find a proper solution. So can you people suggest me few links or any sample code?

I would like to capture who changed the permissions and when permissions got changed.

Thanks in advance.

Check  this
out.

CREATE TRIGGER DDL_AUDIT_Logins ON ALL SERVER
FOR DDL_DATABASE_SECURITY_EVENTS AS

SET NOCOUNT ON;

DECLARE @EventsTable TABLE (
    EType NVARCHAR(max)
    ,EObject VARCHAR(100)
    ,EDate DATETIME
    ,EUser VARCHAR(100)
    ,ECommand NVARCHAR(max)
    );
DECLARE @EType NVARCHAR(max);
DECLARE @ESchema NVARCHAR(max);
DECLARE @DBName VARCHAR(100);
DECLARE @Subject VARCHAR(200);
DECLARE @EObject VARCHAR(100);
DECLARE @EObjectType VARCHAR(100);
DECLARE @EMessage NVARCHAR(max);
DECLARE @ETSQL NVARCHAR(max);

SELECT @EType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]',
 'nvarchar(max)')
,@ESchema = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]',
 'nvarchar(max)')
,@EObject = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]',
 'nvarchar(max)')
,@EObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]',
'nvarchar(max)')
,@DBName = EVENTDATA().value('
(/EVENT_INSTANCE/DatabaseName)[1]',
 'nvarchar(max)')
,@ETSQL = EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand/CommandText)[1]',
'nvarchar(max)');

INSERT INTO @EventsTable
SELECT @EType
    ,@EObject
    ,GETDATE()
    ,SUSER_SNAME()
    ,@ETSQL;

SET @EMessage = 'Login_Event: ' + @EType + CHAR(10) + 'Event Occured at: '
 + Convert(VARCHAR, GETDATE()) + CHAR(10) + 'Changed Login: ' + @EObject +
CHAR(10) + 'Changed by: ' + SUSER_SNAME() + CHAR(10) + 'Executed T-SQL: ' +
@ETSQL

SELECT @Subject = 'SQL Server Login changed on ' + @@servername;

EXEC msdb.dbo.sp_send_dbmail @recipients = 'DDL_Alert@companydomain.com'
    ,@body = @EMessage
    ,@subject = @Subject
    ,@body_format = 'HTML';

SET NOCOUNT OFF;
GO

Result:


Is there a SQL Server equivalent to "OVERRIDING USER VALUE" While googling I found about the OVERRIDING USER VALUE parameter for an INSERT for IBM's iSeries.

Is there an equivalent command for Microsoft SQL Server versions 2005 or newer to allow you to insert into a table that has an IDENTITY column and use the auto-assigned value instead of the user value passed in?

In this instance, the main goal is to help convince a superior from moving us off of using a GUID as the clustered primary key (the schema was developed pre-SQL 2000 where that was a decent idea, vs. now where it is a horrible idea) to adding an identity column to the tables and moving the clustered primary key to that and converting the old index to a non-clustered unique index.

The biggest push back is:

  There are a lot of places in the legacy code that use things like Insert into XXXX Select * from YYYY where ... where the number of columns is different based on run-time adjustable settings on the client's front end. Using a identity column would break those queries whereas GUIDs are unique and won't have the issue and it is too much work to rewrite all of the queries to use named columns due to the fact that the column names are variable per installed client.

So that is why SET IDENTITY_INSERT ON is not a option, as this will be a primary key column. I was hoping to make the argument that if we could use the SQL Server equivalent of OVERRIDING USER VALUE I could use an argument like "We can just add this small bit to the insert statements and we don't need to include the column names."

In SQL Server you actually shouldn't need to make any changes to the outlying code or add an INSTEAD OF trigger to make this work. Here is a quick example, tested on SQL Server 2012, but should work fine on 2005 as well:

CREATE TABLE dbo.source(bar INT, x UNIQUEIDENTIFIER PRIMARY KEY DEFAULT NEWID());
GO
CREATE TABLE dbo.[target](bar INT, x UNIQUEIDENTIFIER PRIMARY KEY);
GO

INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

ALTER TABLE dbo.[target] ADD TargetID INT IDENTITY(1,1);
GO

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2

Now I can also change the primary key and repeat the process:

ALTER TABLE [dbo].[target] DROP CONSTRAINT PK__target__3BD019E50386B4EA;
ALTER TABLE [dbo].[target] ADD CONSTRAINT PK__target__3BD019E50386B4EA
  PRIMARY KEY (targetID);

TRUNCATE TABLE dbo.source;
INSERT dbo.source(bar) SELECT 1;
GO
INSERT dbo.[target] SELECT * FROM dbo.source;
GO

SELECT * FROM dbo.[target];

Results:

bar   x                                      TargetID
---   ------------------------------------   --------
1     EFF8DAC4-FB3E-4734-80BE-6DC229846203   1
1     5036688D-C04A-45FC-920E-FF44D7D501D1   2
1     41FE97FF-7D45-46EB-8A0D-B2C3BA1E67EA   3

So I haven't had to change my bad code that uses insert/select without any column lists, as long as the source table doesn't also change and assuming that the only change to the target is the addition of an identity column.

PS here is how you can automate the generation of the IDENTITY columns (assuming you will want <tablename>ID):

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD ' + t.name + 'ID INT IDENTITY(1,1);'
FROM sys.tables AS t
WHERE name IN (...) -- you will need to fill in this part
AND NOT EXISTS
(
  SELECT 1 FROM sys.columns WHERE [object_id] = t.[object_id]
    AND (is_identity = 1 OR name = t.name + 'ID')
);

SELECT @sql;
-- EXEC sp_executesql @sql;

(Note that the SELECT output will show you roughly what the command looks like, but due to output limitations in SSMS and depending on how many tables you have, it won't necessarily show you the full command that will get executed when you uncomment the EXEC.)

And the drop / re-create of the primary keys:

DECLARE @sql NVARCHAR(MAX);

SET @sql = N'';

SELECT @sql = @sql + '
  ALTER TABLE ' +
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' DROP CONSTRAINT ' + k.name + ';
  ALTER TABLE ' +
  + QUOTENAME(OBJECT_SCHEMA_NAME(t.[object_id]))
  + '.' + QUOTENAME(t.name) + ' ADD CONSTRAINT '
  + k.name + ' PRIMARY KEY (' + t.name + 'ID);'
FROM sys.key_constraints AS k
INNER JOIN sys.tables AS t
ON k.parent_object_id = t.[object_id]
WHERE k.[type] = 'PK'
AND t.name IN (...); -- again, you'll want to identify the list of tables

SELECT @sql;
-- EXEC sp_executesql @sql;

And you'll want to do this while the database is in SINGLE_USER mode or while the application(s) are otherwise not able to connect to the database. You'll also want to test all this on a QA or dev system before unleashing any of it on production.

Now, this still isn't exactly best practice - I highly recommend you stop embedding SQL code in your apps, especially SQL code that does insert/select without specifying column lists.


Selecting the most specific match from one table So let's say there is a simple table for dogs:

CREATE TABLE IF NOT EXISTS dogs (
  id bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  breed char(20) NOT NULL,
  color char(20) NOT NULL,
  special_skill char(20) DEFAULT NULL
);

The real example of this table would have foreign key references for breed and color to an enum table and the dogs table would be pre-populated with values that match the cartesian products of breed, color.
So there is the guarantee that there will always be a record for any combination of breed and color.

And some simple sample data:

1, 'American Bulldog',  'White', 'Runs fast'
2, 'American Bulldog',  'White',  Null
3, 'American Foxhound', 'White',  Null
4, 'American Foxhound', 'Black',  Null
5, 'American Bulldog',  'Black',  Null

How would one go about building a query that would find the most specific match possible by provided query filters?

Say a person wants to find an American Bulldog that is White and Runs fast. They will get a record with Id = 1 from our current table. Another person looking for the same kind of dog but with a 'Black' color instead would find a record with Id = 5 even tough the person also specified to find a dog that Runs fast.

So in essence here would be some pseudo filters and results:

find by ('American Bulldog',  'White', 'Runs fast')
Returns id 1

find by ('American Bulldog',  'White')
Returns id 2

find by ('American Bulldog',  'Black', 'Runs fast')
Returns id 5

During my quest to find a solution to this I was only able to come up with some very hackish queries with a case statement in it and explicitly ordering results in certain ways to be able to pick the top most result. But I am curious if there is a simple version (uses basic SQL features not some case) possible for this that could be done using some DSL in a programming language and not falling back to plain SQL queries.

SELECT *
FROM dogs
ORDER BY CASE WHEN breed <=> @breed THEN @breed_weight ELSE 0 END
        +CASE WHEN color <=> @color THEN @color_weight ELSE 0 END
        +CASE WHEN skill <=> @skill THEN @skill_weight ELSE 0 END DESC LIMIT 1

The parameter weighting factors can be constant literals or varying query parameters.

find by ('American Bulldog',  'Black', 'Runs fast')
Returns id 5

@color_weight must be greater than @skill_weight. Backward record id=1 will be returned, and a random of them if weights are equal.

If some search condition is mandatory - move the comparison to WHERE clause and remove it from ORDER BY expression.

  Yeah the breed and color can basically be in the where clause, they will be mandatory.

SELECT *
FROM dogs
WHERE breed = @breed
  AND color = @color
ORDER BY (skill <=> @skill) DESC LIMIT 1


Update if not exists statement If I have a simple table with 2 columns and a composite primary key consisting of both columns, how do I rename one of the columns but only if it doesn't cause a primary conflict?

Here's a simplified example.

Take the following table:

User  | Role
------|----------------
Joe   | User
Joe   | Administrator
Brian | User
Anna  | Administrator
Anna  | Admin

Suppose I want to write the following query:

UPDATE UserRoles Set Role = 'Admin' WHERE Role = 'Administrator'

Without using cursors, how do I change my SQL Update statement to avoid renaming Anna (because that user already has the Admin role), but still rename Joe?

Is that even possible using set based SQL or is using a cursor the only way?

Thanks.

One way is

UPDATE a
SET [Role]= 'Admin'
FROM UserRoles a
WHERE a.[Role]= 'Administrator'
AND NOT EXISTS
  (
       SELECT NULL
       FROM dbo.UserRoles b
       WHERE b.[User] = a.[User] AND b.[Role] = 'Admin'
  );


Optimal database for large number of columns or rows Problem 1:

I have one file with 567 rows and 16,382 columns, most of which are floating point numbers. I have another file with 117,493 rows but only 3 columns. The contents deal with biology and genetics.

I have no idea what the numbers mean in the files mean. This is a college project where I have to be able to query different things given the files without knowing what the contents are. I am assuming we are not supposed to normalize the data because that would require understanding of the contents. We are graded on performance and reason for choosing which databases.

File 1: (567 rows, 16,382 columns)

I have been getting people surprised at the number of columns in this file. The columns are mostly genetic information with a bunch of numbers and cannot be normalized. I initially thought Postgresql would be good for File 1, but I read they are a row-oriented database, so it would be horrible. I read about Cassandra being good for column-oriented look-ups, but the problem is the most of the column and rows contain data and is mostly structured, except for a few areas with no values. Would it still be a better idea to use Cassandra?

Operations for File 1:

simple look-up
find mean
find standard deviation

I am not able to confirm on the general performance between SQL and NoSQL on aggregate functions such as calculating mean and standard deviation. So this is another factor in selecting my database. I would assume Cassandra is faster than Postgresql here since Cassandra is column-oriented.

File 2: (117,493 rows, 3 columns)

Every single row and column contains data. I am guessing that Postgresql is good here since there are only 3 column but 117,493 rows, Postgresql is row-oriented, and there are no missing data. Is there a better NoSQL alternative in this case? Would a key-value store NoSQL be better since this will mostly be used for retrieval?

Operations for File 2:

simple look-up

Problem 2:

Most of the large files I have are related by some kind of ID, such as File 1 and File 2. If I use Postgresql for File 2 and Cassandra for File 1, is there a considerable performance loss? Usually you would store related tables in an RDMS, but does the number of columns for File 1 mean it is better to use a hybrid approach?

PostgreSQL

  @Dobob everything can be normalized :) If it's not very secret, what sort of data it is? (Asking as a geneticist-turned-DBA.)

This is generally true. And you should start at looking how you can normalize. Clearly you're averse to the idea, and you can't always normalize. For instance, a raster is often a collection of readings from a scientific instrument, and you can't really denormalize that. They're taken at the same time, represent the same sample and frequently have 1,000s of data points. (Though now you can store them in PostGIS).

Solution for Problem 1

PostgreSQL does not support 16,383 columns on a table (or more than 1,600+ for that matter), but it does supports SQL Arrays. You can probably make use of that,

CREATE TABLE foo AS
  SELECT
    r AS id,
    array_agg(c) AS genetic_stuff
  FROM generate_series(1,567)
    AS r
  CROSS JOIN LATERAL generate_series(1,16382)
    AS c
  GROUP BY r;

Now you have a table that meets the first criteria but isn't insane.

          Table "public.foo"
    Column     |   Type    | Modifiers
---------------+-----------+-----------
 id            | integer   |
 genetic_stuff | integer[] |

Finding average is simple..

SELECT id, avg(x)
FROM foo
CROSS JOIN LATERAL unnest(genetic_stuff)
  AS t(x)
GROUP BY id;

And for that whole operation Execution time: 3865.308 ms! Can complain about that. Feel free to use a MATERIALIZED VIEW if you want to cache that average.

For Standard Deviation just use the appropriate aggregate function in the place of avg(). Execution times are about the same.

I'm not sure how you're querying this here, but without an index (worst case situation) is pretty simple and fast (considering it's a seq scan) scanning through an array with 16,000 items isn't slow either for a modern a CPU, and on PostgreSQL 9.6 this seq scan should even run in parallel (afaik).

SELECT * FROM foo
WHERE genetic_stuff @> ARRAY[5];

Can you use an index? Sure. From the intarray module,

  Two GiST index operator classes are provided: gist__int_ops (used by default) is suitable for small- to medium-size data sets, while gist__intbig_ops uses a larger signature and is more suitable for indexing large data sets (i.e., columns containing a large number of distinct array values). The implementation uses an RD-tree data structure with built-in lossy compression.

Let's give it a shot...

CREATE INDEX ON foo
  USING gist(genetic_stuff gist__intbig_ops);
VACUUM FULL foo;

                                                          QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------
 Index Scan using foo_genetic_stuff_idx on foo  (cost=0.14..8.16 rows=1 width=22) (actual time=0.119..47.846 rows=567 loops=1)
   Index Cond: (genetic_stuff @> '{5}'::integer[])
 Planning time: 0.072 ms
 Execution time: 47.948 ms
(4 rows)

Not sure what more you want than index scan on that. Probably be more useful if not every row had an element of 5, but you know it's only sample data.

Anyway have at it.

Solution for Problem 2

Shy of that, the other thing isn't a problem at all a bit of overhead because Pg rows are fat, but you're not talking many rows and ultimately it shouldn't matter this below table meeting that criteria is only 5088 kB on my system. Hardly worth concerning yourself with.

CREATE TABLE baz
AS
  SELECT
    x AS id,
    x*2 AS x2,
    x*3 AS x3,
    x*4 AS x4
  FROM generate_series(1,117493)
    AS t(x);


Get database creation timestamp from PostgreSQL? Is there a way to get a date and time of when a particular database was created in Postgresql?

The \list command does not provide a timestamp.

There's no built-in method to do so, but there are workarounds.

Note however that even PG_VERSION changes when the server is updated to a new major version.

So the only reliable way to store a creation date is to make a one-row table with the date in it.

If this feature is important to your application you could consider funding its development. I think it'd be pretty trivial to implement.


Checking for ApplicationName in a transaction through a Before Trigger I would like to be able to build a trigger that checks the ApplicationName coming across on a transaction, and if the ApplicationName is of a certain value, then I want to throw an error, saying the user is not allowed to make this transaction.

Is there a way to create this script?

Yes, you can use the APP_NAME() function in SQL.

This is set in the connection string though so it isn't reliable

Why not limit users? If a user has permissions, they can run msqry32 which is on any PC with Office installed, set a connection string using ODBC and make it look like what they want...


Orphaned users with their respective database name I need to get a list of the orphaned users in each DB, together with the database name in which they are found in. I did the below which gives the orphaned users in each DB, but I cannot figure out how to include another column next to each user found to show in which the DB. I tried using the DB_NAME() but to no avail.

use master;
GO

CREATE PROCEDURE orphaned_u
AS
BEGIN
DECLARE @command varchar(1000)
SELECT @command = 'use ?
if DB_ID("?")>4
EXEC sp_change_users_login ''Report'' '
EXEC sp_MSforeachdb @command
END

CREATE TABLE tempTable (
    user nvarchar(1000),
    User_sid sql_variant
)

INSERT INTO tempTable
EXEC master..orphaned_u

SELECT * FROM  dbo.tempTable

I have already looked at Find orphaned users but preferable I use the above code and find a way to display the database name in a column with the already built-in stored procedure sp_change_users_login ''Report''

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

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

use master;
GO

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

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

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

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

Running the proc

EXEC orphaned_u

Returns my two orphaned users

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


Advice on how to approach data cleaning & importing I am importing and cleaning data from a datasource that have records like this:

The data needs sorting and rearranging based on the Event Type.

The destination schema would be something like this:

Create Table dbo.Destination
(
DestinationId int not Null primary key clustered,
EventType int not null,
fkyCustomerId int not null,
         Constraint FK_Destination_Customers References dbo.Customers (pkyCustomerId),
fkyCategoryId int not null
         Constraint FK_Destination_Categories References dbo.Categories (pkyCategoryId),
fkyCompanyId int not null
         Constraint FK_Destination_Companies References dbo.Companies(pkyCompanyId),
fkyLocationId int not null
        Constraint FK_Destination_Locations References dbo.Locations(pkyLocationId),
Amount money not null
        Constraint DF_Destination_Amount Default(0)
)
-- Lookup table
Create Table dbo.Categories
(
pkyCategoryId int not null primary key clustered,
SourceCategoryId char(10) Not Null,
CategoryName varchar(50) Not Null
)
-- Lookup table
Create Table dbo.Companies
(
pkyCompanyId int not null primary key clustered,
SourceCompanyId varchar(10) Not null,
CompanyName varchar(50) Not Null
)
-- Lookup Table
Create Table dbo.Locations
(
pkyLocationId int not null primary key clustered,
SourceLocationId varchar(10) Not Null,
LocationName varchar(50) Not Null
)

For each Event Type, and there could be hundreds of event types, there are rules on what data exists in each of the col columns.

So for Event Type 1234, Col0 has the mapping for the Categories lookup and Col1 has the mapping for the Companies lookup.

However, for Event Type 5000, Col0 contains the mapping for the Companies lookup and Col1 contains the mapping for Locations.

Also, there are situations when there will be null values for all Col columns which means I need to map these to default category, company etc values.

I need to design an import process that can handle the existing rules and potentially new rules. The import will handle approx 2.5 millions rows per day and increasing.

What would be a good way to implement this kind of import. Nothing is set in stone here, apart from the the source data, so I am more than willing to entertain the idea of changing the schema etc.

The final goal is to transform the source data so it can be easily used for reporting purposes.

I think the biggest challenge will be doing the event type transformations efficiently, so I'm going to focus on that and ignore the schema completely.

My first thought (read: I've never actually tried this) is to use dynamic SQL to build up a SELECT statement that will do the data transformation such that values end up in their correct columns (i.e., Col0 --> CompanyId, or whatever). This boils down to a mapping table consisting of the event type, the source column, the target column, and a default value if required.

Once you have the mapping table (or tables, depending on how complex this actually is), you can use it to dynamically build a SQL statement of the form:

SELECT
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col0, 'DefaultCategory')
        END
    ) AS Category,
    (
        CASE EventType
            WHEN 1234 THEN COALESCE(Col1, 'DefaultCompany')
            WHEN 5000 THEN COALESCE(Col0, 'DefaultCompany')
        END
    ) AS Company,
    (
        CASE EventType
            WHEN 5000 THEN COALESCE(Col1, 'DefaultLocation')
        END
    ) AS Location
    FROM RawSourceTable

Build the statement as part of the ETL process. If you need to add a new event type, just add the required column mappings to the mapping table, and they'll be handled the next time the ETL process runs.

Once the source data is in this kind of state, the rest of the import process should be pretty standard.


Cannot `create function` in plpython3u, permission denied As postgres user, I have create extension plpython3u; in my database

then I have set the plpython3u to trusted: select lanpltrusted from pg_language where lanname like 'plpython3u'; returns true

but when my db_user tries

  create function check_data_valid(id bigint)
    returns boolean
    as $$
    -- ...
    return true
    $$ language plpython3u;

I got the error: permission denied for the language plpython3u

So, with my postgres user then I have tried: grant usage on plpython3u to db_user and grant execute on plpython3u to db_user but both returns the error:

relation python doesn't exist

maybe it's because it's an extension... however, I don't what to do so as to create my stored procedure.

First don't make plpython3u into a trusted language.  This is bad.  You are giving access to all kinds of things on the database, the filesystem, and the like in ways that could be horribly abused.  Please reconsider.

Now from your comments you want to keep the admin permission separate from the normal user permission.  The way to do this is to make sure the superuser is NOINHERIT and the user is granted the role.  You can:

 CREATE ROLE mydba WITH SUPERUSER NOINHERIT;

 GRANT mydba TO myuser;

Then logged in as myuser then you can:

 SET ROLE mydba;

And you get superuser privileges.  You can use this to then to temporarily escalate your permissions in a way similar to sudo on *nix systems.


Where to put single quotes? I am trying to execute the below statement which creates a database called service2019:

DECLARE @dbname VARCHAR(50);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));

EXEC (
'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf'''  + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'
)

I get the correct database name and logical names. The problem is I get wrong physical names:

'service2019'.mdf

'service2019'.ldf

Could someone explain to me how these quotes actually work? There are some parts where it is clear but sometimes it becomes a challenge to figure out where to put those quotes.

When developing Dynamic SQL, I suggest you start your efforts by making heavy use of the PRINT command rather than EXEC as things can (and often do) go wrong with Dynamic SQL.  Printing out your statement instead of actually running it will help you avoid some issues if you end up having syntactically correct code that is logically wrong.

In this specific case, I'm adding a new parameter, @DSQL VARCHAR(MAX), setting that variable with the command, and printing it out, as follows:

DECLARE @dbname VARCHAR(50), @DSQL VARCHAR(MAX);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));

SET @DSQL = 'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME =' + '' + @dbname +'' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + '''' + '''.mdf'''  + ', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =' + '' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''' + '''' + @dbname + ''''+'''.ldf''' + ', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'

PRINT @DSQL

Which outputs the following:

CREATE DATABASE service2019 ON  PRIMARY
( NAME =service2019, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''service2019''.mdf', SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )
 LOG ON
( NAME =service2019_log, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\''service2019''.ldf', SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )

As you can see, you have too many quotes around service2019 in your mdf and ldf filepaths.  Upon further investigation, it also looks like you have more string concatenations than necessary.  What I suggest you do is limit concatenations (i.e. +) to only the times where you transition between variables and static strings.  I'm also a fan of formatting my DSQL, so I'm also including a Carriage Return/Line Feed (i.e. \r\n) and Tab (i.e. \t) variables so your output looks a little cleaner.  Removing the unnecessary concatenations, including the formatting, and cleaning up the quotes gets me the following:

DECLARE @dbname VARCHAR(50), @DSQL VARCHAR(MAX), @CRLF CHAR(2), @TAB CHAR(1);
SET @dbname = 'service' + CAST(YEAR(GETDATE()) AS VARCHAR(4));
SET @CRLF = CHAR(13) + CHAR(10)
SET @TAB = CHAR(9)

SET @DSQL = 'CREATE DATABASE ' + @dbname  + ' ON  PRIMARY
( NAME = ' + @dbname + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\' + @dbname + '.mdf'' , SIZE = 10MB , MAXSIZE = UNLIMITED, FILEGROWTH = 100KB )'
+ @CRLF + @TAB + 'LOG ON'
+ @CRLF + '( NAME = ' + @dbname + '_log' + ', FILENAME = ''C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQL2014TEST\MSSQL\DATA\' + @dbname + '.ldf'' , SIZE = 10MB , MAXSIZE = 2048GB , FILEGROWTH = 100KB )'

PRINT @DSQL
--EXEC(@DSQL)   /* <--- Uncomment out if you want to run this */

For more formatting suggestions, discussions of pitfalls that come with DSQL, and pretty much anything else, I'll point you to (what I consider the defacto guide on DSQL) The Curse and Blessings of Dynamic SQL by Erland Sommarskog.


Aggregate discarding values in one column that haven't a match in another column Say, I have a table representing colored and labeled items inside numbered boxes.
Each box can not contain more than one item with a particular label, but items with the same label (and the same or a different color) may be unique in other boxes.

Oversimplifying, and using PostgreSQL, we can take the following table:

CREATE TABLE items (
    label character varying,
    color character varying,
    box_number integer
);
INSERT INTO items VALUES
  ('a','red',1),
  ('b','blue',1),
  ('c','blue',1),
  ('a','red',2),
  ('c','green',2),
  ('d','blue',2),
  ('b','red',3),
  ('d','green',3);

I want to know the label and the color of all the items inside the box number 3, but also all the box numbers where an item with the same label can be found. In other words, I'm trying to:

SELECT label, boxes
FROM (
  SELECT label, array_agg(DISTINCT box_number) AS boxes
  FROM items
  GROUP BY label
) AS sub1
WHERE 3 = ANY(boxes);

But I also need to return the color column, showing only the color of the item inside the box number 3.

For the example data, the output should be this:

label | color | boxes
------+-------+------
b     | red   | 1,3
d     | green | 2,3

Here's an SQL Fiddle for the example.

You can use a correlated subquery:

SELECT label, color
    , (SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label)) AS boxes
FROM   items i
WHERE  box_number = 3;

Or (more modern) with LEFT JOIN LATERAL:

SELECT i.label, i.color, b.boxes
FROM   items i
LEFT   JOIN LATERAL (
   SELECT ARRAY (SELECT box_number FROM items WHERE label = i.label) AS boxes
   ) b ON true
WHERE  box_number = 3;

What is the difference between LATERAL and a subquery in PostgreSQL?

Using an array constructor since that is faster than array_agg() for a single column in the SELECT list.

You could also just use a self-join and aggregate:

SELECT i.label, i.color, array_agg(b.box_number) AS boxes
FROM   items i
JOIN   items b USING (label)
WHERE  i.box_number = 3
GROUP  BY i.label, i.color;

I'd expect that to be a bit slower. Test with EXPLAIN ANALYZE.

SQL Fiddle.

We certainly don't need DISTINCT here, since your data definition says:

  Each box can not contain more than one item with a particular label

.. which is surely enforced with a UNIQUE constraint (or equivalent) on (label, box_number)?
This would also automatically provide the second index to make this query fast - besides the obvious index on (box_number). Details:

Is a composite index also good for queries on the first field?


Cannot stop MySQL 5.5 replication threads We have a master-master replication setup using MySQL 5.5. On one of the servers I am trying to stop the slave thread, but without success. To ensure that the replication does not connect to a real server, I set MASTER_HOST to a nonexistent address. I have run the STOP SLAVE command multiple times, but I am still seeing Slave_SQL_Running: Yes when I run SHOW SLAVE STATUS;.

This is what gets logged (every few seconds):

[Note] Error reading relay log event: slave SQL thread was killed
[ERROR] Slave I/O: Fatal error: Invalid (empty) username when attempting to connect to the master server. Connection attempt terminated. Error_code: 1593
[Note] Slave I/O thread killed while connecting to master
[Note] Slave I/O thread exiting, read up to log 'FIRST', position 4
[Note] Slave SQL thread initialized, starting replication in log 'FIRST' at position 0, relay log '/var/log/mysql/mysql01-relay-bin.000002' position: 4

I want all the replication threads to go away. How can I do this?

To halt replication from starting up on a restart of mysqld you should add this to my.cnf

[mysqld]
skip-slave-start

Don't worry about the SQL Thread. It reads from the local relay logs. Once all SQL statements and changes are read from the relay logs and processed, the SQL threadwill just sit there. You can prove that by running SHOW SLAVE STATUS\G and watching

Relay_Log_Space: If the IO Thread is disabled, this should no longer increase.
Exec_Master_Log_Pos:  Once all SQL is processed from relay logs, this remains still

As long as the IO Thread is dead (run STOP SLAVE IO_THREAD;), replication from the remote master is no longer possible, especially since you set the address to a nonexistent server.

My guess is that you are using row-based replication, If you are, the SQL thread is probably staring at an incomplete transaction and waiting for the IO thread to complete a block of changes that never comes over from the master. When that is the case, you can kill the SQL thread by running

KILL connectionid;

for the connection of the system user still active for replication's SQL thread. I say this because the MySQL Documentation on STOP SLAVE says:

  In MySQL 5.5, STOP SLAVE waits until the current replication event group affecting one or more nontransactional tables has finished executing (if there is any such replication group), or until the user issues a KILL QUERY or KILL CONNECTION statement. (Bug #319, Bug #38205)


How to Get Backups & Restores Start and Finish Times? I backup my databases from prod environment to file server. After taking my backups, i restore them to my report server. And i want to know that if i can get information about when backup and restore has started and finished or not. How can i query how long it takes to complete its each process?

The backups of the database can be retrieved using the following statement:

---------------------------------------------------------------------------------
--      Database Backups for all databases For Previous Week
---------------------------------------------------------------------------------
SELECT

/* Columns for retrieving information */

   -- CONVERT(CHAR(100), SERVERPROPERTY('Servername')) AS SRVNAME,
   msdb.dbo.backupset.database_name,
   msdb.dbo.backupset.backup_start_date,
   msdb.dbo.backupset.backup_finish_date,
   msdb.dbo.backupset.expiration_date,
   CASE msdb..backupset.type
       WHEN 'D' THEN 'Full'
       WHEN 'I' THEN 'Diff'
       WHEN 'L' THEN 'Log'
   END AS backup_type,
   -- msdb.dbo.backupset.backup_size / 1024 / 1024 as [backup_size MB],
   -- msdb.dbo.backupmediafamily.device_type,
   msdb.dbo.backupmediafamily.physical_device_name,
   -- msdb.dbo.backupmediafamily.logical_device_name,
   -- msdb.dbo.backupset.name AS backupset_name,
   -- msdb.dbo.backupset.description,
   msdb.dbo.backupset.is_copy_only,
   -- msdb.dbo.backupset.is_snapshot,

   msdb.dbo.backupset.first_lsn,
   msdb.dbo.backupset.last_lsn,
   msdb.dbo.backupset.database_backup_lsn,
   msdb.dbo.backupset.checkpoint_lsn,
   msdb.dbo.backupset.differential_base_lsn,
   msdb.dbo.backupset.fork_point_lsn,

   msdb.dbo.backupmediaset.name,
   msdb.dbo.backupmediaset.software_name,
   msdb.dbo.backupset.user_name,
   'EOR'

FROM   msdb.dbo.backupmediafamily
   INNER JOIN msdb.dbo.backupset
   ON msdb.dbo.backupmediafamily.media_set_id = msdb.dbo.backupset.media_set_id
   INNER JOIN msdb.dbo.backupmediaset
   on msdb.dbo.backupmediaset.media_set_id = backupmediafamily.media_set_id

/* ----------------------------------------------------------------------------
        Generic WHERE statement to simplify selection of more WHEREs
-------------------------------------------------------------------------------*/
WHERE 1 = 1

ORDER BY
          2 desc, -- backup start
          1,      -- database name
          3 desc  -- backup end

The restore history table only contains when the database was restored and not how long it took. However, if you run the following statement just after the restore has occurred, then you will have an estimate of how long it took:

SELECT
    rh.destination_database_name AS RestoredDatabaseName,
    rh.restore_date              AS RestoreDate,
    GETDATE()                    AS CurrentDateAndTime,
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST((CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
    RIGHT('0' + CAST(CAST(DATEDIFF(ss,rh.restore_date,GETDATE()) AS NVARCHAR(20)) % 60 AS VARCHAR),2)                          AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh

This statement basically takes the current date & time (GETDATE()) and the time the restore was performed, and calculates the difference in HH:MM:SS.

Additionaly you can capture the time before and after the restore using GETDATE() as per Dat Nguyen's suggestion and then calculate the difference:

USE [master]
GO
ALTER DATABASE [YOURDATABASE] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO
DECLARE @RestoreStarted  AS DATETIME
DECLARE @RestoreFinished AS DATETIME
SELECT @RestoreStarted=GETDATE() -- capture the date/time before the restore starts
RESTORE DATABASE [YOURDATABASE] FROM  DISK = N'C:\SQL\Backup\YOURDATABASE\FULL\YOURDATABASE_FULL_20191109_223000.bak' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 5
SELECT @RestoreFinished=GETDATE() -- capture the date/time after the restore finishes

SELECT TOP 1 rh.destination_database_name AS RestoreDatabase, rh.restore_date AS RestoreDateInRestoreHistory, @RestoreStarted AS RestoreStarted, @RestoreFinished AS RestoreFinished,
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 3600 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST((CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) / 60) % 60 AS VARCHAR),2) + ':' +
RIGHT('0' + CAST(CAST(DATEDIFF(ss, @RestoreStarted,@RestoreFinished) AS NVARCHAR(20)) % 60 AS VARCHAR),2) AS [Restore HH:MM:SS]
FROM msdb.dbo.restorehistory AS rh
WHERE rh.destination_database_name = 'YOURDATABASE'
ORDER BY 2 desc

This returns the following output:

RestoreDatabase | RestoreDateInRestoreHistory | RestoreStarted          | RestoreFinished         | Restore HH:MM:SS
----------------+-----------------------------+-------------------------+-------------------------+--------------------
YOURDATABASE    | 2019-11-29 10:20:43.577     | 2019-11-29 13:16:29.590 | 2019-11-29 13:16:34.800 | 00:00:05

Reference Reading

SQL SERVER – Convert Seconds to Hour : Minute : Seconds Format
restorehistory (Transact-SQL)
backupmediafamily (Transact-SQL)
backupset (Transact-SQL)
backupmediaset (Transact-SQL)


SUBSTRING and IF statement I have varchar column, its name is column1 and has records like 111232435-0 and 1123443530. The last digit is not always 0; it can be 0-9.

Some of them are digits with (-), other are digits. I want to change digits to digits with(-).

For example: 1123443530 -> change to -> 112344353-0

Pseudo-code:

if record just be digit just update in column3
else
the last digit separate by (-)
and then record update in column3

In SQL Server 2012 and put this new record and records with (-) in new column.

What should I do?

Another alternative is to use STUFF function.

IF OBJECT_ID('tempdb..#ExampleValues') IS NOT NULL
    DROP TABLE #ExampleValues

CREATE TABLE #ExampleValues (
    ExampleValue VARCHAR(100),
    UpdatedValue VARCHAR(100))

INSERT INTO #ExampleValues (ExampleValue)
VALUES
    ('112344353-0'),
    ('11443-8'),
    ('112344-9'),
    ('11234435348'),
    ('11234448712')

UPDATE T SET
    UpdatedValue = CASE
        WHEN T.ExampleValue NOT LIKE '%-%'  -- If the literal does not contain a hyphen
            THEN STUFF(
                T.ExampleValue,             -- Put in the literal
                LEN(T.ExampleValue),        -- Just before the last character
                0,                          -- While replacing 0 characters ("stuffing" in between)
                '-')                        -- Place a hypen!
        ELSE
            T.ExampleValue END
FROM
    #ExampleValues AS T

SELECT
    T.ExampleValue,
    T.UpdatedValue
FROM
    #ExampleValues AS T

Result:

ExampleValue    UpdatedValue
112344353-0     112344353-0
11443-8         11443-8
112344-9        112344-9
11234435348     1123443534-8
11234448712     1123444871-2


Connecting pgAdmin3 to Postgres on Heroku I'm running a Heroku Postgres database (Database) in association with a Django app.  The docs that refer to the add-on are here.  I thought this would be a worthwhile question for the community because Heroku alerted its users today that it will be migrating everything off the shared database add-on that used to be default.

I'm having some trouble connecting pgAdmin3 to the Database.

As per the instructions in the link above, I grab the Database's credentials with the following command:

$ heroku pg:credentials HEROKU_POSTGRESQL_OLIVE

I'm using a homebrew installation of Postgresql on Mac OSX.

In the meantime, I have:

switched SSL on in my pg_postgresql.conf;
added the setting: listen_addresses = '*'
and added host    all         all         0.0.0.0/0         md5
and restarted my postgres server.

Then I go to pgAdmin3 and select File > Add Server.

I enter the credentials provided by Heroku as follows (first is Heroku credential name, second is pgAdmin field name):

In the "Properties" Tab:

dbname into Name;
host into Host;
port into Port;
I leave the pgAdmin field for "Maintenance Database" blank;
user into Username;
password into Password;

In the SSL Tab:

Im the field for SSL, I select "require" (as instructed by Heroku).

I hit enter, and pgAdmin thinks, then throws an error as follows:

An error has occurred:

Error connecting to the server: FATAL: permission denied for database "postgres"
DETAIL: User does not have CONNECT privilege.

Not sure what to do at this point.  Would appreciate any pointers.

Thanks to @araqnid on Stack Overflow for the answer here.

Quoted Verbatim from there:

Open the "Properties" of the Heroku server in pgAdminIII and change the "Maintenance DB" value to be the name of the database you want to connect to.
The default setup is suitable for DBAs et al who can connect to any database on the server, but apparently that isn't true in your case.

This worked for me!


Transacton log backup What happen with the active transaction log when I stop SQL Server? Under the stop process all transactions will write to the db? Or will put when the db will starting...

Is it possible when I stop the SQL Server that not all transactions will be written into the db? Uncommitted transaction will stay in the transaction log and / or transaction log backup only?

If you assume the following:

1 - Clients are actively using the SQL Server, sending inserts,
updates and deletes.
2 - Power loss causes the SQL Server to crash.

On startup, the server will do a brief recovery check on all of the databases and will rollback any transactions that were "in-flight" at that time.  Note that any committed transactions will be preserved, only the ones where SQL Server had not done an acknowledge back to the client.

Caveats:

If you have delayed durability turned on, then you may lose more than
that.
If you suffer disk/file corruption as a result of the power loss /
server interruption then that will also hurt you here.

When the database comes up, it will be in a transactionally consistent state, depending on the size of the transaction that was in flight, the server may have to roll back quite a bit of data, but it will be transactionally consistent.


SnapManager point in time restore problem When using a clone-backup command in SnapManager (SMSQL), we are not able to restore the most recent logs, because they are not included in the snapshot.  Does anyone have any ideas to help get those logs into the snapshot to be restored?

We often restore the previous night's backup.  This is done with -restoreLastBackup 0 and -TransLogsToApply 0 parameters.

We desire the ability to restore the most recent transaction logs, which are taken every 15 minutes.   This seems to be something that is promised in the product.  And let's face it, it would be a pretty terrible product if you couldn't restore your most recent data.   So something is amiss.

In order to restore those logs, we simply remove the TransLogsToApply parameter.  So the command is as follows:

clone-backup
-Server ProdServer01
-Database MyDatabase
-RestoreLastBackup 0
-TargetServerInstance TestServer01

So the command begins, and mounts a snapshot on TestServer01.   But the problem is that the most recent logs are not in that snapshot.  So some nasty errors are produced:

Applying transaction log backup - 62...

[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]

Retrying to obtain new log backup path (2)...

[C:\Program Files\NetApp\SnapManager for SQL Server\SnapMgrMountPoint\MPDisk001\SMSQL_SnapInfo\VDISK_M&N\LogBackup\LOGINST_ProdServer01\LOGDB__MyDatabase\11-05-2013_14.55.56__MyDatabase.TRB]

Failed to find the specified log backup file using new log backup path.
Error Code: 0xc00408c4
Unable to locate the specified transaction log backup dump file to restore.

This is still a problem, so I’m not completely satisfied.  The IT department will upgrade to SnapManager 7.0 soon (we have 5.2 at this time), so maybe that will resolve it as well.   But I did find a viable way to restore the latest transaction logs.   It’s inconvenient, but it works.

Clone last night’s backup with no recovery
Command to run in SnapManager Powershell:
clone-backup
-Server Prod01
-Database MyDatabase
-RestoreLastBackup 0
-TransLogsToApply 0
-TargetServerInstance Test01
-RecoverDatabase $false
Copy all relevant logs from Production to Test. The only ones needed were those taken after the full backup.
Use SQL below to query file system where you copied the files. This will generate “RESTORE LOG” statements
Copy generated SQL to a new window and execute. This should run a few minutes.
Run RESTORE DATABASE [ MyDatabase__Clone] WITH RECOVERY to make the database available to query

SQL:
CREATE TABLE #DirectoryTree (
id int IDENTITY(1,1)
,subdirectory nvarchar(512)
,depth int
,isfile bit);

INSERT #DirectoryTree (subdirectory,depth,isfile)
EXEC master.sys.xp_dirtree ‘B:\LogRestoreTest’,1,1;

SELECT *,
‘RESTORE LOG [ MyDatabase__Clone] FROM DISK = ”B:\LogRestoreTest\’ + subdirectory + ”’ WITH NORECOVERY;’
FROM #DirectoryTree
WHERE isfile = 1 AND RIGHT(subdirectory,4) = ‘.TRB’
ORDER BY id;

This post was helpful in knowing how to use master.sys.xp_dirtree:

http://www.patrickkeisler.com/2012/11/how-to-use-xpdirtree-to-list-all-files.html


Temporal Validity and Primary/Foreign Key Relationships I've read through several of the oracle tutorials that show temporal validity and time features. However, in the examples I'v read, there is no primary key used in the demo tables.

http://docs.oracle.com/cd/E16655_01/appdev.121/e17620/adfns_design.htm#ADFNS1005
http://www.oracle.com/webfolder/technetwork/tutorials/obe/db/12c/r1/ilm/temporal/temporal.html

Should primary keys be added to these tables?
I'm asking because I'm wondering about how one of these temporal tables should be referenced by another table. Can I add a foreign key from one temporal table to another?

If I add a pk/ foreign key realtionship, and then update the referenced in the table with the pk, the table with the fk points to a record that is no longer relevant.... does temporal data break normal primary key - foreign key relationships? And if so, how does this affect performance, do I just use a normal column as the 'foreign key' and select the right one for a referenced time period in a query?

Does anybody know of or have any examples or tutorials handy that show temporal data with normal or pseudo-normal pk / fk usage?

Thanks

Unfortunately present SQL or its implementations in all RDBMS products does not fully support temporal relations.

Only Teradata and DB2 have some features implementing constraints having time in the two or more of columns.

My example:

SET SCHEMA TEST;

CREATE TABLE PRODUCT (
product_ID INT NOT NULL,
suplier NVARCHAR(50),
PRIMARY KEY (product_ID)
)
;

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID)
)
;

ALTER TABLE CAMPAIGN
ADD CONSTRAINT XFK_campaign_productid
FOREIGN KEY (product_ID) REFERENCES PRODUCT (product_id)
ON DELETE NO ACTION;

If you add temporal columns business_startdate and business_enddate and optionally
transaction_starttime and transaction_endtime, you cannot enforce referential integrity anymore.

You might have to implement triggers or stored procedures or application level coding if you want to implement a constraint like this:

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
transaction_starttime TIMESTAMP NOT NULL,
transaction_endtime TIMESTAMP NOT NULL,
price DECIMAL,
PRIMARY KEY (campaign_ID,business_startdate,transaction_starttime)
)
;

For DB2 there exist a temporal primary key constaint in the following syntax:

CREATE TABLE CAMPAIGN  (
campaign_ID INT NOT NULL,
product_ID INT NOT NULL,
business_startdate DATE NOT NULL,
business_enddate DATE NOT NULL,
price DECIMAL,
PERIOD BUSINESS_TIME (business_startdate,business_enddate),
PRIMARY KEY (campaign_ID,BUSINESS_TIME WITHOUT OVERLAPS)
)
;


Sort output of plpgsql function I'm creating a function in Postgres 9.6 that compares two schemas with the same tables. I want to find tables which are empty in one schema but populated in the other. I'm avoiding the reltuples attribute found in the information schema because it is not guaranteed to be correct.

So far I have this function:

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(schema1 text, schema2 text)
RETURNS TABLE(tablename text, schema1_ct integer, schema2_ct integer, match boolean) AS $$
DECLARE
    schema1_tables CURSOR FOR
        select pg_tables.tablename
        from pg_tables
        where schemaname ~ schema1
        order by tablename;
        schema1_ct int;
        schema2_ct int;
BEGIN
    FOR table_record IN schema1_tables LOOP
        EXECUTE 'SELECT count(*) FROM ' || schema1 || '.' || table_record.tablename INTO schema1_ct
        EXECUTE 'SELECT count(*) FROM ' || schema2 || '.' || table_record.tablename INTO schema2_ct
        RETURN QUERY EXECUTE 'SELECT ''' || table_record.tablename || '''::text,' || schema1_ct || ',' || schema2_ct || ',' (schema1_ct >= 0) = (schema2_ct >= 0)
END LOOP
END; $$ LANGUAGE pgpgsql

But I'm really only interested in the cases where the output's match column is false, that is, one table is empty and one table is not empty. How I can I sort my output so that row where match = false are shown first?

While returning rows from a loop directly, the sort order is determined by the loop. PL/pgSQL builds up the result set and you can still raise an exception to prevent the function from returning anything until the last moment. (There is a code example in the manual.) But there is no way to re-order the result set before finally returning. You would have to add ORDER BY to the function call for that:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar')
ORDER  BY match;

FALSE sorts before TRUE, so non-matches come first.

  But I'm really only interested in the cases where the output's match column is false.

So don't do more work than required. This function does what you need, efficient and safe against SQL injection (unlike your original):

CREATE OR REPLACE FUNCTION public.compare_schemas_by_table_emptiness(_schema1 text, _schema2 text)
  RETURNS TABLE(tablename text, schema1_ct bigint, schema2_ct bigint)
  LANGUAGE plpgsql AS
$func$
BEGIN
   FOR tablename IN
      SELECT quote_ident(t.tablename)  -- must be table-qualified, due to dupe in OUT parameters!
      FROM   pg_catalog.pg_tables t
      WHERE  t.schemaname = _schema1
      ORDER  BY 1
   LOOP
      EXECUTE format(
         'SELECT (SELECT count(*) FROM %1$I.%3$s)
               , (SELECT count(*) FROM %2$I.%3$s)
          WHERE  EXISTS (SELECT FROM %1$I.%3$s)
              <> EXISTS (SELECT FROM %2$I.%3$s)'
       , _schema1, _schema2, tablename)
      INTO schema1_ct, schema2_ct;

      CONTINUE WHEN schema1_ct IS NULL; -- happens when both or none have values
      RETURN NEXT;
   END LOOP;
END
$func$;

Call:

SELECT * FROM public.compare_schemas_by_table_emptiness('foo', 'bar');

Counting can be expensive with big tables, so this function only counts when required.

Identifiers may or may not require double-quoting and have to be treated like user input to be safe. See:

SQL injection in Postgres functions vs prepared queries


column must appear in the GROUP BY clause or be used in an aggregate function I have a simple table with columns col1, col2, col3. All not nullable.

I want to delete all rows where the tuple (col1, col2) has several entries. Background: a unique constraint for (col1, col2) should be added.

drop table mytable;

create table mytable (
    col1 integer not null,
    col2 integer not null,
    col3 integer not null);

-- rows to delete
insert into mytable values (1, 1, 1);
insert into mytable values (1, 1, 2);

-- rows to keep
insert into mytable values (2, 2, 1);
insert into mytable values (2, 3, 2);

delete from mytable where
(col1, col2) in  (
    select col1, col2 from mytable
    group by (col1, col2) having  count(distinct col3) >1) ;

select * from mytable;

Above works on PostgreSQL 10 but fails on older versions.

Older versions tell me this error message:

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

How to get this working on PG 9.3?

You just need to remove the parentheses around the columns in group by (col1, col2). This works in version 9.4 and previous as well:

delete from mytable
where (col1, col2) in  (
    select col1, col2 from mytable
    group by col1, col2                   -- <-- changed
    having  count(distinct col3) >1) ;

The reason that it fails (I think) is that while (col1, col2) is equivalent to row(col1, col2), there was some inconsistency in how it was handled in the various clauses which was fixed in 9.5. In previous versions, you could use a more complex construction in WHERE: WHERE (SELECT (col1, col2)) IN .... So this should work in 9.3 as well:

delete from mytable
where (select (col1, col2)) in  (
    select (col1, col2) from mytable
    group by (col1, col2) having  count(distinct col3) >1) ;


Storing 5TB web server access log vs OLAP DB We have over 5TB compressed web server log in a HDFS and we often analyse using Hadoop.

It is painful to run map reduce on 5TB of data and most importantly, not many developer are familiar with it.

I am thinking if we should store the data in columnar database such as Greenplum or other MySQL column store which are aimed to store the analytical data in a efficient manner but able to support raipid query, which is quite important lately.

What are base you would recommend? Anything I should consider before the move? (I will do my own test anyway)

I recommend Vertica.

You can get the free community edition that allows up to 1TB of data. If you normalize your web logs when you load them in, chances are they'd compress down and may fit under 1TB, as Vertica has a pretty powerful data compression engine itself.

If not, I'd still recommend trying out the platform, but the license fee isn't the cheapest thing in the world.


MySQL (ndb) Cluster and BLOB Datatype access issue I'm currently investigating moving our INNODB database to an ndb cluster (vs buying bigger servers (again) and moving the master / slaves to these).

So far my testing on a small cluster has been fruitful, and suggests that we would benefit from this (especially in data resiliency / write access). However I have one small niggling concern. We have one large table (apx 30% of the whole database size - or 60GB apx at present) which is comprised entirely of BLOB fields (80+), and a single field at the start uniqRowID (INT AUTO INCREMENT) to identify the row. Few of these contain very much data (mainly some text/ numbers/ dates etc), many are actually empty (NULL). None of these are indexed. We add new rows constantly, and read the old rows frequently too. Updates are few and far between.

Is this likely to become an issue when running this in a cluster?

From my reading I understand that BLOB fields are held in separate tables/pages in the background, so need to be read separately to the main data. But can't see if this is like INNODB, where the first x data is stored in the main table, the rest held separately, or if the whole thing is separate.

I ran some simple tests, inserting records with similar data, (both disk based and normal), with various joins etc. which showed no differences. But I am concerned that my small test system simply isn't stressing it enough, and don't have the resources (or finances) to run a full size test yet.

Can anyone with some experience of this tell me if I am going the right way with this, or am I just walking into a nightmare?

Following further testing it seems my problem wont be performance, but simply creating the table, as that many BLOB fields exceeds the maximum row size, and so I can't even create the table!!

I got to about 47 BLOB fields before it gave up.


NULL safe NOT LIKE operation in mysql I have InnoDB table with two columns (1) account(int) (2) comment(varchar).

I have index on (account,comment)

Now I want to run query like

select * from table where account = 1111 AND comment NOT LIKE 'abcd%';

How can i make the NOT LIKE null safe?

I mean, include all records whose comment not starts with 'abcd', even if its null value.

mysql > create table nsnl(a varchar(20));
Query OK, 0 rows affected (0.00 sec)

mysql > insert into nsnl values (null), ('asdf');
Query OK, 2 rows affected (0.01 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql > select * from nsnl;
+------+
| a    |
+------+
| NULL |
| asdf |
+------+
2 rows in set (0.00 sec)

mysql > select * from nsnl where a not like 'as%';
Empty set (0.00 sec)

mysql > select * from nsnl where a not like 'as%' or a is null;
+------+
| a    |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

And that's how you can easily find that out yourself. Plus, when you do it yourself, you memorize it better. At least that works for me.


varchar storage and comparisons in SQL Server 2008 I have a large table with a structure similar to this:

Id       bigint primary key
Sequence int
Parent   int foreign key
Data1    varchar(20)
Data2    varchar(20)
Data3    varchar(20)
Data4    varchar(20)
Data5    varchar(20)

Data 1-5 are text fields (mostly numbers and dashes) and can be used to help prevent accidentally recording duplicates, but the data can actually duplicate occasionally. We have to let the user confirm that yes it's OK it's a duplicate.

This duplicate check can be time consuming with millions of rows. I've been tasked to ensure we can deal with tripling the amount of data we are keeping in this database. I've been told by previous developers that the current process will not work with that much data (there is of course a lot more going on with the data than I'm including here, and though the actual query for finding duplicates is pretty simple this part of the system is apparently sensitive to additional latency). I have not done experiments myself to prove this but I trust their judgement, and regardless I'd like to reduce the impact as much as possible. Unfortunately, I do not have a huge volume to test with until fairly late in the process. So while I have time I would like to try to be set up to test a few scenarios when I get a window of time on the test server.

All 5 fields must match to be counted as a duplicate. I'm skeptical that this could never change, but I'm being told that for the foreseeable future all 5 must match exactly. I'm thinking that by adding a indexed column that is a hash of the combined values, I will be able to find potential duplicates much faster. I would still have to compare the individual values to account for hash collisions though. Would I gain anything by combining the values themselves too? So a hash column, and a single column with all 5 values in a delimited string that I use to compare rather than compare each value separately?

It seems like I would only benefit if varchars are not stored with the rest of the row, which I always assumed they were. However that doesn't seem to be the case. I'm having a hard time finding specific information, but it seems that if my row is less than 8060 bytes, the full 20 characters will be allocated in the row. Only if the row is over 8060 will some varchar columns be chose to move into a separate page.

Any documentation that explains how small varchars are stored, and any suggestions regarding whether or not combining the columns into a single column will have a performance impact would be much appreciated.

EDIT:
This table will have millions of rows (say 10 million for an estimate). Each row will definitely have a maximum length of less than 8060 bytes, though I can't give you an exact number right now.

Add a persistent calculated field that contains a CHECKSUM on the 5 fields, and use that to perform the comparisons.

The CHECKSUM field will be unique for that specific combination of fields, and is stored as an INT that results in a much easier target for comparisons in a WHERE clause.

USE tempdb; /* create this in tempdb since it is just a demo */

CREATE TABLE dbo.t1
(
    Id       bigint constraint PK_t1 primary key clustered identity(1,1)
    , Sequence int
    , Parent   int not null constraint df_T1_Parent DEFAULT ((0))
    , Data1    varchar(20)
    , Data2    varchar(20)
    , Data3    varchar(20)
    , Data4    varchar(20)
    , Data5    varchar(20)
    , CK AS CHECKSUM(Data1, Data2, Data3, Data4, Data5) PERSISTED
);

GO

INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
VALUES (1,1,'test','test2','test3','test4','test5');

SELECT *
FROM dbo.t1;
GO

/* this row will NOT get inserted since it already exists in dbo.t1 */
INSERT INTO dbo.t1 (Sequence, Parent, Data1, Data2, Data3, Data4, Data5)
SELECT 2, 3, 'test', 'test2', 'test3', 'test4', 'test5'
WHERE Checksum('test','test2','test3','test4','test5') NOT IN (SELECT CK FROM t1);

/* still only shows the original row, since the checksum for the row already
exists in dbo.t1 */
SELECT *
FROM dbo.t1;

In order to support a large number of rows, you'd want to create an NON-UNIQUE index on the CK field.

By the way, you neglected to mention the number of rows you are expecting in this table; that information would be instrumental in making great recommendations.

In-row data is limited to a maximum of 8060 bytes, which is the size of a single page of data, less the required overhead for each page.  Any single row larger than that will result in some off-page storage of row data.  I'm certain other contributors to http://dba.stackexchange.com can give you a much more concise definition of the engine internals regarding storage of large rows.  How big is your largest row, presently?

If items in Data1, Data2, Data3... have the same values occurring in a different order, the checksum will be different, so you may want to take that into consideration.

Following a brief discussion with the fantastic Mark Storey-Smith on The Heap, I'd like to offer a similar, although potentially better choice for calculating a hash on the fields in question.  You could alternately use the HASHBYTES() function in the calculated column.  HASHBYTES() has some gotchas, such as the necessity to concatenate your fields together, including some type of delimiter between the field values, in order to pass HASHBYTES() a single value.  For more information about HASHBYTES(), Mark recommended this site.  Clearly, MSDN also has some great info at http://msdn.microsoft.com/en-us/library/ms174415.aspx


Remove duplicates from different columns I'm trying to remove duplicates from a set, but the duplicates are in different columns, so for example with this table:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
----------
1,  1, 'DEF', 'ABC'
----------
1,  1, 'GHJ', 'LKJ'
----------
1,  1, 'LKJ', 'GHJ'

What I need to end up with is:

ColA, ColB, ColC, ColD
----------
1,  1, 'ABC', 'DEF'
1,  1, 'GHJ', 'LKJ'

Hope that makes sense, does anyone have any ideas?

This is SQL-Server 2012.

This probably ain't going to be right answer, but this works for data you gave us.

;WITH TestData (ColA, ColB, ColC, ColD)
AS (
    SELECT 1, 1, 'ABC', 'DEF'
    UNION ALL
    SELECT 1, 1, 'DEF', 'ABC'
    UNION ALL
    SELECT 1, 1, 'GHJ', 'LKJ'
    UNION ALL
    SELECT 1, 1, 'LKJ', 'GHJ'
    UNION ALL
    SELECT 1, 1, 'ABC', 'HJK'
    UNION ALL
    SELECT 1, 1, 'HJK', 'ABC'
)
SELECT ColA, ColB, ColC, ColD
FROM TestData
EXCEPT
SELECT ColA, ColB, ColD, ColC
FROM TestData
WHERE ColC < ColD


Showing all values when at least match a given one Given three tables items, genres and itemGenres; how can I filter out the items that have at least the given value while also outputting the rest of its genres.

With this data

INSERT INTO items (title) VALUES ('Mad Max: Fury Road'), ('The Big Lebowski');
INSERT INTO genres (genre) VALUES ('action'), ('comedy'), ('post-apocalyptic');
INSERT INTO itemGenres (itemId, genreId) VALUES (1,1), (1,3), (2,2);

and this query

SELECT
  items.*,
  GROUP_CONCAT(genres.id) as genreIds,
  GROUP_CONCAT(genres.genre) as genres FROM items
LEFT JOIN itemGenres ON
  items.id = itemGenres.itemId
LEFT JOIN genres ON
  genres.id = itemGenres.genreId
WHERE
  genres.id IN (1)
GROUP BY
  items.id

I get the following result

| id |              title | genreIds | genres |
|----|--------------------|----------|--------|
|  1 | Mad Max: Fury Road |        1 | action |

How do I get it to be this?

| id |              title | genreIds |                   genres |
|----|--------------------|----------|------------------------- |
|  1 | Mad Max: Fury Road |      1,3 | action, post-apocalyptic |

The tables are created like this

CREATE TABLE `items` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(120) COLLATE utf8_unicode_ci NOT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `itemGenres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `itemId` int(1) unsigned DEFAULT NULL,
  `genreId` int(1) unsigned DEFAULT NULL,
  PRIMARY KEY (`id`)
);

CREATE TABLE `genres` (
  `id` int(1) unsigned NOT NULL AUTO_INCREMENT,
  `genre` varchar(120) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
);

You need 2 joins to itemGenres, separating the "check" logic (WHERE genres.id IN (1)) and the "gathering of all related genres":

SELECT
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres
FROM items
  LEFT JOIN itemGenres ON
    items.id = itemGenres.itemId
  LEFT JOIN genres ON
    genres.id = itemGenres.genreId
WHERE EXISTS
  ( SELECT *
    FROM itemGenres AS genre_check
    WHERE genre_check.itemId = items.id
      AND genre_check.genreId IN (1)
  )
GROUP BY
  items.id ;

The LEFT joins can be replaced with INNER joins, the results would be identical.

Actually, it could be done with a change of your query, moving the check from WHERE to HAVING but the condition has to be modified:

SELECT
  items.*,
  GROUP_CONCAT(genres.id ORDER BY genres.id)    AS genreIds,
  GROUP_CONCAT(genres.genre ORDER BY genres.id) AS genres
FROM items
  JOIN itemGenres ON
    items.id = itemGenres.itemId
  JOIN genres ON
    genres.id = itemGenres.genreId
GROUP BY
  items.id
HAVING
  COUNT(CASE WHEN itemGenres.genreId IN (1) THEN 1 END) > 0 ;

My personal preference would be the 1st solution but you can test in your database, which of the two is more efficient.


update an id to max+1 in current table? (MySQL) I've got a 'jobs' table where each row has a 'worker_id' that is either zero (job is not being worked on) or some unique ID that needs to be incremental.

Basically, when assigning this worker_id, I want to give it the first next available worker_id.

Here's what I'm trying:

UPDATE jobs SET worker_id=(SELECT MAX(worker_id)+1 FROM jobs) WHERE jobname='Abc'

The idea behind this is: if the current highest worker_id in my jobs table is 14, this query would set the worker_id for my job 'Abc' to 15.

But instead, this results in an error:

#1093 - You can't specify target table 'jobs' for update in FROM clause

What should I do?

FYI, the output of SHOW CREATE TABLE jobs is:

CREATE TABLE jobs (
  jobname varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  worker_id int(11) NOT NULL DEFAULT '0',
  email varchar(200) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL,
  customer_id int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

(I just removed the ` chars around table and column names for pasting as code here on StackExchange)

UPDATE jobs j1
INNER JOIN jobs j2 ON j1.worker_id <= j2.worker_id
LEFT OUTER JOIN jobs j3 ON j2.worker_id < j3.worker_id
SET j1.worker_id = j2.worker_id + 1
WHERE j1.worker_id = 3 AND j3.worker_id IS NULL;

j1 is the row you want to change, based on the condition in the WHERE clause.
j2 is the set of rows with a worker_id greater or equal to j1.
j3 is the set of rows with a greater worker_id than j2 (when j2 has the greatest worker_id in the table, there is no row for j3, so j3.* will be NULL).
j2 therefore has the greatest worker_id, so use its value + 1.

Demo:

mysql> create table jobs (id serial primary key, worker_id int);
mysql> insert into jobs (worker_id) values (1), (2), (3), (4), (5);

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         3 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 3 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         5 |
+----+-----------+

It works regardless of the value we want to change:

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 5 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         7 |
+----+-----------+

And it works even if we're changing the row that already has the highest value in the table:

mysql> update jobs j1 inner join jobs j2 on j1.worker_id <= j2.worker_id
 left outer join jobs j3 on j2.worker_id < j3.worker_id
 set j1.worker_id = j2.worker_id + 1
 where j1.worker_id = 7 and j3.worker_id is null;

mysql> select * from jobs;
+----+-----------+
| id | worker_id |
+----+-----------+
|  1 |         1 |
|  2 |         2 |
|  3 |         6 |
|  4 |         4 |
|  5 |         8 |
+----+-----------+


PostgreSQL: How to restrict resources per DB, Schema or User Is there a way to configure PostgreSQL server instance to limit resources, e.g. work_mem or max_parallel_workers, by databases or schema? (or even limit a specific user)

To my knowledge, parameters from postgresql.conf affect the entire postmaster instance.

Background of this question is multi-tenancy based on DB schema and resource isolation.

You can set many parameters on the database or user level with

ALTER DATABASE ... SET ...;
ALTER ROLE ... SET ...;

Connection limits can also be set on that level.

But a lot of things are global to the cluster (like shared_buffers), and a lot of resources (like CPU and I/O) cannot be limited at all.

I second the horse's comment to look at pg_cgroups, but I should mention that I am the author. That doesn't work on a database or user level though, but if you want to separate databases, it is always commendable to put them in different clusters anyway.


Monitor database activity I would like to see what queries are being sent to a DB and what is returned. Is this possible with MsSQL Server Management Studio or a third party application?
 Is there anything i can use that is free?

You can as a minimum execute sp_who2 that will show session information, but more advanced and detailed methods are available, plenty of scripts available, it depends what you are after specifically.

Great set of tools available at Brent Ozar: First Responder Kit

If you want a better tool to just look at  queries that are currently running, try sp_WhoIsActive, by Adam Machanic.


Insert triggers to eliminate sensitive information A vendor system (i.e. outside of my control) is inserting a row into a table into a database that I do control.  One column in this insert will have sensitive information that I do not want in my database.

If I use an INSTEAD OF INSERT trigger to blank out this column, will the sensitive information still end up in the transaction log?

Yes this will prevent the information showing up in the log. The transaction log only needs to record sufficient information to recover the row that was actually written to the data page. You can see this from using sys.fn_dblog as below.

CREATE TABLE dbo.T
  (
     X      int CONSTRAINT PK PRIMARY KEY,
     String varchar(50) NULL
  )

GO

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (1,
             'Private and Confidential')

/*Without INSTEAD OF trigger this phrase found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK'

GO

CREATE TRIGGER TR
ON dbo.T
INSTEAD OF INSERT
AS
  BEGIN
      SET NOCOUNT ON;

      INSERT INTO dbo.T
                  (X)
      SELECT X
      FROM   INSERTED
  END

go

CHECKPOINT;

INSERT INTO dbo.T
VALUES      (2,
             'Private and Confidential')

GO

/*With INSTEAD OF trigger this phrase no longer found in log record*/
SELECT CASE
         WHEN [Log Record] LIKE '%Private and Confidential%' THEN 1
         ELSE 0
       END,
       [RowLog Contents 0]
FROM   sys.fn_dblog(NULL, NULL)
WHERE  Operation = 'LOP_INSERT_ROWS'
       AND AllocUnitName = 'dbo.T.PK'

The construction of the INSERTED pseudo table takes place in tempdb so this information may appear in the data files there in passing however.


Pagination with PostgreSQL 9.3: counting number of pages I'm implementing pagination and sorting of rows in, let's say, products table, using multicolumn index on category, score and id.

-- index
create index category_score_id on products(category, score, id)
  where active = true;

-- selecting first page
select * from products where
  category = 1
  and active = true
order by score desc, id desc
limit 30;

-- selecting following pages
select * from products where
  category = 1
  and active = true
  and (score, id) < (893, 102458)  -- values from previous page
order by score desc, id desc
limit 30;

This works perfectly fine and as expected, using only index scan:

-- explain analyze of 2-nd query:
Limit  (cost=0.00..99.52 rows=30 width=2591) (actual time=0.090..0.179 rows=30 loops=1)
  ->  Index Scan Backward using category_score_id on products  (cost=0.00..76435.07 rows=23041 width=2591) (actual time=0.089..0.172 rows=30 loops=1)
        Index Cond: ((category = 1) AND (ROW(score, id) < ROW(893, 102458)))
Total runtime: 0.085 ms

What I am curious about is why the following query requires extra Bitmap Heap Scan:

select count(*) from products where
  category = 1
  and active = true;

-- explain analyze:
Aggregate  (cost=49987.80..49987.81 rows=1 width=0) (actual time=104.847..104.847 rows=1 loops=1)
  ->  Bitmap Heap Scan on products  (cost=538.85..49930.20 rows=23041 width=0) (actual time=8.919..98.952 rows=47937     loops=1)
        Recheck Cond: ((category = 1) AND active)
        Rows Removed by Index Recheck: 93006
        ->  Bitmap Index Scan on category_score_id  (cost=0.00..533.09 rows=23041 width=0) (actual time=7.181..7.181 rows=47937     loops=1)
              Index Cond: (category = 1)
Total runtime: 104.892 ms

Index definition
Your multicolumn index generally looks good. If all or most of your queries use order by score desc, id desc, it would be a bit more efficient to define it with matching sort order and a simpler condition:
CREATE INDEX prod_category_score_id ON products(category, score DESC, id DESC)
WHERE active;

WHERE active = TRUE is just a more complicated way of saying WHERE active.
While Index Scan Backward is practically as fast as a plain index scan, access is simpler with matching sort order in multicolumn index.

Why the extra Bitmap Heap Scan?
Due to the MVCC model of Postgres it needs to make sure that entries in the index are actually still alive in the table.
The planner method Index Scan works best for a small number of rows. Postgres takes each row pointer from the index and fetches the row from the heap (the table) sequentially, thereby also checking if the row is actually visible to the current transaction.
With a growing number of hits, this gets less effective. If multiple rows reside on the same data page, it's more efficient to collect tuples and visit the same page only once. That's what a Bitmap Index Scan does essentially (among other things).
When counting rows with count(*), Postgres wouldn't need data from the heap. But it still must verify that each tuple is visible and actually counts. The Wiki page about "Slow counting".
Postgres 9.2 introduced Index Only Scans. If the "visibility map" says all tuples of a page are visible to all current transactions, Postgres does not have to visit the heap to verify. That would be faster for a query such as yours, that only counts a small percentage of rows, most efficiently determined by using the index.
Obviously, you have a lot of concurrent write operations. Or your autovacuum settings are not aggressive enough. Else, you might see a faster Index Only Scan.
The Postgres Wiki about that.


How do I fix and prevent corrupted MySQL tables? We have developed software that uses MySQL. After some months of usage, some of the database tables had been corrupted and need to be repaired. Because of the corruption,we are not able to create a backup using Navicat, and we can not generate a dump too.

The log file has been indicating the following error for more than 2 days:

YYMMDD HH:MM:SS [ERROR] /usr/sbin/mysqld: Can't open file: 'ini.MYI' (errno:145)

What is the cause of this corruption? How can we fix them? How can we prevent the database tables from corruption in the future?

Have you tried

REPAIR TABLE;

or

 myisamchk --recover tbl_name

http://dev.mysql.com/doc/refman/5.1/en/repair-table.html

--EDIT--

As mentioned in the comments under your question, convert your tables to InnoDB tables which are transactional. This should help prevent corruptions in the future.


ALTER DATABASE... SET COMPATIBILITY_LEVEL -- will this impact log shipping? Can it be done live? SQL Server 2017 Standard Ed

I have a database that is at level 2008.

I want to bring it up to current.

This database log ships.

Is it safe to run:

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = <whatever>

1- Live , when the app is running (I know the app is compatible with the new level)

2- Will this break or mess with log shipping?

You can do it in off hours or when you have less activity on the server. Doing when the server is busy, will incur blocking.

Changing compatibility level does not impact logshipping, Mirroring or AlwaysON along with replication.  I have done it safely during less activity time.


Is the BACPAC from a TDE encrypted DB also encrypted? If you create a BACPAC of an encrypted database, will the data in the BACPAC be encrypted?
We're currently using on-prem SQL Server and use TDE to encrypt our customer databases.  We also have a requirement to maintain customer directed backups for up to 10 years.  A backup of an encrypted DB and retention is not an issue.  We're looking to migrate our platform to Azure SQL DB and thus will lose the ability to do customer directed backups.  I'm exploring the use of BACPAC to meet our needs.  If you create a BACPAC of an encrypted database, will the data in the BACPAC be encrypted?

No, the BACPAC is not encrypted. BACPAC is an export of schema and data.
TDE means that decryption is done when SQL Server reads the data pages from disk. I.e., the client tool that generates the BACPAC (sqlpackage.exe, for example) asks SQL Server to read the data, and that tool will never see the encrypted data.
So you'd have to post-encrypt the BACPAC file by some tool, if you have such a requirement.


Want to create partitions of a MySQL table and change the charset of new partitions I have a database table which contains large amount of data. I have written a script to create new partitions every week and drop old partitions (which are of no use). Currently, I have a column in that table which has latin1 character encoding. I want to change it to utf8. Since I have a very large amount of data, I was thinking if there is some way I can create new partition with that column encoded in utf8 and leave the old partitions as they are. They will get deleted at some point of time in future and I will have whole data encoded properly.
Is there any way of doing this?

Partitions have in their syntax the option to have different engines for every partition, but this is not (yet?) supported.

Having different character sets for different partitions, however, may not make sense as all of them should have a common data structure. It is also not supported. Please understand that the charset is a property of a field, not of a table, and that the only reason you can configure it for a table is to set a "default".

If you want to create partitions for archiving, that is a good call, as it makes easier to delete old ones. Be aware of MySQL partitioning restrictions.

If you want to change the charset of a field in an online fashion, I recommend you to use any online alter table tool (I think that MySQL 5.6 does not allow for online charset alters, but I may be wrong) like Facebook's, Percona's or Shlomi's.

As an addendum, if at some point you need to mix different charsets on a field (I have needed it in the past as an intermediate step for having invalid charsets), you could set the type of the string to binary type instead of text (BLOB instead of TEXT, VARBINARY instead of VARCHARetc.). Binary strings do not have encoding information.


How to create a view with added rows for missing dates? I'm not sure how to accurately phrase this question.

Essentially, say I have a bunch of salespeople on different sales teams. Like, the Blue Region, the Orange Region, the Red Region, whatever.

Now, these sales people are each connected to one sales region, but may, on occasion, change sales regions over time.

Say Bob Jones was in the Blue Region for January 2015, February 2015, March 2015, but on April 2015, he went to the Green Region.

So I'm trying to create a report that would aggregate sales revenue based on Region (even though at the technical level, its logged to sales person).

So we would have transaction (table: employee_sales) data as follows:

name           date                 sales
Bob Jones      February 18th, 2015   $50,000

etc. etc.

So in order to aggregate by region, I would need a helper dimension table as follows (Remember, region can change over time) (table: employee_region)

name         region      month      year
Bob Jones    Blue        January    2015
Bob Jones    Blue        February   2015
Bob Jones    Blue        March      2015
Bob Jones    Green       April      2015

Then I can simply do a query:

select er.region, er.month, er.year, sum(es.sales)
from employee_sales es
inner join employee_region er
on es.name = er.name and month(es.date) = er.month and year(es.date) = er.year
group by er.region, er.month, er.year

So this would give me the data I need.

HOWEVER, now I have a problem --- say I have 500 employees and the REGION data is updated a month in arrears. So right now, February 2016, we only have region data from January 2016.

Would I would LIKE to do, is ... for the current month (February 2016) ... if the data in the "helper table" for region is missing for the month, take the last month found (which may be January, but sometimes even December or November potentially).

I'm not sure what to do here. Create some kind of view?

Do I restructure the 'helper table' so it's more of an inequality statement?

EDIT: I think this is a slowly changing dimensions problem. Hmm I probably have to reorganize the dimension table.

I wouldn't organize your "helper table" that way.  I would keep only {Region, Salesman, StartDate}.  To create a view of intervals, join that table to itself to create data ranges where a.StartDate < b.StartDate.  Use an outer join, so that NULL represents an EndDate of "now", which you can coalesce with the current date or other, as appropriate.  To represent departures (not reassignments) use an artificial region named CiaoBaby or some such.

Now your maintenance chore is much easier; you need only capture staffing changes.  The table is oodles smaller (over time, 100 or 1000 oodles).  And you automatically have the built-in assumption that, absent contrary information, a salesman remains attached today to the same one as last month.


Fail to connect from MySQL Workbench to remote server After trying many different suggestions as found on the net, I'm still unable to establish a connection from the Workbench application to the server. Here is a brief list of things I tried:

1) Granting all privileges on . to 'root'@'%' ; (+flush privileges),

2) Creating a new user 'test'@'%' and granting all privileges (yes, it is the same as above, but had to try with someone different from root),

3) Create a user as 'test2'@'' with all privileges,

4) Turning off firewall in both server and my computer,

5) Updated the file /etc/my.cfg my adding a bind-address = 0.0.0.0 line (there wasn't any such line, to begin with; note the location of this file which differs from what is presented in many posts; in my installation, there is no such a folder /etc/my.cfg/...),

...

The error I get is always that cannot establish a connection (with the 4 steps to check; obviously, I checked them all and it should work).

I have Workbench installed on Windows 10 and I'm using the standard MySQL included in the Oracle Linux 7 installation ISO.

Could anyone suggest additional checks to be made and to find what the issue is?

The issue had nothing to do with MySQL parts. It turned out that Linux's firewall and Selinux were blocking access from outside the server. Once turned off, the connection worked perfectly.

Two things I learned from this:

1) Check better the ENVIRONMENT before blaming the product,

2) MySQL could improve the clarity of the messages and include environment elements that could be the source of the issue (I wasted 1/2 day chasing this stupid issue).


The connection is broken and recovery is not possible - sql server error I am having the following sql server error, while creating tempDB files.
Any ideas?

  The connection is broken and recovery is not possible.  The client
  driver attempted to recover the connection one or more times and all
  attempts failed.  Increase the value of ConnectRetryCount to increase
  the number of recovery attempts. (Microsoft SQL Server, Error: 0)

I had this issue when I was trying to Deploying a Database to Azure SQL Server. I tried a few times and I got the same error. I double-check the Azure permissions, Firewall, user and pwd.
What I did to fix it was run SQL Management Studio as Administrator. Simple as it.
I believe the problem was some permission to the temporary file to "C:\Users\xxx\AppData\Local\Temp" to create bacpac file.


Reading CSV files located on LINUX server and updating the tables in SQL Server Database I was wondering how do we ingest CSV files located on a Red Hat LINUX server into SQL Server Database Table.

I know we can write a Stored Procedure/Bulk Insert to read the files that are located on the same Windows Server as SQL Server and update the database but not sure how to do it when the files are present in LINUX.

Any help would be greatly appreciated

I think there are at least two generic options for this kind of thing:

have a network share that the SQL Server host can connect to on the Linux server.
sync the file from Linux server to the DB host (by e.g. using a network share on the DB host, or any other way of copying files over network).

Remark: The way Windows handles mapped network drives, it might be that the login session running the DB processes would not see the drive if it was mapped by some other user account. Thus the first option is preferred (maybe also from the pov of dbas?).


Checking when a row was inserted into a table on SQL Server? Is it possible to check when a specific row was inserted into a specific table in SQL Server ?
The table has an index if this any help.

And I need to do this for an existing table where the row has already being inserted.

Thanks.

To achieve this retrospectively you could do so by inspecting the contents of the transaction log to identify when the insert occurred, of course you need to have access to the relevant transaction log that contains the details.

Here is a walkthrough of how you would inspect the transaction log to locate a table drop event but the process you would follow is mostly the same.

If auditing of this level is actually something that's required then your best bet is to build it into your overall solution, whether through the use of DML Triggers, CDC or Change Tracking or a customised solution.