Bug #10974 No error message if merge table based on union of innodb, memory
Submitted: 30 May 2005 20:29 Modified: 6 Oct 2006 6:10
Reporter: Peter Gulutzan Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Merge storage engine Severity:S3 (Non-critical)
Version:5.0.7-beta-debug OS:Linux (SUSE 9.2)
Assigned to: Sergey Vojtovich CPU Architecture:Any

[30 May 2005 20:29] Peter Gulutzan
Description:
The MySQL Reference manual says:
"You can use only identical MyISAM tables for a MERGE table."
But I can create a MERGE table using InnoDB and MEMORY tables, there's no error message.

How to repeat:
mysql> create table tm1 (s1 int) engine=innodb;
Query OK, 0 rows affected (0.07 sec)

mysql> create table tm2 (s1 int) engine=memory;
Query OK, 0 rows affected (0.01 sec)

mysql> create table tm3 (s1 int) engine=merge union=(tm1,tm2) insert_method=last;
Query OK, 0 rows affected (0.02 sec)
[30 May 2005 20:33] MySQL Verification Team
Thank you for the bug report.

mysql> create table tm3 (s1 int) engine=merge union=(tm1,tm2)
    -> insert_method=last;
Query OK, 0 rows affected (0.00 sec)

mysql> show create table tm3;
ERROR 1017 (HY000): Can't find file: 'tm3' (errno: 2)
[1 Aug 2006 6:43] Sergey Vojtovich
This is expected behavior. There is implicit default value for this case. It is 0 for numeric data types and '' for strings. Warning should be changed and it deserves a note in "LOAD DATA INFILE" section of mysql manual.
[1 Aug 2006 6:44] Sergey Vojtovich
Sorry, prev comment was intended for another bug.
[4 Aug 2006 8:21] Sergei Golubchik
Please do not submit the same bug more than once. An existing bug report already describes this very problem. Even if you feel that your issue is somewhat different, the resolution is likely
to be the same. Because of this, we hope you add your comments to the original bug instead.

Thank you for your interest in MySQL.

See bug#457
[4 Aug 2006 20:35] Peter Gulutzan
Bug#457 was set to "Won't fix" years ago so it became inactive and didn't show up in ordinary searches. However, I'll be happy to shift the comments here in #10974
to #457 and re-open it.
[19 Sep 2006 18:21] Sergey Vojtovich
Reopened to address confusing error message problem.
[19 Sep 2006 20:43] Bugs System
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/12238

ChangeSet@1.2548, 2006-09-20 01:40:59+05:00, svoj@mysql.com +6 -0
  BUG#10974 - No error message if merge table based on union of innodb, memory
  
  Fixed confusing error message from the storage engine when
  it fails to open underlying table. The error message is issued
  when a table is _opened_ (not when it is created).
[29 Sep 2006 13:59] Bugs System
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/12845

ChangeSet@1.2552, 2006-09-29 19:00:52+05:00, svoj@mysql.com +2 -0
  Fix a test case according to fix for bug#10974.
[3 Oct 2006 8:46] Sergey Vojtovich
Fixed in 4.1.22, 5.0.26, 5.1.12.
[4 Oct 2006 2:22] Paul DuBois
Noted in 4.1.22, 5.0.26, 5.1.12 changelog.

The server returns a more informative error message when it attempts
to open a MERGE table that has been defined to use non-MyISAM tables.
[4 Oct 2006 18:15] Peter Gulutzan
The original bug, as illustrated by the test case, is not fixed:
there is still no error on CREATE. However, judging from Monty's
comments on bug#457, he thinks there can't be an error message
at CREATE time. So this fix, which improves error messages after
CREATE time, is right. However ...

The new message is:
"Unable to open underlying table which is differently
defined or of non-MyISAM type or doesn't exists"
It should be correct English:
"Unable to open underlying table which is differently
defined or of non-MyISAM type or doesn't exist"

If I create a trigger on tm3, then try to drop it, I get
error 1168. So it's now impossible to drop the trigger:
create table tm1 (s1 int) engine=myisam;
create table tm2 (s1 int) engine=myisam;
create table tm3 (s1 int) engine=merge union=(tm1,tm2) insert_method=last;
create trigger tm3_bi before insert on tm3 for each row set new.s1 = 0;
alter table tm2 partition by hash (s1);
drop trigger tm3_bi; /* Result: error 1168 */

If I alter tm1 and tm2, and insert into them,
there is no error message, but there's nothing
in tm3:
create table tm1 (s1 int) engine=myisam;
create table tm2 (s1 int) engine=myisam;
create table tm3 (s1 int) engine=merge union=(tm1,tm2) insert_method=last;
select * from tm3;
alter table tm1 engine=innodb;
alter table tm2 engine=memory;
insert into tm1 values (1);
insert into tm2 values (2);
select * from tm3; /* Result: 0 rows */

If I rename tables tm1 and tm2, and insert into t3,
there is no error message, but there's nothing
in tm1 or tm2:
create table tm1 (s1 int) engine=myisam;
create table tm2 (s1 int) engine=myisam;
create table tm3 (s1 int) engine=merge union=(tm1,tm2) insert_method=last;
insert into tm3 values (0);
rename table tm1 to tm1a;
rename table tm2 to tm2a;
insert into tm3 values (1);
show create table tm3; /* shows "UNION=(`tm1`,`tm2`)" */

So it seems that, since MERGE dependencies are not checked
when DDL happens (i.e. CREATE, ALTER, RENAME), later results
can be bad.
[5 Oct 2006 15:41] Sergey Vojtovich
Peter,

thanks for your comments. I will change error message as addition to fix for current bug.

There is a note in "MERGE Table Problems" section of mysql manual, which says:
You cannot use DROP TABLE, ALTER TABLE, DELETE without a WHERE clause, REPAIR TABLE, TRUNCATE TABLE, OPTIMIZE TABLE, or ANALYZE TABLE on any of the tables that are mapped into an open MERGE table. If you do so, the MERGE table may still refer to the original table, which yields unexpected results. The easiest way to work around this deficiency is to ensure that no MERGE tables remain open by issuing a FLUSH TABLES statement prior to performing any of those operations.

IMO opinion it is at least confusing. But according to recent discussions this behaviour was considered as expected.

I'm not sure why drop trigger opens a table. Probably we should open separate bug report for this issue and close current bug.
[5 Oct 2006 17:15] Peter Gulutzan
Sergey,

I had not read, or had forgotten, the section of the manual that you mention.
Since it's documented, you're right, there's no point in pursuing it.
And thanks for educating me.

As you suggested, I have opened a separate bug for DROP TRIGGER:
Bug #23022  	Can't drop trigger of an altered merge table

So only the trivial spelling matter remains.
[6 Oct 2006 5:52] Bugs System
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/13215

ChangeSet@1.2557, 2006-10-06 10:54:47+05:00, svoj@mysql.com +2 -0
  Addition to fix for bug#10974. Fixed spelling.
[6 Oct 2006 6:01] Bugs System
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/13217

ChangeSet@1.2291, 2006-10-06 11:03:14+05:00, svoj@mysql.com +1 -0
  Addition to fix for bug#10974. Fixed spelling.
[6 Oct 2006 6:09] Bugs System
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/13219

ChangeSet@1.2344, 2006-10-06 11:11:47+05:00, svoj@mysql.com +1 -0
  Addition to fix for bug#10974. Fixed spelling.
[6 Oct 2006 6:10] Sergey Vojtovich
As discussed above nothing to do with this bug anymore. Closing it.