Single table with multiple NULL columns vs multiple tables I've read this. But I'm not sure if I understood correctly or my situation is slightly different.

So, Here is my situation: I've a single table with customer's delivery addresses and there are two types of delivery addresses -  main delivery address (every column is required) &  alternative delivery address(optional).

Here's my delivery_info table:

id(PK)
city_id(FK)
area_id(FK)
recipient_name(NULL)
street_address(NULL)
contact_no(NULL)

My orders table:

id(PK)
delivery_info_id(FK)
alt_delivery_info_id(FK)
status
...

The city (city_id) and area (area_id) of both addresses will always be available and must be same that means the alternative delivery address must be in the same city and same area of main delivery address. But other 3 columns - recipient_name, street_address, contact_no is required for main delivery address but optional for alternative delivery address.

So my question is should i separate these two types of delivery addresses in two different tables e.g. delivery_info and a alt_delivery_info OR Keep it as it is???

There is no hard-and-fast rule.  Here's the logic I would use in coming to a decision.

In general, it is clumsy to have an array of things (delivery addresses) spread across multiple columns.

I would pull out the entire "delivery" set of columns and make a table for them. There would be one or two rows in this new table.

Then, I would do one of these approaches:

Plan A:  In the main table, have two columns:

delivery INT UNSIGNED NOT NULL,
alt_delivery INT UNSIGNED  NULL, -- note Nullable

Plan B:  The delivery table would have a column with 1 or 2 in it, indicating 1=preferred.  (Or some variant on such).  The include that column in the JOIN.

Plan B is especially good for something like phone numbers, since there can be an arbitrarily large set of such (cell, home, work, fax, etc.)  On the other hand, for delivery you may have a rather strong limit today of exactly 1 or 2 addresses.

In both cases, it is probably not wise to do any kind of de-dupping of locations or phones.  So what if there is an occasional dup; after all, cohabitation is common.

In a slightly different use case, a billing UI might ask the user "Is the shipping address the same as billing address?"  This is giving the user the option to tie the two together.  In this case, the main table has two non-null columns bill_to and send_to, that may or may not link to the same address.

In a "location" table, it is clumsy to 'normalize' city, country, etc.  And it is not justifiable for either reason for normalizing:  It does not save much space, and you aren't likely to need to change the spelling of the city/country in some central location.  (Czechoslovakia notwithstanging)


Will inserting a row keep it in the buffer cache I understand that selecting a set of rows from the database will make sure they remain in the buffer cache (assuming they fit).

Will inserting a set of rows make sure they are available to select from the buffer cache as well?

Context:
We are considering forcing the current-day partition of a table in the buffer cache, but if inserting rows will enter them into the buffer cache anyway, then there would be no need to make the extra effort. (Rows are entered with the current timestamp, never in the past or the future, partitioning is by day).

Since partitions have partition keys, when you insert the row, the partition key will need to be updated as well.  This works well with range, range-hash partitions from my experience.  So if your primary key is part of the partition, I would say it's not worth the effort to try to load the current day partition into memory, from my experience.  I'm not quite sure why you would want to have an entire partition in memory.  It seems that you would get far better performance, simply by just focusing on partition pruning and partition-wise joining, and parallelism.  Inserts are much faster on partition tables than heap tables anyways, and the buffer cache will be used as long was it has to search for the key.  If you want to test this out, run your process and monitor the X$BH view which gives you an object-by-object view of what's being located in the cache.


grouping rows of common values to create a new 'group id' for each set I am trying to achieve the below but was not able to so far, any help would be greatly appreciated.
I have this data (sorted from a query by id, anchor, date, and time) that I wish to group by common anchor :

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2

I'm looking to do a query in postgres where I can select this data and foreach set of common anchors, have a 'group number'
I then need a query to sum the anchor of points of same group, example above would become:

anchor sum group
2      4   1
3      9   2

thanks!

EDIT: McNets solution works perfect,
I have another case, with below data.
The anchor repeats but after a change of anchor: they're sorted by time, first it was anchor 2, then anchor 3, then again anchor 2.
I need to group after the change (ids 11 & 12) to have a new group number in this case

id anchor date        time     'group' (the value to get)
3  2      2019-01-01  07:00     1
4  2      2019-01-01  08:00     1
5  3      2019-01-01  15:00     2
7  3      2019-01-01  16:00     2
10 3      2019-01-01  17:00     2
11 2      2019-01-01  18:00     3
12 2      2019-01-01  19:00     3

Use DENSE_RANK function:
select
    anchor, sum(anchor) as "sum", grp  as "group"
from
    (select
         anchor, dense_rank() over (order by anchor) as grp
     from
         t) t1
group by
    anchor, grp
order by
    grp;

anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2

db<>fiddle here
More examples about DENSE_RANK here.
Even though it is preferable to add a new question when there is a significant change, this is the answer at your edited question.
select anchor,
       sum(anchor) as "sum",
       grp as "group"
from (
      select anchor, dt, tm,
             sum(rst) over (order by dt, tm) as grp
      from (
            select anchor, dt, tm,
                   case when coalesce(lag(anchor)
                                      over (order by dt, tm), 0) <> anchor
                        then 1 end as rst
            from   t
           ) t1
     ) t2
group by anchor, grp
order by grp;

anchor | sum | group
-----: | --: | ----:
     2 |   4 |     1
     3 |   9 |     2
     2 |   4 |     3

db<>fiddle here


Multiple to_json(array_agg), separate joins I'm building a messaging system, and trying to get a list of messages and their replies; and also join the participants of the message threads.

So I'm shooting for messages = [{id, subject, body, replies: [..], users: [..]}]

SELECT messages.*,
       to_json(array_agg(users)) users,
       to_json(array_agg(replies)) replies
FROM messages
LEFT JOIN (SELECT id, fullname, email, company FROM users) users
   ON users.id = messages.user_id OR users.id = messages.to
LEFT JOIN (SELECT * FROM messages ORDER BY created_at) replies
   ON replies.message_id = messages.id
WHERE messages.to = :to OR (messages.to IS NOT NULL AND messages.user_id = :to)
GROUP BY messages.id;

I've tried various other queries, this gets me the closest. The problem is that for each message, all the replies are repeated once. I.e., if there are 3 replies in a thread, I get 6 (2x each). Any ideas what might be wrong?

I am using Postgres 9.5.

The basic problem is this:

Two SQL LEFT JOINS produce incorrect result

Here is one way to avoid this problem and improve some other details while being at it:

SELECT m.*, u.users, r.replies
FROM   messages m
LEFT   JOIN LATERAL (
   SELECT json_agg(u) AS users
   FROM   (
      SELECT id, fullname, email, company
      FROM   users
      WHERE  id IN (m.user_id, m.to)
      -- no order by?
      ) u
   ) u ON TRUE
LEFT   JOIN LATERAL (
   SELECT json_agg(r) AS replies
   FROM   (
      SELECT *
      FROM   messages
      WHERE  message_id = m.id
      ORDER  BY created_at
      ) r
   ) r ON TRUE
WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL;

Use json_agg() instead of to_json(array_agg()). Simpler, faster.
Aggregate the subquery on users right away in a LATERAL join, thus avoiding the main problem. Related:

Create smaller row from alias and preserve column names

This:

WHERE  m.to = :to OR
      (m.to IS NOT NULL AND m.user_id = :to)

can be expressed simpler:

WHERE  :to IN (m.user_id, m.to)
AND    m.to IS NOT NULL

This:

SELECT json_agg(r) AS replies
FROM   (
   SELECT *
   FROM   messages
   WHERE  message_id = m.id
   ORDER  BY created_at
   ) r

could be shortened to:

SELECT json_agg(r ORDER BY created_at) AS replies
FROM   messages r
WHERE  message_id = m.id

But a single ORDER BY in a subquery is typically faster than per-aggregate ORDER BY.


what is "local block" in pg_stat_statements? https://www.postgresql.org/docs/9.4/static/pgstatstatements.html

local_blks_hit  bigint      Total number of local block cache hits by the statement
local_blks_read bigint      Total number of local blocks read by the statement
local_blks_dirtied  bigint      Total number of local blocks dirtied by the statement
local_blks_written  bigint      Total number of local blocks written by the statement

Doc says "local block" and where does it point to??

From https://www.postgresql.org/docs/9.4/sql-explain.html:

  Shared blocks contain data from regular tables and indexes; local blocks contain data from temporary tables and indexes; while temp blocks contain short-term working data used in sorts, hashes, Materialize plan nodes, and similar cases.


Can I add 1 primary node in existing cluster? I have a 2 node windows 2012 cluster and SQL Server 2012 installed on both. In this I have added a database on nodeA as primary and one on nodeB as secondary. I want to add more 2 databases which will utilize more CPU. So I was thinking I should add one more primary node nodeC which will have nodeB as secondary and  have one listener for all.

Is this possible?

The architecture of an availability group is that there is one primary node that owns all databases in the availability group.  A listener has an IP address, and it would be impossible to have that IP address online on multiple nodes at once.

The only way you can use another node (NodeC) as a primary while the current node (NodeA) is also a primary is to create an additional availability group.  You could set up this new availability group so that only NodeC and NodeB are server replicas, or, you could also include NodeA as a replica as well if desired.


Displaying output in SQLPlus I will open my SQLPlus with CMD in windows. When I write a query it shows 157998 rows as result. I want see the all result in sqlpus window, but it is not showing all the result.

Not an Oracle/SQL*Plus problem. The default size of commandline window screen buffer is 300 (lines), and the maximum is 9999 (lines), so it is unable to display 157998 rows.

Better spool the output to a file:

SQL> spool output.txt
SQL> select ...
SQL> spool off

After that you can view the result in the file output.txt


Prevent users deleting more than a certain number of rows in MySQL The problem:

I have a table with rows, nothing special about it
When a database user removes a row, it should increment a number
A database user is only allowed to remove 3 rows max in the table
A database administrator should be able to add more "remove tokens" to a database user

I know I should have a BEFORE DELETE trigger to see if the user can remove a row, and an AFTER DELETE trigger to increase the number of rows deleted by the user.

How should I store the info about how many rows a database user has removed from that specific table, and how do I allow a database admin to add more "remove tokens" to the user.

I thought of creating a table to store this, but I wonder if there are different ways to do it without a table? This might sound like a weird question, but I'm just trying to figure out how more advanced stuff works.

