Description:
Merge table ends up with error "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"
How to repeat:
We have 2 machines in which MySQL 5.6.49-log is used.
On Master:
----------
1) Create table in MySQL 5.5.62 or MySQL 5.6.49-log use cases:
mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(`t1,t2`) ;
Query OK, 0 rows affected (0.00 sec)
The merge table fails to open on the master + slave
mysql> select * from total;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
If the merge table is with single quotes in between for each table it ends with error on master and in slave
Suggested fix:
ON Master:
----------
mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.01 sec)
CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(`t1`,`t2`) ;
Query OK, 0 rows affected (0.04 sec)
mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2');
Query OK, 3 rows affected (0.00 sec)
Records: 3 Duplicates: 0 Warnings: 0
on slaves:
-----------
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
6 rows in set (0.00 sec)
When the merge table is within quotes it works:ENGINE=MERGE UNION=(`t1`,`t2`) ;
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
6 rows in set (0.00 sec)
OR works is without quotes it works
ENGINE=MERGE UNION=(t1,t2) ;
mysql> select * from total;
+---+---------+
| a | message |
+---+---------+
| 1 | Testing |
| 2 | table |
| 3 | t1 |
| 1 | Testing |
| 2 | table |
| 3 | t2 |
+---+---------+
6 rows in set (0.00 sec)
Description: Merge table ends up with error "Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist" How to repeat: We have 2 machines in which MySQL 5.6.49-log is used. On Master: ---------- 1) Create table in MySQL 5.5.62 or MySQL 5.6.49-log use cases: mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; Query OK, 0 rows affected (0.01 sec) mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; Query OK, 0 rows affected (0.00 sec) mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(`t1,t2`) ; Query OK, 0 rows affected (0.00 sec) The merge table fails to open on the master + slave mysql> select * from total; ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist If the merge table is with single quotes in between for each table it ends with error on master and in slave Suggested fix: ON Master: ---------- mysql> CREATE TABLE t1 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; mysql> CREATE TABLE t2 ( a INT NOT NULL AUTO_INCREMENT PRIMARY KEY, message CHAR(20)) ENGINE=MyISAM; Query OK, 0 rows affected (0.01 sec) CREATE TABLE total ( a INT NOT NULL AUTO_INCREMENT, message CHAR(20), INDEX(a)) ENGINE=MERGE UNION=(`t1`,`t2`) ; Query OK, 0 rows affected (0.04 sec) mysql> INSERT INTO t1 (message) VALUES ('Testing'),('table'),('t1'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 mysql> INSERT INTO t2 (message) VALUES ('Testing'),('table'),('t2'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 on slaves: ----------- mysql> select * from total; +---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+ 6 rows in set (0.00 sec) When the merge table is within quotes it works:ENGINE=MERGE UNION=(`t1`,`t2`) ; mysql> select * from total; +---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+ 6 rows in set (0.00 sec) OR works is without quotes it works ENGINE=MERGE UNION=(t1,t2) ; mysql> select * from total; +---+---------+ | a | message | +---+---------+ | 1 | Testing | | 2 | table | | 3 | t1 | | 1 | Testing | | 2 | table | | 3 | t2 | +---+---------+ 6 rows in set (0.00 sec)