Bug #29458 check/optimize/repair/analyze table reports 'corrupt' unnessesarily
Submitted: 30 Jun 2007 18:18 Modified: 12 May 2009 0:37
Reporter: Shane Bester (Platinum Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Errors Severity:S3 (Non-critical)
Version:5.0.46, 5.1.21 OS:Any
Assigned to: Staale Smedseng CPU Architecture:Any

[30 Jun 2007 18:18] Shane Bester
Description:
check/optimize/analyze/repair table wrongly returns 'corrupt' in at least these cases:

1) table didn't exist
2) somebody did KILL <id> and killed the check table

mysql> drop table if exists a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> check table a;
+--------+-------+----------+------------------------------+
| Table  | Op    | Msg_type | Msg_text                     |
+--------+-------+----------+------------------------------+
| test.a | check | Error    | Table 'test.a' doesn't exist |
| test.a | check | error    | Corrupt                      |
+--------+-------+----------+------------------------------+
2 rows in set (0.00 sec)

So, how can a table be corrupt and not exist?

How to repeat:
drop table if exists a;
check table a;
optimize table a;
repair table a;
analyze table a;

Suggested fix:
I think a more appropriate error would be to just return this:
+--------+-------+----------+------------------------------+
| Table  | Op    | Msg_type | Msg_text                     |
+--------+-------+----------+------------------------------+
| test.a | check | Error    | Table 'test.a' doesn't exist |
+--------+-------+----------+------------------------------+

Also, look at Msg_type column.  One row is 'Error' and the other is 'error', which looks inconsistent.
[19 Nov 2007 14:35] Chad MILLER
Stealing this since it blocks an important bug of mine, Bug#25347.
[3 Oct 2008 13:39] Chad MILLER
This is still in the main trees, as of late September 2008.

Server version: 6.0.7-alpha-debug-log Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> drop table if exists a;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> check table a;
+--------+-------+----------+------------------------------+
| Table  | Op    | Msg_type | Msg_text                     |
+--------+-------+----------+------------------------------+
| test.a | check | Error    | Table 'test.a' doesn't exist | 
| test.a | check | error    | Corrupt                      | 
+--------+-------+----------+------------------------------+
2 rows in set (0.00 sec)

mysql> optimize table a;
+--------+----------+----------+------------------------------+
| Table  | Op       | Msg_type | Msg_text                     |
+--------+----------+----------+------------------------------+
| test.a | optimize | Error    | Table 'test.a' doesn't exist | 
| test.a | optimize | error    | Corrupt                      | 
+--------+----------+----------+------------------------------+
2 rows in set (0.00 sec)

mysql> repair table a;
+--------+--------+----------+------------------------------+
| Table  | Op     | Msg_type | Msg_text                     |
+--------+--------+----------+------------------------------+
| test.a | repair | Error    | Table 'test.a' doesn't exist | 
| test.a | repair | error    | Corrupt                      | 
+--------+--------+----------+------------------------------+
2 rows in set (0.00 sec)

mysql> analyze table a;
+--------+---------+----------+------------------------------+
| Table  | Op      | Msg_type | Msg_text                     |
+--------+---------+----------+------------------------------+
| test.a | analyze | Error    | Table 'test.a' doesn't exist | 
| test.a | analyze | error    | Corrupt                      | 
+--------+---------+----------+------------------------------+
2 rows in set (0.00 sec)
[27 Feb 2009 8:39] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67799
[27 Feb 2009 15:53] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/67883
[4 Mar 2009 12:19] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68229

2827 Staale Smedseng	2009-03-04
      Missing change to funcs_1.myisam_views after push of Bug#29458
[5 Mar 2009 8:41] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68316

2830 Staale Smedseng	2009-03-05
      Addendum to previous patch for Bug#29458. A number 
      of system errnos when opening a table are masked 
      as ER_FILE_NOT_FOUND. This patch handles this as
      "Operation failed" as well.
[5 Mar 2009 10:35] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68340

2833 He Zhenxing	2009-03-05 [merge]
      Auto merge
[5 Mar 2009 10:36] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/68341

3102 He Zhenxing	2009-03-05 [merge]
      Auto merge
[13 Mar 2009 19:03] Bugs System
Pushed into 5.1.33 (revid:joro@sun.com-20090313111355-7bsi1hgkvrg8pdds) (version source revid:zhou.li@sun.com-20090311061050-ihp0g77znonq1tuq) (merge vers: 5.1.33) (pib:6)
[18 Mar 2009 2:47] Paul DuBois
Noted in 5.1.33 changelog.

CHECK TABLE, REPAIR TABLE, ANALYZE TABLE, and OPTIMIZE TABLE
erroneously reported a table to be corrupt if the table did not exist
or the statement was terminated with KILL.

Setting report to NDI pending push into 5.0.x/6.0.x.
[18 Mar 2009 13:20] Bugs System
Pushed into 6.0.11-alpha (revid:joro@sun.com-20090318122208-1b5kvg6zeb4hxwp9) (version source revid:matthias.leich@sun.com-20090310140952-gwtoq87wykhji3zi) (merge vers: 6.0.11-alpha) (pib:6)
[18 Mar 2009 14:25] Paul DuBois
Noted in 6.0.11 changelog.

Setting report to NDI pending push into 5.0.x.
[9 May 2009 16:40] Bugs System
Pushed into 5.1.34-ndb-6.2.18 (revid:jonas@mysql.com-20090508185236-p9b3as7qyauybefl) (version source revid:jonas@mysql.com-20090508100057-30ote4xggi4nq14v) (merge vers: 5.1.33-ndb-6.2.18) (pib:6)
[9 May 2009 17:37] Bugs System
Pushed into 5.1.34-ndb-6.3.25 (revid:jonas@mysql.com-20090509063138-1u3q3v09wnn2txyt) (version source revid:jonas@mysql.com-20090508175813-s6yele2z3oh6o99z) (merge vers: 5.1.33-ndb-6.3.25) (pib:6)
[9 May 2009 18:35] Bugs System
Pushed into 5.1.34-ndb-7.0.6 (revid:jonas@mysql.com-20090509154927-im9a7g846c6u1hzc) (version source revid:jonas@mysql.com-20090509073226-09bljakh9eppogec) (merge vers: 5.1.33-ndb-7.0.6) (pib:6)
[11 May 2009 11:03] Davi Arnaut
5.0 bazaar trigger failed to run. Pushed to 5.0.80
[12 May 2009 0:37] Paul DuBois
Noted in 5.0.80 changelog.