Rollback to savepoint with repeatable read isolation I want to use savepoints to do subtransactions that I can rollback and retry within a transaction.

I have the following scenario when using repeatable read isolation on PostgreSQL 11.

START TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Another transaction updates this record.
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails (as expected) with a serialization error.

-- Rollback and try again.
ROLLBACK TO SAVEPOINT x;
SELECT * FROM customer WHERE id = 1;
-- Difficult application logic goes here to determine values for UPDATE.
UPDATE customer SET ... WHERE id = 1;
-- This fails unexpectedly with a serialization error.

My understanding is that rolling back to a savepoint restores the transaction to the state it was in when the savepoint was created.

I expected that I could ROLLBACK TO SAVEPOINT x and retry the SELECT/application logic/UPDATE cycle. However the SELECT still returns the old data and I still get a serialization error even though the transaction had not seen the relevant record before the savepoint.

Of course, one could also argue that as we are still in the same transaction and we are using repeatable read isolation, the database should return the old data and thus fail the UPDATE with a serialization error.

I would like to know if this a feature or a bug? Is this behavior per the standard or is it PostgreSQL specific?

In PostgreSQL, a REPEATABLE READ transaction uses a fixed snapshot of the database.

So if a concurrent data modification caused a serialization error, then restoring to a savepoint and repeating the actions will always result in the same serialization error. You will still see the same state of the database, and if you update a row that has been modified after the snapshot was taken, that will still be the case.

You actually have to roll back and retry the whole transaction.

This is working as designed.

The SQL standard doesn't describe when exactly serialization errors should be thrown, because it does not deal with the implementation specifics of individual RDBMS. It says:

  The execution of a <rollback statement> may be initiated implicitly by an SQL-implementation when it detects
  the inability to guarantee the serializability of two or more concurrent SQL-transactions. When this error occurs,
  an exception condition is raised: transaction rollback – serialization failure.

So a database is at liberty to throw a serialization error whenever it thinks fit.


How to use 'mysql -uroot -pXXXX' on Debain 9.8? My apologies for this lame question. Debian 9.8 changed some things and mysql is broke but I can't seem to fix it.

I installed MySQL on Debian 9.8, which now installs MariaDB 10.1. After install I ran mysql_secure_installation and set the root password, removed temporary databases and dis-allowed anonymous users. I also added my user to the mysql group, logged off and then logged back in.

The following fails on Debian with MariaDB 10.1, but works on Fedora 29 with MariaDB 10.1:

$ mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
$ sudo mysql -uroot -pXXXXXXXXXXXXXXXXXXXXXXXX
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)

I thought the problem may be How to allow user to login to MySQL? so I used sudo su - to elevate, and then logged in to MySQL and executed the GRANT statements for root.

The Stack Overflow fix did not fix the problem. In fact I cannot seem to login from root now. Sigh...

I now think the problem may have something to do with this MariaDB knowledge base, but I don't know how to fix it. Secure passwordless root accounts only on new installs:

  Secure passwordless root accounts only on new installs

  Unlike the old MySQL packages in Debian, MariaDB 10.0 onwards in
  Debian uses unix socket authentication on new installs to avoid root
  password management issues and thus be more secure and easier to use
  with provision systems of the cloud age.

  This only affects new installs. Upgrades from old versions will
  continue to use whatever authentication and user accounts already
  existed. This is however good to know, because it can affect upgrades
  of dependant systems, typically e.g. require users to rewrite their
  Ansible scripts and similar tasks. The new feature is much easier than
  the old, so adjusting for it requires little work.

So they document they've done something but they don't document how to fix it. Sigh...

How do I run mysql from the command line with the root user and password? What needs to be fixed on Debian 9?

If you do sudo su - to root, are you able to log in without a password? If not, you can always log in by skipping the grant tables. See e.g. step 2 and 3 in this tutorial.

Once inside, you need to change the authentication plugin back to 'mysql_native_password' for the root@localhost user:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');

See documentation on this here.


Accessing impact of deleting ibtmp1 file in MYSQL There is a file called ibtmp1 and it is consuming 138 GB. I understand, this stores uncompressed data. Do we really need these files or can we delete it? if deleting is fine, then could you please share the command or the process. Please note that I am using Windows system and MySQL Server version is 5.6

thank you.

That file comes from MySQL 5.7, not 5.6. You can check by connecting to MySQL and running:

mysql> SELECT VERSION();

Shrinking ibtmp1 would require shutting down MySQL. You cannot just the delete the file because there will be a open file handle on that file coming from mysqld.exe.

First, log into MySQL and run:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

Next, shutdown MySQL, delet the file and start mysql back up:

C:\> net stop mysql
C:\> del ibtmp1
C:\> net start mysql

If your ibtmp1 grew that much, then you have been running some bad queries that create large temp tables.


How to convince Oracle to create optimal execution plans in SQL queries with bind variables? An attempt to parameterize a query in Oracle using bind variables and OR :B1 IS NULL or NVL(:B1,col1) in a WHERE clause leads to suboptimal execution plans.
For example, I'd like to write SELECT like this:
SELECT * FROM MY_TABLE
WHERE (COL1 = :B1 OR :B1 IS NULL) AND (COL2 = :B2 OR :B2 IS NULL) AND (COL3 = :B3 OR :B3 IS NULL);

Its execution plan should depend on the values of the bind variables, bearing in mind that Oracle CBO creates optimal execution plans for each.
As shown in the following execution plan examples, using OR FROM NULL or NVL results in table level filter predicates instead of index level access predicates.
Is there any other way to parameterize SQL query in Oracle?
Test 1
select * from employees where employee_id = :B1;

Plan hash value: 1833546154

---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |     1 |    69 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=TO_NUMBER(:B1))

Test 2
select * from employees where (employee_id = :B1 OR :B1 IS NULL);

Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     6 |   414 |    35   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     6 |   414 |    35   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(:B1 IS NULL OR "EMPLOYEE_ID"=TO_NUMBER(:B1))

Test 3
select * from employees where employee_id = NVL(:B1,employee_id);

Plan hash value: 71496665

--------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |   108 | 14364 |    36   (0)| 00:00:01 |
|   1 |  VIEW                          | VW_ORE_B4851255 |   108 | 14364 |    36   (0)| 00:00:01 |
|   2 |   UNION-ALL                    |                 |       |       |            |          |
|*  3 |    FILTER                      |                 |       |       |            |          |
|   4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES       |     1 |    69 |     1   (0)| 00:00:01 |
|*  5 |      INDEX UNIQUE SCAN         | EMP_EMP_ID_PK   |     1 |       |     0   (0)| 00:00:01 |
|*  6 |    FILTER                      |                 |       |       |            |          |
|   7 |     TABLE ACCESS FULL          | EMPLOYEES       |   107 |  7383 |    35   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter(:B1 IS NOT NULL)
   5 - access("EMPLOYEE_ID"=:B1)
   6 - filter(:B1 IS NULL)

Test 1 is logically incorrect for this use-case. If you provide NULL as the value of :B1, it will return 0 rows.
Test 2 is logically correct, but its execution plan is suboptimal, so is performance. In this case the optimizer is unable to utilize the index, because NULL values are not indexed by default.
Test 3 is logically correct, and its execution plan is optimal, and this should be used for a simple case like this. But be very careful with this.
employee_id = NVL(:B1,employee_id) was transformed into a union of two branches collecting results based on  employee_id = :B1 and :B1 is null. This is called NVL optimization and is displayed as an OR-expansion (ORE) in the execution plan. This is a nice little trick of the optimizer.
The problem is, this works properly only when you have 1 predicate like that. If you have more predicates like that, the optimizer will not do this trick for the remaining predicates, and it will not created additional branches for the UNION.
A query like this:
SELECT * FROM MY_TABLE
WHERE COL1 = NVL(:B1, COL1) AND COL2 = NVL(:B2, COL2) AND COL3 = NVL(:B3, COL3);

will not produce branches for all path. The optimizer will perform the NVL optimization for 1 predicate, then it will place the remaining predicates in the 2 branches created by the transformation of the first predicate. This means a plan generated from a query like that will never be optimal. This is something that causes a big share of SQL performance problems I encounter, and a not a single developer was aware of this so far. If you want to have optimal execution plans and performance, you should generate the simplest SQL statement on the fly instead of trying to cover all possible scenarios with a single SQL statement. For example, if there was no value provided for :B1, then simply do not include that predicate in the SQL statement.


Automatic Version increment on Data-Tier Applications In the project settings of a database project I can set the version number form my data-tier application, however setting this manually is a bit inefficient. I would like to automatically increment the build number, as with other VS projects. How can this be done ? At the same time I would like to see this version number in the generated dacpac file.

I found this forum thread had an answer that worked well: Automatic Version increment on Data-Tier Applications

Minimum steps are:

Create Properties\AssemblyInfo.cs if it doesn't already exist, and make sure it's included in the project and contains at least this code: using System.Reflection; [assembly: AssemblyVersion("1.0.*")] Or however you want your version number to go.
Edit the *.sqlproj file in an outside editor (notepad etc) and add the following XML:

    <Target Name="SetDacVersionToAssemblyVersion" AfterTargets="CoreCompile">
        <GetAssemblyIdentity AssemblyFiles="$(IntermediateTargetFullFileName)">
            <Output TaskParameter="Assemblies" PropertyName="IntermediateTargetAssembly" />
        </GetAssemblyIdentity>
        <PropertyGroup>
            <DacVersion>$(IntermediateTargetAssembly.Split(',')[1].Split('=')[1])
            </DacVersion>
        </PropertyGroup>
        <Message Text="DacVersion set to $(DacVersion)" Importance="high" />
    </Target>


Dynamically update JSONB rows - POSTGRESQL I am currently trying to update some JSONB fields that we have on our database, we are moving nodes from the JSON a level up for an specific field.
The node we are trying to move, however, is not fixed; sometimes its index can be 1, 2, or 3.
I currently have this query:
 UPDATE table1 t1 SET jsonbfield = jsonb_set(jsonfield ,
            '{field1,0,field2,0,configuration}',
            (select jsonbfield->'field1'->0->'field2'->0->'field3'->0->'configuration'
            from table1 where
            jsonbfield->'field1'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME'
            and id=t1.id), true)
            where jsonbfield->'field'->0->'field2'->0->'field3'->0->>'name'='SOME_NAME';

