Bug #10878 MERGE table usage enhancement
Submitted: 26 May 2005 8:12 Modified: 11 Apr 2006 17:42
Reporter: Lenz Grimmer Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:4.1 OS:
Assigned to: Ingo Strüwing CPU Architecture:Any

[26 May 2005 8:12] Lenz Grimmer
Description:
This is a related note to BUG#10687 (MERGE Engine fails on Windows). I tried to reproduce if the bug still exists in 4.1.12a and installed the "essential" MSI package. The installation wizard will automatically configure and start up mysqld.

When I follow the sequence of SQL statements outlined in the bug report, I could still reproduce the error message described in there. After some investigation it turned out, that the Installation Wizard adds the following line to the my.ini configuration file:

  default-storage-engine=INNODB

So the tables t1 and t2 in the example were created as InnoDB tables by default. Nothing wrong with that, but in that case the creation of the MERGE table should have given me an error or at least a warning that creating MERGE tables using InnoDB tables does not work! Instead, it silently accepted the query and even created the .MRG file. However, subsequent queries against this MERGE table fail. Fortunately it's at least possible to DROP it...

How to repeat:
Start a MySQL server with "default-storage-engine=INNODB"
Run the following queries:

CREATE TABLE t1 ( id INT );
CREATE TABLE t2 ( id INT );
CREATE TABLE mm ( id INT ) ENGINE=MERGE UNION=(t1,t2);
SELECT COUNT(*) FROM mm; // or any other query that involves the mm table

Observe the misleading error message after the SELECT statement

Suggested fix:
Creating a MERGE table with a non-supported table type (e.g. InnoDB) should cause an error.
[31 Mar 2006 17:20] Brian Aker
Please make sure this is documented.
[11 Apr 2006 17:42] Mike Hillyer
Added a note to the merge table problems section.