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

Question

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

User · Accepted Answer

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

User · Answer

I normally run sites on Linux  but I also develop on a local Windows machine  I ve run into this problem many times and just fixed the tables when I encountered the problems   I installed an app yesterday to help someone out and of course ran into the problem again  So  I decided it was time to figure out what was going on - and found this thread  I really don t like the idea of changing the sql mode of the server to an earlier mode  by default   so I came up with a simple  me thinks  solution   This solution would of course require developers to wrap their table creation scripts to compensate for the MySQL issue running on Windows  You ll see similar concepts in dump files  One BIG caveat is that this could will cause problems if partitioning is used      Store the current sql mode mysql query  set  orig mode     global sql mode        Set sql mode to one that won t trigger errors    mysql query  set   global sql mode    MYSQL40             Do table creations here            Change it back to original sql mode mysql query  set   global sql mode    orig mode      That s about it

User · Answer

You can   get the same effect as  a default value  by using a trigger  create table my text       abc text     delimiter    create trigger mytext trigger before insert on my text for each row begin    if  NEW abc is null   then       set NEW abc    default text      end if  end    delimiter

User · Answer

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

User · Answer

For Ubuntu 16 04   How to disable strict mode in MySQL 5 7   Edit file  etc mysql mysql conf d mysqld cnf  If below line exists in mysql cnf  sql-mode  STRICT TRANS TABLES NO AUTO CREATE USER NO ENGINE SUBSTITUTION    Then Replace it with  sql mode  MYSQL40    Otherwise  Just add below line in mysqld cnf  sql mode  MYSQL40    This resolved problem

User · Answer

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

User · Answer

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

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

Examples related to mysql

Examples related to default-value