SQL constraints are usually good, as they help maintain a certain consistency of the database. But sometimes they suck. And when they do, they suck really bad and yield some weird errors, most common being:

Can’t create table `mydatabase`.`mytable` (errno: 150)

Annoying little error, isn’t it? Especially when you want to load a backup dump and you can’t because of unfulfilled constrains. This has happened to me on several occasions, especially since I’ve switched over to InnoDB and started using constraints. So far, I was unable to find any elegant solution, just to switch the foreign key checks off at the beginning of the import and switch them back on at the end.

SET FOREIGN_KEY_CHECKS = 0;

# regular dump goes here
# CREATE TABLE `my_table` (...)
# bla bla bla

SET FOREIGN_KEY_CHECKS = 1;

Not exactly rocket science, but I hope it saves you some headaches.

As you probably know, the most popular Open Source RDBMS, MySQL, is being acquired by Oracle, which – some say – will most likely mean the end of the open source project. MySQL has been eroding Oracle’s profits for quite some time now, and there’s a fat chance that Oracle will kill MySQL in order to keep its high profits.

Why should we care? Well, without MySQL, the web would be much different than it is today. Popular open source applications like WordPress and Drupal, millions of websites and applications rely on MySQL. A lot of start-up companies use MySQL in order to cut down costs and not invest in expensive, proprietary RDBMSs.

But we, the people, have the power to stop it by writing to the EC in order to block the transaction. Read what Michael “Monty” Widenius, MySQL’s creator, has to say on the matter: Help saying MySQL.

PS: Do you know the difference between God and Larry Ellison? God knows He’s not Ellison…

What is a bad day? How do you define it? Its very definition varies from person to person and from job to job. For some, a bad day is when they lose the bus or the subway and are late for work.

For others, a bad day is when their hair looks weird or they can’t find a good parking lot. For economists and bankers, a bad day is when…well, everyday, given the current economical climate.

For the average programmer a bad day is when he manages to fuck up something on a epic scale on the production server. Let’s say a database with thousands of users. Today it happened to…well…this friend of mine.

Luckily my friend backups his work on a daily basis and is prepared for such situations. One of the scripts he uses for this is the awesome AutoMySQLBackup script, which I – I mean he recommends to all of you. It’s really simple to use, all you have to do are some minor configurations and add the script to be ran daily as a cron job.

Real men *do* use backups!

While studing for the ZCE exam, I’ve read a lot about prepared statements, especially prepared statements with PDO. According to the textbook, using prepared statements in repetitive queries (such as inserting multiple rows in the database and so on) can lead to a substantial improvement due to the fact that the query is compiled one time and the the values are quoted and put in place, whereas in the “traditional” way, a query is compiled every time it’s executed.

So I’ve decided to give it a try, and see how faster prepared statements actually are. To give the test some objectivity and relevance, I’ve calculated the amount of time required for 1000 inserts, repeated this operation 100 times and computed the average execution time. Read the rest of this entry »