rm -rf master/ bin/mysqld --defaults-file=./m90959.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/master --log-error-verbosity=3 bin/mysqld --defaults-file=./m90959.cnf --basedir=$PWD --datadir=$PWD/master --socket=/tmp/mysql_master.sock --port=3307 --log-error=$PWD/master/log.err 2>&1 & rm -rf slave/ bin/mysqld --defaults-file=./s90959.cnf --initialize-insecure --basedir=$PWD --datadir=$PWD/slave --log-error-verbosity=3 bin/mysqld --defaults-file=./s90959.cnf --basedir=$PWD --datadir=$PWD/slave --socket=/tmp/mysql_slave.sock --port=3308 --log-error=$PWD/slave/log.err 2>&1 & - master - slave -- create database slask; create table slask.insert_error (id int, name varchar(255), primary key (id)); insert into slask.insert_error values (1, 'dude'); insert into slask.insert_error values (2, 'dude'); insert into slask.insert_error values (3, 'dude'); insert into slask.insert_error values (4, 'dude'); insert into slask.insert_error values (5, 'dude'); insert into slask.insert_error values (6, 'dude'); insert into slask.insert_error values (7, 'dude'); insert into slask.insert_error values (8, 'dude'); insert into slask.insert_error values (9, 'dude'); insert into slask.insert_error values (10, 'dude'); -- setup replication [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock --prompt='Master>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Master>CREATE USER 'repl'@'localhost' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'localhost'; Query OK, 0 rows affected (0.00 sec) Master> Master>CREATE USER 'repl'@'127.0.0.1' IDENTIFIED BY 'slavepass'; Query OK, 0 rows affected (0.00 sec) Master>GRANT REPLICATION SLAVE ON *.* TO 'repl'@'127.0.0.1'; Query OK, 0 rows affected (0.00 sec) Master>FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) Master>show master status; +------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +------------------+----------+--------------+------------------+-------------------+ | mysql-bin.000002 | 1220 | | | | +------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock --prompt='Slave>' Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 Server version: 5.7.22-log MySQL Community Server (GPL) Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. Slave>CHANGE MASTER TO -> MASTER_HOST='localhost', -> MASTER_PORT=3307, -> MASTER_USER='repl', -> MASTER_PASSWORD='slavepass', -> MASTER_LOG_FILE='mysql-bin.000002', -> MASTER_LOG_POS=1220; Query OK, 0 rows affected, 2 warnings (0.01 sec) Slave>start slave; Query OK, 0 rows affected (0.00 sec) Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 1220 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 320 Relay_Master_Log_File: mysql-bin.000002 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: 1220 Relay_Log_Space: 527 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: d64a03af-5dab-11e8-aa78-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.22/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) Slave> -- create schema using provides statememts Master>create database slask; Query OK, 1 row affected (0.00 sec) Master>create table slask.insert_error (id int, name varchar(255), primary key (id)); Query OK, 0 rows affected (0.00 sec) Master>insert into slask.insert_error values (1, 'dude'); insert into slask.insert_error values (5, 'dude'); insert into slask.insert_error values (6, 'dude'); insert into slask.insert_error values (7, 'dude'); insert into slask.insert_error values (8, 'dude'); Query OK, 1 row affected (0.01 sec) Master>insert into slask.insert_error values (2, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (3, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (4, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (5, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (6, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (7, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (8, 'dude'); Query OK, 1 row affected (0.01 sec) Master>insert into slask.insert_error values (9, 'dude'); Query OK, 1 row affected (0.00 sec) Master>insert into slask.insert_error values (10, 'dude'); Query OK, 1 row affected (0.00 sec) -- slave bin/mysql -uroot -S /tmp/mysql_slave.sock -e "delete from slask.insert_error where id = 7;" -- master bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'one'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'one'; XA PREPARE 'one';" bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'two'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'two'; XA PREPARE 'two';" -- slave bin/mysql -uroot -S /tmp/mysql_slave.sock -e "BEGIN;select count(*) from slask.insert_error where id = 7 for update;" & -- master bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'one'"; bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'two'"; bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' ###### [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e "delete from slask.insert_error where id = 7;" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'one'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'one'; XA PREPARE 'one';" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'two'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'two'; XA PREPARE 'two';" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e "select count(*) from slask.insert_error where id = 7 for update;" & [1] 21147 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'one'"; [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'two'"; [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 70 | | NULL | | 3 | system user | | NULL | Connect | 74 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 39 | update | insert ignore into slask.insert_error values (7, 'dude') | | 6 | root | localhost | NULL | Query | 30 | statistics | select count(*) from slask.insert_error where id = 7 for update | | 8 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction [1]+ Exit 1 bin/mysql -uroot -S /tmp/mysql_slave.sock -e "select count(*) from slask.insert_error where id = 7 for update;" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 118 | | NULL | | 3 | system user | | NULL | Connect | 122 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 87 | update | insert ignore into slask.insert_error values (7, 'dude') | | 11 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 147 | | NULL | | 3 | system user | | NULL | Connect | 151 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 116 | update | insert ignore into slask.insert_error values (7, 'dude') | | 13 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 199 | | NULL | | 3 | system user | | NULL | Connect | 203 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 168 | update | insert ignore into slask.insert_error values (7, 'dude') | | 14 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 267 | | NULL | | 3 | system user | | NULL | Connect | 271 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 236 | update | insert ignore into slask.insert_error values (7, 'dude') | | 17 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Second_Behind_Master [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Seconds_Behind_Master Seconds_Behind_Master: 262 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 299 | | NULL | | 3 | system user | | NULL | Connect | 303 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 268 | update | insert ignore into slask.insert_error values (7, 'dude') | | 20 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | 2 | root | localhost | NULL | Sleep | 407 | | NULL | | 3 | system user | | NULL | Connect | 411 | Waiting for master to send event | NULL | | 4 | system user | | NULL | Connect | 376 | freeing items | NULL | | 21 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Seconds_Behind_Master Seconds_Behind_Master: 380 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 428 | | NULL | | 3 | system user | | NULL | Connect | 432 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 397 | update | insert ignore into slask.insert_error values (7, 'dude') | | 23 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Seconds_Behind_Master Seconds_Behind_Master: 404 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 38 | | NULL | | 3 | system user | | NULL | Connect | 549 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 514 | update | insert ignore into slask.insert_error values (7, 'dude') | | 25 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Seconds_Behind_Master Seconds_Behind_Master: 518 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | 2 | root | localhost | NULL | Sleep | 162 | | NULL | | 3 | system user | | NULL | Connect | 673 | Waiting for master to send event | NULL | | 27 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Seconds_Behind_Master Seconds_Behind_Master: NULL [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: ################# [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e "delete from slask.insert_error where id = 7;" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'one'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'one'; XA PREPARE 'one';" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA START 'two'; insert ignore into slask.insert_error values (7, 'dude'); XA END 'two'; XA PREPARE 'two';" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e "BEGIN;select count(*) from slask.insert_error where id = 7 for update;" & [1] 29876 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'one'"; [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_master.sock -e "XA COMMIT 'two'"; [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 119 | | NULL | | 3 | system user | | NULL | Connect | 123 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 22 | update | insert ignore into slask.insert_error values (7, 'dude') | | 6 | root | localhost | NULL | Query | 16 | statistics | select count(*) from slask.insert_error where id = 7 for update | | 8 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 132 | | NULL | | 3 | system user | | NULL | Connect | 136 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 35 | update | insert ignore into slask.insert_error values (7, 'dude') | | 6 | root | localhost | NULL | Query | 29 | statistics | select count(*) from slask.insert_error where id = 7 for update | | 10 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 152 | | NULL | | 3 | system user | | NULL | Connect | 156 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 55 | update | insert ignore into slask.insert_error values (7, 'dude') | | 6 | root | localhost | NULL | Query | 49 | statistics | select count(*) from slask.insert_error where id = 7 for update | | 12 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+-----------------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: ERROR 1205 (HY000) at line 1: Lock wait timeout exceeded; try restarting transaction [1]+ Exit 1 bin/mysql -uroot -S /tmp/mysql_slave.sock -e "BEGIN;select count(*) from slask.insert_error where id = 7 for update;" [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 158 | | NULL | | 3 | system user | | NULL | Connect | 162 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 61 | update | insert ignore into slask.insert_error values (7, 'dude') | | 14 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 167 | | NULL | | 3 | system user | | NULL | Connect | 171 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 70 | update | insert ignore into slask.insert_error values (7, 'dude') | | 16 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 189 | | NULL | | 3 | system user | | NULL | Connect | 193 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 92 | update | insert ignore into slask.insert_error values (7, 'dude') | | 18 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 213 | | NULL | | 3 | system user | | NULL | Connect | 217 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 116 | update | insert ignore into slask.insert_error values (7, 'dude') | | 20 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 76 | | NULL | | 3 | system user | | NULL | Connect | 316 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 215 | update | insert ignore into slask.insert_error values (7, 'dude') | | 22 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 5644 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 4027 Relay_Master_Log_File: mysql-bin.000002 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: 4927 Relay_Log_Space: 4951 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: 219 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: bccc901d-5dbb-11e8-ab86-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.22/slave/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: update Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 32 | | NULL | | 3 | system user | | NULL | Connect | 453 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 352 | update | insert ignore into slask.insert_error values (7, 'dude') | | 30 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 193 | | NULL | | 3 | system user | | NULL | Connect | 614 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 513 | update | insert ignore into slask.insert_error values (7, 'dude') | | 44 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 5644 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 197 | | NULL | | 3 | system user | | NULL | Connect | 618 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 517 | update | insert ignore into slask.insert_error values (7, 'dude') | | 46 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 6112 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 219 | | NULL | | 3 | system user | | NULL | Connect | 640 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 539 | update | insert ignore into slask.insert_error values (7, 'dude') | | 48 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 6112 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 228 | | NULL | | 3 | system user | | NULL | Connect | 649 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 548 | update | insert ignore into slask.insert_error values (7, 'dude') | | 50 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show slave status\G' | grep Master_Log_Pos Read_Master_Log_Pos: 6112 Exec_Master_Log_Pos: 4927 [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ | 2 | root | localhost | NULL | Sleep | 252 | | NULL | | 3 | system user | | NULL | Connect | 673 | Waiting for master to send event | NULL | | 4 | system user | | | Connect | 572 | update | insert ignore into slask.insert_error values (7, 'dude') | | 52 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+----------------------------------------------------------+ [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: [umshastr@hod03]/export/umesh/server/binaries/GABuilds/mysql-5.7.22: bin/mysql -uroot -S /tmp/mysql_slave.sock -e 'show processlist' +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ | 2 | root | localhost | NULL | Sleep | 11 | | NULL | | 3 | system user | | NULL | Connect | 709 | Waiting for master to send event | NULL | | 53 | root | localhost | NULL | Query | 0 | starting | show processlist | +----+-------------+-----------+------+---------+------+----------------------------------+------------------+ -- slave Slave>show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: localhost Master_User: repl Master_Port: 3307 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 6112 Relay_Log_File: mysql-relay-bin.000002 Relay_Log_Pos: 4027 Relay_Master_Log_File: mysql-bin.000002 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: 1205 Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Skip_Counter: 0 Exec_Master_Log_Pos: 4927 Relay_Log_Space: 5419 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: 1205 Last_SQL_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transaction_retries variable. Replicate_Ignore_Server_Ids: Master_Server_Id: 1 Master_UUID: bccc901d-5dbb-11e8-ab86-0010e05f3e06 Master_Info_File: /export/umesh/server/binaries/GABuilds/mysql-5.7.22/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: 180522 14:48:28 Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)