Bug #25439 trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED
Submitted: 5 Jan 2007 17:22 Modified: 19 Feb 2013 1:46
Reporter: Heikki Tuuri Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.0.86, 5.1.40, 5.4.3 OS:Any
Assigned to: Vasil Dimov CPU Architecture:Any
Triage: Triaged: D3 (Medium)

[5 Jan 2007 17:22] Heikki Tuuri
Description:
Jason Williams reported this on the General mailing list:

> >> > 061228 19:02:55 [ERROR] trx->active_trans == 0, but trx->conc_state !=
> >> > TRX_NOT_STARTED
> >> > 061228 19:02:55 [Warning] MySQL is closing a connection that has an
> >> > active InnoDB transaction.  0 row modifications will roll back. 

How to repeat:
This may be associated with a deadlock and auto-inc columns.

Suggested fix:
Need to check that the auto-increment code in ha_innodb.cc sets trx->active_trans correctly. Also other places in ha_innodb.cc.
[27 Jul 2007 9:46] Shane Bester
Heikki, I have a testcase, uploading it shortly.
[27 Jul 2007 9:53] Shane Bester
testcase, revealing a few problems

Attachment: bug20090.c (text/x-csrc), 9.63 KiB.

[27 Jul 2007 10:02] Shane Bester
I noticed a few problems when running the testcase, depending on which server version and number of threads I ran with:

1) all threads except one of them seem to hang forever.

2) rarely got message: trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED

3) rarely got message: InnoDB: Error: trying to declare trx to enter InnoDB

4) testcase fails on some queries with message: 1105 (Unknown error)
[27 Jul 2007 10:30] Shane Bester
Regarding the "unknown error", thrown by this:

query failed 'insert ignore into t2(id) select max(id)+1 from t1' : 1105 (Unknown error)

The select part gets a deadlock, and therefore the insert fails. But the error isn't given back to the client, so we just get 1105.  This may be another bug because how will a client detect it?
[20 Aug 2007 10:19] Shane Bester
testcase.  alas!  I repeat it on 5.0.46 within 1 minute, but 5.0.48 error doesn't happen. bug fixed already?

Attachment: bug25439.c (text/plain), 8.84 KiB.

[16 Jan 2008 13:56] Heikki Tuuri
There are some changes in the code that may explain how the bug disappeared in 5.0.48. Marking this as Can't repeat until we get more reports, if ever.

heikki@ws35:~/mysql-5.0-new/sql$ diff -bu ~/mysql-5.0.38/sql ha_innodb.cc
--- /home/heikki/mysql-5.0.38/sql/ha_innodb.cc  2007-02-21 14:50:30.000000000 +0200
+++ ha_innodb.cc        2007-11-20 19:38:06.000000000 +0200
@@ -15,7 +15,7 @@

 /* This file defines the InnoDB handler: the interface between MySQL and InnoDB
 NOTE: You can only use noninlined InnoDB functions in this file, because we
-have disables the InnoDB inlining in this file. */
+have disabled the InnoDB inlining in this file. */

 /* TODO list for the InnoDB handler in 5.0:
   - Remove the flag trx->active_trans and look at the InnoDB
@@ -174,6 +174,7 @@
 my_bool innobase_locks_unsafe_for_binlog        = FALSE;
 my_bool innobase_rollback_on_timeout           = FALSE;
 my_bool innobase_create_status_file            = FALSE;
+my_bool innobase_adaptive_hash_index           = TRUE;

 static char *internal_innobase_data_file_path  = NULL;

@@ -455,9 +456,7 @@
                tell it also to MySQL so that MySQL knows to empty the
                cached binlog for this transaction */

-               if (thd) {
-                       ha_rollback(thd);
-               }
+                mark_transaction_to_rollback(thd, TRUE);

                return(HA_ERR_LOCK_DEADLOCK);

@@ -467,9 +466,8 @@
                latest SQL statement in a lock wait timeout. Previously, we
                rolled back the whole transaction. */

-               if (thd && row_rollback_on_timeout) {
-                       ha_rollback(thd);
-               }
+                mark_transaction_to_rollback(thd,
+                                             (bool)row_rollback_on_timeout);

                return(HA_ERR_LOCK_WAIT_TIMEOUT);

@@ -504,7 +502,7 @@

        } else if (error == (int) DB_TABLE_NOT_FOUND) {

-               return(HA_ERR_KEY_NOT_FOUND);
+               return(HA_ERR_NO_SUCH_TABLE);

        } else if (error == (int) DB_TOO_BIG_RECORD) {

@@ -521,11 +519,12 @@
                tell it also to MySQL so that MySQL knows to empty the
                cached binlog for this transaction */

-               if (thd) {
-                       ha_rollback(thd);
-               }
+                mark_transaction_to_rollback(thd, TRUE);

                return(HA_ERR_LOCK_TABLE_FULL);
+       } else if (error == DB_UNSUPPORTED) {
+
+               return(HA_ERR_UNSUPPORTED);
        } else {
                return(-1);                     // Unknown error
        }
