Is it (always) necessary to backup a certificate with private key? I'm starting a project to encrypt database files. I've created a master key and a certificate the following way:

USE master;
GO
CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Master_K3y';
GO
CREATE CERTIFICATE MY_DB_CERT WITH SUBJECT = 'Some subject here';
GO

I have two options to backup the certificate: with or without a private key. I have no idea where this private key comes from, as I did not provide one. Perhaps it was generated for me when creating the certificate?

In any case, my first backup statements did not provide a private key clause.

USE master;
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'Master_K3y';
GO
BACKUP MASTER KEY TO FILE = 'MASTER_KEY.bak'
ENCRYPTION BY PASSWORD = 'yeK_r3tsaM';
GO
BACKUP CERTIFICATE MY_DB_CERT TO FILE = 'MY_DB_CERT.bak';
GO

I am wondering if that is enough to restore everything in a working state, say when moving to a new server? If not, what is the use-case for backing up a certificate without a (the?) private key?

The code above will only backup the PUBLIC key portion of the certificate. This however by itself, is useless.

The private key is the part needed to decrypt the database - without this the database cannot be decrypted and therefore cannot be accessed.

sqlity Backup Certificate explanation

  To back up a certificate you can use
  the BACKUP CERTIFICATE statement. In its simplest form, it looks like
  this:

 BACKUP CERTIFICATE ACertificate TO FILE ='C:\temp\ACertificate.cert';

  But what about the Private Key? The above statement creates a backup
  of the public portion of the key only. That is however not the
  important part. The important part is the private key of the
  certificate. To create a backup of the private key too, we have to add
  the WITH PRIVATE KEY clause to the BACKUP CERTIFICATE statement:

 BACKUP CERTIFICATE ACertificate    TO FILE
 ='C:\temp\ACertificate.cert'   WITH PRIVATE KEY(
     FILE = 'C:\temp\ACertificate.prvk',
     ENCRYPTION BY PASSWORD = '**********'   );


MySQL Full Text Search Excerpt I am adding MySQL Full Text search to an intranet and one of the things i'd like to do is return an excerpt back in the search result that includes the search term(s) the user searched for.

I initially thought that I could do this with locate() to get the index of the string minus ~10 characters and then substring around 200 characters after but this only works if there is a document that includes these words consecutively. For example:

SELECT  id,created_at, modified_at,
        SUBSTRING(content, (locate('pto request', content))) as excerpt
FROM    search_index
WHERE   MATCH (name,content) AGAINST ('pto request' IN BOOLEAN MODE)

The match clause finds documents that contains the words pto and request that may not be consecutive but the the locate function requires the exact string "pto request" to be somewhere in the document, if it isn't, an excerpt is not generated.

My first thought was to check for 0, if 0 locate didn't match anything so remove a word and try again and keep doing so until a locate finds a match, but this could get really messy.  Is there a better way?

If you want exactly the 2 words, do

... AGAINST('"pto request"' IN BOOLEAN MODE)

If you want some context:

SELECT ...
    SUBSTRING(content,
              GREATEST(1, LOCATE('pto request', content) - 20),
              50)
FROM ...


MariaDB master to master replication auto increment is not sequential I have two master to master MariaDB server replication with the below settings:
server A with 192.168.1.100:
------------------------------
bind-address            = 0.0.0.0
server-id       = 1
log-bin         = "mysql-bin"
binlog-do-db        = wordpress
replicate-do-db     = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 1

and:
Server B with 192.168.1.200:
---------------------------------
bind-address            = 0.0.0.0
server-id               = 2
log-bin                 = "mysql-bin"
binlog-do-db            = wordpress
replicate-do-db         = wordpress
relay-log="mysql-relay-log"
auto-increment-increment = 2
auto-increment-offset = 2

The replication works great but I have problem with auto increments. As you see they are not sequential. For example:
Server A increments: 1, 3, 5, 7, 9 and ...
Server B increments: 2, 4, 6, 8 and ...
The problem occurs when I insert 2 records in server A and 1 record in server B, the total primary key of the table will look like this: ID : 1, 3, 4
As you see number two is missing because I have inserted two records in server A. But I need auto increment to generate sequential numbers since we use them as our invoice numbers.  Gaps can get us in trouble with TAX laws in our country.
I know this is the internal mechanism for MariaDB and MySQL databases in order to avoid duplicate keys. but I just wanted to know if there is an alternative HA solution for sequential index replication.
Thank you

All(?) INSERTs, including IODKU and REPLACE can burn ids, leading to gaps.  This happens with the statement is rolled back.  And for INSERT IGNORE when some UNIQUE triggers "ignore".
Even without multi-master, you can get AUTO_INCREMENT values out of order.  This is because the auto_inc is grabbed when needed, but not replicated until COMMITted.  Replicas will see the ids out of order.
Bottom line:  AUTO_INCREMENT guarantees uniqueness, nothing else.
(Also, what Gordan says.)


How to get the sum with more then one table I have 3 tables as shown in the link sqlfiddle
When I take the sum with two tables I get good result
    sqlfiddle

but when I add 3rd table and it's query I get unexpected result as this

http://sqlfiddle.com/#!2/f9858a/12

How can I resolve this problem pls ?

When you do the aggregation without ingredient, then LEFT JOIN result to ingredient you get this

mysql> SELECT A.*,ig.qty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id;
+----+----------+---------+--------------------+------+
| id | nom      | thisqty | thissum            | qty  |
+----+----------+---------+--------------------+------+
|  1 | Amande   |   19470 | 204.74464999999998 |  600 |
|  1 | Amande   |   19470 | 204.74464999999998 |  200 |
|  2 | Chocolat |    7920 |              62.97 |  100 |
|  2 | Chocolat |    7920 |              62.97 |  200 |
|  4 | Beurre   |     500 |               2.48 |   60 |
|  4 | Beurre   |     500 |               2.48 |   60 |
+----+----------+---------+--------------------+------+
6 rows in set (0.00 sec)

mysql>

Perhaps doing an aggregation after the INNER JOIN would help

mysql> SELECT A.*,SUM(ig.qty) igqty FROM
    -> (SELECT dm.id,dm.nom,
    -> SUM(IF(dm.id=sd.matid, quantite,0)) as thisqty,
    -> SUM(IF(dm.id=sd.matid, sd.montant * (1+sd.tvaval/100),0)) AS thissum
    -> FROM stockdata AS sd
    -> JOIN defmatiere as dm ON dm.id=sd.matid
    -> WHERE dm.type=1 GROUP BY dm.nom ORDER BY dm.id) A
    -> LEFT JOIN ingredient as ig ON ig.matid=A.id
    -> GROUP BY A.id,A.nom,A.thisqty,A.thissum;
+----+----------+---------+--------------------+-------+
| id | nom      | thisqty | thissum            | igqty |
+----+----------+---------+--------------------+-------+
|  1 | Amande   |   19470 | 204.74464999999998 |   800 |
|  2 | Chocolat |    7920 |              62.97 |   300 |
|  4 | Beurre   |     500 |               2.48 |   120 |
+----+----------+---------+--------------------+-------+
3 rows in set (0.00 sec)

mysql>


Using a many-to-many table with a one-to-many relation? I'm building a platform where Suppliers can add products, and shop owners can 'copy' those products to their shops and sell them.

A Supplier can create products, and he can add all sorts of attributes to them, like the size, colors.

When a Shop wants to use one of the Supplier's products, he can choose which attributes he wants to offer. For example if the Supplier has a T-Shirt in offer with 3 sizes. The shop may choose to sell that T-Shirt with only 2 sizes.

So I have a Products table which looks like this. The Supplier adds products in here.

+----+-------------+-------+
| id | description | price |
+----+-------------+-------+
|  1 | T-Shirt     |    10 |
|  2 | Car         |   100 |
+----+-------------+-------+

I have a Product attributes table, Which is also filled by the Supplier

+------------+-------------+------------------------------+
| product_id | description |           options            |
+------------+-------------+------------------------------+
|          1 | size        | ["small", "medium", "large"] |
|          1 | color       | ["white", "black"]           |
+------------+-------------+------------------------------+

Shop Side

When a Shop owner wants a Product. I make a record in the shop_products table, referencing which Shop wants what Product, and adding an optional image, as following

+---------+------------+-----------+
| shop_id | product_id |   image   |
+---------+------------+-----------+
|       1 |          2 | image.jpg |
|       1 |          3 | image.jpg |
+---------+------------+-----------+

  Now the only issue remaining is: How do I make sure the Shop can choose which attributes he wants to take.

Should I create a shop_product_attributes table.. But I can't put a relation on the many-to-many because it doesn't have an id?

Your customers may not offer every possible attribute, right?  E.g., "T-Shirt, Cotton" is available in small, medium, and large, but my store only offers small and medium.  Maybe even black shirts in small and white shirts in small and medium, but not black shirts in medium.  To support that, you need a table which normalizes attributes (one record per possible value, and you need to join your Shops table to that.

So suppliers would populate these tables:

CREATE TABLE Products
(
ProductID   INT NOT NULL PRIMARY KEY,
SupplierID  INT NOT NULL,
CategoryID  INT,
Description VARCHAR(100) NOT NULL,
BasePrice   DECIMAL(18, 2) NOT NULL,
Image   VARBINARY(MAX),
UNIQUE (SupplierID, Description)
)

CREATE TABLE ProductAttributes
(
AttributeID INT NOT NULL PRIMARY KEY,
ProductID   INT NOT NULL,
Dimension   VARCHAR(100) NOT NULL,
Value   VARCHAR(100) NOT NULL,
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
Image   VARBINARY(MAX),
UNIQUE (ProductID, Dimension, Value)
)

Shops would then populate two tables to show what they offer, one for products and one for each intersection of attributes.  It'll be important that the UI allow store owners to quickly add all attributes; you don't want them ticking a box for every record.

CREATE TABLE ProductsOffered
(
ShopID  INT NOT NULL,
ProductID   INT NOT NULL,
PRIMARY KEY (ShopID, ProductID),
Description VARCHAR(100) NOT NULL,
Image   VARBINARY(MAX),
BasePrice   DECIMAL(18, 2) NOT NULL
)

CREATE TABLE AttributesBuckets
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
AttributeID INT NOT NULL,  -- Implies ProductID
PRIMARY KEY (ShopID, BucketID, AttributeID),
Image   VARBINARY(MAX),
PriceModifier   DECIMAL(18, 2) NOT NULL DEFAULT 0.00,
)

So if a store decides to sell T-shirts in "black" and "small" there will be a record in AttributesBuckets for ShopID = MyShop, ProductID = T-Shirt, BucketID = 1, AttributeID = color/black, and another record with AttributeID = size/small.  That's one "bucket;" one combination of attributes which fully describes a product that can be sold.  If they also sell small white shirts, that's one more bucket, defined by two more records.  If you add a "stitching" option, each bucket will have three records, for color, size, and stitching.

