Use serial ID for multiple columns and return it from INSERT I am using Npgsql and I want to be able to do an insert, and if statement that will do an update and then return a value so when ExecuteScalar is called it reads that value.  This was done fine in MSSQL but I'm now trying to support PostgreSQL and I can't seem to get it right.

The SQL is similar to this:

insert into mytable (name, uniquecallid)
values (@name, @uniquecallid);

DO
$do$
BEGIN
IF coalesce(@UniqueCallId,'') = '' THEN
    SET @UniqueCallId = coalesce(@CallIdPad, '')
                     || cast(varchar(50), select currval('mytable_id_seq'));
    UPDATE mytable SET UniqueCallId = @UniqueCallId
    WHERE Id = select currval('mytable_id_seq');
END IF;
END
$do$

select currval('mytable_id_seq');

This errors with a "syntax error near select".

What can I do to get this to return the latest value inserted?

I believe this is a Npgsql issue as when inspecting the SQL that it executes the parameters are replaced with the values but the DO block doesn't have the values in place of parameters.

Syntax errors

Immediate causes for the error:

WHERE  id = select currval('mytable_id_seq');

And a missing semicolon at the end of the DO statement:

...
$do$;

Better query

On a closer look, this does not seem to be another case of UPSERT.
It would seem one of the inserted columns needs to depend on multiple input values in combination with the freshly retrieved value for a serial ID. Try this largely simplified statement:

INSERT INTO mytable (id, name, uniquecallid)
SELECT t.id
     , t.name
     , CASE WHEN t.uniquecallid <> '' THEN t.uniquecallid
            ELSE COALESCE(t.callidpad, '') || t.id END

FROM (
   SELECT nextval('mytable_id_seq') AS id
        , '@name'::text             AS name         -- provide values here, once
        , '@UniqueCallId'::text     AS uniquecallid
        , '@CallIdPad'::text        AS callidpad
   ) t
RETURNING id;

An explicit cast to text (or your actual undisclosed data type(s)) is needed.

This is equivalent to the presented code, just with legal syntax and much faster - unless there are triggers or non-standard default values you did not declare ...

Simpler with currval()

On an even closer look, this can be simplified further:

INSERT INTO mytable (name, uniquecallid)
SELECT '@name'::text
     , COALESACE( NULLIF('@UniqueCallId'::text, '')
                , COALESCE('@CallIdPad'::text, '') || currval('mytable_id_seq'))
RETURNING id;

Detailed explanation:

PostgreSQL next value of the sequences?


Truncated copy of a DB for a developer I have an access to a DB (SQL Server 2008 R2).
The size of the DB is about 40GB.
I am a developer and I'd like to have a local copy of that DB to be able to work offline.

Is there an easy way to get truncated version of that DB (all db objects and about 1000rows per table)?

I have a backup of that DB on a test server, but I am not able to copy 40GB over my slow connection.
I don't have permission to restore that backup and delete rows manually and backup again after.

I probably can script schema by Task -> Generate scripts, but how to script data and limit it to ~1000 rows per table(taking into account the fact that there are about a hundred tables and bounded through FK rows should remain safe without loosing a row at one end)

Couple of options :

A. Native to sql server (no third party):

-- make sure to have it in order due to Foreign keys + if your tables have Identity columns then Identity insert should be ON + you have to order by date to get matching data from all the tables.

select 'select top(100) * from '+ name +char(10)+ 'go' from sysobjects
where type = 'U' -- for user tables

Option 2: BCP OUT and BULK Insert.

See my script at https://dba.stackexchange.com/a/43232/8783

Option 3: SSIS wherein you can specify top rows to be extracted from the source database using T-SQL Script task. Sill here you have to manage FK's , Identity columns, etc.

Option 4: Use third party or tools from codeplex (all free)

SSMS Tool pack. Very good and freebie (till SQL Server 2008R2) and it has Insert statement generator
DBScripter
Sql Script Generator

  Licensing only applies to SSMS 2012 and higher versions.
  For previous SSMS versions the SSMS Tools Pack is still FREE.


Why is a COUNT query faster than a result set query? Suppose I have two similar queries, with complex joins; query 1 returns rows and columns:

SELECT col1, col2, col3, ...etc
FROM MyBigTable
   LEFT JOIN AnotherTable1 On ... etc
   LEFT JOIN AnotherTable2 On ... etc
WHERE someCol1= val1
AND someCol2= val2
AND etc...

and query 2 returns just the count of rows as per query 1:

SELECT count(MyBigTable.PKcol)
FROM MyBigTable
   LEFT JOIN AnotherTable1 On ... etc
   LEFT JOIN AnotherTable2 On ... etc
WHERE someCol1= val1
AND someCol2= val2
AND etc...

Self-evident logic tells me the SQL engine (of most databases, say SQL Server, MySQL, IBM DB2, whatevs...) will return from the count query faster
than the result set query – at least when we are talking big result sets.
Obviously transferring lots of rows and columns across a network will take longer than just a single scalar value of count!

My questions are:

Does the DB Engine have to do similar amount of work / effort for both queries?
If so, is the bottleneck (or delay in receiving the results) simply due to transferring bigger data across a network?

Exactly why probably differs between the architecture of different DBMS, but scanning the primary key index is usually faster than scanning the table itself. I would say that the effect in the engine is similar to the effect in the network, the less data you shuffle between different areas in the engine, the faster it goes


Update row with multiple row in another table Error : ORA-01427: single-row subquery returns more than one row? I have Table A with 1 row and wanted to update it from another table (which has 3 rows for it and I can take any one of them).

Table A
-----------
TrnID | trn_Status
 2345 | null
 9876 | null
 9878 | null

Table B
-----------
TrnID | trn_Status
 2345 | 2
 2345 | 2
 2345 | 2
 9876 | 4
 9876 | 4
 9878 | 2

I am facing error when i try to do something like this

UPDATE TableA tabA
SET    ( tabA.trn_status ) = (
                            SELECT trn_status
                            FROM   TableB tabB
                            WHERE  (
                                        tabA.trn_id = tabB.trn_id
                                    )
                         ) ;

Community wiki answer:

Since there are several rows in the result you need to decide which one to use as the new value for trn_status:

Limit your subquery with FETCH FIRST 1 ROW ONLY.
If you can guarantee that all corresponding records are full duplicates, use DISTINCT.
Using min, max, avg aggregate would be other possibilities.


I can't access any database diagram My databases have been working fine on my Windows Server 2016 standard. The database management system is Microsoft SQL Server 2017 Express.

Today I added a new table and content, all is fine. I tried to open the database diagram and received an error message:

And in case the image doesn't work, the message is:

  Cannot execute as the database principal because the permission "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission. (Microsoft SQL Server, Error: 15517)

Nothing has changed on my server - no updates, etc.

I can access all the tables. When I look at the account I log in, I can see I am a the database owner, which other posts suggest to do - but I wasn't previously so unsure as to why this would help.

This issue affects every single database I have.

The only difference is I now create a daily back up from a scheduled task with the following script

BACKUP DATABASE DBName TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQLEXPRESS\MSSQL\Backup\DBName .bak'
WITH NOFORMAT, INIT,  NAME = N'DBName Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10

The result of

SELECT ORIGINAL_LOGIN(), USER_NAME(), SUSER_SNAME();

shows

This is because the owner of the database ( the login ) was deleted. You should remap the owner of the database, to a login that exists.

ALTER AUTHORIZATION
ON DATABASE::[DatabaseName]
TO [A Suitable Login];

Dont use sp_changedbowner. it's a deprecated feature.


Load data from a Table that has the same structure as Table1 with the only diferrence of an ID not null column? So, I know that if you want to load data into a table that does not have the same structure as the data, well you have to do it in the way of column by column, the thing that in this situation, I have many tables created exactly with the only diferrence than table2's have an indentity column. So my questions is, is there a way that I can load data with the db2 INSERT INTO table2  SELECT * FROM table1 but skipping this identity column? somehow in the sytax that I can tell that to the database? doing it column by column syntax will take a lot of time.

You could make the identity column IMPLICITLY HIDDEN.
CREATE TABLE T(I INT NOT NULL GENERATED BY DEFAULT AS IDENTITY, A INT)

ALTER TABLE T ALTER COLUMN I SET IMPLICITLY HIDDEN

INSERT INTO T VALUES (42)

SELECT I, A FROM T

will output
I|A
-|------
1|    42


Progressive child tables scan in PostgreSQL? We have append-only events table where events from different devices are being collected.
We use PostgreSQL 9.6 and pg_partman to split the table into monthly partitions.
Data is partitioned using event_time column.
Each monthly table has about 100M rows.

There's a query from application side that is used to show latest events from a device. It looks something like this (simplified):

SELECT event_time, event_data
FROM events
WHERE device_id = 'zzz'
ORDER BY event_time DESC
LIMIT 10

Every search criteria is covered by index, so PostgreSQL does only index scans.

The problem is that it scans all child tables and only then does the LIMIT 10.
Indexes for all these tables are quite big and don't fit into memory, so it takes up to 20 seconds to complete this query.

But in most cases the 10 most recent events are available from one child table (partition) that is named events_p<current_year>_<current_month>.

Is there any way to implement "progressive" scan for child tables inside PostgreSQL without changing application code?

For example:

events
events_p2019_02 <- and so on...
events_p2019_03 <- scan this if less than 10 rows found in _04 and _05
events_p2019_04 <- scan this if less than 10 rows found in events_p2019_05
events_p2019_05 <- scan this first

We don't know in advance the possible date range for that 10 events. Some device may have them during the last day, while others may have them spanned over several months (so in many child tables).

The problem is that it scans all child tables and only then does the
  LIMIT 10. Indexes for all these tables are quite big and don't fit
  into memory, so it takes up to 20 seconds to complete this query.

If you have the correct indexes, which in this case would be on events (device_id,event_time), then it will only hit a tiny part of each index (the path leading to the leaf page containing the highest event_time for the specified device_id for each partition), and that part will probably be readily cached, depending on how many distinct device_id exist and are routinely queried.

When version 12 is released, then if you use declarative range partitioning rather than partitioning by inheritance the system will be smart enough to hit partitions in appropriate order and not startup the index scan on more partitions once the LIMIT is already satisfied.  However, this might only be a minor improvement in your case.  If you have the right index, it should be so fast already that no improvement is necessary, and if you have the wrong index it might be annoyingly slow even with this improvement.

If you can't wait for version 12 and refactor your partitioning, and can't build the appropriate indexes, then a set returning function which manually iterates through the partitions in order might the way to go, despite being tedious, ugly, and error prone.


