MySQL Bugs: #28248: mysqldump results with MERGE ... UNION=() cannot be executed

Bug #28248	mysqldump results with MERGE ... UNION=() cannot be executed
Submitted:	4 May 2007 15:54	Modified:	30 Mar 2008 8:57
Reporter:	Tim Rosine	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.0.42-BK, 5.0.27-log	OS:	Linux
Assigned to:	Sergey Vojtovich	CPU Architecture:	Any

Description:
If a database has a merge table with no tables in the UNION, using SHOW CREATE TABLE contains "UNION=()".  Attempting to drop the table and executing the statement (as is performed by mysqldump) fails.

I use empty tables as templates to assist in creating tables using the CREATE TABLE ... LIKE syntax.

I've seen the same results from version 5.0.22-log, and I believe it was also present in 4.1.20-log, although we're off that platform now.

My current workaround is to use sed to strip off an empty UNION=() clause.

How to repeat:
mysql> create database mrg_union;
Query OK, 1 row affected (0.00 sec)

mysql> use mrg_union;
Database changed
mysql> create table template_partition (a int) engine=myisam;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table template_partition\G
*************************** 1. row ***************************
       Table: template_partition
Create Table: CREATE TABLE `template_partition` (
  `a` int(11) default NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> create table template_merge (a int) engine=merge;
Query OK, 0 rows affected (0.04 sec)

mysql> show create table template_merge\G
*************************** 1. row ***************************
       Table: template_merge
Create Table: CREATE TABLE `template_merge` (
  `a` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=()
1 row in set (0.00 sec)

mysql> create table p1 like template_partition;
Query OK, 0 rows affected (0.00 sec)

mysql> create table p2 like template_partition;
Query OK, 0 rows affected (0.00 sec)

mysql> create table mrg like template_merge;
Query OK, 0 rows affected (0.00 sec)

mysql> alter table mrg union=(p1,p2);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> create database mrg_union2;
Query OK, 1 row affected (0.00 sec)

mysql>exit
Bye
shell$ mysqldump mrg_union | mysql mrg_union2
ERROR 1064 (42000) at line 152: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ')' at line 3

Suggested fix:
As I see it, there are several options for this:
1) modify SHOW CREATE TABLE to not include an empty "UNION=()" clause when no tables make up the MERGE table.
2) modify CREATE TABLE to allow "UNION=()".
3) disallow the ability to create empty merge tables - not my preference and probably not feasible since there are several ways of creating empty merge tables.  
** CREATE TABLE ... ENGINE=MERGE (without UNION clause)
** ALTER TABLE ... ENGINE=MERGE from a myisam table (UNION clause not supported in this syntax)
** CREATE TABLE ... LIKE from another merge table (UNION clause not supported in this syntax)

Thank you for a bug report. Verified just as described with latest 5.0.42-BK on Linux. 

Simpler test case that demonstrates server(!) bug:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.42-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>  create table template_merge (a int) engine=merge;
Query OK, 0 rows affected (0.02 sec)

mysql> show create table template_merge\G
*************************** 1. row ***************************
       Table: template_merge
Create Table: CREATE TABLE `template_merge` (
  `a` int(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=()
1 row in set (0.00 sec)

mysql> create table tm2
    ->   `a` int(11) default NULL
    -> ) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=();
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'a` in
t(11) default NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=latin1 UNION=()' at line 2

Workaround: edit dump file to manually remove the empty UNION clause

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

ChangeSet@1.2595, 2008-03-13 20:03:07+04:00, svoj@mysql.com +4 -0
  BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed
  
  When there are no underlying tables specified for a merge table,
  SHOW CREATE TABLE outputs a statement that cannot be executed. The
  same is true for mysqldump (it generates dumps that cannot be
  executed).
  
  This happens because SQL parser does not accept empty UNION() clause.
  
  This patch changes the following:
  - it is now possible to execute CREATE/ALTER statement with
    empty UNION() clause.
  - the same as above, but still worth noting: it is now possible to
    remove underlying tables mapping using ALTER TABLE ... UNION=().
  - SHOW CREATE TABLE does not output UNION() clause if there are
    no underlying tables specified for a merge table. This makes
    mysqldump slightly smaller.

Ok to push from me.
One wish: Please add a test CREATE TABLE ... UNION=().

I agree with Ingo, 
otherwise http://lists.mysql.com/commits/43935 is OK to push.

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

ChangeSet@1.2595, 2008-03-14 19:38:22+04:00, svoj@mysql.com +4 -0
  BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed
  
  When there are no underlying tables specified for a merge table,
  SHOW CREATE TABLE outputs a statement that cannot be executed. The
  same is true for mysqldump (it generates dumps that cannot be
  executed).
  
  This happens because SQL parser does not accept empty UNION() clause.
  
  This patch changes the following:
  - it is now possible to execute CREATE/ALTER statement with
    empty UNION() clause.
  - the same as above, but still worth noting: it is now possible to
    remove underlying tables mapping using ALTER TABLE ... UNION=().
  - SHOW CREATE TABLE does not output UNION() clause if there are
    no underlying tables specified for a merge table. This makes
    mysqldump slightly smaller.

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

ChangeSet@1.2560, 2008-03-15 00:24:10+04:00, svoj@mysql.com +2 -0
  BUG#28248 - mysqldump results with MERGE ... UNION=() cannot be executed
  After merge fix.

Pushed into 5.1.24-rc

Pushed into 5.0.60

Pushed into 6.0.5-alpha

Documented bugfix in the 5.0.60, 5.1.23-ndb-6.3.11, 5.1.24, and 6.0.5 changelogs as follows:

        The SQL parser did not accept an empty UNION=() clause. This meant that,
        when there were no underlying tables specified for a MERGE table,
        SHOW CREATE TABLE and mysqldump both output statements that could not be
        executed.
        
        Now it is possible to execute a CREATE TABLE or ALTER TABLE statement
        with an empty UNION=() clause. However, SHOW CREATE TABLE and mysqldump do not output
        the UNION=() clause if there are no underlying tables specified for a merge table.
        This also means it is now possible to remove underlying tables from a
        MERGE table using ALTER TABLE ... UNION=().