As a bonus, you can now track inventory:

CREATE TABLE Inventory
(
ShopID  INT NOT NULL,
BucketID    INT NOT NULL,
PRIMARY KEY (ShopID, BucketID),
Quantity    INT NOT NULL DEFAULT 0
)


PostgreSQL replication not working after base backup Last week we had an incident with our master PostgreSQL server instance which got totally thrashed so, we had to switch to our slave as our one and only DB instance. After the initial chaos was controlled, we tried to create a new slave replica from scratch, according to instructions left by our DB provider who actually set up the first master-slave replica, but just yesterday I realized that the slave wasn't really replicating at all and, only has the contents of the initial base backup, so we have no redundancy right now in case of a new disaster!

On recovery.conf we have:

standby_mode = on
primary_conninfo = 'host=10.1.1.65 port=5432 user=replicador password=XXXXXXXX'

In /var/lib/postgresql.log (master) there are several messages like this:

2017-03-17 12:21:57 UTC [1969-1] replicador@[unknown] ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

And in the replica's log file, many messages like:

2017-03-22 13:22:32 UTC [2827-1] LOG:  started streaming WAL from primary at B28/3A000000 on timeline 1
2017-03-22 13:22:32 UTC [2827-2] FATAL:  could not receive data from WAL stream: ERROR:  requested WAL segment 0000000100000B280000003A has already been removed

Seems like the process wasn't fast enough to keep the master's pace. What parameters should I check/raise? Can I actually re-sync, or am I lost and once we'll have to do it all over again?

What could be missing? What additional info should I provide?

Environment:

select version()
PostgreSQL 9.4.9 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit

lsb_release -a
Distributor ID: Debian
Description:    Debian GNU/Linux 8.6 (jessie)
Release:    8.6
Codename:   jessie

uname -a
Linux ip-10-1-0-139 3.16.0-4-amd64 #1 SMP Debian 3.16.36-1+deb8u2 (2016-10-19) x86_64 GNU/Linux

Your base backup looks old as current master (earlier slave) must be on timeline greater than 1 after getting promoted as master but your slave is on still timeline 1.
You can either create a new slave from scratch after taking a new base backup from new master.
Or you can try timeline switch in the current non replicating slave (not sure about it) refer it here http://paquier.xyz/postgresql-2/postgres-9-3-feature-highlight-timeline-switch-of-slave-node-without-archives/


MySQL upgrade to 5.6.4 from 5.6.33 on Ubuntu I just upgraded our MySQL server from 5.5.53 by doing the following:

sudo apt-get purge mysql-server-5.5 mysql-client-5.5
sudo apt-get install mysql-server-5.6

Which got me to version 5.6.33

However, I'm looking to get to at least 5.6.4 where it appears the DATETIME datatype takes up 37.5% less space (per this article).

I can't seem to figure out / find how to upgrade from 5.6.33 to 5.6.4.  Can anyone help?

FYI - I'm running Ubuntu 14.04.  Let me know if you need any other relevant data.

Thanks in advance.

I think you are wrong.

MySQL 5.6.33 is bigger than 5.6.4

For more information look at MySQL 5.6 release note:

https://dev.mysql.com/doc/relnotes/mysql/5.6/en/

MySQL 5.6.4 released at 2011-12-20 So MySQL 5.6.33 has already DateTime feature.


Genghis admin ui not working with mongodb 3.0.1 wired tiger We recently updated to mongodb 3.0.1 using the wired tiger storage engine, and have been using the genghis php admin ui through the browser.  However, genghis is not completely working now, specifically it won't show collections available in the database.  It seems the php call $db->listCollections() returns 0 collections.  We are using the latest php mongo driver(1.6.5), and php 5.5.9.

Genghis will show collections for databases if we switch back to the mmap storage engine, so it looks like it might be something going on with how the php driver and listCollections() call works with the wired tiger storage engine?  There are no errors in the apache or mongodb logs when I try browsing the databases with genghis.

I tried searching online and couldn't find any related problems, has anyone had problems with wired tiger + php like this?

Updating to the latest mongo php driver, 1.6.6 fixed it.

sudo pecl upgrade mongo


MongoDB Replication Set Configuration Issue on CentOS 7 My mongodb instance will not run as standalone and I haven't been able to discover why.  Its probably something simple.  Please point me in the right direction.

Linux myserver 3.10.0-514.el7.x86_64 #1 SMP Tue Nov 22 16:42:41 UTC 2016 x86_64 x86_64 x86_64 GNU/Linux

 mongo -version
MongoDB shell version v4.0.6

running the command:

    mongo --host 10.0.0.7/admin --port 27017 -u user -p mypassword

returns:

MongoDB shell version v3.4.13
connecting to: mongodb://admin:27017/?replicaSet=10.0.0.7
2019-03-06T00:54:32.412-0500 I NETWORK  [thread1] Starting new replica set monitor for 10.0.0.7/admin:27017
2019-03-06T00:54:32.414-0500 I NETWORK  [thread1] getaddrinfo("admin") failed: Name or service not known
2019-03-06T00:54:32.414-0500 W NETWORK  [thread1] No primary detected for set 10.0.0.7
2019-03-06T00:54:32.414-0500 I NETWORK  [thread1] All nodes for set 10.0.0.7 are down. This has happened for 1 checks in a row.
2019-03-06T00:54:32.914-0500 I NETWORK  [thread1] getaddrinfo("admin") failed: Name or service not known

So I logged into mongo shell on the host and ran:

use admin
 show users

{
        "_id" : "admin.myname",
        "user" : "myname",
        "db" : "admin",
        "roles" : [
                {
                        "role" : "readWriteAnyDatabase",
                        "db" : "admin"
                },
                {
                        "role" : "root",
                        "db" : "admin"
                },
                {
                        "role" : "userAdminAnyDatabase",
                        "db" : "admin"
                }
        ],
        "mechanisms" : [
                "SCRAM-SHA-1",
                "SCRAM-SHA-256"
        ] }

and

  rs.status()

{
        "ok" : 0,
        "errmsg" : "not running with --replSet",
        "code" : 76,
        "codeName" : "NoReplicationEnabled"
}

Mongd.conf

# mongod.conf

# for documentation of all options, see:
#   http://docs.mongodb.org/manual/reference/configuration-options/

# where to write logging data.
systemLog:
  destination: file
  logAppend: true
  path: /var/log/mongodb/mongod.log

# Where and how to store data.
storage:
#  dbPath: /var/lib/mongo
  dbPath: /data/mongo
  journal:
    enabled: true
#  engine:
#  mmapv1:
#  wiredTiger:

# how the process runs
processManagement:
  fork: true  # fork and run in background
  pidFilePath: /var/run/mongodb/mongod.pid  # location of pidfile
  timeZoneInfo: /usr/share/zoneinfo

# network interfaces
net:
  port: 27017
#  bindIp: 127.0.0.1  # Enter 0.0.0.0,:: to bind to all IPv4 and IPv6 addresses or, alternatively, use the net.bindIpAll setting.
  bindIpAll: true

security:
  authorization: "enabled"

#operationProfiling:

#replication:

#sharding:

## Enterprise-Only Options

#auditLog:

#snmp:

mongo --host 10.0.0.7/admin --port 27017 -u user -p mypassword

The above command takes 10.0.0.7 as replica set name and admin as the hostname. That is the reason you are getting the error.

Use any of the following commands to connect to your MongoDB database

mongo 10.0.0.7/admin --port 27017 -u user -p mypassword

mongo admin --host 10.0.0.7 --port 27017 -u user -p mypassword


InnoDB page_cleaner error message: The settings might not be optimal My server is in idle state but I often get this error:

Note: InnoDB: page_cleaner: 1000ms intended loop took 35165ms.
The settings might not be optimal.
flushed=0 and evicted=0, during the time

How can I fix this issue?

mysql> set global innodb_lru_scan_depth=256;


Configuring Reporting Services - ExecutionLogDaysKept Is it possible to set Reporting Services options when Reporting Services is not installed on a server?  Is this how a SQL Server instance used by SharePoint works?

I'm a new DBA (very junior...), and I'm working in an environment where the client uses SQL Server as a back-end to CRM, SharePoint, BizTalk, SCCM and SCOM.

I recently saw a user request to increase the retention period for Reporting Services logs on two servers. The RFC said to run Update ConfigurationInfo SET Value='275' Where NAME='ExecutionLogDaysKept' on DB ReportServer_BI on server [server_name] and DB ReportServer_RS01_Content server [server_name].

I had a look at both the servers but neither one had Reporting Services installed. At least there was no instance listed in SQL Server Management Studio.

When I ran the query SELECT * FROM ConfigurationInfo the results table returned had a row for ExecutionLogDaysKept.  SQL Server Management Studio was connected to the Database Engine when I ran the query.

I apologise if I've asked the question in the wrong forum.  Is there a more appropriate forum or website I should go to?

When you install SSRS 2012 in SharePoint Integrated Mode it does not install an instance of SSRS on the server like it would if you installed it normally.  So you have to configure it via the SharePoint interface. This is the reason you did not see Reporting Services running on the server.


Where can I see the exact row count of a table in pgAdmin III? Where can I see the exact row count of a table in pgAdmin III?

I know where to find the approximate row count:

I am also aware I can SELECT COUNT(*) FROM my_table. But I want to know whether the exact row count can be found somewhere in pgAdmin III's GUI, in the same way we can query the top 100 rows.

Two lines below the estimate you highlighted we can see:

Rows (counted)  not counted

Right-click and choose Count from the context-menu to get the current count right there. Be aware that the count is only accurate in the moment it is taken.
pgAdmin does not count rows in big tables (because that can be expensive) unless it's told to do so. There is a setting in the Options:

Count rows if estimated less than [2000]

Replace the default 2000 if you want a different limit. Details in the manual here:

http://www.pgadmin.org/docs/1.20/options-browser.html


Amount of data per node in Neo4j I need to store substantial amounts of data per node in Neo4j. The data is Unicode chunks of text. Actually not every node will have big chunks, but many of them will.

I waded through the documentation but didn't find any mention on the Node size – the amount of data a single node can contain.

Does anyone have any idea?

Ultimately, it depends on the architecture that your machine has.

(background) Nodes can solely store data in their properties. Their properties are stored using a key-value store. (per here)

The value in each property is limited to Java primitives (ints, floats, etc.), strings, and arrays of primitives/strings.

