My list of reasons for never using or recommending Amazon’s MySQL RDS service grows every time I experience problems with customers. This was an interesting and still unresolved issue.
ERROR 126 (HY000): Incorrect key file for table '/rdsdbdata/tmp/#sql_5b7_1.MYI'; try to repair it
You may see this is a MyISAM table. The MySQL database is version 5.5, all InnoDB tables and is very small 100MB in total size.
What is happening is that MySQL is generating a temporary table, and this table is being written to disk. I am unable to change the code to improve the query causing this disk I/O.
What I can not understand and have no ability to diagnose is why this error occurs sometimes and generally when the database is under additional system load. With RDS you have no visibility of the server running the production database. While you have SQL access, an API for managing MySQL configuration options (I also add not all MySQL variables), and limited system statistics via a graphical interface, all information about the system performance, disk configuration etc is hidden and not accessible. This is a frustrating limitation of using RDS.
NOTE: While I cannot recommend RDS, I am very happy with AWS EC2 services when correctly configured. For a cloud based MySQL solution I would definitely recommend greater control over your MySQL database using EC2 and EBS.
I’ve seen this issue happen primarily when I set up a limited size RAM disk (several Gigs) for MySQL’s tmp directory in an effort to reduce disk I/O. When I increased the size of the ram disk, the error happened noticeably less often, but did not quite disappear.
My error log generally gets sporadic errors like so:
110517 10:56:31 [ERROR] /usr/sbin/mysqld: Sort aborted
110517 10:56:31 [ERROR] /usr/sbin/mysqld: Sort aborted
110517 10:56:31 [ERROR] /usr/sbin/mysqld: Sort aborted
110522 11:59:20 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/var/lib/mysql/ramdisk/#sql_9ed_0.MYI'; try to repair it
110523 23:45:49 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/var/lib/mysql/ramdisk/#sql_9ed_0.MYI'; try to repair it
110524 13:42:02 [ERROR] /usr/sbin/mysqld: Sort aborted
110524 13:42:02 [ERROR] /usr/sbin/mysqld: Incorrect key file for table ‘/var/lib/mysql/ramdisk/#sql_9ed_0.MYI'; try to repair it
It seems to recover just fine (re-running the query or otherwise), but I haven’t been able to draw any better conclusions other then the partition running out of space. MySQL has never handled running out of disk very gracefully (a full log partition used to be a hilarious way to get init to hang on MySQL during a linux boot). If you find out any further information as to causes and ways to mitigate the temp table issue, I’d love to hear them. The files created in the temp directory tend to be immediately unlinked which makes it very hard to track actual disk usage without low level file system polling. Although I haven’t put a huge amount of debug time into this issue as of yet.
I had this issue on our production server just recently. It turned out to be running out of room in the TMP_DIR location. I’d say RDS is making some assumption regarding temp space its clients would need and your customer outgrew that either legitimately or through bad schema/query.
I had hoped that a reader may undertake to provide an explanation of this problem rather then myself just giving the answer to readers.
This error is caused when the temporary directory location (specified by the tmpdir system variable) is full. As mentioned in my post, you have no access to the machine to confirm this, or verify the size of this location.