How to check if a postgresql backup is finished or not? I started my backup yesterday, but now I don't know if it is finished or not.

Can you please help me?

You can use the function pg_is_in_backup() in PostgreSQL >= 9.3, see Backup Control Functions

For example:

SELECT pg_is_in_backup();

If the result is false, then the computer is not currently backing up. But this depends on the kind of backup you're doing (i.e. via PgAdminIII, SQL command, or pgdump).


Mysql - Grouping and getting the Max(col1) row variables I have this table:

CREATE TABLE `table1` (
    `id` INT(11) NOT NULL AUTO_INCREMENT,
    `col1` VARCHAR(30) NULL DEFAULT NULL,
    `col2` VARCHAR(30) NULL DEFAULT NULL,
    `col3` VARCHAR(30) NULL DEFAULT NULL,
    `riskLevel` VARCHAR(30) NULL DEFAULT NULL
    PRIMARY KEY (`id`)
)COLLATE='utf8_general_ci' ENGINE=InnoDB;

What i want to do is group this table by col1, and get the row with the highest riskLevel for each col1 group values.

When using this query:

SELECT id,col1,col2,col3, MAX(riskLevel)
FROM table1
GROUP BY col1

You get the max riskLevel, but the other values are random, not fittings to the correct row.

I know one solution:

SELECT id,col1,col2,col3, riskLvel
FROM table1 JOIN
(SELECT col1, MAX(riskLevel) as riskLevel
FROM table1
GROUP BY col1) x ON x.col1 = table1.col1 AND x.riskLevel = table1.riskLevel

This solution doesn't seem optimal to me.

Is there another way of getting the described behavior?

SELECT id,col1,col2,col3, riskLvel
FROM table1
where risklevel IN ( SELECT MAX(riskLevel)
                     FROM table1
                     GROUP BY col1 )

IF This query is not correct then you can try this query

SELECT id,col1,col2,col3, riskLvel
    FROM table1
    where col1 IN ( SELECT MAX(col1)
                         FROM table1
                         GROUP BY col1 )


Last character in column shift to next column I have a unique situation I have to fix. Our vendor gave us a data map and things are not mapping out as they said it should. My situation is, starting with a particular column, I need to drop the last character from the column and append it to the next column over. I am at a loss for how to accomplish this.

I would adjust my ssis project and reload the data, however some of the data files were deleted and the infrastructure guys were not snapshotting that folder so I would be missing approx 10 days work of data in the warehouse.

edit

    create table dda
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO dda (comp_acct_num,internet_code,type_code,cr_rate_code)
VALUES ('00000000000000000','111','7',''),('00000365874001000','111','7',''),
       ('00000000000001000','211','7',''),('00003101118001000','211','7','')

The values inserted are the values as they exist now. They need to read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
00000000000000000|  111        |    7    |            |
00000365874001000|  111        |    7    |            |
00000000000001000|  211        |    7    |            |
00003101118001000|  211        |    7    |            |

It should read like this:

comp_acct_num    |internet_code|type_code|cr_rate_code|
-------------------------------------------------------------
0000000000000000 |  011        |    1    |  7         |
0000036587400100 |  011        |    1    |  7         |
0000000000000100 |  021        |    1    |  7         |
0000310111800100 |  021        |    1    |  7         |

Here is a trivial way using left, right, and stuff.

declare @table table (column1 varchar(16), column2 varchar(16))

insert into @table
values
('abcdef','ghijkl')

update @table
set
    column1 = left(column1,len(column1) - 1)
    ,column2 = stuff(column2,1,0,right(column1,1))

select * from @table

If you have trailing spaces, you'll need to use RTRIM accordingly.

column2 = stuff(column2,1,0,right(rtrim(column1),1))

After fixing your sample data to add commas in your DDL, and removing the extra column from the insert, you can still use the method above with a small tweak:

declare @dda table
(
    comp_acct_num varchar(17)
    ,internet_code char(3)
    ,type_code char(1)
    ,cr_rate_code char(1)
)

INSERT INTO @dda (  comp_acct_num,
                    internet_code,
                    type_code,
                    cr_rate_code)
VALUES
 ('00000000000000000','111','7','')
,('00000365874001000','111','7','')
,('00000000000001000','211','7','')
,('00003101118001000','211','7','')

update @dda
set
    comp_acct_num = left(comp_acct_num,len(comp_acct_num) - 1)
    ,internet_code = left(stuff(internet_code,1,0,right(comp_acct_num,1)),3)

select * from @dda


SQL Server Chain Permission We have the 2 databases.

Reporting
HR

There are some users who want to extract data from HR database.  But we don't want to give direct access to that HR database.

So, we created the special views in Reporting database and the users have dbowner right for that Reporting database.

But, when they try to run the query, the error message is showing...

The server principal "test" is not able to access the database "HR" under the current security context.

When I grant them as the db_datareader reader for HR database, it's all fine and they can run the query.  But it breaks the security and we don't want them to get access to the HR database directly.  That's why we made the special views in Reporting database.

How can I enable those users to run the view/query without giving direct access to the HR database?

Unless you have cross-database ownership chaining enabled (off by default) and have the same User in both databases, then giving access to an object in one database does not imply anything permission-wise to other databases. Of course, I am not recommending that cross-database ownership chaining be enabled, and neither is Microsoft.

Instead, you should create a certificate that can be used to sign modules and act as a proxy for permissions between the databases. The catch is that they work on stored procedures, function (non-Inline TVFs), assemblies, and triggers. But this method allows for those Users that should not have any access to the [HR] database to actually not have any access themselves: it is the code (i.e. the module) that has the access.

The basic concept:

Create certificate in [Reporting]
Create stored procedure in [Reporting] (that selects directly from tables in [HR])
Use ADD SIGNATURE to sign the new stored procedure using the certificate
Backup the certificate to a file
Create certificate in [HR] FROM the backup file you just created (the certificate needs to be identical in both places!)
Create user in [HR] FROM the certificate
GRANT SELECT permission on appropriates tables in [HR] (i.e. the ones references in the new stored procedures in [Reporting]) to the new certificate-based user
Go home and enjoy the rest of the day :)

Some resources:

Module Signing Info
Ownership Chains
ADD SIGNATURE
Signing Stored Procedures in SQL Server
Tutorial: Signing Stored Procedures with a Certificate
Tutorial: Ownership Chains and Context Switching

OR, if you prefer to stick with the simplicity of using views, then your views are in the wrong database. You need to instead:

Add the appropriate User(s) to the [HR] database so that there is basic access, but do not grant any permissions to any tables (and hopefully no group or role membership will imply such permissions)
Create the desired Views in the [HR] database
GRANT SELECT only to these Views, and not to anything else, to these Users


How to find the most common strings between rows In SQL 2017, I have a 'food' table as follows:
ID    item    Ingredients
----  -----   -----------
1     item1   flour,sugar,cocoa,butter
2     item2   flour,sugar,butter,water
3     item3   sugar,cocoa,water
4     item4   sugar,cocoa,butter
5     item5   flour,water

Although the "Ingredients" are a CSV in a single field, I have no problem moving them to a related table:
ID    Ingredient
---   ----------
 1    flour
 1    sugar
 1    cocoa
 1    butter
 2    flour

etc
or just select them with a CROSS APPLY string_split
select ID, item, ingredient
from food
CROSS APPLY string_split(food.ingredients,',') as ing

Now here is what I am trying to attempt:
For any given row ex:(where id = 1) I want to return the top n rows (other than id = 1) that have the most common ingredients with id = 1.
So the top 2 with the most common ingredients with id = 1 should be:
ID    item    Ingredients
----  -----   -----------
2     item2   flour,sugar,butter,water    ( 3 in common)
3     item3   sugar,cocoa,water           ( 2 in common)
4     item4   sugar,cocoa,butter          ( 3 in common)
5     item5   flour,water                 ( 1 in common)

The result should return rows with id = 2 and id = 4 as both have 3 ingredients in common with row id = 1
This is my first question on StackExchange, and I hope it is clear.
I can write this as a C# or Python batch program to create a table of 'closest ingredients', but I'm hoping I can do this in SQL.
Thanks !

Please store the ingredients in a table not a list.  So let's call that ItemIngredient with two columns ItemId and Ingredient and leave it at that.  The primary key will be (ItemId, Ingredient).  Because searches by Ingredient will probably happen a lot, let's put a non-unique index on Ingredient (which is necessary for below to work the most efficiently).
SELECT
  ItemId
 ,MatchItemId
 ,IngredientMatchCount
 ,MatchRank
FROM
  (
    SELECT
      Base.ItemId
     ,Match.ItemId AS MatchItemId
     ,COUNT(*) AS IngredientMatchCount
     ,RANK() OVER (PARTITION BY Base.ItemId ORDER BY COUNT(*) DESC, Match.ItemId) AS MatchRank
    FROM
      ItemIngredient Base
    INNER JOIN
      ItemIngredient Match
        ON Match.Ingredient = Base.Ingredient
             AND Match.ItemId <> Base.ItemId
    -- Put your WHERE clause here if you want to only fine matches for certain Ids
    GROUP BY
      Base.ItemId
     ,Match.ItemId
  ) matched
WHERE
  MatchRank <= 1 --Or whatever
ORDER BY
  ItemId

You can experiment with RANK vs DENSE_RANK at some point to see what provides the best results for you.  If you need details about the Item you can join back in the outer query.


Queueing MySQL record inserts to avoid over-subscription of a related resource ... table locking? Given a simplified hypothetical of seats in a lifeboat, if I have the following setup with a lifeboats table and a seats table where each record is one occupied seat in the given lifeboat:
CREATE TABLE lifeboats (
  id INT UNSIGNED NOT NULL,
  total_seats TINYINT UNSIGNED NOT NULL,
  PRIMARY KEY (id));

INSERT INTO lifeboats (id, total_seats) VALUES (1, 3);
INSERT INTO lifeboats (id, total_seats) VALUES (2, 5);

CREATE TABLE seats (
  lifeboat_id INT UNSIGNED NOT NULL);

INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (1);
INSERT INTO seats (lifeboat_id) VALUES (2);

I can find lifeboats with available seats by querying:
SELECT
    l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken
FROM
    lifeboats AS l
        LEFT JOIN
    seats AS s ON s.lifeboat_id = l.id
GROUP BY l.id
HAVING COUNT(s.lifeboat_id) < l.total_seats

