Today, I have spent a couple of hours attempting to do some analysis on a large table. I say attempting, because every time I tried to add an index to the table after importing it, MySQL would crap out, and CHECK TABLE would report got error: 22 when reading datafile. Really weird stuff! Luckily for me, I managed to track down the cause, so I'm writing it down for the next time I run into this difficult to solve problem.

Here's the output from MySQL:

mysql> ALTER TABLE log ADD INDEX `date` (`date`);
ERROR 126 (HY000): Incorrect key file for table './analysis/#sql-49a0_1.MYI'; try to repair it
mysql> CHECK TABLE log;
+--------------+-------+----------+-------------------------------------------------------+
| Table        | Op    | Msg_type | Msg_text                                              |
+--------------+-------+----------+-------------------------------------------------------+
| analysis.log | check | error    | got error: 22 when reading datafile at record: 0      |
| analysis.log | check | Error    | Error reading file './analysis/log.MYD' (Errcode: 22) |
| analysis.log | check | error    | Corrupt                                               |
+--------------+-------+----------+-------------------------------------------------------+
3 rows in set (0.45 sec)

As you can see, it's not very helpful at all. Running perror 22 has this to say:

# perror 22
OS error code  22:  Invalid argument

Ah, yet more incredibly useless information. At this point, I assumed the data was somehow mangled, possibly due to character sets, or perhaps bad data was getting indexed. All completely wrong. The culprit in this case was a huge read_buffer_size setting in my.cnf!

At some point, I must have copied an example config off the web which included this setting (among others to speed up MySQL when using it only for development), and this causes large (?) files to become corrupt from MySQL's point of view. My guess is that there's another memory limit that is being exhausted, which causes MySQL to get its knickers in a twist.

So, to fix it, I simply removed the read_buffer_size setting entirely from my.cnf entirely, leaving it to the default.