With this kind of granularity, you need to have a rather elaborate setup.
I was thinking of suggesting several things
SUGGESTION #1 : Use Stored Procedure for Specialized DML
Having to micromanage the number of DELETEs per user to mitigate DML should not become a server-centric function for DELETEs against a specific table. That should remain an application-centric function. Therefore, create a Stored Procedure that can manage the number of DELETEs.
SUGGESTION #2 : Use the BLACKHOLE storage engine
Any audit trail of DELETEs using a real table can create a localized disk bottleneck that would be a little clumsy to throttle. If the audit trail was recorded in a BLACKHOLE table, that would significantly reduce any disk I/O issues that would otherwise rear its ugly. If you write to a BLACKHOLE table, where would the audit trail data be written? That's leads up to...
SUGGESTION #3 : Use MySQL Replication
How would MySQL Replication help? Suppose the table is called mydb.audittrail. If the mydb.audittrail uses BLACKHOLE on the Prod Server, it could replicate to a Slave that that has mydb.audittrail as MyISAM. That would be the location of the actual audit data. The only drawback would be added disk I/O for writing audit trail info to the binary logs. You can further reduce that by having the binary logs stored on SSD or a RAM disk.
Please see my answer to Is it normal to use many Triggers?  for further info on this concept of audit trail recording with the BLACKHOLE Storage Engine and MySQL Replication.


Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server This is going to be a long story I'm afraid ;-(

I have to read some text-files into a SQL database. This database is on a SQL 2008 R2 x64 database server. I want to use a linked server so after some research I found that I had to install the Microsoft.ACE.OLEDB.12.0 x64 provider. I did this on my local machine and after I created my linked server I had some trouble reading text files.

The provider for the linked server was set to use Microsoft.ACE.OLEDB.12.0, the datasource pointed to the correct folder and the providerstring was set to "TEXT", but I could not test the connection. It was throwing this error:

  Cannot initialize the data source object of OLE DB provider
  "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXXXX". OLE DB
  provider "Microsoft.ACE.OLEDB.12.0" for linked server "XXXXXXX"
  returned message "Unspecified error". (Microsoft SQL Server, Error:
  7303)

After some research, I found that the SQL Service was running under the NETWORKING SERVICE-account. When I changed this to Local Account, everything was working fine.

Note: I also had to change some settings for the provider, "Allow inprocess" is checked, otherwise it was not possible to browse the files in the configure datasource folder.

But now I try to implement this solution on my customers network and I cannot get it to work. It is also a SQL 2008 R2 x64 Db-server, Microsoft.ACE.OLEDB.12.0 provider is installed, but it still throwing the mentioned error. So I asked them which account is used to run the SQL Service. This is an AD-account belonging tot the local Administrator group of this server and therefore it had enough privileges as far as I can see. The user who is testing is connected to the database engine with a windows AD account, also belonging to the administrator group. But when he tries to test the connection, it throwing the same error.

An other thing I tried is using the OPENROWSET, this also give an unspecified error. And yes I have set the "Ad hoc distributed query" using the next statement:

sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
RECONFIGURE;
GO

So here I got my development box, where everything is working fine. But when I deploy to the test-server it has these connection-problems. Actually I don't know what to do or check anymore. I have been working and searching for several days now. Does anyone have some other suggestions I can try?

When you set up a linked server, you have the choice to re-use the credentials of the user, use a specific account or be anonymous.  When you tested it from the same machine, and SQL Server was running as a local account, it worked because it was able to re-use the admin privileges of you while you were logged-in.

To get it to work in a network scenario, things get tricky.  If you are using AD, you need to use AD (principalName) to configure the Kerberos trust between the SQL Server machine and the machine hosting the MSAccess file.  This usually takes some fiddling to get it right.  This TechNet article is long-winded, but contains a lot of config steps and suggestions: http://support.microsoft.com/kb/319723.

Also, you will need to set the server back to use the NETWORKING SERVICE account again so you can grant it permissions.  On the other machines, your SQL Server will be using a hidden account like [domain]\[MachineName]$.  If the MS Access DB is on the same machine as your SQL Server, you still need to grant (NTFS) read/write permissions on the MSAccess DB (file) to the accounts for the SQL Server process and to the network users.


Temporarily Disable Sql Replication Due to a business need, I may need to disable transactional replication in my environment (Sql 2k -> Sql 2008). Functionally, I understand this to mean that I will need to drop subscriptions and articles. Is getting the create scripts enough to restore replication back to original state when the conflicting need is addressed?

Thanks.

I tested this example using SQL Server 2000 (MS2000) as the publisher and SQL Server 2008 (MS2008 )as the subscriber.  On the publisher, one database called arp has several tables, but only the table users is included as an article in the publication. This example removes the subscription from the both servers and then puts it back again.

On the Subscriber MS2008

Removed the existing subscription from MS2008

USE arp
GO

EXEC sp_droppullsubscription @publisher='MS2000', @publisher_db='arp', @publication='arp_pub'

On the publisher MS2000

Dropped the Subscription

-- dropped subscription
exec sp_dropsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @article = N'all'

Dropped the Subscriber

-- On MS2000, dropped subscriber from MS2008 after removing it from MS2008
exec sp_dropsubscriber @subscriber = N'MS2008'

Later on, I recreate the subscription.

On the Publisher, MS2000

Add back in the subscriber

use [master]
exec sp_addsubscriber @subscriber = N'MS2008', @type = 0, @description = null, @security_mode = 1, @frequency_type = 64, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 4, @frequency_subday_interval = 5, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231

exec sp_changesubscriber_schedule @agent_type = 1, @subscriber = N'MS2008', @frequency_type = 4, @frequency_interval = 1, @frequency_relative_interval = 1, @frequency_recurrence_factor = 0, @frequency_subday = 8, @frequency_subday_interval = 1, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 0, @active_end_date = 99991231
GO

Add back in the subscription

use [Arp]
exec sp_addsubscription @publication = N'Arp_pub', @subscriber = N'MS2008', @destination_db = N'arp', @sync_type = N'Automatic', @subscription_type = N'pull', @update_mode = N'read only'
GO

On the Subscriber MS2008

Create the database

CREATE DATABASE arp;
GO

Add the pull subscription

use [arp]
exec sp_addpullsubscription @publisher = N'MS2000', @publication = N'Arp_pub', @publisher_db = N'Arp', @independent_agent = N'False', @subscription_type = N'pull', @description = N'', @update_mode = N'read only', @immediate_sync = 0

Add the agent

The @job_login is a ntlm user that has rights to the default snapshot folder.  That user also has access to the publication on the publisher

The snapshot folder is a shared folder accessible by both Publisher and Subscriber.

exec sp_addpullsubscription_agent @publisher = N'MS2000', @publisher_db = N'Arp', @publication = N'Arp_pub', @distributor = N'MS2000', @distributor_security_mode = 1, @distributor_login = N'', @distributor_password = null, @enabled_for_syncmgr = N'False', @frequency_type = 64, @frequency_interval = 0, @frequency_relative_interval = 0, @frequency_recurrence_factor = 0, @frequency_subday = 0, @frequency_subday_interval = 0, @active_start_time_of_day = 0, @active_end_time_of_day = 235959, @active_start_date = 20120214, @active_end_date = 99991231, @alt_snapshot_folder = N'', @working_directory = N'', @use_ftp = N'False', @job_login = N'MS2008\replication_user', @job_password = 'test123', @publication_type = 0
GO

On the publisher MS2000, I ran the snapshot job

The replicated table was once again visible on MS2008.


Oracle database patch update doubts Oracle Recommended Patches -- Oracle Database (Doc ID 756671.1) says the following 4 patches are recommended to be applied to 11.2.0.4 database.

Note:21744343.8 Combo of 11.2.0.4.5 OJVM PSU and 11.2.0.4.8 DB PSU (Oct 2015)

Note:21555791.8 Oracle JavaVM Component 11.2.0.4.5 Database PSU (Oct 2015) (OJVM PSU)

Note:21352635.8 11.2.0.4.8 (Oct 2015) Database Patch Set Update (DB PSU)

Note:19852360.8 Oracle JavaVM Component 11.2.0.4.1 Database PSU - Generic JDBC Patch (Oct 2014)

I have a newly installed 11.2.0.4 database with absolutely no patches applied on it.

Now should I go ahead and apply all the above mentioned recommended patches on the database? or should I apply any previously released patches before applying these?

If yes, is there an order in which the above patches is to be applied?

Also, I will be applying the above patches through OEM12c, is there anything I should take care in doing the same?

You should download only the combo patch - in essence it is the two other - DB and OJVM patches bundled in one archive. After that the patching sequence is as follows:

Apply the DB PSU, but do not run any post-install steps
Apply the Java PSU
Run the Java PSU post-install steps
Run the DB PSU post-install steps

Take a look at the following support note:

Oracle Recommended Patches -- "Oracle JavaVM Component Database PSU" (OJVM PSU) Patches (Doc ID 1929745.1)

In it is more detailed information about the OJVM patches.


Netezza LIKE operator not finding subsequent backslashes Netezza is able to find records when using the LIKE operator when the first character contains a backslash character.  But when subsequent characters contain a backslash character, it cannot.  How can I correct this?

Result set has records:

SELECT *
FROM TABLENAME
WHERE PATH LIKE '\some_path%'

Result set has 0 records:

SELECT *
FROM TABLENAME
WHERE PATH LIKE '\some_path\%'

I had to avoid the escape character in the pattern matching string (after the LIKE), and append ESCAPE '' to avoid using the backslash as an escape character.  In this example, I had _ characters in the literals, as well as \ characters.


Why would a primary key value change? I have been researching the concept of ROWGUID's recently and came across this question.  This answer gave insight, but has led me down a different rabbit hole with the mention of changing the primary key value.

My understanding has always been that a primary key should be immutable, and my searching since reading this answer has only provided answers which reflect the same as a best practice.

Under what circumstances would a primary key value need to be altered after the record is created?

If you were using a person's name as a primary key and their name changed you would need to change the primary key.  This is what ON UPDATE CASCADE is used for since it essentially cascades the change down to all related tables that have foreign-key relationships to the primary key.

