Bug #31444 "InnoDB: Error: MySQL is freeing a thd" in innodb_mysql.test
Submitted: 8 Oct 2007 11:42 Modified: 20 Jun 2010 0:50
Reporter: Sergei Golubchik Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB storage engine Severity:S3 (Non-critical)
Version:5.1 OS:Any
Assigned to: Sunny Bains CPU Architecture:Any
Triage: D3 (Medium)

[8 Oct 2007 11:42] Sergei Golubchik
Description:
"InnoDB: Error: MySQL is freeing a thd" in the test case for bug#29154 from mix1.inc (included into innodb_mysql.test)

How to repeat:
./mtr innodb_mysql
[8 Oct 2007 15:21] Sergei Golubchik
forgot to mention (to explain the category):

the test does LOCK t1, t2. first lock succeeds, the second fails. external_lock() isn't called to unlock the table (at least one of them,I didn't debug it), on disconnect THD is destroyed, while a table is marked as "locked", hence the error
[17 Oct 2007 10:21] Davi Arnaut
The error happens because the innodb external_lock fails to properly
cleanup it's internal states on the error path when the LOCK TABLES 
t2 fails. The following test case can be used to reproduce the problem:

CREATE TABLE t2 (a INT) ENGINE=InnoDB; 

CONNECT (c1,localhost,root,,);
CONNECT (c2,localhost,root,,);

--echo switch to connection c1
CONNECTION c1;
SET AUTOCOMMIT=0;
INSERT INTO t2 VALUES (1);

#--echo switch to connection c2
CONNECTION c2;
SET AUTOCOMMIT=0;
--error ER_LOCK_WAIT_TIMEOUT
LOCK TABLES t2 READ;

--echo switch to connection c1
CONNECTION c1;
COMMIT;

--echo switch to connection default
CONNECTION default;
SET AUTOCOMMIT=default;
DISCONNECT c1;
DISCONNECT c2;
DROP TABLE t2;
[17 Oct 2007 10:42] Davi Arnaut
Proposed fix

Attachment: bug-31444.patch (, text), 1.23 KiB.

[17 Oct 2007 10:43] Konstantin Osipov
Index: mysql-5.1-runtime/storage/innobase/handler/ha_innodb.cc
===================================================================
--- mysql-5.1-runtime.orig/storage/innobase/handler/ha_innodb.cc
+++ mysql-5.1-runtime/storage/innobase/handler/ha_innodb.cc
@@ -6387,6 +6387,7 @@ ha_innobase::external_lock(
        int     lock_type)      /* in: lock type */
 {
        trx_t*          trx;
+       ulint           error = 0;

        DBUG_ENTER("ha_innobase::external_lock");
        DBUG_PRINT("enter",("lock_type: %d", lock_type));
@@ -6484,13 +6485,13 @@ ha_innobase::external_lock(
                            && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)
                            && thd_in_lock_tables(thd)) {

-                               ulint   error = row_lock_table_for_mysql(
+                               error = row_lock_table_for_mysql(
                                        prebuilt, NULL, 0);

                                if (error != DB_SUCCESS) {
                                        error = convert_error_code_to_mysql(
                                                (int) error, thd);
-                                       DBUG_RETURN((int) error);
+                                       goto err_cleanup;
                                }
                        }

@@ -6500,6 +6501,7 @@ ha_innobase::external_lock(
                DBUG_RETURN(0);
        }

+err_cleanup:
        /* MySQL is releasing a table lock */

        trx->n_mysql_tables_in_use--;
@@ -6535,7 +6537,7 @@ ha_innobase::external_lock(
                }
        }

