MySQL Bugs: #52114: Assertion related to transactional temporary tables (read_only

Bug #52114	Assertion related to transactional temporary tables (read_only_innodb crashes)
Submitted:	16 Mar 2010 20:36	Modified:	13 Sep 2010 10:11
Reporter:	Davi Arnaut (OCA)	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Locking	Severity:	S2 (Serious)
Version:	6.0, 5.6	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	disabled, innodb, lock tables, transactional temporary tables

Description:
Under lock tables, inserting into a transactional temporary table triggers a assertion in mysql_lock_have_duplicate: 

(Side note: the assertion does not kick in the others tree because unique_table skips the check for temporary table -- this check was removed in 6.0 by the patch for Bug#50788).

This function expects that transactional temporary tables will have a lock data (THD::lock::table). This is true when outside locked tables mode, but no lock is taken for temporary tables if under locked tables mode.

It seems to me that this works because under locked tables mode, the InnoDB side is initialized some other way via check_lock_and_start_stmt. 

How to repeat:
-- source include/have_innodb.inc

CREATE TABLE t1 (a INT) ENGINE=INNODB;
CREATE TABLE t2 (a INT) ENGINE=INNODB;
CREATE TEMPORARY TABLE temp1 (a INT) ENGINE=INNODB;
INSERT INTO temp1 VALUES (10);
LOCK TABLES t1 READ, t2 READ;
INSERT INTO temp1 VALUES (10); 

Suggested fix:
Konstantin has some suggestions.

Once this bug is fixed, please re-enable parts of read_only_innodb.test that were disabled due to this bug.

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/104472

3124 Ingo Struewing	2010-03-26
      Bug#52114 - Assertion related to transactional temporary tables
                  (read_only_innodb crashes)
      
      Under lock tables, inserting into a transactional temporary table
      crashed a debug server.
      
      mysql_lock_have_duplicate() was skipped for non-transactional
      temporary tables only. An assert triggered for other kinds of
      temporary tables.
      
      Fixed by extending the check to other temporary tables.
      
      Temporary tables are not locked. The uniqueness of temporary
      tables in a statement is enforced elsewhere.
      
      The fixed check was wrong since the beginning (see Bug#5390,
      http://lists.mysql.com/commits/1507). The problem was masked
      by unique_table(), which skipped temporary tables until the
      new MERGE table implementation required the check (see Bug#50788,
      http://lists.mysql.com/commits/102777).
     @ mysql-test/t/read_only_innodb.test
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Marked the existing tests for this bug.
     @ sql/lock.cc
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Modified two tests to cover all kinds of temporary tables.

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/104502

3124 Ingo Struewing	2010-03-27
      Bug#52114 - Assertion related to transactional temporary tables
                  (read_only_innodb crashes)
      
      Under lock tables, inserting into a transactional temporary table
      crashed a debug server.
      
      mysql_lock_have_duplicate() was skipped for non-transactional
      temporary tables only. An assert triggered for other kinds of
      temporary tables.
      
      Fixed by searching other temporary tables in the lock. If a
      temporary table is not locked, it cannot be checked for
      duplicate locks.
      
      The test suite does already contain proper tests for this bug.
     @ mysql-test/t/read_only_innodb.test
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Marked the existing tests for this bug.
     @ sql/lock.cc
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Added two loops to detect temporary tables that are not locked.

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/109627

3082 Alexander Nozdrin	2010-05-31
      Cumulative patch for Bug#36171 (CREATE TEMPORARY TABLE and MERGE engine) and
      Bug#52114 (Assertion related to transactional temporary tables
      (read_only_innodb crashes)).
      
      Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
      
        In former MySQL versions, up to 5.1.23/6.0.4 it was possible to create
        temporary MERGE tables with non-temporary MyISAM tables.
      
        This has been changed in the mentioned version due to Bug 19627
        (temporary merge table locking). MERGE children were locked through
        the parent table. If the parent was temporary, it was not locked and
        so the children were not locked either. Parallel use of the MyISAM
        tables corrupted them.
      
        Since 6.0.6 (WL 4144 - Lock MERGE engine children), the children are
        locked independently from the parent. Now it is possible to allow
        non-temporary children with a temporary parent. Even though the
        temporary MERGE table itself is not locked, each non-temporary
        MyISAM table is locked anyway.
      
        NOTE: Behavior change: In 5.1.23/6.0.4 we prohibited non-temporary
        children with a temporary MERGE table. Now we re-allow it.
        An important side-effect is that temporary tables, which overlay
        non-temporary MERGE children, overlay the children in the MERGE table.
      
      Bug#52114 - Assertion related to transactional temporary tables
                  (read_only_innodb crashes)
      
      Under lock tables, inserting into a transactional temporary table
      crashed a debug server.
      
      mysql_lock_have_duplicate() was skipped for non-transactional
      temporary tables only. An assert triggered for other kinds of
      temporary tables.
      
      Fixed by searching other temporary tables in the lock. If a
      temporary table is not locked, it cannot be checked for
      duplicate locks.
     @ mysql-test/r/merge.result
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Updated test result.
     @ mysql-test/r/merge_sync.result
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Renamed test ('-' -> '_').
            Added test result.
     @ mysql-test/t/merge.test
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Updated test cases. Temporary MERGE with non-temporary MyISAM is allowed now.
            Added more tests.
     @ mysql-test/t/merge_sync.test
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Renamed test ('-' -> '_').
            Added test case.
     @ mysql-test/t/read_only_innodb.test
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Marked the existing tests for this bug.
     @ sql/lock.cc
        Bug#52114 - Assertion related to transactional temporary tables
                    (read_only_innodb crashes)
        Added two loops to detect temporary tables that are not locked.
     @ storage/myisammrg/ha_myisammrg.cc
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
            Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
            Changed constraint for temporary state of tables.

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/112488

3066 Konstantin Osipov	2010-06-29
      Salvage comments added by Ingo while working on 
      Bug#52114 and Bug#50788.
      The bugs themselves are regressions that are introduced
      by an incomplete fix for Bug#36171 and will not be pushed.

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.5.6-m3 (revid:alik@sun.com-20100731131027-1n61gseejyxsqk5d) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804080001-bny5271e65xo34ig) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:18)

Pushed into mysql-trunk 5.6.1-m4 (revid:alik@ibmvm-20100804081533-c1d3rbipo9e8rt1s) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (merge vers: 5.1.49) (pib:20)

Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)

The bug is not in any released version.