Which basically would achieve the requirement if the node we were trying to move was always at position 0, however, as mentioned, we noticed that the node we want to move up can be on another positon.
Is there a way to iterate trough each row in JSON, get the position of the node we want to move, save it into a variable and place it in the update statement?
I have tried with a do for, but it seems it doesn't interact well with select jsonb_array_elements.
Sample Data
{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{

                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "childStep":[
                  {
                     "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
                  }
               ]
            }
         ]
      }
   ]
}

This json represents the jsonbfield row from table1.
What we want to do is move the configuration Node CONFIG2 from childStep to Step Level, so it would look something like this after updating it:
{
   "parent":[
      {
         "steps":[
            {
               "name":"CONFIG1",
               "index":1,
               "childStep":[
                  {
                     "configuration":{

                     }
                  }
               ]
            },
            {
               "name":"CONFIG2",
               "index":2,
               "configuration":{
                         "configInfo":{
                           "info":"someinfo"
                        }
                     }
            }
         ]
      }
   ]
}

The query that i posted, already moves the field up, however, the issue resides on that we can not know if CONFIG2 will be located at index 0, 1, 2, etc.
So we are trying to figure a way to dynamically get where its located and then update it.
With the current query posted here, it assumes CONFIG2 is always located at index 1 (or 0, first position).

UPDATE table1
SET    jsonfield = (
   SELECT jsonb_set(jsonfield
                  , '{parent,0,steps}'::text[] || (idx - 1)::text
                  ,  val->'childStep'->0 || (val - text 'childStep')
                   )
   FROM   jsonb_array_elements(jsonfield#>'{parent,0,steps}') WITH ORDINALITY a(val, idx)
   WHERE  val->>'name' = 'CONFIG2'
   )
WHERE  jsonfield @> '{"parent":[{"steps":[{"name":"CONFIG2"}]}]}';
-- WHERE  jsonfield @? '$.parent[0].steps[*].name ? (@ == "CONFIG2")';  -- equivalent alternative

db<>fiddle here
I compute the new jsonb value in a correlated subquery.
WITH ORDINALITY produces the array index while unnesting the JSON array with jsonb_array_elements(). The later idx - 1 adjusts the off-by-1 error from Postgres arrays being 1-based and JSON arrays being 0-based. More about WITH ORDINALITY:

How to preserve the original order of elements in an unnested array?

I added an outer WHERE condition so that only applicable rows are processed to begin with. Avoid updating rows with unchanged values as this adds costs without benefits.
You can either use the contains operator @> or the new operator @? in Postgres 12 or later SQL/JSON path language. Either can use an index. See:

Update all values for given key nested in JSON array of objects

Index by subkey inside subkey inside array in jsonb


Sqlserver exec sp_execute to simple query? I use sqlserver 2012 profile to watch all query that run on my db.

I saw only exec sp_execute 2,55,88 or exec sp_execute 1,43,675and command like that.

Why I cant watch update/select queries?

The application that you are profiling is using prepared statements. The SQL statements are being sent as sp_prepare @handle OUTPUT, params, 'T-SQL statement' before the calls to sp_execute are executed. You need to also capture the "SQL:Prepare" event in order to see what statements are being submitted. But I am not sure if you get to see the returned handle (i.e. the statement_id) that is then passed in as the first parameter to sp_execute (example: the 2 in exec sp_execute 2,...). The handle is per each Connection / Session, so handle of 2 for Session 53 is not necessarily the same as handle of 2 for Session 78. Meaning, you need to match the session_id / SPID between calls to sp_prepare and sp_execute.

Please see the following documentation:

sp_prepare
sp_execute


Copy Millions of Records from one table to another with the same schema, in the same database I am trying to find the best way to copy a large amount of rows ( ~70million) from one table to another with the same schema inside the same database.

Our system currently runs with a set of A/B tables that are referenced elsewhere via a View. Our clients have the option to send us data either as a Full file or an Incremental file. When full files are ingested, the "inactive" table is truncated and loaded straight with the new data. For incremental files, we first copy all the data from "active" into "inactive" and then perform the incremental update. In both cases, when data loading is done we switch the view to the appropriate table.

This has been working fine for many years; typically clients send us data files at night time and their data is processed when nobody is using the system. However, one of our larger clients now wants to send us incremental files throughout the day. For a smaller sized client, the A->B copy operation is fairly quick (~30 seconds for 600k rows), but this large client's copy is taking 30-60+ minutes depending on load.

The schema for both tables is as follows (replace TableB with TableA):

CREATE TABLE [dbo].[TableA](
    [ClientId] NCHAR(10) NOT NULL,
    [ItemId] INT NOT NULL,
    [Amount] DECIMAL(19,6) NOT NULL,
    [Loaded] DATETIME NOT NULL,
 CONSTRAINT [PK_TableA] PRIMARY KEY CLUSTERED ( [ItemId] ASC, [ClientId] ASC) ON [PRIMARY])

ALTER TABLE [dbo].[TableA]  WITH NOCHECK ADD  CONSTRAINT [FK_TableA_Items] FOREIGN KEY([ItemId])
REFERENCES [dbo].[Items] ([ItemId])

ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_Items]

ALTER TABLE [dbo].[TableA]  WITH NOCHECK ADD  CONSTRAINT [FK_TableA_Clients] FOREIGN KEY([ClientId])
REFERENCES [dbo].[Clients] ([ClientId])

ALTER TABLE [dbo].[TableA] CHECK CONSTRAINT [FK_TableA_Clients]

CREATE NONCLUSTERED INDEX [IX_TableA_ClientId_ItemID_INC_Amount] ON [dbo].[TableA] ([ClientId] ASC, [ItemId] ASC )  INCLUDE ([Amount])

The insert/select statement we are currently using is a simple:

INSERT INTO [dbo].[TableB]  ( ClientId, [ItemId], [Amount], [Loaded] )
SELECT ClientId, ItemId, Amount, Loaded FROM [dbo].[TableA]

I've tried various methods for optimizing this copy; I've added TabLock to the target table, tried setting Trace Flag 610 and tried many different batch sizes, but nothing seems to work. One variation of the queries I've tried is:

DECLARE @BatchSize BIGINT = 50000;
WHILE 1 = 1
BEGIN
      INSERT INTO TableB WITH ( TABLOCK ) ( [ClientId], [ItemId], [Amount], [Loaded] )
      SELECT TOP ( @BatchSize ) ClientId, ItemId,Amount, Loaded
      FROM      TableA t1
      WHERE     NOT EXISTS ( SELECT 1 FROM TableB WHERE ClientId = t1.ClientId AND ItemId = t1.ItemId );

      IF @@ROWCOUNT < @BatchSize
         BREAK;
END;

I've tried dropping/recreating indexesbut this too was proving costly. The best time I've come up with is about 5 minutes, but this involves dropping the target table and performing a select into, then adding the indexes and check constraints. This was purely "educational"--I can't go this route due to schema-bound views and some other items preventing this approach (in addition to our normal view, we have a "B" view which points to the other table for use during this process only)

While I know that SSIS is probably the best approach here, it is out of the question for now--I'm tasked with speeding up the existing process, if possible. I do have control over what procedures are called, etc from C#.

With that said, do I have any other options? I can do pretty much whatever I want to the inactive table as nothing is using it except these procedures. One thing I was considering is to somehow export the data with BCP from C# and then re-ingest it. Aside from not being sure if its possible, I've no idea if it would be any better (or worse for that matter)

How about keeping all the incremental data inside the inactive table until the end of the day, then load it into active and truncate inactive? Your view could reference both tables using a UNION ALL, that way you aren't switching between the two tables.

You could use partition swapping with the same method. You'd partition both tables, load inactive until 11 PM at night, then swap inactive's loaded partition into active. Your view would still need to reference both tables. This way, you have no downtime for loading besides the time it takes to initially import the file, and incremental files should be loading into an empty table every day.


Find all unique columns that are allowed null I am trying to write a query that displays all unique columns that are allowed to be null in Oracle.

The information I need is in these 3 tables:

ALL_TAB_COLUMNS
ALL_IND_COLUMNS
ALL_INDEXES

I am having difficulty joining these tables to display the information I'm looking for. Any help would be appreciated.

Looking for output such as:

OWNER, COLUMN_NAME, INDEX, UNIQUENESS, NULLABLE

WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE'

Try this one:

SELECT ind.OWNER AS INDEX_OWNER, ind.INDEX_NAME,
    tab.OWNER AS TABLE_OWNER, tab.table_name,
    tab.column_Name,
    UNIQUENESS, NULLABLE
FROM ALL_INDEXES ind
    JOIN ALL_IND_COLUMNS col
        ON ind.OWNER = INDEX_OWNER
            AND ind.INDEX_NAME = col.INDEX_NAME
    JOIN ALL_TAB_COLUMNS tab
        ON tab.owner = col.table_owner
            AND tab.table_name = col.table_name
            AND tab.column_Name = col.column_Name
WHERE NULLABLE='Y' AND UNIQUENESS='UNIQUE';


what are the events recorded in the transaction log? in regards to what events are recorded in the transaction log , is the below an accurate/comprehensive list?

How are things recorded? are the pages that are marked as modified written to the transaction log?

is there a way to see what is in the transaction log?

The start and end of each transaction
Every data modification
Every extent allocation or deallocation
The creation or dropping of a table or index
Broker event category includes events produced by Service Broker.
Cursors event category includes cursor operations events.
CLR event category includes events fired by .Net CLR objects.
Database event category includes events of data.log files shrinking or growing on their own.
Errors and Warning event category includes SQL Server warnings and errors.
Full text event category include events occurred when text searches are started, interrupted, or stopped.
Locks event category includes events caused when a lock is acquired, released, or cancelled.
Object event category includes events of database objects being created, updated or deleted.
OLEDB event category includes events caused by OLEDB calls.
Performance event category includes events caused by DML operators.
Progress report event category includes Online index operation events.
Scans event category includes events notifying table/index scanning.
Security audit event category includes audit server activities.
Server event category includes server events.
Sessions event category includes connecting and disconnecting events of clients to SQL Server.
Stored procedures event category includes events of execution of Stored procedures.
Transactions event category includes events related to transactions.
TSQL event category includes events generated while executing TSQL statements.
User configurable event category includes user defined events.

Every transaction in SQL Server is logged in someway or other depending on recovery model. fn_dblog and fn_dumpdblog both undocumented command would give you nice view what is present in transaction log but the result could be massive. What I do is I run whatever transaction I need to do within begin tran and commit/rollback and then only capture logs I need to using below query

