Mysql (Ver 14.14/Redhat): is a new column with default values efficient on column creation? I want to add a new column to my production database. I would like to add a default value in order to prevent all the if not null and ... business in all my queries (both new and old).

What order of add column-time penalty will I pay? I would ASSUME and HOPE that MySql would be smart enough to not actually fill in the default values for all the old rows, and instead treat the NULL value as the default (in other words, internalize the "if not null..." into the query automatically). ... and thus there should be NO penalty over and above the normal column add.

To be explicit, here are the two different MySQL alter table commands: in 5.1, is there any difference in the time efficiency of the two commands?

alter table XYZ add column newcol int ;

and

alter table XYZ add column newcol int default 0;

I assume that the answer would be version-specific. Am doing this in AWS EC2-land. Here's the version string:

mysql  Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1

You're using mysql 5.1,  This means the only engines you'll have available are MyISAM or innodb. in both cases adding a new column, regardless of nullability will require a complete, blocking, table rebuild during alter table.

You maybe be able to use the pt-online-schema change tool that does some tricks to rebuild the table in a less blocking manner but may not work out if you have complicated composite primary keys.

If you have a more recent version (5.6.22 Percona Build) you could use a tokudb storage engine that allows you to to "instantly" add columns.  The way it's indexing works it propagates changes down as they're accessed.


How to Retrieve value from a User-Defined Table Type (UDTT) Goal:
I want to reach and display the value of @data inside of [wwtest].

Problem:
The UDTT is from the database [test] and I receive an error message:

  The type name 'test.dbo.tvf_id' contains more than the maximum number of prefixes. The maximum is 1.

I don't know how to retrieve the data from the database [test] in a query currently in the database [wwtest]?

create table datatable (id int,
                        name varchar(100),
                        email varchar(10),
                        phone varchar(10),
                        cellphone varchar(10),
                        none varchar(10)
                       );

insert into datatable values
  (1, 'productname', 'A', 'A', 'A', 'A'),
  (2, 'cost', '20', 'A', 'A', 'A'),
  (3, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (4, 'productname', 'A', 'A', 'A', 'A'),
  (5, 'cost', '20', 'A', 'A', 'A'),
  (6, 'active', 'Y', 'A', 'A', 'A');

insert into datatable values
  (7, 'productname', 'A', 'A', 'A', 'A'),
  (8, 'cost', '20', 'A', 'A', 'A'),
  (9, 'active', 'Y', 'A', 'A', 'A');

CREATE TYPE [tvf_id] AS TABLE
(
    [id] [int] NULL
    ,[OrdCol] [INT] NOT NULL
)
GO

DECLARE @data tvf_id INSERT INTO @data([id],[OrdCol])
VALUES (8,1), (2,2), (4,3), (1,4), (3,5);

You need to create the type inside the database where you will declare it - data types can not be reached across databases or servers. This is no different from creating a local variable using an alias type - it has to exist in the context of the current database (or your code has to execute in the context of the database where the type exists, e.g.

EXEC dbname.sys.sp_executesql N'DECLARE @t dbo.type;
  INSERT @t(cols) SELECT whatever...;
  -- do other stuff but it has to be in this dynamic SQL block';


Multiple SQL Server instance install question for AlwaysOn I have a 2 node WSFC cluster with SQL Server 2014 installed on each for AlwaysOn.

This is working fine. My question is can I install another instance of SQL Server 2014 on these nodes for another AlwaysOn configuration.

I have a requirement that Skype for Business (SFB) has to be on its own instance to be supported and can't be installed on the existing AlwaysOn already up and running for some other DBs.

Unless you can point to the direct requirement you are referring to, I do not see any requirement in Skype for Business (SFB?) that states it has to be on a dedicated instance.

I think what you may be referring to is the database needs to be in a dedicated Availability Group. If that is the case you can create another AG on the same instance and cluster to support your database for SFB. A given SQL Server instance can support multiple AGs and each one can have their own listener.


openrowset to connect to SQL server with a password containing single quotes Can someone help me if there is ever a way to query an Azure SQL database using openrowset with an SQL authenticated account having "single quotes" in it's password string. My statement looks like the below

(I'm querying the Azure database from on-premises)

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj'C(D=JJ&'XE6vx};','select * from table1 with (nolock)')a

I tried the below solutions

solution 1 - Dynamic SQL

declare @string varchar(max)
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'''+'C(D=JJ&'''+'XE6vx}";'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)

Solution 2 - Passing the password in as a parameter

declare @string varchar(max)
declare @pwd varchar(max)
set @pwd = 'Fj'''+'C(D=JJ&'''+'XE6vx}'
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'

exec (@string)

They all result in the very same error (points to the password)

  Msg 102, Level 15, State 1, Line 16 Incorrect syntax near 'C'.

using a SQL authenticated account without any "single quote" in it's password works absolutely fine.

I could easily change the password to fix this issue, But still would like to know if there is ever a solution to this.

This is definitely one of those occasions when you should print out @string and see what it is actually set to...

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd="Fj'C(D=JJ&'XE6vx}";','select count(*) from dbo.table1 with (nolock)')a

You can see in the printed out results above (taken from Solution 1, although 2 is identical) that you are not escaping the single quotes in your connection string and so are ending the string before the 'C' and starting it again before the '&'.

You need an additional double set of quotation marks before the 'C' and after the '&'.

declare @string varchar(max);
declare @pwd varchar(max);
set @pwd = 'Fj'''+'''C(D=JJ&'''''+'XE6vx}';
set @string=
'SELECT a.*
FROM OPENROWSET(''SQLNCLI11'', ''SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd='+@pwd+';'',''select count(*) from dbo.table1 with (nolock)'')a'

This gives the following string, which should now have the required escaping of the single quotes and should hopefully work.

SELECT a.*
FROM OPENROWSET('SQLNCLI11', 'SERVER=sqlserver.database.windows.net,1433;DATABASE=testDB;Uid=admin;Pwd=Fj''C(D=JJ&''XE6vx};','select count(*) from dbo.table1 with (nolock)')a


Cursor-SQL Help- Need another Column for Dynamic SQL My company decided to change the value "Unknown" to (-1) in ALL of our lookup tables (tables that end with 'LU'). I am trying to write a script to automatically add a value for VALUES(-1, 'Unknown')

BEGIN TRAN

DECLARE @tablename nvarchar(250)
DECLARE @idcolumn nvarchar(250)
DECLARE @command nvarchar(500)

DECLARE LU CURSOR LOCAL FAST_FORWARD for
select TABLE_NAME, COLUMN_NAME
      from INFORMATION_SCHEMA.COLUMNS
       where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
       and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
       order by TABLE_NAME

OPEN LU
WHILE 1=1
BEGIN
    FETCH NEXT FROM LU INTO @tablename, @idcolumn
    IF @@FETCH_STATUS<>0
    BEGIN
        BREAK
END

SET @command = N'
SET IDENTITY_INSERT '+ @tablename+ ' ON
INSERT into '+ @tablename+ ' ('+@idcolumn+')
VALUES(-1)
SET IDENTITY_INSERT ' +@tablename+ ' Off'
PRINT @command
END
CLOSE LU
DEALLOCATE LU

ROLLBACK tran

which outputs something like this:

SET IDENTITY_INSERT AccessiblityLU ON
INSERT into AccessiblityLU (AccessiblityID)
VALUES(-1)
SET IDENTITY_INSERT AccessiblityLU Off

I am a beginner and realize Cursors shouldn't be used when there are other options available. I tried adding another cursor to grab the second column from each table (the ordinal position of the columns i need is always 2 luckily).
I tried this with no luck:

BEGIN TRAN
--DECLARE VARIABLES
DECLARE @tablename nvarchar(250)
DECLARE @idcolumn nvarchar(250)
DECLARE @command nvarchar(500)
DECLARE @secondcol nvarchar(100)

--FIRST CURSOR
DECLARE LU CURSOR LOCAL FAST_FORWARD for
select TABLE_NAME, COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA = 'dbo' AND TABLE_NAME LIKE '%LU'
and COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
order by TABLE_NAME

OPEN LU
while 1=1
BEGIN
FETCH NEXT from LU into @tablename, @idcolumn
if @@fetch_status<>0
begin
    break
end
DECLARE @secondcol nvarchar(100)
--SECOND CURSOR
DECLARE secondcolumn cursor local fast_forward for
select name
from sys.columns
where OBJECT_NAME(object_id) = @tablename AND column_id=2

OPEN secondcolumn
while 1=1
BEGIN
FETCH NEXT from secondcolumn into @seondcol
if @@fetch_status<>0
begin
    break
end

deallocate LU
deallocate secondcolumn

SET @command = N'
SET IDENTITY_INSERT '+ @tablename+ ' ON
INSERT into '+ @tablename+ ' ('+@idcolumn+', '+@secondcol+')
VALUES(-1, ''Unknown'')
SET IDENTITY_INSERT ' +@tablename+ ' Off'
PRINT @command
end

Any help would be greatly Appreciated!

It appears that you are using SQL Server, SQL Server uses T-SQL while Oracle uses PL/SQL.

This query should give you the results you need without using a cursor. char(10) and char(13) are used for line feed & carriage return, QUOTENAME() to get the square brackets around your objects.

SET NOCOUNT ON;

SELECT 'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ON;' + CHAR(10)+CHAR(13)
        +'INSERT into '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' ('+ QUOTENAME(I1.COLUMN_NAME)+', '+QUOTENAME(I2.COLUMN_NAME)+')' + CHAR(10)+CHAR(13)
        +'VALUES(-1, ''Unknown'');' + CHAR(10)+CHAR(13)
        +'SET IDENTITY_INSERT '+QUOTENAME(I1.TABLE_SCHEMA)+'.'+QUOTENAME(I1.TABLE_NAME) +' OFF;' + CHAR(10)+CHAR(13)
        +'GO'
FROM INFORMATION_SCHEMA.COLUMNS I1
LEFT JOIN INFORMATION_SCHEMA.COLUMNS I2
on object_id(I1.TABLE_NAME) = object_id(I2.TABLE_NAME)
AND SCHEMA_ID(I1.TABLE_SCHEMA) = SCHEMA_ID(I2.TABLE_SCHEMA) AND I2.ORDINAL_POSITION = 2
WHERE I1.TABLE_SCHEMA = 'dbo' AND I1.TABLE_NAME LIKE '%LU'
AND COLUMNPROPERTY(object_id(I1.TABLE_NAME), I1.COLUMN_NAME, 'IsIdentity') = 1
ORDER BY I1.TABLE_NAME;

In SSMS, you could select Results to text:

And then execute the script, giving you the formatted results:

Test tables created

CREATE TABLE [dbo].[PREFIXLU] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LUSUFFIX] ([id] int identity(1,1), [val] varchar(255));
CREATE TABLE [dbo].[LU] ([id] int identity(1,1), [val] varchar(255));

