How do you install MySQL 5.1.34 on CentOS 5.5? I am trying to install Mysql 5.1 on cent OS. I researched  a lot but could not find any link which gives step by step procedure. Everywhere, there are vague answers. Can someone please give me steps to be followed to install Mysql 5.1 ?

While I would normally not recommend this
To download and install MySQL 5.1.34, run these commands at your Linux prompt AS IS...
MYSQL_VERSION=5.1
MYSQL_VERSION_RELNUM=34
MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM}
REDHAT_RELEASE=5
BINARY_VERSION=x86_64
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
MYSQL_MIRROR_INDEX=0
MYSQL_SHARE2_LIB=MySQL-shared-compat-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARED_LIB=MySQL-shared-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_DEVEL__LIB=MySQL-devel-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_CLIENT_LIB=MySQL-client-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
MYSQL_CLIENT_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_CLIENT_LIB}"
MYSQL_SERVER_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SERVER_LIB}"
cd
rm -rf MySQLInstall
mkdir  MySQLInstall
cd     MySQLInstall
wget ${PERL_URL}
wget ${MYSQL_SHARE2_URL}
wget ${MYSQL_SHARED_URL}
wget ${MYSQL_DEVEL__URL}
wget ${MYSQL_CLIENT_URL}
wget ${MYSQL_SERVER_URL}
pwd
ls -l
rpm -Uvh --force ${PERL_FILE}
rpm -Uvh --force ${MYSQL_SHARE2_LIB}
rpm -Uvh --force ${MYSQL_SHARED_LIB}
rpm -Uvh --force ${MYSQL_DEVEL__LIB}
rpm -Uvh --force ${MYSQL_CLIENT_LIB}
rpm -Uvh --force ${MYSQL_SERVER_LIB}

To go get the Latest Version of MySQL, run these AS IS...
HOWMANYBITS=64
WHATOS=5
BINARY_VERSION=000
if [ ${HOWMANYBITS} -eq 32 ] ; then BINARY_VERSION=i386   ; fi
if [ ${HOWMANYBITS} -eq 64 ] ; then BINARY_VERSION=x86_64 ; fi
MINOR_RELEASE=20
MYSQL_RELEASE=5.5.${MINOR_RELEASE}
OS_VERSION=000
if [ ${WHATOS} -eq 0 ] ; then OS_VERSION=linux2.6 ; fi
if [ ${WHATOS} -eq 5 ] ; then OS_VERSION=rhel5    ; fi
RELEASE_TAG=1
DEVEL__RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-devel-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARD2_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-compat-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SHARED_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-shared-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
CLIENT_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-client-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
SERVER_RPM=http://www.mysql.com/get/Downloads/MySQL-5.5/MySQL-server-${MYSQL_RELEASE}-${RELEASE_TAG}.${OS_VERSION}.${BINARY_VERSION}.rpm/from/http://mysql.mirrors.pair.com/
PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
PERL_URL=${PERL_LOCATION}/${PERL_FILE}
cd
rm -rf MySQL5.5_Install
mkdir MySQL5.5_Install
cd MySQL5.5_Install
wget ${PERL_URL}
wget ${DEVEL__RPM}
wget ${SHARD2_RPM}
wget ${SHARED_RPM}
wget ${CLIENT_RPM}
wget ${SERVER_RPM}
ls -l
for RPM in `ls -lSr *.rpm | awk '{print $9}'` ; do rpm -Uvh --force ${RPM} ; done

CAVEAT
When running the download for MySQL 5.1.34, if the RPMs for the MySQL Binaries do not get retrieved, change this line
MYSQL_MIRROR_INDEX=0

to
MYSQL_MIRROR_INDEX=1

One value or the other will work.
UPDATE 2012-02-24 14:35 EDT
I just tried getting MySQL 5.1.34 (I skipped the install part) and I got this
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION=5.1
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION_RELNUM=34

[redwards@lw-lts-155 MySQLInstall]$ MYSQL_VERSION_RELEASE=${MYSQL_VERSION}.${MYSQL_VERSION_RELNUM}
[redwards@lw-lts-155 MySQLInstall]$ REDHAT_RELEASE=5
[redwards@lw-lts-155 MySQLInstall]$ BINARY_VERSION=x86_64
[redwards@lw-lts-155 MySQLInstall]$ PERL_LOCATION=ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages
MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
[redwards@lw-lts-155 MySQLInstall]$ PERL_FILE=perl-DBI-1.53-2.fc7.x86_64.rpm
[redwards@lw-lts-155 MySQLInstall]$ PERL_URL=${PERL_LOCATION}/${PERL_FILE}
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_MIRROR_INDEX=0
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARE2_LIB=MySQL-shared-compat-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARED_LIB=MySQL-shared-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_DEVEL__LIB=MySQL-devel-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_CLIENT_LIB=MySQL-client-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SERVER_LIB=MySQL-server-community-${MYSQL_VERSION_RELEASE}-${MYSQL_MIRROR_INDEX}.rhel${REDHAT_RELEASE}.${BINARY_VERSION}.rpm
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARE2_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARE2_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SHARED_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SHARED_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_DEVEL__URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_DEVEL__LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_CLIENT_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_CLIENT_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ MYSQL_SERVER_URL="http://downloads.mysql.com/archives/mysql-${MYSQL_VERSION}/${MYSQL_SERVER_LIB}"
[redwards@lw-lts-155 MySQLInstall]$ cd
[redwards@lw-lts-155 ~]$ rm -rf MySQLInstall
[redwards@lw-lts-155 ~]$ mkdir  MySQLInstall
[redwards@lw-lts-155 ~]$ cd     MySQLInstall
[redwards@lw-lts-155 MySQLInstall]$ wget ${PERL_URL}
--2012-02-24 14:26:08--  ftp://ftp.de.netclusive.de/pub/parallels/Plesk/Expand/2.3.1/packages/perl-DBI-1.53-2.fc7.x86_64.rpm
           => `perl-DBI-1.53-2.fc7.x86_64.rpm'
Resolving ftp.de.netclusive.de... 89.110.128.25
Connecting to ftp.de.netclusive.de|89.110.128.25|:21... connected.
Logging in as anonymous ... Logged in!
==> SYST ... done.    ==> PWD ... done.
==> TYPE I ... done.  ==> CWD /pub/parallels/Plesk/Expand/2.3.1/packages ... done.
==> SIZE perl-DBI-1.53-2.fc7.x86_64.rpm ... 625843
==> PASV ... done.    ==> RETR perl-DBI-1.53-2.fc7.x86_64.rpm ... done.
Length: 625843 (611K)

100%[=============================================================================================================================>] 625,843      767K/s   in 0.8s

2012-02-24 14:26:10 (767 KB/s) - `perl-DBI-1.53-2.fc7.x86_64.rpm' saved [625843]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SHARE2_URL}
--2012-02-24 14:26:10--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 5058961 (4.8M) [application/octet-stream]
Saving to: `MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 5,058,961    518K/s   in 9.7s

2012-02-24 14:26:21 (512 KB/s) - `MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm' saved [5058961/5058961]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SHARED_URL}
--2012-02-24 14:26:22--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 1827818 (1.7M) [application/octet-stream]
Saving to: `MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 1,827,818    487K/s   in 3.7s

2012-02-24 14:26:26 (487 KB/s) - `MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm' saved [1827818/1827818]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_DEVEL__URL}
--2012-02-24 14:26:26--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 8693623 (8.3M) [application/octet-stream]
Saving to: `MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 8,693,623    512K/s   in 17s

2012-02-24 14:26:46 (511 KB/s) - `MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm' saved [8693623/8693623]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_CLIENT_URL}
--2012-02-24 14:26:46--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 7515446 (7.2M) [application/octet-stream]
Saving to: `MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 7,515,446    501K/s   in 15s

2012-02-24 14:27:03 (497 KB/s) - `MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm' saved [7515446/7515446]

[redwards@lw-lts-155 MySQLInstall]$ wget ${MYSQL_SERVER_URL}
--2012-02-24 14:27:03--  http://downloads.mysql.com/archives/mysql-5.1/MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm
Resolving downloads.mysql.com... 156.151.63.14
Connecting to downloads.mysql.com|156.151.63.14|:80... connected.
HTTP request sent, awaiting response... 200 OK
Length: 20053095 (19M) [application/octet-stream]
Saving to: `MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm'

100%[=============================================================================================================================>] 20,053,095   506K/s   in 42s

2012-02-24 14:27:46 (462 KB/s) - `MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm' saved [20053095/20053095]

[redwards@lw-lts-155 MySQLInstall]$ pwd
/home/lw-staff/redwards/MySQLInstall
[redwards@lw-lts-155 MySQLInstall]$ ls -l
total 42832
-rw-r--r-- 1 redwards users  7515446 Apr  7  2009 MySQL-client-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  8693623 Apr  7  2009 MySQL-devel-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users 20053095 Apr  7  2009 MySQL-server-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  1827818 Apr  7  2009 MySQL-shared-community-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users  5058961 Apr  7  2009 MySQL-shared-compat-5.1.34-0.rhel5.x86_64.rpm
-rw-r--r-- 1 redwards users   625843 Feb 24 14:26 perl-DBI-1.53-2.fc7.x86_64.rpm

Keep in mind that the MySQL Binaries are three years old


sys.sp_reset_connection. Why theres a lot of them ? Theres a lot of questions here on DBA EXCHANGE. But none of them can help me.

I have 2 problems:

1) Using sp_whoisactive, I found lots of sys.sp_reset_connection;1( I know they are sp_reset_connections because I used dbcc inputbuffer(SPID):

This is the "main server", so the other servers ( 3,4,7 ) are constantly requesting information of some databases here ( And what I see is, every request is for the MASTER database ).

2) Why they're taking so much time?

Looking at some answers here, I found that the system is waiting for the CPU. but all CPU is null. How can this be the problem?

  Under the covers SQL Server uses the sp_reset_connection logic to
  ‘reset’ the connection state for the SQL Server, which is faster than
  establishing a completely new connection.  Older drivers send the
  procedure call as a separate, TDS, round-trip to the SQL Server.
  Newer client drivers add a flag bit along with the next command,
  avoiding the extra network round-trip.

Good...But I still don't know what to do.

This is not giving me problems. I just want to know how to troubleshoot this.

sp_reset_connection is an artifact of using Connection Pooling. And what you think you are seeing is not what is actually happening.

