If client killed after ROLLBACK TO SAVEPOINT previous stmts committed
Affects | Status | Importance | Assigned to | Milestone | ||
---|---|---|---|---|---|---|
MySQL Server |
Unknown
|
Unknown
|
||||
Percona Server moved to https://jira.percona.com/projects/PS | Status tracked in 5.7 | |||||
5.5 |
Invalid
|
Undecided
|
Unassigned | |||
5.6 |
Fix Released
|
High
|
Joey | |||
5.7 |
Fix Released
|
High
|
Unassigned |
Bug Description
Description:
When you start transaction, then issue
savepoint tx_0;
rollback to savepoint tx_0;
and then client is killed all data, changed before savepoint tx_0, is committed.
But more strange thing: while this data is committed it is not replicated to slave even if row-based format used.
Can be related to bug #79493
Bug is not repeatable with version 5.5
How to repeat:
create table test (value int) engine=innodb;
begin;
insert into test set value = 1;
savepoint tx_0;
rollback to savepoint tx_0;
Ctrl+z
bg
ps -ef | grep mysql
Find PID of mysql command-line client, kill it with `kill -9`
Now connect to master and run
mysql> select * from test;
+-------+
| value |
+-------+
| 1 |
+-------+
1 row in set (0.00 sec)
Then connect to slave and run:
mysql> select * from test;
Empty set (0.00 sec)
Replication is working fine.
Suggested fix:
Do not commit data, changed before SAVEPOINT statement.
tags: | added: upstream |
More investigation:
savepoint tx_0; is not needed, same behavior happen with following test case:
mysql> create table test (value int) engine=innodb;
Query OK, 0 rows affected (19.57 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test set value = 1;
Query OK, 1 row affected (0.00 sec)
mysql> rollback to savepoint tx_0; ~/build/ mysql-5. 6/mysql- test> ps -ef | grep mysql --gtid_ mode=ON --mysqld= --log-slave- updates --mysqld= --enforce- gtid-consistenc y --mysqld= --binlog- format= row --gtid_ mode=ON --mysqld= --log-slave- updates --mysqld= --enforce- gtid-consistenc y --mysqld= --binlog- format= row SafeProcess/ my_safe_ process -- /home/sveta/ build/mysql- 5.6/bin/ mysqld --defaults- group-suffix= .1 --defaults- file=/home/ sveta/build/ mysql-5. 6/mysql- test/var/ my.cnf --log-output=file --loose- debug-sync- timeout= 600 --default- storage- engine= MyISAM --default- tmp-storage- engine= MyISAM --gtid_mode=ON --log-slave-updates --enforce- gtid-consistenc y --binlog-format=row --core-file build/mysql- 5.6/bin/ mysqld --defaults- group-suffix= .1 --defaults- file=/home/ sveta/build/ mysql-5. 6/mysql- test/var/ my.cnf --log-output=file --loose- debug-sync- timeout= 600 --default- storage- engine= MyISAM --default- tmp-storage- engine= MyISAM --gtid_mode=ON --log-slave-updates --enforce- gtid-consistenc y --binlog-format=row --core-file SafeProcess/ my_safe_ process -- /home/sveta/ build/mysql- 5.6/bin/ mysqld --defaults- group-suffix= .2 --defaults- file=/home/ sveta/build/ mysql-5. 6/mysql- test/var/ my.cnf --log-output=file --loose- debug-sync- timeout= 600 --default- storage- engine= MyISAM --default- tmp-storage- engine= MyISAM --gtid_mode=ON --log-slave-updates --enforce- gtid-consistenc y --binlog-format=row --core-file build/mysql- 5.6/bin/ mysqld --defaults- group-suffix= .2 --defaults- file=/home/ sveta/build/ mysql-5. 6/mysql- test/var/ my.cnf --log-output=file --loose- debug-sync- timeout= 600 --default- storage- engine= MyISAM --default- tmp-storage- engine= MyISAM --gtid_mode=ON --log-slave-updates --enforce- gtid-consistenc y --binlog-format=row --core-file ~/build/ mysql-5. 6/mysql- test> kill -9 31948 ~/build/ mysql-5. 6/mysql- test> ~/build/ mysql-5. 6/mysql- test> mysqlmtr -P13000 test
ERROR 1305 (42000): SAVEPOINT tx_0 does not exist
mysql>
[3]+ Stopped ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13000 test
sveta@thinkie:
sveta 7723 2371 0 13:56 pts/1 00:00:00 ../bin/mysql -uroot -h127.0.0.1 -P13000 employees
sveta 31861 7298 0 23:02 pts/3 00:00:00 /usr/bin/perl ./mtr --start --suite=rpl rpl_alter --mysqld=
sveta 31891 31861 0 23:02 pts/3 00:00:00 /usr/bin/perl ./mtr --start --suite=rpl rpl_alter --mysqld=
sveta 31892 31891 0 23:02 pts/3 00:00:00 ./lib/My/
sveta 31893 31892 0 23:02 pts/3 00:00:00 /home/sveta/
sveta 31910 31891 0 23:03 pts/3 00:00:00 ./lib/My/
sveta 31911 31910 0 23:03 pts/3 00:00:00 /home/sveta/
sveta 31948 7298 0 23:03 pts/3 00:00:00 ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13000 test
sveta 31991 7298 0 23:05 pts/3 00:00:00 grep --color=auto mysql
sveta@thinkie:
[3]+ Killed ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13000 test
sveta@thinkie:
sveta@thinkie:
Reading table information for completion ...