BEGIN TRANSACTION TRANSACTION_NAME
SQL QUERY
COMMIT TRANSACTION OR ROLLBACK TRANSACTION

--Then use below query to filter out records related to the transaction

USE DATABASE_NAME
GO
SELECT
OPERATION,
Context,
[Transaction Name],
[AllocUnitId],
[Page ID],
Description,
[Lock Information]
FROM FN_DBLOG(NULL,NULL)
WHERE [Transaction ID] in
(
select [Transaction ID] from
fn_dblog(Null,Null)
where [Transaction Name]='TRANSACTION_NAME'
)

You can run

SELECT * FROM FN_DBLOG(NULL,NULL)

To see all column names which fn_dblog would produce

Example: I would show what all things are logged when simple update command is fired

Begin tran T1

update [dbo].[IXBUILD] set C2='XXXXXX' where C1=1

Commit

Its has one row for begin tran one for commit and one regarding update operation. You can see LOP_BEGIN_XACT for beginning of transaction and COMMIT for commit of transaction.
LOP_MODIFY_ROW says a row was modified like we did in update statement.
LCK_Clustered_index is in picture because table had CI and row must have been exclusively locked for update
Then comes hexadecimal page ID which actually says what page had row which was modified
Then last column Includes the lock Information. The lock which was taken on HoBt 72057594041860096:ACQUIRE_LOCK_IX OBJECT: 8:613577224:0 ;ACQUIRE_LOCK_IX PAGE: 8:1:344 ;ACQUIRE_LOCK_X KEY: 8:72057594041860096 (8194443284a0)

Database ID=8 file number 1 and page 334. You would also see KEY value
There is also a log sequence number for every transaction and column Log Record Length which would tell you LSN and size of log record for particular operation

This was just a small demo. Remus Rusanu has explained how to read transaction log in details in This article please read it


Cross-database foreign keys vs. database design best practice MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

Let's say there are two databases, A and B, that in general store different information for different purposes and with different means of uploading data, however some part of the data may appear in both databases. Now let's assume that one wants to make sure that everything out of this small intersection that ends up in B has been previously uploaded to A. Is a foreign key a good idea in this case?

MySQL allows to create cross-database foreign keys - but is it a good method of ensuring data integrity? Is it possible to give a general answer or is it project-dependent? What are the advantages and what threat does it pose?

MySQL doesn't really have a concept of "database". This is because MySQL "evolved" confusing "database" for "schema" and never really fixed it. If you're talking to a real DBA, this is not a cross-database foreign key. So for MySQL, this is perfectly fine and acceptable.

Even in version 8.0 of MySQL,

  CREATE SCHEMA is a synonym for CREATE DATABASE.

In PostgreSQL, for instance, we have "schema" and "databases". And cross-schema foreign keys are perfectly fine, but you can't do cross-database fkeys.

psql (9.6.3, server 9.5.6)
You are now connected to database "foobar" as user "ecarroll".
foobar=# CREATE TABLE foo ( id int primary key );
CREATE TABLE
foobar=# INSERT INTO foo SELECT * FROM generate_series(1,10);
INSERT 0 10
foobar=# \connect test;
psql (9.6.3, server 9.5.6)
You are now connected to database "test" as user "ecarroll".
test=# CREATE TABLE foo ( id int references foobar.foo.id, name text );
ERROR:  cross-database references are not implemented: "foobar.foo.id"

That's what a cross-db fkey would look like. Notice it would require explicit schema specification and I'm not aware of any database that implements schema's and databases that permits it.

The downside of the MySQL confusing databases and foreign keys is pretty clear. If you have full access to a single specific database, you do not have the ability to organize your own content. Whereas in PostgreSQL, if you full access to a single database you can create any amount of arbitrary schemas. You don't need SUPER USER nor the ability to CREATE DATABASE


Use a Context namespace in a multi-tenant database? I'm developing an Apex application that uses database Contexts in conjunction with Row Level Security. I'd like to install a second copy of the schema and workspace on the same instance; this works except for the Context, which is a global thing.

For example, in schema "DEV", I create the context:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

When I query dba_context, I see my context has been created:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  DEV     MYAPP_PKG  ACCESSED LOCALLY

In schema "TEST", I create the context as before:

CREATE OR REPLACE CONTEXT myapp_ctx USING myapp_pkg;

This has the effect of removing the dev context and replacing it with the test one:

NAMESPACE  SCHEMA  PACKAGE    TYPE
=========  ======  =========  ================
MYAPP_CTX  TEST    MYAPP_PKG  ACCESSED LOCALLY

I've got the context name hardcoded in various parts of the application (e.g. views, packages, etc) and would rather not have it differ between environments if I can help it.

At the moment my only idea is to parameterize the namespace name in the deployment scripts - so that the namespace will be e.g. MYAPP_CTX_DEV or MYAPP_CTX_TEST depending on a configuration option in the deployment scripts.

Are there any better alternatives that allow me to install multiple versions of my application on the one instance?

Oracle version 11.1.0.7

You could encapsulate the logic into the packages themselves:

SQL> CREATE OR REPLACE PACKAGE pkg IS
  2     g_context VARCHAR2(30);
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2;
  4     -- set_ctx may be private of course
  5     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2);
  6  END;
  7  /

Package created

SQL> CREATE OR REPLACE PACKAGE BODY pkg IS
  2
  3     FUNCTION  get_ctx(p_variable VARCHAR2) RETURN VARCHAR2 IS
  4     BEGIN
  5        RETURN sys_context(g_context, p_variable);
  6     END get_ctx;
  7
  8     PROCEDURE set_ctx(p_variable VARCHAR2, p_value VARCHAR2) IS
  9     BEGIN
 10        dbms_session.set_context(g_context, p_variable, p_value);
 11     END set_ctx;
 12
 13  -- set ctx dynamically
 14  BEGIN
 15     g_context := 'MYCTX_'||sys_context('userenv', 'current_schema');
 16  END;
 17  /

Package body created

Let's create the context and call it :

SQL> BEGIN
  2     EXECUTE IMMEDIATE 'CREATE CONTEXT ' || pkg.g_context || ' USING PKG';
  3  END;
  4  /

PL/SQL procedure successfully completed

SQL> exec pkg.set_ctx ('a', '123');

PL/SQL procedure successfully completed

SQL> select pkg.get_ctx('a') from dual;

PKG.GET_CTX('A')
--------------------------------------------------------------------------------
123


Best way to include AND exclude? I have a large table that contains product IDs and product category IDs.

I want to select product IDs that are in a certain list of category IDs, AND exclude the product ID entirely if it's in another list of category IDs.

Example table:

prod_id cat_id
------- ------
1       10
1       11
2       10
2       11
2       12
3       10
3       11

So I want to see all products that have category IDs 10 and 11, BUT if they are also have category ID 12, they must be excluded.

I was thinking this would work, but it does not:

SELECT prod_id from products WHERE cat_id IN (10,11) AND NOT IN (12)

This seems like it should be really easy and common, but I'm just not getting it.

You can use NOT EXISTS for this purpose.

SELECT DISCTINCT
    prod_id
FROM
    products p1
WHERE
    cat_id IN (10, 11)
    AND NOT EXISTS (SELECT 1
                    FROM   products p2
                    WHERE  p1.prod_id = p2.prod_id
                           AND cat_id = 12);


Dedicated SQL Server instance for SQL Agent I have an environment that has dozens of SQL Server instances all running SQL agent jobs for their own maintenance as well as other processes (ETL, etc.). I am considering a change to consolidate the non-maintenance jobs to a separate SQL Server instance(s). I'm thinking that having the SQL Agent job processing setup on centralized servers would be easier to manage and coordinate jobs' schedules. I would also plan on using one of these SQL Agent only instances as the distributor for replication purposes.

Besides licensing concerns what downside would there be to move to such a configuration?

A: every job query will require four-part names.

B: that Agent's  credentials will require privileges on all instances which is a security risk.


MySQL LIKE IN with Input Parameter of stored procedure Comma Separated Values I am creating a stored procedure to search user's Towns.
From the User Interface user will select multiple towns that will be sent to the stored procedure.
Sample input of the towns parameter for the stored procedure would be like orangi,faisal,nazimabad.

Towns that are stored in the table are

Orangi
Korangi
Shah Faisal
Shahrah-e-Faisal
Nazimabad
North Nazimabad

Now I want to select all those above records against that input. That is I want to use LIKE operator with IN from a stored procedure's parameter.

I don't know how many towns would be there in the input.
it could be just one, or two or three or all these mentioned above.

I want to search all the towns against that query

Sample Stored Procedure is

