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

[2 Oct 2008 19:43] Kevin Benton
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.
[2 Oct 2008 19:47] Kevin Benton
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
[2 Oct 2008 19:53] Kevin Benton
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>
[2 Oct 2008 19:55] Kevin Benton
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
[2 Oct 2008 19:57] Kevin Benton
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.
[2 Oct 2008 20:12] Sveta Smirnova
Thank you for the report.

But this is expected behavior. Please see bug #22716 for explanation.
[2 Oct 2008 20:39] Kevin Benton
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)
[7 Oct 2008 8:04] Sveta Smirnova
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.

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.041 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.