Friday, June 08, 2007

Problems with file upload into MySQL in rails

Today, I was deploying a rails site of mine in production on a Linux virtual host. Hmm, not without problems. Some of them not due to Rails, others were.

MySQL troubles
The upload was failing and I was getting this error from MySQL afterwards:

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

After some digging around I found out it was due to /tmp on my Linux virtual host being only 32MB and mysql seems to use a temporary MyISAM table when inserting large binary data. When there wasn't enough space in /tmp for the temporary table, mysql failed.

I fixed this by setting "tmpdir = /var/tmp" (which has lots of space on my host) in my.cnf

Rails / CGI Upload
Now, mysql did not crash and corrupt on me anymore, but the upload still failed.

It turns out that Rails calls CGI::QueryExtensions.read_multipart, which calls, which again calls core class Dir::tmpdir and this method uses environment variables to determine tmp dir to place uploaded file into.

Fixed this by setting ENV['TMPDIR'] = '/var/tmp' in config/environments/production.rb.

Giving it a tmp with more space available resulted in files being fully uploaded okay in /var/tmp, but the files where left there and the mongrel process hangs from there on. No information available in mongrel.log or production.log. WTF!

Back in mysql trouble
I then remembered back in time when I had some other problems with mysql/activerecord communication, which I solved by installing a mysql gem compiled against the platforms mysql devel libs. Like this:

sudo aptitude install libmysqlclient-dev
sudo gem install rails

Which instantly gave me better error messages. Now, I could see an entry in production.log about "max_allowed_packet" being too small. Funny, cause I specificly set that to 16M in my.cnf and the file uploaded was only 8M. Hmm, maybe it is inserted in a more voluminous format, so I raised it to 32M.

And it worked!

Removing old CGI upload data?
One thing left. The temporary files from the CGI library from the upload keeps lying around until I restart the mongrel instanses. If the instanses die, the data does not get cleaned up. And waiting for a restart before cleanup is also not that nice. Digging a little deeper into I notice it will be cleaned up at garbage collection time of the Tempfile instance unless an explicit close/unlink is done. As far as I can see, no explicit unlink is done in CGI::QueryExtensions.read_multipart. Guess I will have to wait for the ruby garbage collector to come by.


Anonymous said...

I stumbled over your Mysql 'Incorrect key file for table' solution in an attempt to solve a similar problem and the tmpdir in the my.cnf, also solved it for me.
Thanks a lot :-)

Anonymous said...

Great advice on the 'Incorrect key file' error. Worked like a charm. I've been searching for a solution for hours before I found your advice. Thanks!

Frosty said...

Thanks for the MySQL advice

Anonymous said...

Your post is still helping developers, years later. Thanks a lot!