Wednesday, September 2, 2015

SQL Server Isolation Levels with concurrent updates on nonclustered index

Testing under Read Committed Isolation Level

Session 1(PID 90):
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;

Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93):  (Session receive error)
Msg 1205, Level 13, State 45, Line 1
Transaction (Process ID 93) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.


image

 

Testing under Repeatable Read Isolation Level

Session 1 (PID 90):
set transaction isolation level repeatable read
go
begin transaction
update DimGeography set PostalCode='000001' where GeographyKey=1;

Session 2(PID 93): (Session will wait after executing)
update DimGeography set PostalCode='000002' where GeographyKey=1;
Session 1 (PID 90):
update DimGeography set PostalCode='000003' where GeographyKey=1;
Session 2 (PID 93): (Session will continue wait for lock)
Session 1 (PID 90):
commit;
Session 2 (PID 93): (returned with message “(1 row(s) affected)”)

Query the data from Session 1, the update performed by session 1 lost
image

Wednesday, August 26, 2015

Start SQL Server Analysis Service in Console mode

C:\MSAS12.PROD\OLAP\bin>msmdsrv.exe -s C:\MSAS12.PROD\OLAP\Config -c
-s C:\MSAS12.PROD\OLAP\Config -c
Message: The flight recorder was started. (Source: MSOLAP$PROD, Type: 1, Category: 289, Event ID: 0x41210005)
Message: Service started. Microsoft SQL Server Analysis Services 64 Bit Enterprise (x64) SP1 12.0.4100.1. (Source: MSOLAP$PROD, Type: 1, Category: 289
, Event ID: 0x41210000)
Message: Software usage metrics started successfully. (Source: MSOLAP$PROD, Type: 1, Category: 289, Event ID: 0x41210030)

image

How to change the SSAS Port number

SSAS default instance or clustered instance (regardless named instance or not) default to 2383.

For SSAS named instance in standalone environment, default port is high port, and SQL Server Browser Service Listening on TCP 2382.

To change the port:

Edit file “<Instance Name>\OLAP\Config\msmdsrv.ini”, change the port number from “0” to desired value. (3034 in our case).

image

Don't forget to configure your firewall to allow incoming requests on TCP port 3034.

netsh advfirewall firewall add rule name="SQL Server Analysis Services (tcp-in) on 3034" dir=in action=allow protocol=TCP localport=3034 profile=domain

Restart the SSRS Service

image

C:\Users\administrator.DBAGLOBE>tasklist | findstr msmdsrv.exe
msmdsrv.exe                   1084 Services                   0     34,828 K

C:\Users\administrator.DBAGLOBE>netstat -nao | findstr 1084
  TCP    0.0.0.0:3034           0.0.0.0:0              LISTENING       1084
  TCP    [::]:3034              [::]:0                 LISTENING       1084

Connect to the SSAS using format “machinename:port” format, instead of “instancename:port” format.

image

Incorrect format:

image

Find out which port SQL Server services listening on

image

C:\>tasklist |findstr 2060
sqlbrowser.exe                2060 Services                   0     10,000 K

C:\>netstat -nao | findstr 2060
  TCP    0.0.0.0:2382           0.0.0.0:0              LISTENING       2060
  TCP    [::]:2382              [::]:0                 LISTENING       2060
  UDP    0.0.0.0:1434           *:*                                    2060
  UDP    [::]:1434              *:*                                    2060

C:\>tasklist | findstr 2456
sqlservr.exe                  2456 Services                   0    174,624 K

C:\>netstat -nao | findstr 2456
  TCP    0.0.0.0:3033           0.0.0.0:0              LISTENING       2456
  TCP    127.0.0.1:64254        0.0.0.0:0              LISTENING       2456
  TCP    [::]:3033              [::]:0                 LISTENING       2456
  TCP    [::1]:64254            [::]:0                 LISTENING       2456

C:\>tasklist | findstr 2508
SQLAGENT.EXE                  2508 Services                   0     20,732 K

C:\>netstat -nao | findstr 2508

C:\>tasklist | findstr 2156
fdlauncher.exe                2156 Services                   0      3,476 K

C:\>netstat -nao | findstr 2156

C:\>tasklist | findstr 1500
msmdsrv.exe                   1500 Services                   0     35,864 K

