perl-modperl mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Jonathan Vanasco <modperl-l...@2xlp.com>
Subject Re: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
Date Sat, 11 Mar 2006 00:08:09 GMT

I was using InnoDB w/transactions in mysql, because i needed  
transactions and I've used mysql a lot before.

i was using mysql4 fine, but came into some odd bug caused by  
changing the collation of a table to ut8.  the only way to get rid of  
it was to uninstall all of mysql & the data, or migrate to 5.0.  so i  
migrated.

and then well, the mysql5 team just turned me off bigtime w/an  
upgrade feature

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

	create table testtable ( id int(5) not null , name char(2) not null,  
id_2 int(2) not null);
	insert into testtable ( '', 'abced')

	you'll get a row like this: 0 || ab || 0
	mysql will truncate the string, put any nondigit into 0, and  
substitute an undeclared field with the closest legal value -- even  
if its specified as not null.
	
	i found out about this AFTER 3 weeks of testing w/a finalized db  
schema.  I realized all my text data was corrupt and my foreign key  
relations were useless, as it constantly resulted to defaults, never  
calling an error as it should.  i have 163 tables in this project,  
and a strong dependance on foreign keys.  So I lost 3 weeks of work,  
as all of my algorithms were way off.   i'm porting to postgres,  
waving goodbye to mysql, and

	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.

	transaction , foreign keys , or not -- i don't trust that software  
anymore.  why someone would have that as the DEFAULT setting is  
beyond me.  but its scared me enough away from the product.   if you  
try to put invalid data in a db , it shouldn't let you.  we all make  
mistakes - i just like to know where they are so i can learn from  
them and don't repeat them.  i think migrating to postgres will show  
me that on many levels.  it turns out 5 friends at different agencies  
migrated from mysql to postgres because of that in the past 3  
months.  they're all helping me with general postgres, but none use  
mod_perl -- they're all python php and rails.

in any event, if anyone here is using mysql

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.



	





On Mar 10, 2006, at 5:49 PM, Perrin Harkins wrote:

> On Fri, 2006-03-10 at 15:26 -0500, Jonathan Vanasco wrote:
>> I've found large need to migrate from mysql to postgres
>>
>> Are there any mod_perl specific things I should know about?
>
> Were you using transactions with MySQL?  If not, you'll need to learn
> about that, and probably use Apache::DBI to do automatic rollbacks at
> the end of every request.  You also may need to learn about isolation
> levels (who can see what changes when), but I think the default
> PostgreSQL one is what you usually want in a web app.


Mime
View raw message