What is the best way to ensure 2 clients do not grab the last seat in a lifeboat without implementing some coordinating process queue?
My only idea (assuming I'm trying to grab seat in lifeboat 2) is going LOCK TABLE rambo like:
LOCK TABLE seats WRITE, lifeboats AS l READ, seats AS s READ;

INSERT INTO seats (lifeboat_id)
SELECT
    id
FROM
    (SELECT
        l.id, l.total_seats, COUNT(s.lifeboat_id) AS seats_taken
    FROM
        lifeboats AS l
    LEFT JOIN seats AS s ON s.lifeboat_id = l.id
    WHERE l.id = 2
    GROUP BY l.id
    HAVING COUNT(s.lifeboat_id) < l.total_seats) AS still_available;

UNLOCK TABLES;

but this is not very elegant, needless to say.
(My environment is MySQL8/InnoDB)
UPDATE ... Another go:
I've been called out for giving a bad example. The question is really just:
For a given table, how would you best limit (to X) the number of records inserted with a given value Y?
The process receives the limit X & value Y , you query the existing records where value = Y to see if you are under the limit X or not, and if so you insert the record.
But obviously you risk 2 people grabbing the "last" record unless you ... do something .... but what? (I thought the lifeboat analogy was actually a good one!)
Idea one: Write lock the table before beginning the process. Other processes forced to wait. But this stops everybody ... including others with a different value Y.
Idea/Question 2: If I have a 2nd table t2 with unique set of all the Y values and my select "count of Y" query includes t2 reference + a "FOR UPDATE OF t2", will the write lock placed on the Y row in t2 effectively force processes with value=Y to wait until others have completed the process?

SELECT FOR UPDATE can't lock rows that don't exist, therefore if it is used on table seats, it cannot prevent the insertion of new rows. So if you use that, it has to be done on table lifeboats.
Since customers will be in a hurry to find a lifeboat with available seats, it would be better to let go of the slow count() and add a column occupied_seats in table lifeboats.
This should be updated via a before insert or delete trigger on seats, that does an update on the corresponding row from lifeboat. This update will automatically lock the row, which handles your concurrency issues.
This trigger should raise an error (use SIGNAL sql keyword) if the insert would exceed available seats.
This can be done in a semi lock-free manner by doing
UPDATE lifeboats SET occupied_seats = occupied_seats+1
WHERE id=... AND occupied_seats < available_seats;

then testing if that did update one row. That will save one SELECT FOR UPDATE. If it did not update the row, the lifeboat is full, throw an error. If it did, this also locks the row, so COMMIT quickly.


Show all rows even when count is null I want to see all the rows from one table and then do a count on the second table to see how many match, but want to see it all even if there are 0 matches in the second table.

My query:

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
WHERE YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

Example desired output:

area 1 | 1
area 2 | 0
area 3 | 5

What I currently get is:

area 1 | 1
area 3 | 5

I tried CROSS and RIGHT join as well, just to make sure, but I don't get the desired effect.

What am I missing or doing wrong?

You can do this first: REMOVE THE WHERE CLAUSE

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON a.area_id = b.i_area
GROUP BY a.area_name

This will show area 2

If you really want the WHERE clause, move it into the ON Clause

SELECT
 a.area_name,
 COUNT(b.id) AS cnt
FROM areas a
LEFT JOIN events b ON
    a.area_id = b.i_area AND
    YEAR(b.i_date) = YEAR(CURRENT_DATE()) AND
    MONTH(b.i_date) = MONTH(CURRENT_DATE())
GROUP BY a.area_name

GIVE IT A TRY !!!


online Oracle database migration to different Oracle database release We currently have the following environment

Source data center:
1) OS: Oracle Enterprise Linux 5.7
2) DB: Oracle Database Enterprise Edition 10.2.0.1.0
3) Server Type: Physical Server
4) Data Size: 450 GB(used)/ 2 TB (allocated)

Target data center:
1) OS: RHEL 6.8
2) DB: Oracle Database Enterprise Edition 10.2.0.4.0
3) Server Type: Virtual on top of VMWare

WAN connection between Source and Target Data Center is 10 Gbps

At the moment there are no Tape library on Target Data Center

We want to copy the database to Target Data Center without stopping production application on Source Data Center (online copy).

Once database has been copied to target Data Center, we will do functional testing on target Data Center.

After the result from functional testing is confirmed okay then we will do cut over to Target Data Center with maximum 4 hours downtime allowed.

We have tried the following methods:
1) impdp - network link: failed, since the production need to be stopped
2) RMAN duplicate database: failed, since source db and target db is in different release

Tried to install same Oracle release on target Data Center but the prerequisites from oracle said that we should downgrade the OS first to RHEL 5.7 (latest supported OS to run Oracle 11.2.0.1) and then upgrade to RHEL 6.8 after data migration.

After checking with Red Hat, it is also not possible to downgrade to RHEL 5.7 and upgrade later to RHEL 6.8 as Red Hat said that it is major upgrade and should be performed with fresh install then migrate the OS settings.

Need advise on what is the best possible solution for this situation? FYI, Oracle Golden Gate solutions is out of question since we currently do not have allocated budget to purchase additional tools.

Thank you and best regards,

Amir

1) impdp with network link doest not need the production database to be stopped. This is the slowest method, as this needs to build everything from scratch, I am not sure this would finish in 4 hours.

2) RMAN duplicate fails between different versions because it tries to automatically open the database, and that will fail. But you can manually restore the database on the new host from the RMAN backups, then open it with alter database open resetlogs upgrade; and run the upgrade scripts immediately. With this method, you need downtime only to upgrade the database on the new host, should not take more than 30-60 minutes.

3) Streams does not require any license. You can just simply replicate the database, and start using the new database whenever you want to. This requires the least downtime, but the configuration and administration is cumbersome compared to other methods.


Is it possible to determine what part of a stored procedure is currently executing? Say I have a stored procedure like this (assume that the table schemas are fine):

CREATE PROCEDURE p_MyProc
AS

INSERT MyTable SELECT Col1 FROM Table1

INSERT MyTable SELECT Col2 FROM Table2

INSERT MyTable SELECT Col3 FROM Table3

Assuming that these end up being big inserts that can take several minutes each, is it possible to determine which one is currently running from within the context of a stored procedure?

I know how to find what stored procedure is running, I'm just looking to see if there's a way to get more granular without having to add PRINT or other tracing statements in there.  Is there maybe a way to make the statements show up in a trace?

You can follow along with either a SQL Server Trace or Extended Events.

You can watch completed statements and/or batches, and that should give you the granularity you are looking for.

SP:StmtCompleted Event Class


I do not see SQL Server instance name under roles section of Fail over cluster manager screen! How to setup? I have created 2 Virtual machines in azure and configured Windows Cluster using fail over feature. I have enabled the "AlwaysOn" feature in SQL Server Configuration Manager. But I do not anything under Roles section of Failover cluster manager window as shown below. In many tutorials I see SQL Server instance names under roles. How it comes? do we really need them?

You have to actually create the Availability Groups first, then it will show up in FCM. You have basically finished the prerequisite steps to allow an Availability Group to be created...so let's go create one!

Sounds like you have an instance on each VM, create or put a database on one of the instances
Open SSMS and connect to the instance with the database
Expand AlwaysOn High Availability and right click Availability Groups
Start the New Availability Group Wizard
Give your new AG a name
Pick a Database to join to the AG...it needs to be in full recovery mode, and need to have had an initial backup (the wizard will identify these for you under status)
Pick how you want the other node to be joined to the AG (the wizard will backup, restore in norecovery mode, and then join on the other node or you can do it manually, or do it later)
Let the wizard create the AG

This is the very general outline, go to Brent Ozar's site and download the AlwaysOn Availability Groups Setup Checklist to really do things right!


innodb_flush_log_at_trx_commit=1 and flush-to-disk reality I use the default settings of MySQL 5.5 on various servers and I have the following table:

create table test._perf (
    name varchar(255) not null
) engine=InnoDB;

I have an .sql script which contains 1000 lines of:

insert into test._perf values('abc');

and I run it like that:

time mysql -u root < 1000-inserts.sql

Since it runs on auto commit, there are 1000 transactions happening, one after the other.

The default setting of innodb_flush_log_at_trx_commit is 1 which does full ACID. (documentation: https://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit)

The documentation also states that:

  Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1...

All of the servers I'm testing the 1000-inserts.sql script are modern ("fast") but very different between them (metal vs vm, windows vs linux, ext4 vs other filesystems, busy vs idle), but the results are always consistent and fall in one of the following two categories:

1000 inserts finish in 0.2-1 seconds (fast)
1000 inserts finish in 20-55 seconds (slow)

Setting innodb_flush_log_at_trx_commit to 2 in the "slow" servers makes them "fast".

I'd like to know the answer to the following questions:

can I assume that the servers with the "slow" behavior are the right and correct ones that really implement innodb_flush_log_at_trx_commit=1?
can I assume that the servers with the "fast" behavior are wrong and it is the behavior explained in the documentation? (Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not.)

It is obvious to have better performance for the value of innodb_flush_log_at_trx_commit as 2 than 1.

As commit operations are fairly dependent on underlying system even fooling-by-OS is an extra step one takes for every commit.

For value of 1 flushing to disk operations are bound to happen every transaction while for 2 it happens once a second (depending on OS scheduling)

Oprn. is "slow" because  you do ensure the data durability & consistency.
Oprn. is "Fast" because you did less work (disk operations). Even for the "lies" mysqld goes an extra step.

For innodb_flush_log_at_trx_commit=1:

for every transaction {
do-log-write
do-disk-commit (actual / fake )
}

For innodb_flush_log_at_trx_commit=2:

for every transaction {
do-log-write
}
for every second {
do-disk-commit (actual / fake )
}

Also your tests you performed contains multiple parameters for comparisons. You might also want to test with sync_binlog and flush_method changes. Mainly consider variable optimizations for better performance.


Full recovery model and tablock inserts logging When testing some queries, I set the recovery model to full on a database and ran two identical 1M row inserts, with and without TABLOCK.

On the one with TABLOCK, I got around 9295 log records on a sql server 2008 instance, and around 8714 log records on a SQL Server 2017 instance.

When running the insert without tablock, I get around 1035659 records for the 2008 instance and around 1068599 records for the 2017 instance.

The reason for testing on sql server 2008 is to match the data loading performance guide's statement on the recovery models for ML operations :

  Minimally logged operations are available only if your database is in
  bulk-logged or simple recovery mode.

So, what am Is seeing here if it is not minimal logging?

Use DatabaseName

ALTER DATABASE DatabaseName SET RECOVERY FULL;
GO
BACKUP DATABASE DatabaseName TO DISK = '\\location\DatabaseName.bak';

BACKUP LOG DatabaseName TO DISK = '\\location\DatabaseName_log.trn';
GO

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO

CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account without TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts  (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);
--1035659 rows
GO

-- clear the log
BACKUP LOG DatabaseName to disk = '\\location\DatabaseName_log2.trn';

GO

--drop the table

IF OBJECT_ID(N'dbo.Accounts', N'U') IS NOT NULL
BEGIN
    DROP TABLE dbo.Accounts;
END;
GO
-- create the table
CREATE TABLE dbo.Accounts( AccountID INT PRIMARY KEY NOT NULL,
                            AccountName varchar(255),
                            DateCreated DATETIME2);

-- Insert 1M Rows into dbo.Account WITH TABLOCK
GO
SET STATISTICS IO, TIME ON;
INSERT INTO dbo.Accounts WITH(TABLOCK) (AccountID,AccountName,DateCreated)
SELECT TOP(1000000)
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),
        'Name N ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS varchar(255)),
        DATEADD(MINUTE,-ROW_NUMBER() OVER (ORDER BY (SELECT NULL)),GETDATE())
