| Subject: | Wrong COLUMN_DEF value for NOT NULL field |
Originally reported by Ask Bjørn Hansen at
http://bugs.mysql.com/bug.php?id=26544
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 textmysql> 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.