Bug #18580 Default values on BLOB or TEXT columns

• Submitted: 28 Mar 2006 17:34
• Modified: 28 Mar 2006 17:54
• Reporter: Chad MILLER
• Status: Can't repeat
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.19
• CPU Architecture: Any

[28 Mar 2006 17:34] Chad MILLER

Description:
One can create a table that has a Text or BLOB column and still specify a default value, even though the docs say it is illegal.  When the default value is used in an insertion, one does get an error, but that is too late.

See also Bug#18482: mysqldump does not dumps tinytext and mediumtext default

How to repeat:
create table t1 (a tinytext default 'a', b int);
-- should cause a warning
insert into t1 (a, b) values ('x', 1);
-- normal
insert into t1 (b) values (1);
-- failure

Suggested fix:
Give a warning message when a default value is applied on illegal types.

[28 Mar 2006 17:54] MySQL Verification Team

Thank you for the bug report. I was unable to repeat with current
source serve on Suse Linuxr:

You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.20-debug

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> create table t1 (a tinytext default 'a', b int);
ERROR 1101 (42000): BLOB/TEXT column 'a' can't have a default value
mysql> 
-------------------------------------------------------------------
mysql> select version();
+------------------+
| version()        |
+------------------+
| 5.1.8-beta-debug |
+------------------+
1 row in set (0.00 sec)

mysql> create table t1 (a tinytext default 'a', b int);
ERROR 1101 (42000): BLOB/TEXT column 'a' can't have a default value