FROM MASTER..SPT_VALUES SPT1
CROSS APPLY MASTER..SPT_VALUES SPT2;
GO
-- check the amount of records in the log file
SELECT count(*)
FROM
fn_dbLog(NULL,NULL);

--9295 rows

When using TABLOCK your logging even in full model is called "efficient logging", it's when instead of logging every insert row-by-row the whole pages are logged.

It would be minimally logged in simple and bulk logged modes with only page allocations in the log, but in full recovery model you have fully formatted pages of data.

So the number of records that go to the log in simple and full models when inserting with tablock will be nearly the same but the content is different: in simple/bulk logged there will be only page numbers, in full there will be complete pages.

And it is really full logging since the insert operation can be fully reconstituted using these log records while in simple/bulk logged you have only information sufficient to rollback the insert.


Connect to sql plus from command line using connection string Let's say I have a Oracle database. I have a username = x, password = y, database = z. Also I know the port = a, SID = b, Hostname = c.

So how do I need to connect correctly? I used many options like:

sqlplus x/y@'(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=z)(SERVER=DEDICATED)))'

sqlplus (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=c)(PORT=a))(CONNECT_DATA=(SID=b)))

Submitting these command lines usually gives me back error messages like:

  ORA-12560: TNS:protocol adapter error
  ORA-12514: TNS:listener does not currently know of service

Many thanks in advance!!

did you try

sqlplus username/password@host:port/service
sqlplus x/y@c:a/b

modern versions of sqlplus (version 11 or newer) understand this syntax and you don't need a tnsnames.ora file.


MongoDB: enable sharding automatically If we add any new data/collection to the existing(running) shard cluster. Is there any way that the data/collection gets sharded automatically.
If yes, How to achieve that?.

No it is not possible to automatically shard the collection. This is what you have to do per collection.

Ref:

https://docs.mongodb.com/manual/reference/command/shardCollection/
https://docs.mongodb.com/manual/sharding/
https://docs.mongodb.com/manual/reference/command/shardCollection/#dbcmd.shardCollection

You will need to run shardCollection command in order to shard a new collection.  The collection must have an index whose prefix is the shard key.
The following operation enables sharding for the people collection in the records database and uses the zipcode field as the shard key:

use admin
db.runCommand( { shardCollection: "records.people", key: { zipcode: 1 } } )

  Shards a collection to distribute its documents across shards. You
  must run enableSharding on a database before running the
  shardCollection command. The shardCollection command must be run
  against the admin database.

A database can have a mixture of sharded and unsharded collections. Sharded collections are partitioned and distributed across the shards in the cluster. Unsharded collections are stored on a primary shard. Each database has its own primary shard.


ODBC Connection Lost... Close Database I have a Access 2013 frontend linked to a 2014 SQL server. When users leave their workstation with their copy of the frontend open their computer will go often into sleep mode and lose their ODBC connection to the SQL server. When they return their session on the database comes to a violent end causing them to open the task manager and close the application down. Not a pleasant user experience.
How can this situation be trapped? Does something exist that upon return with an ODBC call failure a message box can appear with the button to close file? Ideas?

I think the problem you're trying to solve has been solved for Access 2016, but nothing for Access 2013 (AFAIK). Check What's new in Access 2016 - July 2016:

July 2016
ODBC connection retry logic
When Access loses the ODBC connection to an external data source, users have to close and re-open their Access databases in order to continue working with objects that use data from those external data sources. Access doesn't attempt to re-establish the dropped connection even if the connection is valid again and there is nothing that users can do to make Access try and reconnect to the external data source.
We've improved this experience so that now if Access loses the ODBC connection to an external data source, Access tries to reconnect to the data source automatically. In this scenario, you'll now see the following experience:
If Access loses the ODBC connection to an external data source you're trying to use, you'll see an error message describing the failure.
Access now attempts to reconnect to the data source automatically when performing operations that require a connection to an external data source. If the retry is successful, you can continue working with the data and database objects without having to close and re-open Access.
If the retry fails, you won't be able to work with objects that use the dropped external data sources but you can continue working with other areas of your database that aren't dependent upon the unreachable data sources.
If you attempt to use data or other objects again that rely on the external data connections, Access tries again to reconnect to the external data source.


What does SPJ stand for in MySQL parlance? MySQL has a lot of references to SPJ in MySQL including in official Oracle slides but I can't find what it actually stands for. I see it show up in a few blogs too.

I looked in the source and found nothing.

What does SPJ stand for?

Apparently it stands for "Select Project Join":

  What’s in a name?

  If some of this seems familiar but you don’t remember hearing the term
  “Adaptive Query Localization” before then you’re not going crazy –
  previous internal names were SPJ (Select Project Join) and Pushed-Down
  Joins. We just figured that Adaptive Query Localization was more
  descriptive.

Source: Dramatically Increased MySQL Cluster JOIN performance with Adaptive Query Localization


Does a change in a query executed with sp_executesql cause recompilations? I am experiencing a lot of re-compilations in my database. Most of the queries being executed are simple queries and all of them are being executed using the sp_executesql stored procedure.

Sometimes the query changes (not the parameters, but the query itself). I know this causes  SQL Server to invalidate the plan.

If I change this query:

exec sp_executesql
        N'SELECT nome
        from customer
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

to

exec sp_executesql
        N'SELECT nome, address, phone
        from customer
        where name = @name'
    ,N' @name varchar(50)'
    , @name=N'rafael';

The plan will be invalidated.

Does this also cause a re-compilation?

Those queries are not the queries I am running, they are only examples.

EDIT

I am not using #temp tables. Bellow are 2 queries that are being recompiled:

SELECT TOP 1000 X, Y, Z
FROM MyTable
WHERE Contains(VarcharMaxField, @Clause)
    AND XXX IN (SELECT data FROM fn_ArrayTable(@Pub))
    AND YYY = 1 AND ZZZ < DateAdd(n, -10, GetDate())
ORDER BY A, B, C;

SELECT TOP 1000 A, B, C
FROM MyTable
WHERE Contains(VarcharMaxField, @Clause)
    AND (XXX IN (@Pub0,@Pub1))
    AND (AAA= 1)
    AND (BBB< DateAdd(n, -10, GetDate()))
ORDER BY A, B C;

I had to change the fields due to an internal company policy. The fn_ArrayTable is a function that returns a comma separated string as a table.

It won't actually cause a recompilation, rather you have a completely new query and so it will have to be compiled for the first time.

If these sp_executesql statements are being called from within a stored procedure there might be some other cause for the recompile (such as a temp table or option).

I would recommend following the steps in http://support.microsoft.com/kb/243586 to troubleshoot exactly what statement is causing your recompilations.


How to restore a SQL Server database along with a standalone application? I am new to Microsoft SQL Server. I mostly use MySQL and Firebird databases in my projects.

I have a client who needs to restore some software after his computer crashed.  All that is left is a pen drive with the application and database backup.

I have discovered that this application used Microsoft SQL Server 7 and it comes with three files: the executable, an .MDF and a .LDF file.

I installed the trial version of SQL Server 2012 and defined c:\mssql7\data as the database folder in order to match the original files. I also set the user interface to work with Windows accounts, because I do not know if that application programmer changed the database default user and password.

But when I run the application, it displays this message:

  [DBNETLIB][ConnectionOpen (Connect()).]SQL Server do not exist or
  access denied.

What should I do to restore the database and get it to work with the application?  The original developer cannot be located.

Perhaps the login and password I am using does not match the ones in the restored database files?  Perhaps I have a misconfiguration somewhere, for instance maybe I cannot simply copy the files into the data folder?  Perhaps I need to register the database somehow?

Edit 1: The only thing I found by reading the contents of the executable file was:

Provider=SQLOLEDB
Itegrated Security=SSPI
Persiste Security Info=False
User ID=usuario
Initial Catalog=FVBraganca
DataSource=SCFRANGOS

If you guys want to see the original file, you can download it from my website:

http://alcantara.net.br/MS_SQL_APP_BUGGY.rar
http://alcantara.net.br/MS_SQL_APP_BUGGY.zip

Good deal with getting the connection string out.  Based on that and the error it looks like you'll need to change some things around with your SQL config.

First, SQL 2012 does not support the database compatibility level for SQL Server 7.  You'll need to go back down to at least SQL Server 2005.

Based on that connection string info the app is for a SQL server named SCFRANGOS.  You can try to create a host entry for SCFRANGOS on the machine you're running the app from and give it the IP of your SQL server, or create it in DNS.

The database name you need to use FVBraganca.  Since you have both the mdf (data) and ldf (log) you can try to reattach the database to your SQL Server.

You also need to create a local user on the server and name it usuario.  Then you need to create a Windows login on the SQL server for usario and that login needs to be added to the FVBraganca dabase as a user.

Alternatively, if you're comfortable with editing the binary you could edit your own connection information into the exe.

Steps:

Install SQL Server 2005 or earlier on your server.  You can download SQL Server 2005 Express from here.  Again, SQL 2005 is the newest version you can use that will support the SQL 7 database format.
Make sure the server where SQL Server is installed can be resolved as SCFRANGOS on your network from wherever you are running the application.  You can add a host entry or an alias in DNS.  Or you can add an entry in the hosts file where you will run the application (c:\windows\system32\drives\etc\hosts typically).  When this step is complete you should be able to receive replies when doing a ping SCFRANGOS from the machine where you want to run the application.
Attach the database and name it FVBraganca.  Connect to SQL Server and run this (assuming your mdf and ldf files are in c:\myapp and are called myapp.mdf and myapp_log.ldf):

CREATE DATABASE FVBraganca
ON (FILENAME='c:\myapp\myapp.mdf'),
    (FILENAME='c:\myapp\myapp_log.ldf'),