Is it okay to clear SQL Server Buffer every weekend or month end? We have started using SQL Server 2014 Enterprise edition on our new server having 32 gigs of RAM, and in less than 3 weeks the server process is accumulated 13 GB and plus of RAM.

Including IIS worker processes the total amount of RAM being used is accumulated somewhere 75%-80% so we are just curious if we need to clear the buffer every weekend of at the month end. But, before that we want to make sure if it is a wise idea to do so! SO my questions:

Is it a wise Idea to limit the max server memory for SQL Server? (I guess NOT)
Is it a good idea to clear the buffer pool at a certain frequency (weekend / month-end)? Will it impact the performance?

Thanks in advance!

It's almost always a good idea to set max server memory setting to some value lower than the total amount of memory available, especially if there are other applications running on the same server (IIS in your case). The actual number depends on total amount of memory and on memory requirements of those other applications.

This setting is important to prevent the various applications/services competing for memory (causing memory starvation), which in turn can cause performance problems.

With 32 GB available, I'd subtract from it 3 GB for the OS, then subtract whatever number is used by IIS and other applications, if any, and set the max server memory to the remainder.

There is no need to periodically clear the SQL Server buffer pools, just the opposite.


How to display User Mapping for offline databases? When I use the GUI (SSMS) to review the User Mapping, where the Login has mapping to a database that is offline (or otherwise not available) I get the message below and it only displays Mapping for databases that are online.

  One or more databases are inaccessible and will not be displayed in list

All the details should actually be in the Master DB, there should be something I can run that will show me what the full User Mapping is.

How can I display User Mapping for offline databases?

You can use sys.databases joined with sys.server_principals

SELECT sd.NAME AS databaseName
    ,sp.NAME AS database_owner
FROM sys.databases sd
INNER JOIN sys.server_principals sp ON sd.owner_sid = sp.SID
WHERE sd.state_desc = 'OFFLINE' --> since you want for offline databases !

You can even use at your own risk --> sp_msloginmappings ( <-- Undocumented and unsupported :-) )


SQL Server 2014 - Give user permission to run sp_refreshview I need to give a user the required permission to run sp_refreshview for a specific database on our server.  I've tried adding the user to the db_executor role but that only gave them access to certain stored procedures.

Am I looking to accomplish something that is impossible for SQL Server permissions or am I just looking in the wrong places?  Thank you in advance for any guidance you can provide.