Connection Pooling is a feature of the client library more than of SQL Server. The client provides most of the functionality, while SQL Server itself mainly provides sp_reset_connection. When using Connection Pooling, the client driver does not actually close the physical connection to SQL Server when the app code calls the "close connection" method. Instead, the driver keeps the connection open and will link it to the next "open connection" request that uses the exact same connection string (and from the same Windows account, if using a Trusted Connection / Integrated Security). Now, because the connection was never closed, SQL Server knows only that the client is keeping the connection open, but not why. And because the connection is remaining open, the session still exists with whatever resources and settings were in place as of the last command / batch executed. This is why temporary tables created in the main scope (i.e. not in a stored procedure) will continue to exist (for details, please see: Sessions, Temporary Objects, and the Afterlife), and open transaction will stay open.

SQL Server doesn't know that connection pooling is being used until the next command / batch is submitted, at which point it executes sp_reset_connection. It is sp_reset_connection that cleans up the prior state of the session so that it will be just like a brand new session, which is what the client code is expecting (with a few minor items that do not get cleaned up / reset).

Of course, that description is based on the newer drivers. As the quote in the question states, older drivers sent a separate request for sp_reset_connection. The fact that you are seeing sessions that have no currently executing request (i.e. CPU is NULL) is expected when using Connection Pooling. What is not typical is seeing sp_reset_connection via DBCC INPUTBUFFER. This is not typical since the newer drivers request sp_reset_connection upon submitting the first batch / command upon reconnecting, so you would never see sp_reset_connection since it will never be the last thing to execute (though you can see it using SQL Server Profiler or Extended Events). But, if you are seeing it using DBCC INPUTBUFFER (which will continue to show the last command / batch executed, even after the batch completes), then that would appear to match the behavior of the older drivers (assuming that the older drives, because they are not sending a flag along with the next command / batch, are requesting sp_reset_connection upon "close connection" requests).

Either way, sp_reset_connection is not currently running. And the reason that the requests are all for the master DB is most likely do to master being the default Database for the Login(s) being used.


Bigfile in oracle 12 is full with 120G Hello I have The tablespace with 8192 and According with this Link From Oracle BigFile
I can have 32,768 GB In my tablespace but one of my table have over 1 billion records that make datafile over the 120GB and I getting error with ORA-01652: unable to extend temp segment by XXX in tablespace so what happening here why with 120GB I'm reaching my limit in datafile?
CREATE BIGFILE TABLESPACE TABLE_SPACE
    DATAFILE
        '~\TABLE_SPACE.DBF' SIZE 127301910528 AUTOEXTEND ON NEXT 8192 MAXSIZE 35184372064256
    BLOCKSIZE 8192
    DEFAULT NOCOMPRESS NO INMEMORY
    ONLINE
    SEGMENT SPACE MANAGEMENT AUTO
    EXTENT MANAGEMENT LOCAL AUTOALLOCATE;

ORA-01652: unable to extend temp segment by XXX

This isn't about your 120GB bigfile data tablespace at all.
Your TEMP tablespace isn't big enough to handle whatever sorts or temporary result sets are created by the queries that are being run in your database. If it can't autoextend, then it is probably because you have filled up the disk drive and you don't have room for more, or because the TEMP tablespace data file(s) have reached the maximum size allowed by the file system (typically 32GB each).
If you still have space available on the file system holding your TEMP tablespace, then add one or more more files to the TEMP tablespace. If the partition is completely full, then work with your system and/or storage administrator to determine how to enlarge the file system or where else you could place additional data files.


Separate data from 1 column into multiple columns Below is sample data and expected results:

Table1: salesprice

ItemNo     Variant     SalesCode     UnitPrice
 A           1            SRP           100
 A           2            SRP           200
 A           1            WSP           50
 A           2            WSP           100
 B           1            SRP           300
 B           2            SRP           400
 B           1            WSP           150
 B           2            WSP           200

Query Results:

ItemNo     Variant     SalesCode(SRP)  UnitPrice   SalesCode(WSP)   UnitPrice
  A          1              SRP           100          WSP             50
  A          2              SRP           200          WSP             100
  B          1              SRP           300          WSP             150
  B          2              SRP           400          WSP             200

CREATE TABLE #Data
    (
      ItemNo VARCHAR(10),
      Variant INT,
      SalesCode VARCHAR(30),
      UnitPrice INT
    )
GO
INSERT  INTO #Data
        ( ItemNo, Variant, SalesCode, UnitPrice )
VALUES  ( 'A', 1, 'SRP', 100 ),
        ( 'A', 2, 'SRP', 200 ),
        ( 'A', 1, 'WSP', 50 ),
        ( 'A', 2, 'WSP', 100 ),
        ( 'B', 1, 'SRP', 300 ),
        ( 'B', 2, 'SRP', 400 ),
        ( 'B', 1, 'WSP', 150 ),
        ( 'B', 2, 'WSP', 200 )
GO
SELECT  ItemNo, Variant, 'SRP' AS SalesCode_SRP,
        SUM(CASE SalesCode
              WHEN 'SRP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_SRP, 'WSP' AS SalesCode_WSP,
        SUM(CASE SalesCode
              WHEN 'WSP' THEN UnitPrice
              ELSE 0
            END) UnitPrice_WSP
FROM    #Data
GROUP BY ItemNo, Variant

GO
DROP TABLE  #Data


Recovering MySQL 5.7 tables in MariaDB 10 I did a very foolish thing... I did not realize installing mariaDB would uninstall MYSQL 5.7 using apt-get. (Using Ubuntu 16.04).

So now I am trying to recover my data.

So far, I have read many guides and tried several things.  I think this has gotten me into bad position, because the advice seems to be very version specific.

My most successful attempt is 1) Fresh install of MaraiDB. 2) copy * files from old sql directory to new. 3) Copy database directory myDatabase/ from old sql directory to new. 3) Run mysql_secure_installation 4) Configure my users, etc. etc.

This works except that I cannot run sql queries.

SELECT * FROM myDatabase.tableOne;

I get either:

SQL Error [1932] [42S02]: (conn:56) Table 'myDatabase.tableOne' doesn't exist in engine

OR

SQL Error [1146] [42S02]: (conn:56) Table 'myDatabase.tableTwos'

It seems that all tables give one of these two errors.

So far, I have read that mysqlcheck can fix this.

If I run

/var/lib/mysql$ mysqlcheck myDatabase
myDatabase.tableOne
Error    : Table 'myDatabase.tableOne' doesn't exist in engine
status   : Operation failed

I can't find any suggested solutions for fixing this. Although perhaps I went the wrong way from the beginning. Thanks for your help.

Before doing anything else I would recommend that you backup the old mysql data directory and the configuration file on /etc. So should something go wrong you don't loose your data.

Then I would check what specific MariaDB version of you have installed. As per MariaDB compatibility chart, you need MariaDB 10.2 to migrate from MySQL 5.7. Once you are sure you have the right version installed, you should refer to the migration doc:

https://mariadb.com/kb/en/library/migrating-to-mariadb/

Alternatively you could uninstall MariaDB and install MySQL again, just check that you are installing the latest 5.7 version available. For this you can download a deb package from the mysql downloads and install it manually or set up the mysql repository: https://dev.mysql.com/downloads/repo/apt/


Supplied parameter sid is in use I'm trying to copy a user from my live server to my DR server using the following command:

CREATE LOGIN [xxx]
WITH PASSWORD = 0x010006278DD83306AD19FA8B0CE4405F13DA83DDDFB25B088D7C HASHED
    , SID = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D
    , DEFAULT_DATABASE = [master]
    , CHECK_POLICY = OFF
    , CHECK_EXPIRATION = OFF

I then get an error:

  Msg 15433, Level 16, State 1, Line 1
  Supplied parameter sid is in use.

So I check the sys.syslogins with:

SELECT *
FROM sys.syslogins
WHERE sid =  '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

This finds nothing. So my question is what else could be using this sid?

As @SteBov noted in his comment, comparing the sid with quotes around it is problematic since that forces an implicit conversion to be performed against the sid column.  Since the conversion converts the hexadecimal value into a character value, you will never see the login in the query results.

If you check the execution plan for the following statement:

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = '0xFEEBD63ECD0F6A41AE60A6E0414F5A1D';

We see the following clustered index scan against the internal table, sys.sysxlgns:

In the details, you can see the following predicate:

  [master].[sys].[sysxlgns].[type] as [p].[type]<>'M' AND CONVERT_IMPLICIT(varchar(85),[master].[sys].[sysxlgns].[sid] as [p].[sid],0)='0xFEEBD63ECD0F6A41AE60A6E0414F5A1D'

If you run a select changing the CONVERT_IMPLICIT into a simple CONVERT statement, as below, you can see the sid column is not what you'd expect:

SELECT CONVERT(varchar(85), sp.sid, 0)
FROM sys.server_principals sp;

In order to successfully find the login with the duplicate sid, you'll need to run the select like:

SELECT *
FROM sys.syslogins sl
WHERE sl.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;

Preferably, you'd look at sys.server_principals since syslogins is a SQL Server 2000 compatible view that has been deprecated.

SELECT *
FROM sys.server_principals sp
WHERE sp.sid = 0xFEEBD63ECD0F6A41AE60A6E0414F5A1D;


How do you configure Postgres 9.4 for incremental backups and hot standby? I want to set up Postgres to do incremental backups. I'm using PostgreSQL 9.4 on Windows with a database that weighs about 1GB and has a growing 10000 records each day.

I understood that the steps necessary to configure the backup are:

Configure "write_ahead_logs" to "on" and "wal_level" in "archives/hot_standby"
Make a "start_basebackup" and copy the data folder
After copying the files do a "stop_basebackup"
Finally copy to another location the WAL files generated

But I have doubts about the Postgres documentation:

How often it is recommended or advisable to make a new base_backup
and, if necessary?
If you can have a slave server configured with
hot_standby and have the incremental backup at the same time?

Also I wonder if there is a PITR manager for Windows, as the Barman for linux.

Thanks

To address your first question, if you take a single basebackup of your database and just keep archiving away the WAL segments, the time to restore that database will grow longer and longer as the amount of WAL segments increases. That is not really an ideal situation to be in if you have downtime and need to bring a working database back up.

Most people figure out how much time they can spend restoring, and what recovery requirements they need to meet, and keep multiple basebackups and extra WAL segments as their storage budget and their recovery requirements allow.

Additionally, with very, very, very, very large WAL archives, sometimes things might happen to individual segments (which is yet another great reason to always test your backups!) and if a WAL segment fails to restore, no other segments following that failed segment will restore. At that point, you're out of luck.

One general rule I follow is to take a weekly basebackup, and keep 3 weeks of WAL segments and 2 basebackups, which allows me enough peace of mind to know I have one known-good backup, and a known-good set of WAL segments, and a reasonable restore time.

For your second question, you can have a hot standby server, and incremental backups, if you set it up that way. Your hot standby server only requires an initial basebackup from the primary, and a stream of WAL segments or WAL records (in the case of Streaming Replication, which 9.4 supports and is much nicer than simple WAL shipping).

