Bug #12164 Created merge table never accessible

• Submitted: 25 Jul 2005 21:25
• Modified: 25 Jul 2005 21:42
• Reporter: Rob Vieira
• Status: Closed
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.12
• OS: Windows (Windows 2003)
• CPU Architecture: Any

[25 Jul 2005 21:25] Rob Vieira

Description:
Submitted per Ingo

Can not create a useable Merge table. The table appears to create, but when you go to access the merge table, you get an error indicating that it can't find the underlying file - the underlying file was properly created by the CREATE statement and exists as expected (mysql just can't find it apparently)

How to repeat:
CREATE DATABASE Foo;
USE Foo;
CREATE TABLE t1 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20));
CREATE TABLE t2 (
   a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   message CHAR(20));
INSERT INTO t1 (message) VALUES ('Testing');
INSERT INTO t1 (message) VALUES ('table');
INSERT INTO t2 (message) VALUES ('t2');
CREATE TABLE total (
   a INT NOT NULL AUTO_INCREMENT,
   message CHAR(20), INDEX(a))
   ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
SELECT * FROM total;

Output:
Can't find file: 'total.MRG' (errno: 2)

[25 Jul 2005 21:42] MySQL Verification Team

Thank you for your bug report. This issue has already been fixed
in the latest released version of that product, which you can download at 
http://www.mysql.com/downloads/

Additional info:

c:\mysql\bin>mysql -uroot db5
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 4.1.13-nt

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> CREATE TABLE t1 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
Query OK, 0 rows affected (0.09 sec)

mysql> CREATE TABLE t2 (
    ->    a INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    ->    message CHAR(20));
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO t1 (message) VALUES ('Testing');
Query OK, 1 row affected (0.02 sec)

mysql> INSERT INTO t1 (message) VALUES ('table');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO t2 (message) VALUES ('t2');
Query OK, 1 row affected (0.00 sec)

mysql> CREATE TABLE total (
    ->    a INT NOT NULL AUTO_INCREMENT,
    ->    message CHAR(20), INDEX(a))
    ->    ENGINE=MERGE UNION=(t1,t2) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.06 sec)

mysql> SELECT * FROM total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table   |
| 1 | t2      |
+---+---------+
3 rows in set (0.02 sec)

mysql>