mysqldefault-value

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

If you try to create a TEXT column on a table, and give it a default value in MySQL, you get an error (on Windows at least). I cannot see any reason why a text column should not have a default value. No explanation is given by the MySQL documentation. It seems illogical to me (and somewhat frustrating, as I want a default value!). Anybody know why this is not allowed?

Solution

  • 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):

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

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