Get Insert/Delete/Update operations with data from the transaction log Can anyone please provide a script using fn_dblog to find Insert/Delete/Update operations with data (like Change Data Capture) that are made to the SQL Server from transaction log.

I can't enable Change Data Capture since I am using SQL Server 2012 Standard Edition.

Change Tracking is not suitable since I need before and after values.

Can anyone please explain the script to find Insert/Delete/Update operations with data (like cdc) that are made to the sql server from transaction log.

Free ones of this would be a program and they are generally paid for. There are no free ones that I know of. Additionally the SQL Server Transaction Log is not documented as it is for internal use only.

  I cant enable cdc since I am using SQL server 2012 standard edition.

Then either change your edition or go without. You can write your own tracking using triggers, but it's going to make your application run extremely slow.

You really need to look into items that do this, Golden Gate, CDC, CT, etc., and go from there. If you can't spend any money then you need to make a business justification case for it and if still nothing, you can write it using triggers. Note that using triggers is still spending money on development... so either way money is being used.


Add a column to a table and then "insert into" is invalid in a stored procedure I have a problem similar to this SO question.

I have three tables that are related with an identity column as primary key. And I need to clone them into a new database with new identity values.

These are the tables where I'm going to copy the data, but the relationship is the same in the source database.

CREATE TABLE [dbo].[Code] (
    [CodeId]            INT            IDENTITY (1, 1) NOT NULL,
    [Serial]            NVARCHAR (20)  NOT NULL,
    [...]
)
CREATE TABLE [dbo].[Aggregation] (
    [AggregationId] INT           NOT NULL,
    [Created]       NVARCHAR (50) NOT NULL,
    CONSTRAINT [PK_AGGREGATIONS] PRIMARY KEY CLUSTERED ([AggregationId] ASC),
    CONSTRAINT [FK_Aggregation_Code]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Code] ([CodeId])
)
CREATE TABLE [dbo].[AggregationChildren] (
    [AggregationChildrenId] INT NOT NULL,
    [AggregationId]         INT NOT NULL,
    [Position]              INT NOT NULL,
    CONSTRAINT [PK_AGGREGATION_CHILDS] PRIMARY KEY CLUSTERED ([AggregationChildrenId] ASC),
    CONSTRAINT [FK_AggregationChildren_Code]
           FOREIGN KEY ([AggregationChildrenId])
            REFERENCES [dbo].[Code] ([CodeId]),
    CONSTRAINT [FK_AggregationChildren_Aggregation]
           FOREIGN KEY ([AggregationId])
            REFERENCES [dbo].[Aggregation] ([AggregationId]) ON DELETE CASCADE
)

There is a master table, Code, and the other tables related to the master by its primary key Code.CodeId.

I'm going to add an auxiliary column, with the value of the primary key in the source database, to make the conversion in tables Aggregation and AggregationChildren.

That process work in SQL Server Management Studio, but when I try to use it in a procedure stored it complains about that the auxiliary column doesn't exist.

The stored procedure is like this pseudo code:

// Alter table to add the auxiliary column
// Insert into with select and inserting into the auxiliary column
// Alter table to drop the auxiliary column

The stored procedure has an error because I'm inserting in a column that doesn't exist.

Do you know if there is another solution to this problem using an auxiliary column?

You cannot add a column and use it in the same batch; in Management Studio you are able to insert "go" between them so it works, in stored procedure you can insert your table within EXEC like this:

exec('insert table..(old_cols, new_col)...')

The table structure is checked if a table exists so the procedure cannot be compiled when you use a new column, but it will not check a dynamic code that compiles on its own and when it will be compiled the column will be already there


Copy SQL Logins and Permissions From One Server to Another I need to duplicate all Logins and their associated database-level permissions from one server to another. I am using SQL Server 2008 R2 for Server A and SQL Server 2012 for Server B I am able to find a few different scripts to transfer Logins from server A to server B. However, I nee to also transfer database-level permissions for each Login.

I've reviewed the following for transferring Logins:

http://www.sqlsoldier.com/wp/sqlserver/transferring-logins-to-a-database-mirror
http://support.microsoft.com/kb/918992

The SQL Soldier solution seems to work fine.

The same databases that reside on server A also reside on server B. how can I automate/script the Login and permissions transfer from one server to another?

I created a series of stored procedures that accomplished a similar goal (with some powershell for flavor) based on this article Claire-Hsu - SQL Logins

I don't have a blog of my own (I know, I know) so I can't share my precise solution with you.  I essentially created three stored procedures.  One for the server logins, one for database and another for database object permissions.

Ah, shared on my one drive.  Link to files  It's the stored procedures you need.  I have them in my DBA database.  I then use Powershell to get a list of logins, then query the stored procedure, save the script to a file and repeat.  You could easily change it to run the script on a different server.


MySQL 5.5.57 No longer supports myisamck *.MYI I use
    myisamchk *.MYI
to optimise all my tables.

However this no longer works with the MYI extension

# myisamchk mytable
Checking MyISAM file: mytable
Data records:   17952   Deleted blocks:       0
- check file-size
- check record delete-chain
- check key delete-chain
- check index reference
- check data record references index: 1

# myisamchk mytable.MYI
myisamchk: error: 140 when opening MyISAM-table 'mytable.MYI'

Still happening in MySQL 8.0.11

Walkarround solution: NOT USE THE MYI EXTENSION

myisamchk mytable


LDAP in SQL Server 2012 In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of the discontinued features of SQL Server 2012 is the AD helper service.

Does anyone know of a way to query the AD from SQL Server 2012?

You should still be able to create a linked server to an LDAP database and query the LDAP database directly via the linked server.

There's information here on setting up the linked server.

To test I setup the linked server based on this post.  Then I ran the following code and got back a recordset with various information in it (where bacon.lab is my AD domain).

