| 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: | |
| Category: | MySQL Server | Severity: | S3 (Non-critical) |
| Version: | 5.0.13 | OS: | Windows (windows) |
| Assigned to: | MySQL Verification Team | CPU Architecture: | Any |
[26 Oct 2005 2:33]
MySQL Verification Team
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]
MySQL Verification Team
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]
MySQL Verification Team
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
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,' ');