Bug #19498 Inconsistent support for DEFAULT in TEXT columns
Submitted: 3 May 2006 0:04 Modified: 15 Aug 2006 3:32
Reporter: Ask Bjørn Hansen (Basic Quality Contributor) Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Data Types Severity:S3 (Non-critical)
Version:5.0.18 OS:Not specific
Assigned to: Jim Winstead CPU Architecture:Any

[3 May 2006 0:04] Ask Bjørn Hansen
Description:
http://dev.mysql.com/doc/refman/5.0/en/blob.html says a TEXT column can't have a default value, but it can have a default value of "".

However, the default value is not preserved by mysqldump.

How to repeat:
mysql> create table bar1 ( i int, abc text not null default '' );
Query OK, 0 rows affected (0.38 sec)

mysql> create table bar2 ( i int, abc text not null );
Query OK, 0 rows affected (0.00 sec)

mysql> create table bar3 ( i int, abc text not null default 'test' );
ERROR 1101 (42000): BLOB/TEXT column 'abc' can't have a default value

mysql> insert into bar1 (i) values (1);
Query OK, 1 row affected (0.06 sec)

mysql> insert into bar2 (i) values (1);
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> show warnings;
+---------+------+------------------------------------------+
| Level   | Code | Message                                  |
+---------+------+------------------------------------------+
| Warning | 1364 | Field 'abc' doesn't have a default value |
+---------+------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table bar1\G
*************************** 1. row ***************************
       Table: bar1
Create Table: CREATE TABLE `bar1` (
  `i` int(11) default NULL,
  `abc` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

mysql> show create table bar2\G
*************************** 1. row ***************************
       Table: bar2
Create Table: CREATE TABLE `bar2` (
  `i` int(11) default NULL,
  `abc` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

Suggested fix:
Update the documentation and fix mysqldump to include the "default ''" when it was specified when the table was created.
[3 May 2006 12:57] Hartmut Holzgraefe
verified with current 5.0bk source on linux
[12 Jul 2006 4:03] Greg Lehey
This problem was reported against mysqldump on Mac OS X.  In fact, it occurs on other platforms, including Linux and probably all others, and it's not specifically related to mysqldump, though the problem occurs with mysqldump as well.  The test case shows this: it doesn't refer to mysqldump at all.

The issue here is in 'SHOW CREATE', which doesn't show the defaults.  The test case shows that they are stored.  I'm resetting the category to "Server: Data Types".
[18 Jul 2006 22:07] Jim Winstead
A default value of '' is allowed to be specified for TEXT and BLOB fields, but it does not actually allow a default of '' (except in the case of a NOT NULL field, which will always have a default of '').

It appears this is a feature just to make life easier for applications that automate table generation. We'll make it so this issues a warning (or fails with an error in strict mode).
[18 Jul 2006 22:57] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/9310
[20 Jul 2006 17:49] Ask Bjørn Hansen
Hmn.  Other fields keep that sort of extra meta-data even if it's not actually supported.

(It could be that it's Just Not Right, but in my application I use the "DEFAULT" meta-data to have my framework Do The Right Thing).

Would it be possible to make TEXT fields support a default value?
[22 Jul 2006 17:32] Timothy Smith
Patch looks good.  Thanks, Jim.

Ask Bjørn, allowing default values for BLOB/TEXT fields could be added as a separate "feature request" bug.

Regards,

Timothy
[3 Aug 2006 16:46] Magnus Blåudd
Pushed to 5.0.25
[4 Aug 2006 3:53] Paul Dubois
Noted in 5.0.25 changelog.

TEXT and BLOB columns do not support DEFAULT values. However, when a
default of '' was specified, the specification was silently ignored.
This now results in a warning, or an error in strict mode.
[9 Aug 2006 9:29] Ask Bjørn Hansen
Tim, I added it as an enhancement request in http://bugs.mysql.com/21532.

Thanks.  :-)
[10 Aug 2006 9:13] Ask Bjørn Hansen
Hi Paul,

The change log entry isn't entirely correct.  The default value of '' was actually stored and mysqld would tell you about it if you asked in the right way, but SHOW CREATE ... didn't include it.

 - ask
[10 Aug 2006 9:14] Ask Bjørn Hansen
(reopening the bug so at least the documentation can be updated, if deemed appropriate)
[10 Aug 2006 23:16] Paul Dubois
The changelog entry is derived pretty directly from
the developer comment in the changeset that includes
the patch.
[14 Aug 2006 20:56] Konstantin Osipov
Merged into 5.1.12
[15 Aug 2006 3:32] Paul Dubois
Noted in 5.1.12 changelog.
[29 Dec 2010 11:41] Awan Nord
http://dev.mysql.com/doc/refman/5.0/en/blob.html

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 incompatabiities 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.