Identify why a user can drop a database I have a sql server user that has the ability to drop any database. I've been running the code below to check the rights that the user has in SQL Server but have not been able to identify how the user has the ability to drop databases.  Is there a sql script that can help me identify how this user can drop dbs?  Is there a command to deny them dropping any databases?  (SSMS is not showing the user as part of the dbcreator role)

select USER_NAME(p.grantee_principal_id) AS principal_name,
    dp.type_desc AS principal_type_desc,
    p.class_desc,
    OBJECT_NAME(p.major_id) AS object_name,
    p.permission_name,
    p.state_desc AS permission_state_desc
from    sys.database_permissions p
inner   JOIN sys.database_principals dp
on     p.grantee_principal_id = dp.principal_id
order by principal_name

The output of the query above provides the following three records for the user if it is helpful

  class_desc    object_name permission_name permission_state_desc
  OBJECT_OR_COLUMN  xp_cmdshell EXECUTE          GRANT
  DATABASE          NULL           CONNECT             GRANT
  DATABASE             NULL           CREATE DATABASE GRANT

I would suggest running this query on the master

SELECT
    [UserName] = CASE princ.[type]
                    WHEN 'S' THEN princ.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE princ.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = princ.[name],
    [Role] = null,
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --database user
    sys.database_principals princ
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on princ.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = princ.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col ON col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
WHERE
    princ.[type] in ('S','U')
UNION
--List all access provisioned to a sql user or windows user/group through a database or application role
SELECT
    [UserName] = CASE memberprinc.[type]
                    WHEN 'S' THEN memberprinc.[name]
                    WHEN 'U' THEN ulogin.[name] COLLATE Latin1_General_CI_AI
                 END,
    [UserType] = CASE memberprinc.[type]
                    WHEN 'S' THEN 'SQL User'
                    WHEN 'U' THEN 'Windows User'
                 END,
    [DatabaseUserName] = memberprinc.[name],
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Role/member associations
    sys.database_role_members members
JOIN
    --Roles
    sys.database_principals roleprinc ON roleprinc.[principal_id] = members.[role_principal_id]
JOIN
    --Role members (database users)
    sys.database_principals memberprinc ON memberprinc.[principal_id] = members.[member_principal_id]
LEFT JOIN
    --Login accounts
    sys.login_token ulogin on memberprinc.[sid] = ulogin.[sid]
LEFT JOIN
    --Permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
LEFT JOIN
    sys.objects obj ON perm.[major_id] = obj.[object_id]
UNION
--List all access provisioned to the public role, which everyone gets by default
SELECT
    [UserName] = '{All Users}',
    [UserType] = '{All Users}',
    [DatabaseUserName] = '{All Users}',
    [Role] = roleprinc.[name],
    [PermissionType] = perm.[permission_name],
    [PermissionState] = perm.[state_desc],
    [ObjectType] = obj.type_desc,--perm.[class_desc],
    [ObjectName] = OBJECT_NAME(perm.major_id),
    [ColumnName] = col.[name]
FROM
    --Roles
    sys.database_principals roleprinc
LEFT JOIN
    --Role permissions
    sys.database_permissions perm ON perm.[grantee_principal_id] = roleprinc.[principal_id]
LEFT JOIN
    --Table columns
    sys.columns col on col.[object_id] = perm.major_id
                    AND col.[column_id] = perm.[minor_id]
JOIN
    --All objects
    sys.objects obj ON obj.[object_id] = perm.[major_id]
WHERE
    --Only roles
    roleprinc.[type] = 'R' AND
    --Only public role
    roleprinc.[name] = 'public' AND
    --Only objects of ours, not the MS objects
    obj.is_ms_shipped = 0
ORDER BY
    princ.[Name],
    OBJECT_NAME(perm.major_id),
    col.[name],
    perm.[permission_name],
    perm.[state_desc],
    obj.type_desc--perm.[class_desc]

This should give you a pretty good idea of the roles that have access to your master database and see weather or not the user has any of those roles. You can also run this against any of your other databases to check the user's permissions on a database by database level. This should be an important tool to help track this down.


Synchronize MySQL databases between local and hosted servers I'm developing a software in Microsoft Blend and I need it to be synchronized with my online server MySQL database. I have local MySQL database which I use for the app. The app also have an online version in PHP with a MySQL database. So the user can enter either by the online version or offline app. So I need both the databases to be synchronized so when a data is entered online it will be reflected in the software app. I need separate databases because when the app is not connected to internet, it should work perfectly and when reconnected it should start synchronization both ways i.e. from local to online and from online to local database.

So basically I need 2 MySQL databases to be synchronized both ways where one is local and other online.

Also how to find out when a synchronization breaks?

Schedule a job to backup one database and apply the backup script to another, that way you never need to worry about the synchronization as it is done automatically on a schedule


Putting SQL Server data files in a different partition I have a SQL Server and I put the data files (the .mdf) on a different partition.

Windows runs from C: and the data files and logs are on D:

Is it advantageous or disadvantageous to separate the data files on different partitions?

There is only one disk and it has been partitioned to C: and D: My main focus is SQL Server performance all around like reading and writing.

It won't matter for performance but it could matter for other reasons.

To get better performance, you need to have it split across different physical hardware; splitting files across logical partitions does not help or hurt performance. But I wouldn't worry about this unless you start seeing disk activity as a bottleneck.

There are benefits from this approach:

You can back up the D: drive without having to do a system image backup.
You can restore the D: drive without restoring the entire system.
If you get to a point where you need more space, it is easier to move the D: drive to another physical disk than to have to move the C: drive or relocate the database files.


Xampp 3.2.1 error: MySQL shut down unexpectedly I logged into Xampp today and it wouldn't launch MySQL.  It said there was an error and it shut down unexpectedly.  Here are the errors from the error log.  Hoping someone has seen this before, because I have little experience fixing this stuff.

2013-11-12 18:21:39 8324 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'user'; try to repair it
2013-11-12 18:27:36 5800 [Note] Plugin 'FEDERATED' is disabled.
2013-11-12 18:27:36 16ac InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2013-11-12 18:27:36 5800 [Note] InnoDB: The InnoDB memory heap is disabled
2013-11-12 18:27:36 5800 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2013-11-12 18:27:36 5800 [Note] InnoDB: Compressed tables use zlib 1.2.3
2013-11-12 18:27:36 5800 [Note] InnoDB: Not using CPU crc32 instructions
2013-11-12 18:27:36 5800 [Note] InnoDB: Initializing buffer pool, size = 16.0M
2013-11-12 18:27:36 5800 [Note] InnoDB: Completed initialization of buffer pool
2013-11-12 18:27:36 5800 [Note] InnoDB: Highest supported file format is Barracuda.
2013-11-12 18:27:36 5800 [Note] InnoDB: The log sequence numbers 0 and 0 in ibdata files do not match the log sequence number 1957328 in the ib_logfiles!
2013-11-12 18:27:36 5800 [Note] InnoDB: Database was not shutdown normally!
2013-11-12 18:27:36 5800 [Note] InnoDB: Starting crash recovery.
2013-11-12 18:27:36 5800 [Note] InnoDB: Reading tablespace information from the .ibd files...
2013-11-12 18:27:36 5800 [Note] InnoDB: Restoring possible half-written data pages
2013-11-12 18:27:36 5800 [Note] InnoDB: from the doublewrite buffer...
2013-11-12 18:27:37 5800 [Note] InnoDB: 128 rollback segment(s) are active.
2013-11-12 18:27:37 5800 [Note] InnoDB: Waiting for purge to start
2013-11-12 18:27:37 5800 [Note] InnoDB: 5.6.11 started; log sequence number 1957328
2013-11-12 18:27:37 5800 [Note] Server hostname (bind-address): '*'; port: 3306
2013-11-12 18:27:37 5800 [Note] IPv6 is available.
2013-11-12 18:27:37 5800 [Note]   - '::' resolves to '::';
2013-11-12 18:27:37 5800 [Note] Server socket created on IP: '::'.
2013-11-12 18:27:37 5800 [ERROR] Fatal error: Can't open and lock privilege tables: Incorrect key file for table 'user'; try to repair it

Go to Xampp/mysql/bin/my.ini

open it and add the following line:

innodb_force_recovery = 1

Atleast this was what solved it for me.
I'd backup my .cnf file to be on the safe side.
After adding the single line you should be able to start your mySQL service again.

P.S. I think anything greater than 0 works, since the log error simply states > 0.


dumping database with "compressed data is corrupt" errors I'm using pg_dump to dump a large (396 GB) PostgreSQL database:

pg_dump --clean --create mydatabase

After running for a day, a statement fails with ERROR:  compressed data is corrupt and pg_dump exits.

100% data integrity is not my top priority here. It's probably just one broken row that's preventing me from moving or backing up a db that took many weeks to create. I'm fine with losing that row.

Is there any way to create a dump that ignores such data errors? I found nothing in the pg_dump docs.

If not, how do I find and delete all corrupted rows in my table? Rows in other tables are pointing to them and will need to be deleted as well.

According to a grep in the sources, this error

  ERROR:  compressed data is corrupt

happens in case of a decompression failure of a LZ-compressed TOAST'ed value.

See http://doxygen.postgresql.org/tuptoaster_8c.html#abcb4cc32d19cd5f89e27aeb7e7369fa8

At the row-level storage, large values are stored as pointers to tables in the pg_toast schema containing the actual data.

How do I find and delete all corrupted rows in my table ?

Assuming you know what table it is (if not, iterate over the tables, starting with these with the largest columns), something that might work is:

COPY (select ctid,* FROM tablename ORDER BY ctid) TO '/path/to/file'

ctid is a pseudo-column indicating the physical location of a row in the form of (page,index in page), so this will dump the contents in their order in data files.

This COPY command should error out when it reaches the offending row, but at this point it should have streamed some output (to be confirmed in practice). The end of this output should indicate the ctid up to which the data is not corrupted.

Starting from this ctid, it should be possible to pinpoint the offending row with a dichotomic approach, running queries such as

SELECT ctid,length(t.*::text) FROM table t WHERE ctid>='(?,?)' ORDER BY ctid LIMIT 10

The point of length(t.*::text) is that it must pull all the decompressed data, as opposed to, e.g. count(*).

Once the corrupted row(s) are found, DELETE FROM table WHERE ctid='(?,?)' can be used to delete them.

Note that if VACUUM is working on this table, if might change the ctid of certain rows. Turning off autovacuum or doing all the work inside a transaction should avoid that potential problem.

EDIT: a web search on the error message reveals a blog post from Robert Berry suggesting a method that seems less fastidious than the above. Essentially, create this function:

create function chk(anyelement)
  returns bool
  language plpgsql as $f$
    declare t text;
    begin t := $1;
      return false;
      exception when others then return true;
    end;
  $f$;

and run all rows through it to find the ctid of the corrupted ones.

