Bug #21532 Support for DEFAULT in TEXT/BLOB columns

• Submitted: 9 Aug 2006 11:28
• Modified: 5 Oct 2008 12:05
• Reporter: Ask Bjørn Hansen (Basic Quality Contributor)
• Status: Verified
• Category: Server: DDL
• Severity: S4 (Feature request)
• Version: 5.0.25
• OS: Any (All)
• Tags: text columntype, qc
• Triage: Triaged: D5 (Feature request)

[9 Aug 2006 11:28] Ask Bjørn Hansen

Description:
TEXT columns don't support DEFAULT values.  It'd be nice if they did.

Before 5.0.25 there was some limited (although inconsistent support).  Notably you could
specify a default value of ''.  As Jim said in http://bugs.mysql.com/bug.php?id=19498, it
makes life a little easier for applications that automate some logic from the schema.

How to repeat:
See http://bugs.mysql.com/bug.php?id=19498

Suggested fix:
Implement DEFAULT for TEXT columns. 

To restore the (unintentional) pre-5.0.25 functionality it'd be helpful if just DEFAULT
'' was supported.

[28 Aug 2006 14:27] Valeriy Kravchuk

Thank you for a reasonable feature request.

[5 Oct 2008 1:09] Konstantin Osipov

Which case specifically doesn't work?

[5 Oct 2008 4:01] Ask Bjørn Hansen

Konstantin, there are some pretty specific examples in
http://bugs.mysql.com/bug.php?id=19498

[5 Oct 2008 12:05] Konstantin Osipov

Test case:

drop table if exists bar1, bar3;
create table bar1 ( i int, abc text not null default '' );
show warnings;
create table bar3 ( i int, abc text not null default 'test' );

Results: 

mysql> create table bar1 ( i int, abc text not null default '' );
Query OK, 0 rows affected, 1 warning (0.01 sec)

mysql> show warnings;
+---------+------+---------------------------------------------------+
| Level   | Code | Message                                           |
+---------+------+---------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'abc' can't have a default value | 
+---------+------+---------------------------------------------------+
1 row in set (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

[5 Oct 2008 12:06] Konstantin Osipov

As can be observed from the execution log, there is no consistency (presence of a default
sometimes yield an error, sometimes a warning), and, well, no support.

[5 Oct 2008 13:00] Konstantin Osipov

Bug#26798 ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value was marked a
duplicate of this bug.