MySQL Bugs: #19498: Inconsistent support for DEFAULT in TEXT columns

Bug #19498	Inconsistent support for DEFAULT in TEXT columns
Submitted:	3 May 2006 2:04	Modified:	15 Aug 2006 5:32
Reporter:	Ask Bjørn Hansen (Basic Quality Contributor)
Status:	Closed
Category:	Server: Types	Severity:	S3 (Non-critical)
Version:	5.0.18	OS:	Not specific
Assigned to:	Jim Winstead	Target Version:

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.

verified with current 5.0bk source on linux

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

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

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

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?

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

Pushed to 5.0.25

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.

Tim, I added it as an enhancement request in http://bugs.mysql.com/21532.

Thanks.  :-)

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

(reopening the bug so at least the documentation can be updated, if deemed appropriate)

The changelog entry is derived pretty directly from
the developer comment in the changeset that includes
the patch.

Merged into 5.1.12

Noted in 5.1.12 changelog.