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