Why can't a text column have a default value in MySQL?

Windows MySQL v5 throws an error but Linux and other versions only raise a warning. This needs to be fixed. WTF?

Also see an attempt to fix this as bug #19498 in the MySQL Bugtracker:

Bryce Nesbitt on April 4 2008 4:36pm:
On MS Windows the "no DEFAULT" rule is an error, while on other platforms it is often a warning. While not a bug, it's possible to get trapped by this if you write code on a lenient platform, and later run it on a strict platform:

Personally, I do view this as a bug. Searching for "BLOB/TEXT column can't have a default value" returns about 2,940 results on Google. Most of them are reports of incompatibilities when trying to install DB scripts that worked on one system but not others.

I am running into the same problem now on a webapp I'm modifying for one of my clients, originally deployed on Linux MySQL v5.0.83-log. I'm running Windows MySQL v5.1.41. Even trying to use the latest version of phpMyAdmin to extract the database, it doesn't report a default for the text column in question. Yet, when I try running an insert on Windows (that works fine on the Linux deployment) I receive an error of no default on ABC column. I try to recreate the table locally with the obvious default (based on a select of unique values for that column) and end up receiving the oh-so-useful BLOB/TEXT column can't have a default value.

Again, not maintaining basic compatability across platforms is unacceptable and is a bug.

How to disable strict mode in MySQL 5 (Windows):

• Edit /my.ini and look for line

  sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
  

• Replace it with

  sql_mode='MYSQL40'
  

• Restart the MySQL service (assuming that it is mysql5)

  net stop mysql5
  net start mysql5
  

If you have root/admin access you might be able to execute

mysql_query("SET @@global.sql_mode='MYSQL40'");

Support for using expression as default values was added to MySQL 8.0.13, released 2018-10-22, and works for TEXT, JSON, BLOB and GEOMETRY.

You still cannot write :

create table foo(bar text default 'baz')

But you can now write:

create table foo(bar text default ('baz'))

Which achieve the same thing.

This is a very old question but still it doesn't seems to have been answered properly. And, my this answer isn't actual answer to the question - "WHY can't a text column have a default value", but as it isn't possible to write long text in comment, and as my comment could help someone to prevent the error, here it is as a separate answer:

Some are saying that the error is occurring because of OS - Windows-Linux; but this isn't directly related to OS. (However, there may be differences in default settings of MySQL within different installers for different OSes, I am not sure.)

The main reason is the flag STRICT_TRANS_TABLES for sql_mode setting. if a value is not specified in INSERT statement for TEXT datatype column and if the flag exist in the sql_mode setting then MySQL is reporting an error; and if the flag doesn't exist then MySQL is only reporting a warning and inserts the record.

So, to prevent this error, one can remove the STRICT_TRANS_TABLES from sql_mode setting of MySQL. (He my need to reset the mode to the previous value if it can affect other operations on the database.)

According to the documentation of SQL mode in MySQL ...

For STRICT_TRANS_TABLES, MySQL converts an invalid value to the closest valid value for the column and inserts the adjusted value. If a value is missing, MySQL inserts the implicit default value for the column data type. In either case, MySQL generates a warning rather than an error and continues processing the statement. Implicit defaults are described in Section 11.6, “Data Type Default Values”.

... and documentation of Data Type Default Values ...

The BLOB, TEXT, GEOMETRY, and JSON data types cannot be assigned a default value.

... TEXT column can not have a default value, but if STRICT_TRANS_TABLES is removed from sql_mode then MySQL inserts empty string '' if no value is specified for TEXT column in INSERT statement.