Clear questions and runnable code
get the best and fastest answer
This is a very controversial topic, so I'll start with a list of disclaimers:
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.
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.
Last I looked, foreign key constraints didn't work. This seems to be fixed now innodb, but requires migration to innodb.
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.
Just the other day I tried to import an sql dump - not a big deal you might think:
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:
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.
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.