I would recommend having a hot standby using Streaming Replication, and then taking your basebackups with pg_basebackup from the hot standby server, which will reduce the resource load on your primary considerably.

For an incremental (PITR) backup, you just need to have your primary server send the WAL segments it generates somewhere else (it's best that it's different hardware, of course) using an archive_command or pg_receivexlog.

Pushing the weekly basebackup you created with pg_basebackup there as well will make keeping track of all the necessary components for incremental restoration much easier. Plus, having everything together makes testing those backups trivial.

As for the third question, I don't know of a PITR manager that runs on Windows. In theory, since it's just Python, it could run on Windows, but as the FAQ says, it hasn't been tested.

Magnus Hagander has a great talk on backups from PgCon 2013 that you might find helpful: PostgreSQL Backup Strategies


Transactional Replication Update process I need to create report using ssrs. for that i create the main table contains the values from nine other tables which is the Source tables. Now the new source tables which are used for main table are in another server.So using transactional replication I got all the source tables from another server to my datawarehouse.Now I need the main table values are get automatically updated whenever the transactions(updates) made in the nine source tables.

I am new to replication So please help me.

Thanks in advance

If you need your combined table updated automatically you would need to put triggers on the 9 tables which have been replicated to your server.

A better option would be to put an indexed view on top of the 9 tables (I'm assuming that your table is created with a single query).  This way there's no need to handle updating your table as the indexed view would always be up to date and you can simply just query it.  As it is an indexed view you can add any non-clustered indexes to it as needed to make performance nice and fast.

If the query to combine the 9 tables together into your schema a normal view should be fine.  Just have your report query the view, and the data will always be up to date.  The big difference between a normal view and an indexed view (above) is that with a normal view the indexes go on the 9 replicated tables where with an indexed view the indexes can be placed on the view directly.

What it comes down to, is that there isn't any need for an additional table.


How to make TokuDB engine on Percona delete faster I've done some reading on the percona website, most notably MySQL Partitioning: A Flow Chart where they recommend TokuDB as a solution to faster(er) deletes over innoDB rather than partitioning.

I've taken a sample table, and have added the same 20M rows to both tables. I'm then running tests by deleting ~ 200k rows on an otherwise quiet machine (my extra laptop).

I'm not seeing any differences in speed between deleting 200k rows on InnoDB and TokuDB - they're within a few % time-wise (ie. 45 seconds vs 43 seconds).

I installed Percona 5.6 along with the TokuDB engine on Ubuntu but did not otherwise do any configuration.

Can anyone suggest what I might be missing?

The table looks a bit like this:

CREATE TABLE `testTable` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `filename` varchar(200) DEFAULT NULL,
    -- 20-ish columns
    PRIMARY KEY (`id`),
    KEY `filename` (`filename`),
    CLUSTERING KEY `clstr_key` (`filename`),
    -- 3 or 4 other indexes
) ENGINE=TokuDB AUTO_INCREMENT=20000000 DEFAULT CHARSET=uft8

I've tried doing deletes in several ways to gauge results, including:

DELETE FROM testTable WHERE id BETWEEN :start AND :end;
DELETE FROM testTable WHERE filename IN ('FilenameA', 'FilenameB', 'FilenameC');
-- Where dateCol is indexed
DELETE FROM testTable where dateCol BETWEEN :start AND :end;

Results were all pretty similar for both TokuDB and InnoDB across all methods.

If your table has 1 or more secondary indexes than each delete requires a read on the primary key index to get the fields necessary for the deletes in each secondary, which eliminates much of the TokuDB advantage over InnoDB. Also, if your delete pattern is "left-most" or you are performing a large number of deletes in a specific key range you will likely end up with quite a bit of garbage in your Fractal Tree indexes which will need to be cleaned up by optimizing the table/indexes.


GROUP BY possible sequences I have a table containing the following data, using Postgres 9.6:

log_id | sequence | made_at (timestamp)
206480    1            1
206480    1            2
206480    2            3
206480    3            4
206480    1            5
206480    2            6
206480    4            7
206480    5            8
206480    1            9
206480    2           10
206481    1           11
206481    2           12
206481    3           13
206481    4           14

I have to group and aggregate on the ID so I get an array of possible sequences. In the end I want the data to look like this:

log_id | sequence
206480  {1,1,2,3}
206480  {1,2,4,5}
206480  {1,2}
206481  {1,2,3,4}

I want a new row (with the sequences) when:

the log_id changes; or
the next sequence number is lower than the current sequence number.

There is another column which specifies the ordering (a timestamp), but it's in another table (I join them and use that timestamp). I left it out to make things easier, but we can assume the column is called made_at.

select      log_id
           ,array_agg (sequence)

from       (select      log_id
                       ,sequence
                       ,count (is_restart) over
                        (
                            partition by    log_id
                            order by        made_at
                        ) as restart_id

            from        (select      made_at
                                    ,log_id
                                    ,sequence
                                    ,case
                                         when sequence <
                                              lag (sequence) over
                                              (
                                                  partition by    log_id
                                                  order by        made_at
                                              )
                                         then 1
                                     end            is_restart

                         from        logs
                         ) l
            ) l

group by    log_id
           ,restart_id

order by    log_id
           ,restart_id
;

+--------+-----------+
| log_id | array_agg |
+--------+-----------+
| 206480 | {1,1,2,3} |
+--------+-----------+
| 206480 | {1,2,4,5} |
+--------+-----------+
| 206480 | {1,2}     |
+--------+-----------+
| 206481 | {1,2,3,4} |
+--------+-----------+

Walkthrough

Identify restarts by comparing current sequence to previous sequence (LAG).

select      made_at
           ,log_id
           ,sequence

           ,case
                when sequence <
                     lag (sequence) over
                     (
                         partition by    log_id
                         order by        made_at
                     )
                then 1
            end            is_restart

from        logs

+---------+--------+----------+------------+
| made_at | log_id | sequence | is_restart |
+---------+--------+----------+------------+
| 1       | 206480 | 1        |            |
+---------+--------+----------+------------+
| 2       | 206480 | 1        |            |
+---------+--------+----------+------------+
| 3       | 206480 | 2        |            |
+---------+--------+----------+------------+
| 4       | 206480 | 3        |            |
+---------+--------+----------+------------+
| 5       | 206480 | 1        | 1          |
+---------+--------+----------+------------+
| 6       | 206480 | 2        |            |
+---------+--------+----------+------------+
| 7       | 206480 | 4        |            |
+---------+--------+----------+------------+
| 8       | 206480 | 5        |            |
+---------+--------+----------+------------+
| 9       | 206480 | 1        | 1          |
+---------+--------+----------+------------+
| 10      | 206480 | 2        |            |
+---------+--------+----------+------------+
| 11      | 206481 | 1        |            |
+---------+--------+----------+------------+
| 12      | 206481 | 2        |            |
+---------+--------+----------+------------+
| 13      | 206481 | 3        |            |
+---------+--------+----------+------------+
| 14      | 206481 | 4        |            |
+---------+--------+----------+------------+

Do "running counts" (similar to "Running totals") of restarts (is_restart).
Rows that belongs to the same group will have the same count (AKA restart_id).
The "Order by" in COUNT implies range between unbounded preceding and current row

select      log_id
           ,sequence
           ,count (is_restart) over
            (
                partition by    log_id
                order by        made_at
            ) as group_id

from        (...) l

+--------+----------+----------+
| log_id | sequence | group_id |
+--------+----------+----------+
| 206480 | 1        | 0        |
+--------+----------+----------+
| 206480 | 1        | 0        |
+--------+----------+----------+
| 206480 | 2        | 0        |
+--------+----------+----------+
| 206480 | 3        | 0        |
+--------+----------+----------+
| 206480 | 1        | 1        |
+--------+----------+----------+
| 206480 | 2        | 1        |
+--------+----------+----------+
| 206480 | 4        | 1        |
+--------+----------+----------+
| 206480 | 5        | 1        |
+--------+----------+----------+
| 206480 | 1        | 2        |
+--------+----------+----------+
| 206480 | 2        | 2        |
+--------+----------+----------+
| 206481 | 1        | 0        |
+--------+----------+----------+
| 206481 | 2        | 0        |
+--------+----------+----------+
| 206481 | 3        | 0        |
+--------+----------+----------+
| 206481 | 4        | 0        |
+--------+----------+----------+

Group by log_id and restart_id and aggregate sequence

select      log_id
           ,array_agg (sequence)

from       (...) l

group by    log_id
           ,restart_id

order by    log_id
           ,restart_id
;

+--------+-----------+
| log_id | array_agg |
+--------+-----------+
| 206480 | {1,1,2,3} |
+--------+-----------+
| 206480 | {1,2,4,5} |
+--------+-----------+
| 206480 | {1,2}     |
+--------+-----------+
| 206481 | {1,2,3,4} |
+--------+-----------+


Simulate DELETE CASCADE I need to delete many rows from one table persons, that is referenced by many other tables as FK.

If I had DELETE CASCADE, I could just delete the records from persons, and automatically delete from the other tables, but I don't like the idea.

I would like a script that takes as input a DELETE statement, and outputs all the DELETE statements that are needed. Ideally, it would tell me how many records would be deleted.

Example:

Input:

DELETE FROM persons WHERE person_id < 1000000

Output:

-- This would delete 124,345 records
DELETE FROM persons_addresses WHERE person_id < 1000000
-- This would delete 82,954 records
DELETE FROM persons_phone numbers WHERE person_id < 1000000
...
-- This would delete 999,999 records
DELETE FROM persons WHERE person_id < 1000000

I'm not aware of such tool, but you can easily create one your self (foreign-keys-in-a-sql-server-database):

SELECT C.TABLE_CATALOG [PKTABLE_QUALIFIER],
       C.TABLE_SCHEMA [PKTABLE_OWNER],
       C.TABLE_NAME [PKTABLE_NAME],
       KCU.COLUMN_NAME [PKCOLUMN_NAME],
       C2.TABLE_CATALOG [FKTABLE_QUALIFIER],
       C2.TABLE_SCHEMA [FKTABLE_OWNER],
       C2.TABLE_NAME [FKTABLE_NAME],
       KCU2.COLUMN_NAME [FKCOLUMN_NAME],
       RC.UPDATE_RULE,
       RC.DELETE_RULE,
       C.CONSTRAINT_NAME [FK_NAME],
       C2.CONSTRAINT_NAME [PK_NAME],
       CAST(7 AS SMALLINT) [DEFERRABILITY]