For example:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAKey VARCHAR(200) NOT NULL
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonKey VARCHAR(200) NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonKey)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonKey, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonAKAKey, PersonKey)
VALUES ('Death', 'Joe Black');

A SELECT against both tables:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

Returns:

If we update the PersonKey column, and re-run the SELECT:

UPDATE dbo.People
SET PersonKey = 'Mr Joe Black'
WHERE PersonKey = 'Joe Black';

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonKey = pa.PersonKey;

we see:

Looking at the plan for the above UPDATE statement, we clearly see both tables are updated by a single update statement by virtue of the foreign key defined as ON UPDATE CASCADE:

click the image above to see it in more clarity

Finally, we'll cleanup our temporary tables:

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The preferred1 way to do this using surrogate keys would be:

USE tempdb;
GO

CREATE TABLE dbo.People
(
    PersonID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_People
        PRIMARY KEY CLUSTERED
    , PersonName VARCHAR(200) NOT NULL
    , BirthDate DATE NULL
) ON [PRIMARY];

CREATE TABLE dbo.PeopleAKA
(
    PersonAKAID INT NOT NULL IDENTITY(1,1)
        CONSTRAINT PK_PeopleAKA
        PRIMARY KEY CLUSTERED
    , PersonAKAName VARCHAR(200) NOT NULL
    , PersonID INT NOT NULL
        CONSTRAINT FK_PeopleAKA_People
        FOREIGN KEY REFERENCES dbo.People(PersonID)
        ON UPDATE CASCADE
) ON [PRIMARY];

INSERT INTO dbo.People(PersonName, BirthDate)
VALUES ('Joe Black', '1776-01-01');

INSERT INTO dbo.PeopleAKA(PersonID, PersonAKAName)
VALUES (1, 'Death');

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

UPDATE dbo.People
SET PersonName = 'Mr Joe Black'
WHERE PersonID = 1;

For completeness, the plan for the update statement is very simple, and shows one advantage to surrogate keys, namely only a single row needs to be updated as opposed to every row containing the key in a natural-key scenario:

SELECT *
FROM dbo.People p
    INNER JOIN dbo.PeopleAKA pa ON p.PersonID = pa.PersonID;

DROP TABLE dbo.PeopleAKA;
DROP TABLE dbo.People;

The output from the two SELECT statements above are:

Essentially, the outcome is approximately the same.  One major difference is the wide natural key is not repeated in every table where the foreign key occurs.  In my example, I'm using a VARCHAR(200) column to hold the person's name, which necessitates using a VARCHAR(200) everywhere.  If there are a lot of rows and a lot of tables containing the foreign key, that will add up to a lot of wasted memory.  Note, I'm not talking about disk space being wasted since most people say disk space is so cheap as to be essentially free.  Memory, however, is expensive and deserves to be cherished.  Using a 4-byte integer for the key will save a large amount of memory when you consider the average name length of around 15 characters.

Tangential to the question about how and why keys can change is the question about why to choose natural keys over surrogate keys, which is an interesting and perhaps more important question, especially where performance is a design-goal.  See my question here about that.

1 - http://weblogs.sqlteam.com/mladenp/archive/2009/10/06/Why-I-prefer-surrogate-keys-instead-of-natural-keys-in.aspx


How to use "BACKUP DATABASE", but the database is in a different server.? We have 3 servers ( SQL SERVER 2008 ). Is there a way to backup a database, connected in a different server?

Example: i'm on SQL1. I want to do something like this:

BACKUP DATABASE [SQL2].DATABASENAME
     TO DISK ='BACKUP SERVER'

If there's a way to do this via T-SQL It would be great.

EXEC SQL2.master.sys.sp_executesql N'BACKUP DATABASE dbname
  TO DISK = N''D:\path_on_remote_server\...'';';


Recover PostgreSQL database data I am using PostgreSQL in my project, Now by mistake I have deleted some records of one of my tables. Now is there any way to recover deleted Records in PostgreSQL?

May be this can help --> How to recover deleted records


After I re-installed SSRS 2014 (with default config) I get HTTP 404 when I try to access the ReportServer or Reports pages I have SQL Server 2014 Express with Advanced tools installed. I had a previous installation, but I uninstalled the SQL instance and Reporting Services and then re-installed them.

Right now, I'm unable to access http://localhost/Reports or http://localhost/ReportServer and I get a

  HTTP 404 Page not found error.

Initially, probably stupidly, I thought it was because IIS isn't installed, so I installed that, but that didn't do anything.

I also tried changing the virtual directory names then applying, but that also didn't work. What am I missing?

Well I'm not exactly sure what went wrong, but I reinstalled the whole SQL Server and then it worked again (as apposed to reinstalling only a few components last time.


Updating Database table from another machine I am trying to update my database using a batch file. Batch file is located on machine say ABC which should pick a text file from the local directory on ABC and upload that file to DATABASE located on say XYZ machine.

I am getting an error as

If you are using a remote database server, verify it has access to remote_basedir.

ERROR>:   Error is: [Oracle][ODBC][Ora]ORA-29913: error in executing    ODCIEXTTABLEOPEN callout
ERROR>: ORA-29400: data cartridge error
ERROR>: KUP-04040: file testfile.txt in TEST_9F86D0818 not found

I am calling schedules created in NAVICAT to upload .txt files to DATABASE.

There is an Oracle Support Note ORA-29913: Error in Executing ODCIEXTTABLEOPEN Callout (Doc ID 458620.1)  related to your errormessages. You have an external table (ORGANIZATION EXTERNAL) with data in the file 'testfile.txt'  in the Oracle directory "TEST_9F86D0818".

The support note says that this will happen if your os directories that contain the file testfile.txt does not have the appropriate permissions.

So let's check if this file does not exist or if there are problems with the os permissions on the file or the os directories where it resides.

Find out the path of the Oracle directory "TEST_9F86D0818"

select directory_path
from all_directories
where directory_name='TEST_9F86D0818';

Assume the directory_path retrieve in 1 is '/u01/app/oracle/data' or 'C:\u01\app\oracle\data' if you are on a Windows system. Then login to the database server and check if '/u01/app/oracle/data/testfile.txt' (C:\u01\app\oracle\data\testfile.txt) exists.
Let's assume the user that runs your Oracle Software on your database server is oracle. Usually this is the case on a Linux server. Then check if this user can read testfile.txt:

head /u01/app/oracle/data/testfile.txt > /dev/null

or

type C:\u01\app\oracle\data\testfile.txt > NUL

if you are on a Windows system.
If this generates an error message then this is the reason for your problem that you have to eliminate.
With a user that has access to directory and can execute th UTIL_FILE package (e.g. a dba user) connect to the databae with sqlplus and execute the following
command:

DECLARE
  V1 VARCHAR2(32767);
  F1 UTL_FILE.FILE_TYPE;
BEGIN
  F1 := UTL_FILE.FOPEN('TEST_9F86D0818','testfile.txt','R');
  UTL_FILE.GET_LINE(F1,V1);
  UTL_FILE.FCLOSE(F1);
exception
  when others then
    dbms_output.put_line('ERROR: ' || SQLCODE
      || ' - ' || SQLERRM);
    raise;
END;
/

If an error is rased, this is the reason for your problem that you have to eliminate. If no error is raised show us the create statement of your external table.


Current SQL Server services configuration I know I can use SQL Server Configuration Manager to determine 1) Which services are currently running and 2) which services are set to auto start. However, I have several dozen servers I manage. I'd like to run a quarterly check to make sure all services are still set to auto start and to see which services are currently on. Is there a table or procedure that will give me this information?

I would use Powershell for this task.  Here's a function that leverages Get-WMIObject, WQL, and some syntax to take a list of servers to analyze.

function Get-SQLServiceInfo{
    param([string[]] $servers)
    $return = @()
    $wql = "SELECT * FROM Win32_Service WHERE name like '%SQL%' and (startmode != 'Auto' or state != 'Running')"

    foreach($server in $servers){
        $return += Get-WmiObject -ComputerName $server -query $wql | select name,startmode,state
    }

    $return
}

Get-SQLServiceInfo 'localhost'

Now, there are several ways to approach the matter of scanning all your SQL instances.  I like to use Central Management Server for this task.  The code I would use looks something like this(with the call to the above function):

$CMS=’CMSSERVER’
$servers=@((dir "SQLSERVER:\SQLRegistration\Central Management Server Group\$CMS").Name)

$servers+=$cms

Get-SQLServiceInfo $cms

So you could run this report manually, or implement it in some additional code to build and email reports out to you.  And with it automated, I'd actually say you could have this run once a day instead of quarterly.


Best storage engine for huge tables with mostly update operations (74 million records, MyISAM table size 7.6G) What will be the best storage engine for table with 74 million records, currently stored as MyISAM table size 7.6G.

Operations are mostly select and update. DDL operations (ALTER ADD COLUMN) are occasionally performed.

Joins and alter operations on MyISAM tables are extremely slow.

Will  InnoDB with large innodb_buffer_pool_size be optimal for this scenario or shall I go with In-memory tables?

For virtually all use cases, InnoDB out-performs MyISAM.

DDL operations have been greatly sped up in 5.6 and 5.7 for InnoDB.

InnoDB will be 15-20GB for that table.  Please provide SHOW CREATE TABLE for critique of how to save space.

Please provide some of you SELECTs for critique on how to improve JOIN performance.  See also Converting MyISAM to InnoDB.

After switching to InnoDB, set key_buffer_size = 20M and innodb_buffer_pool_size to 70% of RAM.

InnoDB will 'cache' data and indexes, so it is often as fast as "in memory tables".


Copy csv file into different schema I need to import data from a csv file  which from database a schema a into another database b schema b.
I used the pgAdmin running the export and when I import the data using the same tool pgAdmin and/or directly from psql prompt, I get an error:

permission denied for schema a

both tables from schema a and schema b are the same information definition and attributes.  Not even csv from Oracle's table.
Just a csv, do I still needs to specify schema to schema and how?
"C:\\Program Files (x86)\\pgAdmin 4\\v4\\runtime\\psql.exe" --command " "\\copy b.sc_g28 (receipt_number, rep_ins_attny_id, rep_state_num, rep_volag, rep_rep_code, rep_last_name, rep_first_name, rep_middle_name, rep_firm_name, rep_street, rep_street_2, rep_city, rep_state, rep_zip, rep_province, rep_postal_code, rep_country, mig_filename, mig_insert_dt, mig_modified_dt) FROM 'C:/cidr/CIDR_D~1/EXP_SC~1.CSV' CSV HEADER QUOTE '\"' ESCAPE
ERROR:  permission denied for schema a

