MySQL Bugs: #71986: Manual does not explain how MERGE table with empty UNION can be used

Bug #71986	Manual does not explain how MERGE table with empty UNION can be used
Submitted:	9 Mar 2014 14:13	Modified:	10 Mar 2014 15:43
Reporter:	Valeriy Kravchuk	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S3 (Non-critical)
Version:	any	OS:	Any
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	merge, missing manual, MRG_MyISAM, read only, UNION

Description:
Manual (http://dev.mysql.com/doc/refman/5.6/en/merge-storage-engine.html in case of 5.6) says:

"It is also possible to use ALTER TABLE ... UNION=() (that is, with an empty UNION clause) to remove all of the underlying tables."

You can also create MERGE table with empty UNION clause (no tables associated) explicitly or as a result of having default_storage_engine=MERGE.

Manual unfortunately does NOT explain what one can do with a table like this. Essentially until you associate at least one proper MyISAM table with MERGE table, it's read only and all you can do with it is select nothing from it. It would be nice to have this explicitly documented, as well as some explanation on what this kind of table may be useful for (to understand why server allows to create it at all).

Note that you have no warnings of any kind (even from CHECK) about this read only status or no tables associated, until you try to modify the data somehow.

How to repeat:
mysql> create table tpm(id int) engine=MERGE;
Query OK, 0 rows affected (0.20 sec)

mysql> show create table tpm\G
*************************** 1. row ***************************
       Table: tpm
Create Table: CREATE TABLE `tpm` (
  `id` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
1 row in set (0.11 sec)

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

mysql> insert into tpm values(1);
ERROR 1036 (HY000): Table 'tpm' is read only
mysql> check table tpm;
+----------+-------+----------+----------+
| Table    | Op    | Msg_type | Msg_text |
+----------+-------+----------+----------+
| test.tpm | check | status   | OK       |
+----------+-------+----------+----------+
1 row in set (0.52 sec)

mysql> alter table tpm union=();
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table tpm\G
*************************** 1. row ***************************
       Table: tpm
Create Table: CREATE TABLE `tpm` (
  `id` int(11) DEFAULT NULL
) ENGINE=MRG_MyISAM DEFAULT CHARSET=utf8
1 row in set (0.04 sec)

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

mysql> insert into tpm values(1);
ERROR 1036 (HY000): Table 'tpm' is read only

Suggested fix:
Explain all possible uses for MERGE table with empty UNION clause explicitly.

Thank you for the bug report.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

"Explain all possible uses for an empty MERGE table" is such an open-ended request as to be unactionable.

I've add this note to the MERGE section, following the ALTER TABLE with an empty table list

However, in this case, the table is effectively empty and inserts
fail because there is no underlying table to take new rows. Such a
table might be useful as a template for creating new MERGE tables
with CREATE TABLE ... LIKE.