Per the sp_refreshview msdn library page (https://msdn.microsoft.com/en-us/library/ms187821.aspx):

  Permissions
  Requires ALTER permission on the view and REFERENCES permission on common language runtime (CLR) user-defined types and XML schema collections that are referenced by the view columns.


alert when any job in a job category fails Is it possible to set up an alert in SQL Server 2008 that will send an e-mail anytime a job in a specific category fails?

I am wondering because I would like to set up an e-mail anytime an SSRS subscription fails - and all of these subscriptions are jobs in the category Report Server.

EDIT - it turns out that when an SSRS subscription fails, the job itself does not fail, so my question will not apply to the SSRS subscription monitoring use.  However I would still like to know for other jobs that we run in our environment

You could create a job that checks the msdb.dbo.sysjobhistory table every minute (or however frequently you want). You might want to implement a queue table so you only ever send the message for any single instance failure once.

USE msdb;
GO

CREATE TABLE dbo.ReportServerJob_FailQueue
(
  job_id UNIQUEIDENTIFIER,
  run_date INT,
  run_time INT, -- horrible schema, just matching sysjobhistory
  sql_message_id INT,
  sent BIT NOT NULL DEFAULT 0,
  PRIMARY KEY (job_id, run_date, run_time)
);

So then your code, that you can schedule in a job, becomes:

INSERT dbo.ReportServerJob_FailQueue
  (job_id, run_date, run_time, sql_message_id)
SELECT job_id, run_date, run_time, sql_message_id
FROM msdb.dbo.sysjobhistory AS h
WHERE step_id = 0
AND run_status = 0
AND EXISTS
(
  SELECT 1 FROM msdb.dbo.sysjobs AS j
    INNER JOIN msdb.dbo.syscategories AS c
    ON j.category_id = c.category_id
    WHERE j.job_id = h.job_id
   AND c.name = 'Report Server'
)
AND NOT EXISTS
(
  SELECT 1 FROM dbo.ReportServerJob_FailQueue
    WHERE job_id = h.job_id
    AND run_date = h.run_date
    AND run_time = h.run_time
);

Now I assume you want to send an individual e-mail for each failure, so this could be part of the job as well (or part of a different job, though that isn't necessarily wise):

DECLARE
  @subject NVARCHAR(4000),
  @body NVARCHAR(4000),
  @name SYSNAME,
  @id UNIQUEIDENTIFIER,
  @date INT,
  @time INT,
  @msg INT;

DECLARE c CURSOR LOCAL STATIC READ_ONLY FORWARD_ONLY
FOR SELECT q.job_id, q.run_date, q.run_time, q.sql_message_id, j.name
  FROM dbo.ReportServerJob_FailQueue AS q
  INNER JOIN msdb.dbo.sysjobs AS j
  ON q.job_id = j.job_id
  WHERE q.sent = 0;

OPEN c;

FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;

WHILE @@FETCH_STATUS = 0
BEGIN

  SET @subject = 'Report Server job ' + @name + ' failed.';
  SET @body = 'Error number: ' + RTRIM(@msg);

  BEGIN TRY
    EXEC msdb.dbo.sp_send_dbmail
      @profile_name = 'default',     -- you may need to change this
      @recipients   = 'foo@bar.com', -- you will need to change this
      @subject      = @subject,
      @body         = @body;

    UPDATE dbo.ReportServerJob_FailQueue
      SET sent = 1
      WHERE job_id = @id
      AND run_date = @date
      AND run_time = @time;
  END TRY
  BEGIN CATCH
    PRINT 'Will have to try that one again later.';
  END

  FETCH NEXT FROM c INTO @id, @date, @time, @msg, @name;
END

CLOSE c; DEALLOCATE c;

There are some other options too:

pull in sysjobhistory.message
look at individual steps that failed
only send a message for any job once in n minutes/hours even if there are multiple failures
send a single e-mail with a list of all the jobs that have failed, instead of an e-mail for each failure
you might want to include the run_date and run_time in the message, since the e-mail may not be sent or received quickly enough to be an accurate measure of when the job actually failed (I did not include it here because their horrible data type choices make formatting that stuff a royal PITA)
you'll probably want to clean up old rows after some time, so a purge command may be desired as well

If Database Mail isn't already set up, please see this tutorial.

You could also use 3rd party tools (e.g. SQL Sentry) that will make a lot of this simpler. Full disclosure: I work for SQL Sentry.


Find out which columns must be specified in insert-statement Sometimes when inserting data in a table with many columns it could be useful to know which columns must be specified if the insert-statement shouldn't fail.

I wrote this query to find out which columns are not nullable, identity, computed, timestamp and have no default value.

select  *
from    sys.columns
where   object_id = object_id('<table>') and
        is_nullable = 0 and
        is_identity = 0 and
        is_computed = 0 and
        default_object_id = 0 and
        type_name(system_type_id) <> 'timestamp'

Are there any other properties that should be included in this check?

The BOL documentation for the INSERT grammar has this to say about column_list

  If a column is not in column_list, the Database Engine must be able to
  provide a value based on the definition of the column; otherwise, the
  row cannot be loaded. The Database Engine automatically provides a
  value for the column if the column:

  Has an IDENTITY property. The next incremental identity value is used.
  Has a default. The default value for the column is used.
  Has a timestamp data type. The current timestamp value is used.
  Is nullable. A null value is used.
  Is a computed column. The calculated value is used.

It looks as though your query covers all these cases to me.


How to create INOUT assignment cast on Amazon RDS PostgreSQL? If not possible, then why? I'm migrating existing application to Amazon RDS PostgreSQL.
The application has cross-database support and for compatibility reasons it includes the following SQL:

CREATE CAST (varchar AS numeric) WITH INOUT AS ASSIGNMENT

On plain PostgreSQL, this requires superuser. On Amazon RDS PostgreSQL this fails with

  ERROR: must be owner of type character varying or type numeric

Is there a way to create such a cast in Amazon RDS (perhaps via administrative web interface)?
If this is not possible, then why? Is this deliberate limitation (e.g. is the cast something dangerous) or just a missing feature?

Note: I guess that whether this is assignment or implicit cast is irrelevant, but included this information for completeness.

On AWS RDS you must first alter type <type> owner to <user> before create cast since the default owner of all types is the rdsadmin user.

To run alter type you must do so with the master user system account you created for RDS or any other user that you have given the rds_superuser role. From the docs:

  When you create a DB instance, the master user system account that you
  create is assigned to the rds_superuserrole.
  The rds_superuser role is similar to the PostgreSQL superuser role
  (customarily named postgres in local instances) but with some
  restrictions.


How many IP numbers are there for a SQL server active active cluster setup also a active passive setup? How many IP numbers are required for a SQL server active/ active cluster setup also what in the case of an active/ passive setup?
Correct me if I am wrong
2 for private network
2 for public network ......
1 for virtual server IP
1 for SAN
1 for MSdtc

You are correct

For Multi Instance SQL Cluster (active/active )

1 IP for Windows Cluster

2 IP for SQL Server Virtual Network Name (SQL instance on each node)

1 IP for MSDTC (this is optional however required if you use distributed transaction like linked server)

2 IP for public network (1 per node)

2 IP for heartbeat network (1 per node)

for Single Instance single cluster ( active/passive)

in above instead of two you will only need one IP address for SQL Server virtual network name


Database not showing I create a database in the terminal linux ubuntu,gave it all privileges,created the table,inserted the rows.When I open phpmyadmin the database isn't showing how do I connect them?
I did use mysql -u root -p to open mysql.

Answered in a comment by Rick James:
phpmyadmin does not have permission to see that database.  Change it to use root.


How to resolve Geospatial Data over linked server causing error? Update of the issue.

The exact error is Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.

The error is happening on my local instance of SQL Server 2008r2 in a sp that calls a linked server (another sql server 2008r2) via an OPENQUERY call. I am executing that sp from my local instance. The code that calls the linked server fails but only when geospatial data (geometry) is included in the select part of the statement( the statement called in the open query.) When there is no geospatial data in the select statement to the linked server it executed fine. So geospatial data over a linked server connceting causes the error.

I should point out, but this may be a red herring, that the same stored procedure was called from an sql task in an ssis package and it passed. It called the procedure on my local instance of sql server. But that is not the issue now. The issue is outlined above.

I should also point out that Microsoft called that particular error a Memory Pressure error.

The "memory" being referred to in the term "memory pressure" is physical RAM, not hard-drive space.

What exactly are you attempting to do when you get this error? It seems that it could be due to attempting to invoke CLR code from within an sp_OA* OLE-Automation stored procedure call. That is documented in the following Microsoft forum thread: CLR initialization failed with hresult 0x80131022.

According to the following Microsoft KB article (# 2003681), it could be due to actual memory pressure: Possible causes of "Failed to initialize the Common Language Runtime (CLR)" when using SQL CLR. Memory pressure refers to running low on physical RAM as that is what SQL Server prefers to use. For doing stuff in SQLCLR, these types of errors were much more common prior to SQL Server 2012, especially on 32-bit systems. Are you on a 32-bit system?

Also, in this Microsoft forum thread (related to SQL Server 2008 R2) -- Failed to enter Common Language Runtime (CLR) with HRESULT 0x80131022. This may due to low resource conditions -- Bob Beauchemin states:

  You can increase the amount of memory available to SQLCLR on 64-bit systems by decreasing SQL Server's max_server_memory setting. You can monitor the amount of memory used by SQLCLR with a sum of sys.dm_os_memory_objects where type like '%CLR%' and the appropriate perfmon counters in the .NET CLR memory series.

For more information on SQL Server memory architecture (for SQL Server 2005, 2008, and 2008 R2), please see the following articles:

Basics of SQL Server Memory Architecture (by Karthick P.K)
Understanding the VAS Reservation (aka MemToLeave) in SQL Server (by Jonathan Kehayias)


Best way to filter result set by secondary key I have a table with a primary key and a secondary key, something like

CREATE TABLE Entry (
     PrimaryKey int NOT NULL,
     SecondaryKey int NOT NULL,
     Data nvarchar(100) NOT NULL,
     DateCreated DATETIME NOT NULL
);

    INSERT INTO Entry VALUES(1,1,'test',getutcdate()),
(2,1,'test',getutcdate()),
(3,2,'test',getutcdate()),
(4,3,'test',getutcdate()),
(5,3,'test',getutcdate()),
(6,3,'test',getutcdate()),
(7,4,'test',getutcdate()),
(8,4,'test',getutcdate())

I want to be able to return the entries sorted by datecreated, but only 1 entry per secondary key.  so with the above data, i would only get 4 rows back, the most recent entry for each secondary key.

Whats the best way to do this?

UPDATE:
question answered below, but the question i have next is 'consider the table if it has multiple data fields, what is a better query?'  The original response, where everything is in the CTE:

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
 From CTE where rn = 1

or in another query, where the data kept in the CTE is minimized:

    with CTE as (
     select [PrimaryKey], SecondaryKey, DateCreated,
            rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
      from dbo.Entry
      )
     SELECT PrimaryKey,SecondaryKey, [Data1],[Data2],[Data3],...,DateCreated
FROM ENTRY
WHERE PrimaryKey IN( select [PrimaryKey] FROM CTE where rn = 1 )

You should just use

row_number() over (partition by SecondaryKey
                   order by DateCreated desc)

this will partition the data by SecondaryKey and then you will get the most recent data (ordered by DateCreated desc)

with CTE as (
 select [PrimaryKey], SecondaryKey, [Data],DateCreated,
        rn = row_number() over (partition by SecondaryKey order by DateCreated desc)
  from dbo.Entry
  )
 select [PrimaryKey], SecondaryKey, [Data],DateCreated
 From CTE where rn = 1 --rn = 1 is most recent data since we are ordering by desc

see SQLFiddle


Upgrading SSIS Packages to 2017 The purpose of this question is to document my answer to what seemed to be a strange issue when changing the target SQL Server version of an SSIS package created in some older versions of SSIS prior to 2012 that use script tasks.

An SSIS package originally created in BIDS 2005 or 2008 had no issues when upgraded through the various versions of BIDS/SSDT over the years. Even now when the package was upgraded to SSDT and targeting SQL Server 2016 everything still worked as it always has in addition to being able to take advantage of the latest SSIS features (package parts, project parameters, etc.) and deploying the package through SSDT/Visual Studio to the same target SQL Server. SQL Servers with SSIS installed that were upgraded to 2016 or 2017 seemed to have no issues with packages that were in MSDB or the package store since the jobs continued to run as expected post-upgrade.

So as part of normal upgrade routine, decided to open an SSIS package and upgrade it like before with the previous SSDT changes. In previous SSDT changes, one release worked for a particular version of SSIS. Once they introduced the ability to target some previous versions of SSIS, that feature seemed to work as expected. I figured things would be the same with the latest SSIS and SSDT versions.

However, once changing the target version to SQL Server 2017 within SSDT, there are some things that showed up that SSDT did not catch/fix or could not handle for some reason. Some of the issues I ran into were:

Cannot change the target .Net Framework version as it always reverts
back to original settings in the script task properties
Errors would show up such as "'Dts' is not a member of 'Microsoft.SQLServer'", "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase' is not defined", "Failed to migrate scripts contained in the package to the VSTA 14.0 format. Move the scripts to a new Script task.", and "Type 'Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute' is not defined."
Building the script task would fail and one of the errors would be "The primary reference "Microsoft.SqlServer.ScriptTask, Version=14.0.0.0, Culture=Neutral, PublicKeyToken=89845dcd8080cc91" could not be resolved because it was built against the ".NETFramework,Version=v4.5" framework. This is a higher version than the currently targeted framework ".NETFramework,Version=v4.0" and the other error I would see was "Namespace or type specified in the Imports 'Microsoft.SqlServer.Dts.Runtime' doesn't contain any public member or cannot be found. Make sure the namespace or the type is defined and contains at least one public member. Make sure the imported element name doesn't use any aliases."

The good thing is changing the target back to SQL Server 2016 seemed to work and I could continue editing the packages as before without errors and build would work as expected but the bad news is deploying the 2016 package to an 2017 SSIS server did not get converted like it did during the SQL Server upgrade. So how would I be able to update the packages to target SQL Server 2017 and not have these issues that prevent that?

The main source of the problem is the tags changed in SSIS schema that 2016 and prior handled without issue. I had to view the code of the SSIS package to check for differences and SSIS 2017 seemed to be a bit more strict when it came to the schema within the PropertyGroup node of the Project tag for the script tasks.

Here is what one tag looked like that had errors:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
</PropertyGroup>

The fix that seemed to solve half of my problems were to add the missing entries so that the above now looked like the below code new entries (which are after </ProjectGuid> and before </PropertyGroup>) are:

ProductVersion
SchemaVersion
AppDesignerFolder
TargetFrameworkVersion
FileAlignment
ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch
TargetFrameworkProfile

Here is what it looked like after adding the changes:

<PropertyGroup>
  <ProjectTypeGuids>{30D016F9-3734-4E33-A861-5E7D899E18F3};{F184B08F-C81C-45F6-A57F-5ABD9991F28F}</ProjectTypeGuids>
  <Configuration Condition=" '$(Configuration)' == '' ">Debug</Configuration>
  <Platform Condition=" '$(Platform)' == '' ">AnyCPU</Platform>
  <OutputType>Library</OutputType>
  <RootNamespace>ST_f5177258030742e5ba64fefba1f795ea.vbproj</RootNamespace>
  <AssemblyName>ST_f5177258030742e5ba64fefba1f795ea.vbproj</AssemblyName>
  <StartupObject></StartupObject>
  <OptionExplicit>On</OptionExplicit>
  <OptionCompare>Binary</OptionCompare>
  <OptionStrict>Off</OptionStrict>
  <OptionInfer>On</OptionInfer>
  <ProjectGuid>{D93B5E70-A7E9-4AA1-94E7-7859FE6B3672}</ProjectGuid>
  <ProductVersion>8.0.30703</ProductVersion>
  <SchemaVersion>2.0</SchemaVersion>
  <AppDesignerFolder>My Project</AppDesignerFolder>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
  <FileAlignment>512</FileAlignment>
  <ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>true</ResolveAssemblyReferenceIgnoreTargetFrameworkAttributeVersionMismatch>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Doing this did not fix the issue with inability to change .Net Framework target so there had to be something else so I looked through the code and found the problem. In the code I found the following after </ProjectExtensions>:

<PropertyGroup>
  <TargetFrameworkVersion>v4.0</TargetFrameworkVersion>
</PropertyGroup>
<PropertyGroup>
  <TargetFrameworkProfile></TargetFrameworkProfile>
</PropertyGroup>

Since these were now located in the main PropertyGroup of the script task, they are considered redundant so they are no longer needed in the old location. Deleting these entries fixed the other half of the problem. After doing this, not only was I able to change the SQL Server target to 2017, I was also able to change the .Net Framework version and it would not revert. Also, the builds were able to build successfully again.

Fixing the problem this way was easier than to try opening every package and manually creating a new script task, copying the logic from the old one to the new one, making sure the variables and references were the same, and deleting the old task, among other things. That process would take too long especially if there are a lot of packages to go through.

Hopefully you find this useful if you run into this issue.


Why is this sqlite query much slower when I index the columns? I have a sqlite database with two tables, each with 50,000 rows in, containing names of (fake) people. I've constructed a simple query to find out how many names there are (given name, middle initial, surname) that are common to both tables:

select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;

When there are no indexes except on the primary keys (irrelevant to this query), it runs quickly:

[james@marlon Downloads] $ time sqlite3 generic_data_no_indexes.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    0m0.115s
user    0m0.111s
sys     0m0.004s

But if I add indexes to the three columns on each table (six indexes in all):

CREATE INDEX `idx_uk_givenname` ON `fakenames_uk` (`givenname` )
//etc.

then it runs painfully slowly:

[james@marlon Downloads] $ time sqlite3 generic_data.sqlite "select count(*) from fakenames_uk inner join fakenames_usa on fakenames_uk.givenname=fakenames_usa.givenname and fakenames_uk.surname=fakenames_usa.surname and fakenames_uk.middleinitial=fakenames_usa.middleinitial;"
131

real    1m43.102s
user    0m52.397s
sys     0m50.696s

Is there any rhyme or reason to this?

Here's the result of EXPLAIN QUERY PLAN for the version without indexes:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING AUTOMATIC COVERING INDEX (middleinitial=? AND surname=? AND givenname=?)

This is with indexes:

0|0|0|SCAN TABLE fakenames_uk
0|1|1|SEARCH TABLE fakenames_usa USING INDEX idx_us_middleinitial (middleinitial=?)

In SQLite, joins are executed as nested loop joins, i.e., the database goes through one table, and for each row, searches matching rows from the other table.

If there is an index, the database can look up any matches in the index quickly, and then go to the corresponding table row to get the values of any other columns that are needed.

In this case, there are three possible indexes. Without any statistical information (which would be created by running ANALYZE), the database chooses the smallest one, to reduce I/O. However, the middleinitial index is useless because it does not greatly reduce the number of table rows that need to be fetched; and the additional step through the index actually increases the I/O needed because the table rows are no longer read in order, but randomly.

If there is no index, the lookup of matching rows would require a complete table scan of the second table for each row of the first table. This would be so bad that the database estimates that it is worthwhile to create and then drop a temporary index just for this query.
This temporary ("AUTOMATIC") index is created on all colunms used for the search. The COUNT(*) operation does not need values from any other columns, so this index happens to be a covering index, which means that it is not necessary to actually look up the table row corresponding to an index entry, which saves even more I/O.

To speed up this query, create this index permanently, so that it is no longer necessary to construct a temporary one:

CREATE INDEX uk_all_names ON fakenames_uk(surname, givenname, middleinitial);

EXPLAIN QUERY PLAN
SELECT count(*)
FROM fakenames_uk
JOIN fakenames_usa USING (givenname, middleinitial, surname);

0|0|1|SCAN TABLE fakenames_usa
0|1|0|SEARCH TABLE fakenames_uk USING COVERING INDEX uk_all_names (surname=? AND givenname=? AND middleinitial=?)

The index on surname is no longer needed because the three-column index can be used for any lookups on this column.
The index on givenname might be useful if you will do lookups on this column only.
The index on middleinitial is always worthless: a query that searches for one of the 26 possible values is faster if it just scans the entire table.


Index a field containing people's names I have a materialized view of around 200 million records and would like to add an index to the 'full_names' field to speed up the search. The name data is stored like this;

| full_names                                                    |
-----------------------------------------------------------------
| Smith, John | Doe, Jane,    | Doe, John                       |
| Smith, James| Smith, Clare, | Doe, John                       |
| Adams, John | Doe, Jane,    | Doe, Steven                     |

Since my search term uses 'ILIKE' and '%' a traditional btree index will not work. I had intended to use a trigram index using the following:

CREATE INDEX idx_full_names_trgm_gist ON sample_table USING gisy(full_names gist_trgm_ops);

but received this error:

ERROR: index row requires 10216 bytes, maximum size is 8191

My next thought was to us a ts_vector but my understanding was that ts_vector breaks down words to dictionary defined lexemes. I assume this won't work with names since they are not dictionary words?

Any suggestions on the best way to index this data for fast searching?

This data is largely static and is not updated regularly. External search tools like Solr are not currently an option dues to resrtictions on the server so I was hoping to use postgres' native functions.

Postgres 9.5/RHEL 6.8

Try building a GIN index with gin_trgm_ops instead of GiST.  I've never found GiST useful for trgm indexes.

That said, if your are failing with that message, you probably have some weird data in your table that should be cleaned up. Can you figure which row is causing the problem?  (If not, start with the longest records first as the most likely suspects)


AlwaysON : Log_Send_Queue_Size is increasing but the log_send_rate keeps decreasing? Question

Why, despite the increasingly log_send_queue_size for these two databases, did the log_send_rate keep decreasing?
There were no bandwidth issues on the network at this time, and no other databases experienced this issue. If this happens again, is there a recommended fix, apart from having to manually restore the primary database over the secondary to re-synchronize the pair?

Environment :

  SQL 2012, SP1 CU7 (Build 3393)

  Windows Server 2012 Standard (Build 9200)

  Availability Group of 10 databases (PRDDB1-AG1)

  2 AG replicas, one in London and one in New York (LDSERVER1 & NYSERVER1), primary in NY, secondary in London.

  2 databases within AG1, E-DB1 (50GB log file) and T-DB2 (250GB log file)

The T-DB2 database imports files from clients, processes them (lots of log activity) and then outputs to/updates data in the E-DB1 database.

This process generates lots of data churn and log activity against both databases.
We have occasional spikes of latency between London and New York database replicas maybe once or twice a week maximum but these always clear within a few hours.

Issue :

Last week we saw an increasing log_send_queue_size, and a decreasing log_send_rate. This started on the Monday, and carried on through to Friday night, when it was manually resolved (see Fix section below).
At its lowest, the log_send_rate of the E-DB1 database was just over 100KB/sec with a log_send_queue of over 40GB.
The T-DB2 database had a log_send_rate of 2000KB/sec shrinking down to 300KB/sec, with a log_send_queue of over 300GB.

This led to an increasing amount of latency between the primary and secondary replicas for these two databases within the availability group.
This was characterised by a build-up of log activity within the transaction log for each database affected, which is expected.
Because of this latency, the logs of each database affected expanded to the point where the log drive was in danger of running out of space.

This latency only occurred on these two databases, despite some quite large spikes in transactional activity across all databases within the availability group, as is normal.

Throughout this issue, there was no build-up in the redo queue on the secondary, and the redo_rate stayed high. This would imply that the issue was due to the low send rate for both the affected databases.

Steps attempted

Suspend data movement of the T-DB2 database. I hoped this would free up network bandwidth for the priority database, E-DB1. No effect.
Rebooted the secondary node (LDPRDENTDB1). No effect.

Fix

The following steps resolved the issue. As the log files had grown to over 300GB, I needed to purge and shrink them soon before we ran out of disk space.

a. Removed databases from the availability group.

b. Dropped databases on secondary.

c. Re-added databases back into availability group on primary (NYSERVER1, manual synchronisation option).

d. Backed up the databases on primary and restored to secondary (70GB copied from NY to LD, over just under 24 hours)

e. Re-added databases back into availability group on secondary.

Answering my own question, as future readers will benefit from it :

Seems like we might be hitting Longer latency for SQL Server 2012 database when you use Service Broker, database mirroring, and Availability Groups. This is fixed in SQL server 2012 SP2 CU1. The KB 2976982 has a typo(AlawysOn). So if you are searching by AlwaysON, it wont show up.

After the patch was applied, the issue was fixed.


High availability in postgresql We will be switching from mysql cluster to either postgresql or oracle (with RAC). Mysql cluster is quite reliable, but lacks features in SQL that make programmers life easier (they consider it as datastore, not database;). I know there are multiple possibilities how to achieve redundancy in pgsql but I think none of them is reliable as mysql cluster. So

1) Is there any good conceptual design that could offer reliable pgsql cluster - reliability, data corruption prevention, high availability, no SPOF, load balancing and access to DB through one (virtual?) IP address?

2) Do you know some companies that are able to design and deploy the cluster?

This is pretty broad. Let's answer (2) first:

http://www.postgresql.org/support/professional_support/

Now, on to (1):

  Is there any good conceptual design that could offer reliable pgsql
  cluster - reliability, data corruption prevention, high availability,
  no SPOF, load balancing and access to DB through one (virtual?) IP
  address?

The question is not "if" but "which".

You are describing a heartbeat/failover cluster. There aren't really fully canned solutions for this, you'll need your own heartbeat and STONITH using appropriate non-PostgreSQL-specific tools.

You can use PostgreSQL 9.1 streaming replication and log shipping for the replication. The replication manager repmgr can help automate much of the management.

Other replication options are described on the PostgreSQL wiki. All will require added heartbeat and STONITH for a fully transparent cluster with virtual IP, etc.

You might also want to look at pgbouncer. It's a useful tool not only for connection pooling, but also for failover and load balancing.

See the PostgreSQL manual on high availability, load balancing, and replication for more information.


Convert TimeStamp In dm_os_ring_buffers I see this conversion a lot when trying to convert this to a legitimate time:

SELECT DATEADD (ms, -1 * ((s.cpu_ticks) - r.[timestamp]), GETDATE())
FROM sys.dm_os_ring_buffers r
                CROSS JOIN sys.dm_os_sys_info s

I get the error:

  Arithmetic overflow error converting expression to data type int.

What's the correct formula for converting this?

Answer:ms_ticks

See the SQL Server 2008 calculation in this article. It applies to 2012, 2014 and 2016 too.

cpu_ticks is what was used in SQL Server 2005.

declare @ts_now bigint

select @ts_now = ms_ticks from

sys.dm_os_sys_info

select record_id, dateadd (ms, (y.[timestamp] -@ts_now), GetDate())
...


How can one calculate percentage from previous week in aggregate query? I have a challenge I set out to do that seemed initially trivial. Not so for my developper brain.

Consider the following simple view, used to validate a cron I wrote that queries a subset of 200 000 statements every saturday.

It goes as follows:

mysql> SELECT
    ->     DATE_FORMAT(s.created, "%Y-%m-%d") as "Date",
    ->     count(s.id) AS "Accounts credited",
    ->     sum(s.withdrawal) "Total Credited"
    ->   --  100 * (sum(s.withdrawal) - sum(prev.withdrawal))
         --   / sum(prev.withdrawal) "Difference in %"
    ->     FROM statements s
    -> --    LEFT JOIN prev
    -> --        s.created - interval 7 DAY
    ->  --     ON prev.created = s.created - interval 7 DAY
        --     AND (prev.status_id = 'OPEN'
        --     OR prev.status_id = 'PENDING')
    ->     WHERE (s.status_id = 'OPEN' OR  s.status_id = 'PENDING')
    ->     GROUP BY YEAR(s.created), MONTH(s.created), DAY(s.created)
    ->       ORDER BY s.created DESC
    ->     LIMIT 8;

+------------+-------------------+----------------+
| Date       | Accounts credited | Total Credited |
+------------+-------------------+----------------+
| 2019-01-19 |             18175 |        3173.68 |
| 2019-01-12 |             18135 |        4768.43 |
| 2019-01-05 |             17588 |        6968.49 |
| 2018-12-29 |             17893 |        5404.18 |
| 2018-12-22 |             17353 |        7048.18 |
| 2018-12-15 |             16893 |        7181.34 |
| 2018-12-08 |             16220 |        9547.09 |
| 2018-12-01 |             15476 |        7699.59 |
+------------+-------------------+----------------+
8 rows in set (0.79 sec)

As is, the query is efficient and practical.  I merely would like to add a column,  difference in percentage, from previous week's total, as seen with the -- commented out code.

I have tried various approaches, but because of the GROUP BY, adding an inline column to get the sum(withdrawal) of previous week makes the query run ... forever.

I then tried the LEFT JOIN approach, but this has the same problem, Obviously.  I think the added JOIN has to fetch the sum of previous week for every row of the outer select.

I then had the (not so smart) idea of querying my view, even but then it seems I would have the same issue.

I assume there are much more optimal approaches out there to this simple task.

Is there an elegant way to calculate a percentage from such a query?

Would a stored procedure or some other 'non-plain-sql' approach be more optimal?

When you use MySQL >= 8.0, you can use CTEs (common table expressions)

 ;
WITH
    (
        SELECT  DATE(s.created) as "Date",
                COUNT(s.id) AS "Accounts credited",
                SUM(s.withdrawal) "Total Credited"
            FROM  statements s
            WHERE  s.status_id IN ('OPEN', 'PENDING')
            GROUP BY  DATE(s.created)
            ORDER BY  s.created DESC
    ) AS cte
SELECT  cte.*,
        100 * (cte.`Total Credited` - prev.`Total Credited`) /
              prev.`Total Credited`  AS "Difference in %"
    FROM  cte
    LEFT JOIN  cte prev  ON cte.`Date` = prev.`Date` - INTERVAL 7 DAY;

If you use an earlier version, just replace the CTE with appropriate subqueries, like

SELECT <your_fancy_percentage_calculation>
FROM (<cte from above>) a
LEFT JOIN (<cte from above>) b ON a.Date = b.Date - INTERVAL 7 DAY;

MySQL is smart enough to not execute the same query twice, I think.

Or you create a view and use this.


Clever way to order a json item by key in postgresql (two examples) I need to sort a json item in a record, so here are two examples:
First case
create table jsontable (
    jsonitem json
);

Next I insert a json item:
insert into jsontable(jsonitem) values ('{ "3" : "foo", "2" : "bar", "1" : "qux" }');

Then I query the values:
select t.jsonitem from jsontable t;
                 jsonitem
-------------------------------------------
 { "3" : "foo", "2" : "bar", "1" : "qux" }
(1 row)

Let's suppose the key is unique and is an integer > 0;
Question 1: ¿ How to sort the json item values by the key value, and get the following ?
{ "1" : "qux", "2" : "bar", "3" : "foo" }

Second case
This case use named keys and named values.
create table jsontable_arr(
   jsonitem json
   );

insert into jsontable_arr(jsonitem) values ('[  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]');

select t.* from jsontable_arr t;
                                                jsonitem
--------------------------------------------------------------------------------------------------------
 [  { "key" : "3" , "value": "foo"}, { "key" : "2" , "value": "bar"}, { "key" : "1" , "value": "qux"} ]
(1 row)

Question 2: ¿ How to sort the json array item by the key value, and get the following?
[  { "key" : "1" , "value": "qux"}, { "key" : "2" , "value": "bar"}, {"key" : "3" , "value": "foo"} ]

Thanks for your suggestions.

Be aware that order of array items is a different issue than order of keys in JSON object. See:

How to sort objects in an array inside a json or jsonb value by a property of the objects?

For your first example (implying integer sort), simply casting to jsonb would do the job. It happens to sort keys the way you want by default:
SELECT jsonitem::jsonb FROM jsontable;

db<>fiddle here
Or use jsonb instead of json to begin with.
However, it's not strictly integer sort. Keys are strings. Shorter keys sort before longer keys, which works as desired with only digits. But not necessarily with anything else. Like negative numbers:
2, 3, -1

Then again, your solution does string sort according to the default collation, i.e. breaks integer sort for numbers not comprised of the same number of digits:
111, 22, 3

You didn't specify exactly. This would do integer sort properly:
select id, json_object_agg(key, value) AS jsonitem
from  (
   select id, key, value
   from   tbl
   ORDER  BY id, key::int  -- !!!
   ) tbl
group  by id;

Of course, the cast to integer raises an exception for any string that's not a legal integer representation.
Sorting in a subquery once as this is typically much faster than the per-item sort you get from an added ORDER BY in an aggregate function. This works, because, quoting the manual:

Alternatively, supplying the input values from a sorted subquery will usually work.

See:

Is it REALLY possible that the order will not be guaranteed for this particular redundant derived table?
Customize jsonb key sort order involving arrays

Your second example is a different beast, trying to sort an array with JSON objects by the (single!?) nested key. You could reorder arrays like this:
SELECT j.id, arr.js_sorted
FROM   jsonarr j
CROSS  JOIN LATERAL (
   SELECT json_agg(value) AS js_sorted
   FROM (
      SELECT *
      FROM   json_array_elements(j.jsonitem)
      ORDER  BY (value->>'key')::int  -- true integer sort!
      ) sub
   ) arr;

It really depends on where your values come from, what they can be exactly, and how you want to sort exactly.


1-to-exactly-n in SQL SQL has well understood ways to do one-to-one, one-to-many, and many-to-many relationships. But what about one-to-n, where n is a known natural number that isn't expected to change. (For example, let's say every weekly pay period statement is expected to have seven daily entries.)