FOR ATTACH;
GO
Create a user called usario on the server where SQL Server is installed.  Instructions are found here.
Create a login in SQL Server for the user created above

CREATE LOGIN [myserver\usario] FROM WINDOWS
GO
Add the new login as a user of the database.  If the user already exists you'll need to remove it first so you don't have a mismatch of the security identifiers.  The existing user will have the security identifier tied to it from the original SQL Server where the database was last used.

USE FVBraganca
GO
DROP USER usario
GO
CREATE USER usario FOR LOGIN usario
GO
Since you don't know what the user needs access to yet you might just want to grant it dbo access to the database.  This is typically not a good idea but in this case it'll be easier until you know for sure what objects in the database the application uses.

sp_addrolemember @rolename='db_owner', @membername='usario'

That should be it.  As long as the database attaches cleanly I think this should get you going.


How to have a one-to-many relationship with a privileged child? I want to have a one-to-many relationship in which for each parent, one or zero of the children is marked as a “favorite.” However, not every parent will have a child. (Think of the parents as questions on this site, children as answers, and favorite as the accepted answer.) For example,

TableA
    Id            INT PRIMARY KEY

TableB
    Id            INT PRIMARY KEY
    Parent        INT NOT NULL FOREIGN KEY REFERENCES TableA.Id

The way I see it, I can either add the following column to TableA:

    FavoriteChild INT NULL FOREIGN KEY REFERENCES TableB.Id

or the following column to TableB:

    IsFavorite    BIT NOT NULL

The problem with the first approach is that it introduces a nullable foreign key, which, I understand, is not in normalized form. The problem with the second approach is that more work needs to be done to ensure that at most one child is the favorite.

What sort of criteria should I use to determine which approach to use? Or, are there other approaches I am not considering?

I am using SQL Server 2012.

Another way (without Nulls and without cycles in the FOREIGN KEY relationships) is to have a third table to store the "favourite children". In most DBMS, you'll need an additional UNIQUE constraint on TableB.

@Aaron was faster to identify that the naming convention above is rather cumbersome and can lead to errors. It's usually better (and will keep you sane) if you don't have Id columns all over your tables and if the columns (that are joined) have same names in the many tables that appear. So, here's a renaming:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    UNIQUE (ParentID, ChildID)

FavoriteChild
    ParentID        INT NOT NULL PRIMARY KEY
    ChildID         INT NOT NULL
    FOREIGN KEY (ParentID, ChildID)
        REFERENCES Child (ParentID, ChildID)

In SQL-Server (that you are using), you also have the option of the IsFavorite bit column you mention. The unique favourite child per parent can be accomplished via a filtered Unique Index:

Parent
    ParentID        INT NOT NULL PRIMARY KEY

Child
    ChildID         INT NOT NULL PRIMARY KEY
    ParentID        INT NOT NULL FOREIGN KEY REFERENCES Parent (ParentID)
    IsFavorite      BIT NOT NULL

CREATE UNIQUE INDEX is_FavoriteChild
  ON Child (ParentID)
  WHERE IsFavorite = 1 ;

And the main reason that your option 1 is not recommended, at least not in SQL-Server, is that the pattern of circular paths in the foreign key references has some problems.

Read a quite old article: SQL By Design: The Circular Reference

When inserting or deleting rows from the two table, you'll run into the "chicken-and-egg" problem. Which table should I insert first - without violating any constraint?

In order to solve that, you have to define at least one column nullable. (OK, technically you don't have to, you can have all columns as NOT NULL but only in DBMS, like Postgres and Oracle, that have implemented deferrable constraints. See @Erwin's answer in a similar question: Complex foreign key constraint in SQLAlchemy on how this can be done in Postgres). Still, this setup feels like skating on thin ice.

Check also an almost identical question at SO (but for MySQL) In SQL, is it OK for two tables to refer to each other? where my answer is pretty much the same. MySQL has no partial indexes though, so the only viable options are the nullable FK and the extra table solution.


backup maintenance plan fails with error 0x80131534 I recently set up a series of maintenance plans to backup all online databases. A full backup runs once a week, a differential runs once a day, and a transactional runs every ten minutes. We have two SQL Server instances on two machines. One works perfectly, backing up to another server on its network. The other, a Google cloud compute instance, is trying to back up to another Google compute instance, and it can't.

The SQL server in question, let's call it SQL1, is trying to back up to a Google server we'll call storage1. Storage1 is NOT a Google Storage bucket, just a full server with space to store the backups. Thus, standard Windows sharing should apply. I've made the SQLBackups folder on Storage1's hard drive fully shared to everyone on the network, so accessing it shouldn't be a problem. In fact, the plan can't be created unless the folder can be reached, so I know that's working. Yet, when I run the plan, it fails within ten seconds. The plan log isn't helpful, but the job log is more interesting: it reports that the job failed because it "could not load package. Error: 0X80131534. Description: no description available." Google has next to nothing on this error number.

The odd thing is, I ran into a similar problem while setting the same plans up on the other server, but it went away in the course of working out how to do all this so I never found out what exactly the problem was. I'm very confused, and in the meantime, our database isn't able to back up. Anything anyone can suggest is appreciated. Again, this is just the full backup job, and it's set to ignore offline databases and back up all databases. Thanks.

Grant the access to the computer account since the service is running under NT Service\InstanceName: domain_name>\$

Example: Domain1\MyServerName$

MyServerName must be the name of the server, not the name of the SQL instance. Get the server name via hostname in a cmd window. By adding the dollar sign to the end, it's the computer account.


Category entity relationship / subtype relationships design I am looking to implement a generic comment and file attachment facility within a web application which would be used across various parts and allow users to provide comments and add attachments to different entities.

Whilst looking for a suitable solution I came across this answer and I wanted to check I am fully understanding the implementation of this approach and understand the potential pitfalls.

Using a schema similar to the one below the ContractId, ContractLineId and VariationId values, along with any future entities, would be generated using a SEQUENCE (using SQL Server 2012) and each generated value would be stored in the Entity table along with its EntityType.

The use of the SEQUENCE across multiple entities would allow for a single Comment and Attachment table and therefore a Comment or Attachment could be created against a Contract, ContractLine and Variation and any future entities.

The Entity table is used as a sort of junction table (or some other term) and would allow queries to return all Attachments against particular entity type(s).

Though things get a little messy if attachments against multiple entities need to be returned along with some information about the entity. For example, if all comments made by a user were to be queried along with the name of the entity the comment was against.

Am I going down the right path or should this be avoided.

I think this looks pretty reasonable for a system in which a user can add comments or attachments associated with a contract, a specific line of the contract, or a proposed modification to a line of a contract.

Since you asked for potential shortcomings or pitfalls, here are a few you might consider:

Without knowing much about your system, it seems strange that a variation must apply to only one contract line.  Is there ever a case where a variation might apply to multiple lines?  And then a user might want to comment on the multi-line variation in its entirety?  If so, this case doesn't appear to be handled  right now.
It's surprising that there aren't any columns other than EntityType that are common across all entities.  Perhaps there should be an EntityName column rather than separate columns ContractName, ContractLineName, and VariationName?  This would make the example query you describe in your question far simpler.
The data model  does not strictly enforce that the Variation contains logically consistent data.  It stores both a ContractId and a ContractLineId.  The foreign keys for these columns enforce that each is valid independently, but allow for a ContractLineId to be inserted that comes from the incorrect ContractId (that is, different than the ContractId for that ContractLineId in the ContractLine table).  One option to address this is to remove the ContractId column from the Variation table.


How to add a linked server to a sql server management studio? I have an account in sql server management studio, which I can login to database B, which is on server X.
I also have a crystal report on my local PC.  Using the crystal report, I can access a database (A) thru a ODBC driver, which is DRV_A, install on my local PC.  I can write reports against the database A using the crystal report.

Now I want to directly from sql server management studio and database B to query both database B and A.  So it looks I need to add database A as a linked server to database B.

When I try to do that, I get the following error message:

Cannot show request dialog
  Additional information:
   A requred operation could not be completed.  You must be a member of the susadmin role to perform this openration.  (SqlManagerUI)

So, it seems that

I need to get a role of permission to add the linked server
Install the ODBC driver on server X

What else I should have, IP address and login name, password?

Thank you very much.

Linked Servers are not database specific, but provide a pipeline between two servers.  Of course, the rights granted to the users will control which databases they can access.  The account you are using naturally needs to be able to logon to both servers with enough permissions to execute your query.

Yes, creating a Linked Server is an administrative function and not available to all.  Doing it yourself requires the ALTER ANY LINKED SERVER permission.

The parameters for the sp_addlinkedserver stored procedure are found at: http://msdn.microsoft.com/en-us/library/ms190479(v=sql.110).aspx

Also, under that link are instructions for several different ways to connect the servers together.  If Server X and Server A are both SQL Servers, the setup is quite simple.

You can use sp_addlinkedsrvlogin to manage how the logins authenticate across the linked server.   The details are at: http://msdn.microsoft.com/en-us/library/ms189811(v=sql.110).aspx

Depending on your landscape and if you are using Windows Logins, you may need to have Kerberos configured properly on your landscape so that the Kerberos ticket (allowed permissions) can be forward to the other server.  See: Configuring Linked Servers for Delegation:

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


4 CPUs of 20 (NUMA node 0) Pegged at 100% The previous Senior DBA left the company and I was made aware of a server which suffers from multiple problems, largely slowness (all the way from SSMS taking a long time to open, long running queries, and failing SSIS jobs (Data Warehouse), to difficulties connecting to linked servers (150+ of them)).

There is most likely way too much happening on this server which contains 5 stacked instances. A new Senior DBA will be joining the team soon but it would be nice to have this all straightened out for him or her by the time they join.

So, the nitty-gritty:

When I was made aware of the issue I found that 94% of the server's memory was allocated to SQL Server. I went ahead and brought that down to 85% by deallocating memory from two instances which were overprovisioned.

