Skip Menu |

This queue is for tickets about the DBD-mysql CPAN distribution.

Report information
The Basics
Id: 83517
Status: rejected
Priority: 0/
Queue: DBD-mysql

Owner: Nobody in particular
Requestors: sveta.smirnova [...]

Bug Information
Severity: Normal
Broken in: 4.001
Fixed in: (no value)

Subject: Wrong COLUMN_DEF value for NOT NULL field
Download (untitled) / with headers
text/plain 2.2k
Originally reported by Ask Bjørn Hansen at Description: mysqld 5.0.27, DBD::mysql 4.001. For a "NOT NULL" varchar, DBD::mysql appears to give the column a default value anyway. create table bars (id int unsigned not null primary key, foo varchar(255) not null) engine = InnoDB; show create table bars\G *************************** 1. row *************************** Table: bars Create Table: CREATE TABLE `bars` ( `id` int(10) unsigned NOT NULL, `foo` varchar(255) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Show quoted text
mysql> select * from information_schema.columns where table_schema =
'ntppool' and table_name = 'bars' and column_name = 'foo' \G *************************** 1. row *************************** TABLE_CATALOG: NULL TABLE_SCHEMA: ntppool TABLE_NAME: bars COLUMN_NAME: foo ORDINAL_POSITION: 2 COLUMN_DEFAULT: NULL IS_NULLABLE: NO DATA_TYPE: varchar CHARACTER_MAXIMUM_LENGTH: 255 CHARACTER_OCTET_LENGTH: 255 NUMERIC_PRECISION: NULL NUMERIC_SCALE: NULL CHARACTER_SET_NAME: latin1 COLLATION_NAME: latin1_swedish_ci COLUMN_TYPE: varchar(255) COLUMN_KEY: EXTRA: PRIVILEGES: select,insert,update,references COLUMN_COMMENT: 1 row in set (0.03 sec) Show quoted text
mysql> select default(foo) from bars;
ERROR 1364 (HY000): Field 'foo' doesn't have a default value But asking for $dbh->column_info tells me that DBD::mysql thinks the default value "COLUMN_DEF" is "" (an empty string). /pkg/bin/perl -Ilib -Icombust/lib -MCombust::DB -e '$d=Combust::DB::db_open("ntppool"); $s=$d->column_info(undef,"ntppool","bars",q[%]); $c=$s->fetchall_arrayref({}); print Data::Dumper->Dump([\$c], [qw(c)])' How to repeat: use DBI; use Data::Dumper; $dbh = DBI->connect('dbi:mysql:test', 'root'); $dbh->do('CREATE TABLE `bars` ( `foo` varchar(255) NOT NULL ) ENGINE=InnoDB DEFAULT CHARSET=latin1'); $sth = $dbh->column_info(undef,"test","bars",q[%]); $dbh->do('drop table bars'); $col = $sth->fetchall_arrayref({}); # print Data::Dumper->Dump([\$col], [qw(c)]); print ((defined $col->[0]->{COLUMN_DEF} ? "not " : "") . "ok\n"); For more details see original bug report.
Download (untitled) / with headers
text/plain 160b
This is a bug in MySQL, not in DBD::mysql, and seems to be fixed in versions 5.0.50 or later; see bug report -- Michiel

This service is sponsored and maintained by Best Practical Solutions and runs on infrastructure.

Please report any issues with to