CREATE PROCEDURE `GetUsersByTown`(
IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
      SELECT
      name,
      Town,
      FROM userLocation where Town in (SearchTowns)
END

Expected Outcome

Name ---  Town
Hamza --- Orangi
Jamal --- Korangi
Hasham --- Shah Faisal
Aliyan --- Shahrah-e-Faisal
Danish --- Nazimabad
Farrukh --- North Nazimabad

I have tried to use find_in_set() but FIND_IN_SET does not accept wildcards like % and it doesn't use index?

I have also looked at using REGEXP  ValueA|ValueB but I don't know how to put the input parameter that is comma separated values in that REGEXP

Use REPLACE(SearchTowns, ',', '|') to change commas to pipes.
Put that result in, say, @regexp.
Construct, via CONCAT a single SELECT with

SET @sql = CONCAT('SELECT
  name,
  Town,
  FROM userLocation where Town REGEXP "', @regexp, '"');

PREPARE and EXECUTE the query.

That query should look like:

SELECT
      name,
      Town,
      FROM userLocation where Town REGEXP "orangi|faisal|nazimabad"

(You could manually run that to verify that the correct rows are delivered.)

Also, be sure to have case-folding COLLATION, such as utf8mb4_unicode_520_ci for Town.  (That is indicated by the trailing _ci.)

Code

DELIMITER //
CREATE PROCEDURE `GetUsersByTown`(
    IN `SearchTowns` VARCHAR(200) -- `ORANGI,FAISAL,NAZIMABAD`
)
BEGIN
IF (SearchTowns IS NULL) THEN
    SELECT name, Town
        FROM userLocation;
ELSE
    SET @towns = REPLACE(SearchTowns, ',', '|');
    SELECT name, Town
        FROM userLocation
        WHERE Town REGEXP (@towns);
END IF;
END //
DELIMITER ;


To normalize or not to normalize? (10 fields) I'm helping someone to design a database and for a specific case I'm not sure.

I have 10 fields - this will never change - that will always be filled and used in every query that could be coming up. Always.

So I came up with 2 solutions, normalized and not normalized

I would have to query the first solution like this;

    select n.Id
         , Type1Selector1.Info as Type1Selector1Info
         , Type1Selector2.Info as Type1Selector2Info
         , Type1Selector3.Info as Type1Selector3Info
         , Type1Selector4.Info as Type1Selector4Info
         , Type1Selector5.Info as Type1Selector5Info
         , Type2Selector1.Info as Type2Selector1Info
         , Type2Selector2.Info as Type2Selector2Info
         , Type2Selector3.Info as Type2Selector3Info
         , Type2Selector4.Info as Type2Selector4Info
         , Type2Selector5.Info as Type2Selector5Info

    from dbo.OptionOneMainTable n
         inner join dbo.OptionOneSecondTable Type1Selector1 on
                    Type1Selector1.Id = n.Id
                and Type1Selector1.Type = 1
                and Type1Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type1Selector2 on
                    Type1Selector2.Id = n.Id
                and Type1Selector2.Type = 1
                and Type1Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type1Selector3 on
                    Type1Selector3.Id = n.Id
                and Type1Selector3.Type = 1
                and Type1Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type1Selector4 on
                    Type1Selector4.Id = n.Id
                and Type1Selector4.Type = 1
                and Type1Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type1Selector5 on
                    Type1Selector5.Id = n.Id
                and Type1Selector5.Type = 1
                and Type1Selector5.Selector = 5
         inner join dbo.OptionOneSecondTable Type2Selector1 on
                    Type2Selector1.Id = n.Id
                and Type2Selector1.Type = 2
                and Type2Selector1.Selector = 1
         inner join dbo.OptionOneSecondTable Type2Selector2 on
                    Type2Selector2.Id = n.Id
                and Type2Selector2.Type = 2
                and Type2Selector2.Selector = 2
         inner join dbo.OptionOneSecondTable Type2Selector3 on
                    Type2Selector3.Id = n.Id
                and Type2Selector3.Type = 2
                and Type2Selector3.Selector = 3
         inner join dbo.OptionOneSecondTable Type2Selector4 on
                    Type2Selector4.Id = n.Id
                and Type2Selector4.Type = 2
                and Type2Selector4.Selector = 4
         inner join dbo.OptionOneSecondTable Type2Selector5 on
                    Type2Selector5.Id = n.Id
                and Type2Selector5.Type = 2
                and Type2Selector5.Selector = 5

and obviously the second solution would be

    select Id
         , Type1Selector1Info
         , Type1Selector2Info
         , Type1Selector3Info
         , Type1Selector4Info
         , Type1Selector5Info
         , Type2Selector1Info
         , Type2Selector2Info
         , Type2Selector3Info
         , Type2Selector4Info
         , Type2Selector5Info

    from dbo.OptionTwoMainTable

I'm not alone doing this database and I would personally take the second solution.

Should I try to convince the other person to go for solution two? He wants to go for solution one.

If the fields are always going to have a value and are always read as a group, then option 1 is normalized.

The column names could use some work, but I assume they have been obfuscated.

There is a third option which is to split the two types out into separate tables.  This would be appropriate if either type grouping was optional and appear separately.

Option 2 is appropriate where the fields are independent of the id (Primary key).  From your description, this is not the case.

Even if you do model the two types as separate tables in the design model, one of the implementations of subtypes is to incorporate them in the main table using nullable columns.


Import using pgAdmin3 produces ERROR: duplicate key value violates unique constraint I am importing from the GUI a CSV file into a table. I have created the following table:

CREATE TABLE jira_tickets.daqa_rpt_tbl
(
  daqa_report_id bigint NOT NULL DEFAULT 'jira_tickets.daqa_id_seq'::regclass,
  project_name character varying(250) NOT NULL,
  jira_key character varying(250) NOT NULL,
  jira_summary text NOT NULL,
  issue_type character varying(100) NOT NULL,
  jira_status character varying(100) NOT NULL,
  jira_resolution character varying(250),
  jira_assignee character varying(250) NOT NULL,
  jira_reporter character varying(250) NOT NULL,
  jira_created text,
  jira_last_viewed text,
  jira_updated text,
  jira_resolved text,
  last_updated timestamp without time zone DEFAULT now(),
  CONSTRAINT "daqa_report_id_PK" PRIMARY KEY (daqa_report_id)
);

When I import I get this error.  Clearly the sequence is not incrementing when I move to the next row. Here is how I declare the sequence:

  CREATE SEQUENCE jira_tickets.daqa_id_seq
  INCREMENT 1
  MINVALUE 1
  MAXVALUE 1000000000000000000
  START 1
  CACHE 1;
ALTER TABLE jira_tickets.daqa_id_seq
  OWNER TO postgres;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO public;
GRANT ALL ON TABLE jira_tickets.daqa_id_seq TO postgres;

Any suggestions or trouble shooting would be greatly appreciated.

The column default for daqa_report_id would have to be:

nextval('jira_tickets.daqa_id_seq'::regclass)

Not:

'jira_tickets.daqa_id_seq'::regclass

That would just fetch the OID for the sequence object from pg_class, which is converted to a meaningless, static bigint number.

pgAdmin has nothing to do with this.

But just use the pseudo data type bigserial instead. It does everything automatically. Here's how you would add a bigserial to an existing table:
Most efficient way to add a serial column to a huge table


Memory Optimized Tables - can they really be so difficult to maintain? I'm investigating the benefits of upgrading from MS SQL 2012 to 2014.  One of the big selling points of SQL 2014 is the memory optimized tables, which apparently make queries super-fast.

I've found that there are a few limitations on memory optimized tables, such as:

No (max) sized fields
Maximum ~1KB per row
No timestamp fields
No computed columns
No UNIQUE constraints

These all qualify as nuisances, but if I really want to work around them in order to gain the performance benefits, I can make a plan.

The real kicker is the fact that you can't run an ALTER TABLE statement, and you have to go through this rigmarole every time you so much as add a field to the INCLUDE list of an index.  Moreover, it appears that you have to shut users out of the system in order to make any schema changes to MO tables on the live DB.

I find this totally outrageous, to the extent that I actually cannot believe that Microsoft could have invested so much development capital into this feature, and left it so impractical to maintain.  This leads me to the conclusion that I must have gotten the wrong end of the stick; I must have misunderstood something about memory-optimized tables that has led me to believe that it is far more difficult to maintain them than it actually is.

So, what have I misunderstood?  Have you used MO tables?  Is there some kind of secret switch or process that makes them practical to use and maintain?

No, in-memory really is this unpolished.  If you are familiar with Agile you will know the concept of "minimal shippable product"; in-memory is that.  I get the feeling that MS needed a response to SAP's Hana and its ilk.  This is what they could get debugged in the timeframe for a 2014 release.

As with anything else in-memory has costs and benefits associated with it.  The major benefit is the throughput that can be achieved.  One of the costs is the overhead for change management, as you mentioned.  This doesn't make it a useless product, in my opinion, it just reduces the number of cases where it will provide net benefit.  Just as columnstore indexes are now updatable and indexes can be filtered I have no doubt that the functionality of in-memory will improve over coming releases.

SQL Server 2016 is now generally available. Just as I supposed, In-Memory OLTP has received a number of enhancements. Most of the changes implement functionality that traditional tables have enjoyed for some time. My guess is that future features will be released at the same time for both in-memory and traditional tables. Temporal tables is a case-in-point. New in this version it is supported by both In-Memory and disk-based tables.


Should I lock the table during delete I have a table that has 4 million records. It has a clustered index on a date column (record creation date). It has 5 tables reference this table, all have FK indexes.
The machine has no down time. I had a program that clean up records that older than 31 days. It create a connection, Delete TOP 1000 rows, close the connection, and repeat until all old records are removed.
The delete has been very slow, it is deleting about 1000 rows per 10 sec. Ideally I want to do 1000 row per a second.
I notice that during the delete, it is performing a lot of page lock on the index.
I am wondering if there are faster way to delete data without causing timeout.
My idea is that would it be better if I do a table lock, perform the delete, wait for it for a sec so that it doesn't timeout other transaction, then perform the delete again.
My guess is that if I do a table lock, it should reduce the number of row locks or page locks, which may speed up the delete.
Any suggestions on this issue I have would help.
Please note that the harddrive or database isn't fragmented, and it is a RAID 10 machine.
[Update]
Thanks for asking for the performance execution plan. It looks like the live environment is different than my development environment. It is doing index scan rather than index seek. I think I have to investigate more about why it would do a index scan.

[Update 2] Here is the index that we have for some of those tables. Our index naming convention is [TableName]_[ColumnName], sorry we didn't use MSSQL naming standard. In addition, it turns out that client has a 96% fragmented index (VehicleLocationTP_VehicleLocationKey), that definitely is one of the problems. It may be a reason why SQL2005 would use index scan, rather than index seek.

[Update 3] I finally able to test the delete query on their testing server, instead on my own computer. They are running SQL 2005 Standard, verse SQL 2008 R2 Express on my machine.  The indexes where about 95% fragmented, and rebuilding the index has improved the delete from 25-50%. It is hard to do performance test when the their SQL Server is constantly running. However, checking the actual execution plan, it is the same as the estimated one. So you are right that fragmentation doesn't affect the execution plan. My guess is that it could be the number of rows in the table. Maybe if the table is small, it would uses index scan, rather than index seek.
In addition, this article give me a bit more insight on why it is a index scan Index Scan vs Index Seek
When the execution plan showing index scan, it really scanning the entire table. It calls it a index scan because VehicleLocationAPC is a cluster indexed table. This remove a bit of confusion. It means that index weren't use, it was doing an entire table scan.
Another thing to realize is that the content of the data in VehicleLocationAPC. VehicleLocationKey are almost unique all the time. Our application generation one VehicleLocationAPC row per VehicileLocation row. My guess is that because of this, SQL Server would rather scan the entire table, instead of using the index... but I could be wrong, because I would have thought that the index are sorted in a b-tree, which should be faster to scan the key, rather than doing a table scan.
My focus turns to VehicleLocationTP, this is the table that is causing 63% of the estimated time, and this table is huge.

Changing the locking to table locks will just make the deletes run even slower as the delete won't be able to run until the lock can be taken on the table which means that all other threads need to be finished or blocked.  If you have foreign keys with delete cascade enabled that will probably take a lot of the time.

You might want to change it to a SQL Agent job so instead of running your app which connects and disconnects, you just run a loop deleting data until you are done.

SELECT NULL --Makes the WHILE loop work.
WHILE @@ROWCOUNT <> 0
BEGIN
    DELETE TOP (1000) FROM YourTable
    WHERE Column < getdate()-31
END

If this doesn't work you could look into table partitioning which would allow you to switch the data to another table very quickly, then truncate the data from the new table.  This however does require Enterprise Edition.


Which collation should I choose for a muiti-language website? Does a collation have any influence over a query speed? Does the size of a table change depending of the collation?

If I want to build a website that must support all possible languages (lets take for e.g. Google) which would be the recommended collation?

I will need to store characters such as 日本語, my searches over the website will have to return something for the sóméthíng input, it must be case insensitive as well.

How do I know which is the best choice to make? Which collation better suits this case?

Generally speaking, one of the Unicode variants is probably the best for broad language support - UTF-8 is going to use less memory per codepoint, and thus will have a slight advantage in any time/space tradeoffs you find yourself in need of making; however, I think there are some of the more esoteric languages/scripts that UTF-8 cannot represent (but I'm not 100% certain of that, I haven't done an exhaustive study on the matter).

This Wikipedia article might be enlightening on the dis/advantages of each.


Mysql - How can I replication structure, not data I have master with 2 slaves (4 but other 2 not relevant).  One slave is full replication, ability to switch to be master if needed.  Second slave was designed to replicate certain tables and leave out key data.  Purpose of that slave is to use it for refreshing a development database at any time so developers have what is in production to work on, but without customer sensitive data.  It is using a number of replicate-ignore-table.

For whatever reason, I usually think of that as ignoring replication of DATA to the table, not table structure changes.  I'm obviously incorrect.  Structure changes are also not replicated as per visual evidence and "not to replicate any statement that updates the specified table".  (Maybe I'm too used to thinking "update" is table data, not table_schema).

Other than removing replicate-ignore-table lines and then running an event very often to truncate a list of tables, what other way might there be to have the table structure changes (including trigger changes to a table) be replicated but to NOT replicate the data being put in the database?  Triggers for each table isn't a solution as this db needs to be as much a copy of what is in master as possible with regards structure and various customer configurations (which changes frequently).

I have everything automated from doing daily auto sanitize and dump, transfer to file storage.  For refresh, just run a script to fetch latest dump, do backup of existing developer db, load prod version and all is almost good (need to update some unreleased changes to the db).  And then now realize replicate-ignore-table ignores structure too.

Suggestions?

The blackhole engine type will work for what I need.  We are using row based replication and can't change to statement based but the warnings are not being logged so that won't be an issue.

As a side note, one thing I found somewhere suggested show variables like 'have_blackhole_engine' and nothing came up (nor for show variables like '%blackhole%' or show global variables like '%blackhole%') but I decided to create a table with the engine and it showed up just fine.


Feature of database-level audit in SQL Server 2012 Express edition Like in SQL Server 2012 Enterprise edition, can we have database-level audit in SQL Server 2012 Express edition?

Can we have Audit Folder in Security Tab?

Old question, but since it got bumped I will answer with some updated information.

While Express 2012 was limited to server level auditing (referred to as "basic auditing" in feature comparison documentation) and its RTM SSMS version was limited in GUI functionality for auditing, newer versions have removed both of those limits:

The newest 17.x versions of Management Studio are 100% compatible with SQL 2012 (and every subsequent version) and has no GUI-based limitation on auditing features (assuming they're supported by the SQL Server version it is connecting to).
As of SQL Server 2016 SP1, "fine grained auditing", i.e. database level auditing, is available across all SQL Server editions.

The term "fine grained auditing" is strange in context of SQL Server, as it seems to only be used in feature comparison docs by Microsoft, but not in any other documentation.

Additionally, I have written a distilled comparison of key features across different editions and versions of SQL Server, focusing on the perspective of Express.


Encrypt / Decrypt String I've been approached with a requirement. One of our devs wants to be able to store information in the database, but once there it needs to be encrypted, so that if anyone ever gets a copy of the database or access to the raw data within it they will not be able to read it. You should only be able to read the data once you have authenticated through the app via SSO.

He seems to think there is a simple function for this, that he can call from the application and decrypt the encryption...

I'm telling him that something like TDE is going to be required here which will take a little while to set up. I have no experience of this, so it will be a learning curve....

In short, the requirement is to protect sensitive information if the database is ever hacked, stolen or mislaid.

Any ideas? Or is TDE the way to go?

protect sensitive information if the database is ever hacked, stolen or mislaid.

This is a very broad requirement.

Encryption comes at the cost of CPU cycles, so better load test your application if the outcome is acceptable to your application performance.

There are areas to explore and combination of them will get you want you want.

Encrypting data at rest - this is done using TDE (Transparent data encryption). I have written about my experience embracing TDE in our environment here.
Encrypting all connections to SQL Server using Force Encryption. This is done using loading certificate on the database server, enabling force encryption (will require sql server service restart !) and granting read access to sql server service account to the certificate. You can verify using sys.dm_exec_connections --> column encrypt_option will be TRUE.
Column level encryption - protecting sensitive data - see this article.
If you are going to upgrade to SQL Server 2016, you can explore Always Encrypted option.

On the other hand, make sure you follow the security best practices (warning: its word doc !) with regards to sql server service accounts, logins & users, port allocation, etc.


mongodump: are 4 shards with total datasize 575 GB consider a Small Sharded Cluster? I need to test the restore with Ops Manager. For this I "clone" the production sharded cluster. I create VMs with the same size like production and do mongodump/mongorestore (Ops Manager Deployment). My test (for restore) don't need to be a consistent copy, for me no problem if around 5 GB is missing.

DATA SIZE: 573.6 GB

shard0
142.6 GB

shard1
145.94 GB

shard2
142.55 GB

shard3
142.52 GB

For simplicity reason I wish to take a mongodump and pipe it to mongorestore on the mongos.

I found a an old doc (v3.0) Backup a Small Sharded Cluster with mongodump. This documentation doesn't exists anymore in new MongoDB versions.

  If your sharded cluster holds a small data set, you can connect to a
  mongos using mongodump.

what is a small data set in GB? See above for my deployment.

  If you use mongodump without specifying a database or collection,
  mongodump will capture collection data and the cluster meta-data from
  the config servers.

I don't need to backup explicitly config RS?

  When restoring data to sharded cluster, you must deploy and configure
  sharding before restoring data from the backup. See Deploy a Sharded
  Cluster for more information.

It means in plain English I need to define the shard key (and enable sharding) before restore?

Do I miss any steps/important things?

It is not a small sharded cluster.

Mongodump will take around 5 hrs for 600GB and restore would take more than 5 hrs based on the indexes in place on your collection.

My best suggestion is:

If you have a backup already in place taken by Mongo Ops-Manager, use that and restore on the new environment.
If time is not really an issue in your case, use the dump and restore method.
Mongodump and restore method can handle huge databases, if you want to restore few collections then use export and import option.

Note: A small hint i can give here to make the process faster is:

Mongodump will take a backup of the db and collection in 2 files for each, one is bson and other is json. Bson will have the indexes and json will have the data.

So, Mongorestore process will restore the json file first and then start restoring the bson file and apply indexes.

To make this process done faster, first make a mongodump and get all the indexes from all dbs and collections and apply the indexes first manually and then restore the data with mongorestore, this process will save atleast 30-40% of your time.


SQL Server Service Broker vs. RabbitMQ We are considering RabbitMQ for queuing several thousand tasks per minute and then processing them from 300+ virtual machines. We have a smart scheduling web API that will intelligently (fairly) distribute tasks from 100+ queues to the VMs based on business needs. We know that RabbitMQ is definitely capable of handling this kind of load. Currently, we are querying the database each time across multiple tables which is contributing to 30-40% of the SQL Server load. Instead, we were thinking about setting up a trigger and then pushing the data to RabbitMQ (via an intermediate exchange API) as the data comes in. I realize that SQL Service Broker is also a potential option vs. RabbitMQ for queues, so I have a few questions about SQL Service Broker (SQL Server 2016 and up) that I hope you can answer:

Can we store the queue data in a durable manner and then fetch tasks from specific queues based on business needs? (I assume yes, but just confirming.)
How reliable is it for 100+ queues? I recall that the older versions of the Service Broker were quite unstable.
Can we create new queues dynamically / programmatically?
Can we create min-priority queues (i.e. as tasks are pushed onto the queue, it is sorted based on priority)?
What is the impact in terms of performance and load? At least with RabbitMQ, we can offload the queue management responsibility to a RabbitMQ cluster, leaving our SQL Server free to process other queries.
Is it true that we need to setup some kind of "conversation" between queues in order for it to work?
Are there any other differences / pros/cons that we need to be aware of considering one versus the other?

Can we store the queue data in a durable manner and then fetch tasks
  from specific queues based on business needs? (I assume yes, but just
  confirming.)

Yes.

  How reliable is it for 100+ queues? I recall that the older versions
  of the Service Broker were quite unstable.

A queue is a table.  SEND is an insert.  RECEIVE is a delete.  There is some functionality to process queues from inside SQL Server, but you can always just use external clients.

See eg https://code.msdn.microsoft.com/Service-Broker-Message-e81c4316 for a sample of how to process a queue from an external application.

  Can we create new queues dynamically / programmatically?

Yes.  But like creating tables dynamically, it's got bad code smell.

  Can we create min-priority queues (i.e. as tasks are pushed onto the
  queue, it is sorted based on priority)?

Yes.

https://docs.microsoft.com/en-us/sql/t-sql/statements/create-broker-priority-transact-sql

  What is the impact in terms of performance and load?

A SEND is a single-row insert into a clustered-index table with a single non-clustered index.  A RECEIVE is a delete of one row.

  Is it true that we need to setup some kind of "conversation" between
  queues in order for it to work?

Every SEND and RECEIVE is in the context of a "conversation" which is a potentially long-lived session between two "services".  conversations can live forever and be reused, and are the source of much confusion for getting started with service broker.

  Are there any other differences / pros/cons that we need to be aware
  of considering one versus the other?

Service broker is quite complex, as all messages are sent on potentially-long-lived duplex conversations, and you must program against it in TSQL.  Once you grok the design, and the strange troubleshooting a lot of the TSQL is boilerplate.

The best thing about Service Broker is that it's not a separate repository and your messages are have the same HA/DR and Backup as your other data.  And it's trivial to enlist SEND or RECEIVE in a transaction involving other application data.

If you don't want in-database messaging, consider Azure Service Bus is much simpler to use, more functional, is operated for you in the cloud, and is able to handle that level of scale quite easily and cheaply.

Also for many scenarios you can just use a simple table as a queue.  See eg http://rusanu.com/2010/03/26/using-tables-as-queues/ from Remus Rusanu, who used to be a SQL on the SQL product team that owned Service Broker.  You don't get blocking reads, but if your latency requirements can tolerate a reader in a polling loop, it can be a good option.


What's the term that describes our data processes We provide an online subscription product that allows our users to research and analyze financial data of their interested industries. As such, we have various tools for loading data from various sources, cleansing data, procssing data, tagging data based on user interactions etc. Basically all sorts of processes and tools that deal with data manipulation.

We are looking into overhauling our these data processes, and I am trying to find the right term to call these processes, both for research purpose and for my presentation. The names I have considered so far:

ETL - this term seems too narrowly focused on loading data. Our data processes deal with user inputs extensively too.
Data Warehousing - this term seems too generic and broad. I am not sure if our processes are data warehousing in nature.

I am looking for a term that I can use to name or describe our processes.

For advertising purposes, you could use the term "Business data enhancement engineering"? :-) Personally, I would call what you do "[complex] [business] data processing".

  Thanks. Any suggestion on what terms I should be googling for if I
  want to find new tools for our processes?

Just various combinations of what you already do - etl/data warehousing/complex data processing... without knowing a bit more, I can't really be that helpful. Unless, that is, you give the tools that you're using.

However, tool recommendations are off-topic here (unfortunately in my view, but there you have it). Maybe if you have some more specific question(s) that we could help with? You could also possibly try software recommendations?

Oh, and I would (slightly) revise my comment description of what you do - call it "complex business enhancement data processing" or "business complementation data analysis and processing" (cringe...) :-).

