I'm confused about High Availability/Always On is High Availability something to describe Log Shipping and Always On ( replica )?

is Always on something to describe Log Shipping and High Availability?

I'm studying to get my certification 40-762 but I need to study a lot about these topics ( since i'm bad in this part on online tests ).

But even looking at Microsoft's website I'm confused about these topics. I know something about log shipping and replica, But I would like a point to study, and looking for "always on" and "high availability" not knowing what they are is a bit hard.

So, how can I describe them?

  The AlwaysOn Availability Groups feature is a high-availability and
  disaster-recovery solution that provides an enterprise-level
  alternative to database mirroring. Introduced in SQL Server 2012,
  AlwaysOn Availability Groups maximizes the availability of a set of
  user databases for an enterprise. An availability group supports a
  failover environment for a discrete set of user databases, known as
  availability databases, that fail over together. An availability group
  supports a set of read-write primary databases and one to eight sets
  of corresponding secondary databases. Optionally, secondary databases
  can be made available for read-only access and/or some backup
  operations.

And does "availability groups" is when i have log shipping and replicas in the same server?

AGs are a lot more like a mix of mirroring and clustering than log shipping.

Clustering would let you have multiple instances that fail over from one node to another node, but the nodes and disks are all absolutely distinct and only one instance runs at a time (though you can then load up other instances on the same set of nodes and make it more complicated if you wanted to).

Mirroring copies over the logs in real time (not quite log shipping but that's where the similarity comes from; it's "shipping" logs) and lets you automatically fail over if you have a witness. However, you have to specify the primary and secondary names in the connection string in order for it to work, and you can't go past those initial two nodes.

AGs have the best of all worlds. It's mirroring under the covers so it's live, but it lets you read from the secondaries (and the tech behind that is far superior than any home-grown stuff).

AGs also skip the shortcomings of mirroring because you can have multiple nodes, and you don't specify any of them in the connection string! You get a listener name and use THAT in the connection string, clustering fails over that listener name between nodes as required (while sending off those read only connections to the secondaries). Later on you can add/remove/replace nodes without modifying your connection strings at all.


Transaction Log Maintanance While Using AlwaysOn Availability Group We are using HADR ( AlwaysOn Availability Group) feature of SQL Server 2012.
Server and AG Configuration as below:

SQL12_SRV1
--> AG1(PRIMARY)  - SYNC
   -->> DBTest
SQL12_SRV2
--> AG1(SECONDARY)
   -->> DBTest   - ASYNC

DBTest Database is growing (200GB) Day to day approximate monthly and same Transaction Log File will also grow according to Data.

So How to minimize Transaction Log File Size by using proper way of taking LOG backup.
On which Replica we have to take log backup.

Thanks In Advance.

You can backup the transaction log from either of the replicas.  Doing the transaction log backup on either the primary replica or the secondary replica will mark both replicas' transaction logs as reusable (provided no other stoppers are in place like active transactions, etc.).

To do a test, in a non-production environment, setup an availability group just as you have it in your production system (asynchronous commit to the secondary replica).

In my test environment I have a test database, TestBackupDatabase, and I bloated it with logged transactions through a dummy table:

use TestBackupDatabase;
go

create table dbo.TestTable
(
    id int identity(1, 1) not null,
    some_int int not null
        default 1
);
go

insert into dbo.TestTable
default values;
go 1000

Now when I do a transaction log backup on my primary, using DBCC SQLPERF(LOGSPACE) I see on both transaction logs (primary and secondary) that space used has dropped due to log truncation.  Bloating the transaction log back up with the same test on the primary:

insert into dbo.TestTable
default values;
go 1000

I now do a transaction log backup on the secondary async replica.  Running DBCC SQLPERF(LOGSPACE) again on each replica I see the same behavior:  transaction log reuse.

BOL Reference: Active Secondaries: Backup on Secondary Replicas (AlwaysOn Availability Groups)


SGA size vs SWAP space in Oracle RAC I’ve searched and read wide about the good practices of configuring swap space for Oracle Database installation. I am aware that for 1-2GB RAM, swap has to be 1.5x. For between 2GB and 8GB, swap needs to be equal to SGA. Above 8GB, swap will be twice SGA size (I stand to be corrected).

However, just yesterday, someone introduced something that got me confused. He said, “For Oracle Database or Oracle RAC on systems, available swap space must be at least equal to the sum of the SGA sizes of all instances running on the servers.”

I am a bit confused because I am aware that even though it is a RAC environment, each server has its OS and maintains its internal memory separately hence, the swap for each serverdoesn’t have to be equal to the sum of SGAs as indicated by my colleague.

Can anyone please help me understand what the correct value should be?

Typically:

Server Configuration Checklist for Oracle Database Installation

Swap space allocation relative to RAM (Oracle Database)

Between 1 GB and 2 GB: 1.5 times the size of the RAM
Between 2 GB and 16 GB: Equal to the size of the RAM
More than 16 GB: 16 GB

Then there is Solaris...

Server Memory Minimum Requirements

  Note:

  On Oracle Solaris, if you use non-swappable memory, like ISM,
  then deduct the memory allocated to this space from the available RAM
  before calculating the swap space. If you plan to install Oracle
  Database on systems using DISM, then available swap space must be at
  least equal to the sum of the SGA sizes of all instances running on
  the servers.

It is about summing the SGAs of multiple database instances on the same server, not about multiple database instances in a cluster on different servers (RAC).


Error creating memory optimized filegroup in SSDT I am attempting to add a memory optimized filegroup to a SQL server database project in SSDT. This is for SQL Server 2017, using Visual Studio 2017.

However compiling the project (pressing F5 to build) is resulting in an error. This error does not occur when deploying (via Deploy).

The filegroup is created as normal (with the SQLCMD variable as scripted by SSDT):

ALTER DATABASE [$(DatabaseName)]
ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA

However this results in an error:

  The operation 'AUTO_CLOSE' is not supported with databases that have a MEMORY_OPTIMIZED_DATA filegroup.

However, database settings show that auto close is indeed disabled:

The deployment script generated by SSDT for some reason is creating the database, creating the filegroup, and then only setting AUTO_CLOSE off afterward. It is possible this is resulting in the error:

CREATE DATABASE [$(DatabaseName)]
ON
PRIMARY(NAME = [$(DatabaseName)], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_Primary.mdf')
LOG ON (NAME = [$(DatabaseName)_log], FILENAME = N'$(DefaultLogPath)$(DefaultFilePrefix)_Primary.ldf') COLLATE SQL_Latin1_General_CP1_CI_AS
GO
PRINT N'Creating [MemoryOptimizedFilegroup]...';

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

GO
ALTER DATABASE [$(DatabaseName)]
    ADD FILE (NAME = [MemoryOptimizedFilegroup_69323650], FILENAME = N'$(DefaultDataPath)$(DefaultFilePrefix)_MemoryOptimizedFilegroup_69323650.mdf') TO FILEGROUP [MemoryOptimizedFilegroup];

GO
USE [$(DatabaseName)];

GO
IF EXISTS (SELECT 1
    FROM   [master].[dbo].[sysdatabases]
    WHERE  [name] = N'$(DatabaseName)')
BEGIN
    ALTER DATABASE [$(DatabaseName)]
        SET ANSI_NULLS ON,
            ANSI_PADDING ON,
            ANSI_WARNINGS ON,
            ARITHABORT ON,
            CONCAT_NULL_YIELDS_NULL ON,
            NUMERIC_ROUNDABORT OFF,
            QUOTED_IDENTIFIER ON,
            ANSI_NULL_DEFAULT ON,
            CURSOR_DEFAULT LOCAL,
            CURSOR_CLOSE_ON_COMMIT OFF,
            AUTO_CREATE_STATISTICS ON,
            AUTO_SHRINK OFF,
            AUTO_UPDATE_STATISTICS ON,
            RECURSIVE_TRIGGERS OFF
        WITH ROLLBACK IMMEDIATE;
    ALTER DATABASE [$(DatabaseName)]
        SET AUTO_CLOSE OFF
        WITH ROLLBACK IMMEDIATE;
END

I am not certain whether AUTO_CLOSE is enabled or disabled by default after CREATE DATABASE. If ON by default, it seems that SSDT is generating the deployment script in the wrong order. If OFF by default, then I don't understand why the error exists.

Has anyone had success creating a memory optimized file group in an SSDT project?

I am not certain whether AUTO_CLOSE is enabled or disabled by default
  after CREATE DATABASE. If ON by default, it seems that SSDT is
  generating the deployment script in the wrong order. If OFF by
  default, then I don't understand why the error exists.

  It seems LocalDB has hard coded use of AUTO_CLOSE, ignoring model

LocalDB / SQLEXPRESS

True, when connecting to localdb and running a standard create database command

CREATE DATABASE TEST

GO

SELECT name,database_id
FROM sys.databases
WHERE is_auto_close_on = 1;

name    database_id
TEST    5

It appears that the is_auto_close_on is set to 1 even when model has auto_close disabled.

Normally the default settings are based on the model system database.

Resolving this issue in theory

You could create a trigger that automatically changes the auto close to 0 for newly created databases. This is a bit of a hacky approach but works.

Try / implement at your own risk.

CREATE TRIGGER Change_Auto_Close
ON ALL SERVER
FOR CREATE_DATABASE
AS
    DECLARE
        @DatabaseName NVARCHAR(128)
        ,@SQL NVARCHAR(4000);
    SELECT
        @DatabaseName = EVENTDATA().value('(/EVENT_INSTANCE/DatabaseName)[1]','NVARCHAR(128)')

    SET @SQL = 'USE [master] ALTER DATABASE ['+@DatabaseName+'] SET AUTO_CLOSE OFF WITH NO_WAIT;';

    IF @@TRANCOUNT = 1
    COMMIT TRAN; -- commit previous tran, otherwise we cannot alter the database
    -- PRINT @SQL;
    EXEC (@SQL);
    BEGIN TRAN; --begin new tran as not get error that the transaction ended in the trigger

GO

Ideas from here and here

Other editions

It could be that your model database has is_auto_close_on = 1 as databases are created as a copy of model. Below are some tests with auto_close enabled and disabled on the model database.

Set auto close on for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE ON WITH NO_WAIT;
GO

Create a default database

CREATE DATABASE AutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'AutoClosedDB';

Result

name            is_auto_close_on
AutoClosedDB    1

Set auto close off for model

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO

Create a database with default settings

CREATE DATABASE NoAutoClosedDB;

Validate the auto close state of the newly created database

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'NoAutoClosedDB';

Result

name            is_auto_close_on
NoAutoClosedDB  0

Try creating a memory optimized filegroup on both databases

ALTER DATABASE NoAutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Succeeds

ALTER DATABASE AutoClosedDB
    ADD FILEGROUP [MemoryOptimizedFilegroup] CONTAINS MEMORY_OPTIMIZED_DATA;

Fails with error message:

  Msg 10794, Level 16, State 125, Line 33 The operation 'AUTO_CLOSE' is
  not supported with databases that have a MEMORY_OPTIMIZED_DATA
  filegroup.

To validate the auto close state on the model database, run below script

SELECT name, is_auto_close_on
FROM sys.databases
WHERE name = 'model';

Disable auto close with

USE [master]
GO
ALTER DATABASE [model] SET AUTO_CLOSE OFF WITH NO_WAIT;
GO


Get list of orders that have not been closed that are older than 60 days I have a table te_dates that holds order status information for orders.

The table is a many-to-one relationship, so one order can have many row inserts into my order_status table.

(dsc_id is the order status code)

order_id | dsc_id |             te_date | te_date_id
----------------------------------------------------
    2880 |      1 | 2014-11-19 09:27:18 |      16486
    2880 |      2 | 2014-11-19 09:27:23 |      16487
    2880 |      3 | 2014-11-24 14:10:40 |      16710
    2880 |      4 | 2014-11-24 14:10:41 |      16711
    2880 |      0 | 2014-11-24 14:10:49 |      16712
    2880 |      8 | 2014-12-17 15:52:29 |      17782

My client is asking me to find all orders that are 60 days or older that have NOT been closed, and change their status.

My thought process is to select distinct order_id of files that have been opened (dsc_id = 1) that are greater than 60 days old, and are not currently closed (dsc_id = 8).

I just can't wrap my head around the query.

Any help would be appreciated.

SELECT
    o1.order_id
FROM tblOrders o1
WHERE
    o1.dsc_id = 1
    AND o1.te_date < DATE_ADD(NOW(), INTERVAL -60 DAY)
    AND NOT EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id = 8)
    AND EXISTS (SELECT order_id FROM tblOrders o2 WHERE o2.order_id = o1.order_id and o2.dsc_id != 1)
