MySQL Bugs: #47901: Missing default value warning issued on TEXT fields on INSERT

Bug #47901	Missing default value warning issued on TEXT fields on INSERT
Submitted:	7 Oct 2009 18:03	Modified:	8 Oct 2009 13:25
Reporter:	Marcus Bointon	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Errors	Severity:	S3 (Non-critical)
Version:	5.0.86, 5.1.40	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
TEXT and BLOB types don't support default values, but inserts issue a warning on insert about the lack of a default value.
The presence of the error prevents affected_rows from returning a valid result.

Seen on OS X and linux, but may appear on other platforms too.

How to repeat:
mysql> create table t (`a` TEXT NOT NULL, `b` TEXT NOT NULL);
Query OK, 0 rows affected (0.03 sec)

mysql> insert into u set a = '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 'b' doesn't have a default value | 
+---------+------+----------------------------------------+
1 row in set (0.00 sec)

Suggested fix:
Don't enforce defaults where none are possible?

In my understanding it depends on SQL_mode.

Consider a table with columns a,b and c (where c does no have a default and is NOT NULL) and you INSERT .. (a,b) VALUES ...

in *non-strict* mode you will get a warning - in *strict* mode you will get an error. This is not specific for TEXT/BLOBs. Same would happen with any datatype. If
1) there is no default
2) NOT NULL prevents insertion of NULL
3) strict mode prevents 'silent insertion' or ZERO/empty string.
.. there is no solution for the server. Nothing is allowed. An *error* will be returned. However in *non-strict* mode 'silent insertion' or ZERO/empty string will happen (with a warning).

However for TEXT/BLOB some inconsistency is there with '' (empty string): 
http://bugs.mysql.com/bug.php?id=47705

.. but I agree that the *text* of the error message is not very good for (in particular) TEXT/BLOBs. Better something like:

in strict mode *error* could be:
"no value was specified for a NOT NULL TEXT/BLOB column. Cannot INSERT to column"

in non-strict mode *warning* could be:
"no value was specified for a NOT NULL TEXT/BLOB column. An EMPTY STRING was INSERTED"

.. but this is probably the 27,117th time we have discussed the quality of MySQL error/warning messages here! They sometimes look like they were generated by a random generator and dropped to Earth from a space shuttle ... :-)

Your bug report is the other end of the same illogical problem:

* It's an error to try to set a default
* If you don't have a default, you get an error

Either way, we're screwed...

For now I've worked around it by explicitly setting the fields to empty strings, but it's still a workaround. Can we not have defaults for TEXT fields?

I agree 100% with this comment from Peter:

[7 Oct 21:48] Peter Laursen

This is what we have:

77-52-242-160:5.1 openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.40-debug Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>  create table t (`a` TEXT NOT NULL, `b` TEXT NOT NULL default '');
Query OK, 0 rows affected, 1 warning (0.05 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1101
Message: BLOB/TEXT column 'b' can't have a default value
1 row in set (0.00 sec)

mysql> show create table t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE `t` (
  `a` text NOT NULL,
  `b` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.01 sec)

mysql> insert into t set a = 'a';
Query OK, 1 row affected (0.00 sec)

No error and no warning, so we remembered default value for column b somewhere?

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| a |   |
+---+---+
1 row in set (0.00 sec)

mysql> select @@sql_mode;
+------------+
| @@sql_mode |
+------------+
|            |
+------------+
1 row in set (0.00 sec)

mysql> select * from t where b = '';
+---+---+
| a | b |
+---+---+
| a |   |
+---+---+
1 row in set (0.00 sec)

Now, for column a we have a warning:

mysql> insert into t set b = 'b';
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings\G
*************************** 1. row ***************************
  Level: Warning
   Code: 1364
Message: Field 'a' doesn't have a default value
1 row in set (0.00 sec)

mysql> select * from t;
+---+---+
| a | b |
+---+---+
| a |   |
|   | b |
+---+---+
2 rows in set (0.00 sec)

mysql> select * from t where b = '';
+---+---+
| a | b |
+---+---+
| a |   |
+---+---+
1 row in set (0.00 sec)

mysql> select * from t where a = '';
+---+---+
| a | b |
+---+---+
|   | b |
+---+---+
1 row in set (0.00 sec)

Yes, in strict mode it is all different:

mysql> set session sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t;
Query OK, 0 rows affected (0.00 sec)

mysql>  create table t (`a` TEXT NOT NULL, `b` TEXT NOT NULL default '');
ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value
mysql>  create table t (`a` TEXT NOT NULL, `b` TEXT NOT NULL);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t set a = 'a';
ERROR 1364 (HY000): Field 'b' doesn't have a default value

We do need more correct/precise warning/error message texts for both strict and non-strict SQL modes. 

Or, maybe even better, just let to set default values for TEXT/BLOB columns some day...