C:\>netstat -nao | findstr 1500
  TCP    0.0.0.0:64310          0.0.0.0:0              LISTENING       1500
  TCP    [::]:64310             [::]:0                 LISTENING       1500

Tuesday, August 18, 2015

Install MySQL Python Connector

[mysql@localhost repo]$ tar zxvf mysql-connector-python-commercial-2.0.4-py2.7.tar.gz
[mysql@localhost repo]$ sudo su -
[root@localhost repo]# cd /mysql/repo/mysql-connector-python-commercial-2.0.4-py2.7
[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]#
[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]# python setup.py install
running install
running build
copying mysql/connector/pooling.pyc -> build/lib/mysql/connector
copying mysql/connector/custom_types.pyc -> build/lib/mysql/connector
copying mysql/connector/fabric/connection.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/caching.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/balancing.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/fabric/__init__.pyc -> build/lib/mysql/connector/fabric
copying mysql/connector/catch23.pyc -> build/lib/mysql/connector
copying mysql/connector/connection.pyc -> build/lib/mysql/connector
copying mysql/connector/errorcode.pyc -> build/lib/mysql/connector
copying mysql/connector/cursor.pyc -> build/lib/mysql/connector
copying mysql/connector/conversion.pyc -> build/lib/mysql/connector
copying mysql/connector/authentication.pyc -> build/lib/mysql/connector
copying mysql/connector/locales/eng/client_error.pyc -> build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/eng/__init__.pyc -> build/lib/mysql/connector/locales/eng
copying mysql/connector/locales/__init__.pyc -> build/lib/mysql/connector/locales
copying mysql/connector/errors.pyc -> build/lib/mysql/connector
copying mysql/connector/__init__.pyc -> build/lib/mysql/connector
copying mysql/connector/protocol.pyc -> build/lib/mysql/connector
copying mysql/connector/charsets.pyc -> build/lib/mysql/connector
copying mysql/connector/dbapi.pyc -> build/lib/mysql/connector
copying mysql/connector/utils.pyc -> build/lib/mysql/connector
copying mysql/connector/django/schema.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/introspection.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/client.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/base.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/compiler.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/validation.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/__init__.pyc -> build/lib/mysql/connector/django
copying mysql/connector/django/creation.pyc -> build/lib/mysql/connector/django
copying mysql/connector/constants.pyc -> build/lib/mysql/connector
copying mysql/connector/network.pyc -> build/lib/mysql/connector
copying mysql/connector/optionfiles.pyc -> build/lib/mysql/connector
copying mysql/connector/version.pyc -> build/lib/mysql/connector
copying mysql/__init__.pyc -> build/lib/mysql
running install_lib
running build_py
package init file 'mysql/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/eng/__init__.py' not found (or not a regular file)
package init file 'mysql/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/__init__.py' not found (or not a regular file)
package init file 'mysql/connector/locales/eng/__init__.py' not found (or not a regular file)
running install_egg_info
Removing /usr/lib/python2.7/site-packages/mysql_connector_python-2.0.4-py2.7.egg-info
Writing /usr/lib/python2.7/site-packages/mysql_connector_python-2.0.4-py2.7.egg-info

Sample Python Code for verifying the installation:

import mysql.connector

config = {
  'user': 'root',
  'password': 'complex_password',
  'host': 'localhost',
  'port': '5001',
  'database': 'appdb',
  'raise_on_warnings': True,
}

cnx = mysql.connector.connect(**config)
cursor = cnx.cursor()

query = ("select * from t1")

cursor.execute(query)

for (id,last_updated) in cursor:
  print(str(id)+': '+str(last_updated))

cursor.close()
cnx.close()

[mysql@localhost repo]$ python test.py
1: 2015-08-14 23:01:49
2: 2015-08-14 23:25:42
3: 2015-08-14 23:27:17
4: 2015-08-14 23:29:01
5: 2015-08-14 23:30:00

Setup parameters:

[root@localhost mysql-connector-python-commercial-2.0.4-py2.7]# python setup.py --help
Common commands: (see '--help-commands' for more)

  setup.py build      will build the package underneath 'build/'
  setup.py install    will install the package

Global options:
  --verbose (-v)      run verbosely (default)
  --quiet (-q)        run quietly (turns verbosity off)
  --dry-run (-n)      don't actually do anything
  --help (-h)         show detailed help message
  --no-user-cfg       ignore pydistutils.cfg in your home directory
  --command-packages  list of packages that provide distutils commands

