Sync MongoDB Replica set with Remote Replica Set I'm wondering if it is possible (within reason) to sync a new MongoDB Replica Set(RS) with an RS currently in production.

The Problem

We have a site running on AWS that has its own MongoDB Replica Set and we're working on migrating that site to Google Cloud. We want to start a separate RS on Google Compute that will catch up with the AWS RS and stay up to date during our QA lifecycle until we're ready to cut DNS over to Google.

We originally tried adding the replicas on Google as secondaries to the AWS RS to keep them in sync. Our plan was to then take the AWS RS offline and let the Google RS reassign a Primary and continue as the only RS after the site was completely migrated.

This caused some issues when one of the Google replicas got elected as a master and created some syncing issues with our current live site.

The Solution??

I've been debating if it's possible to rsync from the AWS Primary to the GCloud Primary. Is this doable with Mongo? Will the GCloud primary then properly replicate to its slaves?

Or...

Is there a better solution that anyone knows of for this problem?

UPDATE

After trying to add Google nodes to server we're not seeing the node sync:

INFRA-GENERAL-00:SECONDARY> rs.status()
{
"set" : "INFRA-GENERAL-00",
"date" : ISODate("2016-08-03T00:22:37.275Z"),
"myState" : 2,
"term" : NumberLong(2),
"heartbeatIntervalMillis" : NumberLong(2000),
"members" : [
    {
        "_id" : 0,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.205Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 1,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:34.728Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 2,
        "name" : "****:27017",
        "health" : 0,
        "state" : 8,
        "stateStr" : "(not reachable/healthy)",
        "uptime" : 0,
        "optime" : {
            "ts" : Timestamp(0, 0),
            "t" : NumberLong(-1)
        },
        "optimeDate" : ISODate("1970-01-01T00:00:00Z"),
        "lastHeartbeat" : ISODate("2016-08-03T00:22:35.248Z"),
        "lastHeartbeatRecv" : ISODate("1970-01-01T00:00:00Z"),
        "pingMs" : NumberLong(0),
        "lastHeartbeatMessage" : "exception: field not found, expected type 16",
        "configVersion" : -1
    },
    {
        "_id" : 3,
        "name" : "****:27017",
        "health" : 1,
        "state" : 2,
        "stateStr" : "SECONDARY",
        "uptime" : 338,
        "optime" : {
            "ts" : Timestamp(1470169610, 1),
            "t" : NumberLong(2)
        },
        "optimeDate" : ISODate("2016-08-02T20:26:50Z"),
        "configVersion" : 175485,
        "self" : true
    }
],
"ok" : 1
}

INFRA-GENERAL-00:SECONDARY> rs.config()
2016-08-03T00:23:49.051+0000 E QUERY    [thread1] Error: Could not     retrieve replica set config: {
"ok" : 0,
"errmsg" : "not authorized on admin to execute command {     replSetGetConfig: 1.0 }",
"code" : 13
} :
rs.conf@src/mongo/shell/utils.js:1091:11
@(shell):1:1

You can follow your original strategy with a minor tweak: add Google Compute (GC) nodes into the current replica set and keep them in sync until ready to cut over. The only thing you need to change is to set those new GC nodes to have priority 0. They will be full members of the set, they just cannot be elected primary (or trigger elections).

Once you are ready to switch over to the Google nodes, you need only reset the priority to 1 (or whatever your normal values are), and then step down or remove the AWS nodes from the set. You can guarantee that a GC node is elected primary by giving them a higher priority than the AWS nodes before issuing the step down.

It seems we were unable to add Mongo 3.2 servers to Mongo 2.4 cluster, so we've decided to downgrade our cluster we're migrating to and look into upgrading later. After downgrading to Mongo 2.4 on our Google cluster, replication is working well.

A helpful strategy when migrating to a new system is to make sure none of the servers in the new cluster can vote. This helps you avoid the replica set going through phases where there are an even number of members, which can break things. More on non-voting replicas here: https://docs.mongodb.com/manual/tutorial/configure-a-non-voting-replica-set-member/

Also, as @Adam C mentioned, it is important to set the new members to priority 0, this will keep them from being elected as primary. (This did not seem to keep them from participating in election, so we used the votes config)


Log-in as service account in SQL Server Management Studio The stored procedure that performs a CTE in production is being executed using a service account. However, the stored procedure returns empty and I tried checking this using SSMS but I my domain account has no execute functionality in production.

I tried logging into SQL Server Management Studio using the service account but I ran through some problems. First was resolved by following this link - The certificate chain was issued by an authority that is not trusted

However, once that is executed, it returns as if no user name was provided:

Question is, can I login using a service account in SQL Server Management Studio?

Question is, can I login using a service account in SQL Server
  Management Studio?

How To Add A Service Account To Microsoft SQL Server

Open SQL Management Studio using SQL Authentication SysAdmin (SA)
Account.
Open up Security then Right Click on Logins, choose “New Login”
On the new login screen choose “Search”
On the search screen ensure you are searching the Entire Directory,
type in the user name, choose Check Names, then choose ok.
After you click OK, Click Server Roles and check “sysadmin”
Click OK and you are done
Test out by logging into SQL Management studio using Windows
Authentication.

For further your ref here and here


How to run INSERT INTO AS SELECT in parallel on Oracle? I have a very large table AUD$ that I want to copy it to another empty one with the same structure AUD_BACKUP.

INSERT INTO TEST_USER.AUD_BACKUP
    SELECT * FROM SYS.AUD$;
COMMIT;

How do I use parallel for INSERT as well as for SELECT ?
I can afford to run in 100 parallels. I am on Oracle db 12.2.

You should use direct path INSERT.
For select you can use PARALLEL hint in the same statement.  you may use PARALLEL without any parameter or use PARALLEL(n) with n number of thread.
Let me know if you need more explanation on this.

Explanation
I will try to show you an example as MySql because i didn't found an online complier that could help me to run Oracle quary.
Code Section
Create two tables
-- table 1
CREATE TABLE table1(
    int id integer,
    title varchar(100)
);
-- table 1 backup
CREATE TABLE backup_table1(
    int id integer,
    title varchar(100)
);

Insertion
-- insert data into table1
INSERT INTO table1 (id, title)
VALUES (1, "hello world!");

-- table1_backup
INSERT INTO backup_table1 (id, title)
SELECT id, title FROM table1;

OUTPUT
SELECT * FROM backup_table1;
id     title
1      hello world!

REFERENCE
SQL INSERT INTO SELECT Statement


Database for store images Actualy I'm using MongoDB with embedded images next to other data in same documents.
Images count actualy are more than 14000 and each image size is less than 1MB. Images are readed frequently and updated rarely.

I want to separate images from data and store it in different database than data to use it as microservice and for scalability. I've read people saying that the best solution to store images is on file system, but you have limitations about number of files in folder and in a lot of CloudComputing services you don't have access to the file system. In MongoDB official documentation, they say that GridFS is faster than file system, but I've understood that the most case to use GridFS is when you have files with size more than 16MB.

Do you know if there are some specific database to store files?
If not, what conventional database is more efficient to store files?
Is MongoDB a good solution to store files? If yes, what is better for the size of my images, storing as normal documents in collection or as GridFS?

Thanks

You could do it using GridFS.

This is best approach.


Why won't PostgreSQL 9.3 start on Ubuntu? I have successfully installed PostgreSQL 9.3 from the APT repository on 2 VM's running Ubuntu 12.04 and 13.04...however, I cannot get it to install properly on my host machine running Ubuntu 12.04.

The install (this time) seems to have gone ok, but perhaps there is an error I'm not understanding:

* No PostgreSQL clusters exist; see "man pg_createcluster"
Setting up postgresql-9.3 (9.3.0-2.pgdg12.4+1) ...
Creating new cluster 9.3/main ...
  config /etc/postgresql/9.3/main
  data   /var/lib/postgresql/9.3/main
  locale en_US.UTF-8
  port   5432
update-alternatives: using /usr/share/postgresql/9.3/man/man1/postmaster.1.gz to provide /usr/share/man/man1/postmaster.1.gz (postmaster.1.gz) in auto mode.

So I then try to add myself as a PostgreSQL user, but I get this:

createuser: could not connect to database postgres: could not connect to server: No such file or directory
    Is the server running locally and accepting
    connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

I cannot see PostgreSQL running in system monitor, and there is no file in the /var/run/postgresql/ folder...completely empty.

EDIT: On the VM's, there is a file in /var/run/postgresql/ called 9.3-main.pid

There is nothing on the host machine log file located /var/log/postgresql

So... what's going on here that isn't going on in my VM's? Like I said, the other installations on the VM's, including PostGIS and PGAdmin came in perfect...no idea why this host machine isn't going through...

My locale settings were not properly configured when PostgreSQL was installed. Purging and reinstalling didn't help. I followed the instructions here and that did the trick for me.

Essential parts of the linked information reproduced below:

The problem showed itself in the following manner:

warning: Please check that your locale settings:
LANGUAGE = (unset),
LC_ALL = (unset),
...
are supported and installed on your system.

The first one was very easy to solve by executing:

#dpkg-reconfigure locales

...and choosing the preferred locales.

But after that PostgreSQL still refused to start. This is due to the fact that the installation process tried to create a cluster at installation time but because of the bad locales this wasn't done. So we have to redo this step by executing:

#pg_createcluster 9.3 main --start

(For the 9.3 version of PostgreSQL)

After that step PostgreSQL starts flawlessly via

#/etc/init.d/postgresql start

All,

after some digging, I found (a) solution here:

http://ubuntuforums.org/showthread.php?t=869080

Which contained these instructions:

Run in terminal:

sudo mkdir -p /usr/local/pgsql/data
sudo chown -R postgres:postgres /usr/local/pgsql/
sudo su - postgres
cd /usr/lib/postgresql/9.3/bin/
./initdb -D /usr/local/pgsql/data
./postgres -D /usr/local/pgsql/data

Now my server is up and running!!!

EDIT: after a restart, the server is still not running...

Any thoughts as to why I needed to run this are appreciated!


Unable to create read-only user in PostgreSQL 9.5 I seem to be having trouble creating a read-only user for a database in a fresh install of PostgreSQL 9.5 with PostGIS 2.2 on Ubuntu 16.04. Here is what I've done so far:

Ran this,

CREATE ROLE dbowner LOGIN ENCRYPTED PASSWORD 'dbownerpassword';
CREATE DATABASE thedb WITH OWNER dbowner;
GRANT ALL PRIVILEGES ON DATABASE thedb TO dbowner;
CREATE EXTENSION postgis;
CREATE EXTENSION postgis_topology;

