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.