FROM   INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU
     ON C.CONSTRAINT_SCHEMA = KCU.CONSTRAINT_SCHEMA
    AND C.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
     ON C.CONSTRAINT_SCHEMA = RC.CONSTRAINT_SCHEMA
    AND C.CONSTRAINT_NAME = RC.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C2
     ON RC.UNIQUE_CONSTRAINT_SCHEMA = C2.CONSTRAINT_SCHEMA
    AND RC.UNIQUE_CONSTRAINT_NAME = C2.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KCU2
     ON C2.CONSTRAINT_SCHEMA = KCU2.CONSTRAINT_SCHEMA
    AND C2.CONSTRAINT_NAME = KCU2.CONSTRAINT_NAME
    AND KCU.ORDINAL_POSITION = KCU2.ORDINAL_POSITION
WHERE  C.CONSTRAINT_TYPE = 'FOREIGN KEY'
  AND C.TABLE_SCHEMA = ?
  AND C.TABLE_NAME = ?

will give you all the children of the root table. You can create a recursive CTE that computes all offspring to this table.

As for the number of rows affected, you can construct it like:

SELECT COUNT(1)
FROM (
    SELECT DISTINCT T.*
    FROM T
    WHERE EXISTS (
         SELECT 1
         FROM <JOIN ALL TABLES FROM PARENT OF T TO ROOT>
         WHERE ROOT.PREDICATE
    )
) AS X


Querying Change Data Capture data I have Change Data Capture enabled and working fine.  The question that I have is how do I query the data to show users what changes occurred.

You use built-in table-valued functions (TVFs) as described in Books Online : Using Change Data (MSDN).

First you need to find the LSNs you want to use to filter the ranges you want to query. These LSNs will represent points in the log which you can correlate to datetime values. You can do this using sys.fn_cdc_map_time_to_lsn. Let's say you want to report on all the change data captured today:

DECLARE
  @s DATE = SYSDATETIME(),                  -- start today at midnight
  @e DATE = DATEADD(DAY, 1, SYSDATETIME()), -- end tomorrow at midnight
  @sl BINARY(10),                           -- start LSN
  @el BINARY(10);                           -- end LSN

SELECT
  @sl = sys.fn_cdc_map_time_to_lsn('smallest greater than or equal', @s),
  @el = sys.fn_cdc_map_time_to_lsn('largest less than',              @e);

SELECT @sl, @el; -- start LSN and end LSN within today

