Bug #20108 alter table default value errors on enum fields when unicode
Submitted: 27 May 2006 13:32 Modified: 5 Oct 2006 16:20
Reporter: Ahmet UK Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: Charsets Severity:S2 (Serious)
Version:5.0.18-nt/4.1BK/5.0BK/5.1BK OS:Microsoft Windows (WinXP SP2/Linux)
Assigned to: Igor Babaev CPU Architecture:Any

[27 May 2006 13:32] Ahmet UK
Description:
When we have a table enum field with a default value, at unicode charset(ucs2), alter process fails if we want to add any field type.
The error code is #1067 - Invalid default value for 'recStatus'

Is there any way to solve it, or is this a really bug?

How to repeat:
CREATE DATABASE `deneme` DEFAULT CHARACTER SET ucs2 COLLATE ucs2_turkish_ci;
USE deneme;

DROP TABLE IF EXISTS `test`;
CREATE TABLE IF NOT EXISTS `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `testText` varchar(255) collate ucs2_turkish_ci default NULL,
  `testContent` longtext collate ucs2_turkish_ci,
  `recOrder` int(10) unsigned default NULL,
  `recStatus` enum('active','passive') collate ucs2_turkish_ci NOT NULL default 'passive',
  `recUpDate` datetime NOT NULL,
  `recDate` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_turkish_ci;

ALTER TABLE `test` ADD `testField` INT UNSIGNED NOT NULL AFTER `id` ;
-- error occured

Suggested fix:
If we change charset to anything else unicode, ie:latin5, everything works fine. But we loose the unicode property...
[29 May 2006 12:59] Miguel Solorzano
Thank you for the bug report.
Maybe something related to the bug:

http://bugs.mysql.com/bug.php?id=8204

miguel@hegel:~/dbs/5.0> bin/mysql -uroot db4
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3 to server version: 5.0.23-debug

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

mysql> CREATE DATABASE `deneme` DEFAULT CHARACTER SET ucs2 COLLATE ucs2_turkish_ci;
Query OK, 1 row affected (0.00 sec)

mysql> USE deneme;
Database changed
mysql> 
mysql> DROP TABLE IF EXISTS `test`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE IF NOT EXISTS `test` (
    ->   `id` int(10) unsigned NOT NULL auto_increment,
    ->   `testText` varchar(255) collate ucs2_turkish_ci default NULL,
    ->   `testContent` longtext collate ucs2_turkish_ci,
    ->   `recOrder` int(10) unsigned default NULL,
    ->   `recStatus` enum('active','passive') collate ucs2_turkish_ci NOT NULL default
    -> 'passive',
    ->   `recUpDate` datetime NOT NULL,
    ->   `recDate` datetime NOT NULL,
    ->   PRIMARY KEY  (`id`)
    -> ) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_turkish_ci;
Query OK, 0 rows affected, 1 warning (0.02 sec)

mysql> SHOW WARNINGS;
+---------+------+------------------------------------------------+
| Level   | Code | Message                                        |
+---------+------+------------------------------------------------+
| Warning | 1265 | Data truncated for column 'recStatus' at row 1 | 
+---------+------+------------------------------------------------+
1 row in set (0.00 sec)

mysql> SHOW CREATE TABLE test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `testText` varchar(255) collate ucs2_turkish_ci default NULL,
  `testContent` longtext collate ucs2_turkish_ci,
  `recOrder` int(10) unsigned default NULL,
  `recStatus` enum('active','passive') collate ucs2_turkish_ci NOT NULL default '',
  `recUpDate` datetime NOT NULL,
  `recDate` datetime NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=ucs2 COLLATE=ucs2_turkish_ci
1 row in set (0.00 sec)

mysql> ALTER TABLE `test` ADD `testField` INT UNSIGNED NOT NULL AFTER `id` ;
ERROR 1067 (42000): Invalid default value for 'recStatus'
mysql>
[30 May 2006 10:43] Jan Gerritsen
Hi,
we do have the same Problem with enum and ucs2 charset.

MySQL Version: 
mysql  Ver 14.7 Distrib 4.1.19, for pc-linux-gnu (i686) using readline 5.1

System:
Linux tabasco 2.6.14-hardened-r5 #1 SMP Fri Mar 17 16:47:56 CET 2006 i686 Intel(R) Pentium(R) 4 CPU 2.60GHz GNU/Linux

Example for the Problem:

CREATE TABLE `mytest` (
	`id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
	`myenum` ENUM( 'test1', 'test2', 'test3' ) NOT NULL ,
	`date` DATE NOT NULL
) ENGINE = INNODB CHARACTER SET ucs2 COLLATE ucs2_unicode_ci;

