If MySQL hasn't let you down, then, frankly, you are just lucky. In fact, I'd have trouble to even call MySQL a database as it even lacks the one feature that a database needs to have: The ability to get the data back that you put in it.
If by accident you put Latin 1 (or any other non-utf-8-data) into a database that is configured as UTF8, MySQL will go ahead and cut off your data at the first byte with the 8th bit set. No error. No warning. The data is just gone. The only way to find out this happened is by reading back after every write - something I don't want to have to do.
Or allowing to
alter table whatever add somecolumn text not null;
that succeeds and consequently sets every row of somecolumn to NULL, violating the constraint.
Or inserting strings longer than allowed by the datatype which MySQL doesn't complain about but just truncates them - another case of having to read and compare the data just stored.
And don't get me started about corrupt on disk data, leading to unreadable tables. But worse - mysqldump at least once exited with an exit code of 0 even though it failed to read one of these corrupt tables. What's worse: It stopped the dumping process and didn't dump any tables and even databases following the corrupt table - yeah. I thought I backed up, but in fact I didn't.
Stuff like this must not happen with a database.
Stuff like this never happened to me with PostgreSQL.
It might be harder to set up. It might feel a bit foreign at first. It might provide features you think you don't need. But at least it doesn't destroy data I entrusted it with.
In MySQL, you can set the SQL mode to make it just as strict as Postgres. I'm still using MySQL for two reasons: a) It's already in place and it works great and b) I'm very familiar with it. For the next project (which may never come), I'll definitely be looking into Postgres.
In MySQL, you can set the SQL mode to make it just as strict as Postgres.
How much are you inclined to trust this mode which was bolted on as an afterthought, though?
I'm maintaining a bunch of MySQL installs for customers and the amount of random failures I've seen even with InnoDB and strict mode during backup/restore and upgrades is just not funny.
Obscure second guesses during even a minor version upgrade are the rule rather than the exception. 'mysql_upgrade' hardly ever worked right for me at first try. A straightforward dump/restore is not an option either because they change the schema of the system tables all the time (which, to add insult to injury, are still MyISAM).
Then you also frequently bump into gems like the following:
Incompatible change: As of MySQL 5.5.3, the server includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers.
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision.
(From: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo...)
Oh, and don't get me started on what they call "replication", which will happily, silently desync or corrupt data in various situations.
So well, yeah. Perhaps strict mode indeed works right in all cases. Perhaps.
Comments
If MySQL hasn't let you down, then, frankly, you are just lucky. In fact, I'd have trouble to even call MySQL a database as it even lacks the one feature that a database needs to have: The ability to get the data back that you put in it.
If by accident you put Latin 1 (or any other non-utf-8-data) into a database that is configured as UTF8, MySQL will go ahead and cut off your data at the first byte with the 8th bit set. No error. No warning. The data is just gone. The only way to find out this happened is by reading back after every write - something I don't want to have to do.
Or allowing to
that succeeds and consequently sets every row of somecolumn to NULL, violating the constraint.Or inserting strings longer than allowed by the datatype which MySQL doesn't complain about but just truncates them - another case of having to read and compare the data just stored.
And don't get me started about corrupt on disk data, leading to unreadable tables. But worse - mysqldump at least once exited with an exit code of 0 even though it failed to read one of these corrupt tables. What's worse: It stopped the dumping process and didn't dump any tables and even databases following the corrupt table - yeah. I thought I backed up, but in fact I didn't.
Stuff like this must not happen with a database.
Stuff like this never happened to me with PostgreSQL.
It might be harder to set up. It might feel a bit foreign at first. It might provide features you think you don't need. But at least it doesn't destroy data I entrusted it with.
edit: Also see the rant on my blog I posted when I ran into the UTF8-problem: http://www.gnegg.ch/2008/02/failing-silently-is-bad/
In MySQL, you can set the SQL mode to make it just as strict as Postgres. I'm still using MySQL for two reasons: a) It's already in place and it works great and b) I'm very familiar with it. For the next project (which may never come), I'll definitely be looking into Postgres.
In MySQL, you can set the SQL mode to make it just as strict as Postgres.
How much are you inclined to trust this mode which was bolted on as an afterthought, though?
I'm maintaining a bunch of MySQL installs for customers and the amount of random failures I've seen even with InnoDB and strict mode during backup/restore and upgrades is just not funny.
Obscure second guesses during even a minor version upgrade are the rule rather than the exception. 'mysql_upgrade' hardly ever worked right for me at first try. A straightforward dump/restore is not an option either because they change the schema of the system tables all the time (which, to add insult to injury, are still MyISAM).
Then you also frequently bump into gems like the following:
Incompatible change: As of MySQL 5.5.3, the server includes dtoa, a library for conversion between strings and numbers by David M. Gay. In MySQL, this library provides the basis for improved conversion between string or DECIMAL values and approximate-value (FLOAT/DOUBLE) numbers.
Because the conversions produced by this library differ in some cases from previous results, the potential exists for incompatibilities in applications that rely on previous results. For example, applications that depend on a specific exact result from previous conversions might need adjustment to accommodate additional precision. (From: http://dev.mysql.com/doc/refman/5.5/en/upgrading-from-previo...)
Oh, and don't get me started on what they call "replication", which will happily, silently desync or corrupt data in various situations.
So well, yeah. Perhaps strict mode indeed works right in all cases. Perhaps.