sql-info.de
April 20, 2014

MySQL Gotchas | BLOB/TEXT column ... can't have a default value

So, I was just loading a MySQL schema from a test application I use to experiment with various databases, and suddenly it started spitting out errors like this:

  BLOB/TEXT column 'some_column' can't have a default value

which was kind of odd because it had evidently been working fine before, and what kind of database doesn't allow default values on certain datatypes anyway?

MySQL of course:

BLOB and TEXT columns cannot be assigned a default value

    (11.5 Data Type Default Values).

Unless of course (how could I not have known?) STRICT_TRANS_TABLES is not enabled (which seems to be the historical default), in which case a default value clause for an empty string on BLOB/TEXT columns will be implicitly accepted:

MariaDB [tgg_devel]> SHOW VARIABLES LIKE 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

MariaDB [tgg_devel]> CREATE TABLE deftest (defcol TEXT NOT NULL DEFAULT '');
Query OK, 0 rows affected, 1 warning (0.09 sec)

It will however raise an error if the default value is a non-empty string:

MariaDB [tgg_devel]> CREATE TABLE deftest2 (defcol TEXT NOT NULL DEFAULT 'foo');
ERROR 1101 (42000): BLOB/TEXT column 'defcol' can't have a default value

Enable STRICT_TRANS_TABLES for consistent behaviour.

MariaDB [tgg_devel]> SHOW VARIABLES LIKE 'sql_mode';
+---------------+--------------------------------------------+
| Variable_name | Value                                      |
+---------------+--------------------------------------------+
| sql_mode      | STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+---------------+--------------------------------------------+
1 row in set (0.01 sec)

MariaDB [tgg_devel]> CREATE TABLE deftest (defcol TEXT NOT NULL DEFAULT '');
ERROR 1101 (42000): BLOB/TEXT column 'defcol' can't have a default value

Update: there has been an open bug report for this issue since 2006.

Posted at 5:26 AM