Bug #54453 Failing assertion: trx->active_trans when renaming a table with active trx
Submitted: 12 Jun 2010 6:04 Modified: 15 Oct 2010 10:53
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S1 (Critical)
Version:5.1.40-debug, 5.1.47-debug OS:Any
Assigned to: Davi Arnaut CPU Architecture:Any

[12 Jun 2010 6:04] Shane Bester
Description:
could be related to bug #53798
if you alter table .. rename to .. on a table that has an active transaction open and UNIV_DEBUG is defined, mysqld crashes:

100604 10:35:46  InnoDB: Assertion failure in thread 1191770432 in file handler/ha_innodb.cc line 9665
InnoDB: Failing assertion: trx->active_trans

How to repeat:
edit ./storage/innobase/include/univ.i and change
"#if 0" to "#if 1"

to enable extra debugging defines.  compile debug server, and start with --log-bin

connection1:
-------------
create table t1(a int)engine=innodb;
start transaction;
insert into t1 values (1);

connection2:
-------------
alter table t1 rename to t2; #crashes

Suggested fix:
5.5 fixes this already.  Here's what happens in 5.5.4:

+------+-------------------+-----------------------------+
|    0 | NULL              | show processlist            |
|    8 | Waiting for table | alter table t1 rename to t2 |
+------+-------------------+-----------------------------+
[15 Jun 2010 6:17] MySQL Verification Team
call stack for assertion is:

./sql/mysqld(my_print_stacktrace+0x29)
./sql/mysqld(handle_segfault+0x40a)
/lib64/libpthread.so.0
/lib64/libc.so.6(gsignal+0x35)
/lib64/libc.so.6(abort+0x110)
./sql/mysqld <?>
./sql/mysqld(ha_commit_trans(THD*, bool)
./sql/mysqld(ha_autocommit_or_rollback(THD*, int)
./sql/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)
./sql/mysqld(do_command(THD*)+0x203)
./sql/mysqld(handle_one_connection+0x276)
[15 Jun 2010 12:26] MySQL Verification Team
not a recent regression afterall.
here are my test results of debug builds with UNIV_DEBUG.
seems specific to 5.1.x and the innodb plugin.

5.1.47 + plugin  = crash
5.1.47 + builtin = no crash

5.1.46 + plugin  = crash
5.1.46 + builtin = no crash

5.1.45 + plugin  = crash
5.1.45 + builtin = no crash

5.1.40 + plugin  = crash
5.1.40 + builtin = no crash
[15 Jun 2010 13:08] Marko Mäkelä
I can repeat this bug:

cat > mysql-test/suite/innodb_plugin/t/innodb_bug54453-master.opt << EOF
--log-bin
EOF
cat > mysql-test/suite/innodb_plugin/t/innodb_bug54453.test << EOF
-- source include/have_innodb_plugin.inc

create table bug54453(a int)engine=innodb;
start transaction;
insert into bug54453 values (1);

-- connect (con1,localhost,root,,)

alter table bug54453 rename to bug54453_2;

-- connection default
-- disconnect con1
rollback;
select * from bug54453_2;
drop table bug54453_2;
EOF
[15 Jun 2010 13:25] Marko Mäkelä
It looks like the bug is that mysql_alter_table() is invoking ha_innobase::external_lock(F_WRLCK) and then ha_innobase::external_lock(F_UNLCK) before calling ha_innobase::rename_table(). The F_UNLCK would duly trigger a transaction commit, which will cause the assertion failure later on. The F_UNLCK happens in close_cached_table() in mysql_alter_table(), sql/sql_table.cc:

      /*
        Then do a 'simple' rename of the table. First we need to close all
        instances of 'source' table.
      */
      close_cached_table(thd, table);
      /*
        Then, we want check once again that target table does not exist.
        Actually the order of these two steps does not matter since
        earlier we took name-lock on the target table, so we do them
        in this particular order only to be consistent with 5.0, in which
        we don't take this name-lock and where this order really matters.
        TODO: Investigate if we need this access() check at all.
      */
      if (!access(new_name_buff,F_OK))
      {
	my_error(ER_TABLE_EXISTS_ERROR, MYF(0), new_name);
	error= -1;
      }
      else
      {
	*fn_ext(new_name)=0;
	if (mysql_rename_table(old_db_type,db,table_name,new_db,new_alias, 0))
	  error= -1;
[13 Jul 2010 14:56] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113473

3478 Davi Arnaut	2010-07-13
      Bug#54453: Failing assertion: trx->active_trans when renaming a
                 table with active trx
      
      Essentially, the problem is that InnoDB does a implicit commit
      when a cursor (table handler) is unlocked/closed, creating
      a dissonance between the transaction state within the server
      layer and the storage engine layer. Theoretically, a statement
      transaction can encompass several table instances in a similar
      manner to a multiple statement transaction, hence it does not
      make sense to limit a statement transaction to the lifetime of
      the table instances (cursors) used within it.
      
      Since this particular instance of the problem is only triggerable
      on 5.1 and is masked on 5.5 due to metadata locks, the solution
      (which is less risky) is to explicitly end the transaction before
      the cached table is unlock on rename table.
      
      The patch is to be null merged into trunk and the bug left open
      to address the underlying problem in later versions.
     @ mysql-test/include/commit.inc
        Fix counters, transaction is explicitly ended now.
     @ mysql-test/suite/innodb_plugin/r/innodb_bug54453.result
        Add test case result for Bug#54453
     @ mysql-test/suite/innodb_plugin/t/innodb_bug54453.test
        Add test case for Bug#54453
     @ sql/sql_table.cc
        End transaction as otherwise InnoDB will end it behind our backs.
[20 Jul 2010 17:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/113966

3493 Davi Arnaut	2010-07-20
      Bug#54453: Failing assertion: trx->active_trans when renaming a
                 table with active trx
      
      Essentially, the problem is that InnoDB does a implicit commit
      when a cursor (table handler) is unlocked/closed, creating
      a dissonance between the transaction state within the server
      layer and the storage engine layer. Theoretically, a statement
      transaction can encompass several table instances in a similar
      manner to a multiple statement transaction, hence it does not
      make sense to limit a statement transaction to the lifetime of
      the table instances (cursors) used within it.
      
      Since this particular instance of the problem is only triggerable
      on 5.1 and is masked on 5.5 due 2PC being skipped (assertion is in
      the prepare phase of a 2PC), the solution (which is less risky) is
      to explicitly end the transaction before the cached table is unlock
      on rename table.
      
      The patch is to be null merged into trunk.
     @ mysql-test/include/commit.inc
        Fix counters, the binlog engine does not get involved anymore.
     @ mysql-test/suite/innodb_plugin/r/innodb_bug54453.result
        Add test case result for Bug#54453
     @ mysql-test/suite/innodb_plugin/t/innodb_bug54453.test
        Add test case for Bug#54453
     @ sql/sql_table.cc
        End transaction as otherwise InnoDB will end it behind our backs.
[20 Jul 2010 17:52] Davi Arnaut
Queued to mysql-5.1-bugteam, null merged into trunk.
[4 Aug 2010 7:50] Bugs System
Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:alik@sun.com-20100731074942-o840woifuqioxxe4) (merge vers: 5.5.6-m3) (pib:18)
[4 Aug 2010 8:02] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 8:18] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (merge vers: 5.6.99-m4) (pib:18)
[4 Aug 2010 9:01] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:alik@sun.com-20100731075120-qz9z8c25zum2wgmm) (pib:20)
[4 Aug 2010 17:39] John Russell
Added to 5.1.50 and 5.5.6 change log:

The database server could crash when renaming a table that had active transactions.
(This issue only affected the database server when built for debugging.)
[19 Aug 2010 15:40] Bugs System
Pushed into mysql-5.1 5.1.51 (revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (version source revid:build@mysql.com-20100819151858-muaaor6jojb5ouzj) (merge vers: 5.1.51) (pib:20)
[14 Oct 2010 8:26] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:41] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 8:56] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 10:53] Jon Stephens
Already documented in the 5.1.50 changelog. No new changelog entries required. Reverting to Closed state.