You are viewing a plain text version of this content. The canonical link for it is here.
Posted to modperl@perl.apache.org by Jonathan Vanasco <mo...@2xlp.com> on 2006/03/10 21:26:41 UTC
Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
I've found large need to migrate from mysql to postgres
Are there any mod_perl specific things I should know about?
I'm not referring to SQL traits or DBI differences, but any little
tricks or odd things that might happen when using the pg dbi under
apache
thanks,
Jonathan
Re: Migrating from MySQL to PostGres -- Any mod_perl specific
things I should be wary of?
Posted by Frank Wiles <fr...@wiles.org>.
On Fri, 10 Mar 2006 15:26:41 -0500
Jonathan Vanasco <mo...@2xlp.com> wrote:
> I've found large need to migrate from mysql to postgres
>
> Are there any mod_perl specific things I should know about?
>
> I'm not referring to SQL traits or DBI differences, but any little
> tricks or odd things that might happen when using the pg dbi under
> apache
Nothing comes to mind. I've used PostgreSQL for all of my mod_perl
apps since I first started using mod_perl years ago without any
problems.
You'll definitely want to read up tuning PostgreSQL as it's default
configuration isn't optimal on most current hardware.
---------------------------------
Frank Wiles <fr...@wiles.org>
http://www.wiles.org
---------------------------------
Re: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
Posted by Jonathan Vanasco <mo...@2xlp.com>.
crazy. i used to use postgres extensively @work - my CTO was real
big on it.
we moved over to mysql when he left, because it was easier for me to
staff projects based on it, and we didn't need triggers or stored
procedures. I had no idea about the invalid data - i just checked
some old apps, and I cringed.
sorry I was rash on the list. i lost about 3weeks of work and ~10gb
of corrupt data because my foreign keys didn't work. a few days into
postgres and I'm in heaven though. super strict data validation,
error codes that are verbose and semantic, and the OS management
tools that are out there are just amazing.
Re: Migrating from MySQL to PostGres -- Any mod_perl specific things
I should be wary of?
Posted by Markus Wichitill <ma...@gmx.de>.
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
Not true, MySQL has always treated data that way, that's one of the reasons
many DBAs didn't take it seriously. Only in 5.0 did they finally add those
strict SQL modes. They may not be the defaut yet, but that's because they
would break many existing apps. Although I think the last time I used the
Windows installer, the Strict mode checkbox was actually enabled by default.
MySQL may not be the most standards conforming database, but it's improving,
not the opposite, like you make it sound.
Re: Migrating from MySQL to PostGres -- Any mod_perl specific things
I should be wary of?
Posted by Perrin Harkins <pe...@elem.com>.
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
Re: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
Posted by Jonathan Vanasco <mo...@2xlp.com>.
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.
Re: Migrating from MySQL to PostGres -- Any mod_perl specific
things I should be wary of?
Posted by Perrin Harkins <pe...@elem.com>.
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.
- Perrin
Re: Migrating from MySQL to PostGres -- Any mod_perl specific
things I should be wary of?
Posted by Perrin Harkins <pe...@elem.com>.
On Fri, 2006-03-10 at 13:13 -0800, David Wheeler wrote:
> The only one I can think of (and my information might be severely out-
> of-date) is that you can open a MySQL connection before Apache forks
> and have it still work in the children
I'm not sure where you got this idea, but don't do it. I've seen lots
of people have bugs caused by this on the Catalyst and Class::DBI lists.
If you open database connections during startup, close them before
forking.
- Perrin
Re: Migrating from MySQL to PostGres -- Any mod_perl specific things I should be wary of?
Posted by David Wheeler <da...@kineticode.com>.
On Mar 10, 2006, at 12:26, Jonathan Vanasco wrote:
> I'm not referring to SQL traits or DBI differences, but any little
> tricks or odd things that might happen when using the pg dbi under
> apache
The only one I can think of (and my information might be severely out-
of-date) is that you can open a MySQL connection before Apache forks
and have it still work in the children, but the same is not true for
PostgreSQL (or most other databases, for that matter). If you use
Apache::DBI, there should be no problem. Otherwise, just be sure that
you have no database connections open when apache forks. Close any
that you open on startup, and only open them when you get the first
request in each child. This is good practice for MySQL, too, FWIW.
Otherwise, everything just works.
Best,
David