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: | |
Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
Version: | 5.0.18-nt/4.1BK/5.0BK/5.1BK | OS: | Windows (WinXP SP2/Linux) |
Assigned to: | Igor Babaev | CPU Architecture: | Any |
[27 May 2006 13:32]
Ahmet UK
[29 May 2006 12:59]
MySQL Verification Team
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.