MySQL Bugs: #54462: Innodb Implicit Commit Not closing the Transaction

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

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

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

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

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?

More info on the same bug:
http://venublog.com/2010/06/12/autocommit-implicit-commit-and-open-transactions/

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)

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.

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.

looks like it's a server level bug, not InnoDB.

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.