GROUP BY o1.order_id


SQL Server, firewall and linked servers I have:

SQL Server 2008R2 database server in my LAN and under my control (call it LOCALSQL)
remote SQL Server, on which I have much less control (call it REMOTESQL), for which I only have IP, username, password
Linux-Apache-PHP server, on my LAN, used to access LOCALSQL (call it PHPSERVER)

Up to now, PHPSERVER accesses LOCALSQL; I can only access REMOTESQL from a test Windows machine with SQL Server Management Studio, with the necessary firewall permission.

Now, I would like to use sp_addlinkedserver to have PHPSERVER execute query involving tables from both servers.
What should I do for the firewall? Should I allow connections:

a) between LOCALSQL and REMOTESQL

b) between PHPSERVER and REMOTESQL

c) both of the above

a) between LOCALSQL and REMOTESQL

Your PHP client never connects to the REMOTESQL server. It only connects to LOCALSQL and then LOCALSQL connects to REMOTESQL.

Keep in mind that your PHP availability is now driven by the REMOTESQL availability and consider that distributed queries are notoriously problematic to perf tune. Good luck!


INSERT values from .csv into database We have a sybase database running an application our users use which contains client info.

The situation is that address information for maybe 1000 clients or so is out of date. We were given a .csv file with updated address info, and are being asked to update the addresses in the database.

I've filtered the csv to compare the 2 address columns and only show those that are not the same. So I have a .csv containing only the clients who need their address updated.

So imagine this client information lives on a table. We also have unique client ID's to go by along with other personal info found on the client table.

I'm no SQL expert, so my query syntax for sql is probably way off, but does anybody have any suggestions for how I might write a query for this?

Something like,

INPUT FOR EACH [client_id in .csv column A] [address_info in .csv column B] TO clientAddress.table

That's how I imagine it would work in my head anyway. I tried searching around but can't find anybody with a situation similar enough to mine.

USING FILE clause is used to load data from a file. The column list shows two columns to be imported from the .csv file.

LOAD TABLE clientAddress.table (client.id, address.info)
USING CLIENT FILE 'file.csv'


OLAP or OLATP - How to determine I have SQL express server with database that is used by one application. I am trying to optimize the DB but am not sure if the DB is OLTP or OLAP.

There is no analysis server installed, the DB is inside the SQL Server instance hence it  should be OLTP, but when I run the read/write ration on the DB I have the following:

          Reads    Writes    Read%   Write %

DB file   400 000   75 000   85%     15%

DB log        250   30 000    1%     99%

Standard OLTP DB should have more reads than writes, right? This makes me suspect that maybe It is OLAP DB.

I am asking this regarding the use of HyperThreading technologies, MAXDOP setting and whether to configure cache as write-through or write-back among other things.

The query I am using is as follow:

