MySQL Bugs: #16153: Crashed table not avialable for repair

Bug #16153	Crashed table not avialable for repair
Submitted:	3 Jan 2006 14:03	Modified:	28 Aug 2006 13:19
Reporter:	Björn Dieding	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Administrator	Severity:	S3 (Non-critical)
Version:	1.1.2	OS:	Windows (WIN XP SP2)
Assigned to:	Mike Lischke	CPU Architecture:	Any
Tags:	Catalog

Description:
Form time to time one of our tables in the db crashes. 

The MySQL Query Browser shows the table in the list of tables, but it can't access it. Which is a expected behavoir.

The Administrator does not show the table! Since it does not show it you can't select repair or check it with-in the Adminstrators GUI.

Note:
PHPmyadmin shows the table with the state "busy". If you select repair in this app you can repair it.

How to repeat:
Hmm, sorry i don't know how to reproduce it since i don't know how to make a table crash.

Thank you for a problem report. Please, try to usde newer version of MySQL Administrator, 1.1.6, and inform about the results.

Is there any way to force a table to crash? Sounds stupid, ey :-) But i have currently no table to test it on, since I repaired it with the REPAIR TABLE syntax....

Else I need wait till we have this occurence again that a table has crashed. don't know when it occurs again.

Verified with MySQL Administrator 1.1.6 on Windows XP with the following steps:

1. Create any table in test database using MyISAM engine, with primary key.
2. Put one row of data in it.
3. Find the table.MYI file in the data/database directory and make it empty (size = 0), or delete it
4. Open test schema in Catalogs. You will not see the table in the list (Schema Tables tab).
5. The table is visible in mysql client, and can be repaired:

mysql> repair table table1 extended;
+-------------+--------+----------+--------------------------------------+
| Table       | Op     | Msg_type | Msg_text                             |
+-------------+--------+----------+--------------------------------------+
| test.table1 | repair | error    | Can't find file: 'table1' (errno: 2) |
+-------------+--------+----------+--------------------------------------+
1 row in set, 1 warning (0.00 sec)

mysql> repair table table1 use_frm;
+-------------+--------+----------+------------------------------------+
| Table       | Op     | Msg_type | Msg_text                           |
+-------------+--------+----------+------------------------------------+
| test.table1 | repair | warning  | Number of rows changed from 0 to 1 |
| test.table1 | repair | status   | OK                                 |
+-------------+--------+----------+------------------------------------+
2 rows in set (0.04 sec)

mysql> repair table table1;
+-------------+--------+----------+----------+
| Table       | Op     | Msg_type | Msg_text |
+-------------+--------+----------+----------+
| test.table1 | repair | status   | OK       |
+-------------+--------+----------+----------+
1 row in set (0.01 sec)

So, this is a bug that should be fixed to make the Administrator usable in such cases.

The bug http://bugs.mysql.com/bug.php?id=21393 was marked as duplicate
of this one and has complementary information.

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html