MySQL Bugs: #33487: Problem with row in TABLES

Bug #33487	Problem with row in TABLES
Submitted:	23 Dec 2007 0:40	Modified:	24 Jan 2008 14:40
Reporter:	[ name withheld ]	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Information schema	Severity:	S2 (Serious)
Version:	MySQL version 5.0.22	OS:	Linux (CentOS 5)
Assigned to:		CPU Architecture:	Any

Description:
I have a table that appears to have been only partially created in such a way that I cannot now use MySQL Query Browser version 1.2.12 to either create or drop the table. Its existance is also interfering with producing a structure only dump with mysqldump version 10.10 using the following command:
 
mysqldump -u admin -p -d --skip-lock-tables --ignore-table=my_db.tbl_batch_active my_db > my_db.sql

I get the following error:

Couldn't execute 'SHOW CREATE TABLE `tbl_batch_active`': Table 'my_db.tbl_batch_active' doesn't exist (1146)

I have no idea now what caused this incomplete table creation as it was created some time ago. I connect to the server from Windows Vista using MySQL Query Browser over TCP/IP and the Schemata shows the table exists but reports: Could not fetch columns.

Using a right-click to Drop Table gives:

Error while executing query DROP TABLE 'mysql.tbl_batch_active'
MySQL Error Numner 1051
Unknown table 'tbl_batch_active'

So it appears that the mysql server thinks the table is in the wrong database.

Using MySQL Query Browser to look at the TABLES in the information_schema reveals that the entry for this table looks wrong but I can find no means to correct or remove it. It does not appear to be possible to use mysql -u admin -p on the server to DELETE the tbl_batch_active row from TABLES or to edit it remotely with MySQL Query Browser

How to repeat:
I suspect that any row with ENGINE IS NULL in 'information_schema.TABLES' will cause problems but I cannot do the experiment as this is a live internet server.

I attach an Exported Resultset for the faulty row in TABLES to this report in case this assists someone in recreating the problem.

Suggested fix:
1) provide guidance on how to remove a faulty table row from TABLES

2) ensure mysqldump properly respects the --ignore-table=database.table option when there is a faulty table row in TABLES

Resultset for row in TABLES in CSV format

Attachment: TABLES.csv (application/vnd.ms-excel, text), 438 bytes.

Thank you for a problem report. Please, send the exact steps to create such a table or just send table's files (.frm, .MYI and .MYD, if it was MyISAM initially).

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