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

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