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
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[17 Apr 2008 7:52] jerome billet
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)
[17 Apr 2008 10:28] Susanne Ebrecht
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.
[2 Oct 2008 15:49] Konstantin Osipov
This is a regression!
[12 Mar 2009 18:18] Ingo Strüwing
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.
[16 Mar 2009 18:52] Shawn Green
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)
[20 Apr 2009 17:04] Ingo Strüwing
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.
[2 Jun 2009 15:58] Ingo Strüwing
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.
[17 Jun 2009 10:27] Konstantin Osipov
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.
[30 Jul 2009 14:44] 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/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.
[3 Oct 2009 10:14] Konstantin Osipov
A review sent by email.
[28 Oct 2009 18:37] 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/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.
[28 Oct 2009 22:06] Ingo Strüwing
Queued to mysql-6.0-backup.
[29 Oct 2009 9:14] Ingo Strüwing
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.
[16 Feb 2010 16:07] 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/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".
[20 Feb 2010 9:19] Bugs System
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)
[25 Feb 2010 9:52] Tony Bedford
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
[5 Mar 2010 15:26] 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/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.
[26 May 2010 9:02] Ingo Strüwing
Back to verified since it had been fixed in 6.0 only, which is abandoned.
[31 May 2010 14:07] 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/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.
[29 Jun 2010 14:31] 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/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.
[2 Jul 2010 16:08] 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/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.
[2 Jul 2010 16:14] Konstantin Osipov
Queued in 5.5.6 (trunk-runtime).
[4 Aug 2010 7:52] Bugs System
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)
[4 Aug 2010 9:00] Bugs System
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)
[4 Aug 2010 9:06] Bugs System
Pushed into mysql-next-mr (revid:alik@ibmvm-20100804081630-ntapn8bf9pko9vj3) (version source revid:marko.makela@oracle.com-20100621094008-o9fa153s3f09merw) (pib:20)
[13 Aug 2010 16:28] Tony Bedford
Also added to 5.5.6 changelog.