| 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: | |
| Category: | MySQL Server: Merge storage engine | Severity: | S3 (Non-critical) |
| Version: | 4.1 | OS: | |
| Assigned to: | Jim Winstead | CPU Architecture: | Any |
[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.
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).