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:
Category:MySQL Server: Information schema Severity:S2 (Serious)
Version:MySQL version 5.0.22 OS:Linux (CentOS 5)
Assigned to: CPU Architecture:Any

[23 Dec 2007 0:40] [ name withheld ]
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
[23 Dec 2007 0:43] [ name withheld ]
Resultset for row in TABLES in CSV format

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

[24 Dec 2007 14:40] Valeriy Kravchuk
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).
[25 Jan 2008 0:00] Bugs System
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".