SHOW COLUMNS FROM `mytest`;
+--------+-------------------------------+------+-----+------------+----------------+
| Field  | Type                          | Null | Key | Default    | Extra          |
+--------+-------------------------------+------+-----+------------+----------------+
| id     | int(10) unsigned              |      | PRI | NULL       | auto_increment |
| myenum | enum('test1','test2','test3') |      |     | test1      |                |
| date   | date                          |      |     | 0000-00-00 |                |
+--------+-------------------------------+------+-----+------------+----------------+

ALTER TABLE `mytest` CHANGE `myenum` 
	`myenum` ENUM( 'test 1', 'test 2', 'test 3' ) 
	CHARACTER SET ucs2 COLLATE ucs2_unicode_ci NOT NULL 
	DEFAULT 'test 1';

SHOW COLUMNS FROM `mytest`;
+--------+--------------------------------+------+-----+------------+----------------+
| Field  | Type                           | Null | Key | Default    | Extra          |
+--------+--------------------------------+------+-----+------------+----------------+
| id     | int(10) unsigned               |      | PRI | NULL       | auto_increment |
| myenum | enum('test1','test 2','test3') |      |     |            |                |
| date   | date                           |      |     | 0000-00-00 |                |
+--------+--------------------------------+------+-----+------------+----------------+

ALTER TABLE `mytest` CHANGE `date` 
	`date` DATE NULL DEFAULT '0000-00-00';

#1067 - Invalid default value for 'myenum'
[12 Sep 2006 15:10] Igor Babaev
In fact the default value for the enum UCS2 values turns out to be corrupted
that can can be seen from the following output:

mysql> SHOW CREATE TABLE test;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                               |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| test  | CREATE TABLE `test` (
  `id` int(10) unsigned NOT NULL auto_increment,
  `testText` varchar(255) character set ucs2 collate ucs2_turkish_ci default NULL,
  `testContent` longtext character set ucs2 collate ucs2_turkish_ci,
  `recOrder` int(10) unsigned default NULL,
  `recStatus` enum('active','passive') character set ucs2 collate ucs2_turkish_ci NOT NULL default '',
  `recUpDate` datetime NOT NULL default '0000-00-00 00:00:00',
  `recDate` datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
[12 Sep 2006 21:47] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/11807

ChangeSet@1.2566, 2006-09-12 14:47:24-07:00, igor@rurik.mysql.com +4 -0
  Fixed bug #20108.
  Any default value for a enum fields over UCS2 charsets was corrupted
  when we put it into the frm file, as it had been overwritten by its
  HEX representation.
  To fix it we now we save a copy of structure that represents the enum
  type and when putting the default values we use this copy.
[20 Sep 2006 17:46] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12289

ChangeSet@1.2571, 2006-09-20 09:46:12-07:00, igor@rurik.mysql.com +4 -0
  Fixed bug #20108.
  Any default value for a enum fields over UCS2 charsets was corrupted
  when we put it into the frm file, as it had been overwritten by its
  HEX representation.
  To fix it now we save a copy of structure that represents the enum
  type and when putting the default values we use this copy.
[20 Sep 2006 18:40] Bugs System
A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/12285

ChangeSet@1.2571, 2006-09-20 08:40:55-07:00, igor@rurik.mysql.com +4 -0
  Fixed bug #20108.
  Any default value for a enum fields over UCS2 charsets was corrupted
  when we put it into the frm file, as it had been overwritten by its
  HEX representation.
  To fix it we now we save a copy of structure that represents the enum
  type and when putting the default values we use this copy.
[1 Oct 2006 8:56] Georgi Kodinov
Pushed in 4.1.22/5.0.26/5.1.12
[5 Oct 2006 16:20] Paul Dubois
Noted in 4.1.22, 5.0.26, 5.1.12 changelogs.

For an ENUM column that used the ucs2 character set, using ALTER
TABLE to modify the column definition caused the default value to be
lost.