There is a trigger on both a.sc_28 and b.sc_g28.
I have 2 instances (databases) pscidr and cidr.
pscidr has 3 schemas and owned by postgres.
cidr has 3 schemas and each schema owned by each user/role.

I have no problem to export Oracle's table to csv and import to postgres pscidr schema's table without disable constraint but I can't import either one from Oracle's table and/or a.sc_g28 to b.sc_g28 of cidr db.  Keep receive the "permission denied a.sc_g28".  An a.sc_g28 is from pscidr database.
Can't disable the constraint b.sc_g28's pkey or turned deferrable/deferred on from pgAdmin.
alter table b.sc_g28 disable constraint sc_g28_pkey;

ERROR: syntax error at or near "constraint" LINE 2: alter table b.sc_g28 disable constraint sc_g28_pkey; ^ SQL state: 42601 Character: 38

or
ALTER TABLE b.sc_g28 ALTER CONSTRAINT sc_g28_pkey DEFERRABLE INITIALLY DEFERRED;

ERROR: constraint "sc_g28_pkey" of relation "sc_g28" does not exist SQL state: 42704

I try to alter trigger fine without error
ALTER TABLE cidrdata.sc_g28 DISABLE TRIGGER ALL;

but still can't import csv file into b.sc_g28.
ERROR:  permission denied for schema a.

I found the problem and fixed it.
I grant usage on table to role/user

grant usage on schema schema_name to role/user;

Thank you for all input.


Want to delete then insert with single statement using a CTE in Postgres I want to delete then insert with single statement using a CTE in Postgres.  We could use an explicit transaction and 2 statements, but want to avoid that if possible.  I don't know if there will be zero or 1+ rows deleted, so I don't think I can use a single WHERE EXISTS or WHERE NOT EXISTS to ensure that the delete statement in the CTE runs first.  Here's something like what I envision:

WITH deletions AS (
   DELETE FROM foo WHERE a = 'abc' and b = 2
)
INSERT INTO FOO (a, b, c) VALUES ('abc', 2, 'new value')

but I need a way to force the CTE to run first.  There's no pk on the table that would create issues with attempting to update the same record twice in the same transaction.

Without a UNIQUE constraint standing in the way, your original query works just fine. See below.
I would throw in a separate CTE to provide all input values once:
WITH input(a, b, c) AS (VALUES ('abc', 2, 'new value'))  -- provide values once
   , del AS (
   DELETE FROM foo AS f
   USING  input i
   WHERE  f.a = i.a
   AND    f.b = i.b
   )
INSERT INTO foo (a, b, c)
TABLE input;

The DELETE cannot delete rows from the INSERT in the same statement, as both see the same snapshot of underlying tables. Meaning, the DELETE cannot see the rows entered by the INSERT in the other CTE. Both are executed at the same time, virtually. Related:

Why can't rows inserted in a CTE be updated in the same statement?

That's also the reason why this cannot work with a UNIQUE index on (a.b). Uniqueness is enforced at all times. The INSERT still sees rows being deleted in the other CTE. The obvious alternative would be an UPSERT. But that cannot be the case, since you mentioned that there is no PK and the DELETE can delete 0-n rows.
As a_horse commented: would work with a deferrable constraint, though. See:

Constraint defined DEFERRABLE INITIALLY IMMEDIATE is still DEFERRED?

But deferrable constraints are considerably more expensive and don't work with FK constraints, nor as arbiter in UPSERT statements ...
Note that a free-standing VALUES expression may require explicit type casts. See:

Casting NULL type when updating multiple rows

Related:

How to use RETURNING with ON CONFLICT in PostgreSQL?

That said, I don't see how this is superior to a DELETE and a separate INSERT in a single transaction - which also works with UNIQUE constraints. You commented:

It's a lot easier to use db parameters with a single statement and our db library.

If the statement is used a lot, consider a function:
CREATE OR REPLACE FUNCTION f_foo_delins(_a text, _b int, _c text)  -- actual types
  RETURNS void LANGUAGE sql AS
$func$
   DELETE FROM foo
   WHERE  a = _a
   AND    b = _b;

   INSERT INTO foo ( a,  b,  c)
   VALUES          (_a, _b, _c);
$func$;

Then the call is simple:
SELECT f_foo_delins('abc', 3, 'new value');

If you only need it for some sessions, a temporary function might be an option. See:

How to create a temporary function in PostgreSQL?

Or a prepared statement with the above CTE:
PREPARE foo_delins(text, int, text) AS
WITH del AS (
   DELETE FROM foo
   WHERE  a = $1
   AND    b = $2
  )
INSERT INTO foo ( a,  b,  c)
VALUES          ($1, $2, $3);

Call:
EXECUTE foo_delins('abc', 4, 'new value');

Most languages have their own implementation of prepared statements using libpq ...
Prepared statement and function know their input types. Explicit type casts are not required (except special cases).
Related:

What is the difference between prepared statements and SQL or PL/pgSQL functions, in terms of their purpose?


How to represent directed graph with multiple parents? http://dirtsimple.org/2010/11/simplest-way-to-do-tree-based-queries.html provides an algorithm for inserting and deleting from a Closure Table.

I'd like to model a similar data structure, except that nodes may have multiple parents.

Given:

If we remove [B, C] I expect to end up with:

and if we remove node B I expect to end up with:

However, if you use the author's algorithm for removing links or nodes you will notice that it tags [D, C, 1] for deletion, which is undesirable.

What I've tried so far

I have tried adapting the original data structure by adding a references column that indicates how many ways there are to travel between two nodes. In the above example, you can travel from A to C either through B or through D. The idea would have been that when B gets removed, the path from A to C gets kept and the reference count decreases from 2 to 1. It was nice in theory, but I couldn't figure out how to get the implementation working and now I wonder if it's at all possible (the data structure might not contain enough information to figure out which rows to remove).

What I'm asking

How would you adapt Closure Tables to support multiple parents? What alternative data structures would you recommend? https://stackoverflow.com/q/4048151/14731 contains an exaustive list of such data structures, but it's not clear which ones support (or are best for) multiple parents.

Usually create a nodes table and a relationships table.  Directed graphs are't really hierarchical and they can have loops which makes querying harder.  But if you think of a DAG as being a generalised tree (I.e. a tree that allows multiple parents but is still strictly hierarchical) and a directed graph as being a generalised DAG (i.e. like a DAG but not strictly hierarchical) things get easier.

So for a very simple, PostgreSQL solution we might do something like:

CREATE TABLE node (
    id serial primary key,
    payload jsonb not null
);

CREATE TABLE relationship (
    id serial primary key,
    relationship_type text not null,
    from_node int references node(id) not null,
    to_node int references node(id) not null,
    payload jsonb not null
);

Then you can query something like this:

with recursive dg as (
    select n.id as node_id, null::Int as parent, array[n.id] as path
      from node n
    union all
    select to_node, from_node, path || to_node
      FROM relationship
      JOIN dg on dg.node_id = from_node AND NOT from_node = ANY(path)
)
select * from dg;


Creating multiple windows login on a single databae I am able to create a window login on a database I created. This windows authentication login is based on a windows user that I created earlier, but I don't seem to know how to login into SQL server with the windows authentication login I created. Am I doing things the right way or is there something else that I need to do? Please help me guys.
I want to have two windows authentication logins into a single database with both logins having different privileges

Test your Windows logins with SQL Server Management Studio

At a very basic level Let's test your logins with SQL Server Management Studio. You need to first log into the Windows machine with the login you created, and then login to SQL Server Management Studio with that same Windows login.

Once you've logged in through Windows, open SQL Server Management Studio and register SQL Server via the connect button Shown in Figure A. Here you register your SQL Server specifically for that login. Next, in Figure B you can connect specifically to SQL Server with the Windows login. The Connect To Server dialog box will automatically have the Windows login information provided that you've selected Windows authentication--but you may need to type the SQL Server name correctly.

Figure A: Register SQL Server for your Windows login

Figure B: Test Windows authentication on SQL Server with your Windows login

p.s. With SQL logins, you're saved the hassle of logging in and out of Windows to test those logins. But I guess you could take a shortcut, hold down the shift key and right-click the SQL Server Studio and select Run as different user--to shortcut the Windows login/logout process.

I've noticed that Run as different user selectively shows up in some places and not in others. For example, in Windows Server 2012's search menu, shift-right clicking SQL Server Studio will not reveal this command, but if I pin Studio to my task bar and shift-right click it's there.

Your question is kind of general (as there are numerous ways to login to SQL Server with Windows authentication)--I hope this explanation is a value to you and is what you needed.


enable the data access for the current server - is this operation logged somewhere? I have a similar question related to the same subject:

security concerns over enabling data access for the current server

BEGIN TRY
    PRINT  @@SERVERNAME
    PRINT  DB_NAME()
    PRINT 'enable the data access for the current server'
    EXEC sp_serveroption @server = @@servername
                        ,@optname =  'data access'
                        ,@optvalue =  'TRUE'