Is there any way to maintain a constraint like this at the database level?

The best I can think of would be to have n foreign keys on the one, so in our example the weekly statement table would have columns for day_0, day_1, day_2, etc, which seems uncommon and possibly not 1NF.

This question is inspired by a recent meme of "making impossible states impossible" in the Elm programming language community.

Another level of indirection to the rescue!

Add a Week table, containing the date of the first day of the week and whatever else seems sensible (week number, Year, etc), with one row per week. The Paycheck table has an FK pointing to the Week table.

Add a Day table. This has one row for each day, and contains the Date and an FK to the relevant Week table. It might also have indicators that this is a weekend or national holiday, and so different pay-rates might apply.

Add a table PaycheckDay. This has FK's for the Paycheck row and the appropriate Day row. These two FK's should also be a unique non-clustered index. Pay details for each day are stored on this row. For extra security there could also be constraint that the Day belongs to the same Week that the Paycheck does (also this might be enforced by a Trigger rather than a constraint depending on physical database type).


How to prove the lack of implicit order in a database? Recently I was explaining to colleagues the importance of having a column by which to sort data in a database table if it is necessary to do so, for example for chronologically-ordered data. This proved somewhat difficult because they could simply re-run their query seemingly endlessly and it would always return the same set of rows in the same order.

