perlmeditation
moritz
<p>This is a very controversial topic, so I'll start with a list of
disclaimers:</p>
<ul>
<li>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</li>
<li>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</li>
<li>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.</li>
<li>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 <i>very</i> important.
I can mostly work around that stuff, but I see no reason why I should have
to. </li>
<li>I tried to list only what I experienced myself in order not to spread
FUD.</li>
</ul>
<h3>Data Integrity</h3>
<h4>Inserting invalid data</h4>
<p>If you insert invalid data, mysql does not complain. (Unless you modify the
<c>sql_mode</c> variable, available starting from version 5.0 ). If you insert
an arbitrary string into a date column,
[http://dev.mysql.com/tech-resources/articles/mysql-data-integrity.html|you'll
just get a default value].</p>
<h4>Transactions</h4>
<p>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.</p>
<p>Also stuff like <c>ALTER TABLE</c> and <c>CREATE TABLE</c> 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.</p>
<h4>Foreign keys</h4>
<p>Last I looked, foreign key constraints didn't work. This seems to be fixed
now innodb, but requires migration to innodb.</p>
<h4>Character Encodings</h4>
<p>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.
<h3>Handling Failure</h3>
<p>Just the other day I tried to import an sql dump - not a big deal you might
think:
<code>
bzcat dump.sql.bz2 | mysql $table
</code>
<p>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:
<code>Starting MySQL database server: mysqld . . . . . . . . . . . . . .
failed!</code>
<p>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 <c>mysql</c> and
truncate the binary logs. No, you don't - because mysql didn't start up in the
first place, because the disk was full.
<p>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.
<p>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.
<h3>Documentation</h3>
<p>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.
<hr />
<p>[erix] posted
[http://wiki.postgresql.org/wiki/Why_PostgreSQL_Instead_of_MySQL:_Comparing_Reliability_and_Speed_in_2007|this
link] in the CB that explains the reliabilty issues with mysql in more detail.