If client killed after ROLLBACK TO SAVEPOINT previous stmts committed

[Sveta Smirnova]

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)]

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 ...

[Yura Sorokin (yura-sorokin)]

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

[Yura Sorokin (yura-sorokin)]

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

[Shahriyar Rzayev (rzayev-sehriyar)]

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