I have noticed this before and all I could really do is insist that they trust me and not simply assume that a database table will behave like a traditional CSV or Excel file.

For example, executing the (PostgreSQL) query

create table mytable (
    id INTEGER PRIMARY KEY,
    data TEXT
);
INSERT INTO mytable VALUES
    (0, 'a'),
    (1, 'b'),
    (2, 'c'),
    (3, 'd'),
    (4, 'e'),
    (5, 'f'),
    (6, 'g'),
    (7, 'h'),
    (8, 'i'),
    (9, 'j');

will create a table with a clear conceptual order. Selecting that same data in the simplest way would be:

SELECT * FROM mytable;

Always gives me the following results:

 id | data
----+------
  0 | a
  1 | b
  2 | c
  3 | d
  4 | e
  5 | f
  6 | g
  7 | h
  8 | i
  9 | j
(10 rows)

I can do this over and over again and it will always return to me the same data in the same order. However I know that this implicit order can be broken, I have seen it before, particularly in large datasets, where some random value will get apparently thrown up into the "wrong" place when selected. But it has occurred to me that I don't know how this happens or how to reproduce it. I find it difficult to get results on Google because the search query tends to just return general help on sorting result sets.

So, my questions are essentially these:

How can I demonstrably and concretely prove that the return order of rows from a query without an ORDER BY statement is not reliable, preferably by causing and showing a breakdown of the implicit order even when the table in question is not updated or edited?
Does it make any difference at all if the data is only inserted once en masse and then never updated again?

I would prefer a postgres-based answer since that is the one I am most familiar with but I'm more interested in the theory itself.

I see three ways to try to convince them:

Let them try the same query but with bigger table (more number of rows) or when the table is being updated between executions. Or new rows are inserted and some old ones are deleted. Or an index is added or removed between executions. Or the table is vacuumed (in Postgres). Or indexes are rebuilt (in SQL Server). Or the table is changed from clustered to a heap. Or the database service is restarted.

You can suggest that they prove that different executions will return the same order. Can they prove it? Can they provide a series of tests that proves that any query will give the result in the same order, no matter how many times it is executed?

Provide the documentation of various DBMS in that matter. For example:

PostgreSQL:

Sorting Rows
After a query has produced an output table (after the select list has been processed) it can optionally be sorted. If sorting is not chosen, the rows will be returned in an unspecified order. The actual order in that case will depend on the scan and join plan types and the order on disk, but it must not be relied on. A particular output ordering can only be guaranteed if the sort step is explicitly chosen.

SQL Server:

SELECT - ORDER BY Clause (Transact-SQL)
Sorts data returned by a query in SQL Server. Use this clause to:
Order the result set of a query by the specified column list and, optionally, limit the rows returned to a specified range. The order in which rows are returned in a result set are not guaranteed unless an ORDER BY clause is specified.

Oracle:

order_by_clause
Use the ORDER BY clause to order rows returned by the statement. Without an order_by_clause, no guarantee exists that the same query executed more than once will retrieve rows in the same order.


Oracle to PostgreSQL conversion In oracle: employees table contain more than 10 million data. For update I'm using rownum for faster update.

CREATE OR REPLACE PROCEDURE emp_up AS
begin
  loop
      update employees set commission_pct=0 where commission_pct is null and rownum <=10000;
      exit when sql%notfound;
      commit;
  end loop;
end;

In postgresql: postgresql does not support rownum. Is there any way to update employees table in postgres with the similar way in oracle?

In PostgreSQL a function is always be executed in one transaction. It does not matter if you loop over batches of 10.000 rows or just UPDATE the whole table.

Thanks to MVCC there should also no need to split the UPDATE in order to allow other processes to do something on the table, as the locks are very lightweight in this scenario.

So, the correct replacement would be:

CREATE OR REPLACE FUNCTION emp_up() AS
$func$
  update employees set commission_pct=0 where commission_pct is null
$func$
LANGUAGE plpgsql;


Find MySQL Patch Level What command do you use to find the patch level of MySQL?  I am currently on 5.1.73.  I need to find out what patches have been applied to this database.

Thanks...

There is no command to execute for this. What you need are the following:

Oracle MySQL Executive Summary (under Supported Versions Affected)
MySQL 5.1 Release Notes

I would be concerned because MySQL 5.1 was last updated Dec 03, 2013 and the Last Critical Patch to MySQL 5.1 was Jan 2014 (See Patch Availability Table under column Patch Availability and Installation Information)

On Oct 17, 2014, I posted the answer to Any known issues upgrading from MySQL 5.1.73 to 5.6.21? following the security patch announcement. MySQL 5.1.73 has had no security patch applied. Only MySQL 5.6 (5.6.21) and 5.5 (5.5.40) have new patches.

