Bug #68772 New tables corrupted: Table ... is marked as crashed and should be repaired
Submitted: 26 Mar 2013 2:58 Modified: 1 May 2013 15:57
Reporter: Hankin Chick Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S2 (Serious)
Version:Ver 5.5.29-0ubuntu0.12.04.2-log OS:Linux (Ubuntu 12.04)
Assigned to: CPU Architecture:Any
Tags: myisam, repair table

[26 Mar 2013 2:58] Hankin Chick
Description:
mysqld  Ver 5.5.29-0ubuntu0.12.04.2-log for debian-linux-gnu on x86_64 ((Ubuntu))

Hi,
I've been having table corruption problems.

Every few hundred tables that I create can't be accessed, when I try to access it this error appears...
Error: Table 'xxx1' is marked as crashed and should be repaired

It never used to happen with mysql 5.1, only started when I upgraded to v5.5

I'm not doing any updates on the table, only a 'load data' command to load it up and then selecting it afterwards.

Things that I've checked..
* I'm not running out of disk space.  The disk is mirror raided.
* No errors in mysql logs.
* No errors appeared during the 'load data' command.  Only afterwards when I tried to select the table.

Thank you.

show create table

CREATE TABLE `xxx1` (
  `map_time_id` int(11) NOT NULL,
  `tile_id` int(11) NOT NULL,
  `x` smallint(6) NOT NULL,
  `y` smallint(6) NOT NULL,
  `type` smallint(4) NOT NULL,
  `level` tinyint(4) NOT NULL,
  `city_id` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `province_id` char(1) NOT NULL,
  `city_name` varchar(255) DEFAULT NULL,
  KEY `xxx1_user_id` (`user_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

mysql> repair table xxx1;
+------------------------+--------+----------+------------------------------------------------+
| Table                  | Op     | Msg_type | Msg_text                                       |
+------------------------+--------+----------+------------------------------------------------+
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896388   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896444   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896516   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896596   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896644   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896692   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896740   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896788   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896836   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2896884   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2897404   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2897548   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2897620   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2897804   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2897852   |
| xxx1 | repair | info     | Wrong bytesec:  67-  0- 36 at 2898448; Skipped |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2898532   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2898580   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2898780   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2898932   |
| xxx1 | repair | info     | Wrong bytesec:  67-  0- 40 at 2899536; Skipped |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2899628   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2899724   |
| xxx1 | repair | info     | Key 1 - Found wrong stored record at 2899940   |
| xxx1 | repair | warning  | Number of rows changed from 640000 to 639965   |
| xxx1 | repair | status   | OK                                             |
+------------------------+--------+----------+------------------------------------------------+
26 rows in set (1.33 sec)

Another time when it happened...

| xxx2 | repair | info     | Key 1 - Found wrong stored record at 3821640 |
| xxx2 | repair | warning  | Number of rows changed from 640000 to 639999 |
| xxx2 | repair | status   | OK                                           |

How to repeat:
* Use 'load data' to load up a myisam table.  
* The 'load data' always succeeds. But once every few hundred times I cannot access it afterwards, I get an error...
Error: Table 'xxx1' is marked as crashed and should be repaired

Suggested fix:
I guess I can check the table after every 'load data', and reload it if it doesn't work.
[1 Apr 2013 15:57] Shane Bester
Hi Hankin,

I don't see any evidence of a bug yet.  What happens if you convert the table to InnoDB;  does it get corrupt then?  We really need some definitive testcase.  It would be good to isolate the exact LOAD DATA causing problems.

If you have a slave server, do slaves tables get corrupted too?
[1 Apr 2013 15:57] Shane Bester
We also need to review the complete mysql error log, in case mysqld has been crashing (in which case corruption of myisam is expected).
[2 May 2013 1: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".