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

Question

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'");

Without any deep knowledge of the mySQL engine, I'd say this sounds like a memory saving strategy. I assume the reason is behind this paragraph from the docs:

Each BLOB or TEXT value is represented internally by a separately allocated object. This is in contrast to all other data types, for which storage is allocated once per column when the table is opened.

It seems like pre-filling these column types would lead to memory usage and performance penalties.

As the main question:

Anybody know why this is not allowed?

is still not answered, I did a quick search and found a relatively new addition from a MySQL developer at MySQL Bugs:

[17 Mar 2017 15:11] Ståle Deraas

Posted by developer:

This is indeed a valid feature request, and at first glance it might seem trivial to add. But TEXT/BLOBS values are not stored directly in the record buffer used for reading/updating tables. So it is a bit more complex to assign default values for them.

This is no definite answer, but at least a starting point for the why question.

In the mean time, I'll just code around it and either make the column nullable or explicitly assign a (default '') value for each insert from the application code...

"Support for DEFAULT in TEXT/BLOB columns" is a feature request in the MySQL Bugtracker (Bug #21532).

I see I'm not the only one who would like to put a default value in a TEXT column. I think this feature should be supported in a later version of MySQL.

This can't be fixed in the version 5.0 of MySQL, because apparently it would cause incompatibility and dataloss if anyone tried to transfer a database back and forth between the (current) databases that don't support that feature and any databases that did support that feature.