MySQL Bugs: #39823: mysqld allows corrupt merge union to prevent table changes

Bug #39823	mysqld allows corrupt merge union to prevent table changes
Submitted:	2 Oct 2008 19:43	Modified:	7 Oct 2008 8:04
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S4 (Feature request)
Version:	5.1.26rc	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
MySQL Query Browser shows errors inappropriately.

How to repeat:
Make one of the databases default, then paste the text below into the SQL/Query area.  Execute each of these statements in order.

drop table if exists m1, m2, m3, m4, m5, mx;
create table m1 ( id int );
create table m2 like m1;
create table m3 like m1;
create table m5 like m1;
create table mx like m1;
alter table mx engine=mrg_myisam union=(m1, m2, m3, m4, m5);
alter table mx engine=mrg_myisam union=(m1, m2, m3, m5);

No error was reported for the first alter (but should have).
Attempts to execute the second alter without removing the first fail with the following result: Table 'test.m4' doesn't exist.

Suggested fix:
Make MySQL Query browser send commands properly for the active line, even if the previous command of the same type failed.  Also, when an error occurs, report it.

The problem isn't the query browser, it's the server.  The query browser correctly relayed what the server sent it.

mysql> create table m1 ( id int );
Query OK, 0 rows affected (1.13 sec)

mysql> create table m2 like m1;
Query OK, 0 rows affected (0.18 sec)

mysql> create table m3 like m1;
Query OK, 0 rows affected (0.22 sec)

mysql> create table m5 like m1;
Query OK, 0 rows affected (0.05 sec)

mysql> create table mx like m1;
Query OK, 0 rows affected (0.05 sec)

mysql> alter table mx engine=mrg_myisam union=(m1, m2, m3, m4, m5);
Query OK, 0 rows affected (0.24 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> alter table mx engine=mrg_myisam union=(m1, m2, m3, m5);
ERROR 1146 (42S02): Table 'test.m4' doesn't exist

The only way I found to get out of this endless loop without a repair is to create the missing table.

mysql> alter table mx engine=myisam;
ERROR 1146 (42S02): Table 'test.m4' doesn't exist
mysql> create table m4 like m1;
Query OK, 0 rows affected (0.02 sec)

mysql> alter table mx engine=myisam;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Oh - it was worse than I thought - a repair table wouldn't fix it:

mysql> repair table mx;
+---------+--------+----------+-------------------------------+
| Table   | Op     | Msg_type | Msg_text                      |
+---------+--------+----------+-------------------------------+
| test.mx | repair | Error    | Table 'test.m4' doesn't exist |
| test.mx | repair | error    | Corrupt                       |
+---------+--------+----------+-------------------------------+
2 rows in set (0.04 sec)

mysql> alter table mx engine=mrg_myisam union=(m1, m2, m3, m5);
ERROR 1146 (42S02): Table 'test.m4' doesn't exist

I should have added - I could successfully drop the merge table and re-create it.

In any case, I should be able to fix a broken merge union without having to drop and re-create the merge table.

Thank you for the report.

But this is expected behavior. Please see bug #22716 for explanation.

We disagree on that point.  The underlying table is not being *used* - it's being altered to fix the broken merge union.  That's a bug because mysqld won't let users fix a broken merge after it's been specified except to drop and recreate the table or create the missing table(s).  mysqld will let me create a broken merge, but it won't let me fix a broken merge?  That's broken. (pun intended)

Thank you for the feedback.

I agree your concern is valid, although current behavior is intended and there is no harm to recreate empty table. So I mark this report as feature request.