select ctid from table where chk(table);


Drop auto increment hack w/o alter table? I was looking around for a quick way to remove an auto increment from the definition of a primary key.  As best I can tell the only way to do it is w/ an alter table or dumping all the data into a new schema sans auto_increment.

Just for fun I tried the following to see if it would work.

Create table test1 (innodb) w/ an auto increment PK
Insert a couple rows w/o specifying PK values, letting auto inc do its job
Create table test2 like test1;
Alter table test2 modify pk_col int(10) unsigned not null; -- no auto_inc
Shutdown mysql
backup test1.frm; cp test2.frm test1.frm
Restart mysql
select * shows all rows as expected, w/ previously auto_inc created PKs 1,2 and 3
Insert a row w/o a PK specified, gets created w/ PK value as 0 (the default value).
Insert a row specifying PK of 5
Shutdown mysql
restore original auto_increment test1.frm
Restart; show create table lists auto_inc
Test insert a row w/o specifying PK, generated auto_inc value of 6 (+1 the highest value even though the last one created by virtue of auto_inc was 3

Seems like that works!

For curiosity I ran both .frms through od and diffed that output.  All I got

9c9
< 0010020 000001 015000 002000 000400 003400 001400 000000 001400
---
> 0010020 000001 015000 002100 000400 003400 001400 000000 001400
45c45
< 0020760 002000 005003 000012 000001 015000 007400 000000 020403
---
> 0020760 002000 005003 000012 000001 015000 000100 000000 020403

Thoughts, comments?

I tried something similar just now
Here is MySQL for My PC
mysql> select * from information_schema.global_variables where variable_name='datadir' or variable_name like 'versio%';
+-------------------------+------------------------------+
| VARIABLE_NAME           | VARIABLE_VALUE               |
+-------------------------+------------------------------+
| VERSION_COMMENT         | MySQL Community Server (GPL) |
| VERSION                 | 5.5.12-log                   |
| VERSION_COMPILE_MACHINE | x86                          |
| DATADIR                 | C:\MySQL_5.5.12\data\        |
| VERSION_COMPILE_OS      | Win64                        |
+-------------------------+------------------------------+
5 rows in set (0.00 sec)

I will run this using MyISAM

Step 01) create a table called 'rolando'
Step 02) insert 'dominique' and 'diamond'
Step 03) copy the table structure to 'pamela'
Step 04) alter 'pamela' to not have auto_increment
Step 05) In DOS, copy rolando.MYD to pamela.MYD
Step 06) run REPAIR TABLE pamela; (Rebuild pamela.MYI)
Step 07) run SELECT COUNT(1) FROM pamela;
Step 08) run SHOW CREATE TABLE pamela\G
Step 09) run SELECT * FROM pamela;
Step 10) insert 'carlik' into pamela
Step 11) run SELECT * FROM pamela;

Let's see if these steps are kosher.
Here are Steps 1-4
mysql> drop table if exists rolando;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table if exists pamela;
Query OK, 0 rows affected (0.00 sec)

mysql> create table rolando
    -> (
    ->     name varchar(20),
    ->     id int not null auto_increment,
    ->     primary key (id)
    -> ) ENGINE=MyISAM;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into rolando (name) values ('dominique'),('diamond');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from rolando;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
+-----------+----+
2 rows in set (0.00 sec)

mysql> create table pamela like rolando;
Query OK, 0 rows affected (0.05 sec)

mysql> show create table rolando\G
*************************** 1. row ***************************
       Table: rolando
Create Table: CREATE TABLE `rolando` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=3 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) NOT NULL AUTO_INCREMENT,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.02 sec)

mysql> alter table pamela modify id int(11) unsigned not null;
Query OK, 0 rows affected (0.11 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table pamela\G
*************************** 1. row ***************************
       Table: pamela
Create Table: CREATE TABLE `pamela` (
  `name` varchar(20) DEFAULT NULL,
  `id` int(11) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        0 |
+----------+
1 row in set (0.01 sec)

mysql>

Here is Step 6
C:\>copy C:\MySQL_5.5.12\data\test\rolando.MYD C:\MySQL_5.5.12\data\test\pamela.MYD
        1 file(s) copied.

C:\>

Here are the rest of the Steps starting at Step 7
mysql> repair table pamela;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.pamela | repair | warning  | Number of rows changed from 0 to 2 |
| test.pamela | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.03 sec)

mysql> select count(1) from pamela;
+----------+
| count(1) |
+----------+
|        2 |
+----------+
1 row in set (0.00 sec)

mysql> insert into pamela (name,id) values ('carlik',3);
Query OK, 1 row affected (0.00 sec)

mysql> select * from pamela;
+-----------+----+
| name      | id |
+-----------+----+
| dominique |  1 |
| diamond   |  2 |
| carlik    |  3 |
+-----------+----+
3 rows in set (0.00 sec)

mysql>

Dangerous game, isn't it ???
Guess what? Stuff like this is actually published in "High Performance MySQL : Optimization, Backups, Replication, and more", Pages 146-148 under the Subheading Speeding Up ALTER TABLE. Page 147 Paragraph 1 says:

The technique we are about to demonstrate is unsupported,
undocumented, and may not work. Use it at your risk. We advise you to
back up you data first!

I also had an earlier post when someone ask a similar question : Can I rename the values in a MySQL ENUM column in one query?
You got guts, @atxdba !!!


Disable top (n) Sort Optimization for a specific view I have a a view with a complex logic and three levels of deepness (nested views). Because of the complexity I can't paste the execution plan.

As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.

This (top N) queries in the view perform super bad because the optimizer is choosing a different execution plan for this view (afaik CQScanTopSortNew)

I've tried to do some optimizations for the top (N) use case, like using hash joins but this spoils the non top (n) use cases.

The non top (n) performs good. I would like to know how can I prevent the optimizer to choose a different execution plan when it has a top (n) clause without dramatically change the structure or functionality of the view.

For instance, if I add a select distinct inside the view, the optimizer chooses the correct plan always, but the functionality of the view changes.

I can't think of a fully transparent way to achieve what you want with views without disabling row goals in general, which probably won't suit your purposes.

  As the purpose of the view is to provide some business analytics to data analysts, while they are developing reports they use to check a sample of the view by doing a select (top N) query.

Perhaps you could convince them to use a TOP (x) PERCENT instead? This has to count the number of rows in the whole set to work out the percentage. Materializing the full set comes with a tempdb cost, but this could be manageable if the data is a reasonable size. In any case it will give a plan without a row goal.

SELECT TOP (1) PERCENT TV.* FROM TheView AS TV;

They may find this more natural than choosing an arbitrary sort order (and one which is guaranteed to need a physical sort.

All that said, it's possible that a detailed investigation would reveal tuning options to make the query with TOP as quick as it should be. That's consulting work though, so beyond what can be addressed here on the basis of estimated plans alone.


How to restart MS SQL server right, what is best practice to do that? I`m quite new to DB administration.

I have a problem when I restart SQL server, one of the databases goes into Recovery mode and automatically repairs the DB in 30 min. I don't know why this happens.

Here is what I did, but it seems that not enough:

I have SQL Server Microsoft SQL Server 2014 (SP2-GDR).

install Windows updates
SQL: shrink all DB and logs files
SQL: killed all Runtime Brocker`oв
SQL: stopped SQL agent
SQL: stopped SQL server
Restart Windows Server.

UPDATED :

1) How big is the database?

4.5 GB + 400MB Logs

2) Why are you restarting the instance?
Install Windows updates, and also apps that connect to that SQL server become slower and slower with time. We restart the server once a month usually. It takes 15 minutes to start, but this time it took 45 min (while recovering DB).

3) Is this a server or a pseudo-server (desktop running Server OS)?

its virtual machine. Running on server hardware.

4) Is the Server configured for application or background processes?

The server runs SQL and additional background apps/ web services

5) How much RAM does the server have? How many processors?

16 GB RAM
4 CPU

6) SAN storage or local disks? .... The more details you provide the better we can provide you with an adequate answer.