I then noticed that MAXDOP on our default instance was set to 4 (possibly 6, I don't recall) along with a CPU Affinity setting. Those CPUs were pegged while there was minimal activity on the others. I went ahead and removed the Affinity setting (since these settings were in place before additional CPUs were added). I set MAXDOP to 20 across all 5 instances.

Currently, I'm still seeing 4 of the CPUs pegged but with an overall avg. usage (across all CPUs) of roughly only 25%.

  I've used SysInternal's ProcExp, the Resource Monitor, and the Windows Performance Toolkit to observe the issue but don't really know how to isolate what process(es), specifically, is(are) the root cause. Any recommendations / guidance on how to truly isolate what's happening here? (i.e. specific counters / traces / other programs.)

UPDATE, per requests:
System Info:
Windows Server 2012 R2 Standard
64 GB memory total
20 CPUs

Configuration:
24 GB memory allocated to this instance
26.5 GB memory allocated to other instances (total of 50.5 GB - 78.9%)
Cost Threshold for Parallelism = 50 (across all instances)
I disabled an unused SSAS process.

So you've got 5 'stacked' instances on a single Windows server. You haven't said exactly how many sockets/CPUs are available and how much memory though. I like setting affinity for each instance in such cases, even if I decide to have CPUs overlapping in the struggle to balance the overall CPU load (depends on each instance's load).

Any instance with more than 4 CPUs could use an explicit DOP setting in my experience - rarely over '4' in stacked cases like yours. Don't forget to set 'Cost threshold of Parallelism' for each instance to something reasonable (50?) to avoid excessive parallelism  - in your case this is even more important.

Remember that the memory left "for the OS" should be more now, since you have to account for the footprint of each instance (on top of SSIS etc). Check in SQL Config Mgr if SSAS is also running and adjust its 'max memory' accordingly, by default it goes for 80% of the whole server memory (!)

Also maybe worth taking away and 'Lock pages in memory' rights of the SQL Service account(s) so that the OS can breath and do its job better (if it pages,
everyone suffers!). Also good practice is to set some reasonable 'min memory' for each instance.

I think running sp_blitz and sp_blitz_first on each instance would give you some quick pointers on more pressing issues.

You may also want to monitor some windows permon counters like 'available memory' and 'working set' for each of the processes running there in case you find particular times of the day/night when the server is suffering.


Search a Table for a Lack of Records Please understand this is not my field. I have inherited an access database from a previous employee.  I have a database which logs training so I have 1 table with employees names trade etc.  I then have a table with all the training each person has had.  What I need to do is search to find the names of employees who have not had a specific type of training logged in the second table.  I would like to enter the training description and then it return a list of employee names who have not had this training.

Can anyone help please
Thanks
Regards
Derek

It is helpful if you post the table design, or at least what you will be joining on.  I created a sample that has 2 tables:

1) Student - Name (PK)

2) Training - Id (PK, Autonumber), Name, Class, Date

Create a relationship between Student.Name, Training.Name, enforce referential integrity.

There is a nice "Find Unmatched Records" query wizard in access that will get you going...  I used it as a base and came up with:

SELECT Student.Name
FROM Student
  LEFT JOIN Training ON Student.[Name] = Training.[Name]
WHERE (((Training.Name) Is Null));

From here you can add additional logic in your where clause to whittle it down.

Edit : I missed the part about a specific class - this is probably closer to what you are after:

SELECT Student.Name
FROM Student
where name not in (select name from training where class='your class here')


Upgrade to SQL Server 2017 Fails Windows Server 2016 - Fully Patched
Sql Server 2016 SP2 CU3 -> Upgrade to 2017

In place upgrade fails with error message:

  Wait on the Database Engine recovery handle failed. Check the SQL server error log for potential causes

See this in the event log:

  Script level upgrade for database 'master' failed because upgrade step 'ISServer_upgrade.sql' encountered error 917, state 1, severity 15. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

I'm able to recover from backup, but I need to get this server upgraded.

What am I missing here?

Here is what I'll be trying in a bit (from here)

1) Execute Diff backup in SQL Safe
2) Create Snapshot in VCD
3) Perform Upgrade
4) When fails add -T902 flag to startup parameters
5) Start SQL Server
6) Install CU 11
7) Remove -T902
8) Start SQL Server

Here is what resolved the error, copied lovingly from a Microsoft forum

Here is what I'll be trying in a bit (from here)

1) Execute Diff backup in SQL Safe
2) Create Snapshot in VCD
3) Perform Upgrade
4) When fails add -T902 flag to startup parameters
5) Start SQL Server
6) Install CU 11
7) Remove -T902
8) Start SQL Server

The key here was to start up sql server with the -T902 parameter and then install the latest CU (which is 11 right now)


Using DATE part of datetime column in WHERE clause I'm building a POS system, where I collect the date & time that the event took place. I would like to pull some reports by only using the DATE part of the datetime column in my SQL queries.

My current query looks like:

SELECT * FROM tbl_item_trans
WHERE  (date_time BETWEEN @start AND @end)
ORDER BY date_time DESC, code

However, if I put in two dates e.g. 14th and 15th September it only pulls data from 14. If I want to include 15th September transactions, the end date would have to be 16th September.

How can I solve this problem? It would be better also if I only used the DATE part of the datetime column.

the @start and @end variables use the date data type, e.g. '2017-09-16'.

Community wiki answer:

Try an inclusive start date and exclusive end date:

date_time >= @start AND date_time < DATEADD(day, 1, @end)

See also What do BETWEEN and the devil have in common? by Aaron Bertrand.

Sargability wouldn't be a problem if the query is written to use CAST(date_time AS DATE), since SQL Server will include a helper covering seek predicate. See Understanding SARGability (to make your queries run faster) by Rob Farley (recorded at SQLBits VII in 2010) which demoed this. Note however that the conversion might still affect the accuracy of cardinality estimates.


How do I construct a LINESTRING by aggregating Points (ST_MakeLine) in MariaDB? Let's say I have a table with 3 points,

CREATE TABLE foo ( id int PRIMARY KEY, geog point );
INSERT INTO foo (id, geog) VALUES
  ( 1, Point(0,0) ),
  ( 2, Point(1,1) ),
  ( 3, Point(2,2) );

How can I construct a LINESTRING with those three points in those three different rows. The resulting WKT should be LINESTRING( 0 0, 1 1, 2 2 )?

PostGIS does this with a Spatial Aggregate called ST_MakeLine

SELECT ST_MakeLine(geog)
FROM foo
ORDER BY id;

Because MariaDB (and MySQL) lack all spatial aggregates, the only method is currently to create the WKT, and force MySQL to parse that WKT.

SELECT ST_AsText(
  ST_GeomFromText(CONCAT(
    'LINESTRING (',
    GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ','),
    ')'
  ))
)
FROM foo
ORDER BY id;
-- returns LINESTRING (0 0,1 1,2 2)

The inside part

GROUP_CONCAT(CONCAT(ST_X(geog),' ',ST_Y(geog)) SEPARATOR ',')

Creates the point list, and then we just wrap that in LINESTRING ( $pointlist ) and feed that to ST_GeomFromText()

ℹ There are type-specific versions of ST_GeomFromText, but at least in MariaDB they seem to do nothing different, except add confusion.


Select all values in same group using value in group I have following query:

SELECT
    SubstitutionGroupID,
    Material.Number,
    Material.DescriptionEN
FROM
    MaterialSubstitution
    INNER JOIN Material on Material.MaterialID = MaterialSubstitution.MaterialID
ORDER BY
    SubstitutionGroupID, Material.Number

Result example:

SubstitutionGroupID Number      DescriptionEN
52494               303H603021  HINGE R
52494               3H603020    HINGE R
52495               303H604031  TRAY EJECT
52495               3H604030    TRAY EJECT (1/2)

I want have a query where I can define one number and recieve all numbers in one SubstitutionGroupID.

I believe this is what you are after if I have understood your requirements:

DECLARE @Number NVARCHAR(30);
SET @Number = '3H603020';

SELECT
    ms.SubstitutionGroupID,
    subs.Number,
    subs.DescriptionEN
FROM
    MaterialSubstitution AS ms
    INNER JOIN Material  AS m ON m.MaterialID = ms.MaterialID
    INNER JOIN Material  AS subs ON subs.MaterialID = ms.MaterialID
WHERE
    m.Number = @Number
ORDER BY
    ms.SubstitutionGroupID, subs.Number

You need to join back to Material again to find the "substitutions" (hence the alias subs) for a given Material.Number


Under what user does my process run on Oracle Linux? We experience problems with directory access on some Oracle databases running on Linux.

We have two kinds of processes that need to read/write files from a directory: 1 started in a user session, 2 started by Oracle scheduled job.

It seems that on some systems these processes run under different Linux users and that permissions on the Linux file system are only granted to one of them.

Is this assumption correct and what users are these?

This is such a common problem when the database was installed with user separation (grid + oracle user) and DBAs tend to overlook this.

When you use RAC or even just Oracle Restart (with or without ASM), you need to install Grid Infrastructure. Grid Infrastructure can be installed as a different user (typically grid).

When you have Grid Infrastructure, the proper way to handle listeners is through Grid Infrastructure. If Grid Infrastructure was installed with grid user, then the listener runs as grid user.

In Oracle architecture, by design, remote connections log in through the listener, and the database server process is forked by the listener. On Linux/UNIX platforms, the oracle binary is owned by oracle user, and it has the setuid bit enabled. grid and oracle users share a common group, and the oracle binary can be executed by the members of this group.

Given the above information, remote connections coming through the listener running as grid user can spawn processes whose UID and EUID is the same as the UID of oracle.

So far this is what usually everyone knows. The difference is however the RUID and the inherited privileges because of it.

On a machine with users as (this is the default configuration taken from an Exadata X5-2 compute node, so this is how Oracle officially deploys its configuration):

$ id oracle
uid=1001(oracle) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1002(dba),1003(dboper)
$ id grid
uid=1000(grid) gid=1001(oinstall) groups=1001(oinstall),1004(asmdba),1005(asmoper),1006(asmadmin)

When you log in locally, all user IDs are 1001 (=oracle):

sqlplus user/password

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
85510

SQL>!ps -o uid,euid,ruid -p 85510
  UID  EUID  RUID
 1001  1001  1001

But when you log in through the listener:

sqlplus user/password@orcl

SQL>select spid from v$process where addr = (select paddr from v$session where sid = sys_context('userenv', 'sid'));

SPID
------------------------
90372

SQL>!ps -o uid,euid,ruid -p 90372
  UID  EUID  RUID
 1001  1001  1000

Notice that the RUID is different, it is the UID of grid user.

For example, this can lead to a situation, where file generation from the database with UTL_FILE is successful when executed in a scheduled job, but fails when executed from a remote session.

With the above setup, server processes forked by the listener inherit the privileges of grid user. Scheduled jobs spawned by the database itself inherit the privileges of oracle user. If they do not have the same privileges, they will behave differently.


MS DTC errors on SQL Server 2008 R2 but not SQL Server 2000 running on difference instance I am receiving errors when I try to run a distributed query against different SQL Server 2008 R2. At first I thought this might be cause the servers have been set up on a VM using a clone but after I discovered that I could run this query on SQL Server 2000 but it fails on a SQL Server 2008 R2 on the same server I am beginning to think that there is something I am just missing.

