| Bug #60140 | CREATE TEMPORARY TABLE X ( F int ) ENGINE=MRG_MyISAM UNION=(NonTemp) fails | ||
|---|---|---|---|
| Submitted: | 16 Feb 2011 11:17 | Modified: | 22 Feb 2011 16:06 |
| Reporter: | James Michael DuPont | Email Updates: | |
| Status: | Not a Bug | Impact on me: | |
| Category: | MySQL Server: DDL | Severity: | S2 (Serious) |
| Version: | 5.1.49-1ubuntu8.1 | OS: | Linux |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | MRG_MyISAM, temporary, UNION | ||
[16 Feb 2011 11:34]
MySQL Verification Team
Thank you for the bug report.
C:\DBS>c:\dbs\5.0\bin\mysql -uroot --port=3500 --prompt="mysql 5.0 > "
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.92-Win X64 Source distribution
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.0 > use test
Database changed
mysql 5.0 > CREATE TABLE TESTtest_i4 (
-> `TESTid` int(10) unsigned
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.05 sec)
mysql 5.0 >
mysql 5.0 > drop TABLE if exists TESTtest4;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql 5.0 > CREATE TEMPORARY TABLE TESTtest4 (
-> `TESTid` int(10) unsigned
-> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(TESTtest_i4);
Query OK, 0 rows affected (0.02 sec)
mysql 5.0 > desc TESTtest_i4;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| TESTid | int(10) unsigned | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
1 row in set (0.01 sec)
mysql 5.0 > desc TESTtest4;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| TESTid | int(10) unsigned | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
1 row in set (0.00 sec)
mysql 5.0 >
C:\DBS>c:\dbs\5.1\bin\mysql -uroot --port=3306 --prompt="mysql 5.1 >"
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.56-Win X64-debug-log Source distribution
Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL v2 license
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql 5.1 >use d1
Database changed
mysql 5.1 >CREATE TABLE TESTtest_i4 (
-> `TESTid` int(10) unsigned
-> ) ENGINE=MyISAM DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.12 sec)
mysql 5.1 >
mysql 5.1 >drop TABLE if exists TESTtest4;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql 5.1 >CREATE TEMPORARY TABLE TESTtest4 (
-> `TESTid` int(10) unsigned
-> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(TESTtest_i4);
Query OK, 0 rows affected (0.02 sec)
mysql 5.1 >desc TESTtest_i4;
+--------+------------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+-------+
| TESTid | int(10) unsigned | YES | | NULL | |
+--------+------------------+------+-----+---------+-------+
1 row in set (0.05 sec)
mysql 5.1 >desc TESTtest4;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql 5.1 >
[16 Feb 2011 17:53]
MySQL Verification Team
http://dev.mysql.com/doc/refman/5.1/en/merge-table-problems.html "The reintroduction of support for non-tempporary children with a temporary MERGE table was completed in 6.0.14. Note that 5.1.23 onwards does not currently have the child locking scheme required to support this. " is it still a bug, or just a known issue?
[22 Feb 2011 16:06]
Dmitry Lenev
Hello James, Miguel and Shane! This is not a bug but rather expected and correct behavior. Documentation clearly says that in 5.1 we no longer support temporary merge tables over non-temporary children. Even although they were accepted in 5.0 and early versions of 5.1 series they have never worked properly. See: http://dev.mysql.com/doc/refman/5.1/en/merge-table-problems.html In 5.5 we have fixed issue with locking and temporary merge tables over base children were enabled again. Therefore I am closing this as "Not a bug".
Description: When I create a temporary union table of a normal table it works on 5.0.77 but fails on 5.1.49-1ubuntu8.1. Error message is : mysql> desc TESTtest4; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist How to repeat: drop TABLE if exists TESTtest_i4; CREATE TABLE TESTtest_i4 ( `TESTid` int(10) unsigned ) ENGINE=MyISAM DEFAULT CHARSET=latin1; drop TABLE if exists TESTtest4; CREATE TEMPORARY TABLE TESTtest4 ( `TESTid` int(10) unsigned ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(TESTtest_i4); desc TESTtest_i4; desc TESTtest4; Suggested fix: Dont use temp tables. drop TABLE if exists TESTtest4b; CREATE TABLE TESTtest4b ( `TESTid` int(10) unsigned ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=(TESTtest_i4); desc TESTtest4b; This works.