Therefore, the maximum amount of data a particular property can hold would be limited to the maximum size for a string or the maximum size for an array of strings (that's per node).  This limit (for 32-bit machines) is 4GB.  (Note that this may be limited to 2-3 GB.)

(Also, having said this, there was a bug previously that limited string size to 1 MB.  I expect that this is resolved.)

Of course, this raises the question of whether multiple properties could store more than 4GB per node.  Since the properties list is essentially a key-value store, it would expect that the maximum size would be limited by disk space and key selection.  I can't find anything to support or deny this, however.

That doesn't definitively answer your question, but from what I understand you should be able to store large amounts of data per node (up to disk space capacity).


Type of an id field that references another id field I'm on postgresql. I have a table user (id serial not null) and another table client(id) i need to create a :
foreign key client(id) references user(id).

What the client(id) type should be? Int or serial? Thanks for help.

It should be int. The referencing and the refernced columns have to be of the same datatype but it doesn't make sense for a serial to reference another serial because serials are just int with auto incrementing values provided by a sequence.

And when you will be inserting in the second, referencing table (client), you'll want to use the values already inserted in the 1st, referenced table (user). You don't want automatically provided values as these may not match the existing values you already have in the 1st table.

The Postgres docs about numeric datatypes explain:

  The data types smallserial, serial and bigserial are not true types, but merely a notational convenience for creating unique identifier columns (similar to the AUTO_INCREMENT property supported by some other databases). In the current implementation, specifying:

CREATE TABLE tablename (
    colname SERIAL
);

  is equivalent to specifying:

CREATE SEQUENCE tablename_colname_seq;
CREATE TABLE tablename (
    colname integer NOT NULL DEFAULT nextval('tablename_colname_seq')
);
ALTER SEQUENCE tablename_colname_seq OWNED BY tablename.colname;


INSERT INTO with subquery that might return 0 results I have a status table that I am using to sync other long running processes, and my following query/system is functioning, but also filling up my error logs:
CREATE TABLE status(subject_id UUID PRIMARY KEY REFERENCES users(subject_id)
ON DELETE CASCADE, status1 varchar, status2 varchar)

CREATE TABLE users(subject_id UUID PRIMARY KEY, start_date BIGINT)

problem query:
INSERT INTO status
(status1, subject_id) VALUES ('processing',
(SELECT u.subject_id FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
        WHERE (status.status1 IS NULL or status.status1 = 'ready')
        and usrs.start_date IS NOT NULL LIMIT 1))
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

The ON CONFLICT clause correctly handles the case where the subject_id already exists in status, but no the case where my subquery returns 0 results. In that case I don't want anything to happen, but I would also prefer not to get errors in my log when that happens.
I need to handle cases where:

Claim a subject_id that has no entry in status. This works well.
Claim a subject_id that has an entry in status for another status column. The current ON CONFLICT clause handles this.
Handle the case where there are no more users available. The current query tries to insert a NULL subject_id in this case.

This should be a single query because there are several worker processes that are using the status1, status2, ... columns to synchronize their work and two could both start a long running process on the same subject if the select and insert are separate queries.

INSERT INTO status (status1, subject_id)
SELECT 'processing', u.subject_id
    FROM users AS u
    LEFT OUTER JOIN status ON (u.subject_id = status.subject_id)
    WHERE (status.status1 IS NULL or status.status1 = 'ready')
      and usrs.start_date IS NOT NULL LIMIT 1
ON CONFLICT (subject_id) DO update SET status1 = 'processing'

PS. LIMIT without ORDER BY is a lottery...


I have one table for users, one for a collection of games. My users can own one or many games. How do I structure that in my database? Since I can't store lists, should I add a column to my table called owned games and then just keep a long string like "game1, game2..."?

Or should I add another table called something like ownership and every row represents the fact that a user is linked to a game...

  ownership

  - gameID
  - owner
  - dateAdded

You first need to decide: can a game be owned by more than one person? (consider also if the answer might possibly be yes in the future).

If yes) Then you have a many-to-many relationship and as you described the only way to do this is to use an intermediary table with atleast these three columns:

ID
Game_ID (foreign key referencing the primary key in the Game table)
Owner_ID (foreign key referencing the primary key in the Owners table)

The naming convention for an intermediary table is normally a combination of the two tables you are connecting. So in your case it would probably be 'Game_Owner'.

If no) You have a one-to-many relationship and no intermediary table is required. In the Game table include a column called 'Owner_ID' which is a foreign key.


Database Size Increase After Index Rebuild.How to get back down? I executed an Index Rebuild for a large table in database because it was 85% fragmented. After the Index Rebuild finished the database file size has grown from 19 GB to 27 GB.

Rebuild option:

(PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = ON, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

My Question :  Is there any solution to decrease database file size without increase index fragmentation?

User Biju jose has challenged the frame of your question in a comment, and I think it's probably the most helpful answer that you could get:

  I believe you should not shrink the database to recover the space since once you do a rebuild after that will cause it to grow again. The point here is your database should also have space to accommodate your maintenance task also!

If you really, desperately need these 8 GBs of space back, you could use DBCC SHRINKFILE on the data file to shrink it down some.  Note, however, that this will likely cause some fragmentation.  This unhelpful cycle (fragmentation -> rebuild -> empty space -> shrink -> fragmentation -> repeat) is described in detail here:

What’s So Bad About Shrinking Databases with DBCC SHRINKDATABASE?

If you want a nice collection of additional reasons not to shrink files, check this out (and all of the articles it links to): Stop Shrinking Your Database Files. Seriously. Now.

Randolph West helpfully points out in the comments that if you do need to reduce fragmentation without growing the file so much, you could do a REORGANIZE of the index.  This requires less working space than the index, but also can only eliminate certain types of fragmentation (it rearranges and compacts the leaf pages of an index, but doesn't drop and recreate the whole thing like a REBUILD does).


how to professionally normalize my database - screenshots included Hello database administrators.

I got some excel spreadsheet with data of "my customers" and I'd like to create professionally designed database out of it.
Can anyone tell me how this data should be normalized, what tables should be created and how should they be related with each other, so that my database would meet corporate industry standards?

I know I'm just a beginner in database creation but I've already learned about concept of database normalization, I'm eager to learn and I'll be grateful for any help and directions.

Here is the first chunk of my data imported to access table. As you can see I've already changed the job's names into numbers and I have separate table where these numbers are assigned into the jobs names.
Here it is in jobs table.

Jobs table with jobs names.

Here is the rest of the data - please help me organize it.

How about this *BESTMovie columns - do they need normalization? Some movies repeat 10 times and others don't repeat at all.

And one more question to this "*Profession" and "*BESTMovie" columns.

If I had some person, lets say clint eastwood or robert de niro, that I'd like to assign not only 3 but 5 movies, how to do this without adding another "*BESTMovie" column, where most of the fields would be blank for some other people who didn't have more than 3 movies?

I've heard about some rank column or rank table but I have no idea how should it look like.

The rest of my data looks like this:

Here is an "EyesColor" column and "Adjective" I use to describe a person. Can these columns be normalized and should the be normalized? The eyes color repeats many times of course, and adjectives repeat less frequently but they do repeat.

I hope the question is well put and I'm looking forward for answers from you guys.

You have

a customers table.
a jobs table.

You normalize the 3 Profession columns by moving that information into a linking table which contains

Customer ID
Job ID
something to represent "1st","2nd","3rd" Profession
Constraints to ensure the data is valid

To get the data back in the original form, use a VIEW.

Example:

create table customer_jobs (
    customer_id int not null
    ,job_id int not null
    ,order_by number(1) not null -- defines "1stProfession","2ndProfession","3rdProfession"

    -- ensures that each customer has only one "1st Profession", not two "1st Professions
    ,constraint customer_jobs_pk primary key (customer_id, order_by)
    -- ensures each customer claims a job once and only once
    ,constraint customer_jobs_uq1 unique (customer_id, job_id)
    -- ensures that the customer is a known customer
    ,constraint customer_jobs_fk1 foreign key (customer_id) references customers (id)
    -- ensures that the job is a known job
    ,constraint customer_jobs_fk2 foreign key (job_id) references jobs (id)
    -- Limits to only 1st, 2nd, and 3rd Professions
    ,constraint customer_jobs_ck1 check (order_by between 1 and 3)
);

Repeat this method with the *BestMovie columns


How can we achive the replica of databases for SQL Server? I have been working in VB6 to VB.NET migration project.In VB6 project they have individual databases (access db .mdb) for every user now we made that to centralized database using SQL Server.

In the old application we have a MasterDB.mdb which is in sync with the local.mdb. Some times we need to sync with both the users of local.mdb. After a particular interval of time we need to sync with the MasterDB.mdb.

We need to do this in SQL Server. So, I have a done more analysis related to SQL Server. I read a MSDN forums and many articles related to replication in SQL Server (Related Scenario). I think that merge replication matches to my requirements.

Can anyone suggest whether I have chosen the correct replication? Do we have any environment changes to set up this replication?

To reinforce what Jon says - a single DB to which all clients connect all the time, with the schema designed accordingly, will be a much better solution.  You only need replication in a few circumstances.  One is if a client machine must be able to work while disconnected.  Another will be if there are huge latency issues between your various sites.  Replication, especially two-way merge replication, can be a huge pain - during upgrades, for example.


execute [SSISDB].[catalog].[create_execution] with 'execute as" We are testing a 3rd party tool to schedule/execute jobs in our environment so trying to test launching SSIS packages within the integration service catalog.   I have found examples of the code to run the procedures to execute the SSIS jobs which works fine but I don’t want to grant the 3rd party’s user account full access so like to wrap a procedure around the SSISDB.catalog procedures necessary to run the SSIS job using ‘execute as’.

On a dev server I created the test proc within the SSISDB DB (will change this to a separate db later, just trying make this work) using ‘execute as’ referencing a sql account but got error saying it must run as an AD account.  I then tried using a full AD admin account on the box as the ‘execute as’ but get errors like:  Msg 27146, Level 16, State 1, Procedure create_execution, Line 180
Cannot access the package or the package does not exist. Verify that the package exists and that the user has permissions to it.

Hoping to find out if someone has done something similar and the steps they have taken as I’m concerned I’ll over complicate this or grant more permissions to accounts than necessary.

I appreciate any help or being pointed in the right direction!

Thanks,
Jeff

figured out the issue. didn't know the ssisdb procedures already were doing 'execute as' and didn't know you needed to use an AD account for impersonation to work.


Postgres COPY with on conflict ignore - possible? I want to add on conflict ignore to the Postgres copy command.
I know I can copy the data to a table without unique index /primary key and then use insert with the on conflict syntax.
But I wanted to know if this is possible directly from COPY?

No.  Maybe in some future version.  Now about the best you can do is use file_fdw to map the file in as a foreign table, and then INSERT INTO...SELECT...ON CONFLICT DO NOTHING


SQL Server 2017 Enterprise - how to fast update entire columns I need to update all values in one column in a table. The table is large - nearly 420 000 000 rows.  Is there any clever way to conduct this operation quickly and without causing a lot of transaction log growth?
I know I can do that in batches, but maybe some sort of metadata trick is possible? Some clever way to use ALTER TABLE SWITCH maybe?
TIA

maybe some sort of metadata trick is possible?

Adding a new column with a default can be a metadata-only operation.
Dropping a column can be a metadata-only operation.
Renaming a column can be a metadata-only operation.

I'm not certian that all of these always are metadata-only for every type of table, but  try something like:
  use AdventureWorksDW2017
  go
  drop table if exists DimCustomer2
  go
  select c.*
  into DimCustomer2
  from DimCustomer c
  cross join (select top 100 * from sys.messages) d
  go
  set statistics io on
  go
  begin transaction
  alter table DimCustomer2 add LastName2 nvarchar(200) not null default 'HIDDEN'
  alter table DimCustomer2 drop column LastName
  exec sp_rename 'Dbo.DimCustomer2.LastName2', 'LastName', 'COLUMN'
  commit
  go
  set statistics io off
  go
  select top 100 *
  from DimCustomer2


Connect to SQL Server without specifying port I am trying to use what appears to be a rather badly written application which connects to an SQL Server database which I specify.

I cannot seem to connect to the SQL Server instance I wish to use without specifying the port with a comma e.g. DBServer\DBInstance,<port_number>

Now, the problem that this causes, and why I think the application is badly written, is that for some reason the application then thinks that the port number is the name of the database, and the server\instance name is DBServer\DBInstance. I believe it is not expecting to have to deal with a port number like this.

Now, I have used SQL Server instances before which did not require the port number to be specified. I know this must be some setting under the TCP/IP settings in SQL Server Configuration manager, and I have tried setting the port to the default 1433 as well as turning on dynamic ports, but neither of these have worked.

I am still fairly new to SQL Server, if anybody could point me in the right direction it would be greatly appreciated.

Edit

Having solved the problem as per Ste Bov's answer, I also now know why the application could not handle the port being specified. Internally, the application was storing its data in a temporary csv file and didn't bother to check input for commas or enclose it in quotes, so when it came to the port in the csv it thought it was the next field which was supposed to be the Database Name.

So yes, a badly written application indeed!

If you have named instances and don't want to declare your ports then you need to ensure that SQL Server Browser is running

By default this runs through port UDP 1434


From RAC to Standby RAC With Active Dataguard I have oracle  database 11.2.0.3 with RAC feature  , with 2 node on oracle linux 6.4 :

ol6-112-rac1 and ol6-112-rac2

I want to create active data guard with RAC feature from this database , these active dataguard with rac option has 2 node :

ol6-rac1-guard,ol6-rac2-guard

I want to use this document for creation active data guard from rac database to rac database :

http://www.oracledba.org/11gR2/dr/11gR2_dataguard_RAC_to_RAC.html#Create_Physical_Standby

I do the steps in this document , all of things is ok but in step " Start
the Managed Recovery Process "  after i want to switch log in primary ,
switch log is not occurred on standby database .
After " tail of alert log " on standby database and  executing " Switch log " on primary , this error is occurred :

tail -f  /ora01/app/oracle/diag/rdbms/racguard/racguard1/trace/alert_racguard1.log
Wed Feb 17 04:37:57 2016
Media Recovery Waiting for thread 1 sequence 76
Error 12154 received logging on to the standby
FAL[client, USER]: Error 12154 connecting to rac for fetching gap sequence

This is my mistake , can any one help me ?

In your tnsnames.ora on the standby side the needed tns alias for 'rac' is missing. In a data guard configuration it is extremely important that all members can resolve all other members.


Get last full backup and transaction log backup for each database How to write script or a query that will display the last full backup  and the last log backup for each database on each of the production servers using the system table "backupset" and related backup tables.

Something like this should get you the output that you're looking for.  The below query pulls the most recent backup of type full, differential, or log backup for each database.

;with backup_cte as
(
    select
        database_name,
        backup_type =
            case type
                when 'D' then 'database'
                when 'L' then 'log'
                when 'I' then 'differential'
                else 'other'
            end,
        backup_finish_date,
        rownum =
            row_number() over
            (
                partition by database_name, type
                order by backup_finish_date desc
            )
    from msdb.dbo.backupset
)
select
    database_name,
    backup_type,
    backup_finish_date
from backup_cte
where rownum = 1
order by database_name;


Create multiple variable columns from single column value The following code gets the number of days for a specific month set in @month

DECLARE @month tinyint
, @Days varchar(max)

SET @month = 1 -January

;WITH DaysInMonth
AS
(
    SELECT DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime)))) D
    UNION ALL
    SELECT DATEADD(day, 1, D)
    FROM DaysInMonth
    WHERE D < DATEADD(day, -1, DATEADD(month, 1, DATEADD(month, @month, DATEADD(month, -MONTH(GETDATE()), DATEADD(day, -DAY(GETDATE()) + 1, CAST(FLOOR(CAST(GETDATE() AS float)) AS datetime))))))
)
SELECT
   @Days = substring((SELECT ( ', ' + CONVERT(varchar(2), D, 104))
                           FROM DaysInMonth
                           FOR XML PATH( '' )
                          ), 3, 1000 )  FROM DaysInMonth

