Bug #2422 KILL for threads in autocommit mode lead to partially completed transaction.
Submitted: 16 Jan 2004 4:13 Modified: 4 Mar 2004 7:58
Reporter: Sergey Kostyliov Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:4.0.17 OS:Linux (Linux (Gentoo v1.4))
Assigned to: Michael Widenius CPU Architecture:Any

[16 Jan 2004 4:13] Sergey Kostyliov
Description:
The last statement had not been correctly rolled back for transactional table 
(InnoDB) if thread was killed in autocommit mode. This lead to the partially 
completed transaction and so unpredictable state of data from user angle of 
view. 

How to repeat:
Unfortunately I can't provide a simple "mysql test < tc.sql" testcase because 
this is works only for two threads and in some way depends on timings. I 
beleave the table should be big enough to reproduce this.  
 
 
mysql> SET AUTOCOMMIT=1; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> SELECT COUNT(shop_id) FROM shop_product WHERE shop_id=24; 
+----------------+ 
| COUNT(shop_id) | 
+----------------+ 
|         312913 | 
+----------------+ 
1 row in set (0.00 sec) 
 
mysql> DELETE FROM shop_product WHERE shop_id=24; 
 
### Second thread 
mysql> SHOW PROCESSLIST; 
+----+-------------+-----------+------+---------+------+----------+-------------------------------------------+ 
| Id | User        | Host      | db   | Command | Time | State    | Info                                      
| 
+----+-------------+-----------+------+---------+------+----------+-------------------------------------------+ 
| 31 | root        | localhost | test | Query   | 0    | NULL     | SHOW 
PROCESSLIST                          | 
| 33 | rathamahata | localhost | test | Query   | 14   | updating | DELETE 
FROM shop_product WHERE shop_id=24 | 
+----+-------------+-----------+------+---------+------+----------+-------------------------------------------+ 
2 rows in set (0.00 sec) 
 
mysql> KILL 33; 
Query OK, 0 rows affected (0.00 sec) 
 
mysql> 
### End of second thread 
 
ERROR 1053: Server shutdown in progress 
mysql> SELECT COUNT(shop_id) FROM shop_product WHERE shop_id=24; 
ERROR 2006: MySQL server has gone away 
No connection. Trying to reconnect... 
Connection id:    34 
Current database: test 
 
+----------------+ 
| COUNT(shop_id) | 
+----------------+ 
|         308353 | 
+----------------+ 
1 row in set (7.75 sec) 
 
mysql>
[17 Jan 2004 12:03] MySQL Verification Team
Thank you for the bug report.

We have a fix, but we only have to check if the behaviour you report 
should be changed.
[22 Jan 2004 12:13] MySQL Verification Team
Patch approved and commit is pushed.
[17 Feb 2004 4:23] Sergey Kostyliov
Unfortunately, the behavior I report hasn't changed in mysql-4.0.18. I saw a 
commit in 4.1 tree: 
 
"ChangeSet 
  1.1684 04/01/22 22:13:24 Sinisa@sinisa.nasamreza.org +4 -0 
  Fix for a bug #2422, where CONVERT(expr, cast) was masked by 
  CONVERT(expr, expr, expr);" 
 
I could be wrong, but I beleave it doesn't really fix #2422. Did I read 
something wrong? 
 
Thank you.
[17 Feb 2004 7:00] Sergei Golubchik
no, you are right, this commit was for different bug, this bug number in changeset comment is a typo.

A fix for this bug unfortunately did not get into 4.0.18 :(
It did not pass code review yet
[4 Mar 2004 7:58] Michael Widenius
Thank you for your bug report. This issue has been committed to our
source repository of that product and will be incorporated into the
next release.

If necessary, you can access the source repository and build the latest
available version, including the bugfix, yourself. More information 
about accessing the source trees is available at
    http://www.mysql.com/doc/en/Installing_source_tree.html

Additional info:

The fix will appear in 4.0.19 and 4.1.2

(I fixed this problem in single-table-delete, multi-table-delete and single-table-updates; Other cases looked fine)