Thursday, 21 March 2013
Databases from a webdev point of view
I managed to screw up our test server today. It's so easy to say: "Oh whatevs, let's just update the server, it cannot be bad". And yeah - so it went to the point when mysql was gonna be updated. Yeah, why not. And then it was asking if I want to update the config. It seemed pretty serious but I just hit the f*yeah button.
Then all the sites started breaking. Checked one and it said InnoDB is an invalid database engine. Thanks God that almost all the sites used it. Tried to craft a little in the DB but it was totally broken. Most of the operations were blocked.
Quickly ran to Google and found some solutions. It happened with some and they suggested to remove the log files (/var/lib/mysql/ib*). For me it didn't take any effect. Then the last savior idea was to add the configuration manually to the my.cnf file. It was a bit strange, with the setting on it didn't start. So after a proper panic and headache I decided to reinstall mysql-server. Luckily if you do as I did you can keep your database files. However nothing changed, still no InnoDB. (You can check with: 'show engines;'.) So I took the backup dumps and copied them over to the new server. It worked fine, however some operation fired a small problem - "/mnt/ramdisk does not exist". I quickly checked the updated my.cnf and then I've found that the temp dir was set to the one above. Of course it did not exist. I changed it to /tmp - all fine. Just out of curiosity I tried to add the command to the my.cnf file that makes InnoDB default - restarted mysql - and woot InnoDB existed again. Lesson learned - everybody needs some breathing space. Story ends.
That always make me scared - how much we have to know about our environment. I was thinking if I'd be a better developer if I'd have hardcore MySQL skills. In general I don't have problem with databases. The only thing I can remember is some throughput issue when imported a large dataset. I had to increase the buffers and some limits. The very basic thing you have to know is the syntax, normal form and some basic features of the engine. Forgot to mention - as a web developer. However that may sounds a bit lazy. But really. Everyday web development does not leverage enterprise solutions. I don't do stored procedures. I don't do scalable databases in the cloud. And I think the whole agile technology tier seems to be moving towards that way. The easier to get in the more can help and the sooner it can be fixed. Servers and rams are cheap, as the good old saying states.
I was subscribed for the MySQL podcast for a long time and loved it. And as always I'd love to live in a world where I'm forced to fight with DBDaemons all the time so actually mastering those skills would be essential.
What do you think? I hope you don't get me wrong. Reading references is like looking at naked chicks on the beach, it's fun and useful. But there are times when it doesn't pay back. I just try to provoke you.