Information display options (just display information, ignore any commands)
  --help-commands     list all available commands
  --name              print package name
  --version (-V)      print package version
  --fullname          print <package name>-<version>
  --author            print the author's name
  --author-email      print the author's email address
  --maintainer        print the maintainer's name
  --maintainer-email  print the maintainer's email address
  --contact           print the maintainer's name if known, else the author's
  --contact-email     print the maintainer's email address if known, else the
                      author's
  --url               print the URL for this package
  --license           print the license of the package
  --licence           alias for --license
  --description       print the package description
  --long-description  print the long package description
  --platforms         print the list of platforms
  --classifiers       print the list of classifiers
  --keywords          print the list of keywords
  --provides          print the list of packages/modules provided
  --requires          print the list of packages/modules required
  --obsoletes         print the list of packages/modules made obsolete

usage: setup.py [global_opts] cmd1 [cmd1_opts] [cmd2 [cmd2_opts] ...]
   or: setup.py --help [cmd1 cmd2 ...]
   or: setup.py --help-commands
   or: setup.py cmd --help

Sunday, August 16, 2015

Skip MySQL transactions on Slave when GTID configured

Symptoms:

server2> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl_user
                  Master_Port: 5001
                Connect_Retry: 60
              Master_Log_File: bin-log.000012
          Read_Master_Log_Pos: 2255
               Relay_Log_File: relay-bin.000016
                Relay_Log_Pos: 1902
        Relay_Master_Log_File: bin-log.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 1032
                   Last_Error: Worker 0 failed executing transaction 'e645f6c4-428d-11e5-bf83-08002702dadb:20' at master log bin-log.000012, end_log_pos 1939; Could not execute Delete_rows event on table appdb.a; Can't find record in 'a', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log bin-log.000012, end_log_pos 1939
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 1696
              Relay_Log_Space: 2860
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 1032
               Last_SQL_Error: Worker 0 failed executing transaction 'e645f6c4-428d-11e5-bf83-08002702dadb:20' at master log bin-log.000012, end_log_pos 1939; Could not execute Delete_rows event on table appdb.a; Can't find record in 'a', Error_code: 1032; handler error HA_ERR_END_OF_FILE; the event's master log bin-log.000012, end_log_pos 1939
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e645f6c4-428d-11e5-bf83-08002702dadb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State:
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp: 150816 22:53:41
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: e645f6c4-428d-11e5-bf83-08002702dadb:5-21
            Executed_Gtid_Set: b8ac4b6b-428f-11e5-bf8f-08002702dadb:1-5,
e645f6c4-428d-11e5-bf83-08002702dadb:5-19
                Auto_Position: 0
1 row in set (0.00 sec)

Check the failed statements:

[mysql@localhost script]$ mysqlbinlog /mysql/server1/repl/bin-log.000012 --start-position=1939 --stop-position=1939
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#150816 22:41:17 server id 1  end_log_pos 120 CRC32 0xa5f1c472  Start: binlog v 4, server v 5.6.26-enterprise-commercial-advanced-log created 150816 22:41:17 at startup
# Warning: this binlog is either in use or was not closed properly.
ROLLBACK/*!*/;
BINLOG '
DaHQVQ8BAAAAdAAAAHgAAAABAAQANS42LjI2LWVudGVycHJpc2UtY29tbWVyY2lhbC1hZHZhbmNl
ZC1sb2cAAAAAAAAAAAANodBVEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAXLE
8aU=
'/*!*/;
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;


[mysql@localhost script]$ mysqlbinlog /mysql/server1/repl/bin-log.000012 -v
SET @@SESSION.GTID_NEXT= 'e645f6c4-428d-11e5-bf83-08002702dadb:20'/*!*/;
# at 1744
#150816 22:53:41 server id 1  end_log_pos 1817 CRC32 0x96f22726         Query   thread_id=7     exec_time=0     error_code=0
SET TIMESTAMP=1439736821/*!*/;
BEGIN
/*!*/;
# at 1817
# at 1854
#150816 22:53:41 server id 1  end_log_pos 1899 CRC32 0x384ad943         Table_map: `appdb`.`a` mapped to number 71
# at 1899
#150816 22:53:41 server id 1  end_log_pos 1939 CRC32 0xb9074e0e         Delete_rows: table id 71 flags: STMT_END_F

