Bug #25520 Cannot create a text field with a default value in MySQL 5.0.27 for windows

• Submitted: 10 Jan 2007 9:31
• Modified: 10 Jan 2007 10:41
• Reporter: Vikram J. Gurjar
• Status: Not a Bug
• Category: MySQL Server: General
• Severity: S3 (Non-critical)
• Version: 5.0.27
• OS: Windows (Windows)
• CPU Architecture: Any

[10 Jan 2007 9:31] Vikram J. Gurjar

Description:
Dear Sirs,

In a Linux (RHEL 4 kernel 2.6.19) compiled from source version of MySQL 5.0.27 the following statements work fine :

use test ;
alter table test add column abcdef text not null default '';

The same thing in Windows gives an error saying TEXT/BLOB fields cannot have a default value.

The only issue here is consistency.  Software written for Linux based servers does not create the required fields on Windows based servers.

How to repeat:
Use the following lines in mysql (5.0.27)

mysql 
use test ;
alter table test add column abcdef text not null default '';

Works in Linux but not in Windows.  Windows gives a  "TEXT/BLOB field cannot have a default value" message.

Suggested fix:
I suggest that MySQL should work consistently in Windows and Linux.  Either both OSes should not accept a default value or both OSes should accept a default value.

[10 Jan 2007 10:41] Valeriy Kravchuk

This is not a bug, but a reasult of default storage engine (InnoDB vs. MyISAM, usually) and default sql_mode (STRICT_TRANS_TABLES vs. empty) settings on Windows vs. Linux. 

You can get the same results on Linux:

openxs@suse:~/dbs/5.0> bin/mysql -uroot test
Reading table information for completion of table and column names
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
Server version: 5.0.34-debug Source distribution

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

mysql> create table test2(c1 int);
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test2 add column abcdef text not null default '';
Query OK, 0 rows affected, 1 warning (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

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

mysql> show variables like 'sql_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sql_mode      |       |
+---------------+-------+
1 row in set (0.00 sec)

mysql> set sql_mode='STRICT_ALL_TABLES';
Query OK, 0 rows affected (0.01 sec)

mysql> alter table test2 add column abcdefg text not null default '';
ERROR 1101 (42000): BLOB/TEXT column 'abcdefg' can't have a default value
mysql> show create table test2;
+-------+-----------------------------------------------------------------------
----------------------------------------------+
| Table | Create Table
                                              |
+-------+-----------------------------------------------------------------------
----------------------------------------------+
| test2 | CREATE TABLE `test2` (
  `c1` int(11) default NULL,
  `abcdef` text NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+-----------------------------------------------------------------------
----------------------------------------------+
1 row in set (0.00 sec)