From these three tables, two tables, PREFIXLU and LU should be returned by the script.

I would really check and make sure that  ORDINAL_POSITION = 2 is 100% true for all the tables with LU as a suffix.

Result:

SET IDENTITY_INSERT [dbo].[LU] ON;

INSERT into [dbo].[LU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[LU] OFF;

GO
SET IDENTITY_INSERT [dbo].[PREFIXLU] ON;

INSERT into [dbo].[PREFIXLU] ([id], [val])

VALUES(-1, 'Unknown');

SET IDENTITY_INSERT [dbo].[PREFIXLU] OFF;

GO

  (1 row affected)

  (1 row affected)


How to grant the USAGE privilege on all types? How can I grant the USAGE privilege on ALL TYPES in a schema? For tables and functions we can use ALL TABLES IN SCHEMA schema_name, but this isn't supported for types:

GRANT { USAGE | ALL [ PRIVILEGES ] }
    ON TYPE type_name [, ...]
    TO role_specification [, ...] [ WITH GRANT OPTION ]

I know I can set a default privilege, but in this case I'd already installed an extension with many types before doing that.

We can do this with dynamic sql and psql, Sub out user1 and user2 with the name of the user. Feel free to delete either. You may also want only DOMAIN or USER-DEFINED TYPE adapting this should be pretty simple.

SELECT format(
  $$GRANT USAGE ON TYPE %s TO %s;$$,
  format('%I.%I.%I', object_catalog, object_schema, object_name ),
  grantUser
)
FROM information_schema.element_types
CROSS JOIN (VALUES ('user1'),('user2')) AS t(grantUser)
WHERE object_type IN ('USER-DEFINED TYPE', 'DOMAIN');

Then run

\gexec

You can also run this in a do block with PERFORM


How to speed up foreign key creation in MySQL for large tables? I am trying to set a foreign key constraint on a 5.7 InnoDB table with 30M+ rows.
It now already runs for over 1h on a quad core 64GB server. The processlist outputs the state copy to tmp table for the issued alter table command.
InnoDB_buffer_pool_size is set to 32G and has room. The system does not swap.
Why does the system create a tmp table and can this somehow be increased in performance?

A FOREIGN KEY:

Includes an index, which may help performance during JOINs, sometimes significantly.
Includes a constraint that guarantees the existence of a matching row in another table.

Some cases of adding an index require copying the table over.  Hence the "copy".  Apparently, you have such a situation.  For further discussion, please provide SHOW CREATE TABLE before the command, and the ALTER TABLE that was adding the FK.


pgAgent doesn't run on another database than "postgres" I run a postgreSQL 9.3.3 database and I want to do some automatic Tasks with pgAgent, but it doesn't run on another database than "postgres" db. What can I do to run pgAgent on another db ?

thanks !

The solution is that the maintenance DB's server (when you right click on server --> properties --> the 5th row) is postgres and it might be the other database where you want to run pgagent jobs.


Listing the Microsoft SQL Server configuration with the values set by mssql-conf? The utility mssql-conf in /opt/mssql/bin/mssql-conf has the setting list which tells you possible keys,

$ sudo ./mssql-conf list
control.alternatewritethrough              Enable optimized write through flush for O_DSYNC requests
control.hestacksize                        Host extension stack size in KB
control.writethrough                       Use O_DSYNC for file flag write through requests
coredump.captureminiandfull                Capture both mini and full core dumps
coredump.coredumptype                      Core dump type to capture: mini, miniplus, filtered, full
filelocation.defaultbackupdir              Default directory for backup files
filelocation.defaultdatadir                Default directory for data files
filelocation.defaultdumpdir                Default directory for crash dump files
filelocation.defaultlogdir                 Default directory for log files
filelocation.errorlogfile                  Error log file location
filelocation.masterdatafile                Master database data file location
filelocation.masterlogfile                 Master database log file location
hadr.hadrenabled                           Allow SQL Server to use availability groups for high availability and disaster recovery
language.lcid                              Locale identifier for SQL Server to use (e.g. 1033 for US - English)
memory.memorylimitmb                       SQL Server memory limit (megabytes)
network.forceencryption                    Force encryption of incoming client connections
network.forcesecureldap                    Force using LDAPS to contact domain controller
network.ipaddress                          IP address for incoming connections
network.kerberoskeytabfile                 Kerberos keytab file location
network.tcpport                            TCP port for incoming connections
network.tlscert                            Path to certificate file for encrypting incoming client connections
network.tlsciphers                         TLS ciphers allowed for encrypted incoming client connections
network.tlskey                             Path to private key file for encrypting incoming client connections
network.tlsprotocols                       TLS protocol versions allowed for encrypted incoming client connections
sqlagent.databasemailprofile               SQL Agent Database Mail profile name
sqlagent.enabled                           Enable or disable SQLAgent
sqlagent.errorlogfile                      SQL Agent log file path
sqlagent.errorlogginglevel                 SQL Agent logging level bitmask - 1=Errors, 2=Warnings, 4=Info
telemetry.customerfeedback                 Telemetry status
telemetry.userrequestedlocalauditdirectory Directory for telemetry local audit cache

How do I get the values?

There is no command line option to view what the values are actually set too, for now you'll have to check the file yourself

cat /var/opt/mssql/mssql.conf

However, it does not show the defaults. Stock, it has two options. One for sqlagent.enabled and one for EULA.accepteula. If you change one of those values as in the case of disabling CEIP,

$ sudo /opt/mssql/bin/mssql-conf set telemetry.customerfeedback false

Your conf file will look like this,

$ cat /var/opt/mssql/mssql.conf
[EULA]
accepteula = Y

[sqlagent]
enabled = false

[telemetry]
customerfeedback = false


SQL Server : keep track of a running calculation monthly I have the following in Excel which I need to calculate in SQL Server:

The columns that needs to be calculated is change and new_threshold:

The formula for change is:

While the formula for new_threshold is:

I have tried to use the lead and lag methods SQL Server offers, but I can't seem to keep track of the calculation.

Here is one thing I've tried:

SELECT
    year_month,
    round(original_threshold, 0) original_threshold,
    round(case when original_threshold > isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end, 0) change,
    manualChange,
    round(original_threshold + case when original_threshold > isnull(lead(original_threshold) over (order by [program], [year_month] desc), original_threshold)
        then isnull(lead(original_threshold) over (order by [year_month] desc), original_threshold)  - original_threshold
        else 0 end + manualChange, 0) new_threshold
FROM
    [Test].[dbo].[test]

which yields:

Edit:

Here is the insert statement if you want to test it

insert into [Test].[dbo].[test]
(
     [year_month]
    ,[original_threshold]
    ,[manualChange]
)

values
('2017_09', 401,    0),
('2017_08', 396,    0),
('2017_07', 397,   -5),
('2017_06', 389,   -7),
('2017_05', 393,    0),
('2017_04', 390,    0),
('2017_03', 412,    0),
('2017_02', 439,    0),
('2017_01', 458,    0)

Based on the values from this insert, I want to be able to calculate the change and new_threshold like in the Excel above.

Here is my idea:
-I used recursive approach,  - for this , added a Row_number, used this to join previous records

;WITH cte_Source AS
(SELECT
    year_month
    ,original_threshold
    ,manualChange
    ,CONVERT(int,LEFT(year_month,4)) As mYear
    ,CONVERT(int,RIGHT(year_month,2)) As mMonth
    ,ROW_NUMBER()OVER(ORDER BY CONVERT(int,LEFT(year_month,4)),CONVERT(int,RIGHT(year_month,2))) AS RN
FROM
    #Test AS T
)
,cteR AS
(
    SELECT TOP(1)
        T.year_month
        ,T.original_threshold
        ,T.manualChange
        ,0 AS change
        ,T.original_threshold  +  0 + T.manualChange AS  new_threshold
        ,T.RN
    FROM cte_Source AS T
    --WHERE T.year_month ='2017_01'
    ORDER BY T.mYear * 100 + T.mMonth ASC

    UNION ALL

    SELECT
        S.year_month
        ,S.original_threshold
        ,S.manualChange
        ,CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END AS  change
        ,S.original_threshold
            + CASE WHEN  S.original_threshold > R.new_threshold THEN R.new_threshold - S.original_threshold ELSE 0 END
            + S.manualChange AS  new_threshold
        ,S.rn AS rn
        FROM cte_Source AS S
            INNER JOIN cteR AS R
            ON r.RN + 1 = S.RN

)

select
    [year_month]
    ,[original_threshold]
    ,[change]
    ,[manualChange]
    ,[new_threshold]
from cteR

output for it:

year_month original_threshold change      manualChange new_threshold
2017_01    458                0           0            458
2017_02    439                0           0            439
2017_03    412                0           0            412
2017_04    390                0           0            390
2017_05    393                -3          0            390
2017_06    389                0           -7           382
2017_07    397                -15         -5           377
2017_08    396                -19         0            377
2017_09    401                -24         0            377


What happen when changing column type for a table with clustered index? I have a very large table (200M+ row), and it is clustered indexed. I would like to change one of the foreign key column from a bigint to smallint.

My questions are:

Does SQL need to rebuild the clustered index?
What if this table is a heap and not a clustered index?

1: If the column you're changing is part of the clustered index definition, then yes. If not, no. Any non-clustered indexes involving that column will have to be rebuilt when you change the column type, also.

2: A heap just means no clustered index, so nothing to rebuild. Same as answer #1 for non-clustered indexes.


PostgreSQL partitioning query performance inexplicably bad I find this a bit mysterious. The cost of getting the maximum value out of just the partition parent table (which is empty) is very cheap:

evlampts=# explain select max(timestamp) from only mcdata;
                                              QUERY PLAN
-------------------------------------------------------------------------------------------------------
 Result  (cost=0.15..0.16 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..0.15 rows=1 width=8)
           ->  Index Scan Backward using mcdatatime_idx on mcdata  (cost=0.00..19.20 rows=130 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Likewise, the cost of getting the maximum value out of just the newest partition is very cheap.

evlampts=# explain select max(timestamp) from mcdata_2011_09;
                                                                QUERY PLAN
------------------------------------------------------------------------------------------------------------------------------------------
 Result  (cost=3.17..3.18 rows=1 width=0)
   InitPlan 1 (returns $0)
     ->  Limit  (cost=0.00..3.17 rows=1 width=8)
           ->  Index Scan Backward using mcdata_2011_09_timestamp_idx on mcdata_2011_09  (cost=0.00..560255403.07 rows=176936192 width=8)
                 Index Cond: ("timestamp" IS NOT NULL)
(5 rows)

Here's the weird thing. Getting the maximum from the whole partitioned table is very expensive. PostgreSQL doesn't seem to produce efficient subplans, even though there are only two tables here (the master and one partition) and both can be queried separately efficiently.

evlampts=# explain select max(timestamp) from mcdata;
                                          QUERY PLAN
-----------------------------------------------------------------------------------------------
 Aggregate  (cost=4663394.03..4663394.04 rows=1 width=8)
   ->  Append  (cost=0.00..4221053.22 rows=176936322 width=8)
         ->  Seq Scan on mcdata  (cost=0.00..11.30 rows=130 width=8)
         ->  Seq Scan on mcdata_2011_09 mcdata  (cost=0.00..4221041.92 rows=176936192 width=8)
(4 rows)

Is there a configuration tunable I've missed or is this a limitation I don't understand?

Thanks!

What version do you use? Version 9.1 has a major improvement for partitioning, check this blog from Greg Smith.

When using version 9.1, I get this result for this query

EXPLAIN ANALYZE
SELECT MAX(logdate) FROM logs;

:

Result  (cost=0.88..0.89 rows=1 width=0) (actual time=0.251..0.252 rows=1 loops=1)
  InitPlan 1 (returns $0)
    ->  Limit  (cost=0.78..0.88 rows=1 width=8) (actual time=0.243..0.243 rows=1 loops=1)
          ->  Merge Append  (cost=0.78..14533886.71 rows=147545737 width=8) (actual time=0.238..0.238 rows=1 loops=1)
                Sort Key: public.logs.logdate
                ->  Sort  (cost=0.01..0.02 rows=1 width=8) (actual time=0.013..0.013 rows=0 loops=1)
                      Sort Key: public.logs.logdate
                      Sort Method: quicksort  Memory: 25kB
                      ->  Seq Scan on logs  (cost=0.00..0.00 rows=1 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                            Filter: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_10_logdate on logs_2011_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.014..0.014 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_11_logdate on logs_2011_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_12_logdate on logs_2011_12 logs  (cost=0.00..6785740.95 rows=146709024 width=8) (actual time=0.055..0.055 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_4_logdate on logs_2011_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_5_logdate on logs_2011_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_6_logdate on logs_2011_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_7_logdate on logs_2011_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_8_logdate on logs_2011_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_9_logdate on logs_2011_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_1_logdate on logs_2012_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_2_logdate on logs_2012_2 logs  (cost=0.00..68278.63 rows=815824 width=8) (actual time=0.025..0.025 rows=1 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_3_logdate on logs_2012_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_4_logdate on logs_2012_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.005..0.005 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_1_logdate on logs_2010_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_2_logdate on logs_2010_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.006..0.006 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_3_logdate on logs_2010_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_4_logdate on logs_2010_4 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_5_logdate on logs_2010_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_6_logdate on logs_2010_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_7_logdate on logs_2010_7 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_8_logdate on logs_2010_8 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_9_logdate on logs_2010_9 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_10_logdate on logs_2010_10 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_11_logdate on logs_2010_11 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2010_12_logdate on logs_2010_12 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_1_logdate on logs_2011_1 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.003..0.003 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_2_logdate on logs_2011_2 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2011_3_logdate on logs_2011_3 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_5_logdate on logs_2012_5 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
                ->  Index Scan Backward using idx_logs_2012_6_logdate on logs_2012_6 logs  (cost=0.00..46.31 rows=746 width=8) (actual time=0.004..0.004 rows=0 loops=1)
                      Index Cond: (logdate IS NOT NULL)
Total runtime: 0.718 ms


How can I run a SQL job every 1 second? I have an SQL Job which processes minimum amount of rows at a given time. Currently its running every 10 seconds which is the minimum available in Job Scheduler. But because of that the table used processed by the Job gets filled with many records. So I need to run the job every 1 second. How can achieve this? please advice.

Create a job that is scheduled to start every minute. Have the job do something like:

WHILE 1=1
BEGIN
    EXEC dbo.SomeProcedure; /*  this would be the
        name of a stored procedure that does the
        actual work */
    WAITFOR DELAY '00:00:01.000';
END

Thanks to Max Vernon, who gave me this answer earlier.


Multi-level confirmation process My problem:

I'm designing a system, that will have multiple administrators with different administrator levels (level 1,2, ...). When a user logs in and creates something (eg. organisation), all of the administrators need to confirm what the user created. The administrator with administrator level 1 is the first one to confirm. If he accepts the users data he will pass the decision to administrator with administrator level 2. He will the again decide if the user data is correct or not. This process goes on until the administrator with the highest administrator level is reached.

My proposed solution:

Every administrator would have a column that indicates his administrator level. User data table would then have a confirmation status column and a column, indicating which administrator level was the last one to set the confirmation status.

What do you think?

Consider Confirmationas a separate  entity which stores info on what admin confirmed that user action and when it happened. Status of user (action?) is derived as the confirmation with the max level of admin.
One option I would add is a requried confiration level attribute of user, ie. when admin with required level confirms user, the confirmation process is considered finished successfully. Probably in some future moment more levels will be added to the system and new levels probably should not render old users unconfirmed.


Pivot with 2+ columns (using CROSSTAB?) I have a table deflator that is defined as:

               Table "deflator"
    Column   |       Type        | Modifiers
-------------+-------------------+-----------
country_code | smallint          | not null
country_name | character varying | not null
year         | smallint          | not null
deflator     | numeric           |
source       | character varying |

Sample output from this table looks like:

country_code | country_name  | year | deflator | source
-------------+---------------+------+----------+----------
           1 | country_1     | 2016 |       12 | source_1
           1 | country_1     | 2015 |       11 | source_2
           1 | country_1     | 2014 |       10 | source_2
           2 | country_2     | 2016 |       15 | source_1
           2 | country_2     | 2015 |       14 | source_1
           2 | country_2     | 2014 |       13 | source_2
           3 | country_3     | 2016 |       18 | source_1
           3 | country_3     | 2015 |       17 | source_2
           3 | country_3     | 2014 |       16 | source_3
(9 rows)

I use the following query to pivot the table if I exclude the column source:

SELECT
    *
FROM CROSSTAB (
    'SELECT
        country_code
        , country_name
        , year
        , deflator
     FROM dimension.master_oecd_deflator
     ORDER BY 1;'
     , $$ VALUES ('2014'::TEXT), ('2015'::TEXT), ('2016'::TEXT) $$
) AS "ct" (
    "country_code" SMALLINT
    , "country_name" TEXT
    , "2014" NUMERIC
    , "2015" NUMERIC
    , "2016" NUMERIC
);

The above query gives me:

country_code |   country_name    | 2016 | 2015 | 2014 |
-------------+-------------------+------+--- --+------+
           1 | country_1         | 12   | 11   | 10   |
           2 | country_2         | 15   | 14   | 13   |
           3 | country_3         | 18   | 17   | 16   |

But because the source of the deflator varies from year to year for each country I want to include the source column in the pivot for my desired output to look like:

country_code |   country_name    | 2016 | 2016_source | 2015 | 2015_source | 2014 | 2014_source
-------------+-------------------+------+-------------+------+-------------+------+------------
           1 | country_1         | 12   | source_1    | 11   | source_2    | 10   | source_2
           2 | country_2         | 15   | source_1    | 14   | source_1    | 13   | source_2
           3 | country_3         | 18   | source_1    | 17   | source_2    | 16   | source_3

How do I modify this query to give me the desired output? (With the source for each year listed next to the year). Is this even possible?

Saddam has a smart solution, but it carries some weaknesses. Imagine a source named 'Fresno, CA' (with comma in the string). split_part() would be fooled by the separator character in the string ...

To avoid such corner case problems and preserve original data types, use a (well-defined!) row type instead. You can create a composite type permanently with CREATE TYPE or register a temporary one with CREATE TEMP TABLE:

CREATE TEMP TABLE defso (def numeric, so varchar);  -- once per session!

SELECT country_code
     , country_name
     , (d14).def AS deflator_2014  -- note the parentheses!
     , (d14).so  AS source_2014
     , (d15).def AS deflator_2015
     , (d15).so  AS source_2015
     , (d16).def AS deflator_2016
     , (d16).so  AS source_2016
FROM   crosstab (
    'SELECT country_code, country_name, year, (deflator, source)::defso
     FROM   deflator
     ORDER  BY 1'
  , 'SELECT generate_series(2014, 2016)::int2'
   ) AS ct (country_code int2
          , country_name text
          , d14 defso
          , d15 defso
          , d16 defso
   );

I also removed the unnecessary CTE and simplified a bit.

While dealing with only a hand full of years, you can do without crosstab() and use self-joins:

SELECT country_code, country_name
     , d14.deflator AS deflator_2014
     , d14.source   AS source_2014
     , d15.deflator AS deflator_2015
     , d15.source   AS source_2015
     , d16.deflator AS deflator_2016
     , d16.source   AS source_2016
FROM        (SELECT * FROM deflator WHERE year = int2 '2014') d14
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2015') d15 USING (country_code, country_name)
FULL   JOIN (SELECT * FROM deflator WHERE year = int2 '2016') d16 USING (country_code, country_name)
ORDER  BY country_code;

Using FULL [OUTER] JOIN since we can't assume a row for every combination of (country_code, year). This way we get the same result as with the crosstab query above.

Including country_name in the join condition seems redundant, but if we don't, we have to use COALESCE(d14.country_name, d15.country_name, d16.country_name) AS country_name to defend against missing rows. This functionally dependent value shouldn't be in the table to begin with. Should be in a country table in a properly normalized schema.


How to view xEvent event_file data I have a small xEvent that is sending the results as .xel to an event_file in G:\XEvents and I want to explore the data with SSMS.  I have been looking for a solution and found several posts about reading the XML created by ring buffers.  But I can't get it to work on my event_file.

I read Jes Schultz Borland's post How to Query Extended Events Target XML but It is not working for me.  I have not done anything significant with XML, and when I open the event_file with word, it does not look like XML.

I can display it by using SSMS; > Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data, but I can't really query it.

You are working under a false assumption, event_files do not store the data as XML, that is what ring_buffer does.

event_file: Data from an event_file target is displayed very well, with rich features available.
ring_buffer: Data from a ring-buffer target is displayed as raw XML.

There are a few options but for me the simplest is to put the data in a SQL database.
Once you have the data displayed in SSMS: Instance > Management > Extended Events > Sessions > [MyEvent] > Package > View Target data
Use the new toolbar menu item 'extended events' and export to a table in SQL database.
The database needs to exist when you export but the table does not.  I used a database named xEvent on a test/sandbox instance.
From the Toolbar: Extended Events > Export to > Table.. > (Connect to 'test/sandbox instance' > Existing database > (Type in any name you like)
Use standard SQL to query.

Reference Advanced Viewing of Target Data from Extended Events in SQL Serve


Parallel PostgreSQL Queries with R I'm running a large query that for various reasons, I've broken into a series of  smaller queries. The example below is just to show how the query is broken up by id; the actual query is much more complex:

UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 0  AND id < 10;
 UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 10 AND id < 20;;
  UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 20  AND id < 30;

etc...

I've written a function in R that generates the queries sequentially and sends them to the database using the R library RPostgreSQL:

library(RPostgreSQL)

num <- seq(0, 100, 10)

 query.func <- function(num){

      con <- dbConnect(PostgreSQL(), dbname = "name", post = 5432, user = "user_name", password = "password")

      num2 <- num + 10

       q1 <- paste('UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >=', num, 'AND id <', num2, sep = "")

         dbSendQuery(con, q1)

           dbDisconnect(con)
}

lapply(num, query.func)

Rather than having this long sequence of queries run one after another, I wanted to try and take advantage of my server's multiple CPU's and run them in parallel. Using the R library parallel I've created a cluster and sent multiple queries to the cluster simultaneously:

no_cores <- detectCores() - 1

 cl <- makeCluster(no_cores)

  clusterExport(cl, "query.func")
  clusterExport(cl, "num")

  clusterEvalQ(cl, library(RPostgreSQL))

    parLapply(cl, num, query.func)

stopCluster()

Where query.func is defined as above.

When I run this R script at the terminal I receive no errors and if I check pg_stat_activity I see a list of active queries, each incrementally operating on a separate block of data. However, when I use top to check the resource management on my system, I still only see one postgres process. When I look at the CPU usage, I only see one CPU active at a time.

Are these queries really running simultaneously on separate CPU's? My understanding was that, prior for 9.6, a single postgres query could not be split across multiple cores but each connection could utilize a separate core (related question). Does the process I've outlined above open multiple connections and send a query to the database via each connection?

PostgreSQL 9.3/ Ubuntu 14.04 / R 3.3.2

UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 0  AND id < 10;
 UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 10 AND id < 20;;
  UPDATE target_table SET col1_target = col1_source FROM source_table WHERE id >= 20  AND id < 30;

That's insane especially if the query is not CPU bound. Just run one query with one update.

UPDATE targate_table set col1_target = col1_source
FROM source_table
WHERE (id BETWEEN 0 AND 10)
  OR (id BETWEEN 10 AND 20)
  OR (id BETWEEN 20 AND 30);

PostgreSQL doesn't lock the table for row updates. It only locks the rows. There is no reason to break this up.


Select complete next row based on the current row Hi i have a complicated condition. I have a table lets say 'Test'

 ID    Partner   Type       Amount
143854  CSTC    Purchase    -0.81
144029  CSTC    Purchase    -0.69
144030  CSTC    Purchase    -1.33
144031  CSTC    Purchase    -0.47
144032  CSTC    Purchase    -1.8
149527  CSTC    div         1574.48
149528  CSTC    Purchase    -1574.48
149531  CSTC    div         867.53
149532  CSTC    Purchase    -867.53
149539  CSTC    div         123
149540  CSTC    div         76.2
149541  CSTC    Purchase    -76.2
149550  CSTC    div          8.77
149551  CSTC    Purchase    -8.77
149554  CSTC    div         700.45
149555  CSTC    Purchase    -700.45

I want to remove each occurrence of rows having Type = 'div' and the next row should have Type = 'Purchase'

i.e if type = 'div' and next row type = 'Purchase' delete both else i want to perform some update action on the row having type = 'div'.

I have tried Lead i can get next rows Type col value but it is not helping.
Other thing i tried was

;WITH TestWithRowNumber AS
(
    SELECT *,
    ROW_NUMBER() OVER (ORDER BY ID ASC) AS RowNumber
    FROM Test where Partner = 'CSTC' and   (Type = 'div' or Type = 'Purchase')
)

      Select FirstRow.* From TestWithRownumber FirstRow
  left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and     firstrow.type = 'div' where FirstRow.ID >= (select TOP 1 FirstRow.ID From TestWithRownumber FirstRow
   left outer join TestWithRownumber secondrow on firstrow.RowNumber = secondrow.RowNumber -1 and secondrow.type = 'Purchase' and firstrow.type = 'div' Where FirstRow.[Transaction] = 'div')

Issue with this query is in the output i am getting two consecutive div i.e an extra div whose next row is not with Type = 'Purchase'.

Trying to guess your requirements here, You want to "remove" rows that are part of div-Purchase pairs.

If by remove you mean don't select them, i.e. filter them out in a where clause, then something like this might work:

with TestCTE as
(
    select ID, [Partner], [Type], Amount,
        LEAD([Type], 1) OVER (ORDER BY ID) NextType,
        LAG([Type], 1) OVER (ORDER BY ID) PreviousType
    from test
)
select ID, [Partner], [Type], Amount
from TestCTE
where NOT ( [Type] = 'div' AND [NextType] = 'Purchase' )
  and NOT ( [Type] = 'Purchase' AND [PreviousType] = 'div' )

But if by remove, you mean delete, then that's simply:

with TestCTE as
(
    select ID, [Partner], [Type], Amount,
        LEAD([Type], 1) OVER (ORDER BY ID) NextType,
        LAG([Type], 1) OVER (ORDER BY ID) PreviousType
    from test
)
DELETE
from TestCTE
where ( [Type] = 'div' AND [NextType] = 'Purchase' )
  or ( [Type] = 'Purchase' AND [PreviousType] = 'div' )

That's two examples that both use a select and a delete using a CTE and similar where clauses. If you want to update, you can use the same strategy.


Bringing SQL Server filegroups online We've experienced an sql injection attack which corrupted our main db.

The main db had two partitioned tables into filegroups.

The corrupt database entered into Suspect mode, and it's been impossible for us to use.

I was able to restore a backup of the db with partial (no filegroups, since we didn't have backup of those.. I know bad thing).

I was now able to remove the db in suspect mode so we are left with the restored db and the file groups that were linked to the corrupt db.

Now I'm trying to figure out how to bring those file groups back online.

I've tried removing them so I can then add them back on, but I can't remove them since I get

Msg 5056, Level 16, State 2, Line 1
Cannot add, remove, or modify a file in filegroup 'FG_Audit' because the filegroup is not online.

Is there any work around? I've found posts where they talk about restoring from backup but we don't have one of the filegroups.

Have we lost that data? How do we remove the filegroups? Backups now stopped working because of this.

UPDATE

If I run this

SELECT
 f.name file_group,
 d.name file_name,
 d.state_desc file_state
FROM sys.filegroups f
JOIN sys.database_files d
 ON f.data_space_id = d.data_space_id;
GO

All the file groups that the DB is complaining about being off line they say "RECOVERY_PENDING"

John Huang (MVP) outlines an approach in his blog post:

http://www.sqlnotes.info/2013/05/07/attach-database-with-missing-ndf-file/

The basic approach is outlined by John Huang as:

Create a empty database with the same file layout, including names, filegroups, location, etc, everything but data
Take the missing files offline
Take the database offline
Use the data files you have saved to overwrite the empty data files
Bring the database online

This allows you the chance to cobble things back together, but it can still have issues.

So, please, make sure you maintain good and complete backups.


In SQL*Plus, how do I run @https://here.is.url/script.sql behind a proxy? In SQL*Plus, I try to run a script that is stored at a certain URL.

From where I am currently, I can http(s) connect with the internet only via a proxy.

So, if in SQL*Plus, I try

@https://here.is.url/script.sql

SQL*Plus won't find the script (as SQL*Plus doesn't seem to know about the proxy).

Is there a way to indicate what proxy to use and how to authenticate with that proxy?

Set the environment variable HTTP_PROXY.

HTTP_PROXY=http://username:password@proxyhost


Timestamp-based index I have a big database (200GB+) that contains some log info. And I want to speed up SELECT queries and stored procedures. I have a table with a GeneratedOnUtc datetime column, and have a non-clustered index on it.

I'm thinking to change it to a clustered index.

Reasons for:

Big amount of data (~40 millions rows)
Column is used in multiple Where clauses (between, >, <)
Column is used in ROW_NUMBER() OVER (ORDER BY d.GeneratedOnUtc asc) AS Row queries

Reason against:

Large amount of inserts (~60k per day) may lead to frequent B-tree rebuilds.

Changing your table from a heap to having a clustered index should significantly improve your performance on both queries and perhaps even on inserts. Generally speaking, your clustered index should be narrow, unique, and ever increasing. Using a datetime that you can't guarantee to be unique is not ideal because it's 8 bytes and, since it isn't unique, sql will add a four byte uniquifier to non unique rows. You may be better off using an identity column with an int as your clustered index and, since that's what the "row_number" queries are really after anyway (an ever increasing unique number) that might be a great way to go especially if you have a number of nonclustered indexes already (because the clustered is used as a row pointer by the nonclustered so it adds size to them). The 4 billion available int values leave you decades of growth.

I suggest you make a testing copy of your database and then test it with a clustered index on your datetime value and contrast that with a test using a clustered index on a new identity column (and a nonclustered index on your datetime). See which one does better with your query load. Both scenarios will outperform a heap.


OleDBConnection.Close() very slow on server I am working on a few applications which connect to an MS Access database backend (.mdb) to read/insert/update records.

Everything is working fine, but I noticed that my db operations were quite slow. The backend is accessed by other users, but I still get the issue when querying a copy of the access file which no one else connects to.

I managed to narrow this down so that I can now see the offending code is the line

 connection.Close()

Called on an open OleDBConnection which has just executed some query, e.g:

var con = new OleDbConnection(connectionString);
con.Open();
var query = "SELECT * FROM subGRCReceived WHERE GRVNo=@grv";
var args = new DynamicParameters();
args.Add("@grv", grvNumber);
// Using Dapper
var pallets = (List<Pallet>)con.Query<Pallet>(query, args);
con.Close(); // This is often taking between 7-10 seconds

I can confirm that this is occurring when using using/con.Close()/con.Dispose(), and using or not using Dapper makes no difference.

I did notice that this only seems to happen with web based prjojects (ASP MVC or WCF soap service)  and not Console applications. The issue seems to be intermittent, but occurs frequently enough for it to be a pain for the user (especially when navigating to a page uses 2-3 db queries, as this can take as long as 20 seconds to load).

The problem does not lie with the code itself, as I am able to host the same application on my laptop on the same network as the server and the speed is perfect (~200ms per request). See the specs of the 2 machines below:

Laptop Details:

Processor : Intel Core i7-6700HQ CPU @ 2.60GHz
RAM       : 16.0 GB
OS        : Windows 10 x64

Server Details

Processor : Intel Xeon CPU E5-2603 v4 @ 1.70GHz (2 processors)
RAM       : 32.0 GB
OS        : Windows Server 2012 x64

Setup

32 bit MS Access 2016
2016 Microsoft ACE OLE Engine
64 it OS

What I have tried

Moved the database to same folder as application
Disabled antivirus (ESET)
Increased the MaxBufferSize key value from 0 in Access Connectivity Engine in the registry (does this need a restart?)
Measure the time it takes to run GC.Collect() before calling Close() to ensure it is not the garbage collector

Workarounds

Threading

I tried calling Close() from a new thread, which seemed to work after 1 request, but if I try accessing the application again I am getting unhandled win32 errors on the server (Even though I wrapped my thread and connection.Close() calls in try/catch. I suspect this might be failing because the thread might take 7 seconds to close the connection, but the IIS worker process gets terminated before that, so there may be some missing resources that Close() needs. It might be nice if I could get this working, but I understand that this i bad practise in MVC, and also does not actually solve the issue.

Persistent Connection

I could also just have 1 OleDBConnection and keep it open throughout the session. I did this with the WCF service (1 connection per request) and it works find, however I get the feeling that it wont work quite as well with ASP MVC, and after doing a bit of research it looks like this is not a good idea.

I have been struggling with this for a week now and its driving me crazy, does anyone have any advice at all?

In my experience,

One of the access database exceeded 20000 records and the size was around 150 MB. It started to slow down and performance plummeted. So moved to SQL.

You may consider using SQL compact edition, or sql lite


How do you monitor PostgreSQL queries? I have an application which uses PostgreSQL. I want to monitor all the queries that the application sends to the database. Is there a way to monitor the queries sent?

You can point pgAdmin4 at your PostgreSQL server, it has a dashboard you can use. You can then drill down in a session to see the query.
If thats not what you are looking for then I guess a logging solution would be what you need like pg_stat_statements.


An alternative for sp_unbinddefault I have a column with default value bound to UW_ZeroDefault and I am trying get rid of it.

As I understand it, this default has been created by using CREATE DEFAULT then the column has been bound to this default by using sp_bindefault.

Usually to remove this default, you would use sp_unbinddefault, but since there is a bug in SQL Server 2008 (see KB article), I am getting an error:

  Invalid object name ' '.

I cannot make any changes to the SQL Server installation, so I am looking for some workarround how to drop this thing.

Summary of the discusion below:

SOLUTION 1:

As suggested by @gbn, if you can get your database back to the SQL Server 2000, do it and use sp_unbindefault there. That should work.

But if you can't move it (that's my case)...

SOLUTION 2:

Make a copy of the table(s) and use CONSTRAINT to set a default instead of binding default. This includes dropping all kind of constraints, FKs, etc. and recreating them afterwards. It is a bad solution, but I couldn't find a better.

As per BOL rather than KB, use ALTER TABLE

ALTER TABLE Mytable DROP <defaultname>;

The name of the default could be user supplied or system generated. To find it and generate the SQL needed:

SELECT
    OBJECT_NAME(c.object_id), dc.name,
    'ALTER TABLE ' + QUOTENAME(OBJECT_NAME(c.object_id)) + ' DROP ' + QUOTENAME(dc.name);
FROM
    sys.columns c
    JOIN
    sys.default_constrainst dc ON c.columnid = dc.parent_column_id AND c.object_id = dc.parent_object_id
WHERE
    c.name = 'UW_ZeroDefault';


Why is a trigger preventing update? I have a script that runs weekly. It updates multiple tables in my database. It turns out one statement attempts to update 150,000 rows on a table that contains 250,000 rows. That failed this week after I'd introduced a trigger on that table.
The trigger is auditing one column on that table - if the value in that column changes, a record is inserted into a table with identical name but in the 'audit' schema.
ALTER trigger [dbo].[tau_myTableName] on [dbo].[myTableName]
    after update
    as

    if exists (
        select 1 from inserted i join deleted d on i.primaryKeyId = d.primaryKeyId
        where   isNull(i.someColumn,-1) <> isNull(d.someColumn,-1)
        )

        insert into audit.myTableName (transaction_type, transaction_user, primaryKeyId, someColumn)
        select
        'U', SYSTEM_USER, primaryKeyId, someColumn
        from inserted;

The update statement that was hanging, is:
UPDATE  myTableName
    SET     unrelatedEmailAddressColumn= 'someone@someemail.com'
    WHERE   unrelatedEmailAddressColumn IS NOT NULL AND unrelatedEmailAddressColumn <> ''

Why should that update hang?
When the trigger is enabled, the script doesn't complete in 20 minutes. The statement before this one has persisted. When I disable the trigger the script completes in under a minute. The script runs on two databases - if I leave the trigger disabled on the second database then the first completes and the second hangs. It's a test environment with minimal users, the script is run immediately after a restore and sp_who2 doesn't show any unexpected connections.
The trigger should insert zero rows as it is auditing a column that is not updated by the statement. That said, I've discovered a second trigger exists after update on that table. The second trigger updates a column in any row being updated. This shouldn't conflict but I haven't explicitly set a trigger order, so I will either set an order or merge the triggers then test again. As an aside, I pulled the update statement out of the script file and run just that single update in SSMS with the same hanging effect.

I would recommend using this trigger template where possible.  change it to suit your needs, of course.
It uses an IF UPDATE(column) to short circuit if the DML doesn't reference the column the trigger cares about.  It also uses the EXCEPT clause for what I've found the fastest way to compare the INSERTED and DELETED tables.  Just add more columns to the lists to get more comparisons without needing to worry about NULL/not null values.
Just adding an IF UPDATE block would solve your issue immediately with the least change to the trigger logic.
As for why your statement hung; the INSERTED and DELETED triggers are heaps without indexes.  This makes joining them on the primary key an expensive operation, much less the comparison of the relevant column.  You can make it as fast as possible (as the below has done) but your best bet to avoid it seemingly hanging would be to batch your statements.  Do 5,000 rows at a time to avoid unnecessarily large strain on your tempdb.
A random additional note; we have gone a few steps further, we don't have audit triggers like this, but we use INSTEAD OF INSERT/DELETE to enforce the use of soft-delete in our application.  However, when we do need to hard delete records, we have another short circuit that checks context info for a special binary value, then just doesn't fire off for that value.  You could implement something like that as well.  Leave the trigger in place for the application, but special statements like huge table maintenance could just issue the proper context info and the trigger would ignore the statement.  You would then need to handle the logic for what the trigger was supposed to do on your own, but triggers are brute force.
ALTER TRIGGER trgMyTriggerName ON dbo.MyTableName
AFTER UPDATE
AS
BEGIN

    /** Inside triggers, UPDATE(column) checks to make sure that the update statement
        actually referenced the column.  NOTE that this does not mean the value actually
        changed, just that the update (or whatever DML) statement referenced the column.

        If you use an ORM like entity framework then the whole row gets updated all the time
        anyways, regardless of if the value changed.  still, this is a good short circuit to
        prevent unnecessary logic from being executed.
        **/
    IF UPDATE(MyAuditColumn)
    BEGIN

        ;WITH CTE_Changed AS
            (
            SELECT I.PrimaryKeyID, I.AuditColumn
            FROM INSERTED AS I
            EXCEPT
            SELECT D.PrimaryKeyID, D.AuditColumn
            FROM DELETED AS D
            )
        INSERT INTO audit.MyTableName
            (transaction_type, transaction_user, primaryKeyId, someColumn)
        SELECT 'U', SYSTEM_USER, C.PrimaryKeyID, C.AuditColumn
        FROM CTE_Changed AS C

    END

END


Index selection in MongoDB 3.0 for $or query I have a big $or query and MongoDB is not picking the index that I expect it to.

Query:

{"$or" : [
    {"del_at" : {"$gt" : Timestamp(1448895834, 2)}},
    {"rts" : {"$elemMatch" : {"k" : "app","v" : "38a91025-1109-4256-b08c-df559c0a74df","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}},
    {"rts" : {"$elemMatch" : {"k" : "appv","v" : "e1d1daa4-8fb0-4aef-a6a2-9d80005bb8de","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}},
    {"rts" : {"$elemMatch" : {"k" : "devg","v" : "ebfb77ea-3568-4725-84f6-8d5ee2582868","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}},
    {"rts" : {"$elemMatch" : {"k" : "dev","v" : "5275cb0e-85f1-4f88-970b-e76ee0c55576","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}},
    {"rts" : {"$elemMatch" : {"k" : "usrg","v" : "acab2d5a-2814-4e19-98a0-95b4e0748789","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}},
    {"rts" : {"$elemMatch" : {"k" : "usr","v" : "d3f2aef1-64d4-4d3f-a36a-cdfb0d496c94","a" : false,"del_at" : {"$gt" : Timestamp(1448895834, 2)}}}}
]}

Indexes:

1. {"del_at" : 1,"rts.k" : 1,"rts.v" : 1,"rts.a" : 1,"rts.del_at" : 1}
2. {"rts.a" : 1,"rts.k" : 1,"rts.v" : 1,"del_at" : 1,"up_at" : 1}

In MongoDB 2.6, it does not select an index, however both exist.

In MongoDB 3.0 and 3.2, it selects the second index, but I'd expect it to use the first. Does anyone know why?

Thanks for the help!

I asked this question on the Google group for Mongodb. See Dwight's explanation and work around.

https://groups.google.com/forum/?__hstc=21977188.535555756f91d668db0d32d8604f6233.1443211966594.1443211966594.1443211966594.1&__hssc=21977188.11.1443211966594&__hsfp=469890013#!topic/mongodb-user/iKtaqA5KrKY

Thanks!


SQLite - is it faster to use SELECT NOT EXISTS / EXISTS or IN / NOT IN in a WHERE clause? In SQLite's query planners is it generally faster to do this kind of thing?

SELECT foo.bar,foo.baz
FROM foo
WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha)

... or ...

SELECT foo.bar,foo.baz
FROM foo
WHERE haha NOT IN (SELECT haha FROM lol)

They are functionally equivalent but I'm wondering whether the query planner handles them differently. I know in larger databases like PostgreSQL there are IN / NOT IN optimizations that make the latter as fast or faster than the former.

Let's assume that lol.haha is declared as NOT NULL, so that both queries give the same result.

When there is no index on lol.haha, the EXPLAIN QUERY PLAN outputs look similar:

> SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
QUERY PLAN
|--SCAN TABLE foo
`--CORRELATED SCALAR SUBQUERY
   `--SCAN TABLE lol
> SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
QUERY PLAN
|--SCAN TABLE foo
`--LIST SUBQUERY
   `--SCAN TABLE lol

However, the correlated subquery scans the lol table once for every row in the foo table, while the NOT IN query puts all rows returned by the subquery into a temporary index, and then does all searches using that.
This can be seen in the output of the EXPLAIN command, if you really want to bother to decode it:

> EXPLAIN SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     19    0                    00  Start at 19
1     OpenRead       0     4     0     3              00  root=4 iDb=0; foo
2     Rewind         0     18    0                    00
3       Integer        0     2     0                    00  r[2]=0; Init EXISTS result
4       Integer        1     3     0                    00  r[3]=1; LIMIT counter
5       OpenRead       1     2     0     1              00  root=2 iDb=0; lol
6       Rewind         1     13    0                    00
7         Column         1     0     4                    00  r[4]=lol.haha
8         Column         0     2     5                    00  r[5]=foo.haha
9         Ne             5     12    4     (BINARY)       51  if r[4]!=r[5] goto 12
10        Integer        1     2     0                    00  r[2]=1
11        DecrJumpZero   3     13    0                    00  if (--r[3])==0 goto 13
12      Next           1     7     0                    01
13      If             2     17    1                    00
14      Column         0     0     7                    00  r[7]=foo.bar
15      Column         0     1     8                    00  r[8]=foo.baz
16      ResultRow      7     2     0                    00  output=r[7..8]
17    Next           0     3     0                    01
18    Halt           0     0     0                    00
19    Transaction    0     0     6     0              01  usesStmtJournal=0
20    Goto           0     1     0                    00
> EXPLAIN SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
addr  opcode         p1    p2    p3    p4             p5  comment
----  -------------  ----  ----  ----  -------------  --  -------------
0     Init           0     26    0                    00  Start at 26
1     OpenRead       0     4     0     3              00  root=4 iDb=0; foo
2     Rewind         0     25    0                    00
3       Noop           0     0     0                    00  begin IN expr
4       Once           0     12    0                    00
5       OpenEphemeral  3     1     0     k(1,B)         00  nColumn=1
6       OpenRead       1     2     0     1              00  root=2 iDb=0; lol
7       Rewind         1     12    0                    00
8         Column         1     0     1                    00  r[1]=lol.haha
9         MakeRecord     1     1     2     A              00  r[2]=mkrec(r[1])
10        IdxInsert      3     2     1     1              00  key=r[2]
11      Next           1     8     0                    01
12      Column         0     2     2                    00  r[2]=foo.haha
13      IsNull         2     16    0                    00  if r[2]==NULL goto 16
14      Affinity       2     1     0     A              00  affinity(r[2])
15      Found          3     20    2     1              00  key=r[2]
16      Rewind         3     21    0                    00
17      Column         3     0     3                    00  r[3]=
18      Ne             2     21    3     (BINARY)       00  if r[3]!=r[2] goto 21
19      Goto           0     24    0                    00  end IN expr
20      Goto           0     24    0                    00
21      Column         0     0     4                    00  r[4]=foo.bar
22      Column         0     1     5                    00  r[5]=foo.baz
23      ResultRow      4     2     0                    00  output=r[4..5]
24    Next           0     3     0                    01
25    Halt           0     0     0                    00
26    Transaction    0     0     6     0              01  usesStmtJournal=0
27    Goto           0     1     0                    00

With an index, both queries do an index search for every foo row, i.e., they execute in exactly the same way:

> create index i on lol(haha);
> SELECT foo.bar,foo.baz FROM foo WHERE NOT EXISTS (SELECT lol.haha FROM lol WHERE lol.haha = foo.haha);
QUERY PLAN
|--SCAN TABLE foo
`--CORRELATED SCALAR SUBQUERY
   `--SEARCH TABLE lol USING COVERING INDEX i (haha=?)
> SELECT foo.bar,foo.baz FROM foo WHERE haha NOT IN (SELECT haha FROM lol);
QUERY PLAN
|--SCAN TABLE foo
`--USING INDEX i FOR IN-OPERATOR


I need to import into mysql data from report exported by infobip I want to import this csv sample data:

��SendDateTime;ExternalMessageId;SMSCount;Account Name;Country;Country Prefix;Network;Destination Address;Sender;Price Per Message;Credits Per Message;Parent AccountId;Parent Credits Per Message;Client Metadata;Status;Reason;MessageText;DR Arrival Time
21.2.2016. 0:01:17;="126022022011749133";1;some-account;some coutry;27;somenetwork;="27123456789";="27123456789";90,0000;90,0000;;;="";Delivered;;Confirmation: some sms text value;21.2.2016. 0:01:19;

This is an example from a report exported by http://www.infobip.com/.
Why infobip calls this a csv I don't know.

I am having problems with the following 90,0000 currency values.

How can I convert this to a FLOAD / DECIMAL as part of the import.

In order to solve this I ran the following to pre-process file to remove
problem chars, ��, = as well as remove all null bytes.

tail -n+2 report.csv | tr -d '\000' | tr -d "=" > ./report.fix.csv

I created the following table in mysql database:

CREATE TABLE `2016_feb_msg` (
  `SendDateTime` datetime DEFAULT '2016-01-31 23:59:00',
  `ExternalMessageId` varchar(20) DEFAULT NULL,
  `SMSCount` int(11) DEFAULT NULL,
  `AccountName` varchar(65) DEFAULT NULL,
  `Country` varchar(65) DEFAULT 'South Africa',
  `CountryPrefix` varchar(45) DEFAULT NULL,
  `Network` varchar(60) DEFAULT NULL,
  `DestinationAddress` varchar(45) DEFAULT NULL,
  `Sender` varchar(45) DEFAULT NULL,
  `PricePerMessage` DECIMAL(10,4) DEFAULT '0',
  `CreditsPerMessage` DECIMAL(10,4) DEFAULT '0',
  `ParentAccountId` varchar(45) DEFAULT NULL,
  `ParentCreditsPerMessage` varchar(9) DEFAULT '0',
  `ClientMetadata` varchar(25) DEFAULT NULL,
  `Status` varchar(45) DEFAULT NULL,
  `Reason` varchar(45) DEFAULT NULL,
  `MessageText` varchar(900) DEFAULT NULL,
  `DRArrivalTime` datetime DEFAULT '2016-01-31 23:59:00',
  `idx` bigint(20) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`idx`)
) ENGINE=InnoDB AUTO_INCREMENT=1277493 DEFAULT CHARSET=utf16;

I used the following to load the data into the database:

LOAD DATA INFILE '/tmp/report.fix.csv'
INTO TABLE infobip_reports.2016_feb_msg
FIELDS TERMINATED BY ';'
OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
(@SendDateTime
,ExternalMessageId
,SMSCount
,AccountName
,Country
,CountryPrefix
,Network
,DestinationAddress
,Sender
,@PricePerMessage
,@CreditsPerMessage
,ParentAccountId
,ParentCreditsPerMessage
,ClientMetadata
,Status
,Reason
,MessageText
,@DRArrivalTime
,idx)
SET SendDateTime = STR_TO_DATE(@SendDateTime, '%e.%c.%Y. %k:%i:%S'),
DRArrivalTime = STR_TO_DATE(@DRArrivalTime, '%e.%c.%Y. %k:%i:%S'),
PricePerMessage = CONVERT(REPLACE(@PricePerMessage,",","."),DECIMAL(10,4)),
CreditsPerMessage = CONVERT(REPLACE(@CreditsPerMessage,",","."),DECIMAL(10,4)),
idx = null
; -- format this date-time variable


Changing a variable using SET GLOBAL doesn't impacts sessions variable? I am bit confused between setting the global and session parameters , I am trying to set wait_timeout

The default settings are

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change the session setting

SET wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                 28800 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

The below SQL will change it globally

SET GLOBAL wait_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Check the values , which is pretty clear as well

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                     10 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

Now , I am closing the MySQL thread and opening a new one , I find the below data

SELECT @@global.wait_timeout , @@session.wait_timeout;
+-----------------------+------------------------+
| @@global.wait_timeout | @@session.wait_timeout |
+-----------------------+------------------------+
|                    10 |                  28800 |
+-----------------------+------------------------+
1 row in set (0.00 sec)

I am not able to relate at this point why the session settings have been changed as I already changed the value using SET GLOBAL.

In order to change wait_timeout we should also change interactive_timeout variables.

The definition of wait_timeout : The number of seconds the server waits for activity on a noninteractive connection before closing it. On thread startup, the session wait_timeout value is initialized from the global wait_timeout value or from the global interactive_timeout value.

Here is the test

Query without changing any parameter , all the values are set to 8 hours by default

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                  28800 |                        28800 |                         28800 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at session level

SET wait_timeout = 10 , interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected , global setting stay same and session settings are changed

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                 28800 |                     10 |                        28800 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Change the parameter at global level

SET GLOBAL wait_timeout = 10 ;
Query OK, 0 rows affected (0.00 sec)

SET GLOBAL interactive_timeout = 10;
Query OK, 0 rows affected (0.00 sec)

Results , all looks good as expected

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)

Disconnect MySQL thread and connect again , and all looks good again

SELECT @@global.wait_timeout , @@session.wait_timeout , @@global.interactive_timeout , @@session.interactive_timeout;
+-----------------------+------------------------+------------------------------+-------------------------------+
| @@global.wait_timeout | @@session.wait_timeout | @@global.interactive_timeout | @@session.interactive_timeout |
+-----------------------+------------------------+------------------------------+-------------------------------+
|                    10 |                     10 |                           10 |                            10 |
+-----------------------+------------------------+------------------------------+-------------------------------+
1 row in set (0.00 sec)


unique constraint on a set With reference to this SO question, what would be a correct way to implement Unique tag Combination Constraint in SQL Server 2005?

To illustrate the problem, I make an example:

A tag_bundle consists of one or more than one tags.
  A unique tag combination can map to a unique tag_bundle, vice versa.

 tag_bundle                   tag            tag_bundle_relation
 +---------------+        +--------+      +---------------+--------+
 | tag_bundle_id |        | tag_id |      | tag_bundle_id | tag_id |
 +---------------+        +--------+      +---------------+--------+
 |       1       |        | 100    |      |       1       |  100   |
 +---------------+        +--------+      +---------------+--------+
 |       2       |        | 101    |      |       1       |  101   |
 +---------------+        +--------+      +---------------+--------+
                          | 102    |      |       2       |  101   |
                          +--------+      +---------------+--------+
                                          |       2       |  102   |
                                          +---------------+--------+

There can't be another tag_bundle having exactly the same combination from tag 100 and tag 101.
There can't be another tag_bundle having exactly the same combination from tag 101 and tag 102.

How can I ensure such unique constraint when executing SQL "concurrently"!!
that is, to prevent concurrently adding two bundles with exactly the same tag combination

Adding a simple unique constraint on any table does not work,
Is there any solution other than Trigger or explicit lock.

I come to only this simple way: make tag combination into string, and let it be a unique column.

tag_bundle  (unique on tags)         tag            tag_bundle_relation
 +---------------+-----------+      +--------+      +---------------+--------+
 | tag_bundle_id |  tags     |      | tag_id |      | tag_bundle_id | tag_id |
 +---------------+-----------+      +--------+      +---------------+--------+
 |       1       | "100,101" |      | 101    |      |       1       |  101   |
 +---------------+-----------+      +--------+      +---------------+--------+
                                    | 100    |      |       1       |  100   |
                                    +--------+      +---------------+--------+

but it seems not a good way :(

Is there no other solution but with triggers?

My only suggestion is to create stored procedure that performs DML on tag_bundle_relation and restrict all users from manipulating the table directly (it's in a sense 'explicit lock' as you say).  I don't see any other acceptable ways to enforce the required restriction. In my understanding your simple solution just adds complexity to the system - you will need more triggers to synchronize tags field with tag_bundle_relation and tag tables. Also, you may get false fail if you are adding records to tag_bundle_relation one by one - for instance, inserting tag_bundle_id=3 with tag_ids 100,101,102 will fail after attempting to insert 101 (assuming 100 already inserted).


How do I locate my Postgres data directory while the server is down? I'm on Mac 10.13.6.  I restarted my machine and Postgres, which normally starts upon restart, wasn't running.  Sadly, I cannot remember how or where I installed it.  I'm able to locate instances of "pg_ctl"

sudo find / -name "pg_ctl"

returns

/usr/local/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.1/bin/pg_ctl
/usr/local/Cellar/postgresql/9.5.0/bin/pg_ctl
/usr/local/Cellar/postgresql/9.6.15/bin/pg_ctl
/usr/local/Cellar/postgresql@9.6/9.6.15/bin/pg_ctl
/usr/local/Cellar/libpq/11.5/bin/pg_ctl
/usr/local/Cellar/postgresql@9.5/9.5.19/bin/pg_ctl

However, I need to tell it a data directory in order for it to start, and I can't figure out how to know the data directory while the server is down.  What kind of files should I look for to figure out where the data directory is?

Check  /usr/local/var/postgres

See also answers for Mac in How do I find PostgreSQL's data directory?.

A Linux PGDATA directory looks like:

bash-4.2$ ls -al $PGDATA
total 72
drwx------. 20 postgres postgres  4096 Apr  8 16:44 .
drwx------.  4 postgres postgres    31 Feb 11 23:07 ..
drwx------.  7 postgres postgres    62 Apr  7 21:27 base
-rw-------.  1 postgres postgres    30 Apr  8 16:44 current_logfiles
drwx------.  2 postgres postgres  4096 Apr  8 17:47 global
drwx------.  2 postgres postgres  4096 Apr  7 08:07 log
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_commit_ts
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_dynshmem
-rw-------.  1 postgres postgres  4513 Feb 13 09:21 pg_hba.conf
-rw-------.  1 postgres postgres  1636 Feb 13 09:21 pg_ident.conf
drwx------.  4 postgres postgres    65 Apr  8 17:49 pg_logical
drwx------.  4 postgres postgres    34 Feb 13 09:21 pg_multixact
drwx------.  2 postgres postgres    17 Apr  8 16:44 pg_notify
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_replslot
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_serial
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_snapshots
drwx------.  2 postgres postgres     6 Apr  8 16:44 pg_stat
drwx------.  2 postgres postgres  4096 Apr  8 17:49 pg_stat_tmp
drwx------.  2 postgres postgres    17 Feb 13 09:21 pg_subtrans
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_tblspc
drwx------.  2 postgres postgres     6 Feb 13 09:21 pg_twophase
-rw-------.  1 postgres postgres     3 Feb 13 09:21 PG_VERSION
drwx------.  3 postgres postgres    89 Mar 21 15:02 pg_wal
drwx------.  2 postgres postgres    17 Feb 13 09:21 pg_xact
-rw-------.  1 postgres postgres    88 Feb 13 09:21 postgresql.auto.conf
-rw-------.  1 postgres postgres 24005 Feb 13 09:21 postgresql.conf
-rw-------.  1 postgres postgres    27 Apr  8 16:44 postmaster.opts
-rw-------.  1 postgres postgres   104 Apr  8 16:44 postmaster.pid
-bash-4.2$


Executing a SQL Job only once a month and after completion of a specific job Let me better explain. My first job executes every 15 minutes (top of the hour, 12:15, 12:30 and 12:45). It does this for every day of the week.
I have a second job that runs at 1:30 AM on the first day of the month. This job only runs once a month.

I need to daisy chain job one with job two only on the last execution of a given month. Meaning, job one executes at 11:45 pm and executes for 3 hours, at that point I need job two to start.

Thanks in advance.
RC

For SQL Server, you can specify On success action to go to the next step, only when the job step #1 is completed. There, use a date/time condition to execute the job#2

if (day(getdate()) = 1 and DATEPART(hh, GETDATE())= 1)
EXEC dbo.sp_start_job N'job#2'


MySQL Replication without stopping master I am currently setting up a replication for my database one of the ways i am thinking of synchronizing the master and slave without write locking the master, is by first taking a log position then perform a backup, then restore the backup on the slave and start the slave with the log position(which will be before the backup was taken), one of the issues i see with this approach is the slave will be reapplying changes to it self which is not a problem in the type of system we have, does any one else see any other pitfalls with this approach? Or are there any other better options to achieve this?

There are two approaches you can try with no or minimal downtime
Given the following:

Master IP is 10.1.20.30
Slave IP is 10.1.20.40

APPROACH #1 : Data is 100% InnoDB
This is very straightforward.
STEP01) If the Master does not have server-id defined in my.cnf you will have to add it
[mysqld]
server-id=100

STEP02) If the Master does not have log-bin defined in my.cnf you will have to add it
[mysqld]
log-bin=mysql-bin

STEP03) If you have do Steps 1 and/or 2 on the Master, do service mysql restart (mandatory)
STEP04) Create MySQL Replication User on the Master
mysql> GRANT SELECT,REPLICATION USER,REPLICATION CLIENT ON *.*
TO repluser@'10.1.2.30' IDENTIFIED BY 'replpass';

STEP05) Create a mysqldump as a point-in-time snapshot on the Master
MYSQL_CONN="-uroot -ppassword"
MYSQLDUMP_OPTIONS="--master-data=1 --single-transaction --flush-privileges"
MYSQLDUMP_OPTIONS="${MYSQLDUMP_OPTIONS} --routines --triggers --all-databases"
mysqldump ${MYSQL_CONN} ${MYSQLDUMP_OPTIONS} > MySQLData.sql

When done, line 22 of MySQLData should have the binary log and position of the Master as of the moment the mysqldump was launched. To see it, just run
head -22 MySQLData.sql | tail -1

STEP06) Create replication status on the Slave with
CHANGE MASTER TO
MASTER_HOST='10.1.20.30',
MASTER_PORT=3306,
MASTER_USER='repluser',
MASTER_PASSWORD='replpass',
MASTER_LOG_FILE='mysql-bin.000001',
MASTER_LOG_POS=4;

STEP 07) Load the mysqldump into the Slave
mysql -u... -p... < MySQLData.sql

Don't worry about replication starting at the right place. Remember, I said line 22 contains the command with correct binary log and position.
STEP 08) Run SHOW SLAVE STATUS\G
If Slave_IO_Running is Yes and Slave_SQL_Running is Yes, CONGRATULATIONS !!!
APPROACH #2 : Data is InnoDB/MyISAM Mix
Rather than reinvent the wheel, please read my earlier posts on using rsync to make a Slave

Jul 08, 2011 - MySQL slave replication reset with no Master Downtime (using MyISAM)
May 23, 2011 - How can I move a database from one server to another?
Apr 08, 2011 - Create a MySQL slave from another slave, but point it at the master

Give it a Try !!!


Do I understand this relationship correctly? So I made this bit of a diagram and I am not sure if I am going the right way in terms of understanding relationships. A character has attributes (Health, Mana, Power etc.) There are a quite a lot of attributes so I created a table that stores attributes and added a relation of an attribute with a character that has a value, all cool and all. Now for the IsValueType relation it gets more confusing. An attribute has a value type (Percentage, flat value etc.) so I created a IsValueType Relation that relates the attributeID with ValueTypeID, is this a one to many relationship like I drew it or is it a one to one relation ship as one attribute can only have 1 value type and 1 value type can only be linked to the same attribute (An attribute can not have 2 or more value types).

To your specific question:

If a ValueType can be applied to several Attributes, but each Attribute can have only one ValueType, then there is a one-to-many relationship between ValueType (the "one" table) and Attribute (the "many" table); one ValueType row can be attached to many Attribute rows. So, the relationship should go the opposite direction, and the Attribute table would include ValueTypeID, as a foreign key to ValueType.

On an unrelated note: AttributeValue would need to be a table in your diagram. Each Character has many Attributes, and and each Attribute may be associated with many Characters. AttributeValue would need CharacterID, AttributeID, and the actual AttributeValue.

One last note: If there are a fair number of attributes that won't apply to all characters, then this design makes sense. However, if almost all attributes would apply to all characters (in the RPGs I'm familiar with, attributes like strength, charisma, mana, hit points, etc. apply to all characters), then it would probably make more sense to have the attributes directly in the character table. If there are just a few attributes that don't apply to all characters, then it may make sense to include them in the character table, and set them to NULL.

Using the approach you've chosen can also wind up taking more disk space; for each attribute used, you've got an additional copy of the character ID and the attribute ID to be stored. Of course, this can go the other way: if there are 100 attributes, and most characters only use 6, then the space to store the NULL values in each of the other 94 columns could easily be more than the space to store 6 extra character and attribute IDs.

Finally, how you use the data affects things as well. If you frequently need to bring up just one attribute for a character and work with that, your queries may be faster and cheaper because you don't have to pull up the character row, just the specific AttributeValue row. If you need to work with several attributes at once, or need other data that's in the character table, then going with including the attributes in the character table might be better.


Oracle: how to set only some roles as not default for a user (no GUI) Scenario:

I have instanceA
I have instanceB
I have USER1 in instanceA
I have USER1 in instanceB
USER1 in instancA has four roles grante to it:

ROLE1 default yes admin_option no
ROLE2 default false admin_option yes
ROLE3 default false admin_option no
ROLE3 default yes admin_option yes

USER1 in instanceB has no roles granted
All mentioned roles exist also in instanceB although not granted to USER1
I have already writen a script that generates the DDL in order to duplicate the role grants for USER1 in instanceB.

Problem:

I haven't found a way to programatically do the following because the DEFAULT=NO part doesn't exist in the GRANT ROLE clause.

grant ROLE1 to USER1; -- admin option no
grant ROLE2 to USER1 DEFAULT NO with admin option;
grant ROLE2 to USER1 DEFAULT NO;
grant ROLE3 to USER1 with admin option;

Notice than I want ro replicate in instanceB the same role set USER1 has in instanceA, meaning two of the roles are default and two aren't.
I've studied setting the default role false for ROLE2 and ROLE3 after granting them, using ALTER USER DEFAULT ROLE but that only works for setting ALL roles to default or setting ALL roles to non-default with NONE.
I cannot find a way for setting only ROLE2 and ROLE3 as not-default and ROLE1 and ROLE2 as default.
I know that I can set that using the visual console but I need to automate this so I need syntax way to do it.
I don't want to make the roles ask for password. All roles in the databases are not password roles.

You have to use the GRANT command to grant roles (with admin option if required), then use ALTER USER to set roles to default (or not). So, the order of commands is,

grant roles (with admin option as needed)
alter user to set all roles to non-default
alter user to set only required roles to default.

A way to generate sql using sql is as follows, you can run this sql in instanceA, and actually execute the generated script in instanceB by replacing username if required. Make sure to test though,

with x as (select 'USER1' as usr from dual)
select cmd from (
select 1 as ord, 'grant ' || granted_role || ' to ' || grantee || case when admin_option = 'YES' then ' with admin option' end  || ';' as cmd
from dba_role_privs, x where grantee = x.usr
union all
select distinct 2 as ord, 'alter user ' || grantee || ' default role none;' as cmd from dba_role_privs, x where grantee  = x.usr
union all
select 3 as ord, 'alter user ' || grantee || ' default role ' || granted_role || ';' as cmd from dba_role_privs, x where grantee  = x.usr and default_role = 'YES')
order by ord


Suggestions on database model design/ performance I'm currently building an 'online examination' type of web app using Ruby on Rails and PostgreSQL (or MySQL), which is going to be part of a bigger application once pushed to production. Since I just recently got into RD design I'd appreciate some feedback on the following design.

App Specifications

There are 1000+ different questions. Each question can have 2-5
different options, only 1 option is correct.
The user is presented with 10 questions at a time.
If a user answers a question correctly 2 times, the question will not
be shown to him again (let's call those questions now completed).
If a user answers a question incorrectly then he needs to answer the
question 2 MORE times correctly.
The user passes the exam when there are no more questions left to
show.

My current database schema is:

user
-user_id
-name etc

question
-question_id
-text
-correct_id (or option_id, stores the correct option id)

option
-option_id
-text
-question_id

user_answer
-user_id
-question_id
-is_correct

So in order to create a '10 question test' we could:

create a list of every completed question querying the user_answer table
create a list of possible questions (all questions - completed questions)
finally serve the user with 10 random questions from the last list.

Is there a better approach to represent "the remaining times a question needs to be answered" inside the database? Please feel free to suggest even a completely different design.

What concerns me is that for each single user there would be more than 2x1000 user_answer rows. This quickly becomes a huge number, so I guess there must be a better way.

Would using a json array be okay in this case? But as far as I understand it'd quickly become a hassle to add more questions etc.

As you are at the DBA site, it is likely we will give you database solutions :-)

As far as I can see, you are stuck with your two options - a row for every users answer or a large list. Best bet in terms of database design is to go for a row for every users answer and to make the columns as narrow as possible.

My solution to this would be to change your field "is_correct" in the "user_answer" table to "answer_status" and make it a tinyint. If the user gets the answer wrong, this is set to 0. If the user gets the answer right, it is incremented by 1. For displaying non correctly answered questions, you would scan for all questions where "answer_status" is < 2. Now the column would represent:

0 = question not yet answered or answered incorrectly
1 = question answered correctly once
2 = question answered correctly

When the user starts their exam, you will make an entry for every question in the question table for that user, with the default answer_status = 0.

If your "question_id" column and "user_id" column are both ints, you will store 9 bytes per row - which means if you use 100MB for this table, you would be storing ~119 million rows. If 10,000 rows per user, you will be storing data for ~12,000 users. You would most likely want to cluster this table on "user_id".

In addition, you could clean up the "user_answer" table by setting a flag in a "user_exam" table. It is likely you will need this table to encapsulate the questions into an exam and to list which exams users are enrolled for. This table could have a "status" field which could be set to "Completed" when a user has passed the exam. Subsequently all of the rows in the "user_answer" table can be deleted for that exam, for that user, once they have completed all the answers (save possibly getting some statistics like length of time taken). This would reclaim space in your database when the tables clustered index is rebuilt.


Pre-existing shared memory block (key 5432001, ID 32768) is still in use I have been seeing this error lately that is causing the server to crash all of a sudden. I have seen elsewhere that the solution is to delete the postmaster.pid and postmaster.opts files and restart the server, but no one has explained why this event happens, and how to stop it from happening again.

It's vital that if you forcibly terminate PostgreSQL then all postgres processes must be killed. If you don't terminate them all then the shared memory block may still be held and the database files may still be open and being written to. In this case starting PostgreSQL again may cause massive database corruption, which is why PostgreSQL tries to stop you doing this. Overriding those safety measures may cause data loss.

You should preferably cleanly stop PostgreSQL with pg_ctl as part of the container shutdown process.

In any case, it's very important that stopping the container terminates all postgres processes.

You should not delete postmaster.pid, it's protecting you from data corruption by allowing PostgreSQL to notice that the shared memory segment is still in use. Unless Docker somehow gets in the way of this process.