Then you can use those LSN values in the cdc.fn_cdc_get_all_changes and cdc.fn_cdc_get_net_changes functions, depending on what you are after (let's say, for a table called dbo.floob):

SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_floob(@sl, @el, 'all');

SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_floob(@sl, @el, 'all');


Index rebuild - Not reducing fragmentation I have a table with nearly 18 million records and it has nearly 14 indexes including a clustered index.

One of the non-clustered index has a fragmentation level of 97%, so when I tried to rebuild the index - fragmentation level goes down to 96% or 94%. But, it is not reducing any further. Few details about the index,

Key columns: 2 (Includes clustered index column)

Col 1 - nvarchar(255) – Clustered index column
Col 2 - nchar(16)

Included columns: 2

Col 1 - smallint
Col 2 - smallint

Pages: 143539
Index size: 1.3 GB
Fill factor: 90
Row locks: True
Page locks: False
Pad index: False

Any ideas why fragmentation level is not reducing?

The issue could be not enough contiguous free space inside the database to arrange the index consecutively.  A work around to a very large index that won't defragment, one solution is to pre-size a new database and move all your objects to that DB, then recreate your indexes there as this will surely do the trick.


Mongo repair database "ERROR: error: exception cloning object in local.system.replset " I'm trying to repair a mongo database and am running into the following error early on:

Wed Jul 24 16:55:21 [initandlisten] ERROR: error: exception cloning object in local.system.replset
E11000 duplicate key error index: local.system.replset.$_id_  dup key: { : "replicaset-name" }
obj:{ _id: "replicaset-name", version: 92, members: [ { _id: 16, host: "host1:27032" },
 { _id: 17, host: "host2:27032" },
 { _id: 18, host: "host3:27032", priority: 0.0, hidden: true },
 { _id: 19, host: "host4:27032", priority: 6.0 },
 { _id: 20, host: "host5:27032", priority: 7.0 },
 { _id: 22, host: "host6:27032", priority: 4.0 },
 { _id: 23, host: "host7:27032", priority: 3.0 },
 { _id: 24, host: "host8:27032", votes: 0, priority: 0.0 } ] }

(replica set and host names have been sanitized for public posting)

To attempt the repair I shut down the mongod and started it up with

/usr/bin/mongod-2.2 -f /var/local/mongo/mongod.conf --nojournal --repair --repairpath /mnt/mongo_repair/

My mongod.conf consists of just two lines:

dbpath = /var/local/mongo/data/replicaset-name
port = 17000

The .conf normally did have a replSet declaration, but this was removed for the repair as I'm trying to run the repair on a secondary.  (Per the docs repairs can only be done on primaries or standalones; so need to make it look like a standalone temporarily).

Any idea what the problem could be?

ReplSet configuration exists on many other members of that replica set.  You can safely drop the local db collections (you can even drop the whole DB but then you will just end up syncing from another replica and there is no point to repairing this entire DB).  So drop the troublesome collection - it will be recreated when the host rejoins its replica set.  The only local collection you need to keep will be the oplog.rs collection - this would allow this member to know where to start catching up for operations it misses while you are doing repairs.


PostgreSQL: Can I do pg_start_backup() on a live, running db under load? Our established replication has broken ("requested WAL segment has already been removed" during downtime)
We cannot easily stop the master again.

Can we do

pg_start_backup(),
rsync ${PGDATA}/ master to slave,
pg_stop_backup()

... while the master postgresql is still under full load?
(Or will pg_start_backup() lead to

table locks,
I/O blocks,
inconsistencies,
fire alarm,
slow db response

In other words, will pg_start_backup() affect our application?

pg_start_backup will perform a checkpoint, as dezso notes. This does have an impact, but your database performs checkpoints quite regularly anyway, and must do so to function, so they're clearly not a problem for you. An early checkpoint means that less data has been accumulated, meaning that if anything a checkpoint from pg_start_backup will be lower-impact than normal.

Where you need to worry is the rsync or equivalent pg_basebackup step. The read I/O from this won't be too bad since it's sequential, but it'll still probably significantly hurt your database's I/O performance, and it'll also tend to push hot data out of RAM cache in favour of less-used data, causing cache thrashing as the more-needed data is then read back in.

You can use nice and ionice to help limit the I/O impact (but not the cache impact); however, there's a cost to that. The backup will take longer, and until you complete the backup and run pg_stop_backup your system is - as I understand it - accumulating WAL it cannot delete, accumulating checkpoint debt for a BIG checkpoint at the end of the backup run, and is accumulating table and index bloat because it can't clean up dead rows. So you really can't afford to have the backup take forever, especially if you have very high churn tables.

In the end, it's hard to say whether you can safely use pg_start_backup and pg_stop_backup for hot backups in your environment. Most people can, but if you're close to the edge of what your hardware can do, have tight timing requirements, cannot afford the risk of a stall, and have very high churn tables as well as very big tables, it might be troublesome.

Unfortunately, you pretty much need to test it and see.

If you can, it might be worth issuing a CHECKPOINT then taking an atomic snapshot of the volume your database is on instead using LVM, your SAN's tools, EBS, or whatever you're on. If you can do this, you can then copy the snapshot at your leisure. This approach isn't suitable for taking a base backup for PITR/warm standby/hot standby, but it's perfectly good for a static backup copy, and is much lower impact on the system. You can only do this if your snapshots are atomic and your entire database including WAL is on a single volume, though.

One possibility I haven't yet investigated is combining the two approaches. It occurs to me that one could possibly (untested and possibly wrong and unsafe, I don't know yet):

pg_start_backup
Trigger snapshots of all tablespaces, the main datadir, and the xlog volume
pg_stop_backup
Copy WAL up to the final archive from pg_stop_backup
Copy the data from the snapshotted volumes

Essentially, the idea is to reduce how long the DB has to be delaying its checkpoints by taking a point-in-time of each volume that you can copy at your leisure.


Can I check the services currently running through SSMS? I want to check which services has started and which has not by running a script/query on SSMS.

You can do this with CLR or PowerShell. From PowerShell you can use things like Get-Process or Get-Service for some of this information. With C# you could look at this answer but I don't know of any ready-made code for SQLCLR. Note that with PowerShell you'll need to run the command in the context of the remote computer, e.g.

Invoke-Command -ComputerName RemoteComputerName { Get-Service }

// or if your version of PS is modern enough:

Get-Service -ComputerName RemoteComputerName

You can also use xp_cmdshell to call things like tasklist, net start, wmic or 3rd-party things like Process Explorer (though I haven't tried to invoke that via command line):

EXEC master..xp_cmdshell 'tasklist';
EXEC master..xp_cmdshell 'net start';
EXEC master..xp_cmdshell 'wmic service get';

The latter has much more valuable information, but the output is absolutely horrible to work with.

Of course this all assumes that you and/or the SQL Server service account has sufficient privileges to call these functions.

(Adopted from my answer to a similar question on SO.)


Counting distinct entries in a column using relational algebra So I have a table similar to this one. Each user has posted a review about one or more hotels(A,B,C,D) but on different dates so there are no duplicate tuples even though a person might have reviewed the same hotel more than once.

I need to count the number of DISTINCT hotels every user has reviewed using RELATIONAL ALGEBRA only. How can I do that?

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1)

would give the number of reviews by each user

The result should be the following table:

example to show notations I use:

R = ƔUser,COUNT(Hotel_reviewed)->Num_Reviews (InitialRelation- table 1)

would give the number of reviews by each user

Besides the more compact syntax (from @McNets' answer):

select   User,
         count(distinct Hotel_Reviewed) HotelsReviewed
from     InitialRelation
group by User;

we can also do a projection first to find distinct User, Hotel_Reviewed pairs and then aggregate:

select   User,
         count(Hotel_Reviewed) as Hotels_Reviewed
from
    ( select distinct
               User,
               Hotel_Reviewed
      from     InitialRelation
    ) as D
group by User ;

This leads us to the relational algebra notation:

R = Ɣ User, COUNT(Hotel_Reviewed) -> Hotels_Reviewed
        (π User, Hotel_Reviewed (InitialRelation)) -> D


Failure during install of SQL Server 2016 sp2 I'm trying to install the new SP2 for SQL Server 2016, but it fails during the install with an Engine error.
This is what I can read in the errorlog>

Error: 468, Severity: 16, State: 9.
Cannot resolve the collation
conflict between "Latin1_General_CI_AS" and
"SQL_Latin1_General_CP1_CI_AS" in the equal to operation. The failed
batch of t-sql statements :
CREATE PROCEDURE
sp_sqlagent_get_perf_counters   @all_counters BIT = 0 AS BEGIN ...
Error: 912, Severity: 21, State: 2.
Script level upgrade for database 'master' failed because upgrade step
'msdb110_upgrade.sql' encountered error 200, state 7, severity 25.
This is a serious error condition which might interfere with regular
operation and the database will be taken offline. If the error
happened during upgrade of the 'master' database, it will prevent the
entire SQL Server instance from starting. Examine the previous
errorlog entries for errors, take the appropriate corrective actions
and re-start the database so that the script upgrade steps run to
completion.
Error: 3417, Severity: 21, State: 3.
Cannot recover the master database. SQL Server is unable to run.
Restore master from a full backup, repair it, or rebuild it. For more
information about how to rebuild the master database, see SQL Server
Books Online.
SQL Server shutdown has been initiated

The master database was running "Latin1_General_CI_AS" and MSDB (and other dbs) was running "SQL_Latin1_General_CP1_CI_AS".
I have new backups of all databases on the server including master and msdb.
I'm tempted to try to restore the master master as shown in https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/restore-the-master-database-transact-sql?view=sql-server-2017
But I'm trying ensure that it will actually work, and not just make the situation worse.
How do I get my SQL Server back up and running?
EDIT:
I've started the mssqlserver service like this:
NET START MSSQLSERVER /T902

This gets the server started, and I've looked into msdb110_upgrade.sql, and found quite a few missing "COLLATE DATABASE_DEFAULT"s which I've added.
But it is not taking my changes.

I've seen this error on other versions of SQL Server on a legacy build.

In my case, it was caused by the owner of a role or schema was not the default:
Something like db_datareader was authorised by a user account (they no longer have sysadmin)

How to recover?

Could not restore master because the master database version was lower than the binaries version
Created a new install with the correct version to get it running, then restored master to that, then copied master mdf back to the original install.

Unfortunately, I can't remember the exact details now.
It's the only time in 20 years of SQL Server I've...

seen a service pack install fail that stops SQL Server running
had to deal with a version mismatch on restore of master


sqldeveloper ORA-12505. TNS: listener does not currently know of SID given in connect descriptor There are similar questions asked and answered in this network related to the error message given in the question, however this problem is a little different from each.

I have installed oracle 12c in my ubuntu can successfully connect to my hr user with sqlplus hr/hr@pdborcl through command prompt.

oracle@ubuntu:~$ sqlplus hr/hr@pdborcl
SQL*Plus: Release 12.1.0.2.0 Production on Mon Nov 16 21:57:24 2015
Copyright (c) 1982, 2014, Oracle.  All rights reserved.
Last Successful login time: Mon Nov 16 2015 21:24:32 +05:45
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL>show user;
USER is "HR"

I am however trying to use sqldeveloper for ease. In the create connection option in sqldeveloper when I give the following credentials

I get the following error.

    ORA-12505. TNS: listener does not currently know of SID given in connect descriptor.

However connection with

Username: sys
Password: ******
SID:orcl

And same all other credentials work fine.

My tnsname.ora has

PDBORCL =
  (DESCRIPTION =
   (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
    (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = pdborcl)
   )
 )

ORCL =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
  (CONNECT_DATA =
    (SERVER = DEDICATED)
    (SERVICE_NAME = orcl)
  )
 )

You have to use Service Names to connect to Pluggable Databases in 12c.

It's just one of those things that can trip you up, I guess! SQL Plus connecting OK probably threw you.


SSMS cannot determine if remote computer is connected to domain I've recently set up a new server with an SQL instance running on it (2017), I am able to connect to the server remotely using SSMS, but when I try to add a new login, I receive the attached error. Both machines are on the same domain.

I have already done some prior research and opened up ports 5002, 5001, 135 and 88 in the below configuration.

I have already confirmed that it is an issue with the firewall, as turning off the domain firewall allows access.

Am I missing something?

In case it's relevant, the server is hosted on Azure

This is an issue with the picker for selecting accounts when it cannot connect to the SMB port (or if NetBIOS isn't enabled, but you state that it works if you disable the firewall).
You can either try to open port 445 on the firewall or create the login using T-SQL.
There used to be a Microsoft KB Article for older versions of Windows but I believe this still applies.


Does separating VARCHAR values based on size have a performance gain? I am working with a team that is trying to implement an EAV system. They have decided to split the attribute-value tables out by type and they are debating using different tables for different size ranges of varchar.

EX:

table_1 - up to varchar(10)
table_2 - varchar(11) TO varchar(500)
table_3 - varchar(501) TO varchar(MAX)

I have always been under the impression that varchar was only going to use the size that it needed.

Do you know if this is going to have any gains in performance and would it be worth the extra coding / logic that would be needed?

My gut says that any performance gain you get is unlikely to be worth the extra hassle (and potential for bugs ) resulting from needing to enforce the separation and perform multiple lookups in your application logic.

If you have a lot of small values and were only querying them and none of the rest you would see some performance gain as more rows would fit in each page so overall less pages will need to be processed in RAM or read from disk for a given query. As soon as you need all the properties in one go (or just a mix) this benefit is going to be blown out of the water by either needing to query multiple tables separately or via a UNION.

The only way to be sure of course is to rig up a reasonably realistic large dataset and run some performance tests against the arrangements you are considering. But I very much doubt you will see any change worth the extra complexity. If you data can be split in more logical ways (i.e. ways implied by your business logic) than I suggest you look into data partitioning, particularly if you can split the partitions over different drives. Whenever you find yourself contemplating a potentially complexity laden optimisation (including partitioning) always go back and reconsider your overall data structure and make sure it is not working against your business logic and check your hardware is sufficient for the load you expect - while it is not guaranteed you are likely to find much more significant gains through looking into these core areas instead.


SQL Server 2019 running 100% on only 1-2 cores with MAXDOP 8 to 14 Here is the background on this issue.
I am inserting slightly over 1 million records into a table from within a C++ program using ADO.  One row at a time is read in from flat file and converted info a format that ADO will accept to add/update into SQL Server.
Now before you ask, this is a legacy application and it will not be converted to .NET per the clients request.
I have an i7-5960x CPU running/overclocked to 4.4GHZ with 32GB memory and no spinning disk, all Samsung SSD.
I have SQL Server 2019 (Dev Edition) with a MAXDOP of 8 on a system with 8 cores and 8 logical cores.
When running the Add/Update process I have a total CPU use of 10%-20% with 1-2 cores running at 100% (Memory use is 10G out of 32G) causing the Add/Update process to slow processing drastically. SQL Server is showing 132 threads and using 75-80% of the total CPU use. Even if I increase the MAXDOP to 14 it doesn't stop SQL Server from using 100% of any of the CPU cores.
If I comment out the function to write the data to SQL Server the app will process the data from a flat file (37 fields 387 chars total) to the format needed to add/update the data for SQL Server the function will process at least 10,000 records a second. When I run the process to write to SQL Server it will start out processing/writing about 600 records a second to SQL Server then it slowly degrades to about 220 records per second after about 100,000 records.
So the question is, can I restrict SQL Server to a max use on a per core (Not total CPU use) basis or force it to spread the threads/processing over all MAXDOP cores. When it does max out one core it will do it for about 5 minutes then switch to another core and max it out at 100% and the previous core will drop to almost nothing.
Thanks for reading this so far, if you can offer any help it would be really appreciated.

One row at a time is read in from flat file and converted info a format that ADO will accept to add/update into SQL Server.

You are going to have a very hard time leveraging multiple SQL Server cores with this approach.  Each single-row insert will be executed by one core, sequentially.  And each insert is very likely to keep using the same core, because each insert will be coming from the same connection.  From Microsoft SQL Server 2012 Internals by Kalen Delaney, et al:

Each SPID has a preferred scheduler, which is the one that most recently processed a request from the SPID.

If it's not a lot of trouble, you might get a "quick win" improvement in throughput by splitting the file into multiple files, and then having multiple instances of your C++ application loading each file concurrently.  Of course, you might run into other bottlenecks there, but it's worth a shot.
A side note: Dan Guzman mentioned:

The slowly degrading performance is symptomatic that the add/update query is performing a scan. Index/query tuning may avoid that and provide more linear and better performance overall.

If your table has additional indexes or foreign key / unique constraints, SQL Server will have to check more and more data as you load the table.  You could optimize to get seeks for these checks, or load an unindexed heap staging table first with your app, and then have an additional step that moves data to the real table using T-SQL.

Overall, to leverage more cores, you need to improve the concurrency of your loading application.  Either by using bulk loading APIs, or inserting from multiple clients concurrently. For more stable throughput, you'll need to avoid costly table scans, possibly by inserting into a staging table rather than your app table directly.


Get all distinct values of specific column in all tables in a given schema (Postgres) I have a schema in which all tables have the same basic set of columns. Now I want to query a specific column in all existing tables dynamically to get the distinct list of values of that column. The result should be a list consisting of all "table_name" and "unique_value" combinations (of that queried column in that table).
The result should be this (with the given test set below):
table_name | query_me
-------------------------
sql_test_1 | a
sql_test_1 | b
sql_test_1 | c
sql_test_2 | a
sql_test_2 | f
sql_test_2 | g

Important: I do not want to hard-code the table names I will query, I really want to query "all existing tables (with a given name scheme)".
I unfortunately have absolutely no clue how to do this in (Postgre)SQL. Can somebody help?
test case
CREATE TABLE IF NOT EXISTS public.sql_test_1 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);
CREATE TABLE IF NOT EXISTS public.sql_test_2 (
    id serial PRIMARY KEY,
    query_me varchar(10) NOT NULL,
    val TEXT
);

INSERT
    INTO
    public.sql_test_1 (query_me, val)
VALUES
    ('a', 'hey'),
    ('b', 'ho'),
    ('c', 'silver'),
    ('a', 'let''s'),
    ('b', 'ride');

INSERT
    INTO
    public.sql_test_2 (query_me, val)
VALUES
    ('a', 'hey'),
    ('f', 'ho'),
    ('g', 'silver'),
    ('a', 'let''s'),
    ('f', 'ride');

expected result see above.

You can use query_to_xml() to dynamically run a SQL statement and get the result as an XML string. Using XMLTABLE this can then be converted back into rows and columns
with queries as (
  select format('select distinct %L as table_name, %I as col from %I.%I', c.table_name, c.column_name, c.table_schema, c.table_name) as sql
  from information_schema.columns c
  where c.table_schema = 'public'
    and c.column_name = 'query_me'
), results as (
  select query_to_xml(q.sql, true, false, '')  as data
  from queries q
)
select x.*
from results r
  cross join xmltable('/table/row' passing r.data
                       columns table_name text path 'table_name',
                               value      text path 'col') as x
order by x.table_name

The first CTE returns something like this:
select distinct 'sql_test_1' as table_name, query_me as col from public.sql_test_1
select distinct 'sql_test_2' as table_name, query_me as col from public.sql_test_2

The second CTE then runs each of those queries and returns the result as XML. Something like this:
<table xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<row>
  <table_name>sql_test_1</table_name>
  <col>c</col>
</row>

<row>
  <table_name>sql_test_1</table_name>
  <col>a</col>
</row>

<row>
  <table_name>sql_test_1</table_name>
  <col>b</col>
</row>

</table>

And the final step then extracts the values from the generated XML and returns this based on your sample data:
table_name | value
-----------+------
sql_test_1 | c
sql_test_1 | a
sql_test_1 | b
sql_test_2 | g
sql_test_2 | f
sql_test_2 | a

Online example


sqlservr.exe uses lots of CPU, while SQL queries does not seem to use much CPU at all I've found a Query which supposedely should determine how much CPU is being used by SQL queries, system idle and other processes. This is the Query:

DECLARE @ts_now bigint = (SELECT cpu_ticks/(cpu_ticks/ms_ticks)FROM sys.dm_os_sys_info);

SELECT TOP(30) SQLProcessUtilization AS [SQL Server Process CPU Utilization],
               SystemIdle AS [System Idle Process],
               100 - SystemIdle - SQLProcessUtilization AS [Other Process CPU Utilization],
               DATEADD(ms, -1 * (@ts_now - [timestamp]), GETDATE()) AS [Event Time]
FROM (
      SELECT record.value('(./Record/@id)[1]', 'int') AS record_id,
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/SystemIdle)[1]', 'int')
            AS [SystemIdle],
            record.value('(./Record/SchedulerMonitorEvent/SystemHealth/ProcessUtilization)[1]',
            'int')
            AS [SQLProcessUtilization], [timestamp]
      FROM (
            SELECT [timestamp], CONVERT(xml, record) AS [record]
            FROM sys.dm_os_ring_buffers
            WHERE ring_buffer_type = N'RING_BUFFER_SCHEDULER_MONITOR'
            AND record LIKE '%<SystemHealth>%') AS x
      ) AS y
