Jonathan Vanasco wrote:
with mysql5 , they decided to make mysql easier for everyone by setting a default to ignore errors and coerce the data into a valid format
MySQL has always done that. Being able to turn that off was one of the big new features in MySQL 5.
it turns out that you can set 'sql_mode=TRADITIONAL' in the my.cnf , and it will enforce rules by default. BUT those rules can be disabled during a transaction. i don't want more grant privileges to worry about managing to keep them off should i get a sql injection attack, or the my.cnf file gets overwritten on some update.
If you have SQL injection vulnerabilities in your code, changing the sql_mode is the least of your worries.
why someone would have that as the DEFAULT setting is beyond me.
I don't like it either, but they don't exactly try to hide it. It's been that way since the first release.
MAKE SURE YOU HAVE sql_mode AS TRADITIONAL. mysql isn't actually transaction safe without it - it will coerce invalid data to fit your schema, which means any field can be corrupt and foreign keys are useless.
I don't think that's a fair statement. MySQL's transaction and locking system is every bit as good as the one in PostgreSQL, and doesn't depend on the the sql_mode setting. That's only about data validation.
You can definitely enforce foreign keys, even in older versions like the 4.1 series. If you set a column with a foreign key constrain to NOT NULL, no values except valid foreign keys will be accepted for it. I use this frequently instead of the unenforced "enum" type.
If you aren't comfortable using the strict SQL mode, then PostgreSQL definitely sounds like the right choice for you. It's a solid choice and many people use it with mod_perl, so I expect you'll have no trouble. The default isolation level is somewhat less isolated than the default with InnoDB, but that's usually the right choice for web applications.
- Perrin
