Bug #57657 Temporary MERGE table with temporary underlying is broken by ALTER.
Submitted: 22 Oct 2010 12:43 Modified: 28 Jan 2012 13:39
Reporter: Dmitry Lenev Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.5.7-bzr OS:Any
Assigned to: CPU Architecture:Any
Triage: Triaged: D2 (Serious)

[22 Oct 2010 12:43] Dmitry Lenev
Description:
Attempt to execute ALTER TABLE on temporary MERGE table with temporary underlying table breaks this merge table unless ALTER specifies new list of underlying tables. It also works fine if underlying tables are base tables.

How to repeat:
create temporary table t1_temp(i int);
create temporary table tm_temp_temp (i int) engine=merge union=(t1_temp) insert_method=last;
alter table tm_temp_temp insert_method=first;
check table tm_temp_temp;
# Returns:
#Table   Op      Msg_type        Msg_text
#test.tm_temp_temp       check   Error   Table 'mysqld.1.#sql2207_2_0' doesn't exist
#test.tm_temp_temp       check   Error   Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
#test.tm_temp_temp       check   error   Corrupt
#
# The below SELECT fails with error 1168.
select * from tm_temp_temp
[22 Oct 2010 14:06] Valeriy Kravchuk
Thank you for the bug report. Verified just as described with current mysql-5.5 from bzr on Ubuntu 10.04.
[28 Jan 2012 13:39] Jon Stephens
Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html
[28 Jan 2012 13:40] Jon Stephens
Fixed in 5.6. Documented in the 5.6.5 changelog as follows:

        Attempting to execute ALTER TABLE on a temporary MERGE table
        having an underlying temporary table rendered the MERGE table
        unusable, unless the ALTER TABLE specified a new list of
        underlying tables.

Closed.