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
[download]

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!
[download]

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.

---

---

Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!

Titles consisting of a single word are discouraged, and in most cases are disallowed outright.

Read Where should I post X? if you're not absolutely sure you're posting in the right place.

Please read these before you post! —

• How do I compose an effective node title?
• How do I post a question effectively?
• Markup in the Monastery

Posts may use any of the Perl Monks Approved HTML tags:

a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr

You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)

	For:		Use:
	&		&
	<		&lt;
	>		&gt;
	[		&#91;
	]		&#93;

Link using PerlMonks shortcuts! What shortcuts can I use for linking?

See Writeup Formatting Tips and other pages linked from there for more info.