MySQL Bugs: #26798: ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value

Bug #26798	ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value
Submitted:	2 Mar 2007 15:54	Modified:	5 Oct 2008 10:59
Reporter:	Olaf van der Spek (Basic Quality Contributor)	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: Data Types	Severity:	S4 (Feature request)
Version:	5.1.16	OS:	Windows (Windows XP)
Assigned to:		CPU Architecture:	Any
Tags:	qc

Description:
It seems blob/text colums can't have a default value, not even the empty string. This causes a problem in strict mode, as you then have to explicitly insert the empty string each time, which shouldn't be necessary.

How to repeat:
Server version: 5.1.16-beta-community-nt MySQL Community Server (GP

mysql> create table a (b blob not null, c int);
Query OK, 0 rows affected (0.00 sec)

mysql> insert into a (c) values (1);
ERROR 1364 (HY000): Field 'b' doesn't have a default value
mysql> insert into a (b, c) values ('', 1);
Query OK, 1 row affected (0.00 sec)

mysql> alter table a change b b blob not null default '';
ERROR 1101 (42000): BLOB/TEXT column 'b' can't have a default value
mysql>

Hello Olaf,

Thank you for the report but this is not a bug. BLOB and TEXT column can not have default values.
http://dev.mysql.com/doc/refman/5.0/en/blob.html

Could you please read the rest of my report?

Just to clarify, this is basically a request to at least allow the empty string as default for blob/text columns.

Isn't it a regression ?

This is not a regression, as, at least according to older manual (http://dev.mysql.com/doc/refman/4.1/en/blob.html) any default values were not allowed for BLOB/TEXT columns.

As this is clearly described in the manual, I can not call it a bug, formally. It is a reasonable feature request (to allow '' as default value for NOT NULL BLOB/TEXT column), though.

This is a duplicate of Bug#21532

I am facing the same issue :(