perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Perrin Harkins <per...@elem.com>
Subject Re: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
Date Sat, 11 Mar 2006 02:39:57 GMT
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

Mime
View raw message