MySQL Bugs: #45923: Innodb autocommits if query is aborted or killed midway executing UPDATE

Bug #45923	Innodb autocommits if query is aborted or killed midway executing UPDATE
Submitted:	2 Jul 2009 21:25	Modified:	4 Jul 2009 9:14
Reporter:	Roland Bouman	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: General	Severity:	S1 (Critical)
Version:	5.1.30	OS:	Windows (XP)
Assigned to:		CPU Architecture:	Any

Description:
If autocommit is enabled, aborting an UPDATE statement (either using Ctrl+C in the mysql client, or issuing a kill command from another session) on an InnoDB table will commit the changes up to the abort. The changes cannot be rolled back. 

This is not expected behaviour. Since the statement was aborted, it did not complete normally. If a statement does not complete normally, the pending changes caused by the statement should be rolled back automatically. Alternatively the  user should get the change to rollback the transaction manually (which would be different behaviour from what you get in case the statement fails due to a contraint violation). 

The current behaviour should definitely not occur. I consider it a serious bug since it irreversibly corrupts data.

How to repeat:
Open the mysql command line client. 

CREATE TABLE `innodb_commit` (
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB

INSERT INTO innodb_commit (name) VALUES (REPEAT('A',255));

INSERT INTO innodb_commit (name) VALUES SELECT name FROM innodb_commit;
...repeat until we have a mln rows...
INSERT INTO innodb_commit (name) VALUES SELECT name FROM innodb_commit;
Query OK, 524288 rows affected (40.53 sec)
Records: 524288  Duplicates: 0  Warnings: 0

UPDATE innodb_commit SET name = REPEAT('B', 255);
mysql> update innodb_commit set name = repeat('B',255 );
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted
mysql> select name , count(*) from innodb_commit group by name;
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------+----------+
| name
                                                                                                  | count(*) |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------+----------+
| AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA |  1037222 |
| BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB |    11354 |
+---------------------------------------------------------------------------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------------+----------+
2 rows in set (4.42 sec)

Suggested fix:
Please don't commit changes caused by aborted statements

Thank you for the bug report.

c:\dbs>c:\dbs\5.1\bin\mysql -uroot --port=3510 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.1.37-Win X64-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql 5.1 >use test
Database changed
mysql 5.1 >show variables like "%commit%";
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| autocommit                     | ON    |
| innodb_commit_concurrency      | 0     |
| innodb_flush_log_at_trx_commit | 0     |
+--------------------------------+-------+
3 rows in set (0.00 sec)

mysql 5.1 >CREATE TABLE `innodb_commit` (
    ->   `name` varchar(255) DEFAULT NULL
    -> ) ENGINE=InnoDB
    -> ;
Query OK, 0 rows affected (0.24 sec)

mysql 5.1 >INSERT INTO innodb_commit (name) VALUES (REPEAT('A',255));
Query OK, 1 row affected (0.00 sec)

mysql 5.1 >INSERT INTO innodb_commit (name) SELECT name FROM innodb_commit;
Query OK, 524288 rows affected (1 min 39.99 sec)
Records: 524288  Duplicates: 0  Warnings: 0

mysql 5.1 >update innodb_commit set name = repeat('B',255 );
Query aborted by Ctrl+C
ERROR 1317 (70100): Query execution was interrupted
mysql 5.1 >select name , count(*) from innodb_commit group by name\G
*************************** 1. row ***************************
    name: AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
count(*): 1023153
*************************** 2. row ***************************
    name: BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB
BBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBBB

Duplicate of Bug#45309?

Hi Alexander! 

yes- I am pretty sure this is a duplicate. Sorry for not noticing the existing report.

kind regards,

Roland

making it a duplicate then and categorizing away from InnoDB. Regarding giving people chance to rollback, turning off autocommit should do it. With autocommit on, rollback on ctrl-c will still be a gamble even with the base bug fixed.

Hi Michael,

"Regarding giving people chance to rollback, turning off autocommit should do it. With autocommit on, rollback on ctrl-c will still be a gamble even with the base bug fixed."

This doesn't sound like an acceptable solution. Basically you're saying you can't rely on transactions when autocommit is enabled, yet autocommit is the default!!
BTW - just curious, is there any difference between aborting with Ctrl+C and the kill command? 

TIA, Roland

Roland, you can rely on transactions,you just can't rely on ctrl-c to be
delivered on time, in this sense it applies to interrupting cat(1) as well.
As for kill, I don't know for sure, but iirc it's the same, this is done
by setting a flag in the THD structure, then the thread issues a rollback 
as soon as it notices the flag. MySQL folks could correct me if I'm wrong. Apparently the base bug says MySQL doesn't seem to check the flag often enough.

I Michael! 

thanks for replying. Ehm, I guess your prior comment confused me. Can you please point out to me in what way the behaviour in case of abort is different as compared to a statement that runs into a constraint violation? 

I mean, last time I checked, the latter will rollback the statement if autocommit is enabled, and if autocmmot is not enabled, the statement is rolled back but the transactions is still kept alive. 

You seem to imply this is not the case for an abort, even if http://bugs.mysql.com/bug.php?id=45309 would be fixed. Is that correct? My point is, a failing statement due to abort is no different from a failing statement due to a constraint violation at least from a logical point of view.

tia, Roland.

Roland, abort is different from a constraint violation in that for an abort
there's a client involved. For a constraint violation there's a guarantee that the executing thread will notice the violation before the transaction is committed, irrespective of the autocommit value. On violation the executing thread will then roll back the transaction. 
When a client is involved as in case of ctrl-c, the abort has to be delivered to the executing thread which is not instantaneous. When you hit ctrl-c, the OS has to notice it, interrupt the client, client interrupt handler will generate a packet and send it to MySQL over the network or a socket, MySQL will do whatever it does... all this time the executing thread is running. If it manages to commit in the meantime, this is it, the statement execution is over, transaction is committed, it cannot be rolled back after the commit. So whatever MySQL does there's a small window of opportunity between you hit the ctrl-c but MySQL simply doesn't know it yet, in which the transaction may be committed because of the autocommit.

Hi Michael,

thanks for your explanation - I appreciate it a lot.
Now this part in your explanation confuses me:

"all this time the executing thread is running. If it manages to commit in the meantime, this is it, the statement execution is over, transaction is committed, it cannot be rolled back after the commit."

I mean, the test case I posted clearly indicates that part of the set is changed and committed, and part is not. I mean, why the heck is the executing thread making permanent changes at all before all of the row-level actions corresponding to the statement have completed?

I can understand that if the executing thread happens to be done executing all row-level changes and just made the changes permanently and then receives the abort message, there would be a slight problem, because at that point you would not be able to undo anything. But this problem is not an issue as far as data integrity is concerned - the statement still executed atomically.

So to be clear, prematurely stopping statement execution should absolutely never break atomicity and consistency. It should not matter what the reason is for stopping the statement - wheter it is a constraint violation or an abort just shouldn't matter.

Addtionally, 
if you have a replication, and you isseu Ctrl-C on Master
yuo slave is not receiving the transaction, hence Master and Slave become out of sync. I recommend to always use autocommit=0 for safety.

Regards,
Dmitriy Royzenberg

Additionally, 
if you have a replication, and you issue Ctrl-C on Master
you slave is not receiving the transaction, hence Master and Slave become out of sync. I recommend to always use autocommit=0 for safety.

Regards,
Dmitriy Royzenberg