It does run without any issues on another developers network but it isn't running VMs

Steps I have done so far:

Totally uninstalled MS-DTC, rebooted and installed MS-DTC and another reboot.
Ran Dtcping - no errors
Checked to make sure there are no duplicate SIDs
Asked my non geek wife. She changed the subject to shopping
Rechecked the link servers - everything checks out.
Ran profiler on the transaction - no information given.
Checked the MS-DTC on production boxes to look for anything I might have missed. Also checked out from sp_configure from production boxes to look for anything that I might have missed.
uninstalled/reinstalled MSDTC just in case.
Made sure that the security on DTC isn't stopping me from doing anything.
Firewalls are off on every server I had played with.
Got caught googling MSDTC on my phone while having lunch with my wife.
Updated my servers for with any missing patches.
Talked to my dog about it. At least he listen but didn't have any answers.
Picked up everything and tried it on a different server. No change.
Read the logs from MSDTC to find out that the parent node is the one asking for the abort request

We have run into issues when servers were re-named and we had to set the DTC security properties to "No Authentication Required" on all servers involved in the transaction. This is not a best practice but works for internal network only servers. Directions to the DTC security settings can be found here: https://technet.microsoft.com/en-us/library/cc731495.aspx


Change column type from varchar to text in all tables at once I am using Postgres. I would like to change column type in all tables where column name is "description" from varchar(255) to text.

If anyone knows that I would be very glad for your help.

I guess you don't want to manually run all the necessary ALTER TABLE statements.

You can use the following statement to generate the needed ALTER TABLE statements:

select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
from information_schema.columns
where table_schema = 'public'
  and column_name = 'description'
  and data_type = 'character varying'
  and character_maximum_length = 255;

replace 'public' with your schema if your tables are not located in the public schema.

Run the above statement, spool the output into a text file, then run the generated script.

Something like this:

psql (9.3.4)
Type "help" for help.
postgres=> \t
Showing only tuples.
postgres=> \o alter.sql
postgres=> select 'alter table '||table_schema||'.'||table_name||' alter column '||column_name||' type text;'
postgres-> from information_schema.columns
postgres-> where table_schema = 'public'
postgres->   and column_name = 'description'
postgres->   and data_type = 'character varying'
postgres->   and character_maximum_length = 255;
postgres=> \i alter.sql


Oracle Autotrace vs Explain My team uses Oracle 11 and SQL Developer.  I've been relying heavily on explain plans lately to try and determine the most efficient way to solve various problems.  Recently, a coworker pointed out that explain plan is not always accurate to what actually happens in the database, and that an autotrace is a better indication since the query is actually run against the data.

Testing a query, I've gotten the following results

_________________________________
|      Method        |   Cost   |
|--------------------|----------|
| Query A Explain    |  306,188 |
| Query A Autotrace  |  399,131 |
| Query B Explain    |   99,226 |
| Query B Autotrace  |  137,661 |
|____________________|__________|

When using the autotrace, query A had a cost increase of 30% and Query B of nearly 40%.  Obviously, I should be using query b in both cases, but I don't understand what causes them to differ.

Autotrace in SQL Developer gets the plan from v$sql_plan, and also gets the stats from your session, does a delta of session stats before and after running the query.

Explain Plan asks the database what it THINKS the plan will be for your query.

Your co-worker is right, they can differ wildly, and you're better served to use AutoTrace or our new feature in 4 and higher where we show the cached plan (a drop down control on the explain plan button makes these available.)


On PostgreSQL 9.1, it is possible to get to this state without directly manipulating the catalogs.

I started from having plpgsql as an extension, and did the following:

# DROP EXTENSION plpgsql CASCADE; -- do this only if you are prepared to lose your plpgsql functions, too
# \dx
                                     List of installed extensions
     Name      │ Version │ Schema │                            Description
───────────────┼─────────┼────────┼────────────────────────────────────────────────────────────────────
 btree_gin     │ 1.0     │ public │ support for indexing common datatypes in GIN
 btree_gist    │ 1.0     │ public │ support for indexing common datatypes in GiST
 dblink        │ 1.0     │ public │ connect to other PostgreSQL databases from within a database
 fuzzystrmatch │ 1.0     │ public │ determine similarities and distance between strings
 hstore        │ 1.0     │ public │ data type for storing sets of (key, value) pairs
 intarray      │ 1.0     │ public │ functions, operators, and index support for 1-D arrays of integers
 pgcrypto      │ 1.0     │ public │ cryptographic functions

# SELECT lanname FROM pg_language;
  lanname
───────────
 internal
 c
 sql
 plpythonu

CREATE PROCEDURAL LANGUAGE plpgsql; -- this won't work in 9.3, for example

After this, I have no plpgsql as an extension, but it is listed among the languages.  You can define functions in this language as usual.


Find which functions are used I have a legacy PostgreSQL database schema with hundreds of functions. I know that some of them aren't used anymore, and I want to delete them.

What is the easiest way to find which procedures are executed sometimes and which aren't? Is there any kind of statistics about procedure usage in PostgreSQL?

Used engine version is PostgreSQL 9.5.

Yes, use the statistics collector. The manual:

  PostgreSQL's statistics collector is a subsystem that supports
  collection and reporting of information about server activity. [...]
  It can also count calls to user-defined functions and the total time
  spent in each one.

  [...]

  The parameter track_functions enables tracking of usage of
  user-defined functions.

Bold emphasis mine.

track_functions (enum)

  Enables tracking of function call counts and time used. Specify pl to track only procedural-language functions, all to also track SQL and
  C language functions. The default is none, which disables function
  statistics tracking. Only superusers can change this setting.

  Note: SQL-language functions that are simple enough to be "inlined" into the calling query will not be tracked, regardless of this setting.

So this is off by default. If all relevant functions are LANGUAGE plpgsql, it's enough to set it to pl in postgresql.conf and reload. No need to restart the server for this setting.

track_functions = pl

Reload with pg_ctl reload. Or on a typical Debian(-related) installation:

sudo -u postgres pg_ctlcluster 12 main reload

Postgres starts to gather information. After an adequate period of time, check track records. Careful though, some important functions may not be called in weeks. Your call ...

How to access gathered statistics? The manual again:

  When statistics collection is enabled, the data that is produced can
  be accessed via the pg_stat and pg_statio family of system views.
  Refer to Chapter 27 for more information.

Basically:

SELECT * FROM pg_stat_user_functions;

Or you can easily look it up with pgAdmin (or another GUI). In pgAdmin (3 or 4), select the function and look at the "Statistics" tab top right: "Number of Calls".

Aside:
There are no true "stored procedures" before Postgres 11, just functions. The term "procedure" is often used synonymously.


DB Design betwwen 2 DBs VS 2 Schemas We're preparing a database design for an ERP. The ERP are composed from several modules and every module has his proper db, which can deployed at least on one personnel schema.

When we have the choice to choose between deploying the app on 2 databases on the same physical server or on 2 schemas always on the same server, which strategy we have to choose or use?

We're using PostgreSQL 9.4 and Jboss server.

While cross-database queries are possible and quite efficient with a current release (not such much with the somewhat dated 9.4 version), they are still more complicated then simply joining two tables from two different schemas.

So, if there is just a slight chance that you want to do queries that use tables from more then one module (e.g. for reporting) separate the modules using schemas.

Using different database doesn't really buy you that much unless you also host them on separate instances, which would enable you to e.g. replicate them individually (replication can - as of 9.6 - still only be done per cluster with stock Postgres).

Different database would help if those modules were more like tenants and you want to make sure that a single module and their (database) users never have the chance to even take a glimpse on the other tables.

Unrelated, but: for a new project you should really consider the current Postgres version which is 9.6.


How to show two tables in one query? part and the whole I have these two tables:
FinalExam

+----+------------+-------+
| id |   title    | score |
+----+------------+-------+
|  1 | math       | 12.67 |
|  2 | literature | 17    |
+----+------------+-------+

Quiz

+----+------+-----------+-------+
| id | feid | quiztitle | score |
+----+------+-----------+-------+
|  1 |    1 | midterm   |    14 |
|  2 |    1 | midterm2  |    11 |
|  3 |    2 | quiz1     |    16 |
|  4 |    1 | finalexam |    13 |
|  5 |    2 | finalexam |    18 |
+----+------+-----------+-------+

I want a result set where:

Quiz rows with the same feid are arranged together;
each row from FinalExam is added at the beginning of the corresponding feid group (Quiz.feid is a reference to FinalExam.id).

Expected output
For the above samples the result should look like this:

+----+------------+-------+
| id |   title    | score |
+----+------------+-------+
|  1 | math       | 12.67 |
|  1 | midterm    | 14    |
|  2 | midterm2   | 11    |
|  4 | finalexam  | 13    |
|  2 | literature | 17    |
|  3 | quiz1      | 16    |
|  5 | finalexam  | 18    |
+----+------------+-------+

Preparation sample data:

CREATE TABLE FinalExam(id INT, title VARCHAR(128), score FLOAT);

CREATE TABLE Quiz(id INT, feid INT, quiztitle VARCHAR(128), score FLOAT);

INSERT INTO FinalExam VALUES
(1,'math',12.67),
(2,'literature',17)

INSERT INTO Quiz VALUES
(1,1,'midterm',14),
(2,1,'midterm2',11),
(3,2,'quiz1',16),
(4,1,'finalexam',13),
(5,2,'finalexam',18)

Query to get result:

SELECT id,title,score
FROM (
    SELECT id, feid=id, title, score, final=CAST(1 AS BIT)
    FROM FinalExam

    UNION ALL

    SELECT id, feid, quiztitle, score, final=CAST(0 AS BIT)
    FROM Quiz
) A
ORDER BY feid,final DESC

Now you can see data in right order: final exam first then rest of quiz grades connecting with this final exam. You can delete ALL statement to allow your query delete duplicates.


mongodump sharded collection size is smaller after restoring I need to change shard index from range to hashed in a sharded collection. So I need to mongodump and restore the collection. I have 3 shards with 3 replica sets.MongoDB version is:  3.2.11
I started mongodump from one of mongoses.

mongodump --collection Collname --db dbname

I have "count" : 12925651, "size" : 21233976913 in main collection.

But mongodump is done at %75 with