-       DBUG_RETURN(0);
+       DBUG_RETURN((int) error);
 }

 /**********************************************************************
[17 Oct 2007 10:45] Konstantin Osipov
Sunny, could you p lease take a look at the proposed patch?
[17 Oct 2007 17:05] Heikki Tuuri
All, trx->n_mysql_tables_in_use++ is ONLY executed after MySQL successfully locks a table in ::external_lock().

And trx->n_mysql_tables_in_use-- should ONLY be executed after MySQL successfully unlocks a table in ::external_lock().

The proposed patch does not work that way. I am afraid it is not correct.
[17 Oct 2007 17:07] Heikki Tuuri
... assuming the code in ::external_lock() is as in our source tree:

                /* Starting from 4.1.9, no InnoDB table lock is taken in LOCK
                TABLES if AUTOCOMMIT=1. It does not make much sense to acquire
                an InnoDB table lock if it is released immediately at the end
                of LOCK TABLES, and InnoDB's table locks in that case cause
                VERY easily deadlocks.

                We do not set InnoDB table locks if user has not explicitly
                requested a table lock. Note that thd_in_lock_tables(thd)
                can hold in some cases, e.g., at the start of a stored
                procedure call (SQLCOM_CALL). */

                if (prebuilt->select_lock_type != LOCK_NONE) {

                        if (thd_sql_command(thd) == SQLCOM_LOCK_TABLES
                            && THDVAR(thd, table_locks)
                            && thd_test_options(thd, OPTION_NOT_AUTOCOMMIT)
                            && thd_in_lock_tables(thd)) {

                                ulint   error = row_lock_table_for_mysql(
                                        prebuilt, NULL, 0);

                                if (error != DB_SUCCESS) {
                                        error = convert_error_code_to_mysql(
                                                (int) error, thd);
                                        DBUG_RETURN((int) error);
                                }
                        }

                        trx->mysql_n_tables_locked++;
                }

                trx->n_mysql_tables_in_use++;
                prebuilt->mysql_has_locked = TRUE;

                DBUG_RETURN(0);
        }

        /* MySQL is releasing a table lock */

        trx->n_mysql_tables_in_use--;
        prebuilt->mysql_has_locked = FALSE;

        /* Release a possible FIFO ticket and search latch. Since we
        may reserve the kernel mutex, we have to release the search
        system latch first to obey the latching order. */

        innobase_release_stat_resources(trx);

        /* If the MySQL lock count drops to zero we know that the current SQL
        statement has ended */

        if (trx->n_mysql_tables_in_use == 0) {

                trx->mysql_n_tables_locked = 0;
                prebuilt->used_in_HANDLER = FALSE;

...
[17 Oct 2007 18:33] Davi Arnaut
Wrong assumption.

Talking to Sunny Bains by e-mail, it seems this issue has already been solved in the InnoDB tree:

r1809 | marko | 2007-09-05 19:32:36 +0530 (Wed, 05 Sep 2007) | 5 lines

ha_innobase::external_lock(): Update prebuilt->mysql_has_locked and
trx->n_mysql_tables_in_use only after row_lock_table_for_mysql()
returns DB_SUCCESS.  A timeout on LOCK TABLES would lead to an
inconsistent state, which would cause trx_free() to print a warning.
[7 Nov 2007 1:03] Timothy Smith
Queued to 5.1-build
[21 Nov 2007 18:54] Bugs System
Pushed into 5.1.23-rc
[21 Nov 2007 18:55] Bugs System
Pushed into 6.0.4-alpha
[8 Jan 2008 17:16] Paul Dubois
Noted in 5.1.23, 6.0.4 changelogs.

InnoDB now tracks locking and use of tables by MySQL only after a
table has been successfully locked on behalf of a transaction.
Previously, the locked flag was set and the table in-use counter was
updated before checking whether the lock on the table succeeded. A
subsequent failure in obtaining a lock on the table led to an
inconsistent state as the table was neither locked nor in use.
[5 May 2010 15:21] Bugs System
Pushed into 5.1.47 (revid:joro@sun.com-20100505145753-ivlt4hclbrjy8eye) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[6 May 2010 3:05] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug. Re-closing.
[28 May 2010 6:03] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100524190136-egaq7e8zgkwb9aqi) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (pib:16)
[28 May 2010 6:32] Bugs System
Pushed into 6.0.14-alpha (revid:alik@sun.com-20100524190941-nuudpx60if25wsvx) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[28 May 2010 6:59] Bugs System
Pushed into 5.5.5-m3 (revid:alik@sun.com-20100524185725-c8k5q7v60i5nix3t) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[29 May 2010 22:57] Paul Dubois
Push resulted from incorporation of InnoDB tree. No changes pertinent to this bug.
Re-closing.
[17 Jun 2010 12:07] Bugs System
Pushed into 5.1.47-ndb-7.0.16 (revid:martin.skold@mysql.com-20100617114014-bva0dy24yyd67697) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 12:53] Bugs System
Pushed into 5.1.47-ndb-6.2.19 (revid:martin.skold@mysql.com-20100617115448-idrbic6gbki37h1c) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)
[17 Jun 2010 13:34] Bugs System
Pushed into 5.1.47-ndb-6.3.35 (revid:martin.skold@mysql.com-20100617114611-61aqbb52j752y116) (version source revid:vasil.dimov@oracle.com-20100331130613-8ja7n0vh36a80457) (merge vers: 5.1.46) (pib:16)