GRANT ALL ON ALL TABLES IN SCHEMA public to dbowner;
GRANT ALL ON ALL SEQUENCES IN SCHEMA public to dbowner;
GRANT ALL ON ALL FUNCTIONS IN SCHEMA public to dbowner;

CREATE ROLE readonly LOGIN ENCRYPTED PASSWORD 'ropassword';
GRANT CONNECT ON DATABASE thedb TO readonly;
GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

Then I restarted,
    sudo service postgresql restart

Now,

psql -h localhost -U dbowner thedb
thedb=> CREATE TABLE testtable(col1 VARCHAR(100));
CREATE TABLE
thedb=> INSERT INTO testtable values('123');
INSERT 0 1
thedb=> SELECT * FROM testtable;
 col1
------
 123
(1 row)

psql -h localhost -U readonly thedb
thedb=> SELECT * FROM testtable;
ERROR:  permission denied for relation testtable

pg_hba.conf already has local all all md5 and host all all 127.0.0.1/32 md5. Anything else that I might be missing?

The question doesn't specify what user executed the following statement:

ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;

but based on the surrounding code, let's guess it's postgres. That's the reason why readonly gets a permission denied trying to read it: it is the future creator of the table, dbowner, that should execute this statement.
(to be sure, note that you should also mention to what database you're connected when running the mentioned commands).

As stated by the doc (emphasis mine) at https://www.postgresql.org/docs/9.5/static/sql-alterdefaultprivileges.html :

  You can change default privileges only for objects that will be
  created by yourself or by roles that you are a member of.

psql -h localhost -U **postgres** thedb

GRANT USAGE ON SCHEMA public TO readonly;
GRANT SELECT ON ALL TABLES IN SCHEMA public TO readonly;
GRANT SELECT ON ALL SEQUENCES IN SCHEMA public TO readonly;
GRANT EXECUTE ON ALL FUNCTIONS IN SCHEMA public TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON TABLES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT SELECT ON SEQUENCES TO readonly;
ALTER DEFAULT PRIVILEGES IN SCHEMA public GRANT EXECUTE ON FUNCTIONS TO readonly;

\q

psql -h localhost -U **readonly** thedb

select * from test;
 id | name
----+------
  1 | KK
(1 row)

you need the super user to grant privileges to any user.

Note: GRANT USAGE ON SCHEMA public TO readonly; this is grant privileges on public schema in postgres database not public schema in thedb database.  Each database has own public schema.


How to SELECT from SHOW TABLE STATUS results I'd like to limit the rows and columns that come back from the

SHOW TABLE STATUS

command in MySQL 5.1.  Is there a way to get this same information through a SELECT statement so I can manipulate the results in a normal way?

This has more columns than SHOW TABLE STATUS; but does the trick:

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

UPDATE 2011-06-07 19:02

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = DATABASE();

These queries work if you set the current database.

You can also hard code the specific database:

SELECT table_name,Engine,Version,Row_format,table_rows,Avg_row_length,
Data_length,Max_data_length,Index_length,Data_free,Auto_increment,
Create_time,Update_time,Check_time,table_collation,Checksum,
Create_options,table_comment FROM information_schema.tables
WHERE table_schema = 'mysql';

You can use this query to display all Information Schema.

SELECT * FROM information_schema.tables WHERE table_schema = DATABASE();

or the shorthand:

SHOW TABLE STATUS LIKE 'tableName'


MariaDB Encryption - unknown variable 'plugin-load-add=file_key_management.dll' I'm really new to MariaDB and I need to set up encryption at rest. I'm running MariaDB 10.3 on Windows Svr 2012R2.

I have created a key and added the following to my config file (my.ini)

plugin-load-add=file_key_management.dll
file-key-management
file-key-management-filename = "C:/MariaDB/data/keys.txt"
innodb-encrypt-tables
innodb-encrypt-log
innodb-encryption-threads=4

The service starts fine. However when I go to open the MySQL Client I get the following error:

  C:\Program Files\MariaDB 10.3\bin\mysql.exe: unknown variable 'plugin-load-add=file_key_management.dll'

The dll is present and in the plugin-dir location specified in the config file.

Can anyone help me please?

mariadb from version 10.3 use other command in config:
[mariadb]
plugin_load_add = file_key_management
like show in  here


Can't restore SQL Server db from full backup, log processing fails, database in 'restoring' state I am trying to set up a database for development purposes on my PC's local SQL Server Developer Edition 12.0.2000.8. I've got a full database backup and separate transaction-log-only backup files available which were sent to me over the network.

When trying to restore from full backup, after some time (~1 hour maybe, the database is ~270 GB in size), I'm getting an error :

  System.Data.SqlClient.SqlError: An error occurred while processing the
  log for database 'database name'.  If possible, restore from backup.
  If a backup is not available, it might be necessary to rebuild the
  log. (Microsoft.SqlServer.SmoExtended)

After this, the db is in 'Restoring..' state.

I wanted to run something like (got it from this question)

ALTER DATABASE recovery_test_2 SET EMERGENCY;
ALTER DATABASE recovery_test_2 SET SINGLE_USER;

DBCC CHECKDB (recovery_test_2, REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

against it, but naturally I can't as the database is in 'Restoring.." state.
Restarting the restore process on it leads to the same error message, dropping and restoring again didn't help too.

How do I get the db up and working? Transactional consistency doesn't matter to me.

The SSMS auto-generated restore script :

  USE [master]
  RESTORE DATABASE [database_name] FROM  DISK = N'D:\database_name.bak' WITH  FILE = 1,
  MOVE N'database_name' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name.mdf',
  MOVE N'database_name_index' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_index.ndf',
  MOVE N'database_name_log' TO N'D:\MSSQL\MSSQL12.MSSQLSERVER\MSSQL\DATA\database_name_log.ldf',
  NOUNLOAD,
  STATS = 5

  GO

The result of query suggested by @Craig Efrein

  The log cannot be rebuilt because there were open transactions/users
  when the database was shutdown, no checkpoint occurred to the
  database,  or the database was read-only. This error could occur if
  the transaction log  file was manually deleted or lost due to a
  hardware or environment failure.

From the comments moved to chat, this note from the OP:

I just tried attaching it via GUI. As I selected the .mdf file, the SSMS identified the database consisted of 3 files (data, index, log), but somehow even though I was restoring WITH MOVE, the database details about file path were saying it wrong! So I just pointed them at the old log/data/index file and... The database is online.

Change to single user mode and restore.

example :
The first restore uses the NORECOVERY option so additional restores can be done.  The second command restores the transaction log and then brings the database online for end user use.

RESTORE DATABASE AdventureWorks FROM DISK = 'C:\AdventureWorks.BAK'
WITH NORECOVERY
GO
RESTORE LOG AdventureWorks FROM DISK = 'C:\AdventureWorks.TRN'
WITH RECOVERY
GO


Delete all the data from all tables I have a little problem. I have to delete all the data from our database. I don't want to drop tables, only delete the data sets.

These are 47 tables in total.

I tried the following:

Delete FROM(select TABLE_NAME from USER_TABLES);
Delete FROM(select * from ALL_ALL_TABLES Where OWNER = 'DB_NAME')

But that is not valid. How can I efficiently remove all the data from all my tables?

Generate the statements you need

select 'DELETE FROM '||table_name||' ;' from user_tables;

Even better would be using TRUNCATE instead of DELETE.  That would prevent generating extra redo vectors in the redo log - in the end all you want to do is just get rid of the data. It would also avoid all actions by any DELETE triggers that the tables have.

select 'TRUNCATE TABLE '||table_name||' ;' from user_tables;

You should use TRUNCATE instead of DELETE if you is using SQL Server. Because it will reduce log records in log file.

USE <your database>;
GO

DECLARE @table_name varchar(100);

DECLARE c CURSOR FOR SELECT name FROM sys.tables WHERE type='U'
OPEN c
FETCH NEXT FROM c INTO @table_name
WHILE @@FETCH_STATUS =0
BEGIN
    EXEC (N'TRUNCATE TABLE dbo.'+@table_name);
    FETCH NEXT FROM c INTO @table_name
END
CLOSE c
DEALLOCATE c


Not able to install any DB2 LUW 10.5 fixpacks I am currently on Fixpack 1 and am trying to install ANY more recent fixpack. All of them refuse to apply themselves with similar errors. The one below is from Fixpack 2 file v10.5fp2_ntx64_server. Each of them up to the most recent says that they cannot upgrade my version, but none of them provides a reason.

After reading Installation Planning link, my brain hurts after coming across of this:

DB2 must be installed on a drive with short names enabled or a directory with no spaces such as E:\DB2\SQLLIB. To check the current 8.3 file name settings for a drive, in a command window with administrator privileges, enter

fsutil.exe behavior query disable8dot3

The following possible values are returned when you run the command:

0: Create 8.3 short file names (default)
1: Do not create 8.3 file names
2: Set 8.3 file names on a per volume basis
3: Disable 8.3 file names on all volumes, except the system volume

The registry state of NtfsDisable8dot3NameCreation is 0, which means that 8.3 file names are enabled on all volumes. To enable 8.3 file names on a global basis, enter

fsutil.exe behavior set disable8dot3 0

If DB2 is installed on a drive without 8.3 name support andin a directory without spaces, it must be reinstalled on a drive with 8.3 name support. If the folder you are trying to install DB2 is already created when 8.3 name support is disabled, then the short name is not created if you try a reinstall onto the same directory. Windows short names are created during the directory creation, hence you must install it into a new directory or if the directory is empty delete the directory and allow recreation.

On my system, the setting is at its default 2, which means "per volume". I am not sure if this is the reason the fixpacks refuse to install themselves.
Does this mean that IBM essentially puts their customers into a death trap, by allowing the initial install of DB2 to place it into C:\Program Files\ but refusing to install fixpacks later? I cannot believe that this is the case. Can anyone whose DB2 runs off of C:\Program Files\ explain how we are supposed to upgrade?
UPDATE:
By running dir /x I found out that all directories in the hierarchy from c:\ down to sqllib have short names. The lack of them cannot be the reason why updates are refusing to install.

From your screenshot this is Express-C edition. It cannot be upgraded with fixpack.
See here

In my opinion your best bet is to leave it as is or plan for migration to newer version. IBM provides only newest Express-C edition from what I could gather.

asking google - I found at kbce.com:
Looking for Db2 Express-C Edition? That product does no longer exist and is replaced by Db2 Community Edition!
IBM Db2 Community Edition 11.5, RAM: 16GB, CPU: 4 sockets or 4 cores, Database size: 100GB! No further restrictions or disabled features!


Determine weekend based on country I've a table called BOND which have columns BOND NAME, COUNTRY of ISSUE and ISSUE DATE

BOND  Country  ISSUE_DATE
===========================
B1    INDIA    20-AUG-2017
B2    ISRAEL   18-AUG-2017

How do I find the issue date is weekend or not based on the issue country of BOND, without changing NLS_TERRITORY

SELECT TO_CHAR(ISSUE_DATE,'DY') DAY FROM BOND;

In the above dates 18-AUG-2017 is a weekend in ISRAEL but not in INDIA

Try to test the following:

select
TO_CHAR (issue_date, 'D', 'NLS_DATE_LANGUAGE=HINDI) IN (6, 7)
from table

To see if it works for you. One thing you need to check is, depending on the country, if the week starts at a Monday or Sunday. You might have to change those in values accordingly.
Edit: doing this, you're only changing the value on the query. Isn't it what you wanted? And sorry for not getting the value right. Corrected.


Got timeout reading communication packets vs Got error reading communication packets I felt "Got error reading communication packets" is related to max_allowed_packet variable and "Got timeout reading communication packets" is related 'net_read_timout' parameter or network issues.

Let me know whether my understanding is correct or not.

Thanks in advance.

For my situation, the master met with the error timeout, but the slave run the same sql successfully. The sql is about load data, so the master actually finished reading the whole packet?


MongoDB denying API access, while console shell works I am a MongoDB newbie, with a MongoDB instance running on a Ubuntu machine.

If I login onto the machine's console and run mongo -u username -p password databasename I have permissions for the database databasename to create and drop collections, insert documents and remove them. I have successfully tested these operations.

However, if I try to insert a document (into the same database using the same user credentials) using the MongoDB API from a Java program (running on the same server), I get error 13:

com.mongodb.MongoCommandException: Command failed with error 13:
'not authorized on databasename to execute command { insert: "collectionName" ...

My Java code (works well when running on my local machine for another MongoDB instance without authentication):

...
ServerAddress serverAddress = new ServerAddress(serverName, portNo);
MongoCredential mongoCredential =
     MongoCredential.createCredential(username, DB_NAME, password.toCharArray());
mongoClient = new MongoClient(...
DB db = mongoClient.getDB("databasename");
mongoCollection = db.getCollection("collectionName");
this.mongoCollection.insert(...                          <-- CAUSES EXCEPTION
....

What could be the cause of this error, and how can I solve it?

The cause of this problem was likely a MongoDB server configuration issue, relating to IP address binding. Changing 127.0.0.1 to 0.0.0.0 resolved the problem.

My problem got solved by restarting the app. I am using PM2 as process manager.


Difference between a unique clustered index and a clustered primary key? I'd like to ask two supplementary/follow-on questions, further to this previous/existing question: Is there any tangible difference between a unique clustered index and a clustered primary key?

That question starts with, "I understand that there may be a difference in meaning or intent between the two". I'm a programmer not a DBA, and this fundamental might be unclear to me: what is the difference in meaning or intent between the two?
My summary of the accepted answer, i.e. its most important statement IMO, is that it says, "I don't think there's any difference". If that's so then why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

It seems to me that a primary key (already) is a unique clustered index.

Furthermore, here's a specific problem by way of example.

Let's say I have a table of Users (with a userId as its primary key), and a second table (e.g. Items) which defines items owned by each user. A user can own many items; each item is owned by one user, and has an itemId.

So the itemId could be the primary key of the Items table; and each row in the Items table (which has an itemId) also has a userId to identify its owner.

That's a good way to define a 1-N relationship, isn't it? Assume a foreign key contraint on userId, with Users being the parent table.

At run-time I usually want to retrieve all the items owned by a user, therefore the Items table should be clustered on its userId column.

[Users]
  userId
  + plus other user-specific fields

[Items]
  userId
  itemId
  + plus other item-specific fields

I think there are two ways to define this Items table:

itemId is primary non-clustered key, and (userId,itemId) is unique clustered index

... or:

(userId,itemId) is primary clustered key, and itemId is unique non-clustered index.

Which of the above two is better or more correct, semantically and/or practically, and why?

In case it makes a difference, the itemID is an artificial key: its purpose is to disambiguate/identify the item (and/or identify the item, within the set of items owned by the user).

'Physically' the itemID is probably globally unique (or unique within the table, anyway): because databases make it easy to create a globally-unique artificial key.
'Logically' I wouldn't mind if it were not-globally-unique, but were instead only unique-within-each-user, such that I needed both userId and itemId to uniquely identify an item, i.e.

(userId,itemId) is primary clustered key and itemId is unique non-clustered index.

So I think it isn't altogether wrong to see (userId,itemId) as a composite primary key?

Apparently it's fine and normal to use "two separate attributes" as the primary key of an associative table when it's an N-to-N relationship. Is it wrong (e.g. harmful in some way, for some reason) to use two attributes as the primary key of an object in a 1-to-N relationship? Is it wrong to say that the owner ID is part of the object's identity?

why did Microsoft implement "clustered indexes"? Why not just say instead, "It's always clustered on the primary key, and you should define as the primary key whatever you want it to be clustered on"?

To answer this question: The primary key on the table does not have to be the Clustered Index Key. A Clustered Index is the place where SQL Server stores the data of the entire table. The Index itself has what are called "key values." Key Values (1 to many) define how the index is ordered, along with statistics and other useful things.

In fact, often it's useful to have a Primary Key that is not the Clustered Index Key. For example, some applications create their own keys or GUIDs in order to relate different tables through foreign keys. Using a GUID as a Clustered Index Key is a bad idea though, since GUIDs don't always have an order, unless you create them sequentially. To solve this, you could create a Clustered Index on an identity column, and place the Primary Key on the GUID column.

Your question has many more questions. I think we need to break this down into many questions, since you're asking about a lot of index concepts.

Unique clustered index : when created, it creates an index not any other key and also enforce the uniqueness for the key columns used. this is one way of keeping non-primary key columns unique and have the table shorted physically  according to the key columns used.

Clustered primary key : When created, it creates primary key & index both and used when table is needed to be shorted physically in same order as primary key column. Because by definition primary key is unique, it can be clustered or non-clustered without loosing its uniqueness


How to join a table with a table valued function? I have a user defined function:

create function ut_FooFunc(@fooID bigint, @anotherParam tinyint)
returns @tbl Table (Field1 int, Field2 varchar(100))
as
begin
  -- blah blah
end

Now I want to join this on another table, like so:

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
join ut_FooFunc(f.ID, 1) u -- doesn't work
where f.SomeCriterion = 1

In other words, for all Foo records where SomeCriterion is 1, I want to see the Foo ID and Desc, alongside the values of Field1 and Field2 that are returned from ut_FooFunc for an input of Foo.ID.

What's the syntax to do this?

You need CROSS APPLY not join.

The definition of table expressions involved in joins must be stable. I.e. They can't be correlated such that the table expression means something different dependant on the value of a row in another table.

select f.ID, f.Desc, u.Field1, u.Field2
from Foo f
Cross apply ut_FooFunc(f.ID, 1) u
where f.SomeCriterion = ...

I know the thread is old, I was asked the same question, I did a test, the result as follows...

  Records in FacCurrencyRate = 14264 while TestFunction returns 105 if executed independently.

    SELECT F.*, x.CurrencyKey, x.CurrencyName
    FROM (
           SELECT CurrencyKey, CurrencyName FROM dbo.TestFunction()
        ) x
    INNER JOIN [dbo].[FactCurrencyRate] F ON x.CurrencyKey = f.CurrencyKey;

The execution time is...

    (14264 rows affected)
    Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 1, read-ahead reads 73, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 31 ms,  elapsed time = 749 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

If I use the suggested answer as follows...

select F.*, x.CurrencyKey, x.CurrencyName from [dbo].[FactCurrencyRate] F
cross apply dbo.TestFunction() x

The execution time and result count is...

(1497720 rows affected)
Table 'FactCurrencyRate'. Scan count 1, logical reads 75, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 1, logical reads 38110, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'DimCurrency'. Scan count 1, logical reads 2, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

 SQL Server Execution Times:
   CPU time = 2106 ms,  elapsed time = 43242 ms.

 SQL Server Execution Times:
   CPU time = 0 ms,  elapsed time = 0 ms.

What I see here is that inner query brings out more correct set of results and execution time is much more efficient. Do correct me with better approach to accomplish the same!


Alter auto-increment of table with 16 million rows Learned this the hard way. Need to alter a table's auto-increment with 16 million rows.

Now, it's taking quite sometime.

Any estimate how long it would take? Sorry.

It depends, could be an hour or so. Perhaps you may like to give percona toolkit a try; pt-online-schema-change.

pt-online-schema-change alters a table’s structure without blocking reads or writes.
Specify the database and table in the DSN. Do not use this tool before reading its
documentation and checking your backups carefully.

Source: http://www.percona.com/doc/percona-toolkit/2.2/pt-online-schema-change.html

Two things I learned here.

Insert a record, and edit the last insert id.
Alter table auto-increment, which will take sometime.

It's better to just deal with the problem using #1


How to find out the content differences between 2 SQL tables and produce sync SQL How do I find out the differences in data between the two tables that have exact schema, and how to produce synchronisation SQL to get the union results (without duplicates) ?

These are the 2 tables:

SOURCE01.dbo.Customers (31,022 rows)

TARGET01.dbo.Customers (29,300 rows)

The schema of each table is :

[CustomerId] : nvarchar(255)
[CustomerSerializedProfile]: nvarchar(max)
[CreatedDatetime] : DateTime

Other than tablediff and powershell mentioned in the previous answers, you can also use SQL with the UNION ALL statement to find the records that don’t match in 2 identical tables:

SELECT MIN(TableName) AS TableName
   ,ID
   ,NAME
   ,lastname
   ,Address
   ,City
FROM (
SELECT 'Table A' AS TableName
    ,Customers.id
    ,Customers.NAME
    ,Customers.lastname
    ,Customers.Address
    ,Customers.City
FROM Customers

UNION ALL

SELECT 'Table B' AS TableName
    ,CustomersOld.id
    ,CustomersOld.NAME
    ,CustomersOld.lastname
    ,CustomersOld.Address
    ,CustomersOld.City
FROM CustomersOld
) tmp
GROUP BY ID
   ,NAME
   ,lastname
   ,Address
   ,City
HAVING COUNT(*) = 1
ORDER BY id;

Another option you can try is using Data Compare in Visual Studio itself. It compares data in the source database and the target database and creates a synchronization script for the tables you’ve selected for synchronization.

And last, but not least, you can use SQL data comparison tool - ApexSQL Data Diff, to set all synchronization options, map the tables and columns with different names, create your own keys for comparison in the GUI.
You can schedule it to run unattended and all you have to do is check SQL Server job history in the morning.
If you need more details regarding these options, I recommend reading this article: http://solutioncenter.apexsql.com/automatically-compare-and-synchronize-sql-server-data/

I think you should try xSQL Data Compare, which will do the trick in your case. Let's say for example, that you specify

SOURCE01.dbo.Customers as the **left table** and
TARGET01.dbo.Customers as the **right table**

After you compare the tables, in the comparision result, you can specify that you want to sync only the differences from the left table which would produce a SQL script to insert into TARGET01.dbo.Customers all the rows that are not in this table but exist in SOURCE01.dbo.Customers (Achieving a UNION result without duplicates). Hope this helps!

Disclosure: I'm affiliated to xSQL.


Implications of using a Unique NonClustered Index with Covering Columns instead of a Primary Key We have a large table [MyTable] which currently has both a Primary Key, and a Unique Non Clustered Index on the same column ([KeyColumn]). The U NC index also has additional covering columns.

Having both PK and Unique NC Index on the same columns seems redundant, so I was considering deleting the Primary Key and instead using the Unique Non Clustered index for Referential Integrity purposes.

Note that the table is clustered by another column entirely.

i.e. So we have:

ALTER TABLE [MyTable]
    ADD CONSTRAINT [PK_MyTable]
    PRIMARY KEY NONCLUSTERED ([KeyColumn])
GO

AND

CREATE UNIQUE NONCLUSTERED INDEX [IX_MyTable_SomeIndex]
    ON [MyTable] ([KeyColumn])
    INCLUDE ([Column1], [Column2])
GO

As far as I know, it is not possible to add covering columns to a Primary Key, so I intend to do:

Drop the Foreign Key Constraints reliant on MyTable.KeyColumn
Drop the Primary Key on MyTable.KeyColumn entirely
Re-Add the foreign keys to the table (i.e. RI will be enforced via MyTable.KeyColumn)

The only implication I can think of doing this is that we won’t get the visual key symbol on our ERD diagrams, and that the (leaf) index density will be less because of the included columns.

I've read https://stackoverflow.com/questions/487314/primary-key-or-unique-index and am happy with the integrity and performance aspects of doing this.

My Question is : Is this approach flawed?

Edit
What I'm trying to accomplish : Performance Optimisation and Spring Cleaning. By removing either the PK or an Index, there will be less pages needed for my indexes = faster writes, plus also maintenance/operational benefits, i.e. one less index to keep defragged etc.

To put some background to this, I've never before had a table which is being referenced, without a PK. However, the fact that a NC Index with covering columns was added to the table means that I need to adapt my thinking.

Performance Optimisation. By removing either the PK or an Index, there
  will be less pages needed for my indexes = faster writes, plus also
  maintenance/operational benefits, i.e. one less index to keep
  defragged etc.

You need to be able to prove that what's proposed will actually help (cost vs. benefit). For what reason is this change being considered? Are there actually performance issues with this table, or did it just "look wrong"?

Here are some other questions that will help you come to the best decision for your environment:

How much time would be saved in the maintenance window? In the backup window?
How much storage space would this save (data files, log files, backups, etc.)?
Is INSERT performance on this table really a bottleneck right now? How much would it improve? Is removing an index the best strategy to fix that problem?
Will this cause problems with database tools and frameworks (ORMs particularly) that expect each table to have a primary key and not just a unique index? Transactional Replication requires a primary key on published tables.
Is a self-documenting database schema important?
Despite its limited use, is the narrowness of the primary key index still allowing the optimizer to produce more efficient plans for certain queries? (Use sys.dm_db_index_usage_stats to find out.)

Personally speaking, from what you've told us, I would leave it alone until it can be proven that both (a) the extra index is a problem, and (b) removing it is the solution.

As far as I know, it is not possible to add covering columns to a
  Primary Key

If you have a clustered index on KeyColumn, then since it's a clustered index, all the other columns are implicitly 'covered'.  So you do not gain anything by adding another index on KeyColumn.  In fact you will slow down your inserts and used more space.

This is because a clustered index is not an index as such, but just an instruction to store the rows of the table in the order of the index.  And once you've foudn a row by primary key, then you have all other other columns right there.


Error parsing YAML config file: yaml-cpp I'm using the following command (Windows machine) with the MongoDB shell version: 3.0.7:

mongod --config "G:\NodeApps\mongod.cfg" --install

Contents of mongod.cfg file are given below:

systemLog:
    destination: file
    path:"G:\NodeApps\data\log"
storage:
    dbPath:"G:\NodeApps\data"

Getting the below error:

Error parsing YAML config file: yaml-cpp: error at line 4, column 8: illegal map value
try 'mongod --help' for more information

Similar questions doesn't have the solutions for this.

What I've already tried:

I'm using spaces (not tabs)
I'tried by saving the file in ASCII format, as It was mentioned in one of the posts that Mongod config file shouldn't be saved in non-ACSII format. Not even in UTF-8.

Please help me with this.

The error messages indicate the specific line and column where the YAML parser is having an issue with your configuration file, but if you're not familiar with the format it can be difficult to work out what is expected.
Two sets of changes are required to make your config valid YAML:

Add a "space" between the systemLog.path and storage.dbPath keys and their values
YAML requires a space between key/value pairs, so reports: "error at line 4, column 8: illegal map value".

Remove the double quotes from your path values
YAML interprets backslashes inside quoted strings as introducing an escape character, so reports: "error at line 3, column 16: unknown escape character". As an alternative, you could also leave the path quoted but either escape the backslashes (\\) or use forward slashes.

The following config should work (assuming "G:\NodeApps\data\" has the correct directory and file permissions):
systemLog:
    destination: file
    path: G:\NodeApps\data\log
storage:
    dbPath: G:\NodeApps\data

There are several online testers for YAML syntax that can be useful to troubleshoot problems (eg: YAML Lint).

"\" should be replaced with double slash ("\") when in windows


SQL 2008/R2 Extended Events - Row Count I need to setup a sql audit to capture all queries running on a server from any unauthorized locations, and one of the columns that is required is Row Count.

In sql trace, this is possible. But I can't seem to find corresponding column in extended events. I looked into these 2 DMVs:

select * from sys.dm_xe_object_columns
where object_name = 'sql_statement_completed'

select pkg.name as PackageName, obj.name as ActionName
from sys.dm_xe_packages pkg
inner join sys.dm_xe_objects obj on pkg.guid = obj.package_guid
where obj.object_type = 'action'
order by 1, 2

But all they have is reads/writes, but not actual number of rows.

Is it possible to get this info in SQL 2008 or SQL 2008 R2 ?

That's a bit insane to capture all running queries no? Queries from a specific user or including the ones from sproc, ETL, etc? If they are coming from an unauthorized location, that tells me that your DB is not locked down enough. If one instance has 20 DBs, each of the service accounts, domain users or even SQL login should only been given access to the database pertain to them. The people who are the owner of these accounts (who knows the password) are usually gone trusted and have gone through background checks.

Otherwise, they are 3rd party SQl tools that like idera dm , SQL Sentry and even pefromance dashboard that comes with SSMS 2016 that keep track of queries applied to the database.


Collect disk space metrics for whole SQL Server instance I am quite new to SQL Server. I would like to collect database disk space metrics for the SQL Server instance as a whole.

I have found a code sample that can collect disk space information per database.

IF OBJECT_ID('DISK.dbo.disk_activity') IS NOT NULL
    DROP TABLE disk_activity

CREATE TABLE disk_activity (
       servername NVARCHAR(100)
    ,  database_id INT PRIMARY KEY
    , name NVARCHAR(MAX)
    , data_used_size DECIMAL(18,2)
    , log_used_size DECIMAL(18,2)
)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF((
    SELECT '
    USE [' + d.name + ']
    INSERT INTO DISK.dbo.disk_activity (servername, database_id, name, data_used_size, log_used_size)
    SELECT
          (SELECT @@SERVERNAME AS "Server Name") AS SERVER_NAME
        , DB_ID()
        , DB_NAME()
        , SUM(CASE WHEN [type] = 0 THEN space_used END)
        , SUM(CASE WHEN [type] = 1 THEN space_used END)
    FROM (
        SELECT s.[type], space_used = SUM(FILEPROPERTY(s.name, ''SpaceUsed'') * 8. / 1024)
        FROM sys.database_files s
        GROUP BY s.[type]
    ) t;'
    FROM sys.databases d
    WHERE d.[state] = 0
    FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '')

EXEC sys.sp_executesql @SQL

However, I would like to collect the metrics across the entire server, i.e. total disk space allocated for all databases, total disk space used, total disk space free, and percentage of disk space used.

How should I modify the above script or are there other options available?

To monitor disk space usage and database file size I use this script that you can find on my blog:

-- create a temporary table to hold data from sys.master_files
IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

CREATE TABLE #masterfiles (
    database_id int,
    type_desc varchar(10),
    name sysname,
    physical_name varchar(255),
    size_mb int,
    max_size_mb int,
    growth int,
    is_percent_growth bit,
    data_space_id int,
    data_space_name nvarchar(128) NULL,
    drive nvarchar(512),
    mbfree int
);

-- extract file information from sys.master_files
-- and correlate each file to its logical volume
INSERT INTO #masterfiles
SELECT
     mf.database_id
    ,type_desc
    ,name
    ,physical_name
    ,size_mb = size / 128
    ,max_size_mb =
        CASE
            WHEN max_size = 268435456 AND type_desc = 'LOG' THEN -1
            ELSE
                CASE
                    WHEN max_size = -1 THEN -1
                    ELSE max_size / 128
                END
        END
    ,mf.growth
    ,mf.is_percent_growth
    ,mf.data_space_id
    ,NULL
    ,d.volume_mount_point
    ,d.available_bytes / 1024 / 1024
FROM sys.master_files AS mf
CROSS APPLY sys.dm_os_volume_stats(database_id, file_id) AS d;

-- add an "emptyspace" column to hold empty space for each file
ALTER TABLE #masterfiles ADD emptyspace_mb int NULL;

-- iterate through all databases to calculate empty space for its files
DECLARE @name sysname;

DECLARE c CURSOR FORWARD_ONLY READ_ONLY STATIC LOCAL
FOR
SELECT name
FROM sys.databases
WHERE state_desc = 'ONLINE'

OPEN c
FETCH NEXT FROM c INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN

    DECLARE @sql nvarchar(max)
    DECLARE @statement nvarchar(max)
    SET @sql = '
        UPDATE mf
        SET emptyspace_mb = size_mb - FILEPROPERTY(name,''SpaceUsed'') / 128,
            data_space_name =
                ISNULL(
                    (SELECT name FROM sys.data_spaces WHERE data_space_id = mf.data_space_id),
                    ''LOG''
                )
        FROM #masterfiles AS mf
        WHERE database_id = DB_ID();
    '
    SET @statement = 'EXEC ' + QUOTENAME(@name) + '.sys.sp_executesql @sql'
    EXEC sp_executesql @statement, N'@sql nvarchar(max)', @sql

    FETCH NEXT FROM c INTO @name
END

CLOSE c
DEALLOCATE c

-- create a scalar function to simulate the growth of the database in the drive's available space
IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

EXEC tempdb.sys.sp_executesql N'
CREATE FUNCTION calculateAvailableSpace(
    @diskFreeSpaceMB float,
    @currentSizeMB float,
    @growth float,
    @is_percent_growth bit
)
RETURNS int
AS
BEGIN
    IF @currentSizeMB = 0
        SET @currentSizeMB = 1
    DECLARE @returnValue int = 0
    IF @is_percent_growth = 0
    BEGIN
        SET @returnValue = (@growth /128) * CAST((@diskFreeSpaceMB / (@growth / 128)) AS int)
    END
    ELSE
    BEGIN
        DECLARE @prevsize AS float = 0
        DECLARE @calcsize AS float = @currentSizeMB
        WHILE @calcsize < @diskFreeSpaceMB
        BEGIN
            SET @prevsize = @calcsize
            SET @calcsize = @calcsize + @calcsize * @growth / 100.0
        END
        SET @returnValue = @prevsize - @currentSizeMB
        IF @returnValue < 0
            SET @returnValue = 0
    END

    RETURN @returnValue
END
'

-- report database filegroups with less than 20% available space
;WITH masterfiles AS (
    SELECT *
        ,available_space =
            CASE mf.max_size_mb
                WHEN -1 THEN tempdb.dbo.calculateAvailableSpace(mbfree, size_mb, growth, is_percent_growth)
                ELSE max_size_mb - size_mb
            END
            + emptyspace_mb
    FROM #masterfiles AS mf
),
spaces AS (
    SELECT
         DB_NAME(database_id) AS database_name
        ,data_space_name
        ,type_desc
        ,SUM(size_mb) AS size_mb
        ,SUM(available_space) AS available_space_mb
        ,SUM(available_space) * 100 /
            CASE SUM(size_mb)
                WHEN 0 THEN 1
                ELSE SUM(size_mb)
            END AS available_space_percent
    FROM masterfiles
    GROUP BY DB_NAME(database_id)
        ,data_space_name
        ,type_desc
)
SELECT *
FROM spaces
WHERE available_space_percent < 20
ORDER BY available_space_percent ASC

IF OBJECT_ID('tempdb..#masterfiles') IS NOT NULL
    DROP TABLE #masterfiles;

IF OBJECT_ID('tempdb..calculateAvailableSpace') IS NOT NULL
    EXEC tempdb.sys.sp_executesql N'DROP FUNCTION calculateAvailableSpace'

Basically, it uses sys.master_files to query the size of all the database files and sys.dm_os_volume_stats to query the drive info. FILEPROPERTY returns information on the available space inside the file.

The script there was meant for alerting when available free space falls under a threshold (say 20%), but it can be modified to perform regular monitoring and record the information in a centralized table.

Don't overlook what the Data Collector feature can record in the Management Data Warehouse database. The feature is available in all editions of SQL Server (express excluded for the lack of an agent to schedule the collection). If the reports you have there are not enough, you could query the tables directly (you just need to query [snapshots].[disk_usage] in the MDW database).

In particular, if you're on Enterprise Edition, the Utility Control Point feature focuses on resource utilization with a "holistic" approach. Again, it provides some reports and a dashboard to see resource utilization across the whole server infrastructure or the single server.

In my experience, Data Collector and Utility Control Point are the most underused features in SQL Server.

Read up on the Performance Data Warehouse feature. Gives you space metrics and a LOT more (that sooner or later you or someone doing operations will like to have available) and is actually part of the product.


How can I compare the schema of two databases? Is there a way to find the differences in two SQL Server databases (schema only).  One is local and the second is at a customer's site.  We are experiencing problems with crystal reports running some reports and some code not executing and it would appear that the schemas don't match.

Can I run the same command on both databases and compare the results to tell where the differences are?

If you cannot use one of the many tools out there because of connectivity problems and want an "offline" compare, you can use SSMS to generate scripts for all database objects by right clicking on the database and using the "Tasks.../Generate Scripts" function, and make sure you select to create one file per object.

When you have done that for both databases, get the two sets of scripts onto a local machine in two separate folders and use WinMerge (or similar) to compare the two.

Check this out:

SELECT TABLE_SCHEMA ,
       TABLE_NAME ,
       COLUMN_NAME ,
       ORDINAL_POSITION ,
       COLUMN_DEFAULT ,
       DATA_TYPE ,
       CHARACTER_MAXIMUM_LENGTH ,
       NUMERIC_PRECISION ,
       NUMERIC_PRECISION_RADIX ,
       NUMERIC_SCALE ,
       DATETIME_PRECISION
FROM   INFORMATION_SCHEMA.COLUMNS
where TABLE_SCHEMA in ('dbo','meta')
and table_name in (select name from sys.tables)
order by TABLE_SCHEMA ,       TABLE_NAME ,ORDINAL_POSITION


Get the Largest Count of Consecutive Value I have this table that records win and lose. Let us say this is my table

create table test(
  col1 int not null
)Engine=InnoDB;

it has 10 rows :

0,1,1,1,0,1,1,0,0,1

1=Win and 0=Lose. I want to get max count of the consecutive win and lose. For this example, the max count for consecutive win is 3 and for lose is 2. How can I get my desired result? I'm not sure where to start with this one. Thanks in advance.

EDIT

create table t1 (
 colid int not null auto_increment,
 col1 int,

 primary key(colid)
);

insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (1);
insert into t1(col1) values (1);
insert into t1(col1) values (0);
insert into t1(col1) values (0);
insert into t1(col1) values (1);

select
a.col1,
max(a.rn)
from (

        select
        x.col1,
        if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn, @prev := col1
      from t1 x , (select @prev := - 1 , @current := 1) v
      order by x.colid
      ) a
group by a.col1

this one works the way I see the results.. Is there anything I should consider? I only have knowledge on basic joins. I haven't fully understand the query as of now..

First of all, you can't trust that table to work.  A table, by definition, is unordered.  SQL is free to return the ones and zeros in any order.

So...  You need to have another column that can be used to sort on -- a date or a game-number, or something.

Then you can ask the question because you can do a SELECT ... ORDER BY ....

The rest is messy.  You should consider, instead, writing the algorithm in your favorite programming language other than SQL.

Add an AUTO_INCREMENT column and index it.
Do a self join on a.id = b.id-1 to find where the transitions are (0 to 1 or 1 to 0)
Extract only the transition ids.
Repeat the auto_in game, but instead of finding transitions, subtract the old ids.  This gives you the run lengths.
SELECT ... ORDER BY .. LIMIT 1 to find the longest run, together with some of the history of where it came from.

create table t1 (
col1 int
);

insert into t1 values (0);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (0);
insert into t1 values (1);
insert into t1 values (1);
insert into t1 values (0);
insert into t1 values (0);
insert into t1 values (1);

select t.col1,
   max(t.rn)
  from (
select t1.col1,
if (@prev = col1 ,@current := @current + 1,@current := 1 ) rn,
   @prev := col1
 from t1,(select @prev := - 1 ,
              @current := 1) v
 ) t
 group by t.col1;

DB fiddle


Joining two tables and query them I want to select the name and date of birth of all living artists who have value more than 50. My tables are artist and picture and I wrote this SQL statement but it is not giving me the desired result. Please advise.

SELECT name, dateOfBirth
FROM artist
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value = '50'
    AND artist.dayOfDeath is NULL;

Assuming that value is a numeric data type:

SELECT
    A.name
    , A.dateOfBirth

FROM
    artist A
    JOIN exhibitions.picture P
        ON A.name = P.name

WHERE
    P.Value > 50
    AND A.dayOfDeath is NULL;

SELECT name, dateOfBirth
FROM artist
    JOIN exhibitions.picture
        ON exhibitions.picture.name
WHERE exhibitions.picture.value >'50'
    AND artist.dayOfDeath is NULL;

By this query, you will get only more than 50yrs living artists.
Please use greater than condition for correct results.


SQL Server: grant all 'public' users the permission to create tables I have a set of users who have the 'public' role.

According to the default settings, they can read, update, insert data inside existing tables.

How can I change the database settings to that they can as well create and ALTER tables of schema dbo?

If I click on "Alter role" for the database>decurity>Roles, there is a section 'extended properties" but I am not sure how to use this?

Thank you

Ensure you are in the correct database and then issue the following command:

GRANT CREATE TABLE TO public
GO

If you already have objects that require public to ALTER the table then you can add the following for each existing table:

GRANT ALTER ON OBJECT::dbo.your_table_name TO public
GO

Reference: GRANT (Transact-SQL)

GRANT ALL PRIVILEGES ON *.* TO ''@'localhost' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;

The manual says:

  In this case, any user who connects from the local host with the correct password for the anonymous user will be permitted access, with the privileges associated with the anonymous-user account.

So for granting create and alter permissions for MS SQL, the syntax will be:

GRANT ALTER ON dbo TO public;

According to SQL Server 2008 help:

  Requires CREATE TABLE permission in the database and ALTER permission on the schema in which the table is being created.

Granting permission to create tables to a specific user in a specific database not only requires CREATE TABLE permissions but also requires ALTER permissions to the schema, e.g. DBO.

USE db_mydatabase
GRANT ALTER ON Schema :: DBO TO user_mydatabase
GRANT CREATE TABLE TO user_mydatabase
GO


SQL Server 2012 slower than 2008 I migrated a large website and database from an older server (Windows 2008 / SQL Server 2008 / 16 GB RAM / 2 x 2.5 GHz Quad Core / SAS disks) to a newer, much better server (Windows 2008 R2 / SQL Server 2012 SP1 / 64 GB RAM / 2 x 2.1 GHz 16 Core processors / SSD disks).

I detached the database files on the old server, copied and attached them on the new server. Everything went very well.

After that, I changed to compatibility level to 110, updated statistics, rebuild indexes.

To my huge disappointment, I noticed that most sql queries are much slower (2-3-4 times slower) on the new SQL 2012 server than on the old SQL 2008 server.

For example, on a table with around 700k records, on the old server a query on index took around 100ms. On the new server, the same query takes around 350 ms.

Same happens for all queries.

I would appreciate some help here. Let me know what to check/verify. Because I find it very hard to believe that on a better server with a newer SQL Server, the performance is worse.

More details:

Memory is set to max.

I have this table and index:

CREATE TABLE [dbo].[Answer_Details_23](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserID] [int] NOT NULL,
    [SurveyID] [int] NOT NULL,
    [CustomerID] [int] NOT NULL default 0,
    [SummaryID] [int] NOT NULL,
    [QuestionID] [int] NOT NULL,
    [RowID] [int] NOT NULL default 0,
    [OptionID] [int] NOT NULL default 0,
    [EnteredText] [ntext] NULL,
 CONSTRAINT [Answer_Details_23_PK] PRIMARY KEY NONCLUSTERED
(
    [ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

CREATE NONCLUSTERED INDEX [IDX_Answer_Details_23_SummaryID_QuestionID] ON [dbo].[Answer_Details_23]
(
    [SummaryID] ASC,
    [QuestionID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

I executed this query:

set statistics time on;
select summaryid, count(summaryid) from Answer_Details_23 group by summaryid order by count(summaryid) desc;
set statistics time off;

OLD SERVER - SQL Server Execution Times:
   CPU time = 419 ms,  elapsed time = 695 ms.

NEW SERVER - SQL Server Execution Times:
   CPU time = 1340 ms,  elapsed time = 1636 ms.

EXECUTION PLANS uploaded here:
http://we.tl/ARbPuvf9t8

Later update:

AMD 2.1GHz Opteron 16 core processors look much worse than Intel 2.5GHz quad core processors
Great improvement changing windows power options from ballanced to high power
Further improvement changing max degree of parallelism to 8 and cost threshold to 4

Now, SQL Server Execution Times: CPU time = 550 ms, elapsed time = 828 ms.

It's still worse than the old server, but not that bad. If you have any other suggestions (other than local query optimizations) please feel free to comment.

Let me know what to check/verify

You have a performance problem. Follow a performance troubleshooting methodology like Waits and Queues to identify the bottleneck. The linked methodology shows you what to measure and how. Post here the findings and we can help with specific advice based on your actual measurements. As it is is too open and is anybody's guess. Narrowing it down to a specific issue will eliminate guesswork.

After update

The plans are quite different. The old plan had a stream aggregate low on the stack which actually has a bad  cardinality estimate (141k vs. 108k) and the hash math further mispredicts, the other way (35k vs. 108k). The new plan does not have the stream aggregate and has accurate estimates all the way to the top. Of course, this does not explain why the old plan was executing faster.

The bottom scans have a slightly different row number (not significant) but quite different costs: old is 2.49884 (IO 2.28979 CPU 0.20905) vs new 1.59109 (IO 1.53868 CPU 0.0524084). Again would point toward a better 2012 execution (the index rebuild has perhaps reduced fragmentation?).

What is very different is the number of threads: 32 in new (each getting ~23k rows) vs. 8 in old (each getting ~95k rows). The table is fairly narrow. It could be that the large number of threads is actually hurting performance because of much much more frequent cache invalidations. I would try:

eliminate HyperThreading in the new server config (if any) and/or
try the query with a DOP 8.

Noticed your comment:

  Added the execution plan with maxdop 8 Query is actually faster this way

It probably is just CPUs stepping on each other toes. With SSDs in place the IO is probably next to nothing so and the table is definitely too small to warrant 32 scanners. That exchange swap is probably invalidating L1/L2 constantly.

I have also gone thru this issue for at least 2 weeks without any robust resolution rather than confusing one issue with the other.

Finally the resolution as follows:-

I have reset the compatibility from 010 to 011
Reset the compatibility of master database too. By default sql will retain the old compatibility setting. That we need to change manually.

All the best


Postgres reported table / db size varies wildly from dump size I have a very strange misreporting of the size of one specific database.

According to \l+ in psql the DB size is 292 MB.

Doing a sum over the sizes reported by the following statement also reports very close to 292 MB.

SELECT
    table_name,
    pg_size_pretty(total_size) AS total_size
FROM (
    SELECT
        table_name,
        pg_total_relation_size(table_name) AS total_size
    FROM (
        SELECT ('"' || table_schema || '"."' || table_name || '"') AS table_name
        FROM information_schema.tables
    ) AS all_tables
    ORDER BY total_size DESC
) AS pretty_sizes

Yet a pg_dump of this database produces a sql file of 2.02 GB

I suspect that both counting methods skip LOBs since there are two tables in this database that contain TEXT fields with largish content (up to ~4MB per row).

Is there a way to include LOBs in the size calculation?

EDIT:
It gets even stranger:

This query:

SELECT SUM(LENGTH(text_column))
FROM some_table

Gives a result of 2,091,245,318 (i.e. 2.02 GiB) which is about the size of the dump.

Note that unless your application is going out of its way to use the large objects interface, then it is not storing those columns as LOBs. Normal columns can store up to 1gb using TOAST (The Oversized-Attribute Storage Technique).

In addition to storing the data out of line, it may be stored compressed. Although you don't say so I assume the dump you are composing with is uncompressed, which could account for most of the size difference.

a sql file will always be much bigger than the data stored in your db. It's a text representation of binary data.


ssis ado.net destination has failed to acquire the connection I am Transferring data from sql server source db to sql server destination db (cross db).
but some times getting error "ssis ado.net destination has failed to acquire the connection.The connection may have been corrupted"

configure your connection as xml
set variable in connection
set computer name & logic [Admin]


Regular Expression Difficulty I'm trying to extract 456 from the string :123:456: as follows:

select regexp_substr(':123:456:', ':(\d+):', 1, 2, 'i', 1) from dual

However, this query returns null. What am I doing wrong?

This works fine:

select regexp_substr(':123:456:', '(\d+):', 1, 2, 'i', 1) from dual;

I think yours fails because the opening and closing colons won't get matched by both occurrences (because the first match is greedy).

select regexp_substr(':123:456:','[^:]+',2,2) from dual


Duplicates while doing multiple joins I have 4 MySQL tables

1. companies - {id*, company}
2. employees - {id*, fname, lname, id_company*}
3. technologies - {id*, technology}
4. company_technologies - {id_technology*, id_company*}

Field is indexed if it has '*'.

Companies can use multiple technologies and have multiple employees.

What I'm trying to do is write a query that would select all employees and their companies that use ANY of given technologies. The problem is that I have duplicates when I try to do something like this:

SELECT employees.*, companies.* FROM companies LEFT JOIN employees on companies.id = employees.id_company LEFT JOIN company_technologies ON companies.id = company_technologies.id_company WHERE company_technologies.id_technology IN(1,2,3)

If, for example, a company is using technology 1 and 2 - it will appear twice along with all people.

If I add 'GROUP BY companies.id' condition at the end - I only have one employee for each company, whereas I need all of them.

Tables 1, 2 and 4 have several million records, all tables are InnoDB.

You can convert the join to a semi-join, using either IN or EXISTS:

SELECT employees.*, companies.*
FROM companies
  LEFT JOIN employees
    ON companies.id = employees.id_company
WHERE EXISTS
      ( SELECT 1
        FROM company_technologies
        WHERE companies.id = company_technologies.id_company
          AND company_technologies.id_technology IN (1,2,3)
      ) ;

Another way would be to use GROUP BY or DISTINCT in a subquery and then join:

SELECT e.*, c.*
FROM companies AS c
  LEFT JOIN employees AS e
    ON c.id = e.id_company
  JOIN
      ( SELECT id_company
        FROM company_technologies
        WHERE id_technology IN (1,2,3)
        GROUP BY id_company
      ) AS ct
    ON c.id = ct.id_company ;

Once you have one or more queries working correctly, you can start worrying about efficiency. I would add 2 composite indexes on the junction table, one on (id_technology,id_company) and one on (id_company,id_technology) (and remove the single indexes.)

Then test all queries with various parameters and table sizes and check which indexes are used and what are the execution plans.

MySQL: Removing duplicate columns on LEFT JOIN for 3 tables
the code following should work:

SELECT  *
 FROM  table1
LEFT JOIN table2 USING (ID_Prep_Text)
LEFT JOIN table3 USING (ID_Picture)


Find rows with similar string values I have a Microsoft SQL Server 2012 database table with around 7 million crowd-sourced records, primarily containing a string name value with some related details. For nearly every record it seems there are a dozen similar typo records and I am trying to do some fuzzy matching to identify record groups such as "Apple", "Aple", "Apples", "Spple", etc. These names can also contain multiple words with spaces between them.

I've come up with a solution of using an edit-distance scalar function that returns number of keystrokes required for transformation from string1 to string2 and using that function to join the table to itself. As you can imagine, this doesn't perform that well since its having to execute the function millions of times to evaluate a join.

So I put that in a cursor so at least only one string1 is being evaluated at a time, this at least gets results coming out but after letting it run for weeks it has only made it through evaluating 150,000 records. With 7 million to evaluate, I don't think I have the kind of time my method is going to take.

I put full text indexes on the string names, but couldn't really find a way to use the full text predicates when I didn't have a static value I was searching.

Any ideas how I could do something like the following in a way that wouldn't take months to run?

  SELECT t1.name, t2.name
  FROM names AS t1
  INNER JOIN names AS t2
       ON EditDistance(t1.name,t2.name) = 1
       AND t1.id != t2.id

I've tried soundex, but since the names can contain spaces and multiple words per value I get too many false positives to use it reliably.

Having dealt this issue, the most effective and performant way is to create a CLR function that calculates the Levenshtein Distance. You will be able to mark the assembly as SAFE (if you're at all concerned about security), and it runs much quicker than SOUNDEX() or any in-built SQL Server functions.

Here's the code to set up the assembly and function in the database, as well as a basic version of the Levenshtein Distance algorithm implemented in C# from https://en.wikibooks.org/wiki/Algorithm_Implementation/Strings/Levenshtein_distance#C#

C#:

using System;
using System.Security.Cryptography;

namespace LevenshteinDistance
{
    public class LevenshteinDistance
    {
        private int LevenshteinDistance(string a, string b)
        {
            if (string.IsNullOrEmpty(a))
            {
                if (!string.IsNullOrEmpty(b))
                {
                    return b.Length;
                }
                return 0;
            }

            if (string.IsNullOrEmpty(b))
            {
                if (!string.IsNullOrEmpty(a))
                {
                    return a.Length;
                }
                return 0;
            }

            int cost;
            int[,] d = new int[a.Length + 1, b.Length + 1];
            int min1;
            int min2;
            int min3;

            for (int i = 0; i <= d.GetUpperBound(0); i += 1)
            {
                d[i, 0] = i;
            }

            for (int i = 0; i <= d.GetUpperBound(1); i += 1)
            {
                d[0, i] = i;
            }

            for (int i = 1; i <= d.GetUpperBound(0); i += 1)
            {
                for (int j = 1; j <= d.GetUpperBound(1); j += 1)
                {
                    cost = (a[i-1] != b[j-1])? 1 : 0;

                    min1 = d[i - 1, j] + 1;
                    min2 = d[i, j - 1] + 1;
                    min3 = d[i - 1, j - 1] + cost;
                    d[i, j] = Math.Min(Math.Min(min1, min2), min3);
                }
            }
            return d[d.GetUpperBound(0), d.GetUpperBound(1)];
        }
    }
}

T-SQL:

use [master];
go

exec sp_configure 'clr enabled', 1;
go
reconfigure with override;
go

use [database_name];
go

-- Drop the function...
if exists (select 1 from sys.objects so where so.[name] = 'LevenshteinDistance')
    drop function dbo.LevenshteinDistance;
go

-- ...then the assembly
if exists (select 1 from sys.assemblies sa where sa.[name] = 'LevenshteinDistance')
    drop assembly [LevenshteinDistance];
go

-- Now load the assembly from an appropriately accessible location
create assembly [LevenshteinDistance]
from
    'd:\LevenshteinDistance.dll'
with
    permission_set = safe;
go

-- Create an asymmetric key from the assembly file
use [master];
go

if not exists (select 1 from sys.asymmetric_keys ak where ak.[name] = 'LevenshteinDistanceKey')
begin
    create asymmetric key LevenshteinDistanceKey
    from executable file = 'd:\LevenshteinDistance.dll';
end
go

-- Create a user to associate with the assembly from the asymmetric key, and then
-- revoke connect access. The login is used to execute the assembly.
use [master];
go

if not exists (select 1 from sys.server_principals sp where sp.[name] = 'LevenshteinDistanceKeyUser')
begin
    create login LevenshteinDistanceKeyUser from asymmetric key LevenshteinDistanceKey;
    revoke connect sql from LevenshteinDistanceKeyUser;
end
go

grant external access assembly to LevenshteinDistanceKeyUser;
go

use [database_name];
go
alter assembly [LevenshteinDistance] with permission_set = safe;
go

-- Create the SQL function which will be called
create function [dbo].LevenshteinDistance
(
    @string1 nvarchar(2048)
    ,@string2 nvarchar(2048)
)
returns nvarchar(max)
as
    external name LevenshteinDistance.[LevenshteinDistance.LevenshteinDistance].LevenshteinDistance;
go

Usually I'd go with SOUNDEX on this one.  The issue is your 'Apple' string has a soundex of 'A140' while Spple has a Soundex of 'S140'.  You should give it a shot anyway and see if it helps you out.  It does work rather well for 'Aple' and 'Apples' though.

https://docs.microsoft.com/en-us/sql/t-sql/functions/soundex-transact-sql

  SELECT SOUNDEX('Apple'),
         SOUNDEX('Spple'),
         SOUNDEX('Aple'),
         SOUNDEX('Apples')

Issue is you'd have to put a function in your where clause.  That's always....fun... for your optimizer.

EDIT:  DIFFERENCE should be useful as well.


INSERT... ON DUPLICATE KEY UPDATE not working as I expect I have a table called "Example"

CREATE TABLE IF NOT EXISTS `example` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1;

I want to insert values if not exists and if the value exists then update, so I am using following statement:

INSERT INTO example (a, b, c) VALUES (1,2,3)
  ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

After the above queries executed the table look like this:

Again I execute the above statement, the result looks like this:

What is wrong with my statement?

Well this is the insert bit that you are using:

  INSERT INTO example (a, b, c) VALUES (1,2,3) ....

here you are not specifying the id (the primary key to be checked for duplication). Since, it is set to auto-increment, it automatically sets the id for the next row with just the values for columns a, b and c.

The row in this case is updated when you provide a primary key (that you want to check is duplicate or not), along with the rest of the row data.
If you need to check and update according to record id, you have to provide the KEY as well, which in your case is id.

Try something like this:

INSERT INTO example (id, a, b, c) VALUES (1,1,2,3) ON DUPLICATE KEY UPDATE a = VALUES(a), b = VALUES(b), c = VALUES(c);

Now if, the id is duplicate, the row will update.

In phpmyadmin go to the structure.

Down in inexes click on go.

index name set the same name like column for UNIQUE value. In this table chose column for UNIQUE value.

In php code, example:
INSERT INTO table_name
(id_second, column1, column2)
VALUES (:id_second,:value1,:value2)
ON DUPLICATE KEY UPDATE
id_second = :id_second, value1 = :value1, value2 = :value2

The value that will not be repeated is set in the db in the indexes.

Behind the sentence: ON DUPLICATE KEY UPDATE, here you set values that can be adjusted.

Note that there is also a value that should not be repeated. It does not matter.


How do I do an outer join between all nested subqueries? (getting an error of 'OUTER' is not a valid input for this position) I have this query:

SELECT AVG(a.score) AS Average1, AVG(b.score) AS Average2, AVG(c.score) AS Average3
FROM
(SELECT subfactor.score, dateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
            WHERE DateID='Jan2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')a
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Feb2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')b
ON a.EmployeeID = b.EmployeeID
OUTER JOIN
(SELECT subfactor.score, DateID, EmployeeID
    FROM employeescore
        LEFT JOIN subfactor
        ON employeescore.SubFactorID = subfactor.SubFactorID
            LEFT JOIN factor
            ON subfactor.FactorID = factor.FactorID
                WHERE DateID='Mar2015' AND employeescore.EmployeeID='1' AND subfactor.FactorID='A1')c
ON b.EmployeeID = c.EmployeeID;

Basically I want to select the averages of the three scores and I want to display the output regardless if one of the joined sub queries doesn't have any records. But I keep getting an error of ('OUTER' is not a valid input for this position).
How can I repair?

You need to specify whether you want an left, right or full outer join. A left outer join is the same as left join. Assuming this is what you want:

[...]
FROM (SELECT subfactor.score, dateID, EmployeeID
      FROM employeescore
      LEFT JOIN subfactor
          ON employeescore.SubFactorID = subfactor.SubFactorID
      LEFT JOIN factor
          ON subfactor.FactorID = factor.FactorID
      WHERE DateID='Jan2015'
        AND employeescore.EmployeeID='1'
        AND subfactor.FactorID='A1'
     ) a
LEFT JOIN (SELECT subfactor.score, DateID, EmployeeID
           FROM employeescore
           LEFT JOIN subfactor
               ON employeescore.SubFactorID = subfactor.SubFactorID
           LEFT JOIN factor
               ON subfactor.FactorID = factor.FactorID
           WHERE DateID='Feb2015'
             AND employeescore.EmployeeID='1'
             AND subfactor.FactorID='A1'
      )b
          ON a.EmployeeID = b.EmployeeID
[...]

Remove the keyword OUTER.  MySQL uses the ON/WHERE clause to decide between INNER and OUTER functionality.

There is another problem...  If Employee#1 was not there one of the months, the result will be the empty.  I suggest that you use a 4th subquery (first) to find all employes, then LEFT JOIN to each of the existing subqueries.

But then you may have another problem -- What happens with NULL in an AVG().  You may have to switch to SUM(...) / COUNT(*) to keep from the averages being strange.


MySQL - installed version 5.5 but mysql --version says 5.0.95 I'm running CentOS 5.2 and upgraded MySQL to 5.5. When I run mysql I get this:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.5.45 MySQL Community Server (GPL)

But when I run mysql --version I get this:

mysql  Ver 14.12 Distrib 5.0.95, for redhat-linux-gnu (x86_64) using readline 5.1

Since the mysqld is mysql55-mysqld I'm sure I'm running 5.5, but why am I getting the wrong version with the version command?

This problem may happen if you have multi MySQL instances on the same machine. There was pre-installed MySQL when the OS was installed, or using auto installation methods like yum: in this case when you type mysql it will point to the pre-installed one since the environment variable point there. you can test it by going to /mysql/install/path/bin use ./mysql --version this should show you the exact version of MySQL.

1) Reboot Mysql Server (/etc/init.d/mysql restart)
If that fails, shut down machine and reboot


SQL Server 2008 Database Restore Error - Cannot open backup device 15105 I am trying to restore from a backup and getting the following error

  Msg 3201, Level 16, State 2, Line 1
  Cannot open backup device 'Z:\FULL_BACKUP.bak'. Operating system error 3(failed to retrieve text for this error. Reason: 15105).
  Msg 3013, Level 16, State 1, Line 1
  RESTORE DATABASE is terminating abnormally.

The code that I used in SQL Server is as follows (I also tried using the dialog screens and got the same result)

RESTORE DATABASE DEX
FROM DISK = 'Z:\FULL_BACKUP.bak'
WITH MOVE 'Decri' TO 'E:\Test\DEX.mdf',
MOVE 'Decri_log' TO 'E:\Test\DEX.LDF'

It may be worth noting that the "FULL_BACKUP" is created by the same SQL server, without any issues (so I assume that the server has permissions).

Drive Z is a network path, for which there is 'full access' to all domain users.

The full backup is 200Gb

Drive E (local) has 500Gb free space

Any help would be appreciated.

OS Error code 3 is ERROR_PATH_NOT_FOUND: The system cannot find the path specified. With a path starting with Z: my pshyhic powers tell me it is a mapped drive. Drive mapping is session scoped and I'm convinced your service session does not see Z:. Mapping a drive to be seen by a service is, for all practical purposes, impossible.

Use a valid path. Use UNC names like \\server\share. Consider the security implication of impersonation and delegation when accessing remote shares.

Here is what you guys need to do:

1) Launch SQL Server Configuration Manager - you should be able to find this program on your: Start menu -> All Programs -> Microsoft SQL Server 2008 -> Configuration Tools -> SQL Server Configuration Manager

2) Expand tree view "SQL Server Services". On right pane, youl will see the list of Services installed. In my case, I will have to look at the SQL Server 2008 R2 instance. Previously, it was set to run under "NT AUTHORITY\NETWORK SERVICE"

3) Click on this SQL Server instance -> Properties and change the "Log on" to LOCAL SYSTEM. The system will prompt you that it needs to restart the SQL Service. Please do so.

4) Once this is done, you can then backup your database without any hassles!

Hope this helps!


view current session roles for another session In Oracle, I can view the currently set roles for my current session using the query SELECT * FROM SESSION_ROLES.

Is there any way that I can see this information for another session?

SESSION_ROLES view shows list of roles assigned after you makes session in instance as those roles granted to your connected users. you can view other users granted roles using below:

select   * from   dba_role_privs;

If you want to know list of roles of particular user, use below query:

select   * from   dba_role_privs where grantee='USER_NAME';

Please note that you must have DBA privilege to view above. and you can't see list of roles using currently connected others session. you can only see what role the can be use after getting connected.


Removing Databases in Recovery Pending Status after AG group was removed I have removed a High Availability Group, disabled the option in sql configuration manager and restarted and I have also removed the Failover cluster components from windows and restarted.

Unfortunately the databases are marked as Recovery Pending on both servers.

I have attempted to drop them, but get this:

  Msg 3752, Level 16, State 1, Line 1
  The database 'GEO' is currently joined to an availability group.  Before you can drop the database, you need to remove it from the availatility group.

I also tried to recovery the db with no success and also set it into emergency mode but cannot:

  The operation cannot be performed on database "GEO" because it is involved in a database mirroring session or an availability group. Some operations are not allowed on a database that is participating in a database mirroring session or in an availability group.

I even tried to brute force the server by stopping the engine and renaming the file to try to get it into suspect, but its still in recovery pending...  I cant re-enable the HADR settings as the windows cluster has been destroyed.

All systems catalogs are empty apart from sys.availability_replicas which has a reference to the other server (first time in a few years I wish I was back in the good old days when I could access system tables).

Any ideas how to get rid of these databases?

To resolve this I had to recreate the whole Failover cluster, re-enable HA in the SQL Configuration manager and then I could set one of the db's to recover

I had similar problems, and this solved it

ALTER DATABASE dbname SET HADR OFF
DROP DATABASE dbname


Profling for Index Maintenance I am wanting to perform some index maintenance, in particular we have a poor performing query against a particular table.  Is it a sensible strategy to profile on RPC:Completed, SP:StmtCompleted and SQL:BatchCompleted but filtering only on that table?

The idea being I can focus my indexing maintenance on that table and work out what the best indexes will be?

Joe Changs ExecStats provides a nice GUI for analysing which queries touch each index.

It can be downloaded from: http://www.qdpma.com/JoeChang.html

Or write Joe directly for the latest version. The tool uses sys.dm_exec_query_stats so it is very lightweight.

When it comes to SQL Server index maintenance you cant beat this maintenance script. http://ola.hallengren.com/sql-server-index-and-statistics-maintenance.html

I wouldn't use the profiler to decide anything like this. If you want to write your own simple maintenance plan you need to look at the index DMVs.


Database: One table or many I am working on a project of classified ads site.

I have 12 main categories, and their items are 142, Like vehicles have cars, scooters, bikes, etc. Mobiles Phones have mobiles, tablets, accessories.

Now should i use few tables and use Json ?

Or should i create separate tables for each item (142 tables) ?

Currently i have decided 142 tables to save myself from relations and joins and heavy coding.

Which way is correct or suggest another one. What are the pros and cons ? I have search a lot but didn't got anything that answers my question.

You should definitely (in my opinion) not have 142 tables - it'll be a complete mess to name, index and maintain, and you'll generate yourself a lot of extra work if you some day add another category, if you need to move ads from one category to another, etc.

Storing JSON blobs in the database will kill performance when you're performing searches, so I wouldn't go with that either.

A good relational table design (yes, with joins) is really the best approach when you're using a relational database system. For instance, one table with categories (12 records), joined to one table with items (142 records), joined to a table of ads.

Table name: Categories
Columns:
category_id   number(5) not null - primary key
category_desc varchar(100) not null

Table name: Items
Columns:
item_id     number(5) not null - primary key
item_desc   varchar(100) not null
item_details varchar(2000) not null
item_contact_phone    varchar(20) not null
etc...

Table: item_categories
item_id number(5) not null - this is a foreign key back to the Items table
category_id  number(5) not null - this is a Foreign Key back to the Categories table.

Hope this helps!


Limit results to the first 2 ranking rows In SQL Server 2008, I am using RANK() OVER (PARTITION BY Col2 ORDER BY Col3 DESC) to return data set with RANK. But I have hundreds of records for each partition, so I will get values from rank 1, 2, 3......999. But I want only up to 2 RANKs in each PARTITION.

Example:

ID   Name    Score    Subject
1    Joe      100      Math
2    Jim      99       Math
3    Tim      98       Math
4    Joe      99       History
5    Jim      100      History
6    Tim      89       History
7    Joe      80       Geography
8    Tim      100      Geography
9    Jim      99       Geography

I want the result to be:

SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC)
FROM Table

Subject        Name      Rank
Math           Joe        1
Math           Jim        2
History        Jim        1
History        Joe        2
Geography      Tim        1
Geography      Jim        2

I want only rank 1 and 2 in each category. How do I do this?

select * from (
SELECT Subject, Name, RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
) a
where a.RN <= 2

For Teradara alternatively you can do:

SELECT
Subject,
Name,
RANK() OVER (PARTITION BY Subject ORDER BY Score DESC) as RN
FROM Table
QUALIFY a.RN <= 2


How to quickly increase column size in Oracle? In our Oracle 11 database we have a table with more that 100 million rows and we would like to increase size of one column (not used in any index). I tried to execute command

alter table shipment modify (description varchar2(21) );

but it takes to much time. Is there any other (faster) way to increase column size?

Your two options are (depending on the use case):

https://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:8753545300346585555

https://asktom.oracle.com/pls/asktom/asktom.search?tag=best-way-to-change-the-datatype-of-a-column-in-a-table-with-20-million

Is there any other (faster) way to increase column size?

You can try the parallel clause

alter table shipment modify (description varchar2(21) ) parallel 8;

And you can try the dbms_redefinition package.

Redefining Tables Online


Import .bak file in Microsoft SQL Server 2008 Service Pack 3 I have a backup of my database as a TestDatabase.bak file but I am unable to restore it.

When I try to restore I receive the following error message in SSMS:

  RESTORE could not start database 'FieldopsDevDynamic07042013'

The error log shows:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

How can I restore this database?

Your backup is from a database that is using Enterprise Edition features. You are trying to restore it to an Express Edition server. That will not work.

The message in your error log is telling you exactly that:

  Database 'FieldopsDevDynamic07042013' cannot be started because some
  of the database functionality is not available in the current edition
  of SQL Server.

The error log even tells you which feature is causing the problem:

  Database 'FieldopsDevDynamic07042013' cannot be started in this
  edition of SQL Server because it contains a partition function...

Try restore to an Enterprise or Developer edition server. If it still fails update your question with the result of your attempt.

I dont know if this will help or not...but with some of the older versions of SQl if you had a file still open, it would keep processing until all files were closed.  Make sure you have closed all files/connections.  That might help you.


MySQL - Delete with IN Clause taking more time In MySQL 5.6 I have around 60 million rows. I wanted to remove rows based on Id. Here Id is the primary key.

One strange behavior I had today.

delete from tbl where id=1;

Its executed in milliseconds.

Then I did this.

CREATE TEMPORARY TABLE DELETE_ID (ID int);
Insert into DELETE_ID select id from (subery1(subquery2(subquery3)));

The DELETE_ID has around 150 ID.

Delete from tbl where id in (select id from DELETE_ID);

Its taking more than 30mins, but deleting a single row.

There were no locks or deadlock.

In innodb_trx the status was sometimes fetching rows sometimes unlocking rows.

Can anyone help me to understand why this is happening?

WHERE .. IN is slow in most cases.

Use multi-table delete:

DELETE tbl.*
FROM tbl, DELETE_ID
WHERE tbl.id = DELETE_ID.id

Delete from tbl where  EXISTS (SELECT id FROM DELETE_ID                 WHERE tbl.id = DELETE_ID.id);


SQL Server compact- Query that return 2 constant columns even if query didn't return data Please look at the following simple query:

Select CustomerID
From Customers c
Where  c.CustomerId = "1234"

I want to write similar query that does the following:

Always return 2 columns with constant string instead of CustomerID in this structure:

If value was found then return 2 columns:
Found Value in DB, CustomerId

Else return:
Didn't found value in DB, CustomerId

How can I do that? Especially in Microsoft compact SQL server limited syntax...

Derived tables and Case statements are supported in SQL CE, so you can use this script

Select
case
when s.custs = 0 then 'Didnt found value in DB'
else 'found value in DB'
end
,'customer id'
from
(select COUNT(*) as 'custs' from Customers c Where  c.CustomerId = 1234) s

for reference check what can and what cannot be used in SQL CE

I don't think the @@RowCount is available in SQL CE, so this will be using COUNT function.

Declare @cnt INT=0

Select @cnt=CustomerID
From Customers c
Where  c.CustomerId = "1234"

If @cnt = 0

Select  "Didn't Found value in DB"
,"customerid"

If @cnt<>0

Select  "Found value in DB" ,"customerid"


How to connect to remote PostgreSQL server I wrote a web app, using java + spring+ PostgreSQL, which I have up and running.

I have a tester for my system and I want him to be able to connect to my database directly to check the result of interaction with application. As always, the web is littered with articles on how to connect, but one simple thing they don't mention is whether a client should install some app to connect to the database? I get the bit about making correct settings for the database, but if a mere mortal enters the following in their windows console:

psql -h <host> -p <port> -u <database>

they will get 'psql' is not recognized as an internal or external command,
operable program or batch file. I understand I don't get that error because I operate via shell and my home path is set to psql.exe, but again the client doesn't need to install PostgreSQL to access remote a PostgreSQL database. It just doesn't click in my head.

How can a client connect to database via command prompt without installing anything?

I suppose the simplest way is to let the tester use ssh to login to your server. Then the tester's execution environment is whatever your server exposes through the shell. (I presume that would include psql.)

As far as I know, all modern Unix derivatives install ssh by default. That would include Linux, Solaris, OS/X (Apple), and BSD. I believe that Windows does not ship with ssh, but Cygwin and PuTTY are commonly used to remedy that problem.

You need to check that psql is in their $PATH - or provide the full path to psql when executing it.


Why does Postgres generate an already used PK value? I'm using Django, and every once in a while I get this error:

  IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey"
  DETAIL:  Key (id)=(1) already exists.

My Postgres database does in fact have a myapp_mymodel object with the primary key of 1.

Why would Postgres attempt to use that primary key again? Or, is this most likely my application (or Django's ORM) causing this?

This issue occurred 3 more times in a row just now. What I've found is that when it does occur it happens one or more times in a row for a given table, then not again. It seems to happen for every table before it completely stops for days, happening for at least a minute or so per table when it does occur, and only happening intermittently (not all tables right away).

The fact that this error is so intermittent (happened only 3 or so times in 2 weeks - no other load on the DB, just me testing out my application) is what makes me so wary of a low-level problem.

PostgreSQL will not try to insert duplicate values on its own, it is you (your application, ORM included) who does.

It can be either a sequence feeding the values to the PK set to the wrong position and the table already containing the value equal to its nextval() - or simply that your application does the wrong thing.  The first one is easy to fix:

SELECT setval('your_sequence_name', (SELECT max(id) FROM your_table));

The second one means debugging.

Django (or any other popular framework) doesn't reset sequences on its own - otherwise we would have similar questions every other day.

You can also do this:
ALTER SEQUENCE 'sequence_name_goes_here' name" RESTART WITH (SELECT max(id) + 1 FROM 'table_name_goes_here');