MySQL Bugs: #30491: MERGE doesn't report error when one table is Innodb

Bug #30491	MERGE doesn't report error when one table is Innodb
Submitted:	18 Aug 2007 10:43	Modified:	14 Jan 2008 18:00
Reporter:	Grigory Rubtsov	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	5.0.22, 5.0 BK, 5.1 BK	OS:	Any
Assigned to:	Ingo Strüwing	CPU Architecture:	Any
Tags:	merge

Description:
1. MERGE allows to CREATE merge with an InnoDB table, the error arise on query is not descripriptive
2. If one alter table within MERGE to become InnoDB, the consecutive SELECTs and INSERTs will be performed in a wrong way and no error occur.

How to repeat:
mysql> CREATE TABLE A (name varchar(100)) ENGINE=MyISAM;
mysql> CREATE TABLE B (name varchar(100)) ENGINE=MyISAM;
mysql> CREATE TABLE C (name varchar(100)) ENGINE=Innodb;
mysql> INSERT INTO A VALUES ('Ann'), ('Alice');
mysql> INSERT INTO B VALUES ('Bob'), ('Brian');
mysql> INSERT INTO C VALUES ('Chris'), ('Charlie');

mysql> CREATE TABLE M1 (name varchar(100)) ENGINE=MERGE UNION=(A,B) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE M2 (name varchar(100)) ENGINE=MERGE UNION=(A,C) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.01 sec)

mysql> SELECT * FROM M2;
ERROR 1017 (HY000): Can't find file: 'M2' (errno: 2)
mysql> SELECT * FROM M1;
+-------+
| name  |
+-------+
| Ann   | 
| Alice | 
| Bob   | 
| Brian | 
+-------+
4 rows in set (0.00 sec)

mysql> ALTER TABLE B ENGINE=InnoDB;
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> SELECT * FROM M1;
+-------+
| name  |
+-------+
| Ann   | 
| Alice | 
| Bob   | 
| Brian | 
+-------+
4 rows in set (0.00 sec)

mysql> DELETE FROM B LIMIT 1;
Query OK, 1 row affected (0.00 sec)

mysql> SELECT * FROM M1;
+-------+
| name  |
+-------+
| Ann   | 
| Alice | 
| Bob   | 
| Brian | 
+-------+
4 rows in set (0.00 sec)

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

mysql> SELECT * FROM M1;
+--------+
| name   |
+--------+
| Ann    | 
| Alice  | 
| Bob    | 
| Brian  | 
| Beware | 
+--------+
5 rows in set (0.00 sec)

mysql> SELECT * FROM B;
+-------+
| name  |
+-------+
| Brian | 
+-------+
1 row in set (0.00 sec)

mysql> SELECT * FROM A;
+-------+
| name  |
+-------+
| Ann   | 
| Alice | 
+-------+
2 rows in set (0.00 sec)

Thank you for the report.

> 1. MERGE allows to CREATE merge with an InnoDB table, the error arise on query
> is not descripriptive

This has been fixed and now there is correct error message: "ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist"

> 2. If one alter table within MERGE to become InnoDB, the consecutive SELECTs
> and INSERTs will be performed in a wrong way and no error occur.

This part of report verified as described with version 5.1.

Scenario:

SELECT * FROM M2;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
SELECT * FROM M1;
name
Ann
Alice
Bob
Brian
ALTER TABLE B ENGINE=InnoDB;
SELECT * FROM M1;
name
Ann
Alice
Bob
Brian
...

But:

SELECT * FROM M2;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

ALTER TABLE B ENGINE=InnoDB;
SELECT * FROM M1;
ERROR HY000: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

With version 5.0 behaviour is defferent:

mysql> CREATE TABLE A (name varchar(100)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.08 sec)

mysql> CREATE TABLE B (name varchar(100)) ENGINE=MyISAM;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE C (name varchar(100)) ENGINE=Innodb;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO A VALUES ('Ann'), ('Alice');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO B VALUES ('Bob'), ('Brian');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> INSERT INTO C VALUES ('Chris'), ('Charlie');
Query OK, 2 rows affected (0.00 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> 
mysql> CREATE TABLE M1 (name varchar(100)) ENGINE=MERGE UNION=(A,B) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> CREATE TABLE M2 (name varchar(100)) ENGINE=MERGE UNION=(A,C) INSERT_METHOD=LAST;
Query OK, 0 rows affected (0.00 sec)

mysql> 
mysql> --error 1168
mysql> SELECT * FROM M2;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist
mysql> 
mysql> SELECT * FROM M1;
ERROR 1168 (HY000): Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist

change to P2

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/38967

ChangeSet@1.2671, 2007-11-30 15:16:31+01:00, istruewing@stella.local +2 -0
  Bug#30491 - MERGE doesn't report error when one table is Innodb
  
  1. A bad error message was given when a MERGE table with an
     InnoDB child table was tried to use.
  
  2. After selecting from a correct MERGE table and then altering
     one of the children to InnoDB, incorrect results were returned.
  
  These bugs have been fixed with the patch for bug 26379 (Combination
  of FLUSH TABLE and REPAIR TABLE corrupts a MERGE table).
  
  For verification, I added the test case from the bug report.

NOTE: No check whatsoever is done on CREATE TABLE ... ENGINE=MERGE.
The table is not opened or used in this statement. Hence the checks
don't give us much. The children could easily be altered after the
create. So it is required to check compatibility of all
participating tables at the begin of each statement that uses a
MERGE table.

Since many users are confused about it, we could in theory open
the MERGE table after create. Incompatible tables would then
immediately be detected. But, when issuing an error message, we also
must drop the new table again. This means that all child tables
must be created before the MERGE table. I won't implement it
without a feature request. And even if it exists, I am not sure
if our architects would approve it.

A similar problem would be ALTER TABLE. Should it refuse
incompatible changes? If yes, it would mean that all children
must first be changed, before the MERGE table could be changed.

Today the order of CREATEs/ALTERs doesn't matter as long as you
make all tables compatible before using the MERGE table.

Patch is ok and can be pushed as long as it is
applied to mysql-5.1 and up only.

Queued to 6.0-engines, 5.1-engines.

Pushed into 6.0.5-alpha

Pushed into 5.1.23-rc

Already fixed with Bug #26379 (in 5.1.23/6.0.4) per developer note above. Updated appropriate changelog entry to reflect this info.