Bug #54462 Innodb Implicit Commit Not closing the Transaction
Submitted: 13 Jun 2010 5:16 Modified: 13 Jun 2010 8:12
Reporter: Venu Anuganti Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: General Severity:S3 (Non-critical)
Version:5.0.92, 5.1.49 OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[13 Jun 2010 5:16] Venu Anuganti
Description:
When there is statement that makes a implicit commit; should close any open transactions; but looks like a transaction which is open with autocommit=0 is not closed.

Because of this; the next transaction thinks, there is an open transaction and forces not to execute the statements and fails with an error "Can't execute the given command because you have active locked tables or an active transaction"

How to repeat:
mysql> create table outer_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> insert into outer_tab values(10);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from outer_tab;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql> set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql> create table inner_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.18 sec)

mysql> rename table inner_tab to tmp_x;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction
mysql> 
mysql> drop table if exists tmp_x, outer_tab, inner_tab;
Query OK, 0 rows affected, 1 warning (0.01 sec)

Suggested fix:
Tested with Oracle, works fine.  As autocommit=1 forces any open txn to be committed; there is no reason for rename to think that there is one already open;  and even if its really open, then table is not in use; so it should safely allow to change its meta contents
[13 Jun 2010 5:42] Venu Anuganti
Either set_option_autocommit() in set_var.cc should reset SERVER_STATUS_AUTOCOMMIT after implicit commit (ha_commit()) is successful; if not change the logic in thd->active_transaction() to validate the case against autocommit mode when checking for any open transactions
[13 Jun 2010 5:45] Venu Anuganti
Either set_option_autocommit() in set_var.cc should reset SERVER_STATUS_IN_TRANS after
implicit commit (ha_commit()) is successful; if not change the logic in
thd->active_transaction() to validate the case against autocommit mode when checking for
any open transactions
[13 Jun 2010 7:07] Valeriy Kravchuk
I do not see this problem with current mysql-trunk, for example:

valeriy-kravchuks-macbook-pro:trunk openxs$ bin/mysql -uroot 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 1
Server version: 5.5.5-m3-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>  create table outer_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into outer_tab values(10);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from outer_tab;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql>  set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table inner_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.39 sec)

mysql>  rename table inner_tab to tmp_x;
Query OK, 0 rows affected (0.01 sec)

What exact version(s) you had tested?
[13 Jun 2010 7:18] Venu Anuganti
More info on the same bug:
http://venublog.com/2010/06/12/autocommit-implicit-commit-and-open-transactions/
[13 Jun 2010 7:46] Venu Anuganti
I tested with both 5.0 and 5.1

mysql> select @@version;
+---------------------------------+
| @@version                       |
+---------------------------------+
| 5.1.42-MariaDB-venu-build-debug |
+---------------------------------+
1 row in set (0.00 sec)

and

mysql> select @@version;
+-------------------------+
| @@version               |
+-------------------------+
| 5.0.91-venu-build-debug | 
+-------------------------+
1 row in set (0.00 sec)
[13 Jun 2010 8:02] Venu Anuganti
Quick look at 5.5; I can see that things are completely wrapped and moved to stmt_causes_implicit_commit() and tran_commit_implicit() functions; which does what is needed.
[13 Jun 2010 8:12] Valeriy Kravchuk
Verified just as described on current 5.0.92 from bzr:

valeriy-kravchuks-macbook-pro:5.0 openxs$ bin/mysql -uroot 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 1
Server version: 5.0.92-debug Source distribution

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

mysql>  create table outer_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.04 sec)

mysql> insert into outer_tab values(10);
Query OK, 1 row affected (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from outer_tab;
+------+
| id   |
+------+
|   10 | 
+------+
1 row in set (0.00 sec)

mysql>  set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table inner_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.00 sec)

mysql>  rename table inner_tab to tmp_x;ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

and on current 5.1.49 from bzr (built-in InnoDB):

valeriy-kravchuks-macbook-pro:5.1 openxs$ bin/mysql -uroot 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 1
Server version: 5.1.49-debug Source distribution

Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license

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

mysql>  create table outer_tab(id int)Engine=InnoDB;Query OK, 0 rows affected (0.05 sec)

mysql> insert into outer_tab values(10);
Query OK, 1 row affected (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>  set autocommit=0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from outer_tab;
+------+
| id   |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

mysql>  set autocommit=1;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table inner_tab(id int)Engine=InnoDB;
Query OK, 0 rows affected (0.39 sec)

mysql>  rename table inner_tab to tmp_x;
ERROR 1192 (HY000): Can't execute the given command because you have active locked tables or an active transaction

Not repeatable with current 5.5.x, as I've already demonstrated.
[15 Jun 2010 15:18] Mikhail Izioumtchenko
looks like it's a server level bug, not InnoDB.
[4 Feb 2012 10:34] Sergey Shvets
Reproduced on  5.1.56-log Gentoo Linux mysql-5.1.56

Also noticed that if you explicitly indicate start transaction that bug is not appearing.