Bug #79596 If client killed after ROLLBACK TO SAVEPOINT previous stmts committed
Submitted: 10 Dec 2015 19:38 Modified: 4 Sep 2017 2:41
Reporter: Sveta Smirnova (OCA) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.6.27, 5.6.28, 5.7.10 OS:Any
Assigned to: CPU Architecture:Any
Tags: regression

[10 Dec 2015 19:38] 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

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.
[10 Dec 2015 19:46] Sveta Smirnova
Bug is not repeatable with version 5.5
[10 Dec 2015 21:07] Sveta Smirnova
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 of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.6.27-debug-log Source distribution

Copyright (c) 2000, 2015, 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.

mysql> select * from test;
+-------+
| value |
+-------+
|     1 |
+-------+
1 row in set (0.00 sec)

mysql> \q
Bye

Workaround: issue any statement after "rollback to savepoint tx_0;", even SELECT 1; would prevent changes to be committed.
[11 Dec 2015 6:51] MySQL Verification Team
Hello Sveta,

Thank you for the report and test case.
Verified as described with 5.6.27 build.

Thanks,
Umesh
[11 Dec 2015 14:54] zhai weixiang
After looking into the code ,I think it's because while the session is killed, the thd->lex->sql_command is not reset, So in function MYSQL_BIN_LOG::rollback, ha_rollback_low is not called.

If ctrl+c the client, the sql_command is set to SQLCOM_END.

A simple patch to verify the root cause:

diff --git a/sql/sql_class.cc b/sql/sql_class.cc
index 777f13a..8523725 100644
--- a/sql/sql_class.cc
+++ b/sql/sql_class.cc
@@ -1777,6 +1777,11 @@ void THD::cleanup(void)
   DEBUG_SYNC(this, "thd_cleanup_start");
 
   killed= KILL_CONNECTION;
+  if (lex)
+  {
+    lex->sql_command= SQLCOM_END;
+  }
+
   if (trn_ctx->xid_state()->has_state(XID_STATE::XA_PREPARED))
   {
     transaction_cache_detach(trn_ctx);
[5 Jan 2017 17:04] Mark Callaghan
This is a corruption bug. Why is it still open after more than a year?
[10 Feb 2017 23:17] Dan Reif
Call a data corruption bug with a trivial 100% repro case "non-critical" is absurd.  That it can be fixed with a one-line patch and yet hasn't been actioned in over a year serves only to underscore this fact.  Can we get this fixed, please?
[4 Sep 2017 2:41] Paul DuBois
Posted by developer:
 
Fixed in 5.6.38, 5.7.20, 8.0.3.

If a session rolled back to a savepoint and then was killed, the
statements up to the point of the savepoint could be committed.