END TRY
BEGIN CATCH
        PRINT '--EXCEPTION WAS CAUGHT--' + CHAR(13) +
              'THE ERROR NUMBER:' + COALESCE(CAST ( ERROR_NUMBER()  AS VARCHAR), 'NO INFO') + CHAR(13)

        PRINT 'SEVERITY: '        + COALESCE(CAST ( ERROR_SEVERITY()  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'STATE: '           + COALESCE(CAST ( ERROR_STATE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'PROCEDURE: '       + COALESCE(CAST ( COALESCE(ERROR_PROCEDURE(),'NO INFO')  AS VARCHAR), 'NO INFO') + CHAR(13) +
              'LINE NUMBER: '     + COALESCE(CAST ( ERROR_LINE() AS VARCHAR), 'NO INFO')  + CHAR(13)

        PRINT 'ERROR MESSAGE: '
        PRINT  CAST ( COALESCE(ERROR_MESSAGE(),'NO INFO')   AS NTEXT)

END CATCH
GO

Before anyone says anything about my NTEXT above, I would like to say I am not using this anymore I use this procedure from Solomon Rutzky (with thanks) instead.

No, calls to sp_serveroption are not logged to the default trace, the system_health XE session, or the SQL Server error log. If you want to audit these, you may be able to do it with a server-level audit. Or don't give everyone permissions to do it...


Dynamic Ports are being set to different ports On one of our instances of SQL Server (12.0.5571.0 if you really need to know) we use multiple IP addresses to connect.

127.0.0.1 - Dynamic Port set to 0. No static port set
172.16.0.5 - IP Address for the server that the SQL Server is on. Dynamic Port set to 0 (autgenerating). No static port assigned
172.16.0.6 - IP address for the SQL Server instance (that way teams just have to hit the IP address and not know which server they really are on). No Dynamic port assigned. Static port 1433

I've been playing with puppet and have been messing with the registry settings

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp

to see if I could use puppet to automate the configuration of IP addresses and port assignments. I created a new registry path path called 'IP172016000006'

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP172016000006

which I templated from

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL12.InstanceNameX\MSSQLServer\SuperSocketNetLib\Tcp\IP1

but now whenever I start the instance of SQL Server my dynamic ports for 127.0.0.1 and 172.16.0.5 are different.

For example, I will stop the instance of SQL Server, go into

'SQL Server Configuration Manager'

navigate to the network configuration for the instance

tcp/ip

IP addresses

and set the 'TCP Dynamic Ports = 0 for the IP addresses 127.0.0.1 and 172.16.0.5.

After I restart SQL Server, the instance will be listening on 127.0.0.1:43340 and 172.16.0.5:43341 (it seems to always be 1 more then the other port)

I've read the documentation on SQL Server Browser and found that SQL Server Browser will return the first port that it finds.

I've also read the BOL for setting a static port for SQL Server, which funny enough covers how to set dynamic ports. The BOL says that SQL Server will try to connect to the port that is specified, but if it fails it will get a new port. That makes sense for once in a long while, but SQL Server is consistently having inconsistent dynamic port assignments.

I've removed the offending registry values, but SQL Server is still having troubles setting the dynamic ports to be the same. I've restarted the whole server. I've set the TCP dynamic ports to be '0' multiple times.

How do I get consistent dynamic ports again?

Windows Server 2012 R2
SQL Server 12.0.5571.0
8 Instances of SQL Server installed - 3 of which are clustered (it's a test config box for us DBAs; don't judge too harshly)

The point of dynamic ports is that you don't care what port the server runs on.  SQL Server Browser directs clients to the dynamically chosen port.

If you don't want dynamic ports, just assign a port at the outset, then it will never change.


Alter table force fully I'm trying to decrease the precision of a column from NUMBER(25) to NUMBER(10) but when I do I got error saying column to be modified must be empty to decrease precision

I do not have any data in the column length more than 10. So I want to change this precision forcefully.

How can I update this forcefully? Is it possible?

As the error message states, you can't just modify the number column to reduce its precision and scale.

Here is one of the workarounds I would use for this situation.

Here is my table.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(30)

Add another column with the number data type with specified precision.

SQL> alter table t1_num add id2 number(10);

Table altered.

Update the new column with the data from the old column.

SQL> update t1_num set id2=id;

1 row updated.

SQL> commit;

Commit complete.

Set the old column to null to change its specification.

SQL> update t1_num set id=null;

1 row updated.

Modify the column

SQL> alter table t1_num modify id number(10);

Table altered.

Update the column with the original data.

SQL> update t1_num set id=id2;

1 row updated.

SQL> commit;

Commit complete.

Drop the temp column

SQL> alter table  t1_num drop column id2;

Table altered.

SQL> desc t1_num;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(10)


Table join inheritance I create db with Person, Client, Employee, Manager, Developer. I'm interested what is the best way to put the data in tables. In which cases should programmer unite Client in table hierarchy with EmployeeCategory for next using concept of Table join inheritance (because they have common attributes like firstName, lastName, email..) and in which cases programmer should hold Client table separate from Employee hierarchy (despite that Client has common attributes with Person)?

If you have common attributes across non-exclusive tables, you should consider normalizing your tables.

It appears that Client, Employee, Manager, and Developer are all sub-types of the Person entity.  As such any attributes common to all types should be in the person entity, and attributes for the other entities should depend only on that sub-type.

I would expect Managers and Developers are often or always Employees.

Programmers while need to join the tables need to access the data required for their use case.  It may be appropriate to select from only from the subtype tables, or only from the Person table.


Position of a race result using database query? The scenario is based around a race event (running/jogging)

Is there a way of getting a position of a person by using their race results. For example the quickest person to finish a race would be in a seperate table of race position with the value of 1. The second quickest person would get a result of 2, and so on. Just by using the race result of that individual (Data Type - Time).

You can use a variable and CROSS JOIN it with your table. You should increment the variable value for each returned record.

Keep in mind you need to set the correct order on your subquery.

order by duration asc

I've set up the next example:

drop table if exists course;
create table if not exists course(id int, duration time);
insert into course values
(1, '01:20:00'),
(2, '01:21:02'),
(3, '01:19:02'),
(4, '01:22:02'),
(5, '01:17:05'),
(6, '01:23:02'),
(7, '01:12:02'),
(8, '01:16:02'),
(9, '01:15:02');

select id, duration, @position := @position +  1 as position
from
       (select @position := 0) x,
       (select id, duration
        from course
        order by duration asc) y;

drop table if exists course;

And this is the result:

| id | duration | position |
|---:|---------:|----------|
| 7  | 01:12:02 | 1        |
| 9  | 01:15:02 | 2        |
| 8  | 01:16:02 | 3        |
| 5  | 01:17:05 | 4        |
| 3  | 01:19:02 | 5        |
| 1  | 01:20:00 | 6        |
| 2  | 01:21:02 | 7        |
| 4  | 01:22:02 | 8        |
| 6  | 01:23:02 | 9        |

Rextester here.


Can I change which index a primary key uses? I have a table with a non-clustered index on the id, and a clustered index on some other fields. The fields of the clustered index are non-sequential, and are frequently modified. I'd like to change the table to use the auto-increment id column for the clustered index.

I can't explicitly drop and recreate the index on the id, as it is used to enforce the PK.
I can't drop the PK, because it is referenced by foreign keys.
I would like to avoid dropping the foreign keys, because re-adding them will result in a constraint check on the existing rows, and there are a lot of rows.

I thought the following steps might work:

Drop the existing clustered index
Create a clustered index on the id
Somehow tell the table to use that index for enforcing the PK constraint
Remove the old non-clustered index
Rename the clustered index to match the convention

Is this possible? And if so, is it a good idea?

There are some limitations/restrictions when modifying existing Primary Keys (PK), Clustered Indexes (CI) and Foreign Keys (FK).

General Information

When a PK is created and no CI exists, the PK will create a CI if no other index is referenced in the constraint definition.

  The Database Engine automatically creates a unique index to enforce the uniqueness requirement of the PRIMARY KEY constraint. If a clustered index does not already exist on the table or a nonclustered index is not explicitly specified, a unique, clustered index is created to enforce the PRIMARY KEY constraint.

Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

You can't drop/modify an existing PK if it is being referenced by a FK.

  A PRIMARY KEY constraint cannot be deleted if the following exist:

  If it is referenced by a FOREIGN KEY constraint in another table; the FOREIGN KEY constraint must be deleted first.
  The table has a PRIMARY XML index applied on it.

Reference: Creating and Modifying PRIMARY KEY Constraints (Technet)

Solution

You will have to drop the existing constraints (FK and PK) and/or indexes to create the solution you are aiming for.

This can be observed by reading the following articles:

How to convert clustered primary key to non-clustered without dropping referring foreign keys in SQL Server 2005 (Stackoverflow)
How to change the primary key to be non-clustered? (Stackoverflow)
Modify Foreign Key Relationships (Microsoft Docs)
Creating and Modifying PRIMARY KEY Constraints (Technet)


How to model weak entity in ER diagram? I was going through the weak entity concepts and was trying to model the relation Hotel has many rooms. I have come up with the partial solution presented below but i'm having a problem to uniquely identify the tuples in the room entity related to the specific hotel.
My Assumption

Hotel(hotel_number, name) is a strong entity.
Rooms(room_number, bed) is a weak entity whose existence depends on the existence of the hotel.

1. Hotel entity's table

hotel_id
name

125
n1

132
n2

173
n3

103
n4

193
n5

2. Room entity's table

room_number
beds

101
3

101
2

102
1

103
6

There are two hotels with hotel_id 125 and 132. Hotel with hotel_id 125 has room numbered 101 with 3 beds and another hotel has room_number 101 with 2 beds. So how can i model the given relation in such condition as there's no pk for the weak entity i.e. room ??

Room should have PK of (hotel_id,room_number).
Weak entities have compound keys with the strong entity key/foreign key as the leading column(s).
There are people who disagree with this approach, but they are wrong.

How can i find the room belonging to specific hotel which has the different number of beds limiting the attributes in the domain of my problem

Something like:
select *
from rooms
where hotel_id  = 1001
  and beds = 3


why sp_ExecuteSql output parameter returns null when procedure shows correct value in results pane declare @Table nvarchar(20) = 'tblsteps';
declare @IdFieldName nvarchar(20) = 'stepid';
declare @NumberFieldName nvarchar(20) = 'stepnum';
declare @recid nvarchar(10) = 3;

declare @sql nvarchar(200);
declare @params nvarchar(500);
declare @number_out nvarchar(20)

set @sql =  'SELECT ' +  @NumberFieldName + ' FROM ' + @table + ' WHERE ' + @IdFieldName +' = '+ @recid;

set @params= N'@number_out nvarchar(20) OUTPUT';

Execute sp_executesql @sql,@params,@number_out OUTPUT;

select @number_out as sp_Out;

select @sql as sqlString shows desired sql string;

Execute sp_executesql returned correct answer in results pane;

select @numberout as sp_out always returns NULL;

You never actually set the value of the output parameter in your query. In order to return the value as an output parameter you need to assign the result of the query to the parameter itself.

Try this:

set @sql =  'SET @number_out = (SELECT ' +  @NumberFieldName
                              + ' FROM ' + @table
                             + ' WHERE ' + @IdFieldName +' = '+ @recid + ')';

Or you could do:

set @sql =  'SELECT @number_out = ' +  @NumberFieldName
          + ' FROM ' + @table
          + ' WHERE ' + @IdFieldName +' = '+ @recid;


Confused about what to use to create the data I want to create a database for myself. The data has complex grouping. I don't know which system to use to create my database. The data is in groups and sub-group forms, it almost has 5-6 layers of subgroups. I tried SQL but such complex sub-groups are not possible in it.

I really need to create the database. Hope for some good advice.

This is the type of grouping I want to do and I am searching for a kind of system that can save such data in a way that when I want to filter the data to find something I can get the results.

EG: if I search for high toughness properties of a material then MS and high carbon steel should appear.

Hope this helps.

You can use PostgreSQL's inheritance feature.


MySQL connection process slow for non-root user I'm using Percona server 5.6 on Linux.

When I connect to MySQL from the command line as the root user, it connects immediately.

I have also created a new user in the database, but when I try to connect using the newly created user, it pauses for 30 seconds and then it connects.

What could be the problem?

FYI: This question posted by me is also linked to this question if that could help is resolving this question

Another question which might help in solving this question

It looks that server lookup is taking time while searching for the host over the network and in order to minimize the lookup time add the entries in hosts file as entries in hosts file (/etc/hosts) does minimize the lookup time.


Is attaching a database on the same server under a different database name possible? I recently offered to help a friend with some development. She isn't a technical person and it turned out what she needed was more a DBA/SQL developer. She gave me the IP and password to this server.

There is no development server. Only production. So I have to be extra careful with everything I do.

I accidentally altered a stored procedure and now I'm trying to get it back. I had the old employees send me the lastest files. They were in rar format and after I unzipped the DB folder, there was only a file inside(no extension). Looking at the contents led me to believe it is a SQL Server .mdf file. This leads me to my question.

Can I attach this mdf file to a new database I just created? Without it trying to take down the other instance? It seems like I could but since this is production, I have to be EXTRA careful.

The server is Windows Server 2008 R2, with SQL Server Management Studio 2012 installed.

You can first check to make sure it actually is a valid MDF file using DBCC CHECKPRIMARYFILE. It is an undocumented command but can be useful at times.

If that turns up nothing or shows that it is not a mdf file you can use the RESTORE HEADERONLY to see if it might be a backup file.

To your specific question, yes you can attach this as a different database name as long as the physical file is not in the same directory on the server as the original. So in your case I would just create a sub-directory in your data folder for this file and the future LDF file that would be created.


Select rows with multiple AND conditions that override eachother I have the following table schema:

book_id    |   attribute_id    |    attribute_value
____________________________________________________

1          |        35         |         Sci-Fi
2          |        35         |         Drama
3          |        35         |         Thriller
4          |        35         |         Romance
1          |        36         |         Author1
2          |        36         |         Author2
3          |        36         |         Author3
4          |        36         |         Author4
1          |        37         |         $300
2          |        37         |         $100
3          |        37         |         $600
4          |        37         |         $700

I tried to fetch results that have multiple conditions like in the query below from the table 'books'

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') AND attribute_id = 36 AND attribute_value IN ('Author2')

I know this is not working because I cannot have multiple AND conditions on the same column. I was looking for the following output of the query above

book_id  |
-------- |
   2     |

I tried using UNION and separating the query in 2 different queries:

SELECT book_id FROM books WHERE attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')
UNION
SELECT book_id FROM books WHERE attribute_id = 36 AND attribute_value IN ('Author2')

But obviously this is returning the same results as using the query below (replacing AND with OR

SELECT book_id FROM books WHERE (attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller')) OR (attribute_id = 36 AND attribute_value IN ('Author2'))

I tried INNER JOINING the table on those conditions and I did not had any success. Not sure what I am missing. I am looking for a solution that would work with more attributes not only 2 (35,36). Any ideas?

Depending on what you need

SELECT book_id
FROM books
GROUP BY book_id
-- Either 'Drama' or 'Thriller', at least one
HAVING SUM( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )

or

SELECT book_id
FROM books
GROUP BY book_id
-- Both 'Drama' and 'Thriller'
HAVING SUM( attribute_id = 35 AND attribute_value = 'Drama' )
   AND SUM( attribute_id = 35 AND attribute_value = 'Thriller' )
   AND SUM( attribute_id = 36 AND attribute_value = 'Author2' )

Also you may add

WHERE ( attribute_id = 35 AND attribute_value IN ('Drama', 'Thriller') )
   OR ( attribute_id = 36 AND attribute_value = 'Author2' )

in both (this is not misprint!) queries. This will decrease the amount of records grouped using index if exists, but will add filesort instead. If the index not exists this conditions add is preferred.


What type of database can I use to store mutual scores between every combination of users? Let's say I have an application that calculates and stores a mutual score between every combination of users.

I'm currently using Postgres to store this information.

So if my system had users with id 1, 2, and 3 that table might look like

CREATE TABLE scores (id,user_id,friend_id,score)
AS ( VALUES
    ( 1, 1, 2, 15.2 ),
    ( 2, 2, 1, 15.2 ),
    ( 3, 1, 3, 19.7 ),
    ( 4, 3, 1, 19.7 ),
    ( 5, 2, 3, 21.0 ),
    ( 6, 3, 2, 21.0 )
);

As you can see, the data itself is pretty simple.

Every combination of users has a mutual score calculated between them (the derivation of that score or what it means isn't important here) and I store it in the table.
Because I could look up either user, I have to store the pairings "both ways" (e.g. 1 -> 2 and 2 -> 1)

The problem is that this increases quadratically. With 10,000 users there are 100 million records in this table. With 100,000 users (which is a reasonable scope for my app) there would be 10 billion records.

So my question is - surely Postgres can't be the most scale-able way to store this information right? I don't need access to it constantly, just periodically when I'm running a batch job so I'm happy to store it in some separate type of DB on a separate server.

What kind of DB specializes in storing this type of paired data and can perform quick reads?

For example, I commonly query for all the ranked friends of a given user with SELECT friend_id FROM my_table where user_id = ? ORDER BY score DESC so I'd like to run the equivalent of that query on this new database.

Thanks!

P.S Someone had suggested a Graph DB - didn't know if that would apply here.

Because I could look up either user, I have to store the pairings "both ways"

Well, that's not true. You could normalize the data because the relations aren't directed anyway.

CREATE TABLE scores (
  user_id   int,
  friend_id int,
  score     int,
  CHECK ( user_id < friend_id ),
  PRIMARY KEY (user_id, friend_id)
);

INSERT INTO scores (user_id, friend_id,score)
VALUES
  ( 1, 2, 15.2 ),
  ( 1, 3, 19.7 ),
  ( 2, 3, 21.0 );

Now you can find the relation easy.

SELECT *
FROM scores
WHERE user_id = 1
  AND friend_id = 2;

If you don't want to put the rule in the app, just rewrite the query into.

SELECT *
FROM scores
WHERE (user_id, friend_id) =
  ROW( least(1,2), greatest(1,2) );


When autovaccum will analyze? Let's suppose that a table foo became eligible for autovacuum analyze, e.g. I have inserted a number of rows into foo that exceeds autovacuum_analyze_scale_factor * number of rows + autovacuum_analyze_threshold effective threshold. I can't figure out which are the conditions that should be satisfied in order to have autovacuum performing the analyze on foo.
Will autovacuum analyze run for table foo only if there are no transactions? Or it can run even during active transactions with the conditions that: (1) no transaction is reading a previous version of table foo and (2) the autovacuum analyze can gain SHARE UPDATE EXCLUSIVE lock on table foo?
These questions are related to particular case. I peform a significant number of INSERTs into foo in one transaction and then some UPDATEs on foo in a second transaction. I need the autovacuum analyze to run before the 2nd transaction in order to have an up to date statistics of foo for my query planner better estimates. Are there a way to guarantee that the autovacuum analyze will run before the 2nd transaction? Maybe to sleep (I'm running both transactions from Java app) couple of milliseconds between transactions?

The formula is correct.
The number of live rows is taken from the reltuples column in pg_class, and the result of the formula is compared to n_mod_since_analyze from pg_stat_all_tables.
Note that autovacuum_analyze_scale_factor and autovacuum_analyze_threshold can be overridden by storage parameters on th table.
If all available autovacuum workers are running (autovacuum_max_workers), you may have to wait.
Analyze only takes an ACCESS SHARE lock on the table (it only reads), so it can run concurrently with almost anything. Like a SELECT statement, it will see the rows that are visible to it.
If you need a table to be analyzed between an INSERT and an UPDATE, don't wait for autoanalyze to run. Explicitly start ANALYZE on the table.


DB2 Express-C 10.1 database move to DB2 Work group Server/Enterprise Edition I have not done such migration before and I want to know if I am going to face such issues when moving my database (tables,views,table-spaces,functions,procedures,etc.) to other DB2 distribution.

If there will be some problems, will it be better to download the trial version of "Enterprise Edition" for example and to develop my project on it?

If I understand you right, you are going from DB2 10.1 Express-C edition to DB2 10.1 Work Group Server Edition or Enterprise Edition.

If that is case then you shouldn't have any issues. The binaries for DB2 are basically the same across editions. It is just a matter of a license file that enables or disables certain features and enables/disables use of more/less CPU and memory. (The only exception to this I have seen is the DB2 Spatial Extender-for GIS type storage-which normally would be in Express-C, but somehow isn't. So that one you have to download (for free) from IBM's site. But if you did a regular Enterprise Edition install, it is installed.)

Anyway, if you are "migrating" the entire instance, I take that to mean you just need to apply the Work Group Server edition license file or the Enterprise Edition license file to the database instance and Express-C will automatically turn into one of the other editions. If you are talking about moving data from an Express-C environment to another environment, there should be even less issues (pending the database version is the same). If that is the case you could do a backup from one and a restore to the other.


Concurrent backups (full and transaction) and testing recovery model My intent is to test my recovery model and setup log shipping to replicate a database on another server.

I have a database called AdventureWorks on a primary sql server. Presently SQL Backup performs a full backup on this database every morning @ 6am and on the average takes 3 1/2 hrs (210 minutes) to complete. A separate job runs every 15 minutes and performs a log backup on the same database.

So, I want to restore a full backup from earlier that morning and then all the log backups since that full backup completed how do I know which transaction log backups to select? Is there any danger to applying transaction log backups from 915am to present?

Working out which Log Backup to restore is all based on LSN's. You have to apply all log backups since the full (or differential, but you didn't mention them) backup, including log backups taken while the full backup was in progress.

There is no danger in trying to restore a log backup out of sequence, it just won't work. Kin answered above how to find the actual log backups you need to restore.

It's important that you have a continuous chain of log backup files. If you are missing one, or if it's corrupt then you can't restore anything after that point.


SQL Always on High Availabity Groups Multi Subnet SQL 2017 Standard in SQL always on two node Basic AG.
There are two DNS records for the listener with the different subnet IP's
The servers are across subnets and we have hit issues with an application seeing to randomly time out when talking to the database, other times it responds in milliseconds. After running Wireshark we can see the application is trying to talk to the database via the failover partner listener IP address, the none readable replica.
This information I have read explains the 21 second timeout.
https://techcommunity.microsoft.com/t5/sql-server-support/improved-multisubnet-listener-behavior-with-newly-released-sql/ba-p/318664
MultiSubnetFailover=TRUE seemed like the resolution but the application is using an older version of .NET is there anything else I can do to resolve or work around this?

As mentioned in the docs here the issue is explained:

MultiSubnetFailover=True Not Supported by NET Framework 3.5 or OLEDB
Issue: If your Availability Group or Failover Cluster Instance has a
listener name (known as the network name or Client Access Point in the
WSFC Cluster Manager) depending on multiple IP addresses from
different subnets, and you are using either ADO.NET with .NET
Framework 3.5SP1 or SQL Native Client 11.0 OLEDB, potentially 50% of
your client-connection requests to the availability group listener
will hit a connection timeout.

And there are two main workarounds:

Workarounds: We recommend that you do one of the following tasks.
If do not have the permission to manipulate cluster resources, change
your connection timeout to 30 seconds (this value results in a
20-second TCP timeout period plus a 10-second buffer).
Pros: If a cross-subnet failover occurs, client recovery time is
short.
Cons: Half of the client connections will take more than 20 seconds
If you have the permission to manipulate cluster resources, the more
recommended approach is to set the network name of your availability
group listener to RegisterAllProvidersIP=0. For more information, see
"RegisterAllProvidersIP Setting" later in this section.
Pros: You do not need to increase your client-connection timeout
value.
Cons: If a cross-subnet failover occurs, the client recovery time
could be 15 minutes or longer, depending on your HostRecordTTL setting
and the setting of your cross-site DNS/AD replication schedule.

In my opinion half of the connections taking more than 20 seconds is far from ideal and I would look into changing the RegisterAllProvidersIP to 0 and HostRecordTTL settings according to your needs. Know that lowering this value too much will increase DNS lookup requests. You could also look into monitoring failovers here would help with connection issues after failover.


How to get query output in addition to EXPLAIN ANALYZE output? Is it possible to get EXPLAIN ANALYZE output (e.g. as a notice) in addition to the regular query output?

I am designing a postgresql client and I would like to add a feature that automatically turns all queries into EXPLAIN ANALYZE for the purpose of tracking query execution time, seq scan, etc. However, such feature would require that I am able to obtain the regular query output in addition to the EXPLAIN ANALYZE output.

How to get query output in addition to EXPLAIN ANALYZE output?

Note: I am aware of auto_explain. However, there appears to be no way to access logs from the client for the last executed statement.

Client can be configured to receive the auto_explain message by setting client_min_messages=log.

Example configuration:

-- Load the extension.
LOAD 'auto_explain';
-- or (if you are using AWS RDS):
LOAD '$libdir/plugins/auto_explain';

-- Enable logging of all queries.
SET auto_explain.log_analyze=true;
SET auto_explain.log_format=json;
SET auto_explain.log_min_duration=0;
SET auto_explain.log_timing=true;

-- Enables Slonik to capture auto_explain logs.
SET client_min_messages=log;

with this configuration, client will receive auto_explain log message after every query.

node.js client Slonik uses this configuration to associate auto_explain messages with the specific queries and parse the log messages.


Find all the queries executed recently on a database [I am a beginner level T-SQL Programmer]
[..and hopefully I am on the right stack exchange site]

I would like to get a list of all the queries I executed (in the least, those I executed today since morning). I need to make a report on the execution times of the queries.

Online search didn't yield me much useful info. The only query that I found online which seemed pretty close is

SELECT
    deqs.last_execution_time AS [Time],
    dest.TEXT AS [Query]
 FROM
    sys.dm_exec_query_stats AS deqs
    CROSS APPLY sys.dm_exec_sql_text(deqs.sql_handle) AS dest
ORDER BY
    deqs.last_execution_time DESC

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

I couldn't find anything in the previous questions (if similar question has already been asked, please point me to it).

I saw some suggestions regarding SQL Profiler, but I guess profiler would help me only if I have already started the trace (correct me if I am wrong).

Can someone suggest me how I should go about to get a list of all queries that have been executed on the database since morning (including the query execution times)..

[It would be helpful (not a requirement) if I can also somehow get the username of the user who executed the query]

This query returned some odd results (most of which were a bunch of sprocs). Moreover, all these results show queries executed since today afternoon (I need queries from morning).

That's because you are looking in the procedure cache, and the plans that were used for the morning might no longer live there (due to memory pressure, server/instance restart, manually clearing the proc cache, etc.).

The real way to get the queries executed against an instance (or more specifically a database) would be to create a SQL Trace or an Extended Events session.  Properly created, either of these will give you the information you are looking for.

If you are looking for execution statistics of this morning and only this morning (i.e. setting up the aforementioned monitoring implementations as a proactive and to-do-next-time task isn't sufficient), then unless that was already created there will be no way natively to get this information.

For future reference, start with a SQL Trace that captures the SQL:StmtCompleted event.  In XE it would be the sql_statement_completed event.

In my opinion, instead of continuing your search for remnants of query execution statistics I would start spending time on figuring out how to re-execute the workload that you are trying to measure.  But this time prior to that, set up the proper tracing/monitoring.


SQL Server 2016 SP1 - Unable to execute R code I have SQL Server 2016 SP1 installed, with R Services. I believe I configued everything based on what I understood to be correct and the output below.

I have restarted SQL Server services and the Launcher services. When I attempt to execute the following test code:

EXEC sp_execute_external_script
  @language =N'R',
  @script=N'OutputDataSet<-InputDataSet',
  @input_data_1 =N'SELECT 1 AS hello'
  WITH RESULT SETS (([hello] int not null));
GO

I get the following error:

Msg 39021, Level 16, State 1, Line 1
Unable to launch runtime for 'R' script. Please check the configuration of the 'R' runtime.
Msg 39019, Level 16, State 1, Line 1
An external script error occurred:
Unable to launch the runtime. ErrorCode 0x80070057: 87(The parameter is incorrect.).

If it helps, my rlauncher.config files looks as follows:

RHOME=D:\Program Files\Microsoft SQL Server\MSSQL13.MSSQLSERVER\R_SERVICES
MPI_HOME=C:\Program Files\Microsoft MPI
INSTANCE_NAME=MSSQLSERVER
TRACE_LEVEL=1
JOB_CLEANUP_ON_EXIT=1
USER_POOL_SIZE=0
WORKING_DIRECTORY=C:\Temp

I'm at a bit of a loss to determine how to fix this.

We ran into a number of problems when trying to configure R-Services, but we eventually got it working.  Please refer to the following link for the details.

Problem getting Sql Server 2016 R Services (In-Database) working


Odd error when stress testing against an AG We are running into an odd error when trying to stress test our application that hits an AG.  If we point the application directly to the primary server, we don't see any issues with the test.  However, if you point the application at the listener, we get this error:

  provider: TCP Provider, error: 0 - You were not connected because a
  duplicate name exists on the network. If joining a domain, go to
  System in Control Panel to change the computer name and try again. If
  joining a workgroup, choose another workgroup name.

Our connection string is similar to this:

> connection string= "data source=DB_C_Name; MultiSubnetFailover=True;
> initial catalog=DBName; integrated security=True;
> MultipleActiveResultSets=True; App=EntityFramework;"

The DB_C_Name is an AD CName that points to the Listener, which in turn points to the WFC.

We have double checked and all of the WFC setting seem to be fine and we can't find any duplicate IPs on our network.

We are at a loss as to why this would happen when going through the listener but not going to the server directly.  We've also noticed this happening in a few other locations.

If we point the application directly to the primary server, we don't see any issues with the test.

Sounds like a problem with the name chosen for the Listener...

  error: 0 - You were not connected because a duplicate name exists on the network. If joining a domain, go to System in Control Panel to change the computer name and try again. If joining a workgroup, choose another workgroup name.

This tells me there is most likely a duplicate name in active directory which was added after this one. You'll need to check AD for the duplicate and find that server or item. It could be disabled, in an odd state, or could have already been removed. In AD, if another computer object is added with the same name (successfully) then it will overwrite the SID in the AD database with the new computer object and giving you this error for the old one. When the new one is removed, renamed, or deleted, nothing happens to the old one so it'd need to be re-added. In this case, the listener re-created though I'd expect the creation to error out...

  We have double checked and all of the WFC setting seem to be fine and we can't find any duplicate IPs on our network.

Right, the error is specifically talking about the NAME, not IP.


Cost threshold for parallelism and wait stats We have an Stored Proc on our prod server (SQL Server 2014) that takes 3hr 40m (on a Sunday: low server activity) to run (collection job costs on 220 jobs with each requiring 6 SP calls, so 13200 SP calls and then some aggregating)

On my laptop with cost threshold set to default of 5 it runs in 30 mins. If I set cost threshold to 50 on my laptop it goes to 1hr 10mins (1/3 of time it takes in prod server)

If i set cost threshold on prod server to 25 it drops to 1hr 30mins, but wait stat go through the roof. See attached: green is threshold of 50, yellow is threshold of 25

Top waits are CXPACKET and SOS_Scheduler_Yield

I don't have a comparable dev or staging server (CPU's, RAM and Disk), so I play too much with a bunch of settings while i replay a workload

Tow questions

Am I better off with a cost threshold of 25 and the high wait stats and 60% performanc gain. Or will the wait stats cause a lot of issues when the load increases
Any thoughts on where the bottleneck is and how to solve. my uneducated guess would be CPU (from the blue line in the charts, it's not too high (under 70%) but is it that it can't serve enough threads in parallel?

SOS_SCHEDULER_YIELD and CXPACKET indicates that you’ve got contention and that queries are spending a lot of time waiting to get access to a CPU.
Check MAXDOP and Cost Threshold for Parallelism to determine when the query should be parallelized.


How can I make MySQL client read password from mylogin.cnf? I'm trying to make the mysql client connect to a mysql server without requiring the password to be given interactively.
Steps taken:

1) First create a mylogin.cnf file

$ mysql_config_editor set --user=<user> --password --host=<host>
Enter password:

2) File created successfully:

$ ls -la .mylogin.cnf
-rw-------. 1 urmt urmt  136 Dec 19 11:01 .mylogin.cnf
$ mysql_config_editor print --all
[client]
user = <user>
password = *****
host = <host>

3) Connect using mysql client

$ mysql <dbname>
ERROR 1045 (28000): Access denied for user '<user>'@'<host>' (using password: NO)

Is there a default value/configuration somewhere that makes the client ignore the password in mylogin.cnf? The user and host properties were read correctly from the file.

I am able to connect just fine if I provide the password on the command line:

$ mysql -p <dbname>
Enter password:
Reading table information...
...
mysql>

MySQL client version is 5.6.22, MySQL Server version is 5.6.22, both on Oracle Linux 6. Client and server are on different hosts.

Thanks

use following command to set login path first

  mysql_config_editor set --login-path=lgpath --user=root -p
  password

and then use below command to login mysql

  mysql --login-path=lgpath

Help about mysql login path options can bee seen by

  mysql_config_editor set --help


Data design issue. Need help with normalization I am developing one web based payment system. I am facing problem in database design.

Following are tables and their fields:

Customer: cust_id    , name
Supplier: supplier_id, name
Employee: employee_id, name
Expanse:  expanse_id , name

group: group_id, group_name
(groups are: {1,Customer},{2,Supplier},{3,Employee},{4,Expanse})

and there will be one more table:

payment: payment_id, group_id, name(ids), amount

In above table, name field will contain id of respective group. Like if group id is 1 and name is 15 that is customer whose id is 15 (customer_id=15)

I want to know that above tables satisfy 3NF? if not then what is the correct way.

Also i want to know that if i want list of all the payment, which will have fields like: payment_id, group_name, name_of_person/expanse. What will be the query to get this list.

One of these is not like the others - expanse

Entities:
groupID, ID, name

composite PK on groupID, ID


Upgrading MySQL 5.1 to 5.5 on Centos 6 without removing dependencies I'm trying to upgrade MySQL 5.1 to 5.5 using yum/rpm combo (yum to remove MySQL 5.1 and rpm to install MySQL 5.5). Most instructions I've found suggest to do first:

yum remove mysql mysql-server mysql-libs

However doing so will remove in my case lots of other packages which I definitely don't want to remove:

================================================================================
 Package              Arch        Version                   Repository     Size
================================================================================
Removing:
 mysql                x86_64      5.1.69-1.el6_4            @updates      2.4 M
 mysql-libs           x86_64      5.1.69-1.el6_4            @updates      4.0 M
 mysql-server         x86_64      5.1.69-1.el6_4            @updates       25 M
Removing for dependencies:
 cronie               x86_64      1.4.4-7.el6               @cr           166 k
 cronie-anacron       x86_64      1.4.4-7.el6               @cr            43 k
 crontabs             noarch      1.10-33.el6               @cr           2.4 k
 mysql-devel          x86_64      5.1.69-1.el6_4            @updates      388 k
 percona-toolkit      noarch      2.2.4-1                   @percona      5.7 M
 perl-DBD-MySQL       x86_64      4.013-3.el6               @base         338 k
 php-mysql            x86_64      5.3.3-23.el6_4            @updates      216 k
 postfix              x86_64      2:2.6.6-2.2.el6_1         @base         9.7 M
 sysstat              x86_64      9.0.4-20.el6              @base         807 k

Transaction Summary
================================================================================

How can I upgrade MySQL without removing those packages? Can I somehow remove MySQL 5.1 without dependencies (well, maybe besides mysql-devel) and get away with it? (without libraries mismatch). I've found one blog that suggest to upgrade MySQL in yum shell, however I'm not sure if it would change anything? :

You can remove MySQL without removing the dependencies with RPM:

rpm -e --nodeps mysql

Keep in mind that yum is going to complain next time you try to install a package about the rpm database being modified. You can correct that with yum clean all.


SSIS transfer SQL server objects task -> Foreign key constraint error I try to copy a database from 1 server to the other to build a staging environment. I do this with SSID. First I delete the existing database on my staging server, create a new and empty one and then I run the script. I copy everything. Most of my databases copy correctly except for 1 which gives me a Foreign Key constraint error. What can I do to fix this? I tried googling, but without any results.

   SSIS package "XXXXXXXX.dtsx" starting.
Information: 0x4002F418 at MyDatabase, Transfer SQL Server Objects Task: There are no Logins to transfer.
Error: 0xC002F325 at myDB, Transfer SQL Server Objects Task: Execution failed with the following error: "ERROR : errorCode=-1073548784 description=Executing the query "ALTER TABLE [dbo].[TemplatePropertyValue]  WITH CH..." failed with the following error: "The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "FK_VALUE1_Template". The conflict occurred in database "myDB", table "dbo.Template", column 'Id'.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
 helpFile= helpContext=0 idofInterfaceWithError={C81DFC5A-3B22-4DA3-BD3B-10BF861A7F9C}".
Task failed: myDB
SSIS package "XXXXXXXXX.dtsx" finished: Success.

AS sugested by Billinkc we tried to first copy the table and then copy the relations. This worked as hooped.


No output when use pymongo watch() I try to access real-time data changes with pymongo.mongo_client.MongoClient.watch(), code example (Python 3.5.3, pymongo==3.7.2):

from pymongo import MongoClient
DB_CON_STR = "mongodb://10.10.10.1:27017/?replicaset=rslogs"
mongo_rs = MongoClient(MDB_CON_STR)
MDB_SCHEMA = 'logs'
MDB_COLLECTION = 'rqs'
for change in mongo_rs[MDB_SCHEMA][MDB_COLLECTION].watch():
    print(change)

and don't get any output.

Replica Set consist from five mongod instances,
Earlier two weeks ago used db.version() 3.4 with set featureCompatibilityVersion: 3.2,
then updated to db.version() 3.6.9, WiredTiger storage engine, pv1, featureCompatibilityVersion: 3.6, logSizeMB: 51200.

logs contain:

  2019-02-25T15:02:55.353+0000 I COMMAND  [conn30246] command logs.rqs command: getMore { getMore: 6649875775522786240, collection: "rqs", lsid: { id: UUID("") }, $clusterTime: { signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }, clusterTime:Timestamp(1551106974, 13) }, $db: "logs", $readPreference: { mode: "primary" } } originatingCommand: { aggregate: "rqs", pipeline: [ { $changeStream: { fullDocument: "default" } } ], cursor: {}, lsid: { id: UUID("") }, $clusterTime: { signature: { hash: BinData(0, 0000000000000000000000000000000000000000), keyId: 0 }, clusterTime: Timestamp(1551106971, 9) }, $db: "logs", $readPreference: { mode: "primary" } } planSummary: COLLSCAN cursorid:6649875775522786240 keysExamined:0 docsExamined:16 numYields:16 nreturned:0 reslen:210 locks:{ Global: { acquireCount: { r: 36 } }, Database: { acquireCount: { r: 18 } }, oplog: { acquireCount: { r: 17 } } } protocol:op_msg 927m

In what could be the problem?

Problem was solved by stopping one by one each replica and make resync it, after that was changed primary and resync previous one.


One to many relationship query result in a single row and dynamic multiple columns In a one-to-many relationship query, I would have a single row and dynamic multiple columns.
In this example the expected result is to have for each owner all the associated phone numbers in a single row, starting from a multiple rows table.
The goal is to get a json ready to be imported in a MongoDB database.
SELECT o.id, p.numbers

FROM owner o
INNER JOIN phones p ON o.id = p.owner_id

WHERE o.id=1

getting the following result
o.id     p.numbers
-----    ------
1        333-555-888
1        222-777-666
1        555-657-555
1        ....

The result should be:
o.id     p.numbers1     p.numbers2     p.numbers3     p.numbers4
-----    -----------    ----------     ----------    ----------
1        333-555-888     222-777-666   555-657-555     .......

This Q & A MySQL single table static and dynamic pivot doesn't resolve the issue since "Kode 1" or "Kode 2" are the different phone numbers for me.
I am using MySQL 5.7.24. I want to get for each owner (t1) every phone number (t2) associated to him, in a single row with multiple columns. (Each phone number in a dedicated column) and they can be different.
I want to export to csv. There can be around 10 phone numbers per owner.

The query below returns exactly what you described in your question but I doubt this is what you really need.
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = ',id, ' ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn)
 ) INTO @sql
FROM (SELECT o.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o
INNER JOIN phones p ON o.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o.id=1) phones;
SET @sql = CONCAT('SELECT id, ', @sql, ' FROM owner WHERE id = 1');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=2f7ec41adaf2acff5ca04ed93276fc3d
Query to return records for all owners:
SET @sql = NULL;
SELECT GROUP_CONCAT(
 CONCAT('(SELECT numbers FROM phones WHERE owner_id = o.id ORDER BY numbers LIMIT 1 OFFSET ',rn - 1,' ) AS numbers', rn)
 ) INTO @sql
FROM (SELECT o1.id, p.numbers, @rn := @rn + 1 AS rn
FROM owner o1
INNER JOIN phones p ON o1.id = p.owner_id
CROSS JOIN (SELECT @rn := 0) r
WHERE o1.id=(SELECT owner_id FROM (SELECT owner_id, COUNT(1) AS cnt FROM phones GROUP BY owner_id) c ORDER BY cnt DESC LIMIT 1)) phones;
SET @sql = CONCAT('SELECT o.id, ', @sql, ' FROM owner o -- WHERE id = 2');

-- SELECT @sql;
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Link to dbfiddle: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=0f5f425c11fa45bcc202a3ed116d5033