Bug #17766 The server accepts to create MERGE tables which cannot work
Submitted: 28 Feb 2006 12:18 Modified: 2 Sep 2006 1:37
Reporter: Matthias Leich Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Jim Winstead CPU Architecture:Any

[28 Feb 2006 12:18] Matthias Leich
Description:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1 (f1 INTEGER, f2 CHAR(3)) ENGINE = MERGE ;
INSERT INTO t1 VALUES(1,'A');
ERROR HY000: Table storage engine for 't1' doesn't have this option

For me a MERGE table looks mostly like an updatable UNION VIEW.
It's clear that the MERGE table above cannot work, because the part
"UNION(<list of existing MyISAM tables>)" is missing within the
CREATE TABLE statement. That means the server cannot know
where to look for the data.

My environment:
   - Intel PC with Linux(SuSE 9.3)
   - MySQL compiled from source
        Version 4.1 ChangeSet@1.2471, 2006-02-03
        Version 5.1 ChangeSet@1.2190, 2006-02-28
     I guess Version 5.0 shows the same behaviour.

How to repeat:
Please execute the statements above.

Suggested fix:
I can think of 3 solutions:
1) The server should not accept a CREATE TABLE statement
    where during create time it is already known that this table
    will can not work. That means give an error message.
2) I assume that partitioned tables (MySQL 5.1)  can be used in 
    many cases where MERGE tables were used in history.
    Partitioned tables are more powerful in many SQL areas.
    Some examples about the advantages of partitioned tables:  
    - INSERT ... SELECT is allowed
    - Inserted records can be fine distributed over the partitions
      by a smart partitioning function.
      MERGE tables cause that the record is inserted into the first
      or last "base" table. That means the is no balance in
      distribution of records.
    - Partitioning gives room for optimizations like partition pruning
      in queries or split of queries which lead to parallel execution
      on the partitions
    So it might make sense to remove the MERGE storage engine
    in MySQL Version >= 5.1.
3) Please document the observed behaviour within the manual.

The bug itself is not very important, because there are neither
already stored data damaged nor wrong result sets received.
I would choose 2) and 3).
[27 Jun 2006 1:40] 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/8279
[27 Jun 2006 22:07] Jim Winstead
Per discussion with monty, I will not disable the creation of such tables (since they do have some utility), but I will make it so an insert against them gives a more sensible error message.
[28 Jun 2006 0:06] 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/8361
[12 Jul 2006 0:22] 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/9073
[21 Jul 2006 19:35] Antony Curtis
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://www.mysql.com/doc/en/Installing_source_tree.html
[21 Jul 2006 19:36] Antony Curtis
pushed to 5.0.25 repository
[11 Aug 2006 2:35] Jim Winstead
Fixed in 5.0.25 and 5.1.12.
[18 Aug 2006 13:07] Jon Stephens
Why are we allowing the creation of a table into which data can't be inserted? Please explain. Thanks!
[18 Aug 2006 16:09] Jim Winstead
Because that is how Monty wants it to work. He can explain his rationale. (Which makes sense, I just don't want to misrepresent it by trying to repeat it.)
[2 Sep 2006 1:37] Paul Dubois
Noted in 5.0.25, 5.1.12 changelogs.

It is possible to create MERGE tables into which data cannot be
inserted (by not specifying a UNION clause. However, when an insert
was attempted, the error message was confusing. Now an error occurs
indicating that the table is read-only.