BINLOG '
9aPQVRMBAAAALQAAAGsHAAAAAEcAAAAAAAEABWFwcGRiAAFhAAEDAAFD2Uo4
9aPQVSABAAAAKAAAAJMHAAAAAEcAAAAAAAEAAgAB//4AAAAADk4HuQ==
'/*!*/;
### DELETE FROM `appdb`.`a`
### WHERE
###   @1=0
# at 1939

#150816 22:53:41 server id 1  end_log_pos 1970 CRC32 0xf10570bc         Xid = 62
COMMIT/*!*/;
# at 1970
#150816 22:55:06 server id 1  end_log_pos 2018 CRC32 0x839b9136         GTID [commit=yes]

Fix the replication:

server2> stop slave;
Query OK, 0 rows affected (0.00 sec)

server2> set gtid_next='e645f6c4-428d-11e5-bf83-08002702dadb:20';
Query OK, 0 rows affected (0.00 sec)

server2> begin;commit;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.04 sec)

server2> set gtid_next="automatic";
Query OK, 0 rows affected (0.00 sec)

server2> start slave;
Query OK, 0 rows affected, 1 warning (0.01 sec)

server2> show slave status \G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: localhost
                  Master_User: repl_user
                  Master_Port: 5001
                Connect_Retry: 60
              Master_Log_File: bin-log.000012
          Read_Master_Log_Pos: 2255
               Relay_Log_File: relay-bin.000017
                Relay_Log_Pos: 352
        Relay_Master_Log_File: bin-log.000012
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 2255
              Relay_Log_Space: 2860
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Master_Server_Id: 1
                  Master_UUID: e645f6c4-428d-11e5-bf83-08002702dadb
             Master_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it
           Master_Retry_Count: 86400
                  Master_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Master_SSL_Crl:
           Master_SSL_Crlpath:
           Retrieved_Gtid_Set: e645f6c4-428d-11e5-bf83-08002702dadb:5-21
            Executed_Gtid_Set: b8ac4b6b-428f-11e5-bf8f-08002702dadb:1-5,
e645f6c4-428d-11e5-bf83-08002702dadb:5-21
                Auto_Position: 0
1 row in set (0.00 sec)

 

Alternative method:

server2> stop slave;
Query OK, 0 rows affected (0.01 sec)

server2> CHANGE MASTER TO MASTER_HOST='localhost', MASTER_PORT=5001, MASTER_USER='repluser', MASTER_PASSWORD='compexpassword’, MASTER_LOG_FILE='bin-log.000012', MASTER_LOG_POS=2555;
Query OK, 0 rows affected, 2 warnings (0.02 sec)

server2> start slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)

Reference: https://www.percona.com/blog/2013/03/26/repair-mysql-5-6-gtid-replication-by-injecting-empty-transactions/

Friday, August 14, 2015

Emulating wide area network delays

http://www.linuxfoundation.org/collaborate/workgroups/networking/netem

This is the simplest example, it just adds a fixed amount of delay to all packets going out of the local Ethernet.

# tc qdisc add dev eth0 root netem delay 100ms

Real Examples:

[root@localhost ~]# ifconfig -a
enp0s3: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.56.101  netmask 255.255.255.0  broadcast 192.168.56.255
        inet6 fe80::a00:27ff:fe02:dadb  prefixlen 64  scopeid 0x20<link>
        ether 08:00:27:02:da:db  txqueuelen 1000  (Ethernet)
        RX packets 836  bytes 75866 (74.0 KiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 652  bytes 74833 (73.0 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@localhost ~]# tc qdisc add dev enp0s3 root netem delay 100ms
[root@localhost ~]# tc qdisc show dev enp0s3
qdisc netem 8002: root refcnt 2 limit 1000 delay 100.0ms
[root@localhost ~]# tc qdisc delete dev enp0s3 root netem delay 100ms
[root@localhost ~]# tc qdisc show dev enp0s3
qdisc pfifo_fast 0: root refcnt 2 bands 3 priomap  1 2 2 2 1 2 0 0 1 1 1 1 1 1 1 1

The below screenshot of ping clearly demonstrates the latency added (100ms).

image