...
@@ -6574,6 +6651,13 @@
                                                TL_IGNORE */
 {
        row_prebuilt_t* prebuilt        = (row_prebuilt_t*) innobase_prebuilt;
+       trx_t*          trx;
+
+       /* Note that trx in this function is NOT necessarily prebuilt->trx
+       because we call update_thd() later, in ::external_lock()! Failure to
+       understand this caused a serious memory corruption bug in 5.1.11. */
+
+       trx = check_trx_exists(thd);

        /* NOTE: MySQL  can call this function with lock 'type' TL_IGNORE!
        Be careful to ignore TL_IGNORE if we are going to do something with
@@ -6605,7 +6689,7 @@
                used. */

                if (srv_locks_unsafe_for_binlog &&
-                   prebuilt->trx->isolation_level != TRX_ISO_SERIALIZABLE &&
+                   trx->isolation_level != TRX_ISO_SERIALIZABLE &&
                    (lock_type == TL_READ || lock_type == TL_READ_NO_INSERT) &&
                    (thd->lex->sql_command == SQLCOM_INSERT_SELECT ||
                     thd->lex->sql_command == SQLCOM_UPDATE ||
@@ -6683,17 +6767,6 @@
                    && !thd->tablespace_op
                    && thd->lex->sql_command != SQLCOM_TRUNCATE
                    && thd->lex->sql_command != SQLCOM_OPTIMIZE
-
-#ifdef __WIN__
-                /* For alter table on win32 for succesful operation
-                completion it is used TL_WRITE(=10) lock instead of
-                TL_WRITE_ALLOW_READ(=6), however here in innodb handler
-                TL_WRITE is lifted to TL_WRITE_ALLOW_WRITE, which causes
-                race condition when several clients do alter table
-                simultaneously (bug #17264). This fix avoids the problem. */
-                   && thd->lex->sql_command != SQLCOM_ALTER_TABLE
-#endif
-
                    && thd->lex->sql_command != SQLCOM_CREATE_TABLE) {

                        lock_type = TL_WRITE_ALLOW_WRITE;
[13 Jun 2008 6:08] Shane Bester
I saw this today on 5.1.26-debug :

080613  7:37:51 [ERROR] trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED
080613  7:37:51 [Warning] MySQL is closing a connection that has an active InnoDB transaction.  0 row modifications will roll back.

was doing tests with innodb tables and views. no testcase yet, as I didn't notice the error until later.
[23 Oct 2009 6:36] Shane Bester
testcase:

drop table if exists t1,t2;
create table t1(col60 char(13),key(col60))engine=innodb;
create table t2(col12 datetime,key(col12))engine=innodb;
insert into t1 values (),();
insert into t2 values (),();
create or replace view v1 as
select 1
from t2 where  `col12` =(select `col60` from t1 limit 1);
show create table v1;

Now shutdown mysqld and check error log:
Version: '5.1.40-enterprise-gpl-advanced-debug'  socket: ''  port: 3306  MySQL Enterprise Server - Advanced Edition Debug (GPL)
091023  8:35:25 [ERROR] trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED
091023  8:35:25 [Warning] MySQL is closing a connection that has an active InnoDB transaction.  0 row modifications will roll back.
[23 Oct 2009 6:40] Valeriy Kravchuk
Verified with recent test case from Shane:

Version: '5.0.86-community-nt-log'  socket: ''  port: 3308  MySQL Community Edition (GPL)
091023  9:38:06 [ERROR] trx->active_trans == 0, but trx->conc_state != TRX_NOT_STARTED
091023  9:38:06 [Warning] MySQL is closing a connection that has an active InnoDB transaction.  0 row modifications will roll back.
091023  9:38:08 [Note] C:\Program Files\MySQL\MySQL Server 5.0\bin\mysqld-nt: Normal shutdown
[19 Feb 2013 1:46] John Russell
Added to changelog for 5.1.41: 

The SHOW CREATE TABLE statement could result in a serious error for
some views, especially views involving comparisons between
date/datetime/timestamp columns and strings returned by scalar
subqueries.
[1 Apr 2016 9:29] Albert Navarro
Could it be possible that in version 5.1.48 this bug is not fixed?

We are currently working with version 5.1.48 and we are getting the error periodically. 

regards,