Bug #14306 error inserting without default value for text field
Submitted: 25 Oct 2005 21:09 Modified: 26 Oct 2005 18:18
Reporter: Stéphane De Blois Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S3 (Non-critical)
Version:5.0.13 OS:Microsoft Windows (windows)
Assigned to: Miguel Solorzano

[25 Oct 2005 21:09] Stéphane De Blois
Description:
I got an error when i try to insert a record in a table without  providing a default value for a text field even thougt this text field is empty

error : Field 'description' doesn't have a default value

didn't have this problem with older version of MySql

How to repeat:
insert into $table (product, amount) value ('test', 100);

if you got a column description as text and you don't supply a default value you will have this error: Field 'description' doesn't have a default value

you have to do :
insert into $table (product, amount,description) value ('test', 100,' ');
[26 Oct 2005 2:33] Miguel Solorzano
Could you please provide the output of show create table table_name.

Thanks in advance.
[26 Oct 2005 13:10] Stéphane De Blois
Field                     | Type         | Null | Key | Default | Extra     
description            | varchar(255) | NO   |     |         |                |
note                     | text         | NO   |     |         |                |

same error with varchar. I have to explicitly put the fields with values '' in my insert query, otherwise i got the error. 

In the 5.0.4 version, i didn't have to put a space or something like that to the default value in my varchar field or text field.
[26 Oct 2005 15:39] Stéphane De Blois
CREATE TABLE `ttt` (
  `id` int(11) NOT NULL auto_increment,
  `productcode` varchar(55) NOT NULL,
  `amount` int(11) NOT NULL default '0',
  `desc` varchar(255) NOT NULL,
  PRIMARY KEY  (`id`)
)  ENGINE=MyISAM DEFAULT CHARSET=latin1

Mysql don't create default '' to varchar or text field

i have to explicitly put a space to have default value for these fields
[26 Oct 2005 15:49] Miguel Solorzano
Could you please take a look in your sql_mode sets:

c:\mysql\bin>mysql -uroot -p test
Enter password: ****
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.13-rc-nt

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

mysql> CREATE TABLE `ttt` (
    ->   `id` int(11) NOT NULL auto_increment,
    ->   `productcode` varchar(55) NOT NULL,
    ->   `amount` int(11) NOT NULL default '0',
    ->   `desc` varchar(255) NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> )  ENGINE=MyISAM DEFAULT CHARSET=latin1
    -> ;
Query OK, 0 rows affected (0.16 sec)

mysql> insert into ttt set amount = 100;
Query OK, 1 row affected, 2 warnings (0.02 sec)

mysql> show warnings;
+---------+------+--------------------------------------------------+
| Level   | Code | Message                                          |
+---------+------+--------------------------------------------------+
| Warning | 1364 | Field 'productcode' doesn't have a default value |
| Warning | 1364 | Field 'desc' doesn't have a default value        |
+---------+------+--------------------------------------------------+
2 rows in set (0.00 sec)

mysql> select * from ttt;
+----+-------------+--------+------+
| id | productcode | amount | desc |
+----+-------------+--------+------+
|  1 |             |    100 |      |
+----+-------------+--------+------+
1 row in set (0.02 sec)

mysql> set sql_mode = 'traditional';
Query OK, 0 rows affected (0.02 sec)

mysql> insert into ttt set amount = 101;
ERROR 1364 (HY000): Field 'productcode' doesn't have a default value
mysql>
[26 Oct 2005 17:13] Stéphane De Blois
here's the sql_mode :

mysql> select @@sql_mode;
+------------------------
| @@sql_mode
+------------------------
| STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
+------------------------
1 row in set (0.00 sec)
[26 Oct 2005 17:29] Stéphane De Blois
i set sql_mode to nothing

set sql_mode = '';

doesn't work, always same error
[26 Oct 2005 17:41] Stéphane De Blois
ok Miguel i found the error, i have changed in my.ini the line : 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

for

sql-mode=""

and it works fine. Thanks Miguel.
[26 Oct 2005 18:18] Miguel Solorzano
Thank you for the feedback and bug report.
[23 Feb 2008 12:34] pop show
opent my.ini in folder D:\Program Files\MySQL\MySQL Server 5.0 , or whatever folder
i changed 
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to
sql-mode=""

restart mysql service
and work fine for me 
thanks for the community
[20 Jul 2011 8:58] Stephan Truemper
I also changed the sql-mode line in my.ini to sql-mode ="" and it worked.

We are using mySQL 5.1 on a Windows Server