Just a note - if you're dealing with more technical people, steer clear of the "marketing speak" - otherwise, go for it! :-)

  We use hand coded SQL, Python and DSL scripts currently, so no
  specific tools so far. We are evaluating whether to improve our DSL
  tool chain, or to introduce some commercial UI tools.

I would also examine the Open Source arena if I were you - there are some pretty awesome tools/products out there! Best of luck!


Restoring a database back to a point in time before full backup was made A problem with one of our database tables arose at the end of last week and I wanted to restore that database to a server in order to retrieve the table. We only have the last backup, and a full set of transaction logs before the error occurred, up to and after the full back was taken.

However, when I restore, I can only use the transaction logs to roll forward, not back. Is what I'm trying to do possible?

Sequence of events:

Error occurred: Friday April 5 at 12:00
Full backup taken: Sunday April 7 at 02:00

Many thanks.

Its very difficult to reverse engineer and interpret SQL T-log records. As mentioned there are 3rd party tools like APEX SQL Log , RedGate's LOG Rescue, Log Explorer

Also, there is an undocumented command fn_dblog that can be used as below, but you wont have the flexibility like 3rd party tools.

SELECT
    [Previous LSN],
    [Current LSN],
    [Operation],
    [AllocUnitName],
    [Page ID],[SPID],
    [Begin Time],
    [Transaction Name],
    [Lock Information]

