email MySQL 5.1 multiple tmpdirs and incorrect key file for table errors

Well I found out a few new things today...

Firstly if you see an error similar to this:

Incorrect key file for table '/tmp/#sql_913_4.MYI'; try to repair it

It's likely you've got a dodgy query that is creating a temp table so big it's maxing out all your available temp filesystem space.

Secondly if you try to get round this by throwing disk space at the issue then you'll come unstuck.

Queries that create a self-join cartesian product, and contain ORDER BY clauses, cause the server to materialize the entire result set into a temporary table before applying any limits. If you try to get round this by giving the server a larger tmp dir to expand into, then you'll probably watch your disk fill up very quickly, as these can chew up literally tens of gigs of space or worse.

(by the way for reference, i'm paraphrasing Mark Matthews comment at the end of this: http://bugs.mysql.com/bug.php?id=10075 and using experience from my own issue, similar results my be found with other queries that don't manage the data they're pulling back well)

Thirdly if you're trying to use mysql 5.1's new multiple tmpdir's option in the my.cnf file to provide that extra disk space you need to do it like this:

tmpdir = /tmp:/tmp2:/tmp3

Not like this:

tmpdir = /tmp/:/tmp2/:/tmp3/

As for some reason the mysql server doesn't like the trailing slash on the dir name and ignores the lot if you do it the latter way.

So there you go - I learned something new today, and hopefully, so have you!





Related posts on coderchris.com:

  1. MySQL 5.1 partitioning and loosing all your databases (or not)!
    I can across a few other funky things in MySQL 5.1 today that I...
  2. Bloody MySQL Bugs
    I’m looking to setup some replication between two servers and server B has none...
  3. MySQL 5.1.34 Upgrades on CentOS 4 – ho hum
    So, I needed to upgrade MySQL on our development boxes today and I was...
  4. MySQL Seconds Behind Master Weirdness
    So, I'm running show slave status and I'm seeing that my slave is around...
  5. Setting Up Ubuntu PHP 5.2, MySQL 5.1 and Apache 2 on a PS3
    Recently I wanted a new development box/streaming media server for use around the house...