where SystemIdle >= 80
ORDER BY record_id DESC;

This shows that the CPU under the column SQL Server Process CPU Utilization is rather stable at around 0-10%.

When I'm checking the process task at the server, however, I can see that the CPU usage is very high at 90-100% from time to time. When I check what processes are using the most CPU in the task manager, I can conclude that sqlservr.exe uses much more CPU than the other processes (75+%).

In fact, there are more than one sqlservr.exe present.

My question is, really, how can the SQL queries use Little CPU, while sqlservr.exe uses very much?

I have some queries running in the background, by for instance using SQL jobs, but these should be captured in the CPU Query.

You have multiple instances of SQL Server running, and I suspect you are checking the wrong one for CPU activity.

In this system, I have two instances of SQL Server. In the plain Task Manager view I just see multiple SQL Server processes:

But if I expand each item, I can see the actual instance name involved with each:

In older versions of Task Manager you should be able to add process id (or inspect the user associated with the process):

If you can't identify the instance by name there, then you could look up the process id in Configuration Manager:

This makes it easy - in my case I know that the S1TARGET instance is the one that's using the CPU, so that's where I'm going to run queries like the one in the question.


how to solve this warning in select I have a problem with this query:

SELECT *
FROM Sales.SalesOrderDetail sod
JOIN Sales.SalesOrderHeader soh
ON sod.SalesOrderID = soh.SalesOrderID--- adventureworks db

  Warnings

  Type conversion in expression
  (CONVERT(nvarchar(23),[soh].[SalesOrderID],0)) may affect
  "CardinalityEstimate" in query plan choice

There's nothing to fix, really.

The message refers to this computed column in the Sales.SalesOrderHeader table:

[SalesOrderNumber]  AS (isnull(N'SO'+CONVERT([nvarchar](23),[SalesOrderID]),N'*** ERROR ***')),

The warning is informational in this case, to be sure you're aware in case it was a problem, as discussed in this Q&A: Type conversion in expression may affect “CardinalityEstimate” - on a computed column?


SQL Server Select in XML field I need a help with the situation below:

In my table SQLServer 2012 have a field with xml values, I would like to make a select of the data in that field and show the result in the form columns.

<row>
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>

Thank you.

Use CROSS APPLY and the .nodes method of the XML data-type, eg something like this:

IF OBJECT_ID('dbo.yourTable') IS NOT NULL DROP TABLE dbo.yourTable
GO
CREATE TABLE dbo.yourTable (
    rowId INT IDENTITY PRIMARY KEY,
    ST_Registro_Del XML
)
GO

INSERT INTO dbo.yourTable ( ST_Registro_Del )
SELECT '<row>
  <ID_Cota>162986</ID_Cota>
  <ID_Taxa_Plano>1000</ID_Taxa_Plano>
  <ID_Plano_Venda>1020</ID_Plano_Venda>
  <ID_Pessoa>18522</ID_Pessoa>
</row>'
GO

SELECT
    r.c.value('(ID_Cota/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Taxa_Plano/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Plano_Venda/text())[1]', 'INT' ) AS ID_Cota,
    r.c.value('(ID_Pessoa/text())[1]', 'INT' ) AS ID_Cota
FROM dbo.yourTable yt
    CROSS APPLY yt.ST_Registro_Del.nodes('row') r(c)
WHERE rowId = 1

Here are my results:

The .nodes method returns a resultset from XML.  Use the text() accessor for a performance boost when querying untyped XML ( ie no XSD is associated with the XML column and the [1] ordinal, which is a shorthand way of saying "get me the first element you find".

HTH


One MySQL Table keeps losing auto-increment after restart We are trying to figure out why this one table keeps losing auto-increment value, thus giving an error message: Failed to read auto-increment value from storage engine.

The auto ID column has type BIGINT(20), and table has about 140K records.
There are other tables that also use auto increment IDs and have more records (1M to 5M records).

It started a few month ago, that every time the service restarts, the same table loses the auto-increment, and we had to manually reset.

The MySQL server is of version 5.7, and is running on windows2012.

Where should we look into to fix it?  Or should we rebuild the table?

Thanks

Here is the create table;

CREATE TABLE `notification` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `subject` varchar(400) NOT NULL,
  `category` varchar(200) DEFAULT NULL,
  `created_by` bigint(20) DEFAULT NULL,
  `created_date` datetime NOT NULL,
  `priority` int(11) NOT NULL DEFAULT '2',
  `mime_type` varchar(45) DEFAULT NULL,
  `attachment` varchar(500) DEFAULT NULL,
  PRIMARY KEY (`id`)
  FULLTEXT KEY `ix_notification_subject` (`subject`)
) ENGINE=InnoDB AUTO_INCREMENT=141570 DEFAULT CHARSET=utf8;

Try inserting dummy ID

select max(ID) fromtable1into @IDmax;
set @IDmax = @IDmax + 1;
insert intotable1setID= @IDmax;
delete fromtable1whereID= @IDmax;

This bug usually when trying to insert a duplicated value on a key.


How to Add A User To A Database In Standby / Read-Only Mode In Sql Server I have a database which is in standby / read-only mode in my reporting server. But, I can not add a new user to this database. How can i add a new user to this database?

Check if the database is part of logshipping. As the database in secondary server will be in Read-only/standby state. ALtering the database directly will not be allowed.

Identify the primary server and add the user there. This will move to your read-only secondary.

Edit : 1

Make sure the logins exist in both the servers.


How to rollback transaction if row changed? I am using Postgres 9.3.

When I update row in a table my flow looks like this:

Fetch old row (ex. SELECT * FROM tbl WHERE id = 1)
Validate new data in scope of old data (old row fetched in point 1) - this is done by my app outside of postgres.
Update row with new data (ex. UPDATE tbl SET .... WHERE id = 1)

The problem is that old data may change during point 2 so validation, even if passed may be outdated during point 3 (updating row). I know I could perform update like this UPDATE .... WHERE id = 1 AND column1='oldValue' to ensure certain fields didn't change, however my validation (also comparising new and old values) is very complex and I can't "write" it in SQL UPDATE statement.

So I thought about something like this:

BEGIN
SELECT * FROM tbl WHERE id = 1
Perform validation on my side
UPDATE SET=... WHERE id = 1
COMMIT

And I would like to COMMIT fail if row with id = 1 changed during transaction execution. Is it possible to use transaction like this? If not what is other solution?

Not sure why you are not updating directly with additional predicates in the where clause, but if that is not possible you can select for update as in:

SELECT * FROM tbl WHERE id = 1 FOR UPDATE

This will lock the selected row and prevent updates of it while you do your validation.


Internals of backup compression with TDE (SQL Server) Does anyone have any (inside?) knowledge of how backup compression works with TDE on SQL Server (version 2016+).

In general I would expect compression of encrypted data to have minimal benefit, however we notice that with TDE, when we do a full back up with compression we still see a significant reduction in the backup file size. That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk. Obviously that is going to increase the CPU consumption for the backup process due to the need to decrypt and re-encrypt data.

Details

select @@version

  Microsoft SQL Server 2017 (RTM) - 14.0.1000.169 (X64) Aug 22 2017 17:04:49 Copyright (C) 2017 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Enterprise 10.0  (Build 17763: ) (Hypervisor)'

TSQL Scipt

  BACKUP DATABASE TestTDE TO DISK = 'c:\Test\TestTDE_Compressed.bak' WITH COMPRESSION, MAXTRANSFERSIZE = 65537

Or is the space saving achieved in a different way?

That has made us wonder whether the backup process decrypts the data, compresses it, encrypts that result, then backs up that to disk.

This is exactly how space saving is achieved. When backup process run page is decrypted and loaded into memory, then compressed and then encrypted and saved onto disk. The process is mentioned in Backup TSQL Doc

  Starting with SQL Server 2016 (13.x), this enables an optimized compression algorithm for TDE encrypted databases that first decrypts a page, compresses it and then encrypts it again.

I am assuming here that since you are getting good mileage with backup compression on TDE database you must be using SQL Server 2016 patched to latest SP and CU.
I strongly suggest you to read Backup Compression for TDE Enabled Database

Since you are using SQL Server 2017 and your TSQL script uses MAXTRANSFERSIZE> 65536 you will get benefit of backup compression for TDE enabled database. But I strongly suggest you to patch SQL Server 2017 to latest CU


Automatic create SQL database and table relationship For SQL Server 2008, what is the best way to automatically "draw up" the Database relation ship and table relationship too?

Is there any way to visualize database with this.