FROM ::fn_dblog(null, null)
GO

Paul Randal has described how to read from T-log with excellent examples -- Using fn_dblog, fn_dump_dblog, and restoring with STOPBEFOREMARK to an LSN .


Join On Text Fields In Query I am unable to change DDL for the tables, but I need to join on two text fields.  I get an error of

  Msg 402, Level 16, State 1, Line 23
  The data types text and text are incompatible in the equal to operator.

Below is sample DDL that illustrates my issue, how do I overcome?

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL')
Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON t1.id = t2.id

You can cast text fields as varchar(max)

Declare @Table1 Table(id text,name nvarchar(200))
Insert Into @Table1 (id, name) Values
('123', 'Joe'), ('222.00', 'Jim')
Declare @Table2 Table (id text,green nvarchar(200))
Insert Into @Table2 (id, green) Values
('123', 'JDSKLJS'), ('222.00', 'KLJKLJJKL');

Select
*
FROM @Table1 t1
INNER JOIN @Table2 t2
ON cast(t1.id as varchar(max)) = cast(t2.id as varchar(max))
GO

id     | name | id     | green
:----- | :--- | :----- | :--------
123    | Joe  | 123    | JDSKLJS
222.00 | Jim  | 222.00 | KLJKLJJKL

dbfiddle here


Non-clustered Index Update not displayed in execution plan Possible Duplicate:
  Nonclustered Index Insert

I'm trying to work out the cost of having a nonclustered index on a particular table, but I don't see any maintenance cost in the execution plan for an insert.