SELECT @Days

I am getting the following output in a single column:

01, 02, 03, 04, 05, 06, 07, 08, 09, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 20, 21, 22, 23, 24, 25, 26, 27, 28, 29, 30, 31

How can I get these values in different columns?.

The main aim for get this query is to join or union with another query that contains records marked with 1 or 0 set to a specific date. The final query will output a result similar to the one a have below but, for the full month sent in the parameter.

July    28       29      30         31          01      02          03
NULL    Monday  Tuesday Wednesday   Thursday    Friday  Saturday    Sunday
Jhon    1        0      1           0           1       0           0

Any other solution to get around similar output is welcome.Thank you

For the modifications to the question, here is a modified query:

declare @month datetime = '20140708';

set datefirst 1;  -- align to Monday as first day of week

-- test data as per OP:
create table #t (
     Date       Date not null primary key
    ,IsActive   int  not null
);
insert #t(Date,IsActive)
values ('20140728', 1)
      ,('20140729', 0)
      ,('20140730', 1)
      ,('20140731', 0)
      ,('20140801', 1)
      ,('20140802', 0)
      ,('20140803', 0);

with  -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
        (1),(1),(1),(1),(1),
        (1),(1),(1),(1),(1)
    )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
    select 0 as N union all
    select ROW_NUMBER() over (order by N) as N
    from E4
)

,calendar as (
    select top (datepart(dd,  eomonth(@month)))
         cast(dateadd(dd,tally.N,@month) as date) as Date
        ,N
    from tally
    where N > 0
)

select
     WeekNo
    ,max(isnull(Mon,0)) as Mon
    ,max(isnull(Tue,0)) as Tue
    ,max(isnull(Wed,0)) as Wed
    ,max(isnull(Thu,0)) as Thu
    ,max(isnull(Fri,0)) as Fri
    ,max(isnull(Sat,0)) as Sat
    ,max(isnull(Sun,0)) as Sun
from (
    select
         calendar.Date
        ,cast(datename(dw,calendar.Date) as char(3)) as WeekDay
        ,datepart(ww,calendar.Date) as WeekNo
        ,data.IsActive
    from calendar
    left join #t data
       on data.Date     = calendar.Date
) t
pivot ( max(IsActive) for WeekDay in (Mon,Tue,Wed,Thu,Fri,Sat,Sun)) pvt
group by WeekNo
;
go

yielding this:

WeekNo      Mon         Tue         Wed         Thu         Fri         Sat         Sun
----------- ----------- ----------- ----------- ----------- ----------- ----------- -----------
28          0           0           0           0           0           0           0
29          0           0           0           0           0           0           0
30          0           0           0           0           0           0           0
31          1           0           1           0           1           0           0
32          0           0           0           0           0           0           0

fro the original question:
Not quite sure what this achieves, but the following works nicely:

declare @month datetime = '20140710';

with  -- standard CTE-tally with values from 0 to 10,000
E1(N) as ( select 1 from (values
        (1),(1),(1),(1),(1),
        (1),(1),(1),(1),(1)
    )E1(N)
),
E2(N) as ( select 1 from E1 a cross join E1 b ),
E4(N) as ( select 1 from E2 a cross join E2 b ),
tally as (
    select 0 as N union all
    select ROW_NUMBER() over (order by N) as N
    from E4
)

