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.