Re: [RDBO] Unexpected column defaults in MySQL
On 1/17/07, Jonathan Vanasco <[EMAIL PROTECTED]> wrote: > On Jan 17, 2007, at 12:56 PM, John Siracusa wrote: >> But now that I look further, it appears that it's the "describe >> mytable" output that's incorrect (or at least misleading). When I >> insert a row without specifying a value for that VARCHAR(10) NOT NULL >> column, it ends up with an empty string as its value, which matches >> what DBD::mysql said. > > as perrin said-- are you using strict mode / traditional sql mode? I was ignoring strict mode for the purposes of this exploration. Even in "crazy old MySQL mode" I think the "describe table" output should more closely match what's returned by DBD::mysql in COLUMN_DEF. -John - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
On Wed, 2007-01-17 at 17:33 -0500, John Siracusa wrote: > I was ignoring strict mode for the purposes of this exploration. Even > in "crazy old MySQL mode" I think the "describe table" output should > more closely match what's returned by DBD::mysql in COLUMN_DEF. I agree, that sounds like a bug in DESCRIBE to me. Maybe they are justifying it by saying that this is an automatic system-wide default, rather than a column-specific one, but it's still misleading. - Perrin - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
>mysql behaves DRASTICALLY different depending on that flag. it might >actually do what you expect it to when turned on. >// Jonathan Vanasco "It might actually do what you expect"... hehe...well said! - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
On Jan 17, 2007, at 12:56 PM, John Siracusa wrote: > But now that I look further, it appears that it's the "describe > mytable" output that's incorrect (or at least misleading). When I > insert a row without specifying a value for that VARCHAR(10) NOT NULL > column, it ends up with an empty string as its value, which matches > what DBD::mysql said. as perrin said-- are you using strict mode / traditional sql mode? you can turn it on / off at will (unless its configured in the daemon startup file to only allow strict ) mysql behaves DRASTICALLY different depending on that flag. it might actually do what you expect it to when turned on. // Jonathan Vanasco | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | FindMeOn.com - The cure for Multiple Web Personality Disorder | Web Identity Management and 3D Social Networking | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - | RoadSound.com - Tools For Bands, Stuff For Fans | Collaborative Online Management And Syndication Tools | - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
On 1/17/07, Cory Bennett <[EMAIL PROTECTED]> wrote: >> This is arguably a bug in DBD::mysql, but as far as RDBO is concerned, > > For what it is worth, I dont think it is strictly DBD::mysql's fault, it is > Mysql itself. > > http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html > > It might be fixed with mysql 5.0.2, but I do not have that version around to > test with. > > Unfortunately I ran into this problem this weekend, and hunted around until I > found the doc above. When testing on my system (MySQL 5.0.22, DBD::mysql 3.0007) I saw that DBD::mysql returned a COLUMN_DEF of empty string, but "describe mytable" showed a default value of "NULL" for the VARCHAR(10) NOT NULL column. So it seemed to me that MySQL knew that it should be not be an empty string default value, but that DBD::mysql had decided to make COLUMN_DEF an empty string anyway. But now that I look further, it appears that it's the "describe mytable" output that's incorrect (or at least misleading). When I insert a row without specifying a value for that VARCHAR(10) NOT NULL column, it ends up with an empty string as its value, which matches what DBD::mysql said. -John - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
On Wed, 2007-01-17 at 10:01 -0500, John Siracusa wrote: > I'm forwarding this private email thread to the list with the > permission of the (now anonymized) sender because it's about an > unexpected situation that many RDBO users may face. Yes, it revolves > around yet another fun feature of MySQL/DBD::mysql. How did you > guess? :) Hmm. It sounds like the problem was that he marked some columns NOT NULL but wanted them to accept NULL. When MySQL is not in strict mode, that causes the columns to use generic defaults when passed NULL, hence the column_info data from DBD::mysql saying that this column defaults to empty string. The best thing to do is to run MySQL 5 or later with strict mode enabled, so that trying to put NULL into a NOT NULL column will be a fatal error (like it is in most databases). That makes it obvious where the problem is. - Perrin - Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys - and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV ___ Rose-db-object mailing list Rose-db-object@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/rose-db-object
Re: [RDBO] Unexpected column defaults in MySQL
> This is arguably a bug in DBD::mysql, but as far as RDBO is concerned,
For what it is worth, I dont think it is strictly DBD::mysql's fault, it is
Mysql itself.
http://dev.mysql.com/doc/refman/5.0/en/data-type-defaults.html
It might be fixed with mysql 5.0.2, but I do not have that version around to
test with.
Unfortunately I ran into this problem this weekend, and hunted around until I
found the doc above.
-Cory
On Wednesday 17 January 2007 7:01 am, John Siracusa wrote:
> I'm forwarding this private email thread to the list with the
> permission of the (now anonymized) sender because it's about an
> unexpected situation that many RDBO users may face. Yes, it revolves
> around yet another fun feature of MySQL/DBD::mysql. How did you
> guess? :)
>
> Anyway, here it is, for posterity, the list archives, and ultimately,
> google.
>
> -John
>
> ---cut---
>
> Subject: Rose::DB::Object::load question [ Version 0.758 ]
> Date: Mon, 15 Jan 2007 23:24:32 +0100
> From: X <[EMAIL PROTECTED]>
>
> Dear John,
>
> I was not very happy with "DBIx::Class" and "Class::DBI", so because I
> was already enjoying your module "Rose::HTML::Objects", I've decided to
> try "Rose::DB::Object". (By the way, why HTML::Object*_s_* (with s) and
> DB::Object (without s) ? )
>
> My first impression is very good, congratulation! But I came on the
> following issue:
>
> I have a table with 2 _unique_ keys having each 2 fields [[/x/, /y/],
> [/x/, /z/]] plus a primary key (/id/). Everything is fine If I'm loading
> an object with (/x,//y)/, but if I'm loading with (/x,z/), then the
> "load" method seems to still try to use /x/ and /y/ ( y = '' ) and thus
> does not find the object.
>
> I had a look at the code and the problem seems to be that the accessor
> (used at line 173) never returns "undef"! I've made a small test and if
> a field is not defined, the accessor returned the empty string, but not
> "undef". So the first unique key always matches.
>
> 164:# Prefer unique keys where we have defined values for all
> 165:# key calumns, but fall back to teh first unique key found
> 166:# where we have at least one defined value.
> 167:foreach my $cols ($meta->unique_keys_column_names)
> 168:{
> 169: my $defined = 0;
> 170: @key_columns = @$cols;
> 171: @key_methods = map { $meta->column_accessor_method_name($_) }
> @key_columns;
> 172: @key_values = map { $defined++ if(defined $_); $_ }
> 173: map { $self->$_() } @key_methods;
> 174:
> 175: if($defined == @key_columns)
> 176: {
> 177:$found_key = 1;
> 178:last;
> 189: }
>
> (By the way, note the 2 spelling errors at line 165).
>
> Even if the field is explicitly set to undef : "$o->y(undef)", $o->y()"
> still returns the empty string.
> /
> (my code:)/
>
> $pferd->Name($o{Pferdename});
> $pferd->PassNummer(undef);
> print "Value is ", (defined $pferd->PassNummer ? "Defined" : "UNDEFINED"),
> "\n"; print "and is '" . $pferd->PassNummer . "'\n";
>
> gives:
>
> Value is Defined
> and is ''
>
>
> What should I do to make the accessor returning "undef" if the value of
> the field is not defined, and so make the "load" method work correctly?
> Is there a bug or did I do something wrong?
>
>
> Thank you in advance for your support.
>
> Regards,
>
> -- X
>
> ---cut---
>
> Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
> To: " X" <[EMAIL PROTECTED]>
> From: "John Siracusa" <[EMAIL PROTECTED]>
> Date: Tue, 16 Jan 2007 10:08:31 -0500
>
> On 1/15/07, X <[EMAIL PROTECTED]> wrote:
> > $pferd->PassNummer(undef);
> > print "Value is ", (defined $pferd->PassNummer ? "Defined" :
> > "UNDEFINED"), "\n";
> > print "and is '" . $pferd->PassNummer . "'\n";
> > gives:
> > Value is Defined and is ''
>
> What kind of column is PassNummer? Can you show me the database
> definition for that column as well as the definition in your
> RDBO-derived class?
>
> (I suspect that you're using MySQL, which tends to make the default
> value for non-null (var)char columns an empty string. This default
> value would be picked up by the Loader/auto_initalize() and would
> cause the column to exhibit the behavior you describe.)
>
> -John
>
> ---cut---
>
> Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
> Date: Tue, 16 Jan 2007 17:28:22 +0100
> To: [EMAIL PROTECTED]
> From: X <[EMAIL PROTECTED]>
>
> Hi John,
>
> Thank you very much for your answer.
>
>
> So here is the definition of my table:
>
> CREATE TABLE `live_pferd` (
> `PferdId` int(11) NOT NULL auto_increment,
> `VnummerSVPS` varchar(10) NOT NULL,
> `PassNummer` decimal(10,0) NOT NULL,
> `Name` varchar(26) NOT NULL,
> ...
> PRIMARY KEY (`PferdId`),
> UNIQUE KEY `idx0` (`VnummerSVPS`,`PassNummer`),
> UNIQUE KEY `idx1` (`VnummerSVPS`,`Name`)
> )
>
> ...and I'm using auto_initialize:
>
> package Hippique::DB::Live::Pferd;
>
> use strict;
> use base qw(Hippique::DB::Live::Object);
>
> __PACKAGE__->meta->setup(
> table => 'live_pferd',
[RDBO] Unexpected column defaults in MySQL
I'm forwarding this private email thread to the list with the
permission of the (now anonymized) sender because it's about an
unexpected situation that many RDBO users may face. Yes, it revolves
around yet another fun feature of MySQL/DBD::mysql. How did you
guess? :)
Anyway, here it is, for posterity, the list archives, and ultimately, google.
-John
---cut---
Subject: Rose::DB::Object::load question [ Version 0.758 ]
Date: Mon, 15 Jan 2007 23:24:32 +0100
From: X <[EMAIL PROTECTED]>
Dear John,
I was not very happy with "DBIx::Class" and "Class::DBI", so because I
was already enjoying your module "Rose::HTML::Objects", I've decided to
try "Rose::DB::Object". (By the way, why HTML::Object*_s_* (with s) and
DB::Object (without s) ? )
My first impression is very good, congratulation! But I came on the
following issue:
I have a table with 2 _unique_ keys having each 2 fields [[/x/, /y/],
[/x/, /z/]] plus a primary key (/id/). Everything is fine If I'm loading
an object with (/x,//y)/, but if I'm loading with (/x,z/), then the
"load" method seems to still try to use /x/ and /y/ ( y = '' ) and thus
does not find the object.
I had a look at the code and the problem seems to be that the accessor
(used at line 173) never returns "undef"! I've made a small test and if
a field is not defined, the accessor returned the empty string, but not
"undef". So the first unique key always matches.
164:# Prefer unique keys where we have defined values for all
165:# key calumns, but fall back to teh first unique key found
166:# where we have at least one defined value.
167:foreach my $cols ($meta->unique_keys_column_names)
168:{
169: my $defined = 0;
170: @key_columns = @$cols;
171: @key_methods = map { $meta->column_accessor_method_name($_) }
@key_columns;
172: @key_values = map { $defined++ if(defined $_); $_ }
173: map { $self->$_() } @key_methods;
174:
175: if($defined == @key_columns)
176: {
177:$found_key = 1;
178:last;
189: }
(By the way, note the 2 spelling errors at line 165).
Even if the field is explicitly set to undef : "$o->y(undef)", $o->y()"
still returns the empty string.
/
(my code:)/
$pferd->Name($o{Pferdename});
$pferd->PassNummer(undef);
print "Value is ", (defined $pferd->PassNummer ? "Defined" : "UNDEFINED"), "\n";
print "and is '" . $pferd->PassNummer . "'\n";
gives:
Value is Defined
and is ''
What should I do to make the accessor returning "undef" if the value of
the field is not defined, and so make the "load" method work correctly?
Is there a bug or did I do something wrong?
Thank you in advance for your support.
Regards,
-- X
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
To: " X" <[EMAIL PROTECTED]>
From: "John Siracusa" <[EMAIL PROTECTED]>
Date: Tue, 16 Jan 2007 10:08:31 -0500
On 1/15/07, X <[EMAIL PROTECTED]> wrote:
> $pferd->PassNummer(undef);
> print "Value is ", (defined $pferd->PassNummer ? "Defined" : "UNDEFINED"),
> "\n";
> print "and is '" . $pferd->PassNummer . "'\n";
> gives:
> Value is Defined and is ''
What kind of column is PassNummer? Can you show me the database
definition for that column as well as the definition in your
RDBO-derived class?
(I suspect that you're using MySQL, which tends to make the default
value for non-null (var)char columns an empty string. This default
value would be picked up by the Loader/auto_initalize() and would
cause the column to exhibit the behavior you describe.)
-John
---cut---
Subject: Re: Rose::DB::Object::load question [ Version 0.758 ]
Date: Tue, 16 Jan 2007 17:28:22 +0100
To: [EMAIL PROTECTED]
From: X <[EMAIL PROTECTED]>
Hi John,
Thank you very much for your answer.
So here is the definition of my table:
CREATE TABLE `live_pferd` (
`PferdId` int(11) NOT NULL auto_increment,
`VnummerSVPS` varchar(10) NOT NULL,
`PassNummer` decimal(10,0) NOT NULL,
`Name` varchar(26) NOT NULL,
...
PRIMARY KEY (`PferdId`),
UNIQUE KEY `idx0` (`VnummerSVPS`,`PassNummer`),
UNIQUE KEY `idx1` (`VnummerSVPS`,`Name`)
)
...and I'm using auto_initialize:
package Hippique::DB::Live::Pferd;
use strict;
use base qw(Hippique::DB::Live::Object);
__PACKAGE__->meta->setup(
table => 'live_pferd',
auto_initialize => [],
);
... and yes, I'm using MySQL!
This is what I'm trying to do
my $pferd = Hippique::DB::Live::Pferd->new(
VnummerSVPS => "1234",
);
$pferd->Name("blabla");
$pferd->load; # Ooops!
and it fails to load the object "$pferd->PassNummer" is empty and not
"undef"; so it selects the wrong unique index (idx0 instead of idx1).
But actually MySQL has not yet be involved at this point; it's the
accessor of the "in-memory" object $pferd that returns "" instead of undef.
I could explain the phenomenon by the "NOT NULL" in the column
definition of the database, but actually the columns are "NOT NULL".
It's just that I'm trying to load the object either by (VnummerSVP,
PassNummer) or by (VnummerSVPS, Name
