MySQL Bugs: #13794: TEXT cannot use empty string as default value

Bug #13794	TEXT cannot use empty string as default value
Submitted:	6 Oct 2005 7:28	Modified:	8 Oct 2005 9:40
Reporter:	Qi Liu	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S4 (Feature request)
Version:	5.0.13-rc-nt, 5.0.14-rc	OS:	Windows (Windows, Linux)
Assigned to:		CPU Architecture:	Any

Description:
First create a table which has a NON-NULL field with type TEXT (TINYTEXT/MEDIUMTEXT/LONGTEXT) and default value empty. When adding a row into this table without specifying the value of this field, an error occurs saying '#1364 - Field 'field' doesn't have a default value'.
However, when doing these under Linux, MySQL 5.0.13, the row can be inserted with an empty string as the value of the field.

The behavior under different platforms should be consistent.

How to repeat:
CREATE TABLE `test2` (
  `test` mediumtext NOT NULL DEFAULT ''
);

INSERT INTO `test2` () VALUES ();

Under Windows:
Error: #1364 - Field 'test' doesn't have a default value

Under Linux:
A row has been inserted with empty string as the value.

Suggested fix:
N/A

Thank you for a problem report. Sorry, but I can't repeat the behaviour you described on Windows:

mysql> create table test2 (c1 text not null default '');
Query OK, 0 rows affected (0.42 sec)

mysql> insert into test2 values('abc');
Query OK, 1 row affected (0.06 sec)

mysql> insert into test2 () values();
Query OK, 1 row affected (0.03 sec)

mysql> select * from test2;
+-----+
| c1  |
+-----+
| abc |
|     |
+-----+
2 rows in set (0.03 sec)

mysql> drop table test2;
Query OK, 0 rows affected (0.11 sec)

mysql> create table test2 (c1 mediumtext not null default '');
Query OK, 0 rows affected (0.07 sec)

mysql> insert into test2 () values();
Query OK, 1 row affected (0.03 sec)

mysql> select * from test2;
+----+
| c1 |
+----+
|    |
+----+
1 row in set (0.00 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.06 sec)

Tried with both InnoDB and MyISAM table types - no luck. '' is inserted as expected. What shell I do to repeat the results you had written about?

Use the following statement to repeat the bug:

CREATE TABLE test1 (test TEXT NOT NULL);
INSERT INTO test1 () VALUES ();

This behaves differently under Linux and Windows.

Yes, I was able to get inconsistent behaviour of 5.0.13-rc-nt versus 5.0.14-rc on Linux. 

On Windows:

mysql> CREATE TABLE test1 (test TEXT NOT NULL) engine=MyISAM;
Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO test1 () VALUES ();
ERROR 1364 (HY000): Field 'test' doesn't have a default value
mysql> drop table test1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE test1 (test TEXT NOT NULL) engine=InnoDB;
Query OK, 0 rows affected (0.12 sec)

mysql> INSERT INTO test1 () VALUES ();
ERROR 1364 (HY000): Field 'test' doesn't have a default value
mysql> select * from test1;
Empty set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.0.13-rc-nt |
+--------------+
1 row in set (0.01 sec)

On Linux:

mysql> CREATE TABLE test1 (test TEXT NOT NULL) engine=MyISAM;
Query OK, 0 rows affected (0.02 sec)

mysql> INSERT INTO test1 () VALUES ();
Query OK, 1 row affected, 1 warning (0.02 sec)

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

mysql> drop table test1;
Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE test1 (test TEXT NOT NULL) engine=InnoDB;
Query OK, 0 rows affected (0.08 sec)

mysql> INSERT INTO test1 () VALUES ();
Query OK, 1 row affected, 1 warning (0.05 sec)

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

mysql> select version();
+----------------------------+
| version()                  |
+----------------------------+
| 5.0.14-rc-nightly-20050919 |
+----------------------------+
1 row in set (0.00 sec)

mysql> exit
Bye
-bash-2.05b$ uname -a
Linux nocona.mysql.com 2.4.21-32.0.1.ELsmp #1 SMP Tue May 17 17:46:36 EDT 2005 x86_64 x86_64 x86_64 GNU/Linux

So, on Linux we get warnings for both table types and rows inserted with '' column value. On Windows we get errors. Linux behaviour is more appropriate, according to the manual (http://dev.mysql.com/doc/mysql/en/design-limitations.html), to my mind.

I've found it not a bug.
It is caused by the configuration file automatically generated by the configuration tool under Windows.
In the my.ini, sql-mode is set to STRICT_TRANS_TABLES by default, which causes this error.