[##################......]  dbname.Collname  9700124/12925651  (75.0%)  done dumping dbname.Collname (9700124 documents)

I restore the dump to another db and collection size is smaller than the main collection.

It seems that mongodump doesn't take data from one of the shards. Shard distribution is :

Totals
 data : 19.77GiB docs : 12925651 chunks : 475
 Shard shard01 contains 24.98% data, 24.98% docs in cluster, avg obj size on shard : 1KiB
 Shard shard02 contains 50.03% data, 50.03% docs in cluster, avg obj size on shard : 1KiB
 Shard shard03 contains 24.98% data, 24.98% docs in cluster, avg obj size on shard : 1KiB

What is the problem? And what can i do for replacing the shard index?

If the cluster is healthy, there are no documents with same sharding key at different shards. If there is "duplicates", those are "orphans" what should be cleaned from cluster.

Here more information about orphans and how to remove those.


Postgres not vacuuming 1 table I have created a table where I store serialized Java objects. They are frequently accessed and costly to create, so that is the reason. Solution works fine to me, however table size grows to 25GB inside few days! The objects are removed after few mins, so it's not case the case of amount of data really held, but simply vacuum does not do its job. After full vacuum table size drops to approx. 150 MB. In the logs I see that vacuuming process is running, but as table grows in size, it takes over 5 mins and does not help a lot.

I wonder how can I tune the vacuum to make its job better for this table?

First, take a long, careful look at Aggressive Autovacuum on PostgreSQL as it may very well describe your problem, and check pg_locks to see if there is a locking issue on the table (there may well be one) and also look carefully at Josh Berkus's recommended autovacuum settings for aggressively autovacuuming.

To recover from a bloated table you can run the cluster command from psql (exclusively locks a table, but much faster than vacuum full).

Third, if this is a locking issue, you may try to use a cron job to run a vacuum or even vacuum full (or cluster) during low-utilization times.


Removing foreign key when it is not named I've created a table named 'student' as :

create table student(
  stud_id number primary key,
  stud_name varchar2(30) not null,
  branch_id varchar2(4) references branch(branch_id),
  course_id number references course(course_id),
  hostel varchar2(4)
);

Later on, I found that I don't need course_id column, now I want to remove that column, when I tried to do that, a warning message appeared saying : "cannot drop column from the table owned by SYS" then I thought, I must remove foreign key constraint first and then drop table, but that didn't work. I used :

alter table student drop column course_id;

and since I didn't name my constraint, therefore can't use the following statement:

alter table student drop constraint;

I am using oracle database 11g

You can use cascade constraints to also drop any constraint related to the column:

alter table student drop column course_id cascade constraints;

More details regarding the drop column clause can be found in the manual.

If you want to find out how the constraint was actually named (e.g. if you only want to drop the constraint, not the column), you can query the system catalogs for this:

select constraint_name
from user_cons_columns
where table_name = 'STUDENT'
and column_name = 'COURSE_ID';

That will return something like SYS_C0052446 (it will be a different value on your system). And that name then can be used for the drop constraint:

alter table student drop constraint SYS_C0052446;

More details about the system catalog can be found in the manual


Replication Broken, List Out of Sync Tables In a master-slave setup on MySQL 5.5 on RHEL 6.1, when db replication breaks (for whatever reason), what is a good way to:

check (and alert) replication is broken?
get a list of tables that are out of synch across various databases?

I don't have access to server logs, but can query the tables under "mysql" db and "information_schema", in addition to the dbs used by the applications.

With regard to the first point

  check (and alert) replication is broken?

just check the following

MYSQL_CONN="-uroot -ppassword"
SLAVESTATUS_OUTPUT=/tmp/ShowSlaveStatus.txt
mysql ${MYSQL_CONN} -ANe"SHOW SLAVE STATUS\G" > ${SLAVESTATUS_OUTPUT}
IO_RUNNING=`grep "Slave_IO_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`
SQL_RUNNING=`grep "Slave_SQL_Running" < ${SLAVESTATUS_OUTPUT} | awk '{print $2}'`

Just check for both IO_RUNNING and SQL_RUNNING being Yes.

If either value is No, check the error message.

You could check for other things such as Seconds_Behind_Master ( Does `Seconds_Behind_Master` show exact Slave Lag from Master? )

With regard to the second point

  get a list of tables that are out of synch across various databases?

The best tool in the world for this is pt-table-checksum. Please read the documentation on it because the output will be all the tables in the specified databases. You will have to parse the text output to compare the checksum values between Master and Slave.

I wrote a part post about using this tool:

In a MySQL Master/Slave replication, what would happen if I write to the Slave?
Fixing tables out of sync as reported by pt-table-checksum, pt-table-sync is not working (This one has shell scripting  I made to do the parsing for you)


Query to sort data from a MYSQL 8 table I request your kind support with the following case.
I have this score table as follows:

create table score (idscore int primary key auto_increment,
                    ndeporte varchar(45),
                    category varchar(45),
                    gender varchar(45),
                    team1 varchar(45),
                    score1 int,
                    team2 varchar(45),
                    score2 int,
                    team3 varchar(45),
                    score3 varchar(45)

)

MY SCORE TABLE

NOW I WANT TO GET THIS RESULT.

I have doubts on how to order my data from my table so that it shows as well as the image.
I request your kind support

I am working on MYSQL 8

Do it directly:

SELECT idscore,
       ndeporte,
       category,
       gender,
       'ROJO' team1,
       CASE WHEN team1 = 'ROJO' THEN score1
            WHEN team2 = 'ROJO' THEN score2
            WHEN team3 = 'ROJO' THEN score3
            END score1,
       'AMARILLO' team2,
       CASE WHEN team1 = 'AMARILLO' THEN score1
            WHEN team2 = 'AMARILLO' THEN score2
            WHEN team3 = 'AMARILLO' THEN score3
            END score2,
       'TURQUESA' team3,
       CASE WHEN team1 = 'TURQUESA' THEN score1
            WHEN team2 = 'TURQUESA' THEN score2
            WHEN team3 = 'TURQUESA' THEN score3
            END score1
FROM score;


Filtering Escape Characters in PostgreSQL I am trying to filter distinct serial number from a table and join two tables. Current my code looks like
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM ( select
                 serial_nbr,
                 CASE
                 when substring(serial_nbr FROM 2 for 2) = '18' THEN
                 regexp_replace(serial_nbr,'(...)(\w{{5}})','\\1 \\2')
                 when substring(serial_nbr FROM 2 for 2) = '19' AND length(serial_nbr) = 9 THEN
                 regexp_replace(serial_nbr,'(...)(\w{{6}})','\\1 \\2')
                 ELSE serial_nbr END as serial
                 FROM controller_returns) as t1
LEFT JOIN dblink('{param_db}',
            'SELECT DISTINCT ctrlr_serl_nbr,insert_ts
            FROM ctrlr_basic_setng')
as q1(ctrlr_serl character varying,insert_ts timestamp without time zone)
on q1.ctrlr_serl_nbr = t1.serial
WHERE t1.serial_nbr~'^[ABCD]([AC0-9]{{1,9}})'
ORDER by t1.serial_nbr, substring(t1.serial_nbr from 2 for 2), substring(t1.serial_nbr from 1 for 1)

I am using postgreSQL and connecting through using psycopg2 in python.
This is evolving code but I been noticing that the serial # that I am getting aren't truly distinct. I would sometimes get case like this 'A1800038' and 'A1800038\n'. What I am trying to figure out is is how to filter out the serial with \n. I was trying to do it in the WHERE clause doing '^[ABCD]([AC0-9][^\\n]{{1,9}})' or '^[ABCD]([AC0-9][^[:space:]]{{1,9}})' but neither work.
Any help would be much appreciated.

...how to filter out the serial with \n.

Add this in the subquery t1:
WHERE t1.serial_nbr !~ '\\n$'

You have to double \ to remove its special meaning. To identify actual newline characters (E'\n' - ASCII code 10), use a single \ instead.
$ signifies the end of the string.
That said, you could use cheaper LIKE expressions in your query:
SELECT DISTINCT ON (t1.serial_nbr) t1.*, q1.serl_nbr
FROM  (
   SELECT serial_nbr
        , CASE WHEN serial_nbr LIKE '_18%'      THEN regexp_replace(serial_nbr, '(...)(\w{{5}})','\\1 \\2')
               WHEN serial_nbr LIKE '_19______' THEN regexp_replace(serial_nbr, '(...)(\w{{6}})','\\1 \\2')
                                                ELSE serial_nbr END AS serial
   FROM   controller_returns
   WHERE  t1.serial_nbr !~ '\\n$'   -- !
   ) t1
LEFT   JOIN dblink('{param_db}', 'SELECT DISTINCT ctrlr_serl_nbr, insert_ts FROM ctrlr_basic_setng')
         AS q1(ctrlr_serl varchar, insert_ts timestamp)
         ON q1.ctrlr_serl_nbr = t1.serial
WHERE  t1.serial_nbr~'^[ABCD]([AC0-9]{{1,9}})'
ORDER  BY t1.serial_nbr, substring(t1.serial_nbr FROM 2 FOR 2), left(t1.serial_nbr, 1);


Are 2 INSERT statements guaranteed to complete sequentially? We've got a system that receives event from clients and stores them in a database.

Each event is saved as an atomic INSERT, and has an id_event which is an auto-incremented primary key.

Our clients can execute a specific action that causes 2 events to get fired in rapid succession:

1) name: 'board-added', boardId: 12344
2) name: 'board-switched', boardId: 12344

Both events arrive at our server in the correct order.

However, for some reason we keep finding some board-switched events having an id_event that precedes it's corresponding board-added event.

As you can see in the below result set, board-added has a higher id_event than board-switched which is wrong.

{name: "board-switched", boardId: 313994, id_event: 48041136}
{name: "board-added", boardId: 313994, id_event: 48041137}

My question is:

Is it possible that 2 INSERT events, executed sequentially will cause the 2nd INSERT to complete before the 1st INSERT?

It is not guaranteed if they are separate requests that are not in the same database transaction. Given the architecture of modern web apps, if they are separate requests I can almost guarantee that they are not in the same transaction and so are in no way dependant on each other.

A connection-pooled application is likely to accept the two requests with different threads, so even if the requests are issued by the client in order, the second request could insert into the database before the first if execution of the first was slower.


Monitor locks over long period of time I am having some issues with one of my tables in a production server.

I know there are many diffrent threads running on this table. I need to put some kind of monitoring tool to see how many locks are happening on this table, and which queries are causing this locks.

I am familiar of slow-query-log.

Are there any tools which can help me?  I have seen monyog, are there any free tools maybe?

