I was able to repeat generation of such binary logs on slave using dump of customer's data and entries in their binary log. I cannot repeat same issue with artificial SQL.
Instructions:
1. Use mysql-5.7.17 or Percona Server 5.7.17. I used MySQL, thus master port is 13000 and slave port is 13001. In case if you use Percona Server adjust ports accordingly (13001 and 13002). I also assume you put test files into same mysql-test dir. If not adjust paths accordingly.
2. cd mysql-test
3. ./mtr --start --suite=rpl rpl_alter --mysqld=--gtid_mode=ON --mysqld=--log-slave-updates --mysqld=--enforce-gtid-consistency --mysqld=--binlog-format=row --mysqld=--lower-case-table-names --mysqld=--transaction-isolation=read-committed &
4. ../bin/mysql -uroot -h127.0.0.1 -P13000 -e "create database XXX"
5. ../bin/mysql -uroot -h127.0.0.1 -P13000 XXX < XXX.sql
6. Now connect two clients to master and run tests (order matters!):
session 1> \d ;
session 1> xa commit 'e26811b9-cf17-8d3c-fc43-18416dffb39f,-a3ad-0e5355dde20f';
session 2> \d ;
session 2> xa commit '9c1a4eec-2b85-2e8f-1c8b-50de970226f0,-863b-f8bfb9554802';
7. Now connect to slave and run SHOW SLAVE STATUS\G
You will see something like this:
Slave_SQL_Running_State: System lock
... Retrieved_Gtid_Set: b2184f1d-fa76-11e6-9631-90b11c55312a:1-1429 Executed_Gtid_Set: b2184f1d-fa76-11e6-9631-90b11c55312a:1-1426
8. After some time slave will fail with error:
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.
I was able to repeat generation of such binary logs on slave using dump of customer's data and entries in their binary log. I cannot repeat same issue with artificial SQL.
Instructions:
1. Use mysql-5.7.17 or Percona Server 5.7.17. I used MySQL, thus master port is 13000 and slave port is 13001. In case if you use Percona Server adjust ports accordingly (13001 and 13002). I also assume you put test files into same mysql-test dir. If not adjust paths accordingly. --gtid_ mode=ON --mysqld= --log-slave- updates --mysqld= --enforce- gtid-consistenc y --mysqld= --binlog- format= row --mysqld= --lower- case-table- names --mysqld= --transaction- isolation= read-committed &
2. cd mysql-test
3. ./mtr --start --suite=rpl rpl_alter --mysqld=
4. ../bin/mysql -uroot -h127.0.0.1 -P13000 -e "create database XXX"
5. ../bin/mysql -uroot -h127.0.0.1 -P13000 XXX < XXX.sql
6. Now connect two clients to master and run tests (order matters!):
session 1> source 36742_nogtids.sql.1
session 2> source 36743_nogtids.sql.1
session 2> source 36743_nogtids.sql.2
session 1> source 36742_nogtids.sql.2
session 1> source 36742_nogtids.sql.3
session 1> source 36742_nogtids.sql.4
session 1> source 36742_nogtids.sql.5
session 1> source 36742_nogtids.sql.6
session 2> source 36743_nogtids.sql.3
session 2> source 36743_nogtids.sql.4
session 2> source 36743_nogtids.sql.5
session 2> source 36743_nogtids.sql.6
session 1> \d ; cf17-8d3c- fc43-18416dffb3 9f,-a3ad- 0e5355dde20f' ;
session 1> xa commit 'e26811b9-
session 2> \d ; 2b85-2e8f- 1c8b-50de970226 f0,-863b- f8bfb9554802' ;
session 2> xa commit '9c1a4eec-
7. Now connect to slave and run SHOW SLAVE STATUS\G
You will see something like this:
Slave_ SQL_Running_ State: System lock
Retrieved_ Gtid_Set: b2184f1d- fa76-11e6- 9631-90b11c5531 2a:1-1429
Executed_ Gtid_Set: b2184f1d- fa76-11e6- 9631-90b11c5531 2a:1-1426
...
8. After some time slave will fail with error:
Last_Errno: 1205 on_retries variable.
Last_Error: Slave SQL thread retried transaction 10 time(s) in vain, giving up. Consider raising the value of the slave_transacti
All data is in the ticket.