Doh!

Published on Wednesday 10th March 2010

Comments (5)

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)

Just something I have been using for a while. I converted it to use PDO objects last night, tested it and all seems alright. Currently it assumes the following,

1. Only supports the following datatypes int, text, varchar, datetime, tinyint and blob types. The last two are not really tested since I dont use them very often. Other types may work, but will generate a warning.

2. Requires an autoincrement primary key which is an int. Pretty standard stuff though.

3. You have a PDO php object to pass in (singleton pattern).

I am using it for a few things now though and it seems quite stable and good. Certainly makes it easier to write simple CRUD screens since you can just use an object to add/edit/delete.

I should also mention it has validator generation code in there as well... I would advise that you dont play with them till I get my auto validator code working 100%. The idea is that in the future you can generate your entity against the database, then plugin whatever you need to the frontend and it can generate the HTML form and validate it for you. Doing this would take out the two most annoying things for me when it comes to web development. The first being writing simple SQL for updates when im modifying the tables a lot (since this generates it for you) and the second being validating forms.

Anyways code is below. Feel free to use it however you want. I will probably clean it up at some point and stick it on google code.

generate_entities.txt

Published on Monday 08th March 2010

Comments (2)

The title is all I need to say about AC/DC Black Ice Tour. Totally awesome go and see it while they are still performing.

In other news my video card on my pc seems to have stared to die. I turn it on and get weird yellow lines down the screen on anything thats GUI and weird ascii symbols in a manner similar to the matrix when its in console mode. Quite annoying. What makes it worse is that I wanted to do some work with some stuff on it. No worries I back up to my VPS. Guess what, its down too. Who says lightning never strikes twice.

Published on Sunday 21st February 2010

Comments (3)

I should mention RE the below post about a web crawler. I have been writing and running them for a few years now with each one getting more and more stable and learning more and more about each one. I feel pretty confident that when I fire one up now that it is unlikely to crash someones site or suck down everything on the web.

Published on Wednesday 17th February 2010

Comments (0)