MySQL Bugs: #10974: No error message if merge table based on union of innodb, memory

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

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)

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)

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.

Sorry, prev comment was intended for another bug.

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

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.

Reopened to address confusing error message problem.

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).

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.

Fixed in 4.1.22, 5.0.26, 5.1.12.

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.

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.

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.

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.

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.

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.

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.

As discussed above nothing to do with this bug anymore. Closing it.