Mailing-List: contact modperl-help@perl.apache.org; run by ezmlm
Precedence: bulk
Received-SPF: neutral (asf.osuosl.org: 209.86.89.69 is neither permitted nor
 denied by domain of perrin@elem.com)
Message-ID: <4412387D.7050600@elem.com>
Date: Fri, 10 Mar 2006 21:39:57 -0500
From: Perrin Harkins <perrin@elem.com>
User-Agent: Thunderbird 1.5 (Windows/20051201)
MIME-Version: 1.0
To: Jonathan Vanasco <modperl-list@2xlp.com>
CC: modperl_users List <modperl@perl.apache.org>
Subject: Re: Migrating from MySQL to PostGres -- Any mod_perl specific things
 I should be wary of?
References: <0C1FE548-66D6-4B8A-BAF1-C28D98F8AD09@2xlp.com>
 <1142030946.28877.59.camel@localhost.localdomain>
 <FCE2F695-9547-441C-87B5-24B748E8A32F@2xlp.com>
In-Reply-To: <FCE2F695-9547-441C-87B5-24B748E8A32F@2xlp.com>
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

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