This is a very controversial topic, so I'll start with a list of disclaimers:
- I didn't want to write this at first, but Arunbear and barrd asked me to do it. Since I ranted a bit about mysql I felt obliged to justify my statements
- I used to like mysql, and I still use it for older projects. The reasons below aren't enough for me to convert old projects, but enough to use postgres for new ones
- I very much appreciate the effort from the mysql developers, and don't mean to insult them. I just want to point out that it has weaknesses.
- Many of the problems below have fixes, but they aren't fixed by default (for example you have to set some variables to enforce sane behaviour). I list them nonetheless, because you first have to know about the gotcha, and many people fall into the traps at least once before changing the settings. IMHO sane defaults are very important. I can mostly work around that stuff, but I see no reason why I should have to.
- I tried to list only what I experienced myself in order not to spread FUD.
Data Integrity
Inserting invalid data
If you insert invalid data, mysql does not complain. (Unless you modify the sql_mode variable, available starting from version 5.0 ). If you insert an arbitrary string into a date column, you'll just get a default value.
Transactions
Not all backends supports transactions. If you use transactions that involve tables with other backends, you'll get weird behaviour on rollback - all innodb tables will roll back, all others won't.
Also stuff like ALTER TABLE and CREATE TABLE don't roll if done inside of a transaction. IMHO if it can't roll the changes back, it should forbid them inside a transaction - otherwise you risk losing your data.
Foreign keys
Last I looked, foreign key constraints didn't work. This seems to be fixed now innodb, but requires migration to innodb.
Character Encodings
Mysql offers a myriad of options for handling character encodings in text columns, but I could never get them to really work unless I set everything to the same encoding. I don't know if that's my fault mysql's, but ususally I'm not too bad at reading documentation and setting things up.
Handling Failure
Just the other day I tried to import an sql dump - not a big deal you might think:
bzcat dump.sql.bz2 | mysql $table
But, somehow, it looped. Ususally the import takes about five minutes, after 20 minutes I hit Ctrl+C and aborted the import. For some other reasons I had to reboot the machine, and during that reboot mysql didn't start:
Starting MySQL database server: mysqld . . . . . . . . . . . . . . failed!
Very helpful error message indeed. It turned out that my root partition was full. But it didn't tell - it just looped. Turns out that big chunk on disk were the mysql log files (not mysql's fault this time), and I wanted to delete them. So how do you delete mysql log files? You type mysql and truncate the binary logs. No, you don't - because mysql didn't start up in the first place, because the disk was full.
So instead I just deleted the log files - and mysql wouldn't start, because the log files were missing. Since I was offline at that time (no chance to google for anything) the only thing that I found I could was to reinstall the mysql server. Just because my partition was full.
I regularly hang out on #perlde on irc.perl.org, and many read quite a few reports where mysql didn't behave very gracefully in error situations.
Documentation
The mysql documentation is quite good, but by Debian's standards it's non-free, so it can't be included in the distribution. That's a big disadvantage if you're often offline.
erix posted this link in the CB that explains the reliabilty issues with mysql in more detail.