Bug #79596 If client killed after ROLLBACK TO SAVEPOINT previous stmts committed

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.

Bug is not repeatable with version 5.5

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.

Hello Sveta,

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

Thanks,
Umesh

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

This is a corruption bug. Why is it still open after more than a year?

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?

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.