What's crazy is that MySQL 5.6.20 was released two months prior to MySQL 5.6.21. Again, no mention of MySQL 5.1.73.

I highly recommend you upgrade to MySQL 5.6.21.


How to denormalize many-to-many relation without nested select? I have the following schema:

house has many students, student has one house
gender has many students, student has one gender
game has many students. student has many games

For instance, consider

create table sex (type text);
insert into sex values ("wizard");
insert into sex values ("witch");

create table house (nature text);
insert into house values ("Gryffindor");
insert into house values ("Hufflepuff");
insert into house values ("Ravenclaw");
insert into house values ("Slytherin");

create table students (
  sid integer primary key autoincrement,
  name text,
  fk_type integer,
  fk_house integer,
  constraint valid_sex foreign key (fk_type) references sex (ROWID),
  constraint valid_grade foreign key (fk_house) references house (ROWID)
);
insert into students (name, fk_house, fk_type) values ("Harry", 1, 1);
insert into students (name, fk_house, fk_type) values ("Hermione", 1, 2);
insert into students (name, fk_house, fk_type) values ("Nymphadora", 2, 2);
insert into students (name, fk_house, fk_type) values ("Cedric", 2, 1);
insert into students (name, fk_house, fk_type) values ("Bridget", 2, 2);
insert into students (name, fk_house, fk_type) values ("Lupin", 2, 1);
insert into students (name, fk_house, fk_type) values ("Salazar", 4, 2);
insert into students (name, fk_house, fk_type) values ("Yurika", 4, 2);

create table games (game text);
insert into games values ("Quidditch");
insert into games values ("Aingingien");

create table likes (
  fk_student integer,
  fk_game integer,
  constraint strictM2M UNIQUE (fk_game, fk_student),
  constraint valid_student foreign key (fk_student) references students (sid),
  constraint valid_game foreign key (fk_game) references games (ROWID)
);

insert into likes values (1, 1);
insert into likes values (2, 1);
insert into likes values (2, 2);
insert into likes values (3, 1);
insert into likes values (4, 1);
insert into likes values (5, 1);
insert into likes values (5, 2);
insert into likes values (6, 1);
insert into likes values (6, 2);
insert into likes values (7, 1);
insert into likes values (8, 1);
insert into likes values (8, 2);

Now, I want to create a view that shows student roll number, name, gender, house, and games liked. I wrote a join query

select
  s.sid, s.name, xy.type, h.nature, l.fk_game
from
  students s
  inner join sex xy on s.fk_type = xy.ROWID
  inner join house h on s.fk_house = h.ROWID
  inner join likes l on l.fk_student = s.sid
order by
  l.fk_game;

But I want to see game's real names not ROWIDs, of course I can replace l.fk_game by (select game from games where ROWID = l.fk_game) but is it possible to get the same result without nested select?

Nothing forbids you from adding another join, to games. Then you can add the games.game column in the SELECT list:

select
  s.sid, s.name, xy.type, h.nature, g.game
from
  students s
  inner join sex xy on s.fk_type = xy.ROWID
  inner join house h on s.fk_house = h.ROWID
  inner join likes l on l.fk_student = s.sid
  left join games g on g.ROWID = l.fk_game
order by
  s.sid,
  l.fk_game;


SQL SERVER 2014 Standard - High Availability Listener solution The company has SQL Server 2014 Standard licenses and we're standing the servers up in a VMware environment.

My initial plan is to create 2 instances on each DB server and run Instance1 on SQL1 as primary, Instance2 on SQL2 as primary with failover vice versa.

Questions:

Without the listener available only through Enterprise addition, has anyone used a load balancer to repoint the DB connection when you failover for maintenance?
Is there a better method for failing over with Standard Edition?

The only other solution that comes to mind is DNS CNames/ALIAS and repointing to a different IP address, but that seems inherently flawed.

With SQL Server 2014 Standard Edition you can create a two-node failover infrastructure. For that, you need to have in each node at least Windows Server 2008 R2 SP1 Standard, because you need to first generate a failover cluster with Windows Server.

If you want a multi-instance failover cluster, then you need to generate two SQL Server 2014 installations, since the product works in an Active-Passive mode. I mean, you will need to install the first instance, SQL1 and then you will proceed with the second instance, SQL2. You will need to configure each instance so SQL1 runs in node 1 and SQL2 runs in node 2.

Also, it's very important and recommended, that you have at least a SAN or some device that can provide you with the shared storage schema, since previous to the SQL Server failover cluster installation you need to validate the Windows Server failover cluster support to the SQL Server one.


ORA-01180 when restoring the database I get the following error message when trying to restore the database from backup using RMAN.

rman target /

RMAN> set dbid=130474031
RMAN> configure device type 'sbt_tape' clear;
RMAN> startup nomount;
RMAN> restore controlfile from autobackup;
RMAN> sql 'alter database mount';
RMAN> restore database;

Starting restore at 22-DEC-15
using channel ORA_DISK_1

creating datafile fno=1 name=/opt/oracle/oradata/prod/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/22/2015 12:47:01
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'
RMAN>

EDIT

RMAN> restore database;

Starting restore at 22-DEC-15
using channel ORA_DISK_1

creating datafile fno=1 name=/opt/oracle/oradata/prod/system01.dbf
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 12/22/2015 14:22:01
ORA-01180: can not create datafile 1
ORA-01110: data file 1: '/opt/oracle/oradata/prod/system01.dbf'

RMAN> exit

Recovery Manager complete.
[oracle@DR-Corebank ~]$ id
uid=501(oracle) gid=501(oinstall) groups=501(oinstall),502(dba)
[oracle@DR-Corebank ~]$ cd /opt/oracle/oradata/prod/
[oracle@DR-Corebank prod]$ touch aaa
[oracle@DR-Corebank prod]$ ls -ltr
total 875772
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control03.ctl
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control02.ctl
-rw-r----- 1 oracle oinstall 298631168 Dec 22 14:22 control01.ctl
-rw-r--r-- 1 oracle oinstall         0 Dec 22 14:22 aaa
[oracle@DR-Corebank prod]$ pwd
/opt/oracle/oradata/prod
[oracle@DR-Corebank prod]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p3     1.1T  163G  850G  17% /
/dev/cciss/c0d0p1     244M   18M  213M   8% /boot
tmpfs                 7.9G     0  7.9G   0% /dev/shm
/dev/mapper/mpath0    493G  248M  467G   1% /opt/oracle2/u002

Community Wiki answer from a self-answer originally edited into the question (and based on a comment by Balazs Papp).

I forgot an incarnation:

RMAN> list incarnation;

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       PROD     130474031        PARENT  1          10-MAY-10
2       2       PROD     130474031        PARENT  195833299  21-OCT-10
3       3       PROD     130474031        PARENT  3655020271 06-OCT-12
4       4       PROD     130474031        PARENT  3655121615 08-OCT-12
5       5       PROD     130474031        CURRENT 5588371557 19-OCT-14

RMAN> reset database to incarnation 4;

database reset to incarnation 4

RMAN> restore database;

Starting restore at 22-DEC-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=10993 devtype=DISK

creating datafile fno=3 name=/opt/oracle/oradata/prod/sysaux01.dbf
creating datafile fno=4 name=/opt/oracle/oradata/prod/users01.dbf

The problem is now solved.


Could an update on all statistics separately cause bad performance? We’ve been utilizing Ola Hallengren's maintenance scripts for our index optimization for years on our OLTP databases.  (we are on sql server 2008 R2)
2 weeks ago though one of the DBA’s here has created a maintenance job to update statistics separately and this runs weekly before the Index optimization job.
It seems that after this was created we are getting performance issues on a handful of stored procedures.  I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?
The DBA here seemed to think this was needed but I feel that we started having badly performing stored procedures the morning after the maintenance jobs are running and I constantly needed to recompile the same stored procedures at least 1 or 2 times per day for the following few days then the issue just stops.
My theory is pointing to the statistics updates that occurred before but I am having trouble finding the reason why.  Don't statistics get updated after a certain amount of changes or during an index rebuild anyway?  Why would recalculating them separately cause an issue?

Would anyone be able to offer some clarity as to why this would happen to the same stored procedures every time statistics get updated?

This is the command being used for the statistics job:

EXECUTE dbo.IndexOptimize
    @Databases = 'DB_PRO',
    @FragmentationLow = NULL,
    @FragmentationMedium = NULL,
    @FragmentationHigh = NULL,
    @UpdateStatistics = 'ALL',
    @OnlyModifiedStatistics = 'Y' ,
    @TimeLimit = 3600,
    @LogToTable = N'Y';

Then the Index Maintenance job runs with the following :

    sqlcmd -E -S $(ESCAPE_SQUOTE(SRVR)) -d master -Q "EXECUTE [dbo].[IndexOptimize]
    @Databases = 'DB_PRO', @TimeLimit = 10800, @LogToTable = 'Y'" -b

Question: I am not too familiar with stat maintenance, so I am wondering, is it really necessary to run a weekly job separately to update all statistics?

Yes, you need to maintain your statistics if you have large tables. Read here how Auto update statistics works:

  Auto Update Statistics Option Statistics are checked before query
  compilation or before executing a cached query plan. Statistics are
  considered out-of-date when:

  There was a data change on an empty table. The number of rows in the
  table was 500 or less at the time of statistics creation and the
  column modification counter of the leading column of the statistics
  object has changed by more than 500 since then.

  The table had more than 500 rows when the statistics were gathered,
  and the column modification counter of the leading column of the
  statistics object has changed by more than 500 + 20% of the number of
  rows in the table when the statistics were gathered.

  A table in TempDB with less than 6 rows had at least 6 row
  modifications.

Question:  Don't statistics get updated after a certain amount of changes or during an index rebuild anyway?

  Rebuilding an index, for example by using ALTER INDEX … REBUILD will
  also update index statistics with the equivalent of using WITH
  FULLSCAN unless the table is partitioned, in which case the statistics
  are only sampled* (applies to SQL Server 2012 and later). Rebuilding
  indexes does not update column statistics.

  Reorganizing an index, for

    example using ALTER INDEX … REORGANIZE does not update any statistics.

Question: Why would recalculating them separately cause an issue?

Might be because your sample rate is to low and the statistics is not accurate. Use FULLSCAN to see if that helps. It might also be that the Cardinality Estimation misinterprets the statistics (can happen in SQL Server 2014), if this is the case try trace flag 9481 to disable cardinal estimator in SQL Server 2014.

The StatisticsSample parameter of Ola Hallengrens script give you this:

  Indicate, as a percentage, how much of a table is gathered when
  updating statistics. A value of 100 is equivalent to a full scan. If
  no value is specified, then SQL Server automatically computes the
  required sample.


