MySQL Bugs: #14786: Bogus Field 'x' doesn't have a default value warning

Bug #14786	Bogus Field 'x' doesn't have a default value warning
Submitted:	9 Nov 2005 13:42	Modified:	26 Aug 2006 13:51
Reporter:	Sergey Petrunya	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server	Severity:	S3 (Non-critical)
Version:	5.0-bk	OS:	Linux (Linux)
Assigned to:		CPU Architecture:	Any

Description:
create table t1 (
a varchar(112) charset utf8 collate utf8_bin not null,
primary key (a)
) select 'test' as a ;

Warnings:
Warning	1364	Field 'a' doesn't have a default value

show create table t1;
Table	Create Table
t1	CREATE TABLE `t1` (
  `a` varchar(112) character set utf8 collate utf8_bin NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

How to repeat:
see above.

Suggested fix:
I'm not sure, but in my understanding this warning shouldn't be generated.

See also BUG#14480

Verified on 5.0.16-BK (ChangeSet@1.1972, 2005-11-05 22:45:54-08:00, igor@rurik.mysql.com):

[openxs@Fedora 5.0]$ bin/mysql -uroot
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.16

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table t1;
Query OK, 0 rows affected (0,03 sec)

mysql> create table t1 (
    -> a varchar(112) charset utf8 collate utf8_bin not null,
    -> primary key (a)
    -> ) select 'test' as a ;
Query OK, 1 row affected, 1 warning (0,01 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show warnings;
+---------+------+----------------------------------------+
| Level   | Code | Message                                |
+---------+------+----------------------------------------+
| Warning | 1364 | Field 'a' doesn't have a default value |
+---------+------+----------------------------------------+
1 row in set (0,00 sec)

mysql> show create table t1;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                           |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
| t1    | CREATE TABLE `t1` (
  `a` varchar(112) character set utf8 collate utf8_bin NOT NULL,
  PRIMARY KEY  (`a`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)

verified on latest 5.0 tree, can't repeat

Can't repeat with 5.0.25-BK also:

mysql> create table t1 (
    -> a varchar(112) charset utf8 collate utf8_bin not null,
    -> primary key (a)
    -> ) select 'test' as a;
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.25-debug |
+--------------+
1 row in set (0.01 sec)

So, this bug somehow was fixed.

set the default value of the field 'a' in the table 't1' into 0 or any other number. It works with me.

i also found same error then i understand that just before i have created insert trigger on same table which copy same record into another same image table . once i have removed trigger my application runs as usual . mysql team should look into the trigger creation for the error arises of it.

Mukesh Hirve