Also is the a way to know all the deadlocks that happened overtime, like `show engine innodb status' deadlocks, but over time.

I have found the solution.

There is a variable called innodb_print_all_deadlocks which logs all the deadlocks in to the error log.


Using the correct postgres server I'm not sure what happened but I logged in on friday and did psql -d name to access a db that was perfectly running just fine. Then I log in this monday morning and try to access the same database with psql -d dbname and this is what I got:

psql: FATAL:  no pg_hba.conf entry for host "[local]", user "johndoe", database "db", SSL off

I try sudo su postgres then I got this:

psql: FATAL:  password authentication failed for user "postgres"

After running

 sudo find / -name pg_hba.conf

I get:

/var/lib/pgsql/data/pg_hba.conf
/var/lib/pgsql/9.2/data/pg_hba.conf

Modifying the 1st pg_hba.conf and allowing access to postgres allowed me to login through psql.
With a \l command there showed no databases or users.
This confirms my suspicion that the postgres install I did last Friday did not update the old version I had but installed a new copy of it onto the server.

IS there anyway of logging into the old postgres server or retrieving those databases?
I know for a fact they did not get wiped with the new install of postgres as there are two pga_hba.conf files on the box.

If it did get wiped, is there any way of restoring my Centos 6.5 server to a past date?

EDIT:

Afer stopping the new running server with:

service postgres stop

I type postgres into the terminal and I get:

postgres does not know where to find the server configuration file.
You must specify the --config-file or -D invocation option or set the PGDATA environment variable.

So I point to the correct server config with:

postgres -D /var/lib/pgsql/9.2/data

after switching with su postgres and I get this error:

FATAL:  database files are incompatible with server
DETAIL:  The data directory was initialized by PostgreSQL version 9.2, which is not compatible with this version 8.4.13.

This used to work where it would just give me a warning saying that the db version was 8.4 and server version was 9.2 so some features might not work.

Most likely, you have both Postgresql 8.4 and Postgresql 9.2 installed on this server.

Prior to pg9.0, the RPM packages for postgresql were configured such that you could only have one version installed at a time. Upgrading the RPM replaced the old version with the new.

From pg 9.0, the RPM packages have been configured to allow installation of multiple versions side by side. This is to facilitate in-place upgrade of databases (which requires both versions installed during the conversion). The packages are names postgresql92, postgresql93, etc.

Also note that it would not be possible for both versions to run simultaneously on the same port.

My guess is that your server was rebooted, and when it came up, either they were both configured to start and the 8.4 version started first, or perhaps the 9.2 version is not configured to start at boot at all.

You can confirm this with:

yum list installed "postgres*"

to see that you have both versions installed. You can check which versions are configured to start at boot with:

chkconfig --list | grep postgres

To stop the 8.4 version and start the 9.2 version, so that you can access your data:

service postgresql stop
service postgresql-9.2 start

To make sure that 9.2 starts up next time you boot, and 8.4 does not:

chkconfig postgresql-9.2 on
chkconfig postgresql off

All the above commands executed as root.

If you do not specifically need the 8.4 postgresql installed on your server I would recommend you remove it.


Use of table compression in SQL Server 2014 I am going through articles about table compression in SQL Server. To me it seems quite easy to setup compressed tables and the benefits make the decision no brainer.

I wanted to consult with this community and see if there is any gotcha that I need to know. I am concern that the table compression feature might not be as simple as it looks. Could there any unwanted side effect when I migrate my tables to compressed table?

In my experience there are a couple of things to consider:

Sometimes, perhaps due to some maintenance task which doesn't respect compression settings, indexes and tables have compression turned off so carefully consider any routine tasks you have which rebuild indexes.
Space saving can be significant
When a query against a compressed table/index reads data from disk it was quicker (in my experience) than a query reading an uncompressed table due to the I/O limitations of disk.
When the pages were already in the buffer pool, though, the query was slower than when compared to uncompressed data because of the additional CPU involved in uncompressing the data.

So consider your situation in regards to CPU, RAM and storage capacities. Don't forget to consider columnstore indexes which offer both good compression and query performance.


How to determine actual memory occupied by Query Cache in MySQL? How can we check if the MySQL server is utilizing high memory/RAM ?
How much memory is occupied in total by query cache ? How much memory is fragmented ?
Basically what is occupying xGB off memory by MySQL server ?
Currently running MySQL Server version 5.7.25.

It uses the amount specified by query_cache_size.
If that value is more than about 50M, then it is probably slowing down the server.
The Query cache is going away; it cannot be used in any Clustering setup, and it has been removed from MySQL 8.0.
Every modification to a table triggers a purge of all entries for that table.  Therefore the QC, especially a big one, is costly in busy production servers.
If you must use the QC, follow these tips:

No more than 50M (except on Aurora)
Use SQL_CACHE or SQL_NO_CACHE in each SELECT, based on whether the query is likely to be repeated before it is purged.

(A small percentage of production system do benefit from the QC.)
There are cryptic metrics in SHOW GLOBAL STATUS LIKE 'Qc%'; but I can't explain them.


Why do I have to drop a snapshot in SQL Server 2012 to restore a database? I never had to drop a snapshot with SQL Server 2008, but now when I try to restore in SQL Server 2012, I get the error:

  System.Data.SqlClient.SqlError: The operation cannot be performed on a database with database snapshots or active DBCC replicas.

Why is this necessary in 2012 and not in 2008?

Actually, you did have to drop the snapshot before the restore.  Here's the Books Online post about it:

http://msdn.microsoft.com/en-us/library/ms189940(v=sql.105).aspx

In particular, "The database cannot be dropped, detached, or restored."

You might have been using a restore script or tool that automatically dropped the snapshots for you.  (My own restore database script does that - checks for snapshots first and deletes 'em.)


Associative property inside functional dependancy I have a simple question regarding functional dependancy.. is AB --> C the same as BA --> C? thanks.

Yes.  AB in AB --> C really means A and B functionally determine C.  And, which is the truth functional operator of conjunction, is associative.  Section 4 of the linked article presents the algebraic properties of conjunction.  Therefore A and B is equivalent to B and A.


SQL parse string to get dependent info I have a 2 tables

CREATE TABLE testmain
(dummy_Id numeric(10,0) null,
 memberNo varchar(100)  null)

CREATE TABLE testchild
(dummy_Id numeric(10,0) null,
 xummy_Id numeric(10,0) null)

where xummyId is the dependent meber dummyId

The testmain table has data as of these:

SELECT * FROM testmain

1001 82411/1
1002 82411/2
1003 85545/1
1004 82411/3
1005 85545/2

The left part of memberNo is the code and the last part is the dependent member.

So in the above example 82411/1 is the master and 82411/2,82411/3 are its child members

I need to parse the testmain table and for each entry of type
code/1 and insert in the testchild the dummyIds of the child members

SELECT * FROM testchild

1001 1002
1001 1004
1003 1005

Thanx in advance

patindex can do want you expect:

select a.dummy_Id, b.dummy_Id
from testmain a
join testmain b on left(a.memberNo,patindex('%/%', a.memberNo)-1) = left(b.memberNo,patindex('%/%', b.memberNo)-1)
                    and b.memberNo not like '%/1'
                    and a.dummy_Id <> b.dummy_Id
and a.memberNo like '%/1'


sp_BlitzIndex gives self-contradictory recommendations I've used the Brent's sp_BlitzIndex and received the interesting results. For the first run it advises me to create an index. I've done it and executed it again. This time it has told that the newly created index is too Aggressive.
With this logic DBAs will feel like racing horses going in circles.
Brent, if I am a crazy blind horse, show me the way, please.

Erik here -- sorry, I'm not Brent, though I do sometimes play him on Office Hours.

You're misunderstanding, and misreading the script output.

sp_BlitzIndex gets its missing index requests from the SQL Server DMVs. We don't just make them up, and we don't tell you to create every single one of them. You have to use some judgement when doing so.

It also doesn't tell you an index is 'too aggressive', though it will let you know if indexes are locked aggressively by queries.

This can happen for a multitude of reasons that I can't begin to troubleshoot for you with the information you've provided, nor is this a good forum to do that -- it would require a thorough review of all of your indexes and potentially code for locking hints, etc.

Hope this helps, and best of luck!


Levenshtein distance on MariaDB is there any official or "de-facto" official implementation of Levenshtein distance on MariaDB 10.2 or 10.3?

I found this https://github.com/juanmirocks/Levenshtein-MySQL-UDF, but I want to be sure that there are no alternatives.

There is no single officially approved or universally accepted implementation as of writing this (May 2019). However, as suggested by Rick James in a comment,

  Search for mysql Levenshtein; there are several implementations out there. I don't know if they are all the same. A UDF implementation is likely to be faster than a Stored Procedure.


Unexpected unique constraint violation during update In table foo with two columns (id and seq), I'd like to add +1 to seq for all records with an arbitrary seq > 4738. The plan is to insert a new record at seq=4739 once all the seq > 4738 records are shifted by +1.
This is the table.
CREATE TABLE foo
(
  id uuid NOT NULL,
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
)

CREATE UNIQUE INDEX idx_id
  ON foo
  USING btree
  (id);

CREATE UNIQUE INDEX idx_seq
  ON foo
  USING btree
  (seq);

I try to achieve the +1 shift with the following query. Note, that I use a subquery in an attempt to update the > 4738 records in descending order (i.e. assuming max seq=10000 then the last record is updated first (10000->10001), then second last (seq=10000 doesn't exist at this point, seq=9999 -> seq=10000 (no constraint violation), then 9998 -> 9999, ... to avoid at any one time that a unique constraint violation occurs). However, this assumes sequential execution of the update query which isn't what's seemingly happening.
When running
UPDATE foo SET seq=anon_1.new_seq FROM (
  SELECT foo.id AS id, foo.seq + 1 AS new_seq
  FROM foo
  WHERE foo.seq > 4738 ORDER BY foo.seq DESC
) AS anon_1 WHERE foo.id = anon_1.id

I get the following error.

duplicate key value violates unique constraint "seq_key"
DETAIL:  Key (seq)=(7334) already exists.

Obviously, that's unexpected (as the constraint was satisfied before the UPDATE). Is there anything I could try to resolve the problem (different index type, only constraint, only index)? I noticed that this error very much depends on the number of updated records. If fewer records are updated, then this problem doesn't seem to occur (this may hint to some parallel execution for when there are too many records to update which may interleave and cause some intermediate state in which the constraint/index is not unique any more). Some thoughts and ideas would be very appreciated.

You need to create a deferrable constraint rather than an index.
CREATE TABLE foo
(
  id uuid NOT NULL primary key
  seq integer NOT NULL,
  CONSTRAINT seq_key UNIQUE (seq)
    deferrable initially immediate --<< this
);

By declaring the constraint as deferrable it will be checked at the end of the UPDATE statement (to be more precise: at the end of the transaction) rather than row-by-row.