SELECT DB_NAME(DB_ID()) AS [Database Name] ,
[file_id] , num_of_reads , num_of_writes , num_of_bytes_read , num_of_bytes_written ,
CAST(100. * num_of_reads / ( num_of_reads + num_of_writes ) AS DECIMAL(10, 1)) AS [# Reads Pct] ,
CAST(100. * num_of_writes / ( num_of_reads + num_of_writes ) AS DECIMAL(10,1)) AS [# Write Pct] ,
CAST(100. * num_of_bytes_read / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10, 1)) AS [Read Bytes Pct] ,
CAST(100. * num_of_bytes_written / ( num_of_bytes_read + num_of_bytes_written ) AS DECIMAL(10,1)) AS [Written Bytes Pct]
FROM sys.dm_io_virtual_file_stats(DB_ID(), NULL) ;

Any thoughts from senior DBAs?

From a DBA perspective, the key difference between OLAP and OLTP is the tuning method you apply to the queries. The read/write ratio doesn't really tell you anything useful.

I have a little "magic quadrant" that I use to illustrate the difference (in your case, consider BI/DW and ETL the same as OLAP):

Basically, if you must touch a lot of data to produce query results, you are OLAP. Converse if you can get away with touching very little data to produce return values, you are OLTP.

Obviously, if you have poor indexing strategies, then OLTP can end up looking like OLAP - simply because you end up doing table scans all the time. A good way to make that distinction is to look at how much data MUST be touched by the top 10 most important queries in the system - no matter how good your indexing strategy is.

Once you have settled on what the majority of your workload is, you can start applying workload specific tuning techniques. Here are some of the workload specific tricks you might consider (always tailor to your specific workload)

OLAP:

Prefer hash joins
Optimise table scan speed (typically by laying out data for sequential I/O)
Aggregate view are often better than indexes
Column store indexes are better than B-trees
Sequential I/O
Aggressively de-normalise
Use high MAXDOP
Optimise network for bandwidth

OLTP:

Prefer loop joins
Optimise for index seek (there should always be an indexed path to the data)
Rarely aggregate
B-tree indexes
Random I/O
Keep database in 3NF
Use MAXDOP 1
Optimise network for latency


How to set up multiple fields as primary key in MySQL? I have a table with fields

EmployeeID

blahblah

blahblah2

.....

RecordMonth

RecordYear

so each employee should only have a matching entry for a month, year, Emp#.  How do I set up a table.

So how do I set up the table so that EmployeeID can be updated once a month but can never have two entries for a matching month and year?

Use a compound primary key:

CREATE TABLE yourtable
  (
     employeeid  INT,
     blahblah    VARCHAR(255),
     blahblah2   VARCHAR(255),
     recordmonth DATE,
     recordyear DATE,
     PRIMARY KEY (employeeid, recordmonth, recordyear)
  )

And if your table already exists, drop the old primary key:

ALTER TABLE yourtable
DROP PRIMARY KEY;

And recreate it:

ALTER TABLE yourtable
ADD PRIMARY KEY (employeeid, recordmonth, recordyear);


'ambiguous column name' error refers to database, table, and column guuu sqlite3 gives an 'ambiguous column name' error with the query below. The error itself references what seems to be about as an unambiguous column name as one can imagine, identifying the database as 'sourcedb', the table as 'answers', and the field as 'markup'. Why is this query triggering the error?

sqlite> SELECT answers.*
        FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'
               INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
               INNER JOIN sourcedb.answers
                 ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey;
...>    ...>    ...> Error: ambiguous column name: sourcedb.answers.markup

Some additional detail...

sqlite> pragma table_info(answers);
0|markup|TEXT|0||0
1|identifier|TEXT|0||0
2|text|TEXT|0||0
3|answerskey|INTEGER|0||1
4|answertype|TEXT|0||0
5|ReadPerms||0||0
6|UpdatePerms||0||0
7|DeletePerms||0||0
sqlite> pragma database_list;
0|main|
2|sourcedb|/root/.ttest/database/sqlite/ttest-simple.sqlite

You have FROM sourcedb.answers and INNER JOIN sourcedb.answers so you are introducing the same table twice without giving either an alias.

Some of the join conditions appear probably wrong as well.

 FROM   sourcedb.answers
               INNER JOIN sourcedb.questions_tests
                 ON sourcedb.questions_tests.testskey = '121212eczema'

doesn't reference sourcedb.answers anywhere in the ON.

INNER JOIN sourcedb.answers_questions
                 ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey

Doesn't reference sourcedb.answers_questions in the ON

I presume you actually need something like

SELECT answers.*
FROM   sourcedb.answers
       INNER JOIN sourcedb.answers_questions
         ON sourcedb.answers.answerskey = sourcedb.answers_questions.questionskey
       INNER JOIN sourcedb.questions_tests
         ON sourcedb.questions_tests.questionskey = sourcedb.answers.questionskey
WHERE  sourcedb.questions_tests.testskey = '121212eczema'


Value of the Included Column is stored in Leaf Node? There is SQL- script, which generated the Nonclustered Index with Included Column:

CREATE TABLE users
  (
     id        INT,
     firstname VARCHAR(50),
     surname   VARCHAR(50)
  );

CREATE CLUSTERED INDEX ix_users_id
  ON users (id);

CREATE NONCLUSTERED INDEX ix_users_firstname
  ON users (firstname)
  include (surname);

SELECT firstname,
       surname
FROM   users
WHERE  firstname = 'John';

If I correctly understood, most of the time, Engine of my SQL Server 2019 will seek Nonclustered Index for the above SELECT query, without touching the Clustered Index. Does that mean the value of surname column is stored in Leaf Node of the Nonclustered Index? Also, that means the value of surname is duplicated because it also stored in Clustered Index.

Am I right?

Does that mean the value of surname column is stored in Leaf Node of the Nonclustered Index?

Yes, INCLUDE column values are stored at the leaf node of the index in a non-clustered index. In a clustered index, non-key columns are stored at leaf nodes in the same fashion. A heap works differently because there are no key columns.

Also, that means the value of surname is duplicated because it also stored in Clustered Index.

All non-clustered indexes store duplicate data. For example, the first name column is stored in the clustered index as well as the non-clustered index.

Without a clustered index, the data for all columns is stored in a heap and then data is duplicated in any non-clustered indexes for columns that are key columns or include columns in those indexes.

This is why when designing your indexing strategy, you need to balance performance vs storage/maintenance for the duplicated data.

Check this link for more information about SQL Server Indexes.


MySQL query r.e covering index with Primary Key I have the following table (there are more columns in the real one).

CREATE TABLE `test`.`j_data` (
`j_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,
`j_name` VARCHAR(80) NULL DEFAULT NULL
PRIMARY KEY (`j_id`));

We regularly run queries where the main column used is the PK, e.g.

SELECT * FROM j_data WHERE j_id = 123;

But we are now looking to run a lot of queries like:

SELECT j_id FROM j_data WHERE j_name = xxx;

Would this benefit from a covering index on j_name and j_id.

ADD INDEX `j_name_id` (`j_name` ASC, `j_id` ASC);

Or would an index on j_name only suffice? It's quite a large table so I don't want to spend time adding the index if it is unlikely to help.

Short answer:  Yes.

Long answer:

You are using InnoDB, correct?  (Run SHOW CREATE TABLE j_data to find out what the Engine is.)

In InnoDB, each "secondary index" (contrast "primary key") silently contains a copy of the primary key.

So...  INDEX(j_name) happens to be identical to INDEX(j_name, j_id).

Tip:  At some point you will realize that prefixing all columns with the table name ("j_") is unnecessary clutter.


ORA-01502: index or partition of such index is in usable state problem I have a table in my Oracle database, where

select pkcol, count(*) from myTable group by pkcol having count(*) > 1;

yields

  PKCOL   COUNT(*)
------- ----------
      1          2
      2          2

Trying to remove the duplicate rows

delete myTable where pkcol = 1;

Yields:

  ORA-01502: index 'MYTABLE.PK_MT' or partition of such index is in
  usable state.

I'm using Oracle.DataAccess.Client.OracleBulkCopy to fill the table.

As far as I understand documentation from Oracle PRIMARY KEY constraints had to be checked.

Obviously they are not checked, as I found by doing the same bulkcopy two times in succession which ended in duplicates in all row.

Now I'm only using it after deleting all rows and I'm using a table with a similar primary key as source. As result I expect no problems.

But embedded deep inside my MS Build scripts, I end up with just 2 duplicates out of 2210 rows.

I guess that ignoring the primary key in the first place is a clear bug. No Bulkcopy should be allowed to ignore primary key constraints.

Edit:

Meanwhile I found, that the 2 conflicting rows where normally inserted by some script before bulkcopy was called. The problem reduces to my known problem, that bulkcopy doesn't check primary keys here.

Faced a similar issue.
If you just need to get rid of the error, do:
SELECT 'ALTER INDEX '||OWNER||'.'||INDEX_NAME||' REBUILD;'
FROM DBA_INDEXES
WHERE STATUS = 'UNUSABLE';

This will output ALTER INDEX ... REBUILD; statements for all "unusable" indexes. Run them, so that the indexes can be "usable" again.


Difference between Session Timeout and Heartbeat in always on What is the difference between Session Timeout and Heartbeat in always on...
My Network is laggy and i get frequent connection timeout error on error logs and also at the I see missed heartbeat errors on the failover cluster errors ....Which settings should I change. Crossing which thresholds forces a failover---Session timeout or heartbeat ?

Will appreciate any help. Thanks

Heartbeat is what will determine if a node is down and initiate a failover.  Session timeout could be from the client, server to server, or other network related issues.  it seems like you are having several network issues.

You don't have to have a dedicated heartbeat network but if you don't, it does expose you to broadcast storms and other issues that might hit your main network.

AlwaysOn runs on and uses the WSFC APIs but if you have a bad laggy network, it's best to resolve that or at least put your servers on separate network hardware/VLAN/subnet/etc than the clients if that is what is needed to resolve this.  Regardless, you will want to ensure a healthy heartbeat at a minimum.

WSFC Inter-node Health Detection and Quorum Voting

  Each node in a WSFC cluster participates in periodic heartbeat
  communication to share the node's health status with the other nodes.
  Unresponsive nodes are considered to be in a failed state. A quorum
  node set is a majority of the voting nodes and witnesses in the WSFC
  cluster. The overall health and status of a WSFC cluster is determined
  by a periodic quorum vote. The presence of a quorum means that the
  cluster is healthy and able to provide node-level fault tolerance. +

If you need a quicker bandaid or perhaps you just have to deal with the latency/network issues you can change the value of the timeout to something that works better for your environment.

  Run command prompt as administrator. Execute the following to change
  the same-site heart beat to 2 seconds (replace clustername with the
  actual WFC name).

cluster /cluster:clustername /prop SameSubnetDelay=2000:DWORD

  Execute the following to change the
  cross-site heart beat to 4 seconds (replace clustername with the
  actual WFC name).

cluster /cluster:clustername /prop CrossSubnetDelay=4000:DWORD

  Execute the following to change the
  same-site ping loss threshold to 10 lost pings (replace clustername
  with the actual WFC name).

cluster /cluster:clustername /prop SameSubnetThreshold=10:DWORD

  Execute the following to change the
  cross-site ping loss threshold to 10 lost pings (replace clustername
  with the actual WFC name).

cluster /cluster:clustername /prop CrossSubnetThreshold=10:DWORD

With Powershell:

  Run PowerShell as administrator. Import the FailoverClusters module.

Import-Module FailoverClusters

  Execute the following to change the
  same-site heart beat to 2 seconds (replace clustername with the actual
  WFC name). (get-cluster).SameSubnetDelay = 2000 Execute the following
  to change the cross-site heart beat to 4 seconds (replace clustername
  with the actual WFC name).

(get-cluster).CrossSubnetDelay = 4000

  Execute the following to change the same-site ping loss threshold to
  10 lost pings (replace clustername with the actual WFC name).

(get-cluster).SameSubnetThreshold = 10

  Execute the following to change
  the cross-site ping loss threshold to 10 lost pings (replace
  clustername with the actual WFC name).
  (get-cluster).CrossSubnetThreshold = 10

Note in no way is this a recommendation to run this on your production systems without first testing it and getting the wider group such as network and server admins involved


Can't restore MySQL DB because of a function I'm trying to restore a database dump that I took via mysqldump onto a new server.  Here's how I made the dump:

 mysqldump --routines --triggers database_name | pbzip2 > database_name.sql.bz2

This works fine & I transfer the dump over to the new server to restore it.  I should mention the database also contains two functions:

mysql> show function status;
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| Db               | Name         | Type     | Definer            | Modified            | Created             | Security_type | Comment |
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
| database_name    | function_1   | FUNCTION | analytic@localhost | 2016-05-13 00:56:26 | 2016-05-13 00:56:26 | DEFINER       |         |
| database_name    | function_2   | FUNCTION | analytic@localhost | 2017-07-18 20:47:59 | 2017-07-18 20:47:59 | DEFINER       |         |
+------------------+--------------+----------+--------------------+---------------------+---------------------+---------------+---------+
2 rows in set (0.00 sec)

I created the user and gave the same grants for the analytic user on the new DB server that existed on the old server, then I proceed to restore the database:

GRANT EXECUTE ON FUNCTION `database_name`.`function_1` TO 'analytic'@'localhost';
GRANT EXECUTE ON FUNCTION `database_name`.`function_2` TO 'analytic'@'localhost';

However, restoring the database results in this error every time:

ERROR 1370 (42000) at line 16767: execute command denied to user 'analytic'@'localhost' for routine 'database_name.function_2'

I'm assuming it's because it's dropping the table during the restore, which also drops the grant for the user?  I assume this because the grant I create for this user is no longer there after I attempt the restore.

I even went so far as to grant all privileges to this user on that DB, but I got the same error.  What is the right way to restore this database successfully?

The dump came from a very old server running MySQL 5.0.95, and I am restoring it to a server running MariaDB 10.1.14.

Such DB migration is not obvious and you are not the first one to have trouble with functions and definers after importing DB as things have to be created in the right order.

I'm not sure to understand how you can grant right on a function to an user when this function failed to import so function wasn't created yet.

But instead of restarting restoration from the beginning, split your export in several files. fix the problem (create user analytic, create functions manually, if there is failure, don't use definer, you will fix the issue later after import completed) then continue execute export file from when it failed.


What Happens When Identity Range IsExceeded? I am using identity columns in my merge replication solution to give people an id they can tell us over the phone (i.e. to support staff). A rowguid column would be too hard for this.

I have noticed that int identity columns are replicated no trouble, and each client subscription has a range of identity values it can use.

i.e. select * from MSmerge_identity_range

range_begin    range_end    next_range_begin    next_range_end
6003           7003         7003                8003

I'm just wondering what happens if you do some kind of bulk insert for instance which inserted 5000 new records into this table. What would happen to the identity column then?

If you attempt to bulk insert more rows then available identity values between syncs you will get an error:

  The identity range managed by replication is full and must be updated
  by a replication agent.

The key here is to allocate range sizes large enough to accommodate for the amount of inserts that may occur between syncs.  You can specify the range sizes in article properties when adding articles to the publication.


Setting up a Postgresql Partition with foreign key I have a Ubuntu 12.04 Server with PostgreSQL 9.1 installed.
I am trying to setup Partitioning on a existing Postgres DB(say 'xyz') which has very huge tables and some have foreign keys set on them.

This is what i did until now

I followed http://www.postgresql.org/docs/9.1/static/ddl-partitioning.html to create Partition triggers.
Tested the Triggers, Each data i inserted got placed in the right partition.
Took take a pg_dump of all the tables individually from DB 'xyz'.
The option --column-inserts was enabled for each dump.
Created a new DB 'xyz_new'.
Imported Partition Triggers and Funtions i created into it.
Started importing tables with ones which were free of foreign keys.
Then i imported the ones with foreign keys, in a way that the tables referred to by the foreign keys are already imported.

But while doing the last step i get a error like

ERROR:  insert or update on table "aaaaa" violates foreign key constraint "bbbbb_id_refs_id_5b63fd27"
DETAIL:  Key (bbbbb_id)=(6817) is not present in table "bbbbb".
As a Result, no import happened on the foreign key based tables.

If i do a Select query on bbbbb for id 6817, the record does shows up.

Then after some googling i found this, http://www.postgresql.org/docs/9.1/static/ddl-inherit.html
As per the Caveats mentioned in the Wiki, the Inheritance doesnt work with the foreign key.
(That was a very bad news for me :( i had spent lots of time to create the triggers.)

Now, I would like to know if there is a workaround to manage Partitions on Postgres with foreign keys?
OR
Is there any other way where i can handle huge tables with foreign keys?

First, I would suggest reading this: http://ledgersmbdev.blogspot.com/2012/08/postgresql-or-modelling-part-3-table.html because although it doesn't cover partitioning, you are doing partitioning using table inheritance and this will cover a lot of details.

In general there are three ways you can handle this.  The first is to use specialized key tables (managed with triggers) and reference these tables in your fkey constraints.  This is often the simplest approach but it has significant costs in some cases.

The second possibility is you can partition your joining table by the foreign key.  This can lead to major complexity problems though and so aside from rare cases, it is my least-favored approach.

A final possibility is you can write your own constraint triggers to manage foreign key enforcement.

Which approach you choose will be dependent on the complexity of joining information, particularly transitively joining information.  The second approach allows you to do things entirely declaratively at a significant (possibly major) complexity cost, while the first and third move away from declarative approaches, meaning you may also want to use pgTAP to run test cases on your triggers.....

None of these solutions are without significant complexity.  You may want to rethink whether partitioning is the best way to go here and whether partial indexes and other approaches will get you where you need to go.


How to implement posts with “seen by” like facebook? Inside a Facebook group there is a feature when every time see the group newsfeed it mark all posts as seen by me.

And if I see a specific post inside a group thought a url (facebook.com/groups/123/posts/123), it mark as seen this single post.

My question is about what is the intuitive way and right to model this and performance. I want to know what I am missing or wrong about about to get it right.

What happens on facebook: "When a new member join the group, all the old posts are marked as seen by him". But is it per post or global?

Scenarios:

Mark as seen all individual posts when click group.

Pros:

Easy development
Easy and fast queries. Could implement a int counter column on each post and a has_many table users_id x post_id
Getting and individual post I can tell how many users saw without context

Cons:

How to deal with a new member join a group with > 100 posts? Batch writes?

2.Mark as seen the group when click group.

Pros:

One single write when seen a group

Any tips and advices are welcome!
Thanks!

I think the main thing to consider here is how you store who have seen what.  The most convenient way is to store the post-user pairs.  Any solution with less granularity will have some cases hard to handle - your second suggestion, if I understood it correctly, would store a group-user pair, plus a flag that all posts are already seen (I imagine that this would be rather the last post ID at the moment of joining the group).  This would only work if you can't miss posts, so that the unseen ones form a contiguous group, all having a bigger ID than all already-seen ones.

Since the user-post pairing consists exactly these two fields, which can be, expecting extremely high user and post numbers, 2×8 bytes per row, I wouldn't expect performance problems with 10,000 users and 100,000 posts (provided there are really groups, I mean, not all users belong to all groups and be able to read all posts - in this case, the table would be approximately 30 GB in size (plus the same size for the index necessary for the primary key).  This size can only be handled by appropriate hardware and some magic, like archiving old posts as those are queried rarely and so on.  If your system exceeds that size, you probably make nice money of it, being able to beef your system up :)


How can I write a routine to check, optimize and analyze all tables in databases in MySQL How can I create a routine in MySQL to check, optimize and then analyse the tables?

Also how can I schedule this routine to run every night at set time?

Thanks

I prefer writing it myself. Place these lines in /usr/local/sbin/OptimizeAllTables.sh

WHICH=/usr/bin/which
MYSQL=`${WHICH} mysql`
MYSQL_USER=root
MYSQL_PASS=rootpassword
MYSQL_CONN="-u${MYSQL_USER} -p${MYSQL_PASS}"
SQL="SELECT CONCAT('OPTIMIZE LOCAL TABLE ',db,'.',tb,';')"
SQL="${SQL} FROM (SELECT table_schema db,table_name tb,engine eng"
SQL="${SQL} FROM information_schema.tables WHERE"
SQL="${SQL} engine in ('InnoDB','MyISAM') AND table_schema NOT IN"
SQL="${SQL} ('information_schema','performance_schema','mysql')"
SQL="${SQL} ORDER BY data_length DESC) A"
${MYSQL} ${MYSQL_CONN} -ANe"${SQL}" > /tmp/OptimizeAllTables.sql
${MYSQL} ${MYSQL_CONN} < /tmp/OptimizeAllTables.sql

and make it executeable

chmod +x /usr/local/sbin/OptimizeAllTables.sh

CAVEAT #1

Running OPTIMIZE TABLE is the same as running ALTER TABLE ... ENGINE=... followed by ANALYZE TABLE. Therefore, there is no need to run a separate ANALYZE TABLE.

CAVEAT #2

Please note I use OPTIMIZE LOCAL TABLE instead of OPTIMIZE TABLE. This ensures that the OPTIMIZE TABLE is not recorded in the binary logs because it can replicate to Slaves. If you want the OPTIMIZE TABLE to be replicated all Slaves, OPTIMIZE TABLE is fine.

CAVEAT #3

Note that I ordered all the tables by the biggest table first. If you want to optimize all tables starting with the smallest table, change this line

SQL="${SQL} ORDER BY data_length DESC) A"

to

SQL="${SQL} ORDER BY data_length) A"

EPILOGUE

Simply place this line in the crontab to run every midnight

0 0 * * * /usr/local/sbin/OptimizeAllTables.sh

If the tables are enormous, you may want to schedule this for one a week (Sunday at Midnight)

0 0 * * 0 /usr/local/sbin/OptimizeAllTables.sh

Give it a Try !!!


Clean trace files on Oracle 11g The partition containing my Oracle 11g installation began to fill up with trace files and when I went looking for what are these I've found this disturbing post about how to disable the trace file generation.

The two related questions I have are:

Can I simply delete these trace files from my FS w/o consequence ? (I know, if they apeared one should probably look at them.. but won't happen and the database has no production use whatsoever. Going to have to happen another time.)
What should be done to prevent it from appearing again?

Yes, it's safe to delete them.
If you're running on Linux, set up logrotate to compress & then delete after a set period of time. Examples of Oracle logrotate configuration can be found here.


DB for storing simple time-stamped values I don't have that much experience in databases but I need to accomplish this as best as I can. It is rather specific.

Database will contain only data structured like this:
dataID-timestamp-value

Value could be of any datatype. There will be up to 20 million new entries per hour. Read queries would always look for one or few dataIDs in a specific time period, i.e. - yesterday between 12:00 - 13:00 or something like that. And these queries need to be fast on relatively weak hardware. Old data can be overwritten if space is scarce.

I am open to any suggestions, sql, nosql and so on.

Thank you.

How unique are the DataID's?  Are there just a handful of DataID's for the 20 million entries per hour or are DataID's more numerous?

But if your most frequent query is based on a narrow time frame first and then specific DataID's then this create statement should give you decent performance.  You will need to change the data types to what makes sense for your environment.

Note: statements are for MS-SQL

CREATE TABLE TestData
(
DataID VARCHAR(20) NOT NULL
, [TimeStamp] DATETIME2 NOT NULL
, Value VARCHAR(1000) NULL
)
GO

CREATE CLUSTERED INDEX IDX_C_TestData ON TestData ([TimeStamp], DataID)
GO


SQL Database Design: Balance between table scans and row duplication (Address example) This question is easily illustrated with an example. Suppose I have an Address table, with the columns: address_id, address_line_1,...,address_line_4, locality, region, postcode, country.

This table will be referenced by:

Users (home, default billing, default shipping etc.)
Venues
Orders (billing, shipping)
Bookings

Any Address referenced more than once will not be updatable/deletable.  For updates in this case, a new Address will be created and linked to as necessary.

I can think of three reasonable ways of storing information in the Address table:

Addresses are unique. Every time a new Address is created (e.g., by a User), the entire table must be scanned to see if the Address already exists.  I imagine this solution having an index supporting address_id, and an index supporting the actual Address columns (address_line_1,...,country).
Addresses are unique at the level of the Users or Venues.  If a new Address is added for a User or Venue, only the Addresses for that User or Venue need to be checked before adding another. Orders/Bookings reference these.
Addresses are not shared at all (i.e., any row in the Address table has precisely one Owner; a User or an Order, etc.). Every time there is a new Order, the appropriate User Addresses are duplicated in the Address table and referenced by the Order.

I don't really like the third solution, but choosing between the other two is harder.

Questions

Is it practical, at scale, to scan an entire table to see if an Address exists before adding it (Option 1)?
Is an index supporting a combination of eight columns reasonable (Option 1)?
Or is there value in striking a balance between row uniqueness and the amount of data to be scanned (as in Option 2)?
Is there a point at which one option becomes better than the other?

I would appreciate any advice.

Ensuring that addresses are unique is not a trivial problem. You have to assume that people enter their addresses in slightly different ways, abbreviating some parts sometimes, or omitting optional information. Just checking for exact duplicates would probably fail pretty often.

You should consider whether trying to keep addresses unique is worth the effort and complexity.

Addresses of users and addresses of orders are pretty different concepts. A user can change their current address. An order should never be retroactively changed in that way, the address there should always be the delivery address.

You could consider making addresses immutable. Changing an address would always mean creating a new address. This means you can safely point orders to the common address table, as the addresses will never change.

If a user changes their address, you simply add a new address and link it to the user. The old address can be hidden in the UI, soft-deleted or just shown as an address history. But all orders could still reference the old address because it never changes.

  Is it practical, at scale, to scan an entire table to see if an Address exists before adding it?

That depends entirely on what you consider "at scale", it's not an issue with a few tens of thousands of rows but completely impractical with a billion rows.

  Is an index supporting a combination of eight columns reasonable (as in Option 1)?

B-Tree indexes are limited to something around 2700 characters. Indexing very large items is typically not a good idea. In Postgres 10, which isn't released yet, you could consider a hash index for this which has a fixed size as far as I understand and should have an advantage for large columns.

But I really think you're asking the wrong question here. In your application, you should know when an address is already in the DB because the user didn't enter it from scratch, but e.g. selected it from the list of addresses already associated with their account.

You don't actually need to enforce uniqueness in your DB, if I understand your problem correctly. And if you'd need to do that, you should always use a unique constraint, which automatically uses an index.


Any way to verify user password from PL/PGSQL? I need to verify user password, but this user may be "local" (with md5 method in pg_hba.conf) or LDAP user. This works fine and "local" and LDAP users can login, but I need to verify their password in PL/PGSQL in some function. Is it possible? To call some Postgres system function like verify_login(userName TEXT, userPassword TEXT) which returns BOOLEAN and it knows how to check the password of the user when it is local/LDAP/etc. user.
PS. Little bit strange idea, but it's the requirement of my task.

I did it as:
CREATE FUNCTION login(auid TEXT, password TEXT, dbhost TEXT DEFAULT NULL) RETURNS ...
LANGUAGE PLPGSQL AS
$$
DECLARE
  rec RECORD;
  conn_str TEXT;
  dbname TEXT;
  conn_name TEXT;
  failed BOOLEAN := FALSE;
BEGIN
  SELECT u.uid AS role,u.my_role,u.display_name INTO rec FROM ldap_users u
    WHERE u.uid=auid AND u.my_role IS NOT NULL;

  IF NOT FOUND THEN
    RAISE EXCEPTION 'User not permitted to access TV'
      USING HINT='No such user or it is not allowed to access TV';
  END IF;

  IF dbhost IS NULL THEN
    dbhost := FORMAT('%s', INET_SERVER_ADDR());
  END IF;

  dbname := CURRENT_DATABASE();
  conn_str = 'host=' || dbhost || ' dbname=' || dbname || ' user=' || auid || ' password=' || password;
  conn_name := FORMAT('conn_%s', RANDOM());

  BEGIN
    PERFORM dblink_connect(conn_name, conn_str);
  EXCEPTION WHEN OTHERS THEN failed := TRUE;
  END;

  BEGIN
    PERFORM dblink_disconnect(conn_name);
  EXCEPTION WHEN OTHERS THEN NULL;
  END;

  IF failed THEN
    RAISE EXCEPTION 'User failed to login to the DB'
      USING HINT='It seems the password is wrong';
  ELSE
    ...
    RETURN ...
  END IF;

END;
$$;

Specific things are replaced with ... - they are not related to the question.
To use it you need CREATE EXTENSION IF NOT EXISTS dblink; to enable the dblink extension.
This login() function verifies the password of the user by connecting to itself (to the current host (if dbhost is NULL) and to the current database.
It was tested and I use it but it works fine only if the host has just one IP. Otherwise, it's not clean which IP will be returned from the INET_SERVER_ADDR().


Does SQL Server support GREATEST and LEAST, if not what is the common workaround? Reviewing this question it seems like that's a lot of work that shouldn't be needed. They're trying to extend a range with a date. In other databases, you would just use greatest and least..

least(extendDate,min), greatest(extendDate,max)

When I try to use these though, I get

'least' is not a recognized built-in function name.
'greatest' is not a recognized built-in function name.

That would cover extension in either direction.

For the purposes of the question, you would still have to do exclusive range replacement.

I'm just wondering how SQL Server users implement query patterns to mimic least and greatest functionality.

PostgreSQL GREATEST/LEAST
MySQL GREATEST/LEAST
MariaDB GREATEST LEAST
DB2 GREATEST LEAST
Oracle GREATEST LEAST

Do you unroll the conditions into CASE statements or is there an extension, third party add-on, or license from Microsoft that enables this functionality?

One common method is to use the VALUES clause, and CROSS APPLY the two columns aliased as a single column, then get the MIN and MAX of each.

SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
FROM   dbo.Users AS u
CROSS APPLY ( VALUES ( u.CreationDate ), ( u.LastAccessDate )) AS x ( CombinedDate );

There are other ways of writing it, for example using UNION ALL

SELECT MIN(x.CombinedDate) AS least, MAX(x.CombinedDate) AS greatest
FROM   dbo.Users AS u
CROSS APPLY ( SELECT u.CreationDate UNION ALL SELECT u.LastAccessDate ) AS x(CombinedDate);

However, the resulting query plans seem to be the same.


set default datatype = char (instead of varchar) I'm setting up a database in SQL-2014 that is exchanging data with a mainframe. All the fields are char, so there are trailing spaces. In table designer, my default field type is varchar.

Is there a way to change this one database so the default field type will be char as I create new tables?

The default column data type in the SQL Server Management Studio table designer is not currently configurable.  If this feature is important to you, consider submitting a suggestion on Connect.  Be sure to specify why the feature has value to you and your organization (e.g. enhanced productivity).

The default type is configurable in the Visual Studio IDE, though.  In VS 2015, the option is available under Options-->Database Tools-->Table and Database Designers-->Column Options.


Doing a bulk insert in SQL Server I have to do the insert operation in the db as follows,

I create an item
I create 5-10 properties for that item, all these data i get in the form of a collection of objects.

I donot want to repeatedly hit the db with the following sequence,
insert statement for item,
insert statement for property1,
insert statement for property2,
...,
insert statement for property10.

Kindly note that i can use the C# code transaction for item and properties, i can even insert the item and all properties inside this transaction.

Is there any other way than using the above technique. I also cannot run Stored Procedures or create files with SQL statements as the number of properties is not known before hand.

Can i use the following kind of one,

Begin;
INSERT into item values(...........);
INSERT INTO item values (............);
GO;

Will this execute all the insert statements in a single transaction.

Suggest the best practice to use with C# code.

One approach is to use SQLBulkCopy class. This allows you to insert many rows in one go from some source DataTable or Reader


Trying to add permission to a Role is failing I am trying to add a role into Create and Restore permissions and After researching online, it sounds like it should be as easy as using the GRANT statement. So far so good. However, my simple grant statement which looks like following:

GRANT CREATE, Restore ON dbo TO EventmanagerAdmin

Throws following error :

Msg 102, Level 15, State 1, Line 32
Incorrect syntax near 'CREATE'.

I wish I had more information to debug but that is all it says. Some more details in environment and background:

I am using SSMS 2012
The role does exist with Execute permission on this DB
I used script from List all permissions for a given role? to confirm that what permissions does the role have

Is there anything that I might be missing?

One thing to note is that RESTORE is a command, not really a permission that can be granted.  Which is why your GRANT failed.

In the SQL Server Books Online at https://msdn.microsoft.com/en-us/library/ms186858.aspx has pretty much said the same thing from SQL Server 2000 until now, namely:

RESTORE permissions default to members of the sysadmin and dbcreator fixed
server roles and the owner (dbo) of the database ... members of the db_owner
fixed database role do not have RESTORE permissions.

If the dbo, not db_owner, seems confusing it is like this.   One login owns the database, it may be sa or MyDomain\MyLogin.   That login maps to the dbo user and should have rights to restore the database.   Inside the
database, many users may be in the db_owner role, but since those are inside
the database to be restored, they do not get the RESTORE permission.

So, you can make logins members of the dbcreator fixed server role or you
can make one user the owner of a database and he should then be able to do a
restore of that database.

To test the consequences I created a login TestLogin that is not a sysadmin for these tests.

TEST 1: Granted TestLogin the dbcreator server role and the public role
in MyDatabase.  (It is necessary that TestLogin have some rights inside
the database to be restored.)

RESTORE DATABASE MyDatabase ... was successful.

NOTE:  If the restored database did not already have TestLogin as a user,
TestLogin lost access to the database once the restore was complete since
the sysusers data was restored with the contents from the backup.  So,
TestLogin needs to exist in the source backup if this user is to retain some internal rights after the restore.

TEST 2: Revoked TestLogin from the dbcreator server role and the publicrole in MyDatabase.  Made TestLogin the owner of the database.

USE MyDatabase
exec sp_changedbowner 'TestLogin'

RESTORE DATABASE MyDatabase ... was successful.

In other words, dbcreator can create NEW databases, but can only restore a
database where it is already a user and still retain rights to that database.  Hope that this helps you decide what to do.


Table properties in SSMS and sp_spaceused don't match I have a table Table_A where when I right-click the table in SSMS and go to Properties > Storage, it shows the following:

Data space: 5 GB
Index space: 250 MB
Number of partitions: 400
Partitions using default columnstore compression: 1-400

if I do sp_spaceused Table_A, it shows entirely different numbers (rounded):

Name    rows    reserved        data            index_size  unused
Table_A 5000000 100000000 KB    100000000 KB    25000 KB    28000 KB

What could be causing the difference in space consumed?

The database's properties page is showing that 100 GB reserved space as "Available", but fails to reclaim any of it during a dbcc shrinkdb

This table "grew" suddenly while rebuilding a clustered columnstore index where the index size grew to ~20 GB, but shrank back down after the job completed. I'm still trying to wrap my head around columnstore compression and the indexes, so I feel like I'm missing something simple

Data space used

The query that gets used to calculate the data space on the table via ssms when selecting the storage properties of my dbo.Employees table

exec sp_executesql N'
        declare @PageSize float
        select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

SELECT

        case
        when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)
        FROM sys.indexes as i
        JOIN sys.partitions as p ON p.object_id = i.object_id and p.index_id = i.index_id
        JOIN sys.allocation_units as a ON a.container_id = p.partition_id
        where i.object_id = tbl.object_id),0.0)
        else
        isnull((select (tms.[memory_used_by_table_kb])
        from [sys].[dm_db_xtp_table_memory_stats] tms
        where tms.object_id = tbl.object_id), 0.0)
        end
       AS [DataSpaceUsed]
FROM
sys.tables AS tbl
WHERE
(tbl.name=@_msparam_2 and SCHEMA_NAME(tbl.schema_id)=@_msparam_3)
OPTION (FORCE ORDER)
',N'@_msparam_0 nvarchar(4000),@_msparam_1 nvarchar(4000),@_msparam_2 nvarchar(4000),@_msparam_3 nvarchar(4000)',@_msparam_0=N'1',@_msparam_1=N'E',@_msparam_2=N'Employees',@_msparam_3=N'dbo'

Executing this query gives me:

DataSpaceUsed
20008

This is the data in KB's, for the calculation, keep reading.

Via SSMS it gives me:

Lets explore a part of this query further, for normal, non memory optimized tables.

when (tbl.is_memory_optimized=0) then
        ISNULL((select @PageSize * SUM(CASE WHEN a.type <> 1 THEN a.used_pages WHEN p.index_id < 2 THEN a.data_pages ELSE 0 END)

With the pagesize variable as a value of dbo.spt_values / 1024

select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=@_msparam_0 and v.type=@_msparam_1

Which can be changed to

    declare @PageSize float
    select @PageSize=v.low/1024.0 from master.dbo.spt_values v where v.number=1 and v.type='E'
  select @PageSize

= 8

Which is the 8KB page size as used by sql server

Then the next part, CASE WHEN a.type <> 1 THEN a.used_pages

With the type 1 being:

  1 = In-row data (all data types, except LOB data types)

Source

And the next part

WHEN p.index_id < 2 THEN a.data_pages ELSE 0

To get the clustered / heap data pages, without the nonclustered index pages.

So in short, get the lob data & data pages belonging to the heap or clustered index that is in sys.allocation_units

Where does this take us?

This gets us to link to a different Q/A, with the asker trying to get a performant and correct space used query by using the DMV's.

Same goes for the other linked answer, both excellent answers by by Solomon Rutzky

  The following base query works. It gets the same output as
  sp_spaceused, using the same algorithm, but much more efficiently...

So yeah, sp_spaceused will be more correct as the algorithm used to calculate the size accounts for more information, the query in the linked Q/A does the same but more efficiently.

You can take a look at the dmv's touched by running sp_spaceused with the actual execution plan enabled with CTRL+P, or look at this one, when I ran sp_spaceused on dbo.Employees

The usage of sys.dm_db_partition_stats in sp_spaceused and the query linked
vs.  the usage of sys.allocation_units via ssms is also apparent.


Storing prices with price history and different specificity (global, user group, single user) in MySQL database I need to store prices with their history. A price can have different specificity. It can refer to a single user (most specific), group (less specific) and product price (global - least specific). There are also two different types of prices (product and delivery price). Now I am having trouble with this. I made it to work, but I somehow feel bad about it when I write queries (they tend to get long and complicated).

I designed two tables like this (they are same):

base_prices

| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |

delivery_prices

| id | referent_id | product_id | valid_from | valid_until | amount | pieces_per_lot |

For a user specific price referent id would be a positive integer, matching users id.
For a group specific price referent id would be a negative integer, matching group id (I made group ids negative integers).
For global product price, referent id will be NULL.
This way, when I query price for a specific item and specific user and his group all I need to do is filter table by item_id and order results by referent_id column in descending order. This way, user specific are on top, group after and global are last.

Date ranges valid_from-valid_until cannot overlap for two rows.

Now a query to get current prices would look something like this:

SELECT
    *
FROM
    base_prices AS outer
WHERE
    id = (
        SELECT
            id
        FROM
            base_prices AS inner
        WHERE
                NOW() BETWEEN valid_from AND valid_until
            AND inner.item_id = outer.item_id
            AND (
                    inner.referent_id = # HERE GOES USER ID
                OR  inner.referent_id = # HERE GOES GROUP ID
                OR  inner.referent_id IS NULL
            )
        ORDER BY
                inner.referent_id DESC # FOR SPECIFICITY

Thing that bugs me is that I always have to write this kind of sub-query to get items, and this doesn't seem as a good design to me. When data starts to build up, this queries will cost much more. Also, I had to write a few triggers to keep data integrity during inserts and updates.

My opinion is that current prices and historical prices need to be separated, but I was in a hurry, so this is what came up. I was thinking about scheduling events during inserts based on valid_from column that will move data from historical prices table to (new) current prices table.

I am inexperienced, so this seems kind of weird to me. It might be OK, though, but I would like to see some suggestions and opinions.

If I left out something or there is not enough info, leave a comment.

There are a couple of ways you could separate the current values from the historic ones: you could simply include a boolean field that is true for the latest price and filter on that, or you could keep current prices in a separate table. Both options involve a little extra work to maintain integrity but would make a query for current prices more efficient. This doesn't remove the need for the sub-query to pick the first row that matches the user/group/NULL condition, though fewer rows will be scanned in processing both the inner and outer queries.

The following might be more efficient as a straight query if only the current prices are in the base table (add the is_latest=true check or your current date filter to the where and on clauses if you keep the historic values in the same table):

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   base_prices AS nullmatch
LEFT OUTER JOIN
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = @GroupIDHere
LEFT OUTER JOIN
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = @UserIDHere
WHERE nullmatch.referent_id IS NULL

This will only work if you always have a row for each product where referent_id is null, otherwise you could try:

SELECT CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field1 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field1 ELSE nullmatch.field1 END AS field1
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.field2 WHEN groupmatch.id IS NOT NULL THEN groupmatch.field2 ELSE nullmatch.field2 END AS field2
     (... and so on ...)
     , CASE WHEN usermatch.id IS NOT NULL THEN usermatch.fieldN WHEN groupmatch.id IS NOT NULL THEN groupmatch.fieldN ELSE nullmatch.fieldN END AS fieldN
FROM   users
CROSS JOIN
       base_prices AS nullmatch
LEFT OUTER JOIN
       base_prices AS groupmatch ON groupmatch.id=nullmatch.id AND nullmatch.referent_id = users.groupid
LEFT OUTER JOIN
       base_prices AS usermatch ON usermatch.id=nullmatch.id AND nullmatch.referent_id = users.id
WHERE users.id = @UserIDHere
AND   nullmatch.referent_id IS NULL

In both cases be careful using this construct in a view because further filtering by the fields brought out via those case statements will not be able to use any indexes you may have defined.

Obviously the case statement per field is a bit of faf but it should stop the query runner searching the table once for every row returned as it might with the sub-query arrangement.

In other databases you might be able to use windowing functions (like ROW_NUMBER) to do this more conveniently and efficiently, but mySQL does not support those to my knowledge.


pg_upgrade: Sequence-Check fails After pg_upgrade one check fails.

This SQL gets executed to list tables and their sequences. In comments to this question it is called "above sql".

SELECT S.relname, T.relname, C.attname
FROM pg_class AS S,
     pg_depend AS D,
     pg_class AS T,
     pg_attribute AS C,
     pg_tables AS PGT
WHERE S.relkind = 'S'
    AND S.oid = D.objid
    AND D.refobjid = T.oid
    AND D.refobjid = C.attrelid
    AND D.refobjsubid = C.attnum
    AND T.relname = PGT.tablename
ORDER BY S.relname;

Source: https://wiki.postgresql.org/wiki/Fixing_Sequences

This check gets used to see of there are sequences which next-value is too low.

This check gets executed daily since several years and it did not fail since several years. This was the first time that we used pg_upgrade and not dump+restore.

Now I am a bit nervous, that some internal references are broken.

The output for auth_user_groups_id_seq is broken:

psql -f above.sql| grep auth_user_groups_id_seq
 auth_user_groups_id_seq    | auth_user_groups        | id
 auth_user_groups_id_seq    | modwork_emailsender     | id

The first line is correct, the second seems broken.

The table modwork_emailsender does not have the sequence auth_user_groups_id_seq.

Has someome an idea why above sql outputs strange values after pg_upgrade?

Versions: We upgraded from 9.3 to 10.2.

Update

We found a different SQL to get all sequences:

select s.relname as seq, n.nspname as sch, t.relname as tab, a.attname as col
from pg_class s
  join pg_depend d on d.objid=s.oid and d.classid='pg_class'::regclass and d.refclassid='pg_class'::regclass
  join pg_class t on t.oid=d.refobjid
  join pg_namespace n on n.oid=t.relnamespace
  join pg_attribute a on a.attrelid=t.oid and a.attnum=d.refobjsubid
where s.relkind='S' and d.deptype='a'

Source: https://stackoverflow.com/questions/6941043/get-table-and-column-owning-a-sequence/6945493#6945493

The first query returns 140 rows (including strange results), the second 120 (without strange results).

we face the same problem. Apparently this issue is fixed in version 10.16. I tested 10.9,10.13,10.14,10.5 and all have the same issue.
But with 10.16 it's ok.
I don't see in release note any information on it.


Timestamp with no time zone - if I'm ok with UTC at all times, is there any downside to using it? Timestamp with time zone vs timestamp with no time zone.

I'm aware that timestamptz is the same as timestamp no time-zone + (time-zone difference) that gets added upon saving a value in a database. That is, in a db, when stored, they both are indistinguishable.

If I decide to use timestamps with no time-zone only, even in the cases when normally timestamps with time-zone would be recommended to be used instead, but if I'm and users of my website are ok with keeping in mind that I and they would be operating with UTC only all the time, even if I or them happen to be physically in different time zones, would timestamps with no time-zone work out well and with no flaws?

If all are aware that the timestamp value is supposed to represent UTC time it should work, basically. A convenient advantage is that default input and output is less noisy.

I would still suggest timestamptz. Here are some reasons:

1.

timestamptz literally is the "preferred type" among date/time types in Postgres. See:

test=> SELECT typname, typispreferred FROM pg_type WHERE typcategory = 'D';
   typname   | typispreferred
-------------+----------------
 date        | f
 time        | f
 timestamp   | f
 timestamptz | t  -- !
 timetz      | f
 time_stamp  | f
(6 rows)

The manual about pg_types.

This can work in favor of timestamptz in corner cases of type/function/operator resolution.

2.

When you have timestamptz literals as data input, that just works with type timestamptz:

'2020-04-22 04:46:46.790969+02'

Inserting the same into a timestamp column would ignore the time offset (lose information). One would have to cast to timestamptz explicitly, then transpose to UTC timestamp with something like:

'2020-04-22 04:46:46.790969+02'::timestamptz AT TIME ZONE 'UTC'

The opposite direction is less error prone. When inserting a timestamp literal into a timestamptz column, the current time zone is assumed in the absence of explicit information.

3.

now() and transaction_timestamp() return timestamptz. If you need timestamp, you have to cast. Internally, LOCALTIMESTAMP does just that: it casts transaction_timestamp(). To get the current timestamp for UTC you need now() AT TIME ZONE 'UTC' or similar.

4.

Displaying timestamps for a given time zone is simpler based on timestamptz:

SELECT my_timestamptz AT TIME ZONE 'US/Hawaii';

That's more work with timestamp. We know it's supposed to be UTC time. But Postgres does not until we say so, explicitly:

SELECT my_timestamp AT TIME ZONE 'UTC' AT TIME ZONE 'US/Hawaii'

Related:

Ignoring time zones altogether in Rails and PostgreSQL


Return 2 columns from MySQL subquery? I'm new in MySQL programing and I have one problem.
I created this query:

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost
FROM   topics
       INNER JOIN categories
               ON topics.categoryid = categories.id
       INNER JOIN users
               ON topics.userid = users.id
ORDER  BY topics.lastupdate DESC

This query lists all Topics, joins categories and users by UserID and CategoryID in Topics and Username from TopicAnswers as LastPost. It orders them descending by LastUpdate in Topics that controlled by TRIGER.

It work pretty well, but there is a problem with this subquery:

            (SELECT (SELECT username
                    FROM   users
                    WHERE  id = topicanswers.userid)
            FROM   topicanswers
            WHERE  topicid = topics.id
            ORDER  BY id DESC
            LIMIT  1)        AS LastPost

It selects Username, of last post in topic, from Users by UserID from TopicAnswers as LastPost, now I want to select another one column from table Users, AvatarURL, but when I add :

                        (SELECT username, avatarurl
                        FROM   users
                        WHERE  id = topicanswers.userid)

It returns error:

  1241 Operand should contain 1 column(s)

How I can slove this problem?

it is absolutely correct error, You are select single column in main select, so it must return only one column for avoid collisions

for each column make a new sub-select:

                (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid) as username

                (SELECT avatarurl
                FROM   users
                WHERE  id = topicanswers.userid) as avatarur1

the same will be relate and to TOP level select:

   (SELECT (SELECT username
            FROM   users
            WHERE  id = topicanswers.userid)

IF ADD SECOND QUERY THERE - it will throw an error for TOP Level SELECT

FROM   topicanswers
WHERE  topicid = topics.id
ORDER  BY id DESC
LIMIT  1)        AS LastPost

You query should be like this :

SELECT *,
       topics.createdate AS TopicCreateDate,
       (SELECT (SELECT username
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_name,

       (SELECT (SELECT avatarur1
                FROM   users
                WHERE  id = topicanswers.userid)
        FROM   topicanswers
        WHERE  topicid = topics.id
        ORDER  BY id DESC
        LIMIT  1)        AS LastPost_avatarur1

    FROM   topics
           INNER JOIN categories
                   ON topics.categoryid = categories.id
           INNER JOIN users
                   ON topics.userid = users.id
    ORDER  BY topics.lastupdate DESC


mysql replication - missing table for foreign key on slave i have the following situation:
We're running a simple master slave replication with table filters, so the slaves doesn't have all the master tables.

Master has table A with FK to table B
Slave  has table A with FK to table B which doesn't exists (since it's filtered out)

When a new record is inserted to table A on the master, the SQL_THREAD on the slave is failing due to FK error.

  Could not execute Write_rows event on table DB.A; Cannot add or update a child row: a foreign key constraint fails (DB.A, CONSTRAINT FK_name FOREIGN KEY (TypeId) REFERENCES B (TypeId) ON DELETE NO ACTION ON UPDATE NO ACTION), Error_code: 1452; handler error HA_ERR_NO_REFERENCED_ROW;

I've tried to set session and global foreign_key_checks=0
and also tried setting @@global.init_slave='SET foreign_key_checks=0'

none of them seems have effect and i still get the above error.

Is there a way to make the slave ignore FK checks?

Thanks.

Since error 1452 breaks the SQL thread, you can make MySQL Replication skip that error by adding the slave-skip-errors option under the [mysqld] group header in the Slave's my.cnf as follows:

[mysqld]
slave-skip-errors=1452

You will have to restart mysql for this to take effect.

Give it a Try !!!

CAVEAT : Your data integrity will deteriorate when added new data because many INSERT queries or multiple-row INSERTs will fail because of just one row not having parent keys.

You should really consider loading all referenced tables into the slave to have good, clean data. Otherwise, it's not a true copy of the Master.


How to use MariaDB's new "Invisible" column type I have been playing around with this new column type. The use case being that I want to hide this column from a client issuing a "Select *" query.

But the column seems to behave like any normal column and is not hidden. I have enabled the Invisible column type by using the following command:

ALTER TABLE MyTable ADD COLUMN `TestA` INT INVISIBLE;

Reading up in the MariaDB documentation, the examples provided are only with Create statements. Which makes me wonder if one can only enable this feature when creating a table and NOT with an Alter statement. Can anybody shed some light?

The INVISIBLE is an attribute of the column it can be set CREATE TABLE and ALTER TABLE statements.

I've updated the reference page document to show this.

 CREATE TABLE t (x INT, y INT INVISIBLE, z INT INVISIBLE NOT NULL DEFAULT 4);

 DESC t;

Field | Type    | Null | Key | Default | Extra
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    |
y     | int(11) | YES  |     | null    | INVISIBLE
z     | int(11) | NO   |     | 4       | INVISIBLE

ALTER TABLE t MODIFY x INT INVISIBLE, MODIFY y INT, MODIFY z INT NOT NULL DEFAULT 4

DESC t

Field | Type    | Null | Key | Default | Extra
:---- | :------ | :--- | :-- | :------ | :--------
x     | int(11) | YES  |     | null    | INVISIBLE
y     | int(11) | YES  |     | null    |
z     | int(11) | NO   |     | 4       |

db<>fiddle here


How to get a list of restricted_components with it's related information? I have the following tables structure (DDL):

CREATE TABLE restricted_components
(
    component_id INT(11) PRIMARY KEY NOT NULL,
    resources_id INT(11) NOT NULL,
    component_dom_id VARCHAR(50) NOT NULL,
    component_dom_class VARCHAR(50),
    component_dom_data_name VARCHAR(50),
    visible TINYINT(1) DEFAULT '0' NOT NULL
);

CREATE TABLE resources
(
    resources_id INT(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
    resources_name VARCHAR(64) NOT NULL,
    menu_href VARCHAR(128),
    menu_text VARCHAR(64) NOT NULL
);

CREATE TABLE acl_groups_to_resources
(
    groups_id INT(10) unsigned NOT NULL,
    resources_id INT(10) unsigned NOT NULL,
    bitmask SMALLINT(6) DEFAULT '0' NOT NULL,
    CONSTRAINT `PRIMARY` PRIMARY KEY (groups_id, resources_id)
);

CREATE TABLE groups
(
    groups_id INT(11) PRIMARY KEY NOT NULL AUTO_INCREMENT,
    groups_name VARCHAR(50) NOT NULL
);

I am trying to get a list of restricted_components with it's related information from the rest of the tables meaning the resources_name and groups_name.

I have made this query:

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  LEFT JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  LEFT JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id

But is wrong since I have unwanted results as the image below shown:

I am sure the problem is on the LEFT JOIN but I can't find where exactly and how to fix this.

Update #1: Using JOIN instead doesn't work either.

I believe the problem is in the data but I am not sure at all, hopefully someone will have a time to check this.

Can I get any help?

  Note: I have leave a set of data here so you can play with it if needed but is not accurate, you'll need to remove some columns and it's values.

Playing with LEFT + INNER You can change logic of SQL query

If You need all from restricted_component - LEFT JOIN
But because groups must be for all - INNER

SELECT
  `rc`.*,
  `rs`.resources_name,
  `gr`.`groups_name`
FROM `restricted_components` AS `rc`
  LEFT JOIN `resources` AS `rs` ON rc.resources_id = rs.resources_id
  INNER JOIN `acl_groups_to_resources` AS `acl_gr` ON rs.resources_id = acl_gr.resources_id
  INNER JOIN `groups` AS `gr` ON acl_gr.groups_id = gr.groups_id
ORDER BY component_id

result from Your dataset

1   1   button_1    button_1        1   Users   Administrators
1   1   button_1    button_1        1   Users   User Admins
2   2   button_2            0   Companies   Administrators
3   3   button_3            0   Hardware    Administrators
4   4   button_4            0   Reports Administrators
4   4   button_4            0   Reports Reports Admin
5   5   button_5            0   Notifications   Administrators
5   5   button_5            0   Notifications   Company Admins


How can I use a MySQL query in an openquery query on Microsoft SQL Server.? I have the following query which works fine in MySQL:

DELETE sb
FROM stockbatches sb
    INNER JOIN batchestodelete btd
ON sb.batch_batch = btd.batchno
    AND sb.batch_part = btd.batchpart

What I want to do is run this query on the MySQL table from SQL Server using an openquery to a linked MySQL server.  I cannot get the syntax right.

Can anybody help please?

According to the OPENQUERY syntax you'd have to rewrite the script in the following way:

DELETE OPENQUERY (MySQLServer, '
    SELECT sb.*
    FROM stockbatches sb
    INNER JOIN batchestodelete btd
    ON sb.batch_batch = btd.batchno
    AND sb.batch_part = btd.batchpart;
');


SQL Server unpredictable select results (dbms error?) Below is simple example, which returns strange results, that are unpredictable and we cannot explain it in our team. Are we doing something wrong or is it SQL Server error?

After some investigation we reduced the search area to union clause in subquery, which selects one record from "men" table

It works as expected in SQL Server 2000 (returns 12 rows), but in 2008 and 2012 it returns only one row.

create table dual (dummy int)

insert into dual values (0)

create table men (
man_id int,
wife_id int )

-- there are 12 men, 6 married
insert into men values (1, 1)
insert into men values (2, 2)
insert into men values (3, null)
insert into men values (4, null)
insert into men values (5, null)
insert into men values (6, 3)
insert into men values (7, 5)
insert into men values (8, 7)
insert into men values (9, null)
insert into men values (10, null)
insert into men values (11, null)
insert into men values (12, 9)

This returns only one row:   1  1   2

select
man_id,
wife_id,
(select count( * ) from
    (select dummy from dual
     union select men.wife_id  ) family_members
) as family_size
from men
--where wife_id = 2 -- uncomment me and try again

Uncomment last line and it gives: 2 2   2

There is a lot of odd behaviors:

After series of drops, creates, truncates and inserts on "men" table it sometimes works (returns 12 rows)
When you change "union select men.wife_id" to "union all select men.wife_id" or "union select isnull(men.wife_id, null)" (!!!) it returns 12 rows (as expected).
The strange behavior seems to be unrelated to datatype of column "wife_id". We observed it on development system with much greater data sets.
"where wife_id > 0" returns 6 rows
we also observes strange behavior of views with this kind of statements. SELECT * returns subset of rows, SELECT TOP 1000 returns all

Are we doing something wrong or is it SQL Server error?

It is a wrong-results bug, which you should report via your usual support channel. If you do not have a support agreement, it may help to know that paid incidents are normally refunded if Microsoft confirms the behaviour as a bug.
The bug requires three ingredients:

Nested loops with an outer reference (an apply)
An inner-side lazy index spool that seeks on the outer reference
An inner-side Concatenation operator

For example, the query in the question produces a plan like the following:

There are many ways to remove one of these elements, so the bug no longer reproduces.
For example, one could create indexes or statistics that happen to mean the optimizer chooses not to utilize a Lazy Index Spool. Or, one could use hints to force a hash or merge union instead of using Concatenation. One could also rewrite the query to express the same semantics, but which results in a different plan shape where one or more of the required elements are missing.
More details
A Lazy Index Spool lazily caches inner side result rows, in a work table indexed by outer reference (correlated parameter) values. If a Lazy Index Spool is asked for an outer reference it has seen before, it fetches the cached result row from its work table (a "rewind"). If the spool is asked for an outer reference value it has not seen before, it runs its subtree with the current outer reference value and caches the result (a "rebind"). The seek predicate on the Lazy Index Spool indicates the key(s) for its work table.
The problem occurs in this specific plan shape when the spool checks to see if a new outer reference is the same as one it has seen before. The Nested Loops Join updates its outer references correctly, and notifies operators on its inner input via their PrepRecompute interface methods. At the start of this check, inner side operators read the CParamBounds:FNeedToReload property to see if the outer reference has changed from last time. An example stack trace is shown below:

When the subtree shown above exists, specifically where Concatenation is used, something goes wrong (perhaps a ByVal/ByRef/Copy problem) with the bindings such that CParamBounds:FNeedToReload always returns false, regardless of whether the outer reference actually changed or not.
When the same subtree exists, but a Merge Union or Hash Union is used, this essential property is set correctly on each iteration, and the Lazy Index Spool rewinds or rebinds each time as appropriate. The Distinct Sort and Stream Aggregate are blameless, by the way. My suspicion is that Merge and Hash Union make a copy of the previous value, whereas Concatenation uses a reference. It is just about impossible to verify this without access to the SQL Server source code, unfortunately.
The net result is that the Lazy Index Spool in the problematic plan shape always thinks it has already seen the current outer reference, rewinds by seeking into its work table, generally finds nothing, so no row is returned for that outer reference. Stepping through the execution in a debugger, the spool only ever executes its RewindHelper method, and never its ReloadHelper method (reload = rebind in this context). This is evident in the execution plan because operators under the spool all have 'Number of Executions = 1'.

The exception, of course, is for the first outer reference the Lazy Index Spool is given. This always executes the subtree and caches a result row in the work table. All subsequent iterations result in a rewind, which will only produce a row (the single cached row) when the current iteration has the same value for the outer reference as the first time around.
So, for any given input set on the outer side of the Nested Loops Join, the query will return as many rows as there are duplicates of the first row processed (plus one for the first row itself of course).
Demo
Table and sample data:
CREATE TABLE #T1
(
    pk integer IDENTITY NOT NULL,
    c1 integer NOT NULL,

    CONSTRAINT PK_T1
    PRIMARY KEY CLUSTERED (pk)
);
GO
INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6);

The following (trivial) query produces a correct count of two for each row (18 in total) using a Merge Union:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C;

If we now add a query hint to force a Concatenation:
SELECT T1.c1, C.c1
FROM #T1 AS T1
CROSS APPLY
(
    SELECT COUNT_BIG(*) AS c1
    FROM
    (
        SELECT T1.c1
        UNION
        SELECT NULL
    ) AS U
) AS C
OPTION (CONCAT UNION);

The execution plan has the problematic shape:

And the result is now incorrect, just three rows:

Though this behaviour is not guaranteed, the first row from the Clustered Index Scan has a c1 value of 1. There are two other rows with this value, so three rows are produced in total.
Now truncate the data table and load it with more duplicates of the 'first' row:
TRUNCATE TABLE #T1;

INSERT #T1 (c1)
VALUES
    (1), (2), (3), (4), (5), (6),
    (1), (2), (3), (4), (5), (6),
    (1), (1), (1), (1), (1), (1);

Now the Concatenation plan is:

And, as indicated, 8 rows are produced, all with c1 = 1 of course:

I notice you have opened a Connect item for this bug but really that is not the place to report issues that are having a production impact. If that is the case, you really ought to contact Microsoft Support.

This wrong-results bug was fixed at some stage. It no longer reproduces for me on any version of SQL Server from 2012 onward. It does repro on SQL Server 2008 R2 SP3-GDR build 10.50.6560.0 (X64).


How do I disable escaping when using COPY FROM in PostgreSQL? I have a large tab delimited file that I want to read into a table in PostgreSQL 9.5.  It contains double-quotes and backslashes that I want to treat as regular characters.

I think COPY FROM is the way to go, but I can't figure out how to disable escaping.

Here is a sample of the data (it's from Google's ngram dataset):

aX13_X  2006    8   5
aX13_X  2007    4   3
aX13_X  2008    2   1
a\  1852    1   1
a\  1935    1   1
a\  1937    2   2
ACT1V1T1ES  2003    15  11
ACT1V1T1ES  2004    63  6
ACT1V1T1ES  2005    1   1
ACT1V1T1ES  2006    5   4
ACT1V1T1ES  2008    4   3
ACTION="    1995    3   3
ACTION="    1996    6   5
ACTION="    1997    9   7
ACTION="    1998    19  11
ACTION="    1999    11  5

And the table:

CREATE TABLE onegram (
    id SERIAL,
    ngram character text,
    year integer NOT NULL,
    match_count integer NOT NULL,
    volume_count integer NOT NULL
);

If I try without modifiers, the backslash escapes the tab:

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv';
ERROR:  missing data for column "volume_count"
CONTEXT:  COPY onegram, line 4: "a\     1852    1       1"

So I switch to CSV, and then the double quote characters quote the tabs:

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv' WITH CSV DELIMITER E'\t';
ERROR:  unterminated CSV quoted field
CONTEXT:  COPY onegram, line 17: "ACTION="      1999    11      5
"

Using CSV lets me use the DELIMITER keyword.  It works if I pick a delimiter that doesn't occur in the sample (space, in this case):

ngram=# copy onegram (ngram, year, match_count, volume_count)
from '/home/tims/data/ngram/test.tsv' WITH CSV DELIMITER E'\t' QUOTE E' ';
COPY 16

But I want to be able to include any character (except for tab and newline).  So, how can I disable QUOTE?  Or what can I use instead of COPY FROM?

Edit: For slightly arbitrary reasons, ideally I'd like an option that does not involve pre-processing the data.

The copy command by default uses text format with tab delimiter. So only one thing you need is to escape backslashes:

copy onegram (ngram, year, match_count, volume_count)
from program 'sed ''s/\\/\\\\/g'' < /home/tims/data/ngram/test.tsv';
select * from onegram;
╔════╤════════════╤══════╤═════════════╤══════════════╗
║ id │   ngram    │ year │ match_count │ volume_count ║
╠════╪════════════╪══════╪═════════════╪══════════════╣
║  1 │ aX13_X     │ 2006 │           8 │            5 ║
║  2 │ aX13_X     │ 2007 │           4 │            3 ║
║  3 │ aX13_X     │ 2008 │           2 │            1 ║
║  4 │ a\         │ 1852 │           1 │            1 ║
║  5 │ a\         │ 1935 │           1 │            1 ║
║  6 │ a\         │ 1937 │           2 │            2 ║
║  7 │ ACT1V1T1ES │ 2003 │          15 │           11 ║
║  8 │ ACT1V1T1ES │ 2004 │          63 │            6 ║
║  9 │ ACT1V1T1ES │ 2005 │           1 │            1 ║
║ 10 │ ACT1V1T1ES │ 2006 │           5 │            4 ║
║ 11 │ ACT1V1T1ES │ 2008 │           4 │            3 ║
║ 12 │ ACTION="   │ 1995 │           3 │            3 ║
║ 13 │ ACTION="   │ 1996 │           6 │            5 ║
║ 14 │ ACTION="   │ 1997 │           9 │            7 ║
║ 15 │ ACTION="   │ 1998 │          19 │           11 ║
║ 16 │ ACTION="   │ 1999 │          11 │            5 ║
╚════╧════════════╧══════╧═════════════╧══════════════╝


Connection issue to Mysql from Apache server I am operating on Centos7 and Mysql 5.6.46. I needed to move the default datadir location to an encrypted partition and I changed my default /etc/my.cnf file accordingly:

# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/5.6/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/NEW_FOLDER/mysql
socket=/NEW_FOLDER/mysql/mysql.sock

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

# Recommended in standard MySQL setup
#sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
sql_mode=""

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
port=3306
socket=/NEW_FOLDER/mysql/mysql.sock

After restarting, I verify that I can connect to Mysql by writing: mysql -u <USERNAME> -p, no issue there.
What my issue is has to do with when my CGI-based server is trying to access the DB. I get the error: Error connecting to database: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) which does not make much sense to me since the default socket directory has changed.

I looked into the Apache error log and I see this:

DBI connect('testuser:localhost','testdb',...) failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) at /srv/www/TestSite/perl-lib/Database.pm line 31.

I go to line 31 and I see this:

my $dsn = "DBI:mysql:testdb:localhost";
    $dbh = DBI->connect($dsn, 'testuser', 'testpasswd')
      or throw Portal::DatabaseError("Error connecting to database: $DBI::errstr");

The way I see it, Apache is trying to access Mysql but it cannot use the mysql.socket from the new default mysql datadir location. Any ideas what to do?

put the new socket location under the [client] section (as well as [mysqld] like:

[client]
socket=/NEW_FOLDER/mysql/mysql.sock


Using surrogate key after merging the person's data? This question is about business intelligence with sql server 2008 R2.

If you have a person named Jim Brown with his birthdate and social security number (SSN) being located in three different source system.

Do you use surrogate key that should contain Jim's birthday and SSN after you have merged three data row into single one/row in the ETL phase?

A surrogate key is a system assigned unique value to identify an entity occurrence.  A natural key is what the business uses to identify an entity occurrence.  The source systems, as well as your BI/Data Integration database, can use either type to identify the entity occurrence - such as Jim Brown in your example.  In the source system we call what the source system uses to identify the entity occurrence a source key.  So if you can have 3 different source systems each of which contain Jim Brown, each will have a different source key in addition to the natural key - which you have identified as the SSN + birthdate.  The BI staging environment, which the ETL uses, will include a key map table which will map each source key to the assigned surrogate key for the BI database.  So for example:

Person  Table
Surrogate Key   Name            Birthday     SSN          Other fields
1                   Jim Brown   9-15-1988    123456789

Person Key Map
Surrogate Key   Source Key  Source System
1                   12345           System A
1                   230383          System B
1                   294829          System C

When a transaction against Jim Brown is to be processed, it will come from the source using the source key.  Say its system A with source key 12345 with an update to one of the other fields.  The ETL looks up the source key in the key map and finds it, and knows to apply that transaction to the row on Person with a surrogate key of 1.

If you only had 1 source system life would be easy.  But you have 3.  This is where the natural key comes in as you know you can identify Jim in any system by using these 2 characteristics that never change and are true about him.  Now, say you integrate System D.  The first transaction comes in for Jim Brown in system D, and system D's source key is AB2945.  Now when the ETL looks in the key map, it doesn't find it.  But since you know you have multiple systems that may contain a person, the ETL also does a lookup on the Person table for the natural key and voila - a match.  Now the ETL inserts a new row to the key map for System D also for Jim Brown.

So in summary you always want to use a surrogate key as the PK in the BI database table when doing data integration of the same logical entity among many sources.  You'll map it to the source key using a key map table, and prevent duplicates by looking up on the natural key in the BI table (along with an alternate unique key on the natural key to ensure no duplicates).  Now there are many other details to work out - like is your natural key really unique, do you want to store it in your key map to prevent look ups on Person, do you have to handle composite source keys, do you keep a history of source key changes, do you track a system of record and systems of reference, how do you handle source key reuse (a potentially very sticky issue in its own right), etc.  But this short summary gives you and idea of the differences between natural, surrogate, and source keys and where to use each in the BI scenario.  I hope this helps...


Postgres knowing which where column returned positive result Assuming we have a query like:

select * from table where a=1 or b=2 or c=3

and the where conditions met are a=1 and c=3

Would it be possible to know in the query (in the output) that a and c are the columns that matched the where clause?

Something like:

select t.*, a=1 as "a=1", b=2 as "b=2", c=3 as "c=3"
from table t
where a=1 or b=2 or c=3;