You could use database diagrams, drag and drop your tables and use the designer to create the relationships for you. Open SSMS, expand the server, expand Databases, expand the database you want and it should be the first folder. If it asks to install things, say yes and away you go.


Keeping MySQL staging database on same server updated from production I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly mysqlhotcopy to overwrite staging from production and all was well. However, RDS doesn't give me shell access so I think I need a pure SQL solution to avoid doing a mysqldump to another server, and then restoring back to the RDS server, which seems unnecessary since I just want to copy to database-to-database on the same server. I know RDS supports read replicas, but I need a writable replica, and I don't want to have to fire up another instance for my staging database, which doesn't see a lot of action.

What's the best way to accomplish a database-to-database copy on the same server in pure SQL?

I can see mysqldump being a little pain in the neck because of transaporting data out and then in.

Let's say you are copying data from proddb to stagedb. Here is a query to create the script for lateral copying of every table:

select
concat
(
    'drop table if exists ',db2,'.',tbl,'; ',
    'create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ',
    'insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';'
)
from (select table_schema db1,table_name tbl
from information_schema.tables where table_schema='stack_dev') tbls,
(select 'stack_test' db2) dbname;

Just generate the script as a SQL text file and import the text file to the target server:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" > /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

This should work just fine for MyISAM. It should also work for InnoDB if there are no constraints. If there are constraints, you may have to disable them for the import session:

PROD_IP-10.1.2.20
STAG_IP=10.1.2.40
echo "SET FOREIGN_KEY_CHECKS=0;" > /root/CopyFromProdToStage.sql
mysql -h${PROD_IP} -uusername -p --skip-column-names -A -e"select concat('drop table if exists ',db2,'.',tbl,'; ','create table ',db2,'.',tbl,' like ',db1,'.',tbl,'; ','insert into  ',db2,'.',tbl,' select * from ',db1,'.',tbl,';') from (select table_schema db1,table_name tbl from information_schema.tables where table_schema='stack_dev') tbls,(select 'stack_test' db2) dbname;" >> /root/CopyFromProdToStage.sql
mysql -h${STAG_IP} -uusername -p -A < /root/CopyFromProdToStage.sql

UPDATE 2012-03-09 17:14 EST

I have a stored procedure that will accomplish this. Run the following only once:

