Bug #21532 Support for DEFAULT in TEXT/BLOB columns

• Submitted: 9 Aug 2006 9:28
• Modified: 5 Oct 2008 10:05
• Reporter: Ask Hansen (Basic Quality Contributor)
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 5.0.25
• OS: Any (All)
• CPU Architecture: Any
• Tags: qc, text columntype

[9 Aug 2006 9:28] Ask 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 12:27] Valeriy Kravchuk

Thank you for a reasonable feature request.

[4 Oct 2008 23:09] Konstantin Osipov

Which case specifically doesn't work?

[5 Oct 2008 2:01] Ask Hansen

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

[5 Oct 2008 10: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 10: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 11: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.

[18 Feb 2010 7:26] Valeriy Kravchuk

Bug #51259 was marked as a duplicate of this one.

[23 Sep 2013 23:00] Stefan Neufeind

Still the case. Would be great to be able to give default-values just like for a varchar.

Missing to set a text-column in an insert-statement gives an error since no default-value is set. And trying to set for example an empty string as default doesn't work because blob/text can't have a default-value.

[15 Mar 2017 7:45] Stefan Neufeind

I expect it's not such a big thing to add this, is it?

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

[3 Oct 2018 9:33] Miklós Galicz

It would be nice to see it fixed after 12 years of opening the ticket...

[24 Jan 2019 20:38] Matthew Gallant

+1 This is still a thing in 2019

[9 Dec 2019 3:15] Tsubasa Tanaka

Is it supported 8.0.13 and later?

https://dev.mysql.com/doc/refman/8.0/en/data-type-defaults.html

[12 Aug 2022 4:21] Trung Tran

Almost 2023, 17 years since the request was posted and still no solution. Is it that hard?