Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

comment on

( [id://3333]=superdoc: print w/replies, xml ) Need Help??

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.


In reply to [OT] Why I don't use Mysql for new projects by moritz

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post; it's "PerlMonks-approved HTML":



  • Are you posting in the right place? Check out Where do I post X? to know for sure.
  • Posts may use any of the Perl Monks Approved HTML tags. Currently these include the following:
    <code> <a> <b> <big> <blockquote> <br /> <dd> <dl> <dt> <em> <font> <h1> <h2> <h3> <h4> <h5> <h6> <hr /> <i> <li> <nbsp> <ol> <p> <small> <strike> <strong> <sub> <sup> <table> <td> <th> <tr> <tt> <u> <ul>
  • Snippets of code should be wrapped in <code> tags not <pre> tags. In fact, <pre> tags should generally be avoided. If they must be used, extreme care should be taken to ensure that their contents do not have long lines (<70 chars), in order to prevent horizontal scrolling (and possible janitor intervention).
  • Want more info? How to link or How to display code and escape characters are good places to start.
Log In?
Username:
Password:

What's my password?
Create A New User
Domain Nodelet?
Chatterbox?
and the web crawler heard nothing...

How do I use this?Last hourOther CB clients
Other Users?
Others sharing their wisdom with the Monastery: (4)
As of 2024-03-19 03:01 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    No recent polls found