Fun forward from mod_perl list: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of? - Mailing list pgsql-advocacy

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.