select  -- the query requested by OP, using the CTE tally above
    [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
    [31]
from (
    select top (datepart(dd, eomonth(@month)))
        right('0' + cast(N as varchar(2)),2) as X,
        N
    from tally
    where N > 0
) t pivot (max(X) for N in (
    [01],[02],[03],[04],[05],[06],[07],[08],[09],[10],
    [11],[12],[13],[14],[15],[16],[17],[18],[19],[20],
    [21],[22],[23],[24],[25],[26],[27],[28],[29],[30],
    [31] )
) pvt
;

to yield this:

01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31

(1 row(s) affected)

or with @month int = '20140210' -- February we get:

01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   29   30   31
---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ---- ----
01   02   03   04   05   06   07   08   09   10   11   12   13   14   15   16   17   18   19   20   21   22   23   24   25   26   27   28   NULL NULL NULL

(1 row(s) affected)

No RDBMS tagged, so used SQL Server syntax above.


SQL Server account delegation setup I am try to run bulk insert command on SQL Server 2008 but I am having issues with the security.
after researching via internet, the problem has something to do with account delegation and impersonation.

However all solutions seems to point to an Active Directory setup which my setup is not.

My setup is SQL Server 2008 Express on Windows Server 2008 Standard configured as a workgroup.

Despite a SQL Server user account, assigned all the user mappings and server roles and ensuring security set to SQL, I am getting this error:

  Msg 4861, Level 16, State 1, Line 1
  Cannot bulk load because the file "\server_name\file_name.txt" could not be opened. Operating system error code 5 (Access is denied.).

So, how does one setup SQL Server account delegation and impersonation on a workgroup environment?

Thanks in advance

Based on the information you provided in the SQL script, the NT account which the SQL Server service is running under doesn't have access to the folder which the file is sitting in.  You'll need to see which account the SQL Service is running under, then grant that account rights to the folder.


SQL Server 2012 install hangs during the RunRemoteDiscoveryAction I've tried multiple times to install SQL Server 2012 on a 2-node cluster.  I've tried multiple different media.  Each time it hangs shortly after the initial validation check.

The detail log for the install has this for the last entry before it hangs...

(01) 2014-11-17 23:55:49 Slp: ----------------------------------------------------------------------
(01) 2014-11-17 23:55:49 Slp: Running Action: RunRemoteDiscoveryAction
(01) 2014-11-17 23:55:49 Slp: Running discovery on local machine
(01) 2014-11-17 23:55:50 Slp: Discovery on local machine is complete
(01) 2014-11-17 23:55:50 Slp: Running discovery on remote machine: NODE2

The same thing happens on NODE1 when it tries to install.

The only other google article I could find on this was an unanswered msdn article with almost the same description of my problem.
https://social.msdn.microsoft.com/Forums/en-US/18018405-522f-4793-b5fd-a84835f15234/install-sql-server-2012-sp1-on-a-windows-server-2012-r2-failover-cluster-hangs-at-running?forum=sqlsetupandupgrade

I can ping each node from the other.  The Cluster doesn't appear to be displaying any errors either.  All nodes and networks are showing up and running in the cluster manager.

How can I get the installer to skip this task? What is the fix for this?

The install is no longer hanging.  After opening a call with Microsoft we discovered the problem was trying to open the registry of the other node on the cluster.  Additionally we were unable to use admin shares (i.e. \NODE1\C$).  Once we enabled the Admin Shares, I was able to perform the installation and get past the point where it was hanging.

Enable Admin Shares (i.e. \NODE1\C$ should be accessible from NODE2)


Restoring a differential backup in SQL server 2005 I have created a full back up of my database  by executing the line below in my application:

Dim Query As String = "backup database Ari to disk='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'"

The *.bak file created successfully.

Then I restored it on another Database called test and it was ok.
Then I created a Differential backup by means of the following code:

Dim Query As String = "BACKUP DATABASE Ari TO DISK='G:\Documents and Settings\i\Desktop\BKAR\" & MyTime.getFullCurrentDate.Replace("/", "") & ".Bak'" & "WITH DIFFERENTIAL"

and it created a *.bak file ,but when I try to restore it on the database I mentioned as test ,I receive the following error:

Please help me solve this problem.

You haven't shown us your RESTORE DATABASE command, but you appear to have omitted a recovery option, or specified WITH RECOVERY (which is the default). In either case, if you've done that, you cannot apply any further backups.

If you want to apply a series of backups, you need to specify WITH NORECOVERY on all (or all but the last) of the RESTOREs.


Explicit transaction left open after try/catch I recently wrote a T-SQL script to perform some updates and inserts on 3 different tables. I wanted it done in a single transaction, so I read up on the Microsoft documentation on how to use explicit transactions with try/catch.

According to the documentation, it can be done like this:

BEGIN TRANSACTION;

BEGIN TRY
    -- Generate a constraint violation error.
    DELETE FROM Production.Product
    WHERE ProductID = 980;
END TRY

BEGIN CATCH
    SELECT
        ERROR_NUMBER() AS ErrorNumber
        ,ERROR_SEVERITY() AS ErrorSeverity
        ,ERROR_STATE() AS ErrorState
        ,ERROR_PROCEDURE() AS ErrorProcedure
        ,ERROR_LINE() AS ErrorLine
        ,ERROR_MESSAGE() AS ErrorMessage;

    IF @@TRANCOUNT > 0
        ROLLBACK TRANSACTION;
END CATCH;

IF @@TRANCOUNT > 0
    COMMIT TRANSACTION;
GO

So I implemented this pattern and put all my updates and inserts into the TRY clause. Problem is, the code managed to leave an open transaction after the query finished, and I do not see how this would happen. Regardless of what work is done inside the TRY-clause, what possible scenarios could cause a query like this to leave a transaction open?

There are a few types of errors that are not caught by the T-SQL TRY / CATCH structure (taken from the MSDN page for TRY...CATCH and slightly edited for clarity):

  Errors that have a severity of 20 or higher that stop the SQL Server Database Engine task processing for the session. If an error occurs that has severity of 20 or higher and the database connection is not disrupted, TRY…CATCH will handle the error.
  Attentions, such as client-interrupt requests or broken client connections.
  When the session is ended by a system administrator by using the KILL statement.
  Errors that occur at the same level of execution as the TRY…CATCH construct during statement-level recompilation, such as object name resolution errors that occur after compilation because of deferred name resolution.

For the example code provided in the Question (i.e. a single DML statement), there is a very simple fix: remove the explicit transaction. For a single DML statement an explicit transaction is unnecessary unless there is additional logic that does a check on some condition and might optionally decide to undo that change via a ROLLBACK done in the main TRY and not as part of the CATCH since it wasn't a database engine error. Outside of that particular scenario, I can't see a reason (a good one, at least) to use an explicit transaction for a single DML statement. But I would still keep the TRY / CATCH structure for general error handling.

When it comes to multiple DML statements (which is the true context of the Question as stated in the wording of it above the example code) then you obviously do need an explicit transaction, so here are some thoughts on that:

A comment by @usr on the Question suggests to skip the error handling at the database layer altogether and instead handle it at the app layer. While app code can handle the transactions and error handling, if you are not using an ORM like Entity Framework exclusively (meaning you have stored procedures that are called by SQL Agent jobs and/or by support people via SSMS), then those stored procedures need to include the transaction and error handling because the app layer isn't always the initiator of the execution, yet you still want transaction and error handling in those other situations. I discuss this more in the following answer: Are we required to handle Transaction in C# Code as well as in stored procedure
Uncommitted transactions are automatically rolled-back when the connection ends. This is from SQL Server's perspective, not the client's perspective. Meaning, the connection is no longer listed in sys.dm_exec_connections.
Connection pooling complicates the auto-rollback handling since it, by design, keeps the connection open, even after the client "closes" it. When the connection remains open so that another "connection" attempt can simply re-use it, the session itself is not "cleaned up" until a new connection is opened and a query batch is executed! Upon the first execution submitted by the app code on a "new" connection that is actually reusing a connection from the pool, an internal process labeled as sp_reset_connection is called that will, among other things, roll back an uncommitted transaction in that session that is now being reused. The problem here is when connection pooling is being used, and there is a termination of the process (a Command Timeout perhaps), and no new connection is being requested and no new query is being submitted. In this case the connection just sits in the pool and the session still exists and no cleanup operation is being requested. But this doesn't last forever (though still longer than you want in these cases). According to the MSDN page for SQL Server Connection Pooling (ADO.NET) (in the Removing Connections section):

  The connection pooler removes a connection from the pool after it has been idle for approximately 4-8 minutes, or if the pooler detects that the connection with the server has been severed.

Cancelling a stored procedure execution isn't just a matter of your app code getting a CommandTimeout or calling an explicit "cancel". SQL Server Management Studio (SSMS), or any IDE, is also client code. And it is possible cancel a running stored procedure in SSMS. And doing so will have the same effect of skipping the CATCH block. But cancelling an execution in SSMS still keeps you in the same session, so the transaction is still active until you manually call COMMIT or ROLLBACK, or until you close that query tab (at which point it will tell you that there is an uncommitted transaction and ask if you want to commit it or not; my testing shows that answering "no" will roll it back).

So what to do about app code that is using connection pooling? I am not a huge fan of using SET XACT_ABORT ON;. What I would try first is:

Connections can set the max size of the connection pool via the Max Pool Size connection string keyword. If the pool is very large, then it is less likely for any particular connection to be reused quickly. The default pool size appears to be 100. Setting the value to be slightly lower than it is currently (but not too low as to not make effective use of having connection pooling) will help ensure that the connections are reused more quickly will means the cleanup process will be called more quickly which will rollback any open transaction.
In your app code where you are executing the query / stored procedure, in the catch block you can call SqlConnection.ClearPool which should close the actual connection at the SQL Server level which should in turn allow the auto-rollback of uncommitted transactions to take place. You might even be able to be fairly targeted with this by checking the exception to see if it is a Command Timeout or one of the other few scenarios that would terminate the process while skipping the T-SQL CATCH block, and if so, then call ClearPool.
Technically you could simply disable connection pooling entirely by specifying Pooling=false; in the connection string. However, I do not believe this is necessary and do not recommend it unless either absolutely necessary OR having an application that doesn't generate a lot of connections in the first place.


GROUP_CONCAT with ORDER BY , but results are not ordered The following query works fine

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' )
FROM project_photos
WHERE project_id = projects.id
ORDER BY priority) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5

but the images column are not ordered as priority. I am unable to understand why it is happening

Structure for table project