We buy hosting as service, can`t tell exact hardware architecture. But we have SSD disk (where SQL DB stored).

7) 30 Minutes might well be the best SQL Server can do. It depends on a lot of factors.

It usually takes up to 15 min. I think I miss some steps that I need to do to close all DB in right way.

You almost certainly have a very large number of virtual log files (VLFs) in the problematic log file of the database in question.  Shutting down and restarting SQL Server causes the recovery process to run on all databases on the instance.  For databases that have a very large number of VLFs this process can be agonizingly slow.

You can see the VLFs via this DBCC command:

DBCC LOGINFO;

Each row represents one VLF.  How many do you have?  I'm betting you have a very high number of VLFs (rows).

You can rebuild the log file to remove the excess VLFs; simply shrink the log file, then grow it in sensible increments to the prior size.

I wrote a blog post showing how to do that here.

If you're concerned about shutting down the SQL Server instance without causing recovery to run, you can issue this command before you shut down SQL Server:

shutdown;

However, if there are existing connections, the command will wait until all connections are closed.  See the Microsoft docs for more detail.  SHUTDOWN WITH NOWAIT; will cause SQL Server to immediately shutdown, without waiting for existing connections to close, however this will trigger recovery at the next startup, and is exactly what happens when you shutdown the instance via the control panel, or via shutting down the server itself.


PostgreSQL not running on Mac The error in its entirety reads:

  psql: could not connect to server: No such file or directory. Is the
  server running locally and accepting connections on Unix domain socket
  "/tmp/.s.PGSQL.5432"?

This is my second time setting up Postgresql via Homebrew on my Mac, and I have no clue what is going on. Previously, it had been working. At some point, I must've entered a command that messed things up. I'm not sure. Now, whenever I enter a SQL command from the command line, I receive the above message. I've run a command to check whether the server is running, and it apparently is not. If I attempt to start the server using

  $ postgres -D /usr/local/pgsql/data

I receive the following error:

  postgres cannot access the server configuration file
  "/usr/local/pgsql/data/postgresql.conf": No such file or directory

I've uninstalled and reinstalled Postgresql via Homebrew, but the problem persists. I'm completely at a loss as to how to get this working. Any help would be appreciated.

The problem can also be attributed to a crashed process that left postmaster.pid file behind.
$ brew services stop postgresql
# adjust path accordingly to your install, e.g. `/opt/homebrew/var/postgres/postmaster.pid`
$ rm /usr/local/var/postgres/postmaster.pid
$ brew services start postgresql


Doing lookup transformation I have a contactTypeDescription and I want to find the corresponding contactTypeId for that description. I am doing the following, however it throws a warning saying that atleast one column must be mapped. What am I doing incorrectly?

Left hand item is the "Available Input Columns". That's the data in your pipeline.

The right hand item is the "Available Lookup Columns" - that's the reference data.

When you click the check button, that adds the lookup entry into the pipeline. Thus, you are adding ContactTypeId as a new column and you are replacing ContactTypeDescription.

That's likely not what you want to be doing.

Instead, you want to click and drag the ContactTypeDescription column from the left side to the matching column on the left. You then want to unclick the ContactTypeDescription from the right hand side because, why replace the value with the same thing?

That will tell the SSIS engine that a match is performed by these two things being equal.

Here comes the nuances of the lookup.

Depending on whether you specify a Full Cache (default) or a Partial/None cache, will determine what matching rules are used. Full cache is going to be case sensitive as it uses .NET equality. Partial/None is going to rely on your database rules (generally case insensitive in my world).

The choice on the Connection tab of selecting a table is fine for small data sets but if you don't need LastModifierIdentityId, why have the source system read it from disk, stream it across the network, have SSIS cache that in memory if you're never going to use it? Instead, write a simple SELECT there. SELECT T.ContactTypeId, T.ContactTypeDescription FROM dbo.Table AS T; Now you're only pulling the data you need, the source system can possibly use an index to satisfy that query, there's less traffic over the network and you're not bloating your lookup cache.

Speaking of bloating, the Full Cache, which is generally what you want, does have an impact on the start time of your package. Before it begins executing, all the requested data from the source system must be brought into the local memory before the first bit of data starts moving. That's a great strategy if you're loading a 50 GB file and you need to compare it against 1000 possible codes. On the otherhand, when you're loading trading data and the source is 1000 rows and your reference table is 50 GB, that's a sizeable delay before your package can even start processing. In a situation like that, you might be interested in a partial/none cache.


sum of count in where clause (or having) in the same table with group by I have a products table with columns id, binno, name, sku, inwarddate and more.

Each bin stores 20 or 30 products. We need to group by binno for dispatch and show sum of items in each bin. That makes the query look like:

SELECT  count(*) as total,binno, model, color, sku FROM products
 where location=1 and sku='4'
 GROUP BY binno

This is working. It gives the following result:

Binno model color sku total
231    3    2      4   20
232    3    2      4   20
...

Now I need to select bins where total of this count=500 (or any other number) to limit the results to show 25 bins. Required number may not be 500. it may be any number but the user knows the no of items in a bin (just like ATM user).

I need sum of count(*) in having or where. That will solve my problem.

First limit the result to 500 (or whatever) and then GROUP BY:

SELECT
    binno,
    count(*) AS total
FROM
    ( SELECT binno
      FROM products
      WHERE location = 1
        AND sku = '4'
      ORDER BY binno             -- the order may be different
      LIMIT 500
    ) AS lim
GROUP BY
    binno ;

Optimal index for this query:

INDEX (location, sku, binno)


Is there a way to see what were the Candidate Execution Plans generated by the Query Optimizer? The Query Optimizer creates more than one possible execution plan. How can I see all the plans generated before the executed one was chosen?

Microsoft says:

  The Query Optimizer must analyze the possible plans and choose the one
  with the lowest estimated cost.

Please, notice that I'm not talking about Execution Plan Caching and Reuse, I'm talking about candidate execution plans, the ones that were generated but not selected to be executed.

According to Benjamin Nevarez's article The SQL Server Query Optimizer:

  The generation of candidate execution plans is performed inside the
  Query Optimizer using transformation rules, and the use of heuristics
  limits the number of choices considered in order to keep the
  optimization time reasonable. Candidate plans are stored in memory
  during the optimization, in a component called the Memo.

Would it be possible to visualize the candidate plans rendered the way we can Display an Actual Execution Plan?

The optimizer has heuristics to cut down the search time by discarding plans and partial plans before they are fully explored if it believes they are worse than current best solutions it has seen so far in the search.  So, it doesn't actually fully generate all of the alternatives.  Also, the XML representation is really something that gets generated at the end of the search - the internal representation is somewhat different.  Unfortunately, there is no current public feature for you to explore other possible plans for the same query.  With a debugger we can look at the structure that holds all of this during the optimization process.  It's called the Memo.  You can read more about the Cascades framework on which SQL's optimizer is based here


Estimate large database size and speed First I want to know how to estimate the database size regarding the biggest table it will contains. I've the following :

+----------+------------------+------+-----+---------+-------+
| Field    | Type             | Null | Key | Default | Extra |
+----------+------------------+------+-----+---------+-------+
| users_id | int(32) unsigned | NO   | MUL | NULL    |       |
| s        | binary(16)       | NO   | PRI | NULL    |       |
| t        | binary(16)       | NO   | PRI | NULL    |       |
| x        | binary(16)       | NO   | PRI | NULL    |       |
+----------+------------------+------+-----+---------+-------+

This is the table that will be significant for the size (the other table is only to maintain user data (id, user, pass, email), I don't expect them to have more than 100 entries).

I expect around 7.61263 * 10^9 entries into the table above. I made this simple calculations : 7.61263 * 10^9 + (4 Bytes + 16 Bytes + 16 Bytes + 16 Bytes) ~= 395 GB. But I don't know how to take into account the overhead coming from the dbms (indexes, database structure ...) ?

How to estimate the database size ?

What about the speed and the stability of MySQL running with this large table. Do I have to split the data over two or more databases in order to reduce the size ?

As far as your asking about both size and speed, yes, Russell's right. You're going to have to actully fill a table with sample data, and test representative queries to see how they perform.

For the size issues, you can't always calculate the exact size for the indexes, but see the MySQL documentation for Data Type Storage Requirements and the appropriate documentation for the storage engine you're using. for information about row overhead and some guidance for estimating indexes.

(for instance, you're going to hit the 2**32 limit on rows in MyISAM, so you'll have to build with big tables support)

As for if you'll need multiple databases -- you shouldn't.  If you need to spread it across multiple tables, you can always use partitioning, which I think should also help if you're pushing up against file system limits.


How to debug when OS kills postgres for high memory usage I am running Postgres 9.4 server in a VPS running Ubuntu. It gets killed often (multiple times in a day).
This is the message from dmesg

Out of memory: Kill process 1020 (postgres) score 64 or sacrifice child
Killed process 1020 (postgres) total-vm:445764kB, anon-rss:140640kB, file-rss:136092kB

How to debug what is causing this crash? Is it long running queries or some misconfiguration of the server or lots of idle connections open?

It's the OOM killer in the Linux kernel. Disable VM overcommit, per the advice in the PostgreSQL manual.


Is SQL Server Data Tools a paid product of Microsoft or a Free Product? I don't find an evidence in Microsoft Pages regarding SQL Server Data Tools Licensing and Price details.

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

Here all the instructions to download and install SQL Server Data Tools and no term mentioning it is Licensed.

As I understand searching for such details are to up to our responsibility but I didn't get a clue for it. So my question is SQL Server Data Tools is Free or Not? If Free then Shall we simply download and install as instructed in the below link? If Paid, where is the pricing details?

https://docs.microsoft.com/en-us/sql/ssdt/download-sql-server-data-tools-ssdt?view=sql-server-2017

It is free and a part of Visual Studio Community Edition.


What are the hazards of upgrading a SQL Server service pack? Are there any specific pitfalls/standard considerations when upgrading an instance of MS SQL Server to a higher service pack? Is there anything special that is generally done besides making sure there are current backups of system and user databases?

I'm asking the question in general since I presume the answer is similar regardless of version or situation. However, in the case that I'm mistaken and the specifics do matter, the situation that prompts my question is an upgrade of SQL Server 2008 from SP2 to SP3, running on Windows Server 2008 with failover clustering.

Well one thing you may want to consider doing is validating your application(s) still work on a test instance that contains the same database and upgrading it to the newer service pack. While rare and usually changes are backward compatible, there could be behavior changes to things like the optimizer that would be impossible to guess exactly how they might affect your application without testing. The risk is certainly less with a service pack than a version upgrade, for example, but installing a service pack on a cluster does not exactly have a trivial "undo" button - so in this scenario the backups won't really help you directly (they are still an absolutely sound idea, of course).


When to prefere a truncate over a batch-delete? When I browse DBA-Reactions I understand nearly all the .gifs but with this one I have trouble:

https://twitter.com/DBAReactions/status/613840893322338304

I mean truncate obviously doesn't create the transaction log as a batch delete does but still there are cases where you are pretty sure that you can simply truncate a complete table. Are there any other concerns besides that?
So basically I would love to understand the complete difference.

There are certain circumstances where you cannot use the TRUNCATE TABLE command and must use the DELETE command instead.

  You cannot use TRUNCATE TABLE on tables that:

  • Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)

  • Participate in an indexed view.

  • Are published by using transactional replication or merge replication.

  For tables with one or more of these characteristics, use the DELETE statement instead.

  TRUNCATE TABLE cannot activate a trigger because the operation does not log individual row deletions. For more information, see CREATE TRIGGER (Transact-SQL).

There is a good explanation on MSDN:

https://msdn.microsoft.com/en-gb/library/ms177570.aspx

Also, see this question on Stack Overflow which has a lengthy discussion on the topic.


Sql Server 2016 maintenance plan with weekly full backup and 4h differential backups I am new to SQL server management, I'm mostly a developer tasked with managing a SQL Server :-(

That aside, please bear with me.

Right now I have made a maintenance task to create a Full Backup every day at 2:00 am, and afterwards delete any backup older than 2 days. That however leaves a whole workday worth of data to be lost in case something went wrong at the right (wrong?) time. So to minimize data loss without without making performance take a hit, I', trying to create one weekly Full Backup and then a Differential Backup every 4 hours. I am however puzzled at how to do this from the management studio, or anywhere else for that matter.

So my question is:

How can I configure this backup 'policy' on my Server?

Sounds like you are using maintenance plans for your Full backup.  If so, make a new plan with a Backup task in it and change the backup type in the GUI to Differential, then set the schedule.   As RDFozz says, consider also using t-log backups every 15-60 minutes throughout the day.   If not, and your databases are in FULL recovery model, you will likely get crazy file growth in your .ldf files :)

Note that the task in this image says full, because that is the default.  When I click OK, it will change.


Multiple Partition Keys? This is a follow-on from a previous question.

I've never worked with SQL Server partitioning but I currently faced with designing a database for which the volumes probably warrant it. The system is for coupons. The coupons are to be issued periodically, usually every six weeks although there will also be ad-hoc issuance - eg for a special event. There are 15 million customers and for each issuance event, every customer will receive 6 different coupon types, giving a total of 90 million coupon instances. We need to track the coupon instance redemption data and maintain this for 6 months, although typically a coupon is only valid for six weeks. Any redemption request for an invalid coupon will not reach the database because it will be validated by the POS till.

Over a six month period we'll need to store up 1 billion rows in the Coupon Instance table and up to 200 million (assuming max 20% redemption rate) in the Redemption table.

There will be two main queries to these tables:
1. select from coupon instance where coupon barcode = x (for redemption)
2. select from coupon instance where loyalty card number = x (to issue a coupon via till printer)

To help with the 1st query I could partition the instance table by issuance event and then embed the issuance event in the barcode. So, if we have an issuance event that creates 50m records in the instance table, we have a single partition for these records - SQL is then able to go to the correct partition for a given barcode substring (a 2 digit issuance event number)

What about the 2nd query? I guess it's not possible to partition two ways? If I were to implement this using database sharding then my sharding algorithm would dictate that I need to store the same record on multiple shards in 50% of records. This would work but it's expensive in storage space and I'd like to avoid sharding because of the "re-balancing" required when an addition shard is added

Is there any special way partitioning can help here or should I simply partition to help with the 1st query and resign myself to seek of a full table high index for the 2nd?

Thanks

Rob

You really don't need partitioning for this to work efficiently, which is what I told you on your last question as well...even if you have billions of rows.

If you cluster on the BarcodeID (which I am assuming is unique) and put a nonclustered index on LoyaltyCardID it should work just fine.  These are NOT complicatd queries with a lot of additional logic from the sounds of things, and simple seek operations are extremely efficient on their own.

Are you getting pressure to partition or is it just something that you have decided to do?


mysql.sock missing: php connection works but cant connect from terminal When I try to run mysql from the command line I get the following:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

interestingly the php scripts that connect to mysql in /var/www appear to be connecting just fine and when I stop MySQL these scripts can no longer connect as expected. However when I start mysql I continue to get the missing .sock error from the command line while my php scripts once again are connected to the database they are supposed to.

I have tried restarting mysql in various ways (restarting, starting then stopping, sudo pkill mysql) and still no luck. The only sock file on my system is /run/mysqld/mysqld.sock; but, without being sure that this is the cause of the command line error I dont want to haphazardly make symbolic links in my /tmp directory.

How can I resolve the issue of connecting to mysql via the command line? some suggestions I have read recommend reinstalling MySQL which I don't want to do considering my php connection is working.

First you need to check your MySQL config file my.cnf and make sure it contains socket parameter

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

[mysqld]
socket=/var/mysql/mysql.sock

if those parameters not presented then you need to add them and start MySQL service.

after that you need to change MySQL folder permission sudo chmod -R 755 /var/lib/mysql/ and make sure it is owned by MySQL user.

finally start MySQL service.

Note: you need to make sure ports are opened for MySQL inside firewall


WAL archive: FAILED (please make sure WAL shipping is setup) I am trying to configure Barman to backup. When I do a barman check replica I keep getting:

Server replica:
WAL archive: FAILED (please make sure WAL shipping is setup)
PostgreSQL: OK
superuser: OK
wal_level: OK
directories: OK
retention policy settings: OK
backup maximum age: FAILED (interval provided: 1 day, latest backup age: No available backups)
compression settings: OK
failed backups: OK (there are 0 failed backups)
minimum redundancy requirements: FAILED (have 0 backups, expected at least 2)
ssh: OK (PostgreSQL server)
not in recovery: FAILED (cannot perform exclusive backup on a standby)
archive_mode: OK
archive_command: OK
continuous archiving: OK
archiver errors: OK

I am using Postgresql 9.6 and barman 2.1; I am not sure as to what the issue is could someone help?
Here is my Barman server configuration:

description = "Database backup"
conninfo = host=<db-ip> user=postgres dbname=db
backup_method = rsync
ssh_command = ssh postgres@<db-ip>
archiver = on

When running into an error like this, be sure to run barman switch-xlog --force --archive <server_name> as noted in the barman documentation to verify the WAL archiving process.


MySQL5.7 Find_In_Set alternative I have a table that contains User information from different countries. Information like - Name, Country, LastLoginDate, IsUpdated, Certification.

This table has 1.5 million rows. Now to filter this, I have a stored procedure that takes input as -
UserNames = 'John, Matt, Robin'

And the query looks like -

SELECT Name, Country, LastLoginDate FROM Users
WHERE find_in_set(Users.Name, UserNames) <> 0
;

The EXPLAIN statement says that this has type - ALL and doesn't use any index. Is there any way I can take the text and create a table with input values and join to my core table which will help using index and faster ouput?

You can split the search parameters and use a join instead. Example

-- Some data to test with
CREATE TABLE testdata ( x varchar(100) not null primary key );
INSERT INTO testdata (x) VALUES ('a'),('c'),('e');

-- This can be a temporary table, add all numbers up to
-- the maximum number of tokens in parameter string
CREATE TABLE positions (pos int not null primary key);
INSERT INTO positions (pos) VALUES (1),(2),(3),(4),(5);

-- Table that hold tokens, this can also be a temporary table
CREATE TABLE tokens (token varchar(100) not null primary key);

-- Split the search string in individual tokens, and store in table
INSERT INTO tokens
SELECT substring_index(
           substring_index(T.tokens, ',', p.pos)
         , ','
         , -1
       ) as token
FROM (SELECT 'a,b,c,d' as tokens) as T -- 'a,b,c,d' is the search string
JOIN positions p
  ON char_length(T.tokens)
    - char_length(replace(T.tokens, ',', ''))
    >= p.pos - 1;

-- Get all testdata that matches search string
SELECT t.*
FROM testdata t
JOIN tokens tt
    ON t.x = tt.token;

x
-
a
c


Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index? Is it possible to create an InnoDB table without a Primary Key?

Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that table still be a clustered index, but around a secondary key?

Thanks.

If you have a unique key that is not null, it gets promoted internally to be the clustered key (or if you have more than one that matches this criteria - the first one does.)

If you don't have a unique key that is not null, a hidden key is created that is 6 bytes in length.

There is no way to specify the key to cluster around, and there is no way to not have a clustered index.


How to get the PC models with identical speed? I am trying to get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).

Result required as : model with the bigger number, model with the smaller number, speed, and RAM. The result I am expecting is

╔═══════╦═══════╦═══════╦═════╗
║ model ║ model ║ speed ║ ram ║
╠═══════╬═══════╬═══════╬═════╣
║  1233 ║  1121 ║   750 ║ 128 ║
║  1233 ║  1232 ║   500 ║  64 ║
║  1260 ║  1232 ║   500 ║  32 ║
╚═══════╩═══════╩═══════╩═════╝

and table structure is

╔═══════╦═══════╦═════╗
║ model ║ speed ║ ram ║
╠═══════╬═══════╬═════╣
║  1121 ║   600 ║ 128 ║
║  1121 ║   600 ║ 128 ║
║  1121 ║   750 ║ 128 ║
║  1232 ║   450 ║  32 ║
║  1232 ║   450 ║  64 ║
║  1232 ║   500 ║  32 ║
║  1232 ║   500 ║  64 ║
║  1233 ║   500 ║  64 ║
║  1233 ║   750 ║ 128 ║
║  1233 ║   800 ║ 128 ║
║  1233 ║   900 ║ 128 ║
║  1260 ║   500 ║  32 ║
╚═══════╩═══════╩═════╝

The query I am trying is this and I am using MySQL

select pca.model, pcb.model, pca.speed, pca.ram
from pc pca
where pca.speed in (
    select pcb.speed
    from pc pcb
    where pca.speed = pcb.speed
);

This should do the trick:

select distinct
  pca.model, pcb.model, pca.speed, pca.ram
from pc pca
join pc pcb
  on (pca.speed = pcb.speed
  and pca.ram = pcb.ram
  and pca.model > pcb.model)


How to create a mapping table? I have 2 tables as below
CITY_A

ID
CODE
CITY

001
90
PARIS

002
90
PARIS

003
85
PARIS

004
60
SYDNEY

005
75
SYDNEY

006
75
SYDNEY

CITY_B

ID
CODE
CITY

001
IN
PA

002
OUT
PA

003
OUT
PA

004
IN
SYD

005
OUT
SYD

006
IN
SYD

The content is same in both tables but the representation is different. I have 2 mapping here for CITY column and CODE column.
Mapping for CITY_A

CODE
CITY
MEANING

90
PARIS
ACTIVE

85
PARIS
NOT_ACTIVE

60
SYDNEY
ACTIVE

75
SYDNEY
NOT_ACTIVE

Mapping for CITY_B

CODE
CITY
MEANING

IN
PA
ACTIVE

OUT
PA
NOT_ACTIVE

IN
SYD
ACTIVE

OUT
SYD
NOT_ACTIVE

Now i have to compare both tables based on the mapping and find the mismatches. So my expected output is

ID
CITY
RESULT

001
PARIS
MATCH

002
PARIS
MISMATCH

003
PARIS
MATCH

004
SYDNEY
MATCH

005
SYDNEY
MATCH

006
SYDNEY
MISMATCH

Currently I'm using CASE with Temp table. It is too big.
SELECT A.ID , CASE WHEN A.CODE = 90 AND A.CITY = 'PARIS' THEN 'ACTIVE'
                   WHEN A.CODE = 85 AND A.CITY = 'PARIS' THEN 'NOT_ACTIVE'
               -- FOR OTHER CITIES
               END AS A.CODE_MEANING,
               CITY
INTO #TEMP_CITY_A
FROM CITY_A A

SELECT B.ID,CASE WHEN B.CODE = 'IN'  AND B.CITY='PA' THEN 'ACTIVE'
                   WHEN B.CODE = 'OUT'  AND B.CITY='PA' THEN 'NOT_ACTIVE'
              -- FOR OTHER CITIES
            END AS B.CODE_MEANING,
            CASE WHEN B.CITY = 'PA' THEN 'PARIS'
                 WHEN B.CITY = 'SYD' THEN 'SYDNEY'
            END AS CITY
 INTO #TEMP_CITY_B
 FROM CITY_B B

SELECT A.ID ,A.CITY, CASE WHEN A.CODE_MEANING!=B.CODE_MEANING THEN 'MISMATCH' ELSE 'MATCH' END AS RESULT
FROM #TEMP_CITY_A A
JOIN #TEMP_CITY_B B ON A.ID = B.ID AND A.CITY = B.CITY

Which is the most efficient way?

Create intermediate derived tables of your main and mapping tables and then join these to compare and use a CASE statement to check the matching rows.
CREATE TABLE #MAP_CITY_A (CODE TINYINT, CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_A (CODE,CITY,MEANING)
VALUES (90, 'Paris', 'Active'),(85, 'Paris', 'Not_Active'),(60, 'Sydney', 'Active'),(75, 'Sydney', 'Not_Active')

CREATE TABLE #MAP_CITY_B (CODE VARCHAR(3), CITY VARCHAR(10), MEANING VARCHAR(10))
INSERT #MAP_CITY_B (CODE,CITY,MEANING)
VALUES ('IN', 'PA', 'Active'),('OUT', 'PA', 'Not_Active'),('IN', 'SY', 'Active'),('OUT', 'SY', 'Not_Active')

SELECT a.ID,
       a.CITY,
       CASE
          WHEN a.MEANING = b.MEANING THEN 'MATCH'
          ELSE 'MISMATCH'
       END as RESULT
FROM
(
    SELECT CITY_A.ID, CITY_A.CITY, #MAP_CITY_A.MEANING
    FROM CITY_A
    JOIN #MAP_CITY_A ON CITY_A.CODE = #MAP_CITY_A.CODE
         AND CITY_A.CITY = #MAP_CITY_A.CITY
) a
JOIN
(
    SELECT CITY_B.ID, #MAP_CITY_B.MEANING
    FROM CITY_B
    JOIN #MAP_CITY_B ON CITY_B.CODE = #MAP_CITY_B.CODE
         AND CITY_B.CITY = #MAP_CITY_B.CITY
) b
ON a.ID = b.ID
ORDER BY a.ID


Retrieve column info from next column I have a query that is working but I figure I could enlist some help to make it more efficient. However before I show the query I would like to get a few answer on how others would do this.

What the query is supposed to do is to select data from a table, however some of the data is calculated within the SELECT statement. One of those calculations is the retreival of data from the next row. For example, I select a date from the current record, but I also want to know the next row's date as well.

Sample data:

=======================
date        |   comment
=======================
2013-01-01  |   comment
2013-03-03  |   comment

Desired output:

======================================
date        |   leadDate   |   comment
======================================
2013-01-01  |   2013-03-03 |   comment
2013-03-03  |     null     |   comment

Right now  I am using a stored function and a cursor to get this info and I tried using a LEFT JOIN which messed up my variables I am using.

I just wanted to get input from others on how they would do this.

The idea of next or previous rows makes sense only in the presence of explicitly specified ORDER BY clause.

Now assuming that you need your resultset ordered by date you can achieve your goal either by using a subquery

SELECT date,
      (
        SELECT date
          FROM table1
         WHERE date > t.date
         ORDER BY date
         LIMIT 1
      ) leadDate,
       comment
  FROM table1 t;

or by leveraging user(session) variables

SELECT date, leadDate, comment
  FROM
(
  SELECT date, CAST(@d AS DATE) leadDate, @d := date, comment
    FROM table1 CROSS JOIN (SELECT @d := NULL) i
   ORDER BY date DESC
) q
 ORDER BY date;

Sample output (in both cases):

+------------+------------+----------+
| date       | leadDate   | comment  |
+------------+------------+----------+
| 2013-01-01 | 2013-03-03 | comment1 |
| 2013-03-03 | NULL       | comment2 |
+------------+------------+----------+

Here is SQLFiddle demo


Backup Structured Data and Unstructured Data seperately in SQL Server - Is it Possible? Can I get some inputs on below:

SQL Server Version : 2014 SP2 GDR with AOAG environment.

Is it possible to backup Structured Data & Unstructured Data (FileStream) separately in SQL Server ?

For structured data backup, I am reading about the partial backup to exclude FILESTREAM file groups.

If backup Structured Data & Unstructured Data (FileStream) separately in SQL Server is possible , can I restore Structured Data & Unstructured Data (FileStream) separately ? How to restore FileStream data alone ? or structured data alone ?

No, there's no such direct feature. You would work at the filegroup level, and at that level SQL Server doesn't care whether it is a filestream or something else you have in that filegroup.

And when you restore a part of the database, you need to have the primary file group (since this contains all metadata) and whatever other filegroups you want. The non-restored filegroups will be "defunct", and will forever have those "ghost" filegroups hanging around (there's no way to delete/remove a defunct filegroup).


Jet OLEDB driver refuses to open a linked table where the original has a lock - any way to force this? I have a requirement to read reference data from several access databases for an ETL job.  The data owners have produced a file with a series of linked tables that consolidates them all into one place.  However, if one of the base files is open with a lock (.ldb) file the OLEDB provider will refuse to open the linked table.

The SSIS package is set up with several data flow tasks reading from OLEDB sources.  To clarify, these are reference tables, and not necessarily being updated at the time - the .mdb file has a lock (.ldb) file.

Is it possible to configure the MS jet OLEDB driver to force-open the linked tables, ignoring the locks?

A couple of alternatives that we've looked into are:

Configure the linked tables the other way around - put the sources in the central database and link to them from the other access DBS.  The business are reluctant to do this, and we don't have the clout to force it.
Copy the base databases to a staging area and open the copies - this is our plan B, although it involves shifting about 2GB across a slow-ish WAN link and adds latency right at the start of the ETL job (pretty much everything is dependent on these reference tables).  I'd rather avoid the latency if possible.

EDIT: See my answer - the problem was due to lack of write permission on the lock file and an ambiguous error message returned by the driver.

Found the problem - permissions issue in disguise.  The Jet driver needs to have write access on the directory so it can update the lock files.  When it can't write to the lock files it throws its toys out of the cot with a misleading error message.


Oracle 11g XE 64bits installation problem on Windows 7 I had to install Oracle 11g XE on my laptop under Windows 7.
So I follow this Tutorial. The installation didn't even fail once, and seem to be OK.
But every time i tried to connect to it with SQLPLUS, it failed with ORA-12638 error...

SQL> connect
Enter user-name: system
Enter password:
ERROR:
ORA-12638: Credential retrieval failed

After some search I found this Web Site and attempt to resolve my problem with the second solution. But then I get another kind of error : ORA-01034 and ORA-27101...

SQL> connect
Enter user-name: system
Enter password:
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist
Process ID: 0
Session ID: 0 Serial number: 0

It seem that my database didn't even exist to begin with...
And if looking in [...]\oraclexe\app\oracle\oradata\XE, there is no files.

When looking the logs at [...]\oraclexe\app\oracle\product\11.2.0\server\config\log, I found that during the installation process, the application wasn't even able to connect to the database created.

Example in the file : postDBCreation.log

SQL> connect "SYS"/"&&sysPassword" as SYSDBA
ERROR:
ORA-12638: Credential retrieval failed
[...]
SQL> startup ;
ORA-12638: Credential retrieval failed
SQL> select 'utl_recomp_begin: ' || to_char(sysdate, 'HH:MI:SS') from dual;
SP2-0640: Not connected
SQL> execute utl_recomp.recomp_serial();
SP2-0640: Not connected

Another file in the same directory : CloneRmanRestore.log

SQL> startup nomount pfile="D:\Applications\oraclexe\app\oracle\product\11.2.0\server\config\scripts\init.ora";
ORA-12638: Credential retrieval failed
SQL> @D:\Applications\oraclexe\app\oracle\product\11.2.0\server\config\scripts\rmanRestoreDatafiles.sql;
SQL> set echo off;
SP2-0640: Not connected
SP2-0640: Not connected
SP2-0640: Not connected
ERROR:
ORA-12638: Credential retrieval failed

I am log as a domain user with administration right on the laptop, so why does the installation fail without putting an explicit error ? And how to ensure that the installation of Oracle 11g XE on my laptop work ?

After more than 6 hours to fix this, I found this post which help me to understand the problem.

The problem was that I tried to install Oracle 11g XE with my user account connected on the enterprise domain. The installation process, then, wasn't able to connect to the database. I suspect that it try to connect to it from the enterprise domain, and not from localhost.

To solve this, I had to redo the installation with a local admin account on the computer and not as an admin user on my enterprise domain.

Found another post for reference.

I hope this answer will help other guy which encounter the same problem.


Move PDB to a new host We're using Oracle 12c, and have two separate servers running Oracle, with a CDB with 2 PDBs.

What is the easiest way of getting the data from server A to server B, as a one-time job (not replication)?

So far I can imagine the following alternatives:

Do a TRANSPORTABLE RMAN backup, copy it to the second host, do a restore there
Do a datapump export, move it to the second host, restore there
Unplug the PDB, copy its xml-file and data to the second host, plug it in there

About alternative 3, I'm not even sure if it's a real option.
Which of these alternatives would be easiest to accomplish?

I've tried to read up on the Oracle documentation, but it's very verbose and not at all easy to comprehend (which I suppose is why they're selling so much support services).

Option 3.

Unplug the PDB:

Unplugging a PDB

ALTER PLUGGABLE DATABASE salespdb UNPLUG INTO '/oracle/data/salespdb.xml';

Copy the files (XML + database files), then perform the checks listed below:

Plugging In an Unplugged PDB

Finally plug in the PDB:

Examples of Plugging In an Unplugged PDB

CREATE PLUGGABLE DATABASE salespdb USING '/disk1/usr/salespdb.xml'
  NOCOPY
  TEMPFILE REUSE;


This is a classic greatest-n-per-group problem. They frequently arise in a whole host of areas and, like Analytic functions (see below) are well worth studying.

Nowadays, it is typically solved by using Analytic (aka Window) functions - see the fiddle here.

You can use this query -

WITH cte AS
(
  SELECT
    ROW_NUMBER() OVER (PARTITION BY col1, col2 ORDER BY col3 DESC) AS rn,
    col1, col2, col3
  FROM test
  ORDER BY col3 DESC
)
SELECT * FROM cte
WHERE rn = 1

Result -

rn  col1    col2    col3
1   2   xyz     2015-09-13
1   1   abc     2015-09-11
1   3   tcs     2015-01-18

Analytic functions are well worth getting to know - they are very powerful and you will find that they will repay you many times  for any effort you put into learning them. Run the inner query on its own - experiment, it's how I learnt. BTW, it's always worth tagging your question with the version of PostgreSQL that you are using!

A more traditional method of doing this would be

SELECT x, y, mc FROM
(
  SELECT col1 AS x, col2 AS y, MAX(col3) AS mc
  FROM test
  GROUP BY col1, col2
) AS tab
ORDER BY mc

Same result - also on the fiddle.


Split a pattern string into columns in select query I have a person table with below structure.

CREATE TABLE person(id integer, details text);
INSERT INTO person(id,details) VALUES(1, "name:Tahir,age:30,sex:male");

I want to split details column by comma and show results in difference columns. Like, in this example result would be id, name,age, sex

I believe that is possible with aggregate functions and I tried to produce a query like below

SELECT id,
if(person.details like '%name:%',cast(substring_index(substring_index(person.details,'name:',-1),',',1) as unsigned),null) as `name`,
if(person.details like '%age:%',cast(substring_index(substring_index(person.details,'age:',-1),',',1) as unsigned),null) as `age`,
if(person.details like '%sex:%',cast(substring_index(substring_index(person.details,'sex:',-1),',',1) as unsigned),null) as `sex`
FROM person

above query fetches data correctly for age but name and sex gets 0. Could you please figure out what I am missing here?

https://www.db-fiddle.com/f/cqyqb7Vhvs7kKYifFywmkV/6

Does this cover your expected result?

SELECT SUBSTRING_INDEX(name, ':', -1) name,
       SUBSTRING_INDEX(age, ':', -1) age,
       SUBSTRING_INDEX(sex, ':', -1) sex
FROM (SELECT SUBSTRING_INDEX(details, ',', 1) name,
             SUBSTRING_INDEX(SUBSTRING_INDEX(details, ',', 2), ',', -1) age,
             SUBSTRING_INDEX(details, ',', -1) sex
      FROM   person) t;

name  | age | sex
:---- | :-- | :---
Tahir | 30  | male

dbfiddle here


How can I get a unique array in PostgreSQL? The following

SELECT ARRAY[a,b,c,d]
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

Returns {foo,bar,foo,baz} of type text[]. I would like to get {foo,bar,baz} of type text[] with one of the duplicate bar elements removed? Does PostgreSQL have a unique function that works on a text-array, or an anyarray of anyelement?

While there is no function to accomplish that, you can use

unnest() to convert an array of elements, to a table of rows of one-column,
DISTINCT to remove duplicates
ARRAY(query) to recreate the row.

That idiom looks like,

ARRAY( SELECT DISTINCT ... FROM unnest(arr) )

And in practice is applied like this,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e))
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

If you want it sorted you can do,

SELECT ARRAY(SELECT DISTINCT e FROM unnest(ARRAY[a,b,c,d]) AS a(e) ORDER BY e)
FROM ( VALUES
  ('foo', 'bar', 'foo', 'baz' )
) AS t(a,b,c,d);

And that can all can be written with CROSS JOIN LATERAL which is much cleaner,

SELECT ARRAY(
  SELECT DISTINCT e
  FROM ( VALUES
    ('foo', 'bar', 'foo', 'baz' )
  ) AS t(a,b,c,d)
  CROSS JOIN LATERAL unnest(ARRAY[a,b,c,d]) AS a(e)
  -- ORDER BY e; -- if you want it sorted
);

Answer inspired by RhodiumToad on irc.freenode.net/#postgresql


SQL Server 2008 R2 returning wrong record I have a SQL Server 2008 R2 hosting a database with which my software works through the ODBC API. The database has a table, called Users, which has a number of columns, but there are 3 columns (3 first ones in the Table Designer, if it matters) instrumental to understanding the problem:

UserID - INT, PK, identity, incremented by 1, starts from 10
NetworkUserID - BIGINT
NetworkID - INT

Fields 2 and 3 are indexed.

There is a stored procedure, which receives NetworkUserID and NetworkID through the input parameters and returns UserID through an out parameter, here is a semi-pseudocode of the procedure:

PROCEDURE [dbo].[Procedure]
@NetworkID INT,
@NetworkUserID BIGINT,

@UserID INT OUT
AS
BEGIN
SET @UserID = 0

-- check whether the user exists
IF NOT EXISTS
(
    SELECT [UserID]
    FROM [Users]
    WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID
)
BEGIN

    -- user does not exist, create a new record for him
    INSERT INTO [Users]
        ([NetworkUserID], [NetworkID], [some_other_fields])
    VALUES
        (@NetworkUserID, @NetworkID, @some_other_values)

END
ELSE
BEGIN

    -- user has already registered, update his profile
    UPDATE [Users] SET [some_irrelevant_fields_are_being_updated_here, UserID_is_not_touched] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID

END

-- get the internal user ID
SELECT @UserID = [UserID] FROM [Users] WHERE [NetworkID] = @NetworkID AND [NetworkUserID] = @NetworkUserID

RETURN
END

Here is the problem - when the physical server running this SQL server starts to get low on memory, the SQL server starts to slam the disk due to massive swapping.

This results in overall, pretty significant, slowing down of query execution and here the problem pops up:

occasionally the SQL server returns the wrong UserID for the specified pair of NetworkID and NetworkUserID. This happens very randomly, so I cannot track it down. But it only happens when the SQL server slows down due to swapping.

I have instructed the SQL server not to consume more than 3/4 of available RAM, but it doesn't help, because the remaining memory is eventually eaten up by a leaky system service, which eventually leads to swapping anyway. From time to time I see the following messages in the logs of my software produced by the ODBC API:

  SQLFetch: state = 40001, native error = 1205, message = [Microsoft][SQL Server Native Client 10.0][SQL Server]Transaction (Process ID 63) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

  SQLDriverConnect: state = 08001, native error = 258, message = [Microsoft][SQL Server Native Client 10.0]Shared Memory Provider: Timeout error [258].

Apparently, these messages have something to do with the described above error.

Did anyone experience anything like that?

How can the SQL server return a totally wrong record by a uniqie pair of values NetworkID + NetworkUserID?

First, make sure your SQL Server is fully patched and up to date.  For 2008 R2, at this time I'd say the hard minimum is build 10.50.6220 (SP3 + MS15-058), and you really should be at 10.50.6537 (SP3 + MS15-058 + TLS 1.2 capability).

Second, make sure you have a unique index on the combination of NetworkUserID, NetworkID (with the highest cardinality one of those two first).

Third, patch your host OS and move that leaky service to another machine.
Or, if you absolutely cannot, then reboot the server often enough that it doesn't run out of memory before the next reboot.  Or buy more RAM for it.

My expectation is that EITHER the patching will fix it, OR putting the unique index on will fail because you've got duplicates in the table already.

The other thing that might be an issue is that you have an actual hardware fault, and may have corrupt data - this is actually very rare.

make sure you have SQL Alerts set up and running; Glen Berry has a script on sqlskills.com to do this.
run DBCC CHECKDB with DATA_PURITY first, on ALL databases on the instance.
make sure you have CHECKSUM checking on every database.
rebuild all your indexes (ALTER INDEX ALL on  REBUILD).

just in case CHECKSUM was added late, and some of the pages don't have it yet because they've never been touched.
and make sure every table has a clustered index, while you're at it.

check for existing records of disk errors (event log, hardware logs, etc.)
run chkdsk in Windows on all your drives.
if you're on RAID, try a consistency check and/or patrol read or other validation.
run underlying hardware diagnostics.


Generic TVP tradeoffs? Is there a best practice or strategy for table types used in TVPs? For instance, given the following:

CREATE TABLE dbo.Colors (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Shapes (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Materials (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
);

CREATE TABLE dbo.Items (
    Id int identity PRIMARY KEY,
    Name nvarchar(100),
    ColorId int FOREIGN KEY REFERENCES dbo.Colors (ID),
    ShapeId int FOREIGN KEY REFERENCES dbo.Shapes (ID),
    MaterialId int FOREIGN KEY REFERENCES dbo.Materials (ID),
);

If you implemented a stored procedure for searching items that needed to support selecting multiple colors, multiple shapes, and multiple materials via TVPs (think checkbox lists in the UI), would you create three separate table types, one for every TVP, or would you create a single type for using it across all three?

In other words, this:

CREATE TYPE dbo.ColorIds AS TABLE (Id int);
CREATE TYPE dbo.ShapeIds AS TABLE (Id int);
CREATE TYPE dbo.MaterialIds AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds ColorIds READONLY,
    @ShapeIds ShapeIds READONLY,
    @MaterialIds MaterialIds READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

Versus this:

CREATE TYPE dbo.Ids AS TABLE (Id int);
GO

CREATE PROCEDURE dbo.SearchItems
    @ColorIds Ids READONLY,
    @ShapeIds Ids READONLY,
    @MaterialIds Ids READONLY
AS
BEGIN
    PRINT 'Do something here'
END
GO

The sample is deliberately contrived; the real use case consists of a lot more tables which although have different columns, all have a ID int primary key. Because of this, I personally am much more inclined to do the latter. It's far less overhead, but I'm curious to know if there are any cons I should be aware of in doing this. This is of course for TVPs and TVPs only (I would never mix different entities in a real table, or any other structure of a more permanent nature.)

While at it, what is your naming convention for naming table types and TVPs?

I personally use the latter, more generic version in my systems. I have two tables that I can think of off the top of my head: UniqueIntegerTable and UniqueStringTable - as you can imagine, they are defined as follows:

CREATE TYPE Utils.UniqueIntegerTable AS TABLE (
    [Value] INT NOT NULL PRIMARY KEY
);
CREATE TYPE Utils.UniqueStringTable AS TABLE (
    [Value] NVARCHAR(50) NOT NULL PRIMARY KEY
);

I prefer to have generic TVP's so that I don't clog up my schema's with multiple types that are basically the same. The performance is exactly the same as if you defined an explicit type as in your first example and it has the benefit that it creates less code for me to maintain.

I know one argument that I have previously heard for using explicit types that are bound to tables is that it is easier to understand their usage. I personally don't agree with this. There is nothing preventing me from defining a stored procedure using the wrong type (but has the correct shape for my needs). Instead, I can give the variable a good name to infer usage and the contents of the table:

CREATE PROCEDURE dbo.UpdateEmployees (
    @employeeIds Utils.UniqueIntegerTable READONLY
)
BEGIN
SET NOCOUNT ON;

-- Use table as needed.

END
GO

In terms of naming conventions, I don't know of any official standard but the one I use is to append Table to the end of the type name. I know that this is not really all that different from prefixing things with tbl but I'm ok with it in this instance. As with all naming conventions, pick one that you feel is easy to work with it - but once you do, stick to it. Naming conventions are only useful if you are consistent.


Postgres inherit indexes for partitioned tables I have a table with approximately 60 million rows that I have partitioned by state into 53 sub-tables.  These tables "inherit" the big table like so:

CREATE TABLE b2b_ak (LIKE b2b including indexes, CHECK ( state = 'AK') ) INHERITS (b2b8) TABLESPACE B2B;

My question is this:  if I do not build the indexes on b2b8 until after the copy statement completes, do the sub-tables inherit the indexes?  In other words I want to do this:

Create b2b8
Create b2b8_ak inherits b2b8
COPY b2b8 FROM bigcsvfile.csv
CREATE INDEX CONCURRENTLY

And have the whole thing turn out to have created all of the indexes on the sub-tables.

As explained before, INHERITS does not copy index definitions.

Therefore, you need to state both inheritance and index copying.

    CREATE TABLE sub_tab (LIKE tab INCLUDING ALL) INHERITS (tab);

Thank you, @dezso, for your remark.


What mongodb database layout would fit better I'm currently writing an application using the MEAN stack, pretty much only for fun and because MEAN is "cool" at the moment.

Therefore I'm thinking about the database layout. The data needed to be saved represents a music collection. So I had two ideas. But because I'm quite new to NoSQL, I don't know which one I should use or if both of them are rubbish.

First idea:

One big collection "artists" with an array "albums". This array also has an array called "songs". Here I don't know what to do about playlists!

Second idea:

Four collections "artists", "albums" ,"songs" and "playlists" all linked using the _id   field - This is how I would do it in a traditional database, like MySQL.

Which one would in your opinion fit better and - that's what I'm even more interested in - why?

I think your first idea is better.  The second way, as you state, is how you would model the data in an RDBMS.  If you're going to use MongoDB for fun, you might as well explore the fact that it has a different data model, and structure your collections accordingly.  While I'm sure performance is not going to be an issue for the scale of this project, keeping the data in a single document avoids joins, which can be expensive.  It's commonly held that, within MongoDB environments, denormalization is faster (see here).

In the schema describe in your first idea, playlists could all be subdocuments in a kind of catch-all artist document called "various".  I imagine the same would be done in a relational implementation, with the table artist having a record for "various", to cover compilation albums and soundtracks, for example.

I have a JSFiddle here with a JSON representation of what a music collection might look like implemented as your first idea.

Of course, there is yet another way, and that is a collection albums, with most documents having a key artist, which could either be a reference to the _id from a document in a collection artists, or just have the name of the artist itself.  In this implementation, playlists wouldn't have this key; or, alternatively, it could be an array with all the artists with tracks in the playlist.


How Many Nodes For MySQL Cluster Setup? I quit not fully understand how exactly MySQL Cluster does to bring faster queries and faster databases operations.

I have

1 MGM cluster (management cluster)
1 MySQL API cluser (mysqld)
2 Storage clusters (node database)

To get fast queries, should I add more storage clusters or more MySQL API?

Thanks.

The primary purpose of MySQL Cluster is not performance -- it's high availability. By making sure every component has a possibility of failover (though not in your case), you ensure that you can remain operating without interruption even through emergencies and crashes. Unless your whole data center goes down, of course.

Though MySQL Cluster also tends to give benefits to performance, because the more storage nodes you have, the more you split up your data. Searching half as much data for a given row is faster. Searching a quarter as much data (if you add two more storage nodes) is even faster.

Though this works best if one of your searches naturally touches data on only one storage node. If you need to run range queries, the query will have to visit many storage nodes, and then gather the results in the MySQL node. This can actually be worse for performance than a conventional, non-cluster architecture.

So it's important to keep in mind that your application and queries have to be designed carefully to take advantage of MySQL Cluster (this also applies to any other sharded architecture). For example, an app that always accesses individual rows, instead of arbitrary sets of rows. If you just port your existing database to MySQL Cluster without this in mind, you can (and likely will) see performance get worse, not better.

One other way MySQL Cluster offers a performance boost: it stores all data in RAM on all the storage nodes. RAM is about 100,000 times faster than disk, so even as you fetch data over a network, it can be a lot faster than accessing a disk in a conventional setup. I worked with a performance architect who said, "remote memory is faster than local disk."


Using HASHBYTES() yields different results for nvarchar and a variable I'm using server-side hashing to transmit passwords, then running PBKDF2 in the database to store the hashed password + salt combination.

Hashing nvarchar(max) and a @variable holding the same value yielded different results with the HASHBYTES() function.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', 'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

Yields the following:

NVARCHAR_INPUT                             | VARIABLE_INPUT
0xA03BEF0E3EC96CC7C413C6646D3FEC6174DA530F | 0x74B55C42E1E0AB5C5CDF10B28567590B240355C3

This is SQL Server 2012. This particular database is running SQL Server Express, so I'm also curious if this question is version agnostic.

If you want a quoted string to be NVARCHAR (treated as Unicode), you need to prefix it with N.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

SELECT HASHBYTES('SHA1', N'password5baa61e4c9b93f3f0682250b6') AS NVARCHAR_INPUT,
       HASHBYTES('SHA1', @hash) AS VARIABLE_INPUT

This will show matching hashes.

NVARCHAR_INPUT                               |VARIABLE_INPUT
0xCF01AF0DCECF41BA0106A264666544C2590A4660   |0xCF01AF0DCECF41BA0106A264666544C2590A4660

The prefix isn't necessary here because it's declared as NVARCHAR and the string will be converted.

DECLARE @hash NVARCHAR(MAX) = 'password5baa61e4c9b93f3f0682250b6'

Though I've run into some problems with that, so I'll usually make it clear:

DECLARE @hash NVARCHAR(MAX) = N'password5baa61e4c9b93f3f0682250b6'

If you want some explanation of why they can be hashed differently, start with this example:

DECLARE @n NVARCHAR(1) = N'N'
DECLARE @v VARCHAR(1) = 'N'

SELECT DATALENGTH(@n), DATALENGTH(@v)


Why can't temporary tables reference regular (non-temp) tables in PostgreSQL? Why can't temporary tables references non-temp tables in PostgreSQL? When I run the following

CREATE TABLE foo ( a int PRIMARY KEY );
CREATE TEMP TABLE bar ( a int REFERENCES foo );

I get,

  ERROR:  constraints on temporary tables may reference only temporary tables

I thought this used to work, so I went back to PostgreSQL 8.4 and I see even then it used to error,

  ERROR:  cannot reference permanent table from temporary table constraint

Why is this though? Why can't a temporary table reference a non-temporary table.

There are two sides to a foreign key constraint:

When inserting a child record (or updating its foreign key), the parent record must exist
When deleting a parent record (or updating its primary key), a child record must not exist

Postgres implements this by installing triggers on both tables to perform the existence checks. But the parent trigger simply won't work if the child is a temp table: the trigger needs to fire for all database users in order to guarantee referential integrity, but the temp table data is only visible within the session which created it, so the constraint cannot be enforced.

Even if it were possible, it's probably a bit too intrusive; it's rarely desirable to have a DELETE on the parent fail just because some other user is referencing it from a temporary dataset. But if you really need to maintain integrity in this case, you can get much the same effect by locking the parent records with a SELECT ... FOR KEY SHARE.


DB2 ROLLFORWARD option "TO END OF LOGS" - does it read archive logs? I read about ROLLFORWARD option on link http://www-01.ibm.com/support/knowledgecenter/SSEPGG_9.7.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0001978.html?cp=SSEPGG_9.7.0%2F3-6-2-4-106 but I could not find answer to my issue.

I have database in Archive mode.

I am using DISK option in LOGARCHMETH1

I made full ONLINE backup at Monday at 23:59

I have archive logs for Tuesday, Wednesday and Thursday.
Let's say just for example one for each day: S0000001.LOG and S0000002.LOG and S0000003.LOG.

In Friday I have active log S0000004.LOG.

Let's say just for testing purpose I want to do the restore of Online backup made at Monday and all transactions committed during  Tuesday, Wednesday,Thursday and Friday. So I want to restore everything from Monday to Friday

I thought by reading all manuals that I just need to perform after restore ROLLFORWARD TO END OF LOGS

db2 rollforward db maxdb75 to end of logs and complete;

I thought that this command to end of logs it will include Archive Logs(S0000001.LOG + S0000002.LOG + S0000003.LOG.)+Active Logs(S0000004.LOG)

But one good database expert told me that: "When rolling forward to end of logs without specifying overflow log path only active logs will be read"

So I have 2 questions:

What in my example will be restored in case of "to end of logs" command
Is this the command if after the restore I want to rollforward everything from Monday To Friday in my exampe: db2 rollforward db maxdb75 to end of logs and complete overflow log path "(/data/archiveLogsPath)";

Thank you in advance!!!!

Whoever told you "When rolling forward to end of logs without specifying overflow log path only active logs will be read" was wrong.

When you have LOGARCHMETH1 set to DISK, TSM, USEREXIT or VENDOR, the DB2 log manager EDU (db2logmgr) is responsible for moving log files to and from the archive location.  When performing a ROLLFORWARD operation, DB2 will determine which log files it needs, and db2logmgr will retrieve files for processing if they aren't already in the active log path.

Now, if you have LOGARCHMETH1 set to LOGRETAIN, and you are manually moving log files from the active log path to somewhere else, only then will you have to specify OVERFLOW LOG PATH (or move/copy the transaction log files back to the active log path).

So: to end of logs means just what it sounds like:  DB2 will replay all transactions present in log files between the backup time and when the restore took place.


Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance I have come across three old databases sitting on SQL Server 2000 which I need to move to 2012. I believe the standard approach is to restore into a 2005 or 2008 instance, update, re-export and finally restore into 2012.

Fine, except we have no 2005 or 2008 instances available.

Are there any workarounds or other methods that might be worth trying?

For information, the databases only contain 15-20 tables and a few views, they appear very simple and the backups are only 100-200MB in size.

No, there is no workaround to upgrading a database directly from 2000 to 2012.

Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), including:

Import/Export wizard
SSIS
BCP
Manual queries using a linked server from 2012 or an application

However these will not necessarily bring over other things like users, permissions, roles, etc. And not everything will create your objects in the right dependency order. So they are more work and error-prone. In my experience it would be worthwhile to just put up an intermediate instance temporarily and then do two backup+restore operations - which will be easier, faster, and less error-prone than any of the above methods.

If your databases are smaller than 10GB, you can install a copy of Express (free) in a few minutes, and use that. You can download 2008 R2 Express here. I don't think there are any Enterprise features in 2000 around database structure etc. that would block that upgrade (a bigger concern for more modern versions).

If your database is too large for Express, you should be able to find Developer Edition for $49 or so (or even cheaper - I found 2005 on eBay for $37), or if you have an MSDN subscription, you should be able to get any SKU from there.

Or you can get away with using an Evaluation Edition, which is amazingly still being offered for download for older versions (e.g. I found 2008 here), however I'm on a phone so was not able to confirm the download for that version still works.


Strange behaviour DBCC Shrinkfile I am attempting to run a dbcc shrinkfile in chunks of 1GB against a database where 95% of the data has been archived and deleted. I'm leaft with a 235GB file where 9GB is data/indexes. I want to shrink this down to 50GB. I know that shrinking database files is bad, it causes fragmentation etc. As part of the data purge/shrink we also have a rebuild idnex script.

When I run my dbcc shrinkfile script against the database on my own workstation (quad core, 12GB RAM, 2 x SATA drives), the shrink takes around 8-10 minutes.

When running the identical code against an identical copy of the database post data purge, in our testing envrionment, (80+ cores, 128GB RAM, SSD SAN), it takes 70 minutes. To note, there is little activity on this server at the time of the shrink file running. It has been run 4 times with identical results.

I then took a different approach, of moving the remaining 9GB to a different filegroup and physical file. Running dbcc shrinkfile on the empty 230GB file to shrink it down to 50GB, on my own workstation takes < 1 minute.

With this same approach, on the testing environment, it again takes 70+ minutes.

I have taken a snapshot of waitstats before and after as per Brent Ozar's script during the 70 minute run on the test environemnt, and the waittypes returned showing nothing to be conerned about. Top 3 rows below:

Second Sample Time Sample Duration in Seconds  wait_type   Wait Time (Seconds) Number of Waits Avg ms Per Wait
2013-05-28 11:24:22.893 3600    WRITELOG    160.8   143066  1.1
2013-05-28 11:24:22.893 3600    CXPACKET    20.9    13915   1.5
2013-05-28 11:24:22.893 3600    PAGELATCH_EX    11.1    443038  0.0

Windows event log shows nothing unusual. I am heading scratching at this point, why it is taking so long on the ninja hardware, compared to my standalone workstation.

Shrinkfile on a datafile is a single-threaded operation, reusing a small memory buffer.

So the Ninja hardware hasn't got an edge with the extra memory and the 80 cores.

Your local PC however has the benefit of local I/O latency (local disk, i.e. not having to make multiple trips to the SAN).


Postgresql streaming replication slow on macOS I am using PostgreSQL 10.1 on MAC on which I am trying to set up streaming replication. I configured both master and slave to be on the same machine. I find the streaming replication lag to be slower than expected on mac. The same test runs on a Linux Ubuntu 16.04 machine without much lag.

I have the following insert script.

for i in $(seq 1 1 1000)
do
bin/psql postgres -p 8999 -c "Insert into $1 select tz, $i * 127361::bigint, $i::real, random()*12696::bigint from generate_series('01-01-2018'::timestamptz, '02-01-2018'::timestamptz, '30 sec'::interval)tz;"
echo $i
done

The lag is measured using the following queries,

SELECT pg_last_wal_receive_lsn() - pg_last_wal_replay_lsn();
SELECT (extract(epoch FROM now()) - extract(epoch FROM pg_last_xact_replay_timestamp()))::int;

However, the observation is very unexpected. The lag is increasing from the moment the transactions are started on master.

Slave    localhost_9001: 12680304        1
Slave    localhost_9001: 12354168        1
Slave    localhost_9001: 16086800        1
.
.
.
Slave    localhost_9001: 3697460920      121
Slave    localhost_9001: 3689335376      122
Slave    localhost_9001: 3685571296      122
.
.
.
.
Slave    localhost_9001: 312752632       190
Slave    localhost_9001: 308177496       190
Slave    localhost_9001: 303548984       190
.
.
Slave    localhost_9001: 22810280        199
Slave    localhost_9001: 8255144         199
Slave    localhost_9001: 4214440         199
Slave    localhost_9001: 0       0

It took around 4.5 minutes for a single client inserting on a single table to complete on master and another 4 minutes for the slave to catch up. Note that NO simultaneous selects are run other than the script to measure the lag.

I understand that replay in PostgreSQL is pretty simple like, "move a particular block to a location", but I am not sure about this behavior.

I have the following other configurations,

checkpoint_timeout = 5min
max_wal_size = 1GB
min_wal_size = 80MB

Now, I run the same tests with same configurations on a Linux Ubuntu 16.04 machine, I find the lag perfectly reasonable.

Am I missing anything?

UPDATE :

MAC Hardware configurations :
16GB RAM, 512GB SSD (Nvme)
Intel(R) Core(TM) i7-7820HQ CPU @ 2.90GHz

Linux hardware configurations :
16GB RAM, 512GB SSD (Nvme)
Intel(R) Core(TM) i7-6600U CPU @ 2.60GHz

You can  checked max_standby_streaming_delay (30s default) or max_standby_archive_delay (30s default) and recovery_min_apply_delay and checkpoint_flush_after (32 default).


How to revive SQL Server 2008 R2 performance counters? I'm running SQL Server 2008 R2 Developer on Windows 7 x64, as the default instance. For some reason, SQL Server's performance counters seem to have disappeared. SELECT * FROM sys.dm_os_performance_counters returns zero rows.

I tried running lodctr /T:perf-MSSQLSERVERsqlctr.ini. Although it completed without errors, it didn't fix anything, even after restarting the Remote Registry service. In fact, the counters are now missing from perfmon. unlodctr MSSQLSERVER still says that the counters are not installed, but lodctr /Q:MSSQLSERVER says they're present and enabled. Regular Windows and .NET counters are working fine.

In the Windows Error log, I'm seeing Error 8317:

  Cannot query value 'First Counter' associated with registry key
  'HKLM\SYSTEM\CurrentControlSet\Services\MSSQLSERVER\Performance'. SQL
  Server performance counters are disabled.

I suspect the problem may have been triggered by an installation of SQL 2012 RC0 that failed. Any suggestions for how to recover, short of reinstalling SQL Server 2008 R2?

Have a try with lodctr /R, it will rebuild all performance counters from informations stored in registry.


What is the purpose of "Schema Owner"? If I create a new schema and set the schema owner to another schema, for example dbo, what is the meaning of that in regards to my new schema?

Does my new schema inherit the same permissions from it's owner dbo?

A Schema's owner is always a User (or Database Role).  There may be a Schema with the same name as a User, because in very old versions of SQL Server a User and a Schema were the same thing.  So there's still a User called dbo and a Schema called dbo in every database.

A Schema's owner is a User who has full control of the schema, and who will be the default owner of every object added to the schema.  Owning an object suppresses permissions-checking on the object, and plays an important role in Ownership Chains.


SSISDB AlwaysOn Support in 2016 - SSIS Failover Monitor Job fails on non-readable secondaries Setting up SSISDB with the new AlwaysOn support in SQL 2016 Enterprise. This creates two jobs, one of which is the SSIS Failover Monitor Job. The secondary is not readable. The code for this job is:

DECLARE @role int
DECLARE @status tinyint
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
IF @role = 1
BEGIN
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N'SQL2', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]
END

Every time this job runs, I get the below error due to SSISDB not being online on this secondary instance. This is because SQL Server must parse all parts of the query, regardless if conditional code paths are currently valid or not. Since SSISDB is offline, it cannot be parsed.

  Executed as user: ##MS_SSISServerCleanupJobLogin##. The target database, 'SSISDB', is participating in an availability group and is currently not accessible for queries. Either data movement is suspended or the availability replica is not enabled for read access. To allow read-only access to this and other databases in the availability group, enable read access to one or more secondary availability replicas in the group.  For more information, see the ALTER AVAILABILITY GROUP statement in SQL Server Books Online. [SQLSTATE 42000] (Error 976).  NOTE: The step was retried the requested number of times (3) without succeeding.  The step failed.

This code is generated by SQL Server, and since it is such a simple setup, I have a hard time believing it is a bug. But I don't know 1) what else it could be and 2) how one would work around it and provide the designed functionality without the secondary being readable.

Consider wrapping the statement in the BEGIN END block inside of sp_executeSQL so that it bypasses the syntax checking until runtime.

DECLARE @role int
SET @role = (SELECT [role] FROM [sys].[dm_hadr_availability_replica_states] hars INNER JOIN [sys].[availability_databases_cluster] adc ON hars.[group_id] = adc.[group_id] WHERE hars.[is_local] = 1 AND adc.[database_name] ='SSISDB')
IF @role = 1
BEGIN
    exec sp_executeSQL N'DECLARE @status tinyint;
    EXEC [SSISDB].[internal].[refresh_replica_status] @server_name = N''YourServerName'', @status = @status OUTPUT
    IF @status = 1
        EXEC [SSISDB].[catalog].[startup]'
END

Note: edited the script


Generated DDL doesn't match with CREATE TABLE statement I'm using MySQL with InnoDB engine. I create a new table that contains a foreign key, eg:

CREATE TABLE rooms (
    id INTEGER NOT NULL AUTO_INCREMENT,
    my_id VARCHAR(15),
    house_id INTEGER,
    PRIMARY KEY (id),
    FOREIGN KEY(house_id) REFERENCES houses (id) ON DELETE CASCADE
)

When I use a DB tool such as DBeaver to see the details of my database and this table, I see that my foreign key house_id is missing the ON DELETE CASCADE setting.

DBeaver also shows the DDL for this table as follows:

CREATE TABLE `rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `my_id` varchar(15) COLLATE utf8mb4_general_ci DEFAULT NULL,
  `house_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `house_id` (`house_id`),
  CONSTRAINT `rooms_ibfk_1` FOREIGN KEY (`house_id`) REFERENCES `houses` (`id`) ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Why does the DDL have the extra line KEY 'house_id' ('house_id').? This extra line also causes my foreign key to show 2 house_id columns in my DBeaver table viewer. If I get rid of this, my ON DELETE CASCADE gets set correctly.

I'm using an ORM so I don't have control of the CREATE TABLE statement, so I'm just trying to at least understand what's going on here.

Thanks,

As the manual says (emphasis mine):

  MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist.

In other words, this is an expected, documented behaviour. If DBeaver cannot see a difference between table constraints and its indexes, may be choose some other tool.


how to replace user's names with random names? How can I use SQL only on mysql DB to replace first and last names of real users in users table using a random name from two related tables: random_first_names and random_last_names. Our users table contains over 250K records and each of the rundome tables contain over 5000 names that should be picked at random for each record in users table. Is it possible to achieve with SQL only?

[UPDATE]

I solved the problem based on Rick's input and shared the full SQL script below.

If you decide that the Update is too slow, I suggest the following will be about 1000 times as fast.

Loop (can be done in a Stored Proc)...

[Re]Create a table with randomly ordered set first_names, with a `PRIMARY KEY of 1..5000.  Ditto for last_names (a second table).
Multi-table UPDATE the 'next' 5000 rows joined to the two random tables.  Use ON Users.id % 5000 = RandomFirstNames.id (etc)

