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

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.

Greg Sabino Mullane wrote:
> I was using InnoDB w/transactions in mysql, because i needed
> transactions and I've used mysql a lot before.
>
Greg,

My company is working on a translation from mysql to postgresql for
LAMS.  Perhaps we could collaborate and write a short white paper about
the process.

On 4/24/06, Greg Sabino Mullane <greg@turnstep.com> wrote:

For reference, the mail quoted is this one here:
http://mail-archives.apache.org/mod_mbox/perl-modperl/200603.mbox/%3CFCE2F695-9547-441C-87B5-24B748E8A32F@2xlp.com%3E

(...)
> 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.

To be fair to MySQL, version 5.0 does actually produce warnings when it does
this, rather than performing the "conversion" silently as it did in
previous versions.

To make the most of this feature, at least in the CLI, the "warnings"
option should be
enabled:

mysql> warnings;
Show warnings enabled.
mysql> insert into testtable value ( '', 'abced','');
Query OK, 1 row affected, 3 warnings (0.01 sec)

Warning (Code 1264): Out of range value adjusted for column 'id' at row 1
Warning (Code 1265): Data truncated for column 'name' at row 1
Warning (Code 1264): Out of range value adjusted for column 'id_2' at row 1

>         it turns out that you can set 'sql_mode=TRADITIONAL' in the my.cnf ,
> and it will enforce rules by default.

I very much enjoyed the documentation's description of this setting:

"Make MySQL behave like a "traditional" SQL database system. A simple
description of
this mode is "give an error instead of a warning" when inserting an
incorrect value into a column."
  http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

(Presumably the default mode is "AVANT_GARDE"? ;-)

Interestingly, the modes "ANSI" and "TRADITIONAL" are mutually exclusive,
although it looks like they're "wrappers" for the specific settings
listed further
down on the page, and a dedicated user could probably set them individually...


Ian Barwick

Ian Barwick wrote:
>
>>         it turns out that you can set 'sql_mode=TRADITIONAL' in the my.cnf ,
>> and it will enforce rules by default.
>
> I very much enjoyed the documentation's description of this setting:
>
> "Make MySQL behave like a "traditional" SQL database system. A simple
> description of
> this mode is "give an error instead of a warning" when inserting an
> incorrect value into a column."
>   http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html
>
> (Presumably the default mode is "AVANT_GARDE"? ;-)

sql_mode = EXCITING

--
   Richard Huxton
   Archonet Ltd

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> My company is working on a translation from mysql to postgresql for
> LAMS.  Perhaps we could collaborate and write a short white paper about
> the process.

I'm willing to collaborate, but I did not write that email, I was just
forwarding it on. :) Googling the text of it should show you the original
content. There's also a couple of ongoing projects to convert from
one to the other that I recommend looking into / joining / contributing
to if you have not already:

http://gborg.postgresql.org/project/mysql2psql/projdisplay.php
http://pgfoundry.org/projects/mysqlcompat

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200604251127
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFETj/TvJuQZxSWSsgRArFAAKCxP6clFpsEMH5vVl9EBt+pIMwIqQCggJdA
bJxFQT52FV/aWrTNNsoomKM=
=u7kM
-----END PGP SIGNATURE-----