SELECT * FROM OpenQuery (
  ADSI,
  'SELECT displayName, telephoneNumber, mail, mobile, facsimileTelephoneNumber
  FROM ''LDAP://bacon.lab''
  WHERE objectClass =  ''User''
  ') AS tblADSI
ORDER BY displayname


Why might my Postgres table be growing? I've got a table that grows under Postgres 9.1. It's a high-throughput table with lots of inserts and deletes, but it should generally remain between 1-2k in terms of number or records. Auto-vacuum is enabled, and we've run manual vacuums. We've checked for locks and there don't appear be any. Running "vacuum verbose;" says that while there are millions of dead records, but "found 0 removable".

What might I have not thought about?

Run this query:

SELECT * FROM pg_stat_activity ORDER BY xact_start NULLS LAST LIMIT 1;

If you get a row back where xact_start is not null and it's anything more than a few seconds old, that's your culprit.  VACUUM cannot remove rows that have transaction_ids greater than or equal to the oldest open transaction in the system (even if it's in a completely different database).  If you terminate that transaction and re-run the VACUUM, you should see more than 0 rows removable.


SQL Server 2012, triggers missing after restore database I am using MS SQL Management Studio 2012 to restore the database from a .bak file.  (That file is from the backup of the database on server)

In my new database, the triggers are missing. Why did I lose the triggers?

A wild guess - are the triggers based on CLR code? If CLR Integration has not been enabled on the machine you are restoring to then the triggers won't work.

CLR Integration can be enabled with the following code.

exec sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
exec sp_configure 'clr enabled', 1;
GO
RECONFIGURE;
GO


After change to 13306, Galera logs error: Slave I/O: error connecting to master 'repuser@:3306' Working with Galera 25.3.23 on RHEL 7.3

Galera works good before. After changing the MySQL server port from 3306 to 13306, one of the node report error, after restart:

Slave I/O: error connecting to master 'repuser@<IP>:3306' - retry-time: 60  maximum-retries: 86400  message: Can't connect to MySQL server on '<IP>' (111 "Connection refused"), Internal MariaDB error code: 2003

The other 2 nodes works fine after the restart.

Googled the web, but don't find the way to specify the port number.

Also, if possible, please share the usage of the "repuser" ID.

You look like you are using classical async master/slave replication on a galera server. Was this intentional? Galera uses a different replication protocol and master/slave off, or especially between nodes, is odd.

Use CHANGE MASTER TO to change the master's port.


Subscription Initialization throws Error "The option "INLINE=ON" is not valid for this function" which is incorrect Scenario
We are upgrading our SQL estate to SQL Server 2019 and have setup what will be the production environment.

From:
SQL2008R2 Standard/Windows Server 2008R2 - Primary Server/Secondary Server (Windows Fail-over Cluster) & Reports Server (Transactional Replication).

To:
SQL2019 Standard/Windows Server 2019 - Primary Server/Secondary Server (Always-On) & Reports Server (Transactional Replication)

Backups from 2008R2 were restored on 2019 and compatibility level set to latest. Always-On setup has gone relatively smooth and initial testing against current application has shown no issues with compatibility.

The issue is purely with setting up Transactional Replication. There are 2 databases that require replication, one publication each with the primary as the publisher and distributor.

The Issue
During the initialization of the subscription for each DB on the reports server it runs fine until it gets to the point of creating functions and produces the errors below.

1st Database:

  Message: The option "INLINE=ON" is not valid for this function. Check the documentation for the constructs supported with INLINE option in a function.
  Command Text: CREATE FUNCTION [dbo].[f_clienttels - Mirror Copy ce2d3663eb494f3589bd5000dad1bf1f](@ClientID [int])
  RETURNS varchar WITH INLINE = ON, EXECUTE AS CALLER
  AS
  BEGIN ......

2nd Database:

  Message: An invalid option was specified for the statement "CREATE/ALTER FUNCTION".
  Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
  Incorrect syntax near ')'.
  Command Text: CREATE FUNCTION [dbo].[GetGroupAndDescendantGroupsSelective - Mirror Copy 46f329d5eed444428f45b052f07c7ea8](@GroupId [int])
  RETURNS TABLE WITH INLINE = ON
  AS
  RETURN (
          WITH GroupsCTE AS ( ........

They are both different errors but i believe both are to do with the "INLINE=ON" option, this option is not present in either of those functions, none of our functions use the inline option explicitly, if you remove those articles from the subscription it just gives the same error on the next function (CTE error if the function starts with a CTE and the INLINE=ON error if it does not).

So it appears replication is inserting "WITH INLINE = ON" to functions prior to replicating and then erroring on the addition it has made.

I have patched all instances to the latest CU4 update 15.0.4033.1, tested those functions on both servers (which work), validated all functions/procs within the database and all are fine. In the current 2008R2 environment I had to recreate the publication a couple months back and didn't get these errors. As a work around for now I'm manually creating functions at the subscriber and removing all function articles from the publication.

Any help with a resolution to this would be greatly appreciated, the only reference to this error (from 1st DB) is mentioning that it's undocumented (link below) and i could not find any other forum posts mentioning it.

  16203 – The option “INLINE=ON” is not valid for this function. Check
  the documentation for the constructs supported with INLINE option in a
  function.

From: Brent Ozar - What’s New in SQL Server 2019’s sys.messages: More Unannounced Features

We don't have an active support contract with Microsoft but are trying to reach them via the supplier that provided the licences so I will give an update here if they get back to us.

I can provide more information if needed.

Refer this article The only solution worked for me was drop and recreate the UDF after upgrading to SQL 2019.
The inline_Type column from Sys.sql_modules will be 1 after your upgrade to SQL 2019.
After you drop and recreate the UDF, the Inline_type will be 0 and the replication initialization works fine.


Tuning MySQL to consume less memory I have a VM which has 2GB Ram, (full specs)
And I am setting up a site which has one table in particular with over a million records.
There's little or no usage of this particular database (perhaps once or twice a day) but simply running mysql grinds the whole server to a halt. I've looked through the top results but nothing is really denting the CPU however the memory seems to be the issue.
The site isnt even live of taking requests yet. the memory situation looks like this:

# free -m
             total       used       free     shared    buffers     cached
Mem:          2006       1880        126          0          3         53
-/+ buffers/cache:       1823        183
Swap:         2047        345       1702

Are there any good pointers to tune mysql to stop hogging the system memory?

Thanks very much

EDIT: (requested by 8bit):

http://tny.cz/b41a0b12

MySQL should include a number of starting point configuration files for my.cnf that can adjust the memory usage. The files are named my-small.cnf, my-medium.cnf, my-large.cnf, and my-huge.cnf.

There is an online MySQL memory usage calculator at http://www.mysqlcalculator.com/. By comparing the starting point files and using the memory usage calculator you should be able to adjust your my.cnf configuration file to accomodate your server's memory constraints.

For more information about what each parameter does, you can reference the MySQL documentation available online. Here is a link to the MySQL documentation for MySQL server 5.1: http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html


How to get rows where count() is null? This is my SQL request :

SELECT COUNT( b0_.id ) AS sclr0, d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
      d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
      m2_.libelleMention AS libelleMention5, t3_.libelleType AS libelleType6
FROM Bac b0_
INNER JOIN Bachelier b4_ ON b0_.bachelier_cne = b4_.cne
INNER JOIN EtablissementBac e5_ ON b0_.etabBac_id = e5_.codeLieu
INNER JOIN Delegation d1_ ON e5_.delegation_id = d1_.codeLieu
INNER JOIN MentionBac m2_ ON b0_.mentionBac_id = m2_.codeMention
INNER JOIN TypeBac t3_ ON b0_.typeBac_id = t3_.codeType
WHERE m2_.codeMention IN ('TB',  'B')
AND t3_.codeType IN ('114',  '129')
GROUP BY d1_.codeLieu, m2_.codeMention, m2_.libelleMention,
         t3_.codeType, t3_.libelleType, t3_.abbrType
ORDER BY d1_.codeLieu ASC , b0_.mentionBac_id ASC , b0_.typeBac_id ASC

And this is the result (it's a screenshot):

http://nsa33.casimages.com/img/2013/05/10//130510034126658849.png

The thing is I get all the rows with the equivalent number (sclr0), but I also need rows with null values. The needed rows are defined by the columns libelleMention5, libelleType6. In other words, I want for each codeLieu1, all the possible combinations of libelleMention5 and libelleType6, and the equivalent number in the column sclr0 (which should be 0 if the cell is NULL).

How can I do that ?

EDIT

This is the new screenshot : http://nsa34.casimages.com/img/2013/05/11//130511101655302101.png

OK, without any detailed info about the tables' relationships (1-1, 1-to-many, many-to-many) and the primary keys, it's not easy to deal with this. But lets try.

The FROM clause has a 6-table join:

FROM
  Bac              b0_  INNER JOIN
  Bachelier        b4_   ON b0_.bachelier_cne = b4_.cne INNER JOIN
  EtablissementBac e5_   ON b0_.etabBac_id = e5_.codeLieu INNER JOIN
  Delegation       d1_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN
  MentionBac       m2_   ON b0_.mentionBac_id = m2_.codeMention INNER JOIN
  TypeBac          t3_   ON b0_.typeBac_id = t3_.codeType

Since 2 tables (m2_ and t3_) are involved in the WHERE clause:

WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')

and one more (d1_) in the GROUP BY clause:

GROUP BY
  d1_.codeLieu,
  m2_.codeMention, m2_.libelleMention,
  t3_.codeType, t3_.libelleType, t3_.abbrType

we'll assume that these 3 should be join with proper INNER joins.

And since you are counting on b0_, this will probably have to be "LEFT" joined. That leaves with some choices about the other two tables (b4_ and e5_). So, lets rewrite the FROM part. First the 3 (sure JOIN) tables, then the ambiguous 2 and lastly the sure (LEFT JOIN) b0_:

FROM
  TypeBac          t3_  INNER JOIN
  MentionBac       m2_  INNER JOIN
  Delegation       d1_  INNER JOIN

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu INNER JOIN
  Bachelier        b4_
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne
                        AND b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Well, that doesn't look correct. Because (except for the d1 - e5 join), we don't have a way to join the other 4 tables. All 4 are to be joined with the b4 which has to be last (as we want to LEFT JOIN it.) This observation leaves with one choice:

FROM
  TypeBac          t3_  CROSS JOIN
  MentionBac       m2_  CROSS JOIN
  Delegation       d1_  INNER JOIN

  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu
                        CROSS JOIN
  Bachelier        b4_
                        LEFT JOIN
  Bac              b0_   ON b0_.bachelier_cne = b4_.cne
                        AND b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention

Try changing the FROM clause as above. The only other option I see is the removal of the CROSS JOIN  Bachelier  b4_, as no column of this table is included in the SELECT.

After more comments and a request, here is one final trial:

SELECT COUNT( b0_.id ) AS sclr0,
       d1_.codeLieu AS codeLieu1, d1_.nomLieu AS nomLieu2,
         d1_.lngLieu AS lngLieu3, d1_.latLieu AS latLieu4,
       m2_.libelleMention AS libelleMention5,
       t3_.libelleType AS libelleType6
FROM
  TypeBac          t3_
    CROSS JOIN
  MentionBac       m2_
    CROSS JOIN
  Delegation       d1_
    INNER JOIN
  EtablissementBac e5_   ON e5_.delegation_id = d1_.codeLieu
    LEFT JOIN
  Bac              b0_   ON b0_.etabBac_id = e5_.codeLieu
                        AND b0_.typeBac_id = t3_.codeType
                        AND b0_.mentionBac_id = m2_.codeMention
WHERE m2_.codeMention IN ('TB',  'B')
  AND t3_.codeType IN ('114',  '129')
  AND EXISTS
        ( SELECT *
          FROM Bac b
          WHERE b.etabBac_id = e5_.codeLieu
            AND b.typeBac_id = t3_.codeType
            AND b.mentionBac_id = m2_.codeMention
        )
GROUP BY
    d1_.codeLieu,
    m2_.codeMention, m2_.libelleMention,
    t3_.codeType, t3_.libelleType, t3_.abbrType ;


Lock or disable connections to Postgres while performing maintenance I never have this issue with MySQL, but PostgreSQL is always a pain when trying to do any kind of maintenance.
I am trying to truncate and load new data into databases on a PostgreSQL server, but there are usually active connections, and this messes up things. There is also one particular database I need to drop and create, and current active doesn't allow me to drop it.
All these are setup in a script that should run. My issue is active connections is killing me and not letting the script work.
Do I lock connections to the DB or block connections? How do I do this? Not as familiar with PostgreSQL as I am with MySQL.

You can set connection limit for role:

ALTER ROLE x SET CONNECTION LIMIT 0;

or for databases:

ALTER DATABASE db SET CONNECTION LIMIT 0;

After your maintenance chores are done, reset your limit to its previous value such as -1 for unlimited connections.


PostgreSQL: simple equality query returning no results for indexed columns I have a very large table (around 140M rows) with five columns:

CREATE TABLE IF NOT EXISTS citations
(
   citing_id text,
   citing_issn text,
   cited_id text,
   cited_issn text,
   year int
);

There are hash indexes on citing_id and on cited_id, neither of which has any NULL values (confirmed), but queries selecting on equality in these columns are returning zero results no matter what. Even this query, which I would think would work in any populated table, returns zero rows:

SELECT *
FROM citations
WHERE citing_id IN (SELECT citing_id
                    FROM citations LIMIT 1)
LIMIT 100;

However, the same query on a non-indexed column works exactly as expected:

SELECT *
FROM citations
WHERE citing_issn IN (SELECT citing_issn
                      FROM citations LIMIT 1)
LIMIT 100;

The data was loaded from a CSV file using the COPY command, and I suspect there may be extra hidden characters on the end of the indexed values -- querying with WHERE citing_id LIKE '<id>%' is very slow (hash indexes don't help with partial strings) but returns the right results. Still, the first query above should work no matter what, unless I'm mistaken.

I'm running Postgres 9.5.3.0 on MacOS 10.13.1

edit:

The query plan for the first query (on the indexed column, returning no rows) is:

Limit  (cost=0.03..1.38 rows=100 width=58)
   ->  Nested Loop  (cost=0.03..265.83 rows=19751 width=58)
         ->  HashAggregate  (cost=0.03..0.04 rows=1 width=17)
               Group Key: citations_1.citing_id
               ->  Limit  (cost=0.00..0.02 rows=1 width=17)
                     ->  Seq Scan on citations citations_1  (cost=0.00..3164425.68 rows=148131768 width=17)
         ->  Index Scan using citations_citing_id on citations  (cost=0.00..265.14 rows=65 width=58)
               Index Cond: (citing_id = citations_1.citing_id)

and for the second query (on the non-indexed column, returning expected results):

Limit  (cost=0.04..139.54 rows=100 width=58)
   ->  Hash Semi Join  (cost=0.04..3581836.35 rows=2567617 width=58)
         Hash Cond: (citations.citing_issn = citations_1.citing_issn)
         ->  Seq Scan on citations  (cost=0.00..3164425.68 rows=148131768 width=58)
         ->  Hash  (cost=0.03..0.03 rows=1 width=10)
               ->  Limit  (cost=0.00..0.02 rows=1 width=10)
                     ->  Seq Scan on citations citations_1  (cost=0.00..3164425.68 rows=148131768 width=10)

There are hash indexes on citing_id and on cited_id, neither of which
  has any NULL values (confirmed), but queries selecting on equality in
  these columns are returning zero results no matter what.

The index is probably corrupted. Hash indexes are only WAL-logged since PostgreSQL 10. In previous versions, they don't have that mechanism that makes them persist correctly across unclean shutdowns.

The doc on CREATE INDEX for 9.5 has this warning:

  Hash index operations are not presently WAL-logged, so hash indexes
  might need to be rebuilt with REINDEX after a database crash if there
  were unwritten changes. Also, changes to hash indexes are not
  replicated over streaming or file-based replication after the initial
  base backup, so they give wrong answers to queries that subsequently
  use them. Hash indexes are also not properly restored during
  point-in-time recovery. For these reasons, hash index use is presently
  discouraged.

Consider upgrading to PostgreSQL 10, and in the meantime, REINDEX the hash indexes.


Log Sequence Numbers in Full BackUp and Log Backup My full back-up starts daily at 02:00 AM. It takes about 3 hours.
I also take T-Log back-ups every 15 minutes (at 15,30,45 and 00 hours).
I can verify the LSN sequences of T-log backups when I "restore headeronly".
However, there seem to be a gap between the last LSN of the full backup and the T-log backup taken immediately after backup had started (i.e 02:15)

Below is restore headeronly output from the full backup started at 02:00

FirstLSN: 76583000073086500011
LastLSN: 76595000084143900001

...and this is the restore headeronly output from the log backup started at 02.15

FirstLSN: 76570000086250400001
LastLSN:  76571000009693900001

Shouldn't the last LSN of the full back up and first LSN of log back up be the same?
I compared the LSNs of the log back up taken at 05:15 and 05:00 (when the full back up has finished) but there is no sequence again.
I also checked RESTORE FILELISTONLY of the full backup and CreateLSN, DropLSN of log file is 0.
What is it that I might be missing here?
Thank you.

LastLSN + 1 of the Full database backup will fall in between the FirstLSN and LastLSN of its subsequent transaction log backup.
(http://www.mssqltips.com/sqlservertip/3209/understanding-sql-server-log-sequence-numbers-for-backups/)


Unable to start SQL Server on Ubuntu 16.04 I had SQL Server v.Next public preview on my Ubuntu 16.04 machine installed and working yesterday. But it's not working today.

Getting below error:

root@OraServer:/var/opt/mssql/log# systemctl status mssql-server
● mssql-server.service - Microsoft(R) SQL Server(R) Database Engine
   Loaded: loaded (/lib/systemd/system/mssql-server.service; enabled; vendor preset: enabled)
   Active: inactive (dead) (Result: exit-code) since Sun 2016-11-27 13:34:23 IST; 18s ago
  Process: 6368 ExecStart=/opt/mssql/bin/sqlservr (code=exited, status=1/FAILURE)
 Main PID: 6368 (code=exited, status=1/FAILURE)

Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Unit entered failed state.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Failed with result 'exit-code'.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Service hold-off time over, scheduling restart.
Nov 27 13:34:23 OraServer systemd[1]: Stopped Microsoft(R) SQL Server(R) Database Engine.
Nov 27 13:34:23 OraServer systemd[1]: mssql-server.service: Start request repeated too quickly.
Nov 27 13:34:23 OraServer systemd[1]: Failed to start Microsoft(R) SQL Server(R) Database Engine.

I couldn't see anything in SQL server error logs. You can say no logs at all after the last reboot.

SQL Server did not start because the OS did not have 3250MB of memory. Was tracked by below command

journalctl -u mssql-server.service -b

Nov 30 00:43:21 OraServer sqlservr[4075]:
 sqlservr: This program requires a machine with at least 3250 megabytes of memory.

Memory was added and SQL Server was started:

systemctl status mssql-server


Why did adding a self-referential foreign key freeze the table? I'm running PostgreSQL 11.5.  I have a simple table of registers.
create table register(
  id serial primary key,
  name text not null
);

This table is small (~6000 rows), relatively heavy read (100s queries/s), almost no write.  I wanted to add a self-referential foreign key:
alter table register
  add column leader_id integer references register(id);

Given the size of the table I assumed this would be a rather mundane change.  It ran fine locally and on the staging server.  However, when I ran it against production, the table locked up.  Logs show ALTER and various SELECT statements taking ~10m to complete.
2020-08-04 00:01:15 UTC:10.0.2.101(59588):postgres@db:[21609]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: could not receive data from client: Connection reset by peer
2020-08-04 00:02:39 UTC:10.0.2.101(59558):postgres@db:[1795]:LOG: unexpected EOF on client connection with an open transaction
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: duration: 456741.453 ms execute <unnamed>: alter table register
add column leader_id integer references register(id);

2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:LOG: could not send data to client: Broken pipe
2020-08-04 00:02:39 UTC:10.0.2.101(59578):postgres@db:[18376]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: duration: 456635.839 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 123 AND register.account_id = '22781BD1-F37A-4ACE-9A3D-CBF3464AFB43'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52906):db@db:[3365]:FATAL: connection to client lost
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: duration: 456656.956 ms statement: SELECT register.deleted_at IS NOT NULL AS deleted, register.client_id AS register_client_id
FROM register
WHERE register.id = 234 AND register.account_id = 'A6D8395C-63E8-40A8-A0AE-4F19B1DA5509'::uuid
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:LOG: could not send data to client: Connection timed out
2020-08-04 00:02:39 UTC:10.0.1.227(52904):db@db:[3364]:FATAL: connection to client lost

What's going on here that is causing the table to lock up?  How can I safely work with a self-referential foreign key?

The quick thing to try is set lock_timeout=1000;, then try your ALTER a bunch of times. Most likely you'll hit the timeout each time, but you might get lucky and that'll save you from having to hunt down the actual fix, which is, I'm sorry to say, not nearly as easy.
Almost certainly what is going on is that the other processes keep their transaction open after performing their SELECT on register. After that first SELECT is performed, the transaction will have an ACCESS SHARE lock on that table until the transaction commits or rolls back.
Unfortunately, ADD COLUMN requires an ACCESS EXCLUSIVE lock - regardless of whether there's a foreign key, self-referential or not, involved. And, per the table of conflicting lock modes, ACCESS EXCLUSIVE does conflict with ACCESS SHARE.
You can poke into which locks specifically are involved with pg_locks, and it's useful to join pg_stat_activity to see the actual queries. What follows is 3 psql sessions, interleaved, so you can see what's going on.
-- psql session 1: what the always-reading application code is doing
testdb=# BEGIN; SELECT * from register where id=10;
BEGIN
 id | name
----+------
 10 | bar
(1 row)

testdb=# -- now going to psql session #2.

-- psql session 2: look at the locks from the SELECT's still-open transaction.

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |      mode       |                query
---------------+-----------------+-------------------------------------
 register      | AccessShareLock | SELECT * from register where id=10;
 register_pkey | AccessShareLock | SELECT * from register where id=10;
(2 rows)

-- psql session #3: perform the ALTER
testdb=# begin; ALTER TABLE register ADD COLUMN leader_id integer;
BEGIN
-- is now hanging on the ALTER - back to session #2

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
(3 rows)

As you can see, the ALTER TABLE is trying, and failing (I add an asterisk when pg_locks.granted is false) to get an AccessExclusiveLock on the register table.
At this point, your application is going to start having problems. Let's open up a 4th psql and try another SELECT:
testdb=#  BEGIN; SELECT * from register where id=12;
BEGIN
--- aaaand... the SELECT is hanging now too! Let's go back to #2 and check the locks again

testdb=# select relation::regclass, case when granted then mode else concat(mode, '*') end as mode, query from pg_locks join pg_stat_activity on pg_locks.pid=pg_stat_activity.pid and pg_locks.pid<>pg_backend_pid() where pg_locks.locktype='relation';
   relation    |         mode         |                       query
---------------+----------------------+----------------------------------------------------
 register      | AccessShareLock      | SELECT * from register where id=10;
 register_pkey | AccessShareLock      | SELECT * from register where id=10;
 register      | AccessExclusiveLock* | ALTER TABLE register ADD COLUMN leader_id integer;
 register      | AccessShareLock*     | SELECT * from register where id=12;
(4 rows)

PG, seeing that there's an already-waiting ACCESS EXCLUSIVE lock on the table, will no longer grant additional ACCESS SHARE locks on the table until the ACCESS EXCLUSIVE has finished it's thing and released the lock. So now your SELECTs are piling up and everything is stuck until all of the transactions that had selected from register as of when the ALTER TABLE first tried to grab the lock either commit or rollback.
The fix here, alas, is "don't do that"; the application code needs to issue a COMMIT or ROLLBACK (as long as the SELECT is the only thing that has occurred in the transaction, it doesn't matter which) right after the SELECT, so that the ACCESS SHARE lock is released immediately. You could also split the ADD COLUMN and ADD FOREIGN KEY steps since only the ADD COLUMN requires ACCESS EXCLUSIVE (ADD FOREIGN KEY requires only SHARE ROW EXCLUSIVE, which does not conflict with ACCESS EXCLUSIVE), but I'd be surprised if that helps at all; it's the locking sequence that is causing issues, not the bit of extra work that is involved in doing the ADD FOREIGN KEY.


Is it possible to limit timeout on Postgres server? I set connection and command timeout to 10 minutes in my application (client side).

Than my application runs a simple query: SELECT pg_sleep(65)

On some servers it works fine, but other servers close the connection after 60 seconds.

Could this be some sort of PostgreSQL server configuration which limits timeouts and ignores my client settings?

Yes, it's possible

There are two settings mentioned in the docs (idle_in_transaction_session_timeout is new to version 9.6x)

statement_timeout (integer)

  Abort any statement that takes more than the specified number of milliseconds, starting from the time the command arrives at the server from the client. If log_min_error_statement is set to ERROR or lower, the statement that timed out will also be logged. A value of zero (the default) turns this off.

  Setting statement_timeout in postgresql.conf is not recommended because it would affect all sessions.

idle_in_transaction_session_timeout (integer)

  Terminate any session with an open transaction that has been idle for longer than the specified duration in milliseconds. This allows any locks held by that session to be released and the connection slot to be reused; it also allows tuples visible only to this transaction to be vacuumed. See Section 24.1 for more details about this.

  The default value of 0 disables this feature.

It's important to not set the statement_timeout in postgresql.conf unless you want amusement.

Here is an example of it working

SET statement_timeout = 10000;
SET
test=# SELECT pg_sleep(15);
ERROR:  canceling statement due to statement timeout


Optimum configuration for 4 SSD drives I would like to move a SQL Server database from a shared configuration with the web-server to it's own dedicated box. My current budget will allow me to put 4 disks together in an array with a single hot spare. I'd like to stretch to 8+ drives but the costs are a little out of my budget right now (and probably somewhat overkill).

So my question is, what would be the optimum configuration for SQL Server 2012 when limited to 4 disks? The database is around 29 GB and is growing around 250-500 MB per month. The database will typically deliver 80% reads to 20% inserts/updates/deletes.

I understand from researching this subject that my options are as follows:

RAID 5 with all functions on a single volume.
Dual RAID 1 with OS, Backups and system dbs on one volume and database + log files on the other.
RAID 10 with all function on a single volume.

I'm looking for a solution that will give me reasonable performance but won't obliterate the array if a single drive fails (which I understand is quite common with SSDs).

Current Hardware ------------------

HP ProLiant DL360 G7 1 x Xeon E5640 / 2.66 GHz - RAM 12 GB - 2 x 300GB Pluggable SAS SFF 10,000 rpm disks in RAID 1.

With spinning-platter disks you want to have the logs and data on separate drives as random access data disrupts the sequential log write operations, making the logs a performance bottleneck.  SSDs do not have this issue as they lack the performance constraints imposed by the mechanical action of conventional hard disks.

If you're getting SSDs for a DB server, get ones designed for a long life span, such as Intel S3700s.  You're probably better off getting two of those than four cheaper ones, and 2x100GB units + a hot spare (which is probably enough to keep you going for a few years at your current rate of growth) should cost around £200 each according to Google Shopping.

Mixing logs and data on the same disks isn't such a big deal any more.  However, you may get some resiliency out of having separate log drives.  If you want to do this then 5 disks (enough for two pairs + a hot spare) should still see you with change out of £1,000.

Try two good quality drives in a RAID-1, with a hot spare for a starter.  Your growth suggests you probably won't have transaction volumes so high that this setup can't keep up with your application workload.

Back up your database onto spinning disks - a couple of enterprise-grade SATA disks and a hot spare should cost you a few hundred dollars.

If (and only if) you have performance problems with that lot then you might look into adding more SSDs, but I suspect just one pair will be fast enough to keep up with your transaction volumes quite comfortably.


Problems Deleting Data I'm stuck on trying to delete some information on my MySQL database.

I've never used a database this big and when I developed this application I didn't imagine it would get so big (maybe small for you guys, but for me it's huge).

My thought was:

I have a table with "mos" Managed Objects (PK moid) (Image 1)
I have a table with "periods" (PK periodid) (Image 1)
I have a table that joins "Period" with the MO (PK moperiodid, FKs periodid, moid) (Image 1)
I have 200 tables (the number of tables is variable according to user inputs) that uses the moperiodid as FK (Image 2)
Then, when I want to clean the "historical data", I just delete cascade from MO or from PERIOD.

That was what I thought, but in my real world, it isn't happening well.

Everything works fine for Selects and Inserts, but for the first time I want to begin cleaning data, the main idea was to keep only the last X days (configured according to users input also).

For every day I have 3094555 new moperiods, even the delete of only one is not working:

0   13  22:46:19    delete from parser_customer_period_mo where id = 1  Error Code: 1205. Lock wait timeout exceeded; try restarting transaction    51.169 sec

And after every try the InnoDB Writes/Reads per Second goes crazy for 1 hour. (Image 3)

I have no idea about how I'll delete the old information, and this is the small system, I'm building the second one of 30 systems, and the second will have 12 times the amount of data.

Image 1:

Image 2:

Image 3:

parser_customer_period_mo is a many-to-many relationship table?  It probably does not need id.  (Getting rid of unnecessary indexes helps at some level.)  (This may have somehow caused the lock wait timeout.)

200 similar tables -- bad design.  (But seems to be 'necessary' in this situation.)

FKs cost something, but probably that won't explain what you are seeing.

IN ( SELECT ... ) is an inefficient construct (especially in older versions of mysql), see if you can turn it into a JOIN.

OR inhibits the use of an index.  (Probably cannot improve on that.)

Change innodb_buffer_pool_size to be about 70% of available RAM.  This should cut back the I/O a lot.


query execution time in mysql I have run the query in mysql which consists of approximately 33million records when i have executed the query

select population1 < 200000 from city;
it shows like this after execution

34459393 rows in set (16.04 sec)

My question is what is 16.04 sec because in normal time it has taken morethan 30min but it shows 16.04 sec.
How can i calculate the actual time.

The 16.04 seconds is a timer on the client that measures the amount of time that passed between the call and the execution of the query (if you want to be more specific than that, the wall clock time between calling start_timer() and mysql_end_timer(), something that can lead to hilarious results like this one I got).

The reason that you think that it took you more than 30 minutes to execute is probably because it doesn't have into account the output to the stdout. You can check that it really takes 16 second by doing:

mysql> pager cat > /dev/null
mysql> <your query here>
mysql> nopager

If you want to measure how much time takes to write to the standard output, you can do:

$ time mysql -u <your user> -p<your password> <your database> \
  -e "select population1 < 200000 from city"

Note: Are you sure you want to print a 0 or a 1 for ALL rows from that table? Maybe the option --safe-updates and/or using a GUI can help you a bit with your queries?


auto populate common columns of all tables using triggers I have the following 4 tables:

course
section
student
enrollment

All of them have 4 columns in common that I want to auto-populate before an insert or update.
At first the simple insert trigger was giving me an infinite recursion error.
After researching for a while I found out that using views and an instead of trigger could be a better solution. Now when I insert a row with course_no, description, cost and prerequisite it gives an error that CREATED_BY can't be null. However, the values should have been automatically inserted by the trigger.
I've been working on it for a few hours now, but had no luck.
The course table is defined as
CREATE TABLE COURSE
(
COURSE_NO NUMBER(8, 0) NOT NULL
, DESCRIPTION VARCHAR2(50 BYTE) NOT NULL
, COST NUMBER(9, 2)
, PREREQUISITE NUMBER(8, 0)
, CREATED_BY VARCHAR2(30 BYTE) NOT NULL
, CREATED_DATE DATE NOT NULL
, MODIFIED_BY VARCHAR2(30 BYTE) NOT NULL
, MODIFIED_DATE DATE NOT NULL
)

The view for course is defined as
CREATE VIEW v_course
AS SELECT CREATED_BY, CREATED_DATE, MODIFIED_BY, MODIFIED_DATE
FROM COURSE

The instead of trigger is as follows
create or replace TRIGGER course_trRg INSTEAD OF
INSERT ON v_course
FOR EACH ROW
BEGIN
INSERT INTO course (
    created_by,
    created_date,
    modified_by,
    modified_date
) VALUES (
    user,
    sysdate,
    user,
    sysdate
);
END;

This is how I'm trying to insert
INSERT INTO v_course (
course_no,
description,
cost,
prerequisite
) VALUES (
1231,
'desc',
1,
1
);

All the tables have the columns created_by, created_date, modified_by, modified_date in common.

You didn't post what you tried for a simple row level trigger so it isn't obvious how you got an infinite recursion error.  My assumption is that you tried to insert into the table in your trigger rather than simply setting the column values.  Creating a view and an instead of trigger is vastly overcomplicating the problem.
My assumption is that you want something like this.  I'm not clear why modified_by and modified_date are not null since there is logically no modification date or user if a row has just been inserted.  If you want to populate modified_by and modified_date on the initial insert, you can add that to the inserting branch.
create or replace trigger course_trg
  before insert or update on course
  for each row
declare
begin
  if( inserting )
  then
    :new.created_by := user;
    :new.created_date := sysdate;
  elsif( updating )
  then
    :new.modified_by := user;
    :new.modified_date := sysdate;
  end if;
end;


AlwaysON Availability Groups - IP address & subnet change of secondary replica node This is a follow up from question:
AlwaysON Availability Groups - IP address change of secondary replica node

Same scenario, Availability Group in synchronous commit mode, one primary and one secondary replica in a multi-subnet configuration.  The cluster nodes are physical machines.  Due to hardware maintenance, the server used by the secondary replica cluster node is being moved, so will be offline and come back online with a different IP address in a different subnet.  How would one approach this?

My initial thoughts are:  If possible, add the new network interface to the secondary node.  Configure the new network interface with the new IP address which is in a different subnet.  The cluster should automatically build the internal routes and register the new network interface.

In FCM (Failover Cluster Manager) a new cluster network will appear under the 'Networks' tab.  In cluster core resources, add the new network and create a new static IP address for the cluster VNN.  Apply the changes then go back in and add an OR dependency on the new IP address.

Prior to the server move outage where the old IP address/network interface will be removed, in FCM, go back to cluster core resources, go into the properties of the cluster VNN and remove the old network/static IP address and remove any dependencies on it.  When the server is taken offline and comes back online, the old network interface won't be visible to the server and won't appear in the networks tab in FCM.  There shouldn't be any issues with any clustered resources as any dependencies on the old IP/subnet/network were removed.

Is there anything else that should be taken into consideration?  Since the cluster nodes are physical, does this complicate matters with regards to the network interfaces?  With testing in a virtual environment it's obviously simplified as removing and attaching virtual network switches is easy.

How would one approach this?

With a whole bunch of pre-move work :)

  My initial thoughts are: /snipped for brevity

You have the right ideas, let me add a few more.

Pre-Work

The cluster is now Multisubnet, we'll need to add an IP for the cluster name in the second subnet. This will be added to the cluster name resource as an 'OR' dependency as you've stated above. This can be added at any time (I'd add it pre-move). The cluster CAP will now have 2 IP addresses, one for each subnet.
Firewall rules, etc.
The listener (as you've already stated) will become multi-subnet. Another IP address will need to be added in the 'OR' dependency.
Double check the setting of the client access point (CAP [aka VNN, aka network name]) for: HostRecordTTL and RegisterAllProvidersIP to make sure they are setup the way you want them. Note that you may want to use multiple listeners to facilitate clients that use older connection libraries not supporting the new keywords.
Decide if you want to remove the replica from the AG (if it's going to take a while to physically move the server) or to just pause the AG. If the AG is removed, the databases will go back into a "restoring..." state and you'll be able to catch them up with restores at a later point after the server arrives and is setup.
Quorum: There isn't much to say here, it's a 2 node cluster with 1 node getting airline miles. Included because we'll hit this later and it's a common question.

Post-Work

The server should (with a new IP and proper firewall rules) be able to contact the cluster and join.
Run cluster validation wizard. Save this report as the initial move report. We may need to look at it later. This will also verify most cluster related items and runs very quickly.
Verify network latency. Set CrossSubnetDelay and CrossSubnetThreshold appropriately for the latency and health of the connection. No changes may be needed but it's good to double and triple check.
Restore transaction logs/diffs/etc.
Add the replica back into the AG
Revisit Quorum. Even though there are just two nodes in the cluster, we'll want to double check we're not using a disk witness. Depending on the version of Windows we should/could use either a fileshare witness or Azure witness.

There may be additional items specific to your environment but that should be the gist of it.  You've pretty much hit the nail on the head in your original question/post, this just adds in a little filler :)


How can I solve postgresql problem after deleting wal files? I turned the archive_mode on on my postgresql configuration for testing a backup server . And since the wal files took lots of disk space, after my test I turned it off and also deleted the wal files. When I tried to restart postgresql I got the following error.

 root@hooshang:/etc/postgresql/9.1/main# /etc/init.d/postgresql restart
 * Restarting PostgreSQL 9.1 database server
 * The PostgreSQL server failed to start. Please check the log output:
 2014-10-16 13:15:28 IRST LOG:  database system was shut down at 2014-10-15 15:51:53 IRST
 2014-10-16 13:15:28 IRST LOG:  could not open file "pg_xlog/00000001000007DC00000037" (log file 2012, segment 55): No such file or directory
 2014-10-16 13:15:28 IRST LOG:  invalid primary checkpoint record
 2014-10-16 13:15:28 IRST LOG:  could not open file "pg_xlog/00000001000007DC00000029" (log file 2012, segment 41): No such file or directory
 2014-10-16 13:15:28 IRST LOG:  invalid secondary checkpoint record
 2014-10-16 13:15:28 IRST PANIC:  could not locate a valid checkpoint record
 2014-10-16 13:15:28 IRST LOG:  startup process (PID 17467) was terminated by signal 6: Aborted
 2014-10-16 13:15:28 IRST LOG:  aborting startup due to startup process failure

How can I fix this issue?

You have corrupted your database by manually deleting files from within the data directory. Never delete files from within the data directory manually.

Safely removing WAL

If you want to remove WAL, either let the server do it at CHECKPOINT time, or use pg_archivecleanup. Note that the server will remove WAL is no longer needs automatically, unless:

archive_mode is on, but archive_command is failing, so the server keeps on retrying the archive attempts until they succeed or the admin intervenes;
It's still preserved by wal_keep_segments
(in 9.4) It's still needed by a replication slot.

If none of those apply, a CHECKPOINT (either automatic, or manually issued via SQL) will remove all WAL that's not currently required. So you don't have to delete it by hand.

In unusual circumstances you might need to use pg_archivecleanup, like if you've run out of disk space due to WAL accumulation after archiving has failed for a long time. You might decide to accept that you'll have to re-create your replicas as a result of throwing away WAL they still need and use pg_archivecleanup to free space to get the master running.

But you should never delete WAL segments by hand.

Recovering with archived WAL

If you kept the archived WAL somewhere else, you might just be able to copy the files back into pg_xlog, or create a recovery.conf with a restore_command to do so. See the manual on PITR and log shipping for details.

Recovering without archived WAL

If you don't have a copy of those WAL files somewhere else, you should restore from a backup if you have a recent backup, because you have corrupted your database.

If you do not have a backup, follow the instructions in the corruption wiki page and only once you have made a complete copy of the current state of the database, as a last resort only, use pg_resetxlog to throw away the transaction logs and force the DB to start with incomplete transactions.

You must then pg_dump the database, stop it, initdb a new one, and restore to it. Do not keep using the database you damaged. Never keep using a database you used pg_resetxlog on, and never use it except as a last resort.


Does the length of the fields play a role in index access in MS-SQL? Last week I had already started two threads dealing with slow SQL selects that probably did not use the index.
Today I noticed the following: The customer and the material are involved in almost all index accesses.
Customer and material are nvarchar fields everywhere, and the customer and material numbers are the same in all tables.
Only the length of the nvarchar fields is different in different tables.
The customer sometimes consists of an nvarchar (10), sometimes of an nvarchar (20) and sometimes of an nvarchar (30).
This is because these tables were created by external consultants who each used a different length for the customer-fields.
However, the customers are only seven characters everywhere in all tables.
Could that be a reason that the index access is not working?
Does the nvarchar length play a role when linking different tables via fields for which an index exists?

Length of the fields does not determine if the index is used for a query in SQL Server. But it could affect how performant that index is when it is used to serve the data. It's the same idea why indexing a UUID field is a little less performant than indexing an INT, one reason being the difference in the number of bytes that the values use when indexed.
When and how an index is used is solely based on which columns and their order in how you define in the index, the columns you use in your predicates (JOIN, WHERE, HAVING clauses), the current statistics on those columns at the time you run your query, and how you are comparing those columns in the predicates (e.g. in your linked thread you're using a function in one of your predicates which likely is preventing an index seek from happening and instead an index scan occurs).
Things like using functions in predicates, or type conversions when comparing two different data types could cause a sub-optimal plan, and definitely cause performance problems like cardinality estimate issues.


UDF in check constraint -- downside? I have a table where all actions affect single rows (insert and update, no delete's but if there were, they would be individual as well).  I'm considering using a UDF in a check constraint so I can make sure that a field value is only used in combination with another value (two FK's, the first time A=1 is used, B=x becomes fixed, so that if x = 4 then A=1 can never be matched with B=3 or anything except 4).

What are the downsides of doing this, other than the neligible additional time spent when inserting/updating the row?

UPDATE: The application doesn't provide a mechanism for changing either column A or B after the row has been created.

You can store A and B columns in a separate table. Make sure your table has a primary key on A, and a unique constraint on (A,B) Refer to (A,B) from your table. The primary key on A will guarantee only one B per A. The foreign key without ON UPDATE CASCADE will make sure B does not change as long as the row is referred from the child table.


Transformation of row data based on preceding row for special cumulative sum Following the answer to this question, I have managed to produce the following output to get a running sum of values:

id      creation                    operation       value       running sum
SyJw-c  2016-09-01 00:11:08.307419  positive_op_1   1.33        28.82
SyJw-c  2016-08-21 08:32:54.431662  negative_op_1   -1          27.49
SyJw-c  2016-08-18 07:38:33.878365  positive_op_2   1           28.49
SyJw-c  2016-08-14 18:12:03.599797  negative_op_1   -1          27.49
SyJw-c  2016-08-02 15:44:29.693303  positive_op_1   1.33        28.49
SyJw-c  2016-07-31 12:08:50.659905  override_op_1   4.66        27.16
SyJw-c  2016-06-26 06:53:54.537603  negative_op_1   -3.5        22.5
SyJw-c  2016-05-31 13:34:08.005687  negative_op_1   -1          26
SyJw-c  2016-05-31 13:34:04.776970  negative_op_1   -1          27
SyJw-c  2016-05-31 11:27:09.502983  override_op_2   28          28

BUT my case is more complex. Not only do I need to sum up the values, I need to be able to perform a conversion first to some rows based on the running sum of the row right beneath it.

Let me first explain the motivation:

Currently I have a table with incremental, decremental and override operations. I would like to port the data to a table with only incremental and decremental operations such that I would be able to straightforwardly sum up the values. I am not looking to maintain the old table, simply a way to migrate the data into a simpler model and henceforward to append data to the new table only.

Taken the "raw" table above, I would like to write a query (I am running on postgresql 9.5) and get a table as closely resembling the below. (Conversely, I would like to know that what I am attempting is impossible)

Note the override operators are interspersed between the normal operators, they may appear more than twice as in the example, also, all initial operators (the earliest in the table) are override with an initial value that should be taken into account as in the example below. Moreover I had shown only data belonging to one group (same id) but the general idea is to perform this migration for all groups. Lastly I show the math in parentheses, I don't need that in the result, it is for the example only:

id      creation    oper      transformed_op   value   transformed_value       running sum
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.98
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_2                    1       1                       10.65
SyJw-c  2016- ...  neg_op_1                    -1      -1                      9.65
SyJw-c  2016- ...  pos_op_1                    1.33    1.33                    10.65
SyJw-c  2016- ...  ovr_op_1   new_rel_op_1     4.66    (4.66-22.5) = -17.84    4.66
SyJw-c  2016- ...  neg_op_1                    -3.5    -3.5                    22.5
SyJw-c  2016- ...  neg_op_1                    -1      -1                      26
SyJw-c  2016- ...  neg_op_1                    -1      -1                      27
SyJw-c  2016- ...  ovr_op_2   new_rel_op_2     28      (28-0) = 28             28

The table is shown ordered from last to first. The 22.5 is 26 - 3.5. This subtraction should be done (this_value - previous_sum) depends on the value of the transformed_op. When the original op had been an override op I would like to perform some action based on the running sum of the row proceeding it (ordered by creation desc), in this case subtract that running sum value from the value in the value column.

Based on this table definition:

CREATE TABLE tbl (  -- no PK?
  id text NOT NULL
, creation timestamp UNIQUE NOT NULL
, operation text NOT NULL
, value numeric NOT NULL
, running_sum numeric  -- optional (not needed for task)
);

Data types and constraints are almost always essential.
(creationdoes not strictly have to be unique. But if there can be duplicate values per group (id), you need to do more.)

Basic query to compute your special running sum

SELECT id, creation, operation, value
     , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
FROM   (
   SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                      OVER (PARTITION BY id ORDER BY creation) AS run
   FROM   tbl
   ) t
ORDER  BY id, creation DESC;

Any operation name starting with 'override_op_' indicates the start of a new run (group, patch, partition).

In addition to the related answer you already linked to:

Calculating Cumulative Sum in PostgreSQL

Consider this related question for details how to partition rows into groups (run in this query, since you are using the term "group" for the id column)

Select longest continuous sequence

I use the new aggregate FILTER clause for the partial count:

Return counts for multiple ranges in a single SELECT statement

You could use the simpler (less clear) expression in older versions:

count(operation LIKE 'override_op_%' OR NULL)

Compute delta

Based on this, you can easily compute the desired delta:

SELECT *
     , running_sum - lag(running_sum, 1, numeric '0')  -- data type must match!
                     OVER (PARTITION BY id ORDER BY creation) AS transformed_value
FROM (
   SELECT id, creation, operation, value
        , sum(value) OVER (PARTITION BY id, run ORDER BY creation) AS running_sum
   FROM   (
      SELECT *, count(*) FILTER (WHERE operation  LIKE 'override_op_%')
                OVER (PARTITION BY id ORDER BY creation) AS run
      FROM   tbl
      ) t
   ) t
ORDER  BY id, creation DESC;

I use the 3-parameter form of the window function lag() to provide 0 (data type must match!) as default value for the first row in the table.


sql error 1241 operand should contain 1 column This is my query

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1,player2,player3,player4,player5,player6,player7,player8
                    from team
                    where sap='60003100009')
order by price desc;

It works fine without the pname not in ... clause.

I can't figure out whats wrong cause syntactically it's correct as mysql doesn't show any errors.

You cannot have multiple columns being returned in a subquery like that, so you have several ways that you would have rewrite this query to work.

Either you can unpivot the data in the team table so you are only returning one column:

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1
                    from team where sap='60003100009'
                    union all
                    select player2
                    from team where sap='60003100009'
                    union all
                    select player3
                    from team where sap='60003100009'
                    union all
                    select player4
                    from team where sap='60003100009'
                    union all
                    select player5
                    from team where sap='60003100009'
                    union all
                    select player6
                    from team where sap='60003100009'
                    union all
                    select player7
                    from team where sap='60003100009'
                    union all
                    select player8
                    from team where sap='60003100009')
order by price desc;

Or you can use a NOT EXISTS query:

select *
from players p
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and not exists (select *
                  from team t
                  where sap='60003100009'
                    AND
                    (
                      p.pname = t.player1 OR
                      p.pname = t.player2 OR
                      p.pname = t.player3 OR
                      p.pname = t.player4 OR
                      p.pname = t.player5 OR
                      p.pname = t.player6 OR
                      p.pname = t.player7 OR
                      p.pname = t.player8
                    ))
order by price desc;

Or you would have to use multiple WHERE filters on the player name:

select *
from players
where sport='football'
  and position='DEF'
  and pname!='Binoy Dalal'
  and pname not in (select player1
                    from team where sap='60003100009')
  and pname not in (select player2
                    from team where sap='60003100009')
  and pname not in (select player3
                    from team where sap='60003100009')
  and pname not in (select player4
                    from team where sap='60003100009')
  and pname not in (select player5
                    from team where sap='60003100009')
  and pname not in (select player6
                    from team where sap='60003100009')
  and pname not in (select player7
                    from team where sap='60003100009')
  and pname not in (select player8
                    from team where sap='60003100009')
order by price desc;

However, ideally you should consider normalizing the team table so you have one column with the player name and another column that assigns them a player number. Similar to this:

create table team
(
  player varchar(50),
  playerNumber int
);

Then when you are searching the team data you only have to join on one column instead of 8 different columns.


Package sttill used? Is there a way to know if a package is still used ? (I tried with RedGate SQL Search and I found those in SERV-XXX and Maintenance Plans in various Job Steps, but for those in Data Collector I got nothing)

Nope. Unless you explicitly enabled logging within each and every SSIS package, there is no guaranteed way to know if it's being run.

You can look through SQL Agent job steps, check for xp_cmdshell calls that either use dtexec.exe or call batch scripts which then use dtexec.exe and then discover some clever sod wrote a windows service that uses the SSIS object model to run them. Or it's the unscheduled process that is only run when Accounting says the books are closed at month/quarter/year end and only then is it run.


Evan explains why the generate_series() cannot do by itself what you want. But it can be done with a simple UNION:

select generate_series('2016-06-25'::timestamp, '2016-12-15', '1 month')
         as g_date
union
select '2016-12-15'
-- order by g_date
;

or UNION ALL:

select generate_series('2016-06-25'::timestamp,
                       '2016-12-15' - interval '1 day',
                       '1 month') as g_date
union all
select '2016-12-15' ;

Also note that generate_series() output is of timestamp without
timezone type. if you want a date type, you need to cast:

select generate_series('2016-06-25'::timestamp,
                       '2016-12-15' - interval '1 day',
                       '1 month')::date as g_date
union all
select '2016-12-15'::date ;


InfluxDB - How can I limit the measures that a user can read by their tags? I have some data that is collected by several sensors. Some users may own many of these sensors. I want to keep all the measures in a single database in InfluxDB. To differentiate the sensors I pretend to add some tags (such as 'sensor_id=aaa', 'sensor_id=bbb', etc).

I have read the documentation and it seems that there is no security option nor anything similar that allows me to restrict any given user to the sensors that he owns or has permissions to read.

Version 0.8 has this option that seems to do what I need. In version 0.9 (which I'm using) I can't find anything similar.

I want to use Grafana to allow people to create some dashboards, that's why I need InfluxDB, but I'm stuck on this part.

Does some configuration/feature exist that helps me to do this? Should I try a different approach? Any suggestions? Is there some other tool that would be more helpful in my case?

Thanks.

First and foremost, I would suggest moving away from any version of InfluxDB before 1.0 as there have been a number of improvements to InfluxDB since 0.9.

At the moment there is no way to limit read and write access for a particular tag. This functionality is a planned feature in version 1.2 of Enterprise InfluxDB.

That being said there is one alternative implementation that I see as feasible. OSS InfluxDB allows administrations to set database level read and write permissions. You could use this to your advantage. Rather than have a single database and setting permissions on the tags within that database, you'd have a database for each user and give each user read and write access to that database.

One issue that comes to mind with this approach is that if you have 1000s of users, you'll end up having 1000s of databases. This may cause some weirdness within InfluxDB itself. If you did have 1000s of users, you could simply partition your users across multiple InfluxDB instances.


Is it possible to pick next row's cell value on same column if condition not matched? I have a table flaggedcomment and a table price.

In flaggedcomment, there are two empty columns Price_DateTime and Price_Open which I want to copy from price, depending on the ON conditions. Query as below.

UPDATE flaggedcomment as t1
        LEFT JOIN price as t2
             ON t1.Ticker_Ticker_ID = t2.Ticker_Ticker_ID AND t1.Comment_DateTime = t2.Price_DateTime
SET t1.Price_DateTime = t2.Price_DateTime, t1.Price_Open = t2.Price_Open

This query will leave some cells with null value because there's no match between t1.Comment_DateTime and t2.Price_DateTime.

Is there a way to "pick" the next available Price_DateTime if there's no match found?

For example, if the value of t1.Comment_DateTime is 2014-09-29 20:44:00 but it's not exist in t2.Price_DateTime, can I pick the next row's Price_DateTime value, e.g. 2014-09-30 08:00:00?

I am new to deal with MySQL queries and I've tried my best in explaining what I want to do. If I didn't explain my question well, please let me know and I will try to explain it further.

Any help would be much appreciated. Thank you!

I assume you mean you want to find the "first" row with same Ticker_ID and same or bigger DateTime, first when ordered by DateTime ascending. One way to do this, assuming that the price table has a unique constraint on (Ticker_Ticker_ID, Price_DateTime):

UPDATE flaggedcomment AS fc
    INNER JOIN price AS p
        ON  p.Ticker_Ticker_ID = fc.Ticker_Ticker_ID
        AND p.Price_DateTime =
            ( SELECT pi.Price_DateTime
              FROM price AS pi
              WHERE pi.Ticker_Ticker_ID = fc.Ticker_Ticker_ID
                AND pi.Price_DateTime >= fc.Comment_DateTime
              ORDER BY pi.Price_DateTime ASC
              LIMIT 1
            )
SET
    fc.Price_DateTime = p.Price_DateTime,
    fc.Price_Open = p.Price_Open ;


Getting error while installing log4db2 utility DB21034E  The command was processed as an SQL statement because it was
  not a valid Command Line Processor command.  During SQL processing it
  returned: SQL0601N  The name of the object to be created is identical
  to the existing name "LOG_CONF_BP" of type "BUFFERPOOL".
  SQLSTATE=42710

How to remove pre-existed "LOG_CONF_BP" of type "BUFFERPOOL" in db2 database? Is there any other way to define logging for my stored procedures?

LOG_CONF_BP is a bufferpool. That is a chunk of memory dedicated to doing something (usually servicing one or more tablespaces, which is where tables reside). So what is happening is your stored procedure is trying to create an object that already exists.

So what is going to need to change is the stored procedure. I would ask if your stored procedure needs that exact bufferpool or not. If yes, then change the stored procedure to check for the existence of LOG_CONF_BP (you can check against syscat.bufferpools for that). If it doesn't exist, create it, otherwise don't.

Otherwise, even if you drop the LOG_CONF_BP, so your stored procedure succeeds, it will just fail again the next time you run it anyway....


Checking for BCNF in a relational database through FDs? Okay so I know that for BCNF, when listing all non-trivial FD's that everything to the left has to be a candidate key.

So I have this relation:

Person(Id, TFN, Name, Phone)

Where Id and TFN(Like SSN) are both Primary Keys.

So the minimal FDs I have is:

ID->TFN
ID->Name
ID->Phone
(TFN->ID is redundant right?)

So it looks to be in BCNF but I was thinking, does Phone->Name? Because Phone would be unique and point to a Name too, along with that a person could have more than 1 phone number, is that the right approach? Given that is true, it would not be in BCNF because the left Phone is an attribute correct? Or at the very least would Phone be considered a candidate key? But if it is a candidate key, it is on the left and therefore still BCNF.

I'm going around in circles with this thought process..

So if that is all correct then the way to decompose the Person relation would be to remove phone and make a new relation

PersonPhone(PhoneNo, id*)

Where id is a foreign key on Person. Then that would make Person BCNF?

Thanks

First of all, you say:

  (TFN → ID is redundant right?)

This dependency is not redundant, since otherwise you do not know that TFN is a key, and you lose an essential information. So you must include it in the set of functional dependencies.

For phone, if each phone is uniquely associated to a person, and each person can have only one phone, then you have these functional dependencies:

ID → TFN
ID → Name
ID → Phone
TFN → ID
Phone → ID

There are three keys, ID, Phone and TFN, and the relation is in BCNF.

However, if each person can have more than one phone, than the situation is completely different. You have this set of functional dependencies:

ID → TFN
ID → Name
TFN → ID
Phone → ID

the key now is only Phone, and the relation has redundancies (since for each phone related to the same person, you have to repeat the ID, TFN, and Name of that person). The relation is not in BCNF, and to bring it in such form you have to decompose it in the two relations:

R1(ID, TFN, Name)
R2(Phone, ID)

the first with the dependencies:

ID → TFN
ID → Name
TFN → ID

and the second one with the dependency:

Phone → ID

so that no dependency is lost with the decomposition.


In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller? In MySQL Workbench 6.1 (OS X), can you make the Result Grid taller?

I know the region expands to fill the space vacated by Action Output/Text Output/History, should you choose to hide that.

But I'd like to shrink the space used by the Queries (i.e. "Query 1") and make the Result Grid taller (at the expense of the Queries tabs). Can it be done?

You mean like this:

Place your mouse on the line between the grid toolbar and the editor bottom. It's only a few pixel so watch out. The cursor image should change to a split cursor and you can drag that line to resize both, editor and grid. On Mac the split cursor sometimes does not appear, but otherwise it still works moving the split position.


How To Sum A Varchar Data Type In SQL Server 2008 I have a table with similar structure.  Unfortunately changing the data structure is not an option, so I am stuck with attempting to come up with an alternative.  I have tried to run this query, but I get an error of:

  (5 row(s) affected)
  Msg 8114, Level 16, State 5, Line 9
  Error converting data type varchar to numeric.

Here is DDL - what syntax should be changed in the query in order for me to receive valid output and not an error message?

Declare @GreenHouse Table
(nomen varchar(100), vtc varchar(100), d1 date)

Insert Into @GreenHouse (nomen, vtc, d1) VALUES
('Green', '507.02', '2016-01-14'), ('Green', '4.44089e-015', '2016-01-03')
,('Green', '200.57', '2016-01-18'), ('Green', '649.01', '2016-01-19'),
('Green', '1849.85', '2016-04-30')

Select nomen, Round(Sum(Cast(vtc As Decimal(10,2))),0.00) As FormatedInfo, d1
FROM @GreenHouse Group By nomen, d1 Order By d1 DESC

The 4.44089e-015 is most likely giving you the error by trying to convert it to a decimal. Try using float instead:

SELECT  nomen,
        ROUND(SUM(CAST(vtc AS float)),0.00) FormatedInfo,
        d1
FROM @GreenHouse
GROUP BY nomen, d1
ORDER BY d1 DESC;

The results with your sample data are:

╔═══════╦══════════════╦════════════╗
║ nomen ║ FormatedInfo ║     d1     ║
╠═══════╬══════════════╬════════════╣
║ Green ║         1850 ║ 2016-04-30 ║
║ Green ║          649 ║ 2016-01-19 ║
║ Green ║          201 ║ 2016-01-18 ║
║ Green ║          507 ║ 2016-01-14 ║
║ Green ║            0 ║ 2016-01-03 ║
╚═══════╩══════════════╩════════════╝

(yeah, I know that it converts the 4.44089e-015 value to 0, but it is expected for a float)


query to get list of tables that has same columns from information_schema.columns? idea is to list all the tables that has redundant columns across database using information_schema.columns!
let's say

table1 = dbo.ABC
columns = id, name, department, designation

table2= dbo.DEF
columns= department, permissions, cube

table3= dbo.GHI
columns= street, zip, city, state

the query should list the tables 'dbo.ABC'  and 'dbo.DEF'

something like this:

select distinct c1.table_schema||'.'||c1.table_name
from information_schema.columns c1
where exists (select 1
              from information_schema.columns c2
              where c2.table_name <> c1.table_name
                and c2.column_name = c1.column_name
                and c2.table_schema = c1.table_schema);


How to set ALL the replication to DEADLOCK_PRIORITY LOW? I have just got this email here:

Then looking at the sql server log I find this deadlock was caused by one of our procedures (that was the victim) against the replication.

O.K. in this case in particular I noticed that this specific procedure could be tuned, I even found a missing index, as you can see below.

But this same situation has happened so many times, I would have had the procedure won, the replication was the victim, and I would deal with the procedure later on.

How can I put this:

SET DEADLOCK_PRIORITY LOW

everywhere, in every replication transaction?

this is to check it out:

    SELECT session_id,deadlock_priority
FROM sys.dm_exec_sessions
    WHERE SESSION_ID = @@SPID

How

After having a good setup and monitoring the tempdb
we opted and used the READ COMMITTED SNAPSHOT as described in the comments.
How to implement Read Committed Snapshot and how it is different to Snapshot has been nicely described (including links explaining how to set it up) here.

In addition, READ_COMMITTED_SNAPSHOT (henceforth called RCSI) will
only give you some properties of optimistic concurrency. RCSI and
Snapshot (SI) differ in three main ways:

RCSI will be used immediately by all RC isolation queries.
RCSI provides a point-in-time (snapshot) view of the database for a single statement.
SI provides a point-in-time view for an entire transaction.
SI can have write conflicts.

also quoted from the same source:

Other useful details relating some gotchas and caveats when using
RCSI/SI can be found in these articles:

Implementing Snapshot or Read Committed Snapshot Isolation in SQL Server: A Guide
Read Committed Snapshot Isolation: Writers Block Writers (RCSI)


Postgresql 11.5 installation problem While installing PostgreSQL I keep getting the message:

  "Problem running post-install step. Installation may not complete
  correctly  The database cluster initialisation failed."

This seems to prevent me from using PGadmin correctly.

In the install log I have found the following section which seems to indicate where things went wrong.

I haven't been able to find a solution to this problem.

Executing batch file 'rad7C33E.bat'...
    The program "postgres" was found by "C:/Program Files/PostgreSQL/11/bin/initdb.exe"
but was not the same version as initdb.
Check your installation.

Called Die(Failed to initialise the database cluster with initdb)...
Failed to initialise the database cluster with initdb

Script stderr:
 Program ended with an error exit code

Error running cscript //NoLogo "C:\Program Files\PostgreSQL\11/installer/server/initcluster.vbs" "NT AUTHORITY\NetworkService" "postgres" "****" "C:\Users\JesseDanckaarts\AppData\Local\Temp/postgresql_installer_6f373c7c91" "C:\Program Files\PostgreSQL\11" "C:\Program Files\PostgreSQL\11\data" 5432 "Dutch,Netherlands" 0: Program ended with an error exit code
Problem running post-install step. Installation may not complete correctly
 The database cluster initialisation failed.
Executing icacls "C:\Users\JesseDanckaarts\AppData\Local\Temp/postgresql_installer_cb3c83cf49" /inheritance:r
Script exit code: 0

I have tried the following without luck so far:

https://wiki.postgresql.org/wiki/Running_%26_Installing_PostgreSQL_On_Native_Windows#Common_installation_errors
https://webkul.com/blog/postgresql-windows-installation-problem-running-post-install-step-installation-may-not-complete-correctly/
https://www.2ndquadrant.com/en/blog/postgresql-cluster-initialisation-failures-no-error-windows/

and many more!

If anyone knows what could be the problem I'd be very grateful!

Cross posting from StackOverflow:

I've just struggled with this for several hours, so I'm posting this for anyone else who winds up here.

Initially, all I could find were two bug reports on the pgsql-bugs mailing list:

BUG #15856: The program "postgres" was found by "initdb" but was not the same version as initdb.

BUG #15970: Db initialization error - initdb.exe and postgres not same version

The specific symptoms are:

The program "postgres" was found by ".../initdb.exe" but was not the same version as initdb.

and although the versions match, you get this:

C:\Program Files\PostgreSQL\11\bin>postgres -V
WARNING:  01000: could not determine encoding for locale
"<some encoding>.utf8": codeset is "CPutf8"

If these are your symptoms, the issue is that you have your region/language settings set to use UTF-8 (beta setting).  This causes problems with lots of programs, and PostgreSQL is one of them.  Disable this and re-install and you should be fine.


Auditing of database connections in SQL Server 2008 R2 We are finally planning to close down our last SQL Server 2008 R2 server. Before we can proceed with this we need to track what users are connecting to which databases. We have tried different options like using SQL Audit and checking the logins in the error log but all of these are missing the information to which database the connection are made. We did also look into using the LOGON trigger but as it is triggered when the user logins to the instance it will not track if the user afterwards change the database.
In newer versions I have used Extended Events for this kind of auditing but the sqlserver.login events is not yet available in this database version. It seems like it should be possible to use Profiler for this but I'm worried about continuously running trace specially on this old server. Are there any other options that I have missed or is Profiler the only option?

I faced a similar problem a couple of years ago and created a SQL Agent job to run the query below every few minutes. The query polled
sys.dm_exec_sessions, sys.sysprocesses, sys.dm_exec_connections
it's a bit rough and ready but it met my purposes and might help you too
CREATE TABLE [dbo].[log_db_connections](
[Database] [nvarchar](128) NULL,
[Login] [nvarchar](128) NOT NULL,
[nt_user_name] [nvarchar](128) NULL,
[LoginTime] [datetime] NOT NULL,
[CollectionTime] [datetime] NOT NULL CONSTRAINT [DF_CollectionTime] DEFAULT GETDATE(),
[Program] [nvarchar](128) NULL,
[client_interface_name] [nvarchar](32) NULL,
[Host] [nvarchar](128) NULL,
[client_net_address] [varchar](48) NULL,
[client_tcp_port] [int] NULL,
[net_transport] [nvarchar](40) NOT NULL,
[net_packet_size] [int] NULL,
[protocol_type] [nvarchar](40) NULL,
[endpoint_id] [int] NULL,
[encrypt_option] [nvarchar](40) NOT NULL,
[auth_scheme] [nvarchar](40) NOT NULL,
[num_reads] [int] NULL,
[num_writes] [int] NULL
) ON [PRIMARY]
GO

-- Create Unique Clustered Index with IGNORE_DUPE_KEY=ON to avoid duplicates
CREATE UNIQUE CLUSTERED INDEX [CIX_log_db_connections] ON log_db_connections
(
    [Database] ASC,
    Login ASC,
    Host ASC,
    Program ASC,
    LoginTime ASC
) WITH (IGNORE_DUP_KEY = ON)

INSERT [dbo].[log_db_connections]
(
[Database],
[Login],
[nt_user_name],
[LoginTime],
[Program],
[client_interface_name],
[Host],
[client_net_address],
[client_tcp_port],
[net_transport],
[net_packet_size],
[protocol_type],
[endpoint_id],
[encrypt_option],
[auth_scheme],
[num_reads],
[num_writes]
)
SELECT
DB_NAME(p.dbid) ,
s.login_name ,
nt_user_name,
s.login_time as LoginTime,
s.[program_name] as Program,
s.client_interface_name,
[host_name] as Host,
c.client_net_address,
c.client_tcp_port,
c.net_transport,
c.net_packet_size,
c.protocol_type,
c.endpoint_id,
c.encrypt_option,
c.auth_scheme,
c.num_reads,
c.num_writes
FROM sys.dm_exec_sessions s
inner join sys.sysprocesses p on
s.session_id = p.spid
inner join sys.dm_exec_connections c on
s.session_id = c.session_id
WHERE s.[program_name] not like 'Microsoft SQL Server Management Studio%'


Database schema size result differs from file size when exported I used this query to find out my database size

SELECT table_schema "Data Base Name",
sum( data_length + index_length ) / 1024 / 1024 "Data Base Size in MB"
FROM information_schema.TABLES GROUP BY table_schema ;

it returned as 849MB but when I exported the database it was only 501MB.

Being that there seemed to be over 300MB of info missing I decided to export in parts all the a,b,c etc, once done the total size of the folder the parts where stored in it was 606MB

This was better 100MB more but that still leaves around 200MB unaccounted for, I didn't export as zip or gzip archive just straight .sql file so really it should not differ and when the difference is over 200MB I'm thinking there is something wrong but I haven't a clue what or why each try returns a different size ...

...any suggestions on why this is happening?

The export does not contain the indexes. It only contains the definition of the indexes but not the data that is stored in there.

Additionally the size of SQL export will also not reflect the size of your data (just think of the SQL keywords that are needed for the SQL export).

When you move data from one representation to another (MySQL "on-disk" format -> Binary Export -> SQL Script) it is to be expected that the different formats show a different size.

But I don't understand what you problem actually is.


Representing lists in SQL My database setup is modern (My)SQL.
I've got a Content Table, including ids.
Now, users should be able to comment on content.
To achieve this, arrays have come to my mind.
Now I see several options of representing them :

A second table Comments with Content id and Comment
One new table for each Content, to store the comments(advantage : no need to store content id again for each comment)
A Text/String column, using a certain separator to seperate comments & comment infos
Same as above, but using a BLOB

Which one should I use ? What other options haven't I thought of ? Thanks for taking your time to answer !

In terms of relational databases each table represents a type. Table Content contains the items of content type. Table Comment contains the items of comment type etc. There is no strict requirement like "normalization" to keep all the items of the same type in the same table. Even more - sometimes you have to do that for sake of performance. But having multiple tables of the same type requires all that tables to be modified simultaneously. As of experience that requirement is very easy to violate by mistake. Now I'm trying to avoid this at any cost.

Sure if you have to get "the latest comment for [some|all] content" frequently the table of the same structure as Comment but with UNIQUE constraint combined with INSERT .. ON DUPLICATE KEY UPDATE .. (often acronymed by IODKU) is way more faster than any joined subqueries. But this is the special case.

So in my opinion the first option is much better than second.

And the third and fourth options requires some redundant homemade storage engine over the relational database that should parse string or blobs for fetching the data. This approach insults not only performance but also the reliability and maintanability of the storage. This is the sort of the bad design that should be avoided.


Data Files Outgrowing Hardware - Split the File? Where I'm currently working, the primary business database is located on a virtual server (SQL Server 2005 standard). The temp DB and logs are on one disk, the database itself on another. The SAN is configured in such a way that they can only provision 512GB disks (4K blocks I'm told, but I nothing about SANs). There is about 4% free space on the disk that holds the DB.

I was tasked with working a way to archive off old data and purge it from the database into one year chunks for archive to tape (I'm a contractor and really I'm a developer although I have dabbled in DBA world in the past and have 15 or so years experience developing with and for SQL Server, not a DBA, and it was handed to me as soon as I walked in the door. We do have a DBA so I don't know why I'm doing this but never mind). That was about 6 months ago and since then I have, on and off, built something which will do just that. But I am increasingly concerned that we cannot possibly test the resulting database enough to verify that we really haven't broken something and I've been casting my mind around for an alternative solution.

I first thought about partitioning but that's out because we have SE and no chance of upgrading to EE as in 6 or so months the whole shebang will move to a dedicated data centre with larger, more modern infrastucture and the whole system will be replaced by new developments in the next 2-4 years. But then it occurred to me that surely we can split the database into multiple files within a filegroup and spread them across multiple disks (we have no problem provisioning more 512GB disks). This leaves the entire DB intact and may even have a slight performance benefit, although that isn't the primary objective here.

I'm pretty sure this is OK in SE (again, I'm not a DBA) and I see this as far less risky and potentially quicker to implement. so my questions:

Is it possible to have a database split across multiple files in 2005 SE?
Is it possible to split an existing DB that is currently in one file on a single disk into multiple files across multiple disks?
Do I even need to present any more than this information to convince any sane person that it would be foolish to risk the disruption to a multi-million pound business of purging data from a production database?

1) Yes

2) Yes,

After creating the new files, you'll need to drop and re-create the clustered indexes  of the target tables, moving them on to the new filegroup to move the data:

CREATE CLUSTERED INDEX CIX_YourTable
ON dbo.YourTable(YourClusteringKeyFields)
WITH DROP_EXISTING
ON [filegroup_name]

3) If you can get the DBA to back you up on this solution, I think you'll be OK. Based on your limitations this seems like a perfectly reasonable move - but remember to backup before trying to move the data!


Strange MySQL database that I can't select I am investigating a web site server (CentOS release 6.3) that runs the traditional lamp stack;

I am getting the following output my MySQL;

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| #mysql50#.config   |
| webtestdb          |
| mysql              |
| test               |
| websitedb          |
+--------------------+
6 rows in set (0.00 sec)

What is "#mysql50#.config"? Also, I can't select it;

mysql> use #mysql50#.config;
    -> ;
ERROR:
USE must be followed by a database name
mysql> use \#mysql50\#.config;
No connection. Trying to reconnect...
Connection id:    1707
Current database: *** NONE ***

ERROR 1049 (42000): Unknown database 'mysql50.config'
mysql> use `#mysql50\#.config`;
ERROR 1102 (42000): Incorrect database name '#mysql50#.config'

Does this resemble some sort of crashed database, or a temporary database from a previous import or export than has been left behind by mistake?

mysql> SHOW VARIABLES LIKE "%version%";
+-------------------------+---------------------+
| Variable_name           | Value               |
+-------------------------+---------------------+
| protocol_version        | 10                  |
| version                 | 5.1.67-log          |
| version_comment         | Source distribution |
| version_compile_machine | x86_64              |
| version_compile_os      | redhat-linux-gnu    |
+-------------------------+---------------------+
5 rows in set (0.00 sec)

This is nothing but a folder/file in database directory.

The default location for data directory is /var/lib/mysql on ubuntu.Go to that location(datadir) and find what is there in that folder if it not your database please move that to some another place.

If you need to find the location of data directory issue command on MySQL shell

SHOW VARIABLES LIKE 'datadir';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| datadir       | /db-data/mysql/ |
+---------------+-----------------+
1 row in set (0.00 sec)

Which will give you the location of data directory


Design for periodic data cuts I've been building an application that has a 6-monthly update schedule, i.e. a cut of the data for H1 & H2 of each year. The previous version of this application implemented this by making a copy of an MS Access database and starting again from scratch. Obviously this leads to multiple copies of a database lying around a network, with all the attendant problems you'd expect.

The new design includes a Dataset table, which holds a list of data cuts (e.g. 2014 H1, 2014 H2... etc.).

What is the best way to implement this into the larger design? I can think of two ways to do it:

1 - Add a 'Dataset' field to all fact table records, i.e. prices, volumes etc. are associated with a particular reporting period.

2 - Add a new table acting as a junction between dimension tables and the data cut, i.e. each asset (e.g. product, stock item etc.) in the database is assigned a new Id for each cut.

I can see benefits and drawbacks to each approach: then first leads to a more complex key in many tables, and the second leads to more tables but potentially greater efficiency.

Is there a standard method/best practice of implementing this, or is there a better approach entirely that I'm unaware of?

Your question implies that you're using a Kimball Star Schema approach.  Option 1 implies a star schema while option 2 implies a more snowflaked design.  Kimball argues against snowflaking your design.

Therefore, go with option one.  It will be easier for your report writers long term and newer in memory database technologies will take advantage of it.  While you're implementing option one, switch to a surrogate key instead of the multi-field key you're concerned about creating.


Restore Database with Multiple Restore commands For automation, I have to dynamically construct the restore commands and thus I have created a script. A .bak file is created on a remote server and I receive it daily. The script builds the restore string and executes it.

Only hindrance so far is I'm unable to restore the db on multiple steps, because the back up file comes from an external source and fails with  "Directory lookup for the file "xxxxxx.ndf" failed with the operating system error 3 (The system cannot find the path specified.)." It is the folder where the backup resides on the remote server.

Basically the script looks like:

RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'a' TO 'E:\x.mdf', NORECOVERY
RESTORE DATABASE x FROM DISK = 'E:x.bak'  WITH MOVE 'b' TO 'E:\x1.ndf', NORECOVERY
RESTORE LOG x      FROM DISK = 'E:\x.bak' WITH MOVE 'c_log' TO 'E:\x.ldf',NORECOVERY
RESTORE DATABASE x WITH RECOVERY

How can I achieve such a scenario?

The following code will restore the database to the desired location:

RESTORE DATABASE x
FROM DISK = 'Filepathofbackupfile.bak'
WITH RECOVERY,
MOVE 'logicalDatafilename' TO 'Newfilepath.mdf',
MOVE 'logicalLogfilename' TO 'Newfilepath.ldf'


Are CREATE INDEX statements replicated in row-based Mysql Replication? I've not been able to find a simple explanation of what kinds of statements are or are not replicated from a master db to a slave in MySQL, row-based replication.  Specifically I want to add a fulltext index to a table and am wondering if i'll have to do that on each slave too or if that will get replicated.

DDL can still replicate with row-based replication

From the MySQL Documentation for binlog-do-db

  For example, DDL statements such as CREATE TABLE and ALTER TABLE are always logged as statements, without regard to the logging format in effect

Since CREATE INDEX is mapped to ALTER TABLE, CREATE INDEX will work just fine.