| Bug #26544 | Wrong COLUMN_DEF value for NOT NULL field | ||
|---|---|---|---|
| Submitted: | 21 Feb 2007 20:45 | Modified: | 21 Feb 2013 21:55 |
| Reporter: | Ask Hansen (Basic Quality Contributor) | Email Updates: | |
| Status: | Unsupported | Impact on me: | |
| Category: | Connectors: DBD::mysql ( Perl ) | Severity: | S2 (Serious) |
| Version: | 4.001 | OS: | Any (any) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | qc | ||
[22 Feb 2007 2:32]
MySQL Verification Team
Thank you for the bug report.
[27 Feb 2007 21:45]
Jim Winstead
The problem here is that we use 'DESCRIBE table' to figure out the column info, and it returns "" for the default value in this case. If column_info() were rewritten to use INFORMATION_SCHEMA, we could do the right thing, since that does return the correct value. Maybe it could be argued that the results from DESCRIBE are the real bug.
[2 Mar 2007 23:49]
Ask Hansen
Hi Jim, Using information_schema would be the Wave of the Future. Fixing describe to do the right thing would be good (too). I ran into something else where that was a problem, but I don't think I filed a bug report then. Should I make one? - ask
[5 Mar 2007 21:26]
Jim Winstead
yes, please go ahead and file the inconsistency between DESCRIBE and INFORMATION_SCHEMA as a bug. i'll also look at adding INFORMATION_SCHEMA based implementations of the catalog functions to DBD::mysql.
[5 Mar 2007 21:44]
Ask Hansen
done in http://bugs.mysql.com/26849
[28 May 2007 15:08]
Patrick Galbraith
The question I'm wondering is if in 5.0, describe is using info schema?
[21 Feb 2013 21:55]
Sveta Smirnova
Thank you for the report. We don't work on DBD::mysql bugs anymore. All its bugs should go to CPAN: https://rt.cpan.org/Public/Dist/Display.html?Name=DBD-mysql I re-submitted your report to https://rt.cpan.org/Public/Bug/Display.html?id=83517 Please subscribe to the new report on CPAN and work with DBD::mysql developers in case if they need additional details.
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 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) 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");