The table looks like this:

    CREATE TABLE [dbo].[ASPStateTempSessions](
    [SessionId] [nvarchar](88) NOT NULL,
    [Created] [datetime] NOT NULL,
    [Expires] [datetime] NOT NULL,
    [LockDate] [datetime] NOT NULL,
    [LockDateLocal] [datetime] NOT NULL,
    [LockCookie] [int] NOT NULL,
    [Timeout] [int] NOT NULL,
    [Locked] [bit] NOT NULL,
    [SessionItemShort] [varbinary](7000) NULL,
    [SessionItemLong] [image] NULL,
    [Flags] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
    [SessionId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[ASPStateTempSessions] ADD  DEFAULT (getutcdate()) FOR [Created]
GO

ALTER TABLE [dbo].[ASPStateTempSessions] ADD  DEFAULT ((0)) FOR [Flags]
GO

As well as a Clustered index on [SessionID] it has the following non-clustered index

/****** Object:  Index [Index_Expires]    Script Date: 12/14/2012 10:13:58 ******/
CREATE NONCLUSTERED INDEX [Index_Expires] ON [dbo].[ASPStateTempSessions]
(
    [Expires] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

But when I run an execution plan for the following, which updates the column on which there is a non-clustered index:

UPDATE [ASPState].dbo.ASPStateTempSessions
            SET Expires = DATEADD(n, Timeout, GETUTCDATE())
            WHERE SessionId = '32gghltsuoesnvlzbehchp2m2014c0f1'
            RETURN 0

I don't see any mention of cost in the execution plan:

Is it usual to not see it? How can I calculate the cost of this index in write operations?

Try generating an actual plan in SentryOne Plan Explorer.

For a narrow plan the actual costs are not included in the XML, so you won't see those, but you at least have the ability to see that non-clustered indexes are affected.

A lot more information found in the duplicate question:

Nonclustered Index Insert

As well as these blog posts that Martin pointed out previously:

Wide vs. Narrow Plans
Optimized Non-clustered Index Maintenance in Per-Index Plans


Get the full text of a long request How to get the full text of the request from the following request:

SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Here, with a sufficiently long request, the request text breaks off.
I use SSMS 17 and 18 versions.

This can be due to the maximum amount of characters that the result to grid can return, 65535.

Starting from SSMS 18.2 you are able to change this.

Thanks Aaron Bertrand for pointing that out.

Aaron also mentions:

  if 64kb is the limit you’re hitting, there are a lot of places where
  statement text is truncated long before that any way (see literally
  any execution plan).

The text datatype of sys.dm_exec_sql_text is nvarchar(max), no issues there.

You could cast the column to XML as a workaround

SELECT CAST(t.[text] AS XML)
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t

Or a better method by Evgeniy Gribkov

SELECT t.[text]
FROM sys.dm_exec_requests AS r
CROSS APPLY sys.dm_exec_sql_text(r.sql_handle) AS t
FOR XML RAW, ELEMENTS;

  Better this way: SELECT t.[text] FROM sys.dm_exec_requests AS r CROSS
  APPLY sys.dm_exec_sql_text(r.sql_handle) AS t FOR XML RAW, ELEMENTS;
  Since the request text can not always be converted to XML. In
  particular, it cannot convert query text when remotely calling stored
  procedures: "XML parsing: line 13, character 129, illegal qualified
  name character"

Or save the results to a file

Thanks Erik Darling for linking some more related answers


Get all the database in db2 through web I would like to get all the databases available on a db2 instance from a C# application.
The CLP command to be used is LIST DB DIRECTORY.

How can I fire this command from C#? Is there another select statement I can use to get all the databases on one db2 instance?

Unfortunately there is no way to use SQL (that I know of) to ask DB2 for all its databases. Perhaps this is a difference between how DB2 is set up/managed versus other databases. A DB2 instance is essentially a database server. It does not do SQL per se as it is not a database. It is the "brains" or "guts" that runs the DB2 logic (and yes, it thus runs SQL, but it is not a database in and of itself). You can create several databases within an instance. But those databases are not stored in a database. Their information is stored in the local database directory, system database directory, and (if remote) the node directory. (Well, and if remote and z/OS or i5/OS, there is also the DCS directory.) This is because the DB2 instance can know about both local and remote databases.

Because of this you have to ask the instance to check its directories for what databases it knows about. This is where you use the non-SQL statements like above.

db2 list db directory will give you the system directory, ie, all databases both local and remote.
db2 list db directory on <path> will give you the local database directory, where local databases reside on disk
db2 list node directory will give you the nodes (ie, the remote servers) that remote databases reside on.
db2 list dcs directory will give you the DCS entries needed for connecting to remote z/OS and i5/OS systems/databases.

So, you unfortunately can't use a database driver as there is nothing to query from database-wise. When you are talking to an instance you aren't necessarily connected to a database. And if you are connected to a database, it won't have information about the other databases, even in its catalog tables, as those are only for the particular database you are connected to.

Now to solve your issue, you could still do this programmatically. You could open a command line prompt (through your code) and if your prompt has DB2 set up in its path (so it can execute the DB2 commands) you could then issue these commands through your code that way. In my past life as a Java developer, I did this with working with some things with DB2 (like dynamically setting certain configuration parameters).


XML Whitespaces/Unwanted Characters Could anyone help on how to remove white spaces from an XML file?

I  used the following code that i got from  Mikael Eriksson on Stack Exchange to extract the site id:

cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]', 'int') AS SiteID

from the following xml:

<DLR_NOTIFICATION>
  <kid>204122</kid>
  <siteid> 7929 </siteid>
  <transid>1c3508e7-f80e-44de-bdf6-3687ff3da84a</transid>
  <service>00000</service><mobile>447500000</mobile>
  <msisdntype>IPAlias</msisdntype>
  <operator>Three</operator>
  <status>100</status>
  <tariff>600</tariff>
  <billingroute>ImpulsePay</billingroute>
  <purpose>IMPerial NOTIFICATION</purpose>
  <tracking>
    <aff>14_z</aff>
    <param>undefined</param>
    <paramdetails>kp=xkpgbadu201409232240100000000000000000000000231</paramdetails>
  </tracking>
</DLR_NOTIFICATION>

How do I trim the whitespaces or any unwanted characters? Or if there is any better way of dealing with this?

I realised that some of this XML have got whitespaces within as shown above in the siteid had caused the below error message

  Msg 245, Level 16, State 1, Line 100 Conversion failed when converting
  the nvarchar value ' 7929 ' to data type int.

This actually does the job by changing int to nvarchar(10) from the script that was posted by Mikael Eriksson:

select cast(msg as xml).value('(/DLR_NOTIFICATION/siteid/text())[1]','nvarchar(10)')SiteID


Getting Database Request Statistics on MySQL Is there a possible way to query (or in anyway) get the number of requests (select, insert, update, delete, etc) that a specific schema is doing on a MySQL Database.

The reason behind this is that we would want to monitor and isolate a schema that uses a lot of server resource. Once identified, we would want to group all schema in a single hardware that can handle high consuming schema.

Also, we wanted to have a report showing this statistics to be presented.

MySQL Version : 5.6.16

Thanks in Advance.

Jaime's answer is pretty good (and thanks for the credit). However, that only deals very specifically with "Table IO" (i.e latency at the storage engine handler layer), and file IO, per schema.

I'd like to suggest a slightly alternate answer, which gives exactly what was asked for. This deals with statement latency over all, and counts the numbers of each types of statement:

SELECT schema_name,
       substring_index(digest_text, ' ', 1) AS sql_type,
       COUNT(*) AS count,
       sys.format_time(SUM(sum_timer_wait)) AS latency
  FROM performance_schema.events_statements_summary_by_digest
 GROUP BY schema_name, substring_index(digest_text, ' ', 1)
 ORDER BY schema_name, SUM(sum_timer_wait) DESC;

+--------------------+----------+-------+---------------+
| schema_name        | sql_type | count | total_latency |
+--------------------+----------+-------+---------------+
| NULL               | CALL     |     2 | 584.49 ms     |
| NULL               | SELECT   |     2 | 247.07 ms     |
| NULL               | SHOW     |     1 | 16.82 ms      |
| NULL               | CREATE   |     1 | 9.60 ms       |
| NULL               | INSERT   |     1 | 268.62 us     |
| NULL               | SET      |     3 | 225.49 us     |
| performance_schema | SHOW     |     2 | 243.25 ms     |
| performance_schema | SELECT   |     1 | 52.18 ms      |
| performance_schema | INSERT   |     2 | 335.52 us     |
| sys                | CREATE   |   120 | 19.16 s       |
| sys                | SELECT   |    83 | 1.54 s        |
| sys                | DESC     |     2 | 627.22 ms     |
| sys                | DROP     |    38 | 504.05 ms     |
| sys                | SHOW     |     2 | 139.58 ms     |
| sys                | INSERT   |     1 | 88.10 ms      |
| sys                | SET      |     3 | 421.86 us     |
+--------------------+----------+-------+---------------+

The NULL schema_name is for statements executed without a default database (i.e no "USE dbname").

You will also need sys installed, but could skip the sys.format_time(...), and just select the SUM(...), in which case the value will be in picoseconds for the latency (same as Jaime's).

Since I can't yet comment (my first answer on here!), I'll also note here that performance schema table data does not replicate (even DML against the setup_* tables in performance_schema), and that the above query will work with the default configuration with 5.6 (performance_schema and the statement instrumentation are both enabled by default there).


Is it possible for a MySQL database to have duplicate tables with the same name? As in the question title - is it possible for a MySQL database to have duplicate tables with the same name? Is there a directive that can be used for a database to allow this?

Normally, when trying to add duplicate tables, MySQL should return an error #1050 - Table 'tablename' already exists, but recently, while working on a project, I noticed that almost every table in the database is duplicated (only the structure, new tables are empty). While I know that some table-creating scripts could be executed, MySQL shouldn't allow this, right?

How to even deal with such an issue? The program causing this is a Magento - a PHP-based eCommerce platform.

There are parts of a dump from this database:

--
-- Table structure for table `adminnotification_inbox`
--

CREATE TABLE IF NOT EXISTS `adminnotification_inbox` (
  `notification_id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'Notification id',
  `severity` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Problem type',
  `date_added` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Create date',
  `title` varchar(255) NOT NULL COMMENT 'Title',
  `description` text COMMENT 'Description',
  `url` varchar(255) DEFAULT NULL COMMENT 'Url',
  `is_read` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification read',
  `is_remove` smallint(5) unsigned NOT NULL DEFAULT '0' COMMENT 'Flag if notification might be removed',
  PRIMARY KEY (`notification_id`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_SEVERITY` (`severity`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_READ` (`is_read`),
  KEY `IDX_ADMINNOTIFICATION_INBOX_IS_REMOVE` (`is_remove`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COMMENT='Adminnotification Inbox' AUTO_INCREMENT=26 ;

--
-- Dumping data for table `adminnotification_inbox`
--
-- there was some data from this table, not relevant here

-- --------------------------------------------------------

--
-- Table structure for table `
                adminnotification_inbox`
--
-- in use(#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)
-- in use (#1146 - Table 'dev_testproject_1_12.
                adminnotification_inbox' doesn't exist)

-- --------------------------------------------------------

I don't think this dump would even run without errors.

Make sure that the table name has no whitespaces that are not displayed in HTML.


How to rollback a SQL Server transaction and use output param? I am trying to set an output param in a stored procedure, but I think that when I rollback the transaction I think I'm also rolling back the assignment to @out. Is that correct?

If so, how can I return a message and rollback a transaction? I am fetching the @out param from C#.

create PROCEDURE [dbo].[sp]
@out varchar(2000) output
AS
BEGIN
    SET NOCOUNT ON
    BEGIN TRANSACTION
    BEGIN TRY
        SET @OUT = "success";
        COMMIT TRANSACTION
    END TRY

    BEGIN CATCH
        set @out = 'not success';
        ROLLBACK TRANSACTION
    END CATCH
END

I was originally doing a

SELECT
    ERROR_NUMBER() AS ErrorNumber,
    ERROR_SEVERITY() AS ErrorSeverity,
    ERROR_STATE() AS ErrorState,
    ERROR_PROCEDURE() AS ErrorProcedure,
    ERROR_LINE() AS ErrorLine,
    ERROR_MESSAGE() AS ErrorMessage;

...but that didn't help, though I would prefer that method.

A more robust way to write this stored procedure would be:

CREATE PROCEDURE dbo.sp
AS
BEGIN
    SET XACT_ABORT, NOCOUNT ON;

    BEGIN TRY
        BEGIN TRANSACTION;

        SELECT 1/0; -- An error!

        /* Other good code omitted*/

        COMMIT TRANSACTION;
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION;

        DECLARE @Message nvarchar(2048) = ERROR_MESSAGE();
        DECLARE @Severity integer = ERROR_SEVERITY();
        DECLARE @State integer = ERROR_STATE();

        RAISERROR(@Message, @Severity, @State);
        RETURN -1;
    END CATCH;
END;

Note the use of XACT_ABORT and nothing of consequence before the BEGIN TRY. Also RAISERROR is often preferred over THROW for reasons covered in the reference below, but importantly:

  In difference to RAISERROR, ;THROW always aborts the batch.

Aborting the batch results in any output parameters not being assigned.

Error handling in SQL Server is quirky in the extreme, so I would encourage you to review the seminal work on the topic by Erland Sommarskog: Error and Transaction Handling in SQL Server.


Problems during migration from SQL Server to MySQL via MySQL Workbench We've found most of the options we needed to include the necessary CHARSET. Here is what we've included in the MySQL my.cnf:

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

But we still get an error which points to a wrong charset and/or collation:

10:17:38 [INF][      copytable]: Statement execution failed: Incorrect string value: '\xF3\xB3\xB4\xAF)<...' for column 'LangText' at row 57

Complete Log file: https://ufile.io/52s13

Can anybody point to a solution for his issue? Thanks!

We need to migrate a SQL Server database to MySQL for the first time (for a customer project). To do so, we've quickly set up a local testing machine to try one of the many tutorials to migrate via MySQL Workbench.

Installed/ prerequisites:

Local Windows 10 (64-bit)
MySQL Workbench 6.3 (Community Server)
MySQL Server 8.0
Microsoft SQL Server Management Studio (SSMS) 17.0
Microsoft SQL Server 2017 (Developer Version)
Driver: SQL Server Native Client 11.0

Unfortunately, we run into some problems during migration and re-importing the DB on another machine. To quickly move forward, we edited the resulting SQL dump with the following queries:

Regarding Export:

sed -i 's/utf8mb4_0900_ai_ci/utf8mb4_unicode_ci/g' mssql_to_mysql_20180730.local.sql
sed -i 's/ENGINE=InnoDB DEFAULT/ENGINE=InnoDB ROW_FORMAT=DYNAMIC/g' mssql_to_mysql_20180730.local.sql
sed -i 's/DEFAULT CHARSET=utf8mb4/ROW_FORMAT=DYNAMIC CHARSET=utf8mb4/g' mssql_to_mysql_20180730.local.sql

Performance:

sed -i '1s/^/SET foreign_key_checks=0;\nSET unique_checks=0;\nSET autocommit=0;\n/' mssql_to_mysql_20180730.local.sql
sed -i -e "\$aCOMMIT;\nSET unique_checks=1;\nSET foreign_key_checks=1;\n" mssql_to_mysql_20180730.local.sql

Additionally, we've adjusted the target db:

SET GLOBAL innodb_default_row_format=DYNAMIC;

How can we set these options during the migration in workbench? Is there an overview on which options can be altered during the migration configuration?

I think F3B3B4AF is beyond the end of established UTF-8 characters.  Perhaps MSSQL allowed, but MySQL did not.

Please provide SHOW CREATE TABLE for the target table.  It had better be using utf8mb4 for the target column.

Do any of these look correct?

                              gb2312, gbk   6  2 '蟪疮'
                                    euckr   6  2 '車눕'
                                     big5   6  2 '馧敞'
                            eucjpms, ujis   6  2 '鶻姦'


TempDB on Solid State Drives on SQL Server Cluster Can solid state drives be used for the TempDB on a SQL Server failover cluster if they are local drive on both machines? It is supported in 2012 but our cluster is a SQL 2005.  The application that it is running isn’t upgradeable to 2012.

If the SSD drives are on the same local path on both servers wouldn't it just rebuilt the TempDB when it failed over? Wouldn't the cluster recognize any drive the OS recognizes? Or are there other issues that need to be accounted for.

I realize that Microsoft won’t support it but the performance gain would be great. Our TempDB on that cluster is the heaviest used DB and we can’t change any code in the application it runs.

Previous to SQL 2012, all SQL Server files had to be on a shared cluster resource.  It's not so much an issue of the recognition of a local path, but just that SQL Server won't allow you to use local storage in a clustered installation.  This is problematic for SSDs because it is usually to expensive to attach SSDs as anything other than direct attached storage.  With SQL 2012, you can now place TempDB on local disk.  You can still use SSDs, they just have to be added as a shared cluster resource.

Are you 100% sure that you can't upgrade to SQL 2012?  SQL 2012 will support SQL 2005 compatability level, meaning your code will be executed as if it were SQL 2005 but it will be on the 2012 engine.  There might be some other hurdles, but this should address the application interface.  Caveat of thoroughly testing applies.


Multiple tables entry with current timestamp I have several different tables that the user can perform an action on, for example:

Vote
Comment
Chat

Each of the above tables will have a timestamp column, to hold the date of creation (it is set to the current time).

I am using long polling to get real-time updates for other users that require these updates.

I assume it would heavier on the database to constantly search multiple tables for rows where time values are greater than some value. The number of these kinds of tables can increase if features are added.

So, I want to create a dummy table called actions, which will contain all the actions that a user performed. There will be another column in that table that will contain what the type of that action is, for example vote, comment, chat etc.

It will also contain a timestamp value, so that my long polling can just search in this actions table for any actions.

If there are actions present that are newer than a certain timestamp, then go to those action type tables (vote, comment, chat), and get rows that are newer than that same value, and return them to the user.

My question is: As I am inserting data to two different tables (and I want both of the timestamp values to be exactly equal), what should I do to maintain this integrity?

I would suggest creating your actions table with a timestamp field using a default of current_timestamp, and have the other tables use regular timestamp fields with no default value.

Then, after inserting a new row into your actions table, you can retrieve that row and insert the required rows into the additional tables using that returned value to populate the relevant fields.

In this way, the values between the tables will match as you require.

You may require an auto-incrementing field in your actions table, and then use the SQL command SELECT LAST_INSERT_ID(); within the same connection to get the auto-incrementing id from the actions table, allowing you to then perform another SELECT in order to get the timestamp value.

It will add a few queries to your overall insert, but will achieve the desired result.


Execute Mysql queries using the same file more than once I have a file with .sql extension in which I write a query like insertions, deletions or updates, and execute it in toadformysql . I repeat the same action many times because I have lot of queries, so it turned out that I have a lot .sql files.
for the first query  It works . but When I tried to add a second query in the same file and execute it  , there are  errors because the first query has already been executed. If the first query is delete for example, it displays an error "no such column" , which is logic beacuse I already delete the column.
Is there a way that I can have a single file in which I add all my queries and while executing it , I won't have errors from old queries like duplicates or others, something like errors handling. It is because I have to keep an history of all queries.
Only the query that I didn't already execute will throw an error if there is.

for example if my first query is

ALTER TABLE adbproject DROP COLUMN imageFormat

and I execute it. for the second time I want to add another query which is:

ALTER TABLE PERSON ADD MATRICULE VARCHAR(50) AFTER CODE;

So the file to be executed line's will be :

ALTER TABLE adbproject DROP COLUMN imageFormat;
ALTER TABLE PERSON ADD MATRICULE VARCHAR(50) AFTER CODE;

but I have logically this error : Can't DROP 'imageFormat'; check that column/key exists. I am searching a way to avoid this error.
Thanks in advance

The problem is, you should not be trying to create an ever-growing set of queries in this manner.

Of course the first step in your example fails on the second run, since the imageFormat column has already been dropped.  There is nothing to restore imageFormatand there is no error handling to cope with the missing column.

I cannot fathom why you would want to build up your queries in such a manner.

If your goal is to record every query that you run, then insert the code into a table that records the time and the code that you run.

But do not expect to run all the commands without errors unless you have scripted the code to also deal with each exception that may arise.


SQL Server parallelism for DML operations I know SQL Server can use parallelism for certain DDL operation e.g. index rebuild or certain SELECT statements  mostly likely in a data warehouse environments when query cost exceeds cost threshold of parallelism.

ORACLE offers parallel DML operations like INSERT, UPDATE or DELETE since version 8i.

Does SQL Server use parallelism for DML operations?

SQL Server supports parallelism for some of its physical operators.

DML statements, at a high level, are logical operations. In other words, you specify what you want done, not how it's done. As long as the logical operation is maintained, SQL Server is free to satisfy the statement using any set of physical operators it deems appropriate -- that applies both to DML and DDL.

SQL Server uses the cost threshold server setting to consider using parallelism for the physical operators chosen in the query plan. There's no difference as far as parallelism is concerned between DDL and DML, because as I said, SQL Server parallelizes the physical operations (i.e., an index rebuild can use the same Index Scan physical operator as a SELECT or UPDATE query could).

So, the short answer is yes, SQL Server can parallelize DML, but aside from the cost threshold setting, you have very little control over it. (That said, it can be restricted by using things like MAXDOP hints.)

Related, but a bit aside: for a query plan that includes parallel operators, the DOP at which the query runs is selected dynamically at execution time, not at plan compile time.


How to wait between PostgreSQL DB restore and reindexing? I've written a script to upgrade PostgreSQL from 8.2.1 to 9.2.1 on a Linux From Scratch distribution:

Start the pg service
Vacuum all DBs (not sure if this is needed)
Backup with pg_dumpall
Stop the pg service
Move away the directory where the data is stored (/var/pg; it's a simple, single-server setup)
Install PostgreSQL 9.2.1
initdb
Start the server
Restore the dumped data
reindexdb all DBs
Recreate the referential_constraints view
Vacuum all DBs (AFAIK required after this upgrade)

Unfortunately reindexing fails in about one third of the executions, even when using a virtual machine which I reset between tries. The code:

PGOPTIONS='--client-min-messages=warning' psql \
    --no-psqlrc \
    --variable=ON_ERROR_STOP=1 \
    --quiet \
    --log-file="$restore_log" \
    --single-transaction \
    --username postgres \
    --file="$sql_backup"
[error checking which stops the script if the import failed]
for database in $(databases); do
    reindexdb --username postgres $database
    [error checking which stops the script if the reindex failed]
done

The restore log shows no error at the end. The last lines of the reindexdb output (anonymized to be able to publish it):

NOTICE:  table "myschema.mytable" was reindexed
reindexdb: reindexing of database "myschema" failed: ERROR:  could not open relation with OID 16738

/var/log/pg/postmasterlog contents during the upgrade:

After a VM reset I found that the OID belongs to a table which is backed up and restored properly. Could it be a problem that the reindexdb step is run immediately after psql has processed the backup file?

Edit: This is getting even worse - This time I encountered another issue I haven't seen before:

reindexdb: reindexing of database "myschema" failed: ERROR:  deadlock detected
DETAIL:  Process 3883 waits for AccessExclusiveLock on relation 17684 of database 16385; blocked by process 3980.
Process 3980 waits for RowExclusiveLock on relation 16513 of database 16385; blocked by process 3883.

In /var/log/messages:

Process 3980: UPDATE tablename SET [...]

Looks like the problem is a cron job which was trying to update the database during the reindexing.

Note that the REINDEX step only applies if you're updating an 8.3.x cluster in-place from x<5 to x>=5. It does not apply for dump and restore. You do not need to perform this step for what you are doing. You won't even be using 8.3, you'll be going straight from 8.2 to 9.2 (right?).

Also, as per the upgrade documentation, you should use the new PostgreSQL's pg_dump when preparing for an upgrade, not the old version.

That said, this really shouldn't be happening; see comments.


SQL Server mirrored database is stuck in recovery mode I have a database that is stuck in recovery mode for few days already. There are multiple threads about this, but the solutions there don't work for me.
Here is what I tried already:
RESTORE DATABASE [DBNAME] WITH RECOVERY
Error: Exclusive access could not be obtained because the database is in use.

ALTER DATABASE [DBNAME] SET OFFLINE WITH ROLLBACK IMMEDIATE
Error: ALTER DATABASE failed because a lock could not be placed on database 'DBNAME'
I get the same error when trying to set the db to SINGLE_USER

exec sp_who2 --> nothing that contains my database, so nothing that I can kill
(Or I need to look for something else ??)

I can't just stop the SQL-service, because it contains too many databases that can't go down.
Who knows what I can do to get the database out of recovery mode? At both the primary and mirror location is the database state 'In recovery'.
UPDATE
I found a process in sp_who2 with command DB STARTUP.
At sys.dm_tran_locks, I see this session has a resource_database_id for the database that is in recovery, so this keeps the database locked. Anyone knows how to fix this without stopping SQL Server?

Restart the db mirroring end point:

--To stop
ALTER ENDPOINT<Endpoint Name> STATE=STOPPED

--To start
ALTER ENDPOINT<Endpoint Name> STATE=STARTED

TECH note: if there are multiple databases, not just the one in error, then stopping and starting the enpoint affects all the databases on that endpoint. This can cause problems on a production system like SharePoint. To fix, go the other mirrored database server and enter the same commands:
 ALTER ENDPOINT  STATE=STOPPED
 ALTER ENDPOINT  STATE=STARTED

How to find the name of the end point?
select * from sys.endpoints  <- works for SQL 2012
The name will be of " type=4 " for mirroring.