DROP DATABASE IF EXISTS utility;
CREATE DATABASE utility;
DELIMITER $$
DROP PROCEDURE IF EXISTS `utility`.`CopyDB` $$
CREATE PROCEDURE `utility`.`CopyDB` (sourceDB VARCHAR(64),targetDB VARCHAR(64))
TheStoredProcedure:BEGIN

    DECLARE found_count,ndx,ndx_last INT;
    DECLARE sqlcmd VARCHAR(1024);

    SELECT COUNT(1) INTO found_count
    FROM information_schema.tables
    WHERE table_schema = sourceDB;
    IF found_count = 0 THEN
        LEAVE TheStoredProcedure;
    END IF;

    DROP TABLE IF EXISTS DBTablesToCopy;
    CREATE TABLE DBTablesToCopy
    (
        id INT NOT NULL AUTO_INCREMENT,
        src  VARCHAR(64),
        tgt  VARCHAR(64),
        tbl  VARCHAR(64),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;
    DROP TABLE IF EXISTS SQLScriptToCopyTables;
    CREATE TABLE SQLScriptToCopyTables
    (
        id INT NOT NULL AUTO_INCREMENT,
        sqltext VARCHAR(1024),
        PRIMARY KEY (id)
    ) ENGINE=MyISAM;

    INSERT INTO DBTablesToCopy (src,tgt,tbl)
    SELECT sourceDB,targetDB,table_name
    FROM information_schema.tables
    WHERE table_schema = sourceDB;

    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('DROP DATABASE IF EXISTS ',targetDB));
    INSERT INTO SQLScriptToCopyTables (sqltext) VALUES
    (CONCAT('CREATE DATABASE ',targetDB));
    SELECT MAX(id) INTO ndx_last FROM DBTablesToCopy;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('CREATE TABLE ',tgt,'.',tbl,' LIKE ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        INSERT INTO SQLScriptToCopyTables (sqltext)
        SELECT CONCAT('INSERT INTO ',tgt,'.',tbl,' SELECT * FROM ',src,'.',tbl)
        FROM DBTablesToCopy WHERE id = ndx;
    END WHILE;

    SELECT MAX(id) INTO ndx_last FROM SQLScriptToCopyTables;
    SET ndx = 0;
    WHILE ndx < ndx_last DO
        SET ndx = ndx + 1;
        SELECT sqltext INTO @stmt FROM SQLScriptToCopyTables WHERE id = ndx;
        PREPARE s1 FROM @stmt;
        EXECUTE s1;
        DEALLOCATE PREPARE s1;
    END WHILE;

END $$
DELIMITER ;

then anytime you feel like copying stack_dev to stack_test, do this:

call utility.copydb('stack_dev','stack_test');

and that's all.

Give it a Try !!!


SQL Server : log folder too large Please note that I'm not talking about transaction logs. I'm referring to a folder within SQL Server called 'Log' where ErrorLog and SQLDrmp files exists.

Well, the fact is that this folder is growing by 8GB a day on a machine dev machine that has 250gb total storage. That's a problem.

I figured out that I can create a new folder called Log2, run sp_cycle_errorlog get the new "errorlog" copy to this new folder, stop SQL Server (and agent) switch the folders, start SQL Server (and agent) again, and delete the old folder.

Ideally I would like to disable this kind of logging but if not possible, is there a way to automatically clean it? I mean, discard the logs perhaps?

It is recommended that you recycle your error log frequently. You can set that up as sql agent job to do that. I do this every night at midnight but you need to decide what is suitable for your environment.

System stored procedure "EXEC sp_cycle_errorlog" will do the recycling for you.

Once you have recycle set up you can decide how many files you want to keep (meaning how many days of error log you want to retain).

Expand the “Management” folder in SSMS.
Right click on “SQL Server Logs”
Select “Configure”
Check the box “Limit the number of error log files before they are
recycled”
Pick some value to put in the “Maximum number of error log files”
box
Click “OK”
List was copied and edited from here.


SSRS View Report: Change View Report Button Text Is there anyway to change the View Report Button in SSRS to say something else. Such as "Hello", or "See Data". Is there a place in the SSRS XML RDL, I can edit?

You could modify the button text through javascript if you edit C:\Program Files\Microsoft SQL Server\MSSQL.n\Reporting Services\ReportManager\js\ReportingServices.js

See this blog post

Just define a function using the DOM to replace the button text and add it to the addLoadEvent


schedule join multiple tables Basically I need an scalable efficient way to select all scheduled items so it made sense that the main table is the schedule. The problem is that all the operation tables have different properties

I created a diagram which is obviously wrong but demonstrates what I am trying to achieve.

I was thinking about having the join on the other side so that all three operations pointed to the schedule, but if I do that then to get a complete schedule I have to aggregate all the operation tables. What if I have loads more in the future?

Another option was to have the delivery, maintenance, loading columns in the schedule table but again there could be loads more operations. I'm just wondering of I am missing something or is this the best option?

You can have two fields in schedule table:

operation_type
operation_id

Then you can select everything like this:

SELECT s.*, l.weight, d.driver, d.fuel, mo.mechanic, mo.parts
  FROM schedule AS s
  LEFT JOIN loading_operation AS l ON s.operation_id=l.id
                                  AND s.operation_type='l'
  LEFT JOIN delivery_operation AS d ON s.operation_id=d.id
                                  AND s.operation_type='d'
  LEFT JOIN maintenance_operation AS mo ON s.operation_id=mo.id
                                  AND s.operation_type='m';

and the the output of this query would be something like:

id  date_from  date_to    operation_type operation_id weight driver fuel mechanic
 1 2019-02-06 2019-03-14       m            5                               874
 2 2019-01-16 2019-02-24       d            3                 431     838
 3 2019-03-16 2019-03-03       m            5                               874
 4 2018-12-16 2019-02-11       m            2                               975
 5 2019-02-12 2019-03-03       d            1                 825     438
 6 2019-02-12 2019-03-14       d            7                 604     534
 7 2019-01-11 2019-02-25       l            9          304
 8 2019-03-15 2019-02-14       m            8                               188
 9 2019-03-05 2007-04-20       m            2                               975
10 2019-02-25 2007-04-22       m            5                               874


Benefit of using WITH (TABLOCK, XLOCK) when inserting one line in a table? I found in some legacy code (originally SQLServer 2008) INSERT hints that I'm not sure to understand.

I have a table (storing many rows) and some procedures to fill this table.
We insert rows one by one. One procedure call = one line inserted.
Fine.

Sometimes (not always) SQL code looks like:

insert into my_table with(tablock, xlock)
values(......)

Why tablock / xlock have been put here ?

What could be the benefit of locking a table when we insert only one line ?

Note this insert is embedded within a transaction with isolation serializable.

(I can imagine the benefit if we want to insert a bunch of records, ie. better performance).

Exclusive Access or Increased Performance

From what I've seen, this "could be" someone's attempt to make sure that absolutely no one else enters identical rows, that you're inserting, into the table.

Or if it's mass inserts, it can save time and have minimal logging.

https://drillchina.wordpress.com/2012/12/18/use-tablock-to-boost-your-insert-into-select-performance/


Full-text search does not work if query begins from 0 I have a table with users and their addresses. Some flats looks like "p06"

This query does not find anything:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +06' IN BOOLEAN MODE);

This query finds "Washington street, h.36, fl.p06":

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +p06' IN BOOLEAN MODE);

But this query finds only Washington street, h.36, fl.6:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +6' IN BOOLEAN MODE);

This query works well and finds Washington street, h.36, fl.16:

SELECT username, address FROM base WHERE MATCH (username,address) AGAINST ('+Wash* +36 +16' IN BOOLEAN MODE);

p.s. ft_min_word_len = 1

I wish to get all results by query "+Wash* +36 +6". And find "Washington street, h.36, fl.p06" by query "+Wash* +36 +06"

Sure it could be wrong queries for my task

Are you using MyISAM or InnoDB?

What is the min-word-length?

In InnoDB, with innodb_ft_min_token_size > 2, the +xx leads to finding nothing.  Here's the logic:  + says that it must find the the word xx.  However the token size says that the "word" xx is not indexed.

Note:  I think MyISAM was more lenient.

One workaround:  When building the AGAINST string, don't prepend + to any 'word' shorter than min-token-size.  (I actually have an app that uses this trick; I am happy with the results.)

*06 does not work in FULLTEXT

Under no circumstances can you get FULLTEXT to find pxx by searching for xx.

The 0 is irrelevant.  (If it is relevant, please rephrase the Question with a suitable example.)


"Lock request time out period exceeded" when publishing SqlServer databases in parallel I am working on a continuous integration environment using SqlServer2014. There I need to publish many SqlServer databases simultaneously on the same instance. Our publish process uses a .sqlproj file executed by MSBuild, which generates a publishing script.

Sometimes, during this publishing process we got an error: "Lock request time out period exceeded". It happens when two or more publishing scripts are being executed in parallel, more specifically on the procedure sp_fulltext_database.

Searching for further explanations, I found out that the default lock timeout limit is -1, which is unlimited. Even so, the error keep showing. Trying to change the lock timeout limit to 20 seconds through the query:

SET LOCK_TIMEOUT 20000;

The error did not happen again.

My question is: Is this the only way to solve the problem? Changing the LOCK_TIMEOUT? Since the publishing process is done by a auto-generated script, I do not think it is a good idea to change the script manually.

Edit:
Using the SqlServer profiler during a deploy I found out that something is changing the lock timeout to 5 seconds. This command does not exists in my publishing script, so I assume that an internal command or configuration is doing it. Going to Sql Server Management Studio and right-clicking on the server > properties > advanced > parallelism I found some configurations, but they do not seem to affect this automatic definition of 5 seconds.

Looking at the MSDN documentation for sp_fulltext_database we see the following note:

  Has no effect on full-text catalogs in SQL Server 2008 and later versions and is supported for backward compatibility only. sp_fulltext_database does not disable the Full-Text Engine for a given database. All user-created databases in SQL Server 20xx are always enabled for full-text indexing.

The "xx" in the "20xx" above changes based on what version of the documentation you are looking at, but starting with SQL Server 2008, that xx will be: "08", "12", or "16".

You are on SQL Server 2014, so I am questioning why EXECUTE sp_fulltext_database 'disable'; is even showing up in your SSDT-generated deploy scripts. I just did some testing and it seems that no matter what the project's "Target platform" is set to, the deploy script always generates lines for:

IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';

The "enable" or "disable" is controlled by the "Database Setting..." on the "Project Settings" tab of Project Properties, in the "Miscellaneous" tab.

The only way to get rid of these 2 lines is to uncheck the "Deploy database properties" option, under "Deployment Options" on the "Debug" tab of Project Properties. But if you uncheck that option, then it won't enforce any of the database properties. So:

If you are not using the SSDT deployment to enforce the database properties then go ahead and uncheck this option. If you haven't made any other changes since the last build, this change alone will not update the deploy script, in which case you need to do a "rebuild".
If you are using the SSDT deployment to enforce the database properties, then either remove those two lines manually or find a way to do it programmatically.

I don't know why there is a call to an unused system stored procedure outside of it probably being a low priority to remove since it doesn't break anything and most people aren't doing parallel deployments ;-).


How can I assign group IDs depending on content? I have the following table with data as shown below; linenrs are monotonically increasing but not necessarily consecutive; when the key field contains an ellipsis ... that indicates an entry that is continued from above:

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

Now I want to have a view where a group number is assigned based on the content of the key field; group numbers do not have to be consecutive but should be distinct for each group of rows that starts with a key other than ... and continues thru all lines where key is ..., like this:

╔════════╤═══════╤═══════╤════════════╗
║ linenr │ group │  key  │   value    ║
╠════════╪═══════╪═══════╪════════════╣
║      2 │     1 │ tags  │ a          ║
║      3 │     1 │ ...   │ b          ║
║      4 │     2 │ title │ The Title  ║
║      5 │     3 │ note  │ this is    ║
║      6 │     3 │ ...   │ an EXAMPLE ║
║      8 │     4 │ title │ over       ║
║      9 │     4 │ ...   │ three      ║
║     10 │     4 │ ...   │ lines      ║
║     11 │     5 │ about │ grouping   ║
╚════════╧═══════╧═══════╧════════════╝

I tried to do this with windows / partitions and the tabibitosan pattern, but haven't been able to come up with anything working; also, using at the preceding row with lag() doesn't help where there are multiple consecutive lines with .... In a spreadsheet this is an extremely easy thing to do but in SQL I can seemingly not refer to the previous row of the current query, can I?

create table source (
  linenr    integer unique not null,
  key       text not null,
  value     text );

insert into source values
  (  2, 'tags',  'a'          ),
  (  3, '...',   'b'          ),
  (  4, 'title', 'The Title'  ),
  (  5, 'note',  'this is'    ),
  (  6, '...',   'an EXAMPLE' ),
  (  8, 'title', 'over'       ),
  (  9, '...',   'three'      ),
  ( 10, '...',   'lines'      ),
  ( 11, 'about', 'grouping'   );

You can get it using a simple CASE.

select linenr, key, value,
       case when key <> '...' then 1 end as rst
from   source;

linenr | key   | value      |  rst
-----: | :---- | :--------- | ---:
     2 | tags  | a          |    1
     3 | ...   | b          | null
     4 | title | The Title  |    1
     5 | note  | this is    |    1
     6 | ...   | an EXAMPLE | null
     8 | title | over       |    1
     9 | ...   | three      | null
    10 | ...   | lines      | null
    11 | about | grouping   |    1

Now SUM(rst) ignores null values and do the trick.

select linenr, key, value,
       sum(rst) over (order by linenr) grp
from (
      select linenr, key, value,
             case when key <> '...' then 1 end as rst
      from   source
     ) x;

linenr | key   | value      | grp
-----: | :---- | :--------- | --:
     2 | tags  | a          |   1
     3 | ...   | b          |   1
     4 | title | The Title  |   2
     5 | note  | this is    |   3
     6 | ...   | an EXAMPLE |   3
     8 | title | over       |   4
     9 | ...   | three      |   4
    10 | ...   | lines      |   4
    11 | about | grouping   |   5

dbfiddle here


Updating dbi_dbccLastKnownGood I have a VLDB and I offload the DBCC CHECKDB to another server.  Is it possible to update the dbi_dbccLastKnownGood on the VLDB server so it passes any scripts that check that date like sp_Blitz?

No, this feature was put in to record the last time that DBCC CHECKDB ran successfully. It would be extremely unlikely that a feature would be added that allows this value to be edited at will and effectively lie about the history.

The only way to update this that does not actually involve hacking the database boot page is to execute DBCC CHECKDB.

So you should customise these scripts to not report that issue or ignore this result if it is entirely expected that this database isn't being checked due to alternate arrangements.


How did you split strings before string_split() I have some strings I need to split on a SQL Server 2014 instance.

I would normally do it something like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

select value from string_split(@bk, '~')

and I can also do it like this:

declare @bk nvarchar(1000)

set @bk = 'SIS5.0~person_id~696969'

;with hizizzle as (
select left(@bk, charindex('~', @bk)-1) as flippity, right(@bk, len(@bk) - charindex('~', @bk)) as floppity)

select flippity, left(floppity, charindex('~',floppity)-1) as floppity,
right(floppity, len(floppity) - charindex('~', floppity)) as flooo
from hizizzle

...but there must be a better way, right?

Question: How did you do string splits in the ancient past before 2014?

From comments:

  The built-in STRING_SPLIT() function was added in SQL Server 2016, not 2014. And for me, I go the SQLCLR route because I am not using Azure Single DB or AWS RDS and thus have no reason not to. I use the SQLCLR library that I created, SQL#, which contains String_Split and String_SplitInts (optimized for splitting a list of BIGINT / INT / SMALLINT / TINYINT values). The "4k" versions of both are to be used when the input string is guaranteed to never be more than NVARCHAR(4000) as they are faster than the non-"4k" versions. – Solomon Rutzky

  there were lots of User-Defined Table-Valued functions floating around that did this. Here's a survey article by Aaron Bertrand: sqlperformance.com/2012/07/t-sql-queries/split-strings – David Browne - Microsoft

  this is probably the most efficient by Jeff Moden sqlservercentral.com/articles/… – Squirrel


Unable to generate a MySQL query I have a table in a MySQL database as below:

ID  Timestamp           State
1   2013-07-10 09:00:00 Good
1   2013-07-10 09:10:00 Good
1   2013-07-10 09:20:00 Good
1   2013-07-10 09:30:00 Bad
1   2013-07-10 09:40:00 Bad
1   2013-07-10 09:50:00 Bad
1   2013-07-10 10:00:00 Good
1   2013-07-10 10:10:00 Good

I want to generate a report as shown below using the above table.

ID  Start Timestamp         End Timestamp        Duration(in mins)  State
1   2013-07-10 09:00:00 2013-07-10 09:30:00 30          Good
1   2013-07-10 09:30:00 2013-07-10 10:00:00 30          Bad
1   2013-07-10 10:00:00 2013-07-10 10:10:00 10          Good

The query that I had generated is not able to showcase the switching taking place.

One way to do this is using user variables. The following code will sort through the table  (first in reverse) and then through this result set (grouping and) keeping only rows that are wanted:

SELECT
    id,
    MIN(time_stamp) AS start_timestamp,
    start_ts        AS end_timestamp,
    TIMESTAMPDIFF(MINUTE, MIN(time_stamp), start_ts)
                    AS duration_in_mins,
    MIN(state)      AS state
FROM
  ( SELECT
        id, time_stamp, state,
        @st_ts :=
            CASE WHEN state = @prev_state AND id = @prev_id
                     THEN @st_ts
                 WHEN id = @prev_id THEN @prev_ts
                     ELSE time_stamp
            END AS start_ts,
        @prev_ts := time_stamp,
        @prev_state := state,
        @prev_id := id
    FROM sample
       CROSS JOIN
         ( SELECT
             @prev_state := '',  @prev_ts := 0,
             @st_ts := 0,        @prev_id := 0
         ) AS dummy
    ORDER BY id DESC, time_stamp DESC
  ) AS tmptable
GROUP BY
    id, start_ts ;

Test at SQL-Fiddle-1


SSAS Tabular Cube partitioning - number of partitions We ha a Fact table with 500 milions of rows (data from 10 years - 60GB). I've partitioned this table on database level and SSAS Tabular Cube level as well - one month per partition to improve the overall browsing performance and SSAS Cube processing time. But last time I've heard that configuring more than 2 partitions for SSAS Tabular cube is not good. There should be max 2 partitions per tabular cube. Have you heard about something like that ? Is that true ?

In SSAS 2014 Tabular partitions inside a table process serially. In SSAS 2016 Tabular partitions inside a table can process in parallel. So in your version partitioning won't speed up processing if you process the whole table.

However partitioning is great for use in incremental processing. If you partition by year and only 2017 rows changed then you can just process the 2017 partition. Also to archive off data older than 10 years old you can drop the oldest partition.

I don't know where the 2 partition rule you mentioned came from but don't believe it makes sense. If you have a reference for that assertion with some context then feel free to share.