If client killed after ROLLBACK TO SAVEPOINT previous stmts committed

Bug #1524948 reported by Sveta Smirnova on 2015-12-10
6
This bug affects 1 person
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.

Sveta Smirnova (svetasmirnova) wrote :
Download full text (3.9 KiB)

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;
ERROR 1305 (42000): SAVEPOINT tx_0 does not exist
mysql>
[3]+ Stopped ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13000 test
sveta@thinkie:~/build/mysql-5.6/mysql-test> ps -ef | grep mysql
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=--gtid_mode=ON --mysqld=--log-slave-updates --mysqld=--enforce-gtid-consistency --mysqld=--binlog-format=row
sveta 31891 31861 0 23:02 pts/3 00:00:00 /usr/bin/perl ./mtr --start --suite=rpl rpl_alter --mysqld=--gtid_mode=ON --mysqld=--log-slave-updates --mysqld=--enforce-gtid-consistency --mysqld=--binlog-format=row
sveta 31892 31891 0 23:02 pts/3 00:00:00 ./lib/My/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-consistency --binlog-format=row --core-file
sveta 31893 31892 0 23:02 pts/3 00:00:00 /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-consistency --binlog-format=row --core-file
sveta 31910 31891 0 23:03 pts/3 00:00:00 ./lib/My/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-consistency --binlog-format=row --core-file
sveta 31911 31910 0 23:03 pts/3 00:00:00 /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-consistency --binlog-format=row --core-file
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:~/build/mysql-5.6/mysql-test> kill -9 31948
[3]+ Killed ../bin/mysql -uroot -h127.0.0.1 -P13000 -P13000 test
sveta@thinkie:~/build/mysql-5.6/mysql-test>
sveta@thinkie:~/build/mysql-5.6/mysql-test> mysqlmtr -P13000 test
Reading table information for completion ...

Read more...

tags: added: upstream
Yura Sorokin (yura-sorokin) wrote :

5.6 part fixed in upstream 5.6.38
Merge PR: https://github.com/percona/percona-server/pull/1968

Yura Sorokin (yura-sorokin) wrote :

5.7 part fixed in upstream 5.7.20
Merge PR: https://github.com/percona/percona-server/pull/1984

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-952

To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers

Remote bug watches

Bug watches keep track of this bug in other bug trackers.