Well ever had the title as an error in MySQL before?

Me either. I didn't even think it would be an issue considering I am using InnoDB for most of the tables too. Well this morning I hit that issue. I did some googleing around and it turns out that InnoDB is quite dumb when it comes to its default setup.

By default if you dont set up how it saves files to disk it sticks all data into one huge file. Not one file per database, or one file per table, but one single file. Why it does this I have no idea. I can understand letting people modify it to be in different files but really. Mine seems to have assumed the maximum size was 2 gig for all InnoDB data which isnt very pratical.

So I found the following link, http://dev.mysql.com/doc/refman/4.1/en/adding-and-removing.html and thought ok fine I will just increase the tablespace restart and all should be fine. Alas no. I restarted MySQL and still the same issue.

Fine. I can sort this out. The solution was the following,

mysqldump -u root -p --all-databases > alldatabases.sql

Then clean out all of the existing database files. First I stopped mysql then went to where it stores everything, /var/lib/mysql in my case and deleted all the ib_logfile's the ibdata1 file (the InnoDB database file) and each directory which contained database information.

I then had to modify the my.cnf file and I added the following lines,

innodb_data_file_path = ibdata1:10M:autoextend:max:3999M;ibdata2:10M:autoextend:max:3999M;ibdata3:10M:autoextend:max:3999M

That should take care of the problem, at least until I hit a file of 12 gig in size. Since my upper limit before was 2 gig I should be right for the forseeable future. I will increase this out to something like 64 gig at some point.

Restart MySQL and run the following,

mysql -u root -p < alldatabases.sql

And im back in action. No more stupid table is full messages.

EDIT - Should mention the following points.

1. Check what filesystem you are using before setting the max size so you dont go over what is allowed (DOH!).
2. Make sure you put the config in the [mysqld] setting part to ensure that it is actually picked up.

EDIT2 - Looks like you cant have multiple autoextend files in your config. I have replaced it with the following,

innodb_data_file_path = ibdata1:1500M;ibdata2:1500M;ibdata3:1500M

This makes the inital startup VERY slow as it has to create 4.5 gig of something to store everything but gets around the filesize limit of my VPS. I wish they had large file support enabled.

EDIT3 - Finally got the bastard to work. First it wouldnt use the config, then it ignored the size limits and broke things, and then it disabled InnoDB for some stupid reason. Its working now. But my god... im sure it would have been faster to switch to Postgresql.

Published on Tuesday 09th March 2010

Comments (0)