CREATE TABLE `projects` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`catID` int(11) NOT NULL,
`title` varchar(255) NOT NULL,
`description` varchar(400) NOT NULL,
`url` varchar(255) DEFAULT NULL,
`tags` varchar(255) DEFAULT NULL,
`featured` varchar(3) NOT NULL DEFAULT 'No',
`featured_url` varchar(255) DEFAULT NULL,
`order` int(11) DEFAULT NULL,
`created_at` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (`id`),
KEY `catID` (`catID`),
CONSTRAINT `FK_catID` FOREIGN KEY (`catID`) REFERENCES `category` (`catID`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=48 DEFAULT CHARSET=latin1;

Structure for table project_photos

CREATE TABLE `project_photos` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`url` varchar(250) DEFAULT NULL,
`project_id` int(11) DEFAULT NULL,
`priority` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=347 DEFAULT CHARSET=utf8;

How to Order GROUP_CONCAT() Values in MySQL

SELECT
*,
(SELECT
GROUP_CONCAT(url SEPARATOR '$$' ORDER BY priority)
FROM project_photos
WHERE project_id = projects.id
) AS images
FROM projects
WHERE catID = 2
LIMIT 0,5;


mysql Wrong bytesec: 0-0-0 at linkstart: 0 I have several MySQL (MyISAM) tables that have been corrupted. Running CHECK TABLE mytable returns:

> +-----------------+-------+----------+----------------------------------- |

> clldata.mytable | check | warning  | Table is marked as crashed

> clldata.mytable | check | warning  | Size of datafile is: 4654864 Should be: 4602520

> clldata.mytable | check | error    | Wrong bytesec: 0-0-0 at linkstart: 0

> clldata.mytable |
> check | error    | Corrupt
> |
> +-----------------+-------+----------+-------------------------------------------------------+

4 rows in set (0.01 sec)

Running REPAIR TABLE mytable results in truncating about 90% of the rows that were originally in the table (and the size of the .MYD file also shrinks about 90%). And most of the remaining rows in mytable are clearly actually from one of the other corrupted tables.

Is there any hope of recovering data from such a table?

MY CONDOLENCES
MyISAM only caches index pages in the MyISAM Key Buffer. MyISAM never caches data. Notice how the messages from CHECK TABLE is only mentioning the datafile (that's the .MYD file) .
Since MyISAM data is fully reliant on the OS, rows of data are cached only in the OS. The MyISAM tables that are corrupt had open file handles when the crash happened (doesn't matter if mysqld crashed or the OS that crashed)
Recovery of the data is not possible.
FUTURE CONSIDERATIONS
Switching to InnoDB
In the future, please use InnoDB for all tables. InnoDB has a higher tolerance for crashes because it has transaction logs and double write buffers to recover data that was committed but not yet written as well as rollback segments and undo logs for rolling back uncommitted transactions.
Still Want to Use MyISAM

You should backups of the MyISAMs regularly
You should keep binary logs enabled, even if you don't use Replication. That way, you could use backups of the MyISAM tables and use the binary logs to replay INSERTs, UPDATEs, and DELETEs up to the point before the crash.
You should setup MySQL Repication so that the Slaves have good copies of the same tables

Give it a try!!!


Monitoring SQL Server, What key KPIs? I actually built some "Custom" Monitoring tool to track information from the DMV Tables and store them in some kind of DataWarehouse I'm taking snapshots each 5minutes.

the data I'm retrieving is:

Database:

File Latency (ldf and mdf)
File Sizes and use
Transactions
Log Growths and Flushes
Database CPU used
Database Buffer MB used

SERVER:

SQL CPU / IDLE PROCESS / OTHER PROCESSES
RAM (TARGET/USED)
Page Life Expectancy
Cache Hit Ratio
User Connections / Database Connections

LOCKWAITS:

General Locks/DeadLocks/Waits information (Network... Io Latch...)

QUERIES:

In this part, I'm a bit lost... I would like to be able to detect problematic queries on my server and to do so I need to store historical information as the information on some of the DMVs is cumulative so if I want to some averages like for example execution_count I need to snapshot them.
I don't know what Key KPIS and threshold should a query reach to be considered a problematic query...

Also, I don't know if I should monitor more KPIs in order to detect CPU pressure/ RAM pressure / Disk pressure.

Any help on improving it will be appreciated!

If you don't know then you're not ready to write a tool of this nature. To echo the comments of others this is why people buy a tool to do it better than we can, and the prices are super reasonable for very small shops of a few servers.

The only people writing decent tools for themselves are highly skilled professionals sometimes working in larger shops where they can't get management to swallow a 6 figure monitoring cost.

If you want to monitor query performance you need to read up on query fingerprints, and fully read though some Grant Fritchey books on how plans are cached. It's hard going and even he makes mistakes; it's MCM level.

After that you'll understand a little but even the paid tools won't point out when something has gone wrong. What they do point out are the top queries (in CPU, disk, memory), or weird memory grants, or missing indexes. You can do and store these easily from the internet but they aren't alertable thresholds. They never are.

On your own you might also be able to pinpoint excessively different plan recompilations based on fingerprints and execution details - but likely this isn't worth it otherwise commercial tools would do it. It's probably too noisy.

There's another book called Healthy SQL which covers a lot of ideas and thresholds but it gets hazy.

Another great place is to download the Performance Analysis of Logs tool (PAL) which has a list of SQL counters (along with disk IO counters and similar things) plus defines in XML exactly how it calculates thresholds and why. These are useful overall but again I'm not sure I'd alert on them except as a daily report.

I think some company has also published a semi marketing white paper on why thresholds are pointless and what you really need are baselines (and doing statistical analysis on standard deviations but I'm pretty sure they don't use it for much themselves). But IMHO nobody is sharing easy to understand ways to analyse and flag disparate raw baseline data between snapshots. R looks like a promising spot to do it but there's no "how".


SQL Server - RangeX-X and RangeI-N locks I came to a dead point in a deadlock analyze. According to msdn:

RangeX-X are Exclusive range, exclusive resource lock; used when updating a key in a range.
RangeI-N are Insert range, null resource lock; used to test ranges before inserting a new key into an index.

So I understand that if I have an Index on 2 key columns - and I insert a new key I would have RangeI-N lock but if I update an existing key from the index I would have RangeX-X.

But my question is more or less complicated. Say I have the index IX_keys_included on column A, B and included column C.

In Serializable isolation mode I insert a new value for the included column C. Will there be RangeI-N or RangeX-X locks for the index IX_keys_included? Actually , will there be any locks given the fact that I insert a new column for an included column in the index?

I have figured this on my own and I wrote on my blog. For those interested in the solution visit this posts: RangeS-S, RangeS-U, RangeX-X
NB: The links above have been modified to point to archive.org because the site is no longer valid. It's unfortunate. Also, the content of the blogs is quite extensive or I would try to capture some of that data to here. It's just too much for one post. ~ jcolebrand PS: Don't forget to throw a few dollars at the archive.org folks if you follow these links.


Get x random rows from SQL Server I have read this question:
What is the best way to get a random ordering?
and can do a select top 4 from tabel order by NEWID() but I need it per category.
So I need 1 row from category A, 1 row from category B, 1 row from category C and 1 row from parentcategory D.

So I tried to union them and I tried to add a wrapping select query which told me that I could not order the subquery... So I am stuck.

Here is the SQL I have so far:

SELECT top 4 Artikel.ArtikelID -- etc.
FROM Artikel
INNER JOIN Categorie ON Artikel.CategorieID = Categorie.CategorieID
INNER JOIN CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID
INNER JOIN CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
INNER JOIN CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
INNER JOIN Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
INNER JOIN Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID
WHERE (ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
and taal.taalcode = 'EN'
order by NEWID()

current result could contain 4 rows all of the same category. That is not what I want. It's a bit different than the possible duplicate because it is not grouped on 1 column, but two different once.

Let's simplify the problem because I think it will be easier to visualize when you don't start adding in the complication of joins and other clauses.

Let's think about objects in the system catalog sys.all_objects, and say you want one of each of the following object types in the resultset:

table ('U')
procedure ('P')
view ('V')
any type of function ('FN', 'IF', 'FS', 'AF', 'TF')

The whole set is simply:

SELECT * FROM sys.all_objects
WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF');

To get a single, random row from each type (or set of types), you can say:

;WITH wholeset AS
(
  SELECT *, rn = ROW_NUMBER() OVER
    (
      PARTITION BY CASE
        WHEN [type] IN (N'FN',N'IF',N'FS',N'AF',N'TF')
          THEN N'FN'
        ELSE
          [type]
        END ORDER BY NEWID()
    )
  FROM sys.all_objects
  WHERE type IN (N'U',N'P',N'V',N'FN',N'IF',N'FS',N'AF',N'TF')
)
SELECT * FROM wholeset WHERE rn = 1;

Applying similar logic to your query, assuming -1 is not a valid category, in which case you could use -999 or some other impossible token value:

;WITH wholeset AS
(
  SELECT ...cols..., rn = ROW_NUMBER() OVER
    (
      PARTITION BY CASE
        WHEN Categorie.ParentCategorie = 292 THEN -1
        ELSE Artikel.Categorie
      END ORDER BY NEWID()
    )
  FROM dbo.Artikel -- always use dbo prefix
  INNER JOIN dbo.Categorie ON Artikel.CategorieID = Categorie.CategorieID
  INNER JOIN dbo.CategorieTaal ON Categorie.CategorieID = CategorieTaal.CategorieID
  INNER JOIN dbo.CategorieGroep on Categorie.CategorieGroepID = CategorieGroep.CategorieGroepID
  INNER JOIN dbo.CategorieGroepTaal on CategorieGroep.CategorieGroepID = CategorieGroepTaal.CategorieGroepID
  INNER JOIN dbo.Taal ON CategorieTaal.TaalCode = Taal.TaalCode AND ArtikelTaal.TaalCode = Taal.TaalCode and CategorieGroepTaal.TaalCode = taal.TaalCode
  INNER JOIN dbo.Levertijd ON Artikel.LevertijdID = Levertijd.LevertijdID
  WHERE (Categorie.ParentCategorieID = 292 or artikel.CategorieID = 246 or artikel.CategorieID = 247 or artikel.CategorieID = 288)
  AND taal.taalcode = 'EN'
)
SELECT * FROM wholeset WHERE rn = 1;

There are some edge cases here, like if you don't have any rows in a category, or if you only have one row that is in the parent category and that is the only row in one of the other three categories as well. But for most data sets this should work pretty reliably.


MariaDB auto increment problem Using OpenSUSE leap 15.1
10.2.29-lp151.2.9.1-x86_64 from vendor OpenSUSE (installed)

CREATE TABLE testtab2(
    my_id2 INT AUTO_INCREMENT,
    name2 VARCHAR(10),
    comment2 VARCHAR(20),
    PRIMARY KEY (my_id2)
);

testtab.txt:

|Roger |hurray   |
|George|today    |
|Paul  |yesterday|
|John  |last year|
|Ringo |tomorrow |

LOAD DATA LOCAL INFILE 'testtab.txt' INTO TABLE testtab FIELDS TERMINATED BY '|';

works fine with linenumbers (my_id) 1-5

INSERT INTO testtab (name,comment) VALUES ('Beatles','band');

this gets linenumber (my_id) 8 - should be 6.

If testtab.txt have 8 records and using INSERT INTO 1 record this gets linenr (my_id) 16.
This happens in other tables too - one moved from 199 to 256.

Anyone who can solve the problem?

This is not a problem, but expected, documented behaviour:

An AUTO_INCREMENT column normally has missing values. This happens because if a row is deleted, or an AUTO_INCREMENT value is explicitly updated, old values are never re-used. [...] With InnoDB, values can be reserved by a transaction; but if the transaction fails (for example, because of a ROLLBACK) the reserved value will be lost.
Thus AUTO_INCREMENT values can be used to sort results in a chronological order, but not to create a numeric sequence.

Most RDBMSes work this way. The identity/sequence/autoincrement value is not incremented by a random number. What happens is each session reserves and caches a certain, often configurable, number of identity/sequence/autoincrement values, and unused values from the cache are discarded if the session ends before they are all exhausted.
For example, session A reserves values 1 through 5 for itself, then session B reserves 6 to 10. Session A uses values 1, 2, and 3 and terminates, so 4 and 5 are never used.
If for some reason you require a gapless sequence (which in reality is very rarely needed), you'll have to implement it yourself, but note that this will be a performance bottleneck under any nontrivial workload.


Create table partitions on different filegroups, the first boundary on a Memory Optimised filegroup 99% of all our transactions are against the latest week's data, a WEEK_SELECTOR column describing which week is being described.
Partitioning our billion+ row table by week would be beneficial, more so if the half million or so rows for the latest week were in an in-memory partition.

On attempting to split the top week off to our memory optimised filegroup with

CREATE PARTITION FUNCTION [TPF](int) AS RANGE RIGHT FOR VALUES (N'1700114')
CREATE PARTITION SCHEME [TPS] AS PARTITION [TPF] TO ([PRIMARY], [memtest])

we get the following error:

Msg 7737, Level 16, State 1, Line 2
Filegroup memtest is of a different filegroup type than the first filegroup in partition scheme TPS

We are now assuming that filegroups must be of the same type for partitions and our plan is not possible, but cannot find documentation to say that it is impossible.

Can someone explain how it can be done, or if not, provide a link saying as much? Thanks.

Partitioning memory-optimized tables directly is not yet supported. See SQL Server Features Not Supported for In-Memory OLTP.

A workaround is described in the documentation Application Pattern for Partitioning Memory-Optimized Tables

  You can emulate partitioned tables with memory-optimized tables by maintaining a partitioned table and a memory-optimized table with a common schema. Current data would be inserted and updated in the memory-optimized table, while less-frequently accessed data would be maintained in the traditional partitioned table.

Alternatively, you could consider other technology solutions like Columnstore for real time operational analytics, which combines in-memory OLTP with columnstore (as [optionally filtered] secondary storage).


transaction log in RAM or physical file? I'm a beginner in transaction, just a question on transaction log.
We know that when we commit a transaction, the changes are written to the transaction log, but is transaction log in RAM or physical files? If it is in RAM and when system failure happens, obviously the RAM will be re-erased so we lose the transaction information, so how can we recover the commit?

You can find a pretty comprehensive guide to this question here, but to summarise, SQL Server will not return control to the application that committed a transaction until that transaction has been hardened to disk. Specifically, once it has been hardened to the transaction log file, control can be returned.

The data, at this point, may not be hardened into the data file, it may still be in the data buffer cache, but because it has been hardened into the transaction log then database recovery, in the event of a failure, can recover this transaction and persist the changes safely.

There is a log buffer cache in memory used to reduce the performance impacts of the sequential writes to transaction logs. The buffer is flushed to disk on several conditions, but one of them is a transaction commit. Until this data has been hardened, control is not returned to the caller, so even if you have a failure during this buffer flush the transactional consistency is maintained because this transaction is not yet considered committed. You will lose the data changes in that transaction, but as it was not committed, your application would already consider those changes lost as the commit was never completed.


sql server: sp_send_dbmail : Can I build the data to send in discrete operation? SQL Server 2012 and 2014

I can make sp_send_dbmail work fine when my SQL query is compact.
When the query itself is a big long beast, I can't make sp_send_dbmail work. (The deadly "
Failed to initialize sqlcmd library with error number -2147467259.")

The question is: Within SQL, can I use a different approach to send query results via email, to use in a SQL Agent job??

(IOW: Is there an approach OTHER than sp_send_dbmail + @attach_query_result_as_file)

Is it possible to build the data set to email in Step 1 of a job, and then in Step 2 take the result from Step 1 and attach it to an email?

Ideally, it would be possible to break the process up into discrete steps. The "single shot" sp_send_dbmail is fragile. If I could build the payload in one step, and pass that payload to the email in the next step, things would be much more managable. (And if that attachment could be zipped, oh the joy! And if it could be an xls, we could get some work done!)

(I just spent half a day fighting with sp_send_dbmail. The solution, after hours of obscure errors, was to package the big query in a view, so the sp_send_dbmail  query is just three lines. But this is crazy coupling. sp_send_dbmail  itself is the problem. We need a different path.)

That might be the case of checking the attachment limit size set in your mail server, or the Maximum File Size (Bytes) setting in the database mail configuration. The default value for this setting is 1000000(bytes), if your result set is larger than this value it won't be sent.


How can I design a database that easily lets me recreate historical snapshots I'm designing a database for an accounting system. In the system there are companies, and each company has many contracts.

Each contract gives a company access to products, data types, or both. Clients use the different products to access different data types.

The initial contract may give the company Products A and B with access to data types X and Y. A second contract may come along and give the company access to data type Z within Product A. And so on.

It is important for the users of this system to be able to move through the contracts and see the state of the company's products and data type access, as of different contracts.

I am trying to think of a good approach to the architecture, and I've come up with two options:

Store the latest state of a company's products and data types in a products and data_types table. These would belong directly to the company. Each new contract would update these tables appropriately. So, querying the current state of a company's access would be a simple SQL query.

Then, in a separate table, I could store the contracts, and each contract would have many contract_items. This is where I could record which product or data type the contract is adding, updating or removing.

I could then write the logic in my software layer to recreate historical snapshots, using this contract_items table. I would simply pull all the items for a company up to the selected contract, then use the items to build up the state of the company as of that contract.
Store a snapshot of the company as of each contract. I am actually having trouble thinking through how I could do this in the database layer. One way would be to create a new rows in the companies, products and data_types tables for each contract.

This would make querying the state of the company as of any particular contract a simple SQL query. I would just select out of those three tables where contract_id equaled the current selected id. However, it seems like a lot of data duplication. Perhaps this is not a problem.

Any thoughts on this? Are there best practices in this area, or something I'm missing? Thanks in advance.

Versioning. Not a new problem - used thousands of times per day by developers using any version control system.

Mark any item with 2 fields - ValidFromNo and ValidToNo. The last can be 0.

Keep a table versions that has the version numbers. ValidFromNo is from which version number an item is valid, ValidToNo - which can be null - is the last valid version. Null marks "Currently in use" so you do not have to update all valid items on every new versin.

Simple like that.


How to pause the subscriber end of transactional replication How do I go about pausing transactional replication on the subscriber end.

We have a publisher on Server A, a distributor on Server B and a subscriber on Server C. All are in the same network.

Some background, we have quite a busy BizTalk server where we replicate the data from the BAM parts to a separate database server which will be used for a customized web application for statistics, searching etc. Seeing as the tables in the BizTalk server are both quite large and missing indexes etc since they're basically just there to have data dumped into them we opted to have the second, replicated database in which we can apply various optimizations for reading the data.

One thing I've noticed though is that adding a basic, non-clustered, index takes forever and causes all kinds of locks (we're using SQL Server Standard so Online indexing isn't an option unfortunately) I was thinking that giving the database a bit of respite from all the write operations from the BizTalk data while creating the indexes would be a good idea (right or wrong?).

Question is, how do I pause the replication (without losing any data from the publisher) on the subscriber end while creating the indexes?

Replication runs using "Agents"--essentially external executables that do the work. For transactional Replication, you have three agents:

Snapshot Agent - this is the process who does the snapshot to initialize Replication (assuming you aren't initializing via another method. Most people use snapshots). There is one per publication. It lives on either the Publisher or the Distributor.
Log Reader Agent - this is the process that reads the transaction log on the publisher, identifies changes, and writes those changes to the distribution database on your distributor. There is one per publication. It lives on either the Publisher or the Distributor.
Distribution Agent - this is the process that takes changes from your distribution database and pushes it to the subscriber. There is one per subscriber. It lives on either the Subscriber or the Distributor.

The standard setup is that the agents run as SQL Agent jobs. If you want to stop distributing commands to a subscriber, simply stop the jobs. You can find distribution agents based on the job category of REPL-Distribution. The job name will be composed to include publisher/subscriber info to help you identify the correct job to disable. Those values may be truncated when making the job name, so if you have multiple distributors, you may need to look at the distribution job step itself to be 100% sure you have identified the correct job.

Note you can also confirm the correct job by querying the distribution database--but I generally find it quicker and easier to look at the job itself.


UPDATE with subquery LIMIT 1 returns multiple rows I am struggling with a query which is randomly returning multiple rows instead of one. I am using PostgreSQL 9.4.12

The query is the following :

UPDATE folder as f set locked = 'locked', date_locked = current_timestamp
from (
    SELECT * from folder as f2
    WHERE f2.locked = 'available'
    LIMIT 1 FOR UPDATE ) as folderToUpdate
WHERE f.id = folderToUpdate.id returning f.*

The id is the primary key.

It is executed through a bunch of integration tests. When I am executing only the test doing the query above, I don't have any issue. But, when I am executing all the tests, I randomly get multiple rows. Do you have any suggestions?

It's quite normal you get multiple rows.  The update selects all rows from folder, and for each of them selects a row from folder f2 (I guess the same, if id is PK). If locked = 'available', the record is updated.  On to the next row.

The correct syntax is here, Postgres UPDATE ... LIMIT 1,  and consists in using a CTE.

Without CTE, your result depends on how PostgresSQL decides to execute your update, starting with the outer (multiple rows are possible) or with the inner select.


How to count multiple values of a column as one group? I have a table main with the following rows:

 id |  name  | rank
----+--------+------
  1 | Ali    | a
  2 | Sami   | b
  3 | Khan   | c
  4 | Kamran | d
  5 | Imran  | e
  6 | Asad   | a
  7 | Nawid  | v
  8 | Jamil  | c
  9 | Usman  | j

I want to count rows with certain values in column rank. For example, I want to group values as follows:

Values (a,b,v) should come in one group by name myvalues.
Values (c,d,j) should come in another group by name yourvalues.
Value (e) should come to another group by name extravalues.

My desired result:

myvalues | yourvalues | extravalues
   4     |     4      |      1

myvalues counts 4 because it consists of (a,b,v). There are 2 occurrences of a, 1 occurrence of b and 1 of v - a total of 4.
The same is the case with yourvalues which consists of (c,d,j) and the occurrences of these values makes a total of 4.
And the last group extravalues consists of (e) and counts only 1 row.

Use the aggregate FILTER clause in Postgres 9.4+:

SELECT count(*) FILTER (WHERE rank = ANY ('{a,b,v}')) AS myvalues
     , count(*) FILTER (WHERE rank = ANY ('{c,d,j}')) AS yourvalues
     , count(*) FILTER (WHERE rank = 'e')             AS extravalues
FROM main;

Details and alternatives for older versions:

Return counts for multiple ranges in a single SELECT statement


How to convert list of dates to list of date ranges grouped by a condition Given a data set in the below format:

State   AuditDate
1       2016-11-01
1       2016-11-02
1       2016-11-03
0       2016-11-04
1       2016-11-05
0       2016-11-06
0       2016-11-07
1       2016-11-08
1       2016-11-09
1       2016-11-10
0       2016-11-11
0       2016-11-12

Is there an easy way to convert it to a list of date ranges grouped (and ordered) by the state? (with the last record ending on GETDATE()) E.g.

State   StartDate   EndDate
1       2016-11-01  2016-11-04
0       2016-11-04  2016-11-05
1       2016-11-05  2016-11-06
0       2016-11-06  2016-11-08
1       2016-11-08  2016-11-11
0       2016-11-11  2016-12-05

I initially tried doing this using Row_Number and Dense_Rank but couldn't get it to work, eventually I did come up with a solution (see below) however it feels very clunky and inefficient, there must be a better way to do this?

IF OBJECT_ID('tempdb..#Records') IS NOT NULL DROP TABLE #Records
Create Table #Records
(
     State bit
    ,AuditDate date
    ,StartDate date
    ,EndDate date
)

Insert Into #Records
(
     State
    ,AuditDate
)
Select 1, '2016-11-01'
union Select 1, '2016-11-02'
union Select 1, '2016-11-03'
union Select 0, '2016-11-04'
union Select 1, '2016-11-05'
union Select 0, '2016-11-06'
union Select 0, '2016-11-07'
union Select 1, '2016-11-08'
union Select 1, '2016-11-09'
union Select 1, '2016-11-10'
union Select 0, '2016-11-11'
union Select 0, '2016-11-12'

Update r1
    Set StartDate = SP.StartOfPeriod
From
    #Records r1
    Outer Apply
    (
        Select
            Max(AuditDate) as LastTimeDifferent
        From
            #Records r2
        Where
            r2.AuditDate < r1.AuditDate
            and r2.State != r1.State
    ) as LD
    Outer Apply
    (
        Select
            Min(AuditDate) as StartOfPeriod
        From
            #Records r3
        Where
            (r3.AuditDate > LD.LastTimeDifferent or LD.LastTimeDifferent is null)
            and r3.State = r1.State
    ) as SP

Update r1
    Set EndDate = isnull(ND.NextTimeDifferent, GETDATE())
From
    #Records r1
    Outer Apply
    (
        Select
            Min(AuditDate) as NextTimeDifferent
        From
            #Records r2
        Where
            r2.AuditDate > r1.AuditDate
            and r2.State != r1.State
    ) as ND

Select Distinct
     State
    ,StartDate
    ,EndDate
From
    #Records
Order By
    StartDate

With help of this other question, you can try :

WITH
  t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate)
                           THEN NULL ELSE 1
                       END
      FROM #Records
    )
select min(auditDate), dateadd(dd, 1, max(auditDate)), state
from (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate)
    FROM t
    ) t
group by c, state
ORDER BY 1 ;

That assumes that records are every day. If not, let's do some small changes:

WITH
    t AS
    ( SELECT state, auditDate, x = CASE WHEN state = LAG(state) OVER (ORDER BY auditDate)
                           THEN NULL ELSE 1
                       END
      FROM #Records
    ),
    t2 as
    (
    SELECT state, auditDate, c = COUNT(x) OVER (ORDER BY auditDate)
    FROM t
    )

select a.state, min(a.auditDate) as startDate, isnull(min(b.auditDate), getdate()) as endDate
from t2 a
    left join t2 b on a.c = b.c - 1
group by a.state, a.c
order by 2


Auto-Commit in SQL Server and Oracle When I worked on Oracle 8 many years before, I used to execute COMMIT command manually after each bulk INSERT. In SQL Server, Auto-Commit is ON by default which has advantages as well as hazards.

I want to know whether the newer versions of Oracle still has Auto-Commit OFF by default? I also want to know how to put OFF Auto-Commit in SQL Server 2005.

Oracle

From AskTom in 2000:

  ...the only method Oracle operates in is "client tells us when to commit".  We do not have an autocommit mode. Various TOOLS and API's do (eg: I can tell sqlplus to autocommit, that just means sqlplus will issue a commit after each statement).

This is still true and probably always will be. For a detailed description of commit see the Oracle docs

SQL Server 2000+

from SO (inverted):

  You can turn autocommit OFF by setting implicit_transactions ON

or if you prefer, from the docs (or Mark's more direct link)


How to selecting data where the date is with the next month or later I want to display all data where the the expiry date is within next month or later, here is my code:

<cfquery name="ExpiryDate">
        SELECT *
        FROM Date
        WHERE Expiry >=

</cfquery>

This is what you want:

    SELECT *
    FROM Date
    WHERE Expiry >= DATE_ADD(curdate(), INTERVAL 1 MONTH);

MySQL date and time functions are documented here.


Oracle - large table with large IN Table students has about 10 million records; ID is indexed.
Student grades has 20 million records - student_id is indexed.
I am querying about 20,000 students by their ids ids with a join between students and parents:

select * from students s left join grades g on s.id=g.student_id
where (s.id IN (s1, s2... s1000)
or (s.id IN (s1001, s1002... s2000)
or (s.id IN (s.2001, s2002...s3000)
//until s20000)

I need to split the INs into multiple batches as IN can only get 1000 or less values.

The query takes about 5 minutes to return. Is there any way I can optimize it?

Thanks!

Data Comes from External Source

The IN clause is limited to 1000 values.  Don't use it for such searches.

Workaround:

Use a GTT (Global Temporary Table)
insert "values of interest" into that GTT
Modify the SELECT statement to JOIN against the GTT.

Example

Create the GTT

create global temporary table students_gtt (
  student_id int primary key
);

Insert values into the GTT

insert into students_gtt
select level
from dual
connect by level <= 50000;

and JOIN it in the SELECT statement

select
    *
from
    students       s
    left join grades         g on s.id = g.student_id
    join students_gtt   g on s.student_id = g.student_id

Notes

First, I suspect a good portion of your time is spent Hard Parsing your query.

I'm taking a 22,974,957 row table and left outer joining it to a 93,501,293 row table in addition to joining the 50,000 row GTT.  The values return in seconds.

Eventually, you are going to a run into a "node" limit for the parser.
The GTT method does not require you to "hack your SQL statement".

Data from another SQL Statement?

Just include that SQL within the SELECT statement.


You can see one difference in the plans, one has an estimated row count of 100, the other has a (correct) estimated row count of 42.  (Note that this is fixed in v12, where UNNEST has been taught to count its input size when given a constant array, rather than just blindly reporting an estimate of 100).  This could become important if the difference were larger than 42 vs 100, and if you added further joins (or other complexities) to the query.

  One thing that I thought of was that unnest can work for a composite key lookup (searching by two indexed fields, not just one).

How would you use UNNEST for this, where you can't use =ANY equivalently instead?  Usually a VALUES list is used for this (which also has its own issues, which also differ from version to version.)


ERROR: "sql " is not a known variable This must one of the dumbest questions I've ever asked here, but there must something really sick hiding in my SQL script that is blocking it from running.

I'm invoking the cobertura.sql file using the following sample CLI syntax:

psql -h localhost -U myUser -f cobertura.sql myDB

But it complains with the following error:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ... cobertura.sql file:

DO language plpgsql $$
declare
    eq  record;
    sql varchar;
BEGIN

create table if not exists cobertura_tmp  (num integer, realtime char(1), lat numeric, lng numeric);

truncate table cobertura_tmp;
for eq in select imei_equipo as imei  from cliente_avl_equipo where id_cliente in (select id from cliente where nombre ilike '%enangab%') limit 3

loop

sql := format('insert into cobertura_tmp select count(*) as num, tipo as realtime, round(CAST(latitud as numeric), 4) as lat ,round(CAST(longitud as numeric), 4) as lng   from reports.avl_historico_%s where latitud between -38.67405472 and -36.75131149 and longitud between  -73.08429161 and -69.65333954 group by tipo, round(CAST(latitud as numeric), 4),round(CAST(longitud as numeric), 4)', eq.imei);

execute sql;

end loop;

update cobertura_tmp set num= -1* num where realtime='S';

create table if not exists cobertura_tmp_resumen  (num integer, lat numeric, lng numeric);
truncate cobertura_tmp_resumen;
--    select sum(num) as num , lat, lng into cobertura_tmp_resumen from cobertura_tmp group by lat,lng;

--    drop table if exists cobertura_tmp;

END;
$$;

The same script runs remotely and smoothly from Mac OSX using Postico Version 1.3.2 (2318).

QUESTION: Why isn't it finding the sql variable that is in the declare section?

Your error message says:

psql:cobertura.sql:29: ERROR:  "sql " is not a known variable
LINE 14: sql := format('insert into cobertura_tmp select count(*) as ...

Look closely: "sql ", not "sql"

That means you have a sneaky, invisible character right after "sql" instead of an innocent space character. It's not in your question, probably got lost in translation when you copied & pasted it in your question here. Your original code contains something like:

sql　:= format ...

Do you see it? No? Because one cannot see it. (You might notice the space is a tad bit wider in this particular case - if your browser, font and character set produce the same result as mine.) It's an "ideographic space", Unicode U+3000, HTML &#12288. Just a random example. There are various characters like that in Unicode. Replace it with a simple space to fix your problem.

dbfiddle here

I hate this about Unicode, too many characters that mostly just confuse people ...

For a quick test:

SELECT ascii(' ');   -- 32    -- plain space
SELECT ascii('　');  -- 12288 -- not a plain space
SELECT '　' = ' ';   -- f     -- not the same!

A quick test whether there are any non-ASCII characters in a string:

SELECT octet_length(string) = char_length(string)  -- f
FROM  (SELECT text 'sql　:= format' AS string) t;

Assuming encoding UTF8, non-ASCII characters occupy 2-4 bytes, while plain ASCII characters occupy a single byte. octet_length() returns the

  Number of bytes in string

while char_length() (same as length()) returns the number of characters. For all-ASCII, both return the same. If octet_length() returns a higher number, you have suspicious characters. Does not have to mean anything. Any accented character in a string is good for it.

Or use an editor that can highlight suspects.

Aside, while being at it, your plpgsql code would be considerably more efficient like this:

DO
$$
DECLARE
   _imei text;
   _sql  text;
BEGIN

IF to_regclass('pg_temp.cobertura_tmp') IS NULL  -- if tmp table does not exist
THEN
   CREATE TABLE cobertura_tmp (
      num      integer
    , realtime char(1)
    , lat      numeric
    , lng      numeric
    );
ELSE
   TRUNCATE TABLE cobertura_tmp;
END IF;

FOR _imei IN
   SELECT e.imei_equipo::text  -- imei is NOT NULL & *safe* against SQL injection
   FROM   cliente c
   JOIN   cliente_avl_equipo e ON e.id_cliente = c.id  -- assuming c.id is UNIQUE
   WHERE  c.nombre ILIKE '%enangab%'
   LIMIT  3
LOOP
   _sql := format(
   $s$
   INSERT INTO cobertura_tmp (num, realtime, lat, lng)
   SELECT count(*)::int * (CASE WHEN a.tipo = 'S' THEN -1 ELSE 1 END) -- AS num
        , a.tipo                         -- AS realtime
        , round(a.latitud::numeric, 4)   -- AS lat
        , round(a.longitud::numeric, 4)  -- AS lon
   FROM   reports.avl_historico_%s a
   WHERE  a.latitud  BETWEEN -38.67405472 AND -36.75131149
   AND    a.longitud BETWEEN -73.08429161 AND -69.65333954
   GROUP  BY 2,3,4
   $s$
 , _imei);

   EXECUTE _sql;
END LOOP;

/* integrated above to avoid costly extra update:
UPDATE cobertura_tmp
SET    num = -1 * num
WHERE  realtime = 'S';
*/

-- ... etc

END
$$;

Many little details, but that's not the topic of the question.
I doubt you need a temporary table at all ...

Try it if you want to improve the code. Related:

How to check if a table exists in a given schema
Insert text with single quotes in PostgreSQL