End Loop

Shuffling the table (step 1 of the loop) is something like

CREATE TABLE RandomFirstNames (
    id SMALLINT UNSIGNED AUTO_INCREMENT,
    first_name VARCHAR(...),
    PRIMARY KEY(id) )
SELECT first_name FROM FirstNames ORDER BY RAND();

After OP's UPDATE

Don't do

SELECT count(id) INTO count_names FROM _RandomFirstNames;

Instead, do this once:

SELECT @mask_ct := COUNT(*) FROM _masked_names;

and use @mask_ct instead of count_names;

As for the skipped ids, CREATE TABLE _RandomFirstNames without an id, then ALTER TABLE _RandomFirstNames ADD id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY; to get the ids.  This should give you ids without gaps (unless you are on a multi-Master cluster of any kind).


log shipping to multiple-read servers The SQL Server 2008 R2 Web Edition product marketing site has a benefit listed on its High Availability section:

  Benefit from log shipping, which helps automatically back up data on separate servers or keep multiple-read servers online to better handle large amounts of web traffic... through one-way merge and transactional replication.

How is this scenario implemented? Is this referring to:

log shipping to multiple target instances and using snapshots and lots of T-SQL to keep them in sync
new functionality to manage multiple-read servers?
other?

Log Shipping is a high availability feature of SQL Server where a database copy from a specific point in time is made available to a different server using full backups + scheduled transaction log backups delivered by an internal mechanism of SQL Server.
It's composed of some SQL jobs and some executables and is accessible by a wizard - right click a database -> Properties -> Transaction Log Shipping.

It's no new functionality of SQL Server (if I recall correctly it's at least from SQL 2000).

You can read about it here: Log Shipping Overview.