MySQL Bugs: #36171: CREATE TEMPORARY TABLE and MERGE engine

Bug #36171	CREATE TEMPORARY TABLE and MERGE engine
Submitted:	17 Apr 2008 7:52	Modified:	13 Aug 2010 16:28
Reporter:	jerome billet	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.1.23-rc,5.1.30	OS:	Any (Solaris 9, Ubuntu AMD64)
Assigned to:	Ingo Strüwing	CPU Architecture:	Any

Description:
Can't create a temporary table using MERGE engine from MyISAM tables.

I can create a temporary table.
I can create a merge (but not temporary) table.

But creating a temporary table and MERGE result in error 1168.

How to repeat:
mysql> CREATE TABLE t1 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TABLE t2 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> CREATE TEMPORARY TABLE t3 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t3;
Empty set (0.01 sec)

mysql> CREATE TABLE t4 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION(t1,t2);
Query OK, 0 rows affected (0.04 sec)

mysql> select * from t4;
Empty set (0.00 sec)

mysql> CREATE TEMPORARY TABLE t5 (a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MERGE UNION(t1,t2);
Query OK, 0 rows affected (0.01 sec)

mysql> select * from t5;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
Empty set (0.00 sec)

Many thanks for writing a bug report.

Verified as described by using the given statements and by using 5.1-bk tree and Ubuntu 7.10 amd64.

This is a regression!

Either all MyISAM *and* the MERGE table must be temporary, or all must be non-temporary.

The behavior change is described in the patch and in the changelog (5.1.23, 6.0.4) for Bug#26379 (Combination of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table).

Docs team, please include this in the user manual's description of the MERGE engine.

I disagree that this bug, without a workaround, can be closed with a simple change to the documentation. 

There are known users currently using temporary MERGE tables on 5.1 covering permanent base tables that will be stranded at 5.0 unless we provide some way to migrate forward. 

At the very least the decision to deprecate the use of TEMPORARY ENGINE=MERGE tables for any non-TEMPORARY tables needs greater explanation and it should not happen for 5.1 as it is not the second version beyond which the original behavior was permitted (5.0)

We had some internal discussions and I made some experiments. It was suggested to either

A) allow TEMPORARY MERGE to have permanenet children and to lock them as usual, or

B) handle TEMPORARY MERGE with permanent children as a permanent MERGE table, which gets a session-local name like temporary tables and is implicitly dropped at session end.

These options need to be approved by the architects before implementation. However, a respective request has not yet been made with the architects. I hope to find time for it.

Hi Architects,

please decide on the following implementation request:

Support team is unhappy with the change introduced with Bug#19627 (temporary merge table locking): Creation of tables with non-temporary children of a temporary MERGE table is now prohibited.

For a customer, they want something like the old behavior back in 5.1 (Issue#33677, Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine).

Support proposes either:

1) Make TEMPORARY tables lockable so that TEMPORARY MERGE tables will respect the locks held on their base tables

or

2) Extend MERGE tables to allow TEMPORARY behavior. By this I mean that a TEMPORARY MERGE table would have the following additional behaviors not normally found in a MERGE table:

2a) Names are aliased so that they are have connection scope. Any two connections can create their own copy of the same named MERGE table but only one copy of each name is permitted within the same connection.

2b) When the connection that creates a TEMPORARY MERGE table is destroyed, it takes all of its TEMPORARY MERGE tables with it.

Discussed with Ingo, that would be too difficult to fix in 5.1 (E4/R3).
In 5.4, where we have implementation of WL#3726, the fix could be much simpler. 
Suggest to re-triage for 5.4, where risk/effort are more adequate.

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

2851 Ingo Struewing	2009-07-30
      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.
     @ mysql-test/r/merge.result
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Updated test result.
     @ mysql-test/r/merge_sync.result
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Renamed test ('-' -> '_').
        Added test result.
     @ mysql-test/t/merge.test
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Updated test cases. Temporary MERGE with non-temporary MyISAM is allowed now.
     @ mysql-test/t/merge_sync.test
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Renamed test ('-' -> '_').
        Added test case.
     @ storage/myisammrg/ha_myisammrg.cc
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Changed constraint for temporary state of tables.

A review sent by email.

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

2851 Ingo Struewing	2009-10-28
      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.
     @ mysql-test/r/merge.result
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Updated test result.
     @ mysql-test/r/merge_sync.result
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Renamed test ('-' -> '_').
        Added test result.
     @ mysql-test/t/merge.test
        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
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Renamed test ('-' -> '_').
        Added test case.
     @ storage/myisammrg/ha_myisammrg.cc
        Bug#36171 - CREATE TEMPORARY TABLE and MERGE engine
        Changed constraint for temporary state of tables.

Queued to mysql-6.0-backup.

A note to the NOTE in the revision comment: The re-allowance of permanent children with a temporary MERGE table is done in 6.0 only. 5.1 does not have the required child locking scheme. This patch may be back ported into some 5.x version later, when the locking scheme is, but x will probably be >= 5.

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

3101 Ingo Struewing	2010-02-16
      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.
      
      This leaves merge.test with failures, which are to be fixed by later backports.
     @ 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.
     @ sql/item_func.cc
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
        Added Debug Sync Point "before_acos_function".
     @ sql/mdl.cc
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
        Added Debug Sync Point "mdl_wait_table".
     @ sql/sql_table.cc
        Backport of revid:ingo.struewing@sun.com-20091028183659-6kmv1k3gdq6cpg4d
        Added Debug Sync Point "after_admin_flush".
     @ 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.
        Added Debug Sync Point "before_myisammrg_attach" and
        "before_myisammrg_store_lock".

Pushed into 6.0.14-alpha (revid:ingo.struewing@sun.com-20100218152520-s4v1ld76bif06eqn) (version source revid:ingo.struewing@sun.com-20100119103538-wtp5alpz4p2jayl5) (merge vers: 6.0.14-alpha) (pib:16)

A note has been added to the MERGE section of the reference manual (5.1/5.4/5.5/6.0).

An entry has been added to the 6.0.14 changelog:

Reading from a temporary MERGE table, with two non-temporary
child MyISAM tables, resulted in the error:

ERROR 1168 (HY000): Unable to open underlying table which is differently 
defined or of non-MyISAM type or doesn't exist

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

3123 Ingo Struewing	2010-03-05
      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.
      
      This leaves merge.test failing. To be fixed by later backports.
     @ 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.
     @ 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.

Back to verified since it had been fixed in 6.0 only, which is abandoned.

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.

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

3075 Konstantin Osipov	2010-07-02
      A fix and a test case for Bug#36171 "CREATE TEMPORARY TABLE and 
      MERGE engine".
      Backport the patch from 6.0 by Ingo Struewing:
      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.
     @ mysql-test/r/merge.result
        Update results (Bug#36171).
     @ mysql-test/r/merge_mmap.result
        Update results (Bug#36171).
     @ mysql-test/t/merge.test
        Add tests for Bug#36171
     @ mysql-test/t/merge_mmap.test
        Add tests for Bug#36171.
     @ storage/myisammrg/ha_myisammrg.cc
        Changed constraint for temporary state of tables.

Queued in 5.5.6 (trunk-runtime).

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-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)

Also added to 5.5.6 changelog.