MySQL Bugs: #39824: Inaccurate error message when attempting to create new merge with improper union

Bug #39824	Inaccurate error message when attempting to create new merge with improper union
Submitted:	2 Oct 2008 19:47	Modified:	13 Apr 2009 21:53
Reporter:	Kevin Benton (Candidate Quality Contributor)	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Merge storage engine	Severity:	S3 (Non-critical)
Version:	4.1.20	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
ERROR 1017 (HY000): Can't find file: '#sql-dde_316128.MRG' (errno: 2)

The file described is not listed in the alter and therefore misleads the reader into believing that a .MRG file is missing when the real problem is a missing table specified in the union.

This may be related to Bug 39823

How to repeat:
From my end: This is inaccurate for the following alter:

mysql> alter table mx union=( m200611, m200612, m200701, m200702, m200703, m200704, m200705, m200706, m200707, m200708, m200709, m200710, m200711, m200712, m200801, m200802, m200803, m200804, m200805, m200806, m200807, m200808 );
ERROR 1017 (HY000): Can't find file: '#sql-dde_316128.MRG' (errno: 2)
mysql> 

Suggested fix:
The problem was that one of the tables specified was missing, in this case, m200807 yet the error message I got was misleading.

When a table in a union is missing, report that the table is missing, not the obscure error displayed.

Thank you for the report.

But version 4.1.20 is old. Please try with current version 4.1.23 and provide repeatable test case if bug still exists.

I get correct error "failed: 1168: Unable to open underlying table which is differently defined or of non-MyISAM type or doesn't exist" with current 4.1 and 5.0 development sources and the statement does not fail with 5.1 and 6.0

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

When a MyISAM union (merge) has an invalid / missing table in it, MySQL prevents authorized users from altering the union to "get rid of" the offending table.  There is a workaround - drop the merge then recreate it.  The problem is - the error message misleads the reader into thinking something else is wrong.

ERROR 1017 (HY000): Can't find file: '#sql-dde_316128.MRG' (errno: 2)

This simply just doesn't explain the real problem.  It would be much better if the error message said something like this (at am inimum)

ERROR ... (...): Invalid merges can't be changed.  Use DROP TABLE instead.  Offending tables: ... (errno: ...)

Any update on this?

Again, I ask - any update on this?

Kevin,

thank you for the feedback. Unfortunately version 4.1 is in Extended Support stage and I get correct error with latest sources of 4.1 and later versions, so this bug has low priority, especially if we need to test earlier releases of 4.1.

Thank you for the report.

I could not repeat error message from the initial reply. According to your comment about latest error message is not clear enough there is feature request: bug #24773 about very same problem.

So I close this report as "Can't repeat". But if you are able to repeat original problem with one of latest versions feel free to reopen it.