uncommitted xmin during vacuum, what to do? Running vacuum on two specific tables in my postgresql 10 database results in the following error:

VACUUM (FULL|ANALYZE|FREEZE|) table;
ERROR:  uncommitted xmin 359569171 from before xid cutoff 435784199 needs to be frozen

The tables are otherwise functioning, I can query/insert/update them (I can query the whole tables without any error). I can also REINDEX them. However, this error stops the nightly VACUUM ANALYZE on the whole database prematurely. The xmin value value seems to be constant, the cutoff value increases monotonously.

How can I fix this? From the very few instances on the web with a similar error, (e.g. https://stackoverflow.com/questions/50354521/postgres-corruption-error-duplicate-rows-with-the-same-primary-key), it seems there should be some bad rows, but I cannot seem to find them.

A somewhat elegant solution that needs no downtime

I can find the specific rows, and update some insignificant column (comment in my case) in them to its current value:

SELECT id, comment FROM table WHERE xmin=359569171;

and then with the results, do updates for each row.

UPDATE table SET comment=(SELECT comment FROM table WHERE id=<ID>) WHERE id=<ID>;

As there were only three rows, I did not bother to create a formalised script.

Old brute force solution

This is what I could come up with first, and it is not the most elegant way to get rid of the message. I simply dump the table, delete all rows, and then restore it.
This is the shell script I created, someone may find it useful.

#!/bin/bash

DB=database_name

for TABLE
do
    SAVEFILE=/tmp/$TABLE.sql.gz
    echo "dumping $TABLE to $SAVEFILE"
    su - postgres -c pg_dump $DB --table=$TABLE --data-only | pigz -1 > $SAVEFILE"
    echo "disabling triggers for $TABLE"
    su - postgres -c "psql  $DB -c 'ALTER TABLE $TABLE DISABLE TRIGGER ALL'"
    echo "deleting the contents of $TABLE"
    su - postgres -c "psql $DB -c 'DELETE FROM $TABLE'"
    echo "restoring contents of $TABLE"
    zcat $SAVEFILE | su - postgres -c "psql $DB"
    echo "re-enabling triggers for $TABLE"
    su - postgres -c "psql $DB -c 'ALTER TABLE $TABLE ENABLE TRIGGER ALL'"
done

The drawback of this approach is that the application must be stopped during this operation to avoid inconsistencies, as triggers must be stopped for the table. This causes about half an hour of downtime in my case.

I tried this approach on a copy of the database and it works, but I am still searching for a better solution that may be done without any downtime.


Closed loops in database model, does it affect the performance? I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the same results and might lead to inconsistent records in the query result.

Is that true? Also I'm wondering if it affects the query plans and SQL Server performance.

What do you think? What are the other troubles it might cause?

I have heard that having loops in the database relational model should be avoided.

To me, a table structure (>1 table) where the foreign keys form circular dependencies (traversing foreign keys starting from one table, it's possible to end up back at the same table) is a red flag indicating a likely problem with the table/system design.

I'm not going to say this is never correct, but I have yet to see an example where this kind of thing was used correctly, or could not be reasonably redesigned to avoid the use of a circular dependency.

(It's the same type of issue in other areas too, as I pointed out here.)

  Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.

Those tables are related, yes, but the relationships probably should be of the form:

Questionnaires 1..* QuestionaireResultsets

QuestionaireResultsets *..* Answers, with a join table in between

These changes result in a structure that does not contain a circular dependency.

Remember that foreign key relationships have a direction. This is very important. Tables can be related such that a schema diagram looks like a circle; it's only a problem when all the segments of the circle point in the same direction.

  I'm wondering if it affects the query plans and SQL Server performance.

If you ask SQL Server to perform a task using recursion instead of using sets or iteration, it's possible that performance could suffer; however, the reverse may be true as well. I'm not sure we can have a meaningful discussion about performance in this case without a specific example.


How to improve this query performance by indexing properly? I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek?

DECLARE @now DateTime;
DECLARE @currentweekstart DateTime;
DECLARE @currentweekend DateTime;
SET @now = getDate();
SET @currentweekstart = DATEADD(
                 DD,
                 -(DATEPART(DW, @now - 7) - 1),
                 @now - 7);
SET @currentweekend = DATEADD(
               DD,
               7 - (DATEPART(DW, @now - 7)),
               @now - 7);

  SELECT  CHINFO.CHILDID,COUNT(*) AS CURRENTWEEKPOTTYBREAKS
          FROM  BKA.CHILDINFORMATION CHINFO
  JOIN  BKA.CHILDEVENTS CHE
         ON CHE.CHILDID = CHINFO.CHILDID
  WHERE TYPE ='POTTYBREAK'
          AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend
           GROUP BY CHINFO.CHILDID

Primary Keys:

  TableName --> ColumnName

  CHILDINFORMATION-->ChildId

  CHILDEVENTS-->EventId

Foreign Keys:

  CHILDEVENTS-->Here ChildId is the foreign key reference of
  CHILDINFORMATION table

NonClustered Indexes:

  CHILDINFORMATION-->No nonclustered indexes

  ChildEvents-->IX_ChildEvents_Type-->Type include EventId

  ChildEvents-->IX_ChildEvents_Type_AddDate-->Type,AddDate

  ChildEvents-->IX_ChildEvents_Adddate_Type-->Adddate,Type include
  ChildId,EventId

Since you have a foreign key from CHILDEVENTS to CHILDINFORMATION and you don't select any column from the child information table, you can skip that table. One less join will certainly not hurt:

SELECT CHE.CHILDID, COUNT(*) AS CURRENTWEEKPOTTYBREAKS
FROM  BKA.CHILDEVENTS AS CHE
WHERE CHE.TYPE ='POTTYBREAK'
  AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend
GROUP BY CHE.CHILDID ;

As for indexing, the existing index on (Adddate,Type) INCLUDE (ChildId, EventId) does not look very good for this query. I would try replacing it (in fact removing both those AddDate-Type indexes) with an index on:

(Type, AddDate) INCLUDE (ChildId)


Find the first gap in an aggregation of integers in SQL Server Let's say I have a table called dbo.GroupAssignment.

GroupID | Rank
------------------
1    1
1    2
1    3
2    1
2    3
2    4
3    2
3    4
3    5

The PK is GroupID, Rank. Normally, the ranks within a group are a contiguous sequence of integers starting from 1. But it's possible for a GroupAssignment to get removed, leaving a gap. When a new assignment is made for the group, I want to fill the first gap available.

So, how could I calculate this in SQL Server?

You can use ROW_NUMBER to generate what the rank should be

;WITH ranks AS
(
    SELECT
       GroupID, Rank,
       ROW_NUMBER() OVER (PARTITION BY GroupID ORDER BY Rank) AS newRank
    FROM
       dbo.GroupAssignment
)
SELECT
   GroupID, MIN(newRank), MIN(Rank)
FROM
   ranks
WHERE
   newRank < Rank -- don't need <> here
GROUP BY
   GroupID;

Personally, I wouldn't re-use this Rank if one of these applies

It can be generated at runtime with ROW_NUMBER etc
It is some kind of ID column: the old value will live in history tables etc and will be ambiguous
It is simply an arbitrary internal key

Also, do you want RANK or DENSE_RANK to deal with "joint 2nd" or "equal values" instead of ROW_NUMBER


PostgreSQL dump and restore I can't seem to dump/restore on Windows with my database. Lines used and error messages:

pg_dump -h localhost -U postgres --format=c -O -d ue > latest.dump
pg_restore -h localhost -U postgres -d ue latest.dump

Getting this error,

  pg_restore: [archiver] input file does not appear to be a valid archive

With this,

pg_dump -h localhost -U postgres -O -d ue > latest.dump
psql -h localhost -U postgres -d ue -f latest.dump

I get this error,

  psql:latest.dump:1: ERROR:  invalid byte sequence for encoding "UTF8": 0xff

I'm completely stumped. Most of the search results relate to not using the correct pg_restore/psql with the file type exported by pg_dump, but as you can see above, I've accounted for that.

latest.dump seems to be filling up correctly, at least in the plain format it exports SQL that I can read and is the correct export of the database.

Apparently the problem was the > operator. Changing that to -f to specify the file for the dump, resolved the problem (using the plain format).

I was writing a Powershell script which may have been the underlying cause. The commands were accepted, but there could have been a corruption issue there.


MySQL - Pass input parameter into Cursor query Is it possible to pass input parameter into Cursor

SELECT statement WHERE clause?

For some reason I think it isn't working.

I'm trying to pass _TAG and _ITEM_NAME  into where clause.

    DECLARE cursor_test cursor
for SELECT itemid
FROM items
WHERE  key_ LIKE "sometext_@_TAG_sometext_@_ITEM_NAME"
AND STATUS = '0';

Here is the the Stored procedure:

    DELIMITER //
    CREATE PROCEDURE getSomething(IN _HOSTNAME VARCHAR(20),
                                  _TAG VARCHAR(20),
                                  _ITEM_NAME VARCHAR(50))
    BEGIN
    declare FINISHED BOOL default false;

    DECLARE cursor_test cursor for SELECT itemid
FROM items
WHERE hostid = @_HOSTID AND key_
LIKE "sometext_@_TAG_sometext_@_ITEM_NAME"
AND STATUS = '0';

    DECLARE CONTINUE HANDLER for not found set FINISHED := true;

    SET @HOSTNAME = _HOSTNAME;

    PREPARE STMT1 FROM

    "SELECT hostid INTO @_HOSTID FROM hosts  WHERE NAME = ?";

    EXECUTE STMT1 USING @HOSTNAME;
    DEALLOCATE PREPARE STMT1;

    open cursor_test;
       SET @TOTAL_VALUE := 0;
       loop_itemid: loop
            fetch cursor_test into _ITEMID;
            SELECT _ITEMID;
            if FINISHED then
                leave loop_itemid;
            end if;
            SET @TOTAL_VALUE := @TOTAL_VALUE +
             (SELECT value from history_uint WHERE itemid = _ITEMID
               ORDER BY clock DESC LIMIT 1);

       end loop loop_itemid;
       SELECT @TOTAL_VALUE;
    close cursor_test;

    END //

Thanks to Akina's comment using CONCAT in the where clause worked.

WHERE key_ LIKE CONCAT('sometext_', _TAG, '_sometext_', _ITEM_NAME)


How to update multiple columns of multiple rows in one SQL statement In my SQL Server database I want to update columns of multiple rows. I can do it for one row only:

UPDATE theTable
SET theColumn = case
WHEN id = 1 then 'a'
WHEN id = 2 then 'b'
WHEN id = 3 then 'c'
WHERE id in (1, 2, 3)

How can I update more columns (theColumn2, theColumn3...) in the same query?

You could supply the new values as a table (with the help of the VALUES row constructor), so that you could join it with the target table and use the join in the UPDATE statement, like this:

UPDATE
  tgt
SET
  Column1 = src.Column1,
  Column2 = src.Column2,
  Column3 = src.Column3,
  ...
FROM
  dbo.TargetTable AS tgt
  INNER JOIN
  (
    VALUES
    (1, 'a', 'k', 'x', ...),
    (2, 'b', 'l', 'y', ...),
    (3, 'c', 'm', 'z', ...)
  ) AS src (ID, Column1, Column2, Column3, ...)
    ON tgt.ID = src.ID
;


Need to take backup from other server on network using AutoMySQLBackup We have two servers namely serverA and serverB. I need to take mysql database backup of serverA staying at serverB. The tool I need to use is "AutoMySQLBackup".

I got the link for downloading it:

https://sourceforge.net/projects/automysqlbackup/

Extracted the tar and changed the config file in /etc/automysqlbackup/automysqlbackup.conf as per my need.

As I run automysqlbackup, I get the backups on daily basis though there are warnings:

###### WARNING ######
Errors reported during AutoMySQLBackup execution.. Backup failed
Error log below..
mysql: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqlshow: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: [Warning] Using a password on the command line interface can be insecure.
mysqldump: Got error: 1142: SELECT, LOCK TABLES command denied to user 'root'@'localhost' for table 'accounts' when using LOCK TABLES
mysqldump: [Warning] Using a password on the command line interface can be insecure.

But, I need to take the backup of remote server and don't find the way to do that using this tool. I tried using :

ssh serverA@ip-address | automysqlbackup

but nothing is happening.

Note: I can't reply to answers so i am mentioning it here. I need to get the backup directly from serverA to serverB with the help of automysqlbackup like the way mysqldump or someother tool does.

mysqldump --host 192.168.1.15 -P 3306 -u dev -pmjQ9Y mydb > mydb.sql

Is there way to pipe automysqldump with ssh?
No scp,rsync.
I have access to certain database in serverA while accessing from serverB.

If anyone of you have used this tool, then please help me.

1: Are you able to do the backup? as i see an error on that message i got confused about it.

2: Did you set up a CRON to run the script daily? First thing that come to mind was to run a ssh copy of the backup to server B in the CRONjob (using SCP command)


connecting to Postgres running inside docker container 'posgres' does not exist I want to connect to Postgres (running in a container) using psql

What I've done:

docker pull postgres
docker run -p5432:5432 --name mydb -e POSTGRES_PASSWORD=pass123 -d postgres

Trying to connect via PSQL command
psql -h localhost -U postgres

But I keep getting psql: FATAL:  role "docker" does not exist
(I tried different variations of the command like with -d postgres or with -W , even tried with PgAdmin ...still same error)

However, when I jump into the container docker exec -it mydb bash then psql -U postgres I can see the following:

postgres=# \du
                                   List of roles
 Role name |                         Attributes                         | Member of
-----------+------------------------------------------------------------+-----------
 postgres  | Superuser, Create role, Create DB, Replication, Bypass RLS | {}

postgres=# \c postgres
You are now connected to database "postgres" as user "postgres".
postgres=# \l
                                 List of databases
   Name    |  Owner   | Encoding |  Collate   |   Ctype    |   Access privileges
-----------+----------+----------+------------+------------+-----------------------
 postgres  | postgres | UTF8     | en_US.utf8 | en_US.utf8 |
 template0 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
 template1 | postgres | UTF8     | en_US.utf8 | en_US.utf8 | =c/postgres          +
           |          |          |            |            | postgres=CTc/postgres
(3 rows)

Found the issue:

I had PSQL installed locally (via brew) which was showing the error message.

Uninstalled it. Then used PgAdmin to connect to the container. And it's working fine now.


MySQL Replication query - Primary key need I have few doubts regarding MySQL replication (Master-Slave).
Is it mandatory for a table to have primary query for replication to function properly?

Referring this Percona link for the above question as it mentions that If there is no primary key or unique key defined then it’s even worse because INSERT may be re-executed and you will get multiple rows with the same data – which again means you’ve got inconsistent data with the master

But with InnoDB as the storage engine, if a table does not have primary or a unique query defined, the engine itself creates hidden clustered index on a synthetic column containing row ID values as per Jeremy Cole's blog

So, even in the case primary or unique not present, the replication should not have any impact it in itself creates a clustered index which should ensure the replication is smooth, correct? I'm not sure on this part.

Would be great if someone can throw some light on the need of Primary key in Master-Slave Replication setup.

Your assumption is incorrect.

The hidden clustered index does nothing to help, here, because the server can't use that index to find rows in a way that replication needs.

This causes some problems when the master is logging in STATEMENT mode, different problems in ROW mode, and all of the problems, combined, in MIXED mode.  Here's the tl;dr on the biggest pitfall:

Every replication event against a table without a primary or unique key whose value is accessible through the SQL interface has the potential to require a full table scan for each row replicated.

The inaccessibility of the hidden clustered index to the SQL layer excludes it from use... and it wouldn't help, even if it were not as hidden, since its value is not deterministic from server to server, and so its value is never written to the binlog.

Simply enough... you should not create tables without accessible primary keys. It's bad design.

I would argue that a valid table cannot have two identical rows -- every relation has at least one candidate key, by definition, and candidate keys must be unique, also by definition. Choose one and make it primary, or create a surrogate (auto-increment) so that it's visible.  If you have unique constraints, you need to define them in the schemata, not just impose them in code.


Configure Login Audit - Skip one user- I have configured the login audit to save only the failed ones, but I need to omit a specific login in said audit, is it possible?

I am using sqlserver 2008-r2

Thanks.

You cannot do it there.  But you can set up a server audit and exclude specific login.  I have not tested the code in 2008 R2.  Change you login value with LoginName. You can also use server_principal_id instead of server_principal_name.

USE [master]

GO

CREATE SERVER AUDIT [FailedLogin]
TO FILE
(   FILEPATH = N'C:\'
    ,MAXSIZE = 64 MB
    ,MAX_FILES = 50
    ,RESERVE_DISK_SPACE = OFF
)
WITH
(   QUEUE_DELAY = 1000
    ,ON_FAILURE = CONTINUE
)
WHERE server_principal_name <> 'LoginName'

GO

USE [master]

GO

CREATE SERVER AUDIT SPECIFICATION [ServerAuditSpecificationFailedLogin]
FOR SERVER AUDIT [FailedLogin]
ADD (FAILED_LOGIN_GROUP)

GO


How to view who gets kicked from my SQL Server Script? I have a maintenance job in SQL that was failing because of an application that was holding a lock for a specific user. I'm now running a script before the step that runs the maintenance job to kick the user. Now the maintenance job runs without issues. I'd like to get output from the step as it runs so I can see in the job activity monitor what gets kicked. How to make the output more verbose?

--Create a cursor
declare mycursor cursor
for

-- Identify any server process id used by your user for the database. Update fields below as required. The username or database name can be removed if you wish to disconnect all users or a user from all databases.
select spid, Loginame, hostname
from master..sysProcesses
where HostName='johnpc' or loginame='browser' and dbid=db_id('approd')

open mycursor

declare @spid int, @loginame varchar(255), @cmd varchar(255), @hostname varchar(255)

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)
    print @loginame
    Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
end

close mycursor
deallocate mycursor
go

In the Job Step Properties, on thew Advanced page, enable the option to include step output in job history:

Then include a PRINT statement in your cursor loop to output the details of each KILL.

Example:

declare @message varchar(max), @spid int, @loginame varchar(255), @cmd varchar(255), @hostname varchar(255)

-- For each row in the cursor, kill the process by spid
Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
While (@@FETCH_STATUS <> -1)
begin
    select @cmd = 'kill ' + cast(@spid as varchar(5))
    exec(@cmd)

    set @message = 'SPID: ' + CAST(@spid AS VARCHAR(5)) + ' | Login: ' + @loginame + ' | Host: ' + @hostname;

    PRINT @message;

    Fetch NEXT FROM MYCursor INTO @spid, @loginame, @hostname
end


Identify NON-ANSI joins in SQL Server 2008 R2 on a Database that's in 2000 (80) compatibility mode We have a very old database running on SQL Server 2008 R2. The database is running in 2000 (80) compatibility mode and has oodles of NON-ANSI joins in stored procedures, views and functions.

We need to update this database to at least 2005 compatibility mode to enable the use of CTE SQL, ROW_NUMBER OVER (PARTITION BY) and so on.

When we first upgraded to SQL Server 2008 R2 we obviously ran Upgrade Advisor 2008 R2 first which highlighted all SQL that had non-ansi joins, at the time we just ignored them and put the database in SQL Server 2000 (80) compatibility mode.

Our problem now is that SQL Server 2008 R2 Upgrade advisor will no longer work for us as the version isn’t supported (as we're already using that version), and 2000 (80) databases aren’t supported in the 2012 Upgrade advisor either.

My question, short of scripting the whole database and painstakingly going through it checking for *= and =* and *=* etc, is there a tool we can use to detect all procs, views and functions that contain these joins so we can get them re-written.

This may produce some false positives (e.g. you may have *= in a comment), but should be a good start:

SELECT
  obj = QUOTENAME(SCHEMA_NAME(o.[object_id]))
      + '.' + QUOTENAME(o.name),
  o.type_desc
FROM sys.sql_modules AS m
INNER JOIN sys.objects AS o
ON m.[object_id] = o.[object_id]
WHERE m.definition LIKE '%=*%'
OR m.definition LIKE '%*=%';


Oracle SQL Developer Textcursor disappearing In the Oracle SQL Developer on my manjaro Linux, the blinking cursor in the editor area won't show anymore. I deleted everything of local config files (~/.sqldeveloper/) and reloads the program. At first, the cursor is visible again but after i pressed the "excute script (F5)"-Button, it will get lost and there seems no way to fix this.

My setup:

SQL Developer

Version 4.1.5.21
Build MAIN-21.78

Java

java version "1.8.0_112"
Java(TM) SE Runtime Environment (build 1.8.0_112-b15)
Java HotSpot(TM) 64-Bit Server VM (build 25.112-b15, mixed mode)

OS

Linux 4.4.39-1-MANJARO

Adding AddVMOption -Dsun.java2d.opengl=true to ~/.sqldeveloper/<version>/product.conf fixed it for me.