[Dbix-class] any factual comparisons of Oracle, PostgreSQL, MySQL ?

Robert Kinyon rob.kinyon at gmail.com
Thu Apr 14 17:33:25 GMT 2011


On Thu, Apr 14, 2011 at 13:19, Matija Grabnar <matija at serverflow.com> wrote:
>> If you care about keeping your data consistent, do not use it. You can
>> never rely on MySQL honoring constraints, so you have to do all your data
>> integrity checking in the application. As a trivial demonstration, create a
>> table with a NOT NULL Text column and then insert an empty row. MySQL will
>> happily do as you say, putting an empty string in the Text column. It is
>> possible to bludgeon MySQL into nearly being reliable, but it's not the
>> default behavior and it will vanish at the slightest mistake.
>
> I can see how people can like Postgresql more than MySQL - but this
> statement is really going a bit too far.

Actually, no, it's not. In the strictest possible mode, employing
every single way to ensure your data is consistent and that foreign
keys are being honored, there's a simple thing anyone can do to screw
you with MySQL.

SET FOREIGN KEYS OFF;

BAM! You're done. Finished. You cannot trust a single thing in your
database the way you can in EVERY SINGLE OTHER relational database.
Did you know that there is a foreign key checking stored procedure in
MySQL? It tells you what foreign keys you have that are illegal. What
other system has that?

And that's not counting the pathological behavior MySQL has for:
    * subqueries
    * views
    * index picking
    * comparison behavior

And, what other database has a "strict" mode? Not only is there a way
you can enable strictness (implying that it can be disabled), but IT'S
NOT EVEN THE DEFAULT.

Oh, and when you disable strictness, do stuff, then re-enable
strictness . . . the stuff you did while not strict remains and is
legal. So, technically, whenever you read, you really should assume
that the data was entered in the least strict of modes.

That's MySQL.

Rob



More information about the DBIx-Class mailing list