Bug #56527 BLOB column default value: is it a error or a warning?

• Submitted: 3 Sep 2010 7:07
• Modified: 5 Nov 2012 12:10
• Reporter: Gleb Shchepa
• Status: Duplicate
• Category: MySQL Server: DDL
• Severity: S3 (Non-critical)
• Version: 5.1+
• OS: Any
• CPU Architecture: Any

[3 Sep 2010 7:07] Gleb Shchepa

Description:
As the documentation says, blobs can't have default values.

It is interesting, that the parser processes this "can't have" in a different way for empty and for non-empty default values: a warning for empty values and an error for non-empty ones.

mysql> CREATE TABLE t1(a BLOB DEFAULT "");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'a' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> DROP TABLE t1;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE t1(a BLOB DEFAULT "1");
ERROR 1101 (42000): BLOB/TEXT column 'a' can't have a default value

How to repeat:
DROP TABLE IF EXISTS t1;
CREATE TABLE t1(a BLOB DEFAULT "");
SHOW WARNINGS;
DROP TABLE t1;
CREATE TABLE t1(a BLOB DEFAULT "1");

[3 Sep 2010 7:15] Valeriy Kravchuk

Thank you for the problem report.

[10 Sep 2010 10:09] Gleb Shchepa

Omer,

Sure, this minor issue may be sorted out as "not a bug", but in
this case we have another: CREATE TABLE doesn't ignore this empty
default string!
It really works as empty default string later, so as minimum we
have to update our documentation.

Example:

mysql> CREATE TABLE t1(a BLOB NOT NULL DEFAULT "");
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW WARNINGS;
+---------+------+-------------------------------------------------+
| Level   | Code | Message                                         |
+---------+------+-------------------------------------------------+
| Warning | 1101 | BLOB/TEXT column 'a' can't have a default value |
+---------+------+-------------------------------------------------+
1 row in set (0.00 sec)

mysql> INSERT INTO t1 VALUES ();
Query OK, 1 row affected (0.00 sec)

#
# Warning missed -^
#

mysql> SHOW WARNINGS;
Empty set (0.00 sec)

#
# Expected result:
#

mysql> INSERT INTO t1 VALUES ();
Query OK, 1 row affected, 1 warning (0.00 sec)

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

#
# Obviously, in both cases the resulting table contains empty string:
#

mysql> SELECT a, LENGTH(a) FROM t1;
+---+-----------+
| a | LENGTH(a) |
+---+-----------+
|   |         0 |
+---+-----------+
1 row in set (0.00 sec)

[8 Oct 2010 16:06] Omer Barnir

triage: setting tag to CHECKED

[5 Nov 2012 12:10] Jon Olav Hauglid

Closing as duplicate of Bug#47705