| Bug #21620 | ALTER TABLE affects other columns | ||
|---|---|---|---|
| Submitted: | 14 Aug 2006 11:53 | Modified: | 5 Oct 2006 17:09 |
| Reporter: | Marc Delisle | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Charsets | Severity: | S2 (Serious) |
| Version: | 5.0.24/5.0/5.1BK | OS: | Linux (Linux) |
| Assigned to: | Alexander Barkov | CPU Architecture: | Any |
[14 Aug 2006 12:38]
MySQL Verification Team
Thank you for the bug report. Verified as described:
miguel@hegel:~/dbs/4.1> 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 to server version: 4.1.22-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `calendar` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `creator` int(10) unsigned NOT NULL,
-> `start_time` datetime NOT NULL,
-> `end_date` date NOT NULL,
-> `circle` char(1) NOT NULL,
-> `alert_num` int(10) unsigned NOT NULL,
-> `alert_unit` varchar(10) NOT NULL,
-> `alert_sms` char(1) NOT NULL,
-> `title` tinytext NOT NULL,
-> `content` text NOT NULL,
-> `members` text NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk ;
Query OK, 0 rows affected (0.03 sec)
mysql> ALTER TABLE `calendar` CHANGE `content` `content` MEDIUMTEXT CHARACTER
-> SET gbk COLLATE gbk_chinese_ci NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table calendar\G
*************************** 1. row ***************************
Table: calendar
Create Table: CREATE TABLE `calendar` (
`id` int(10) unsigned NOT NULL auto_increment,
`creator` int(10) unsigned NOT NULL default '0',
`start_time` datetime NOT NULL default '0000-00-00 00:00:00',
`end_date` date NOT NULL default '0000-00-00',
`circle` char(1) NOT NULL default '',
`alert_num` int(10) unsigned NOT NULL default '0',
`alert_unit` varchar(10) NOT NULL default '',
`alert_sms` char(1) NOT NULL default '',
`title` tinytext NOT NULL,
`content` mediumtext NOT NULL,
`members` text NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
------------------------------------------------------------
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 5.0.25-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `calendar` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `creator` int(10) unsigned NOT NULL,
-> `start_time` datetime NOT NULL,
-> `end_date` date NOT NULL,
-> `circle` char(1) NOT NULL,
-> `alert_num` int(10) unsigned NOT NULL,
-> `alert_unit` varchar(10) NOT NULL,
-> `alert_sms` char(1) NOT NULL,
-> `title` tinytext NOT NULL,
-> `content` text NOT NULL,
-> `members` text NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk ;
Query OK, 0 rows affected (0.01 sec)
mysql> ALTER TABLE `calendar` CHANGE `content` `content` MEDIUMTEXT CHARACTER
-> SET gbk COLLATE gbk_chinese_ci NOT NULL;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table calendar\G
*************************** 1. row ***************************
Table: calendar
Create Table: CREATE TABLE `calendar` (
`id` int(10) unsigned NOT NULL auto_increment,
`creator` int(10) unsigned NOT NULL,
`start_time` datetime NOT NULL,
`end_date` date NOT NULL,
`circle` char(1) NOT NULL,
`alert_num` int(10) unsigned NOT NULL,
`alert_unit` varchar(10) NOT NULL,
`alert_sms` char(1) NOT NULL,
`title` tinytext NOT NULL,
`content` mediumtext NOT NULL,
`members` mediumtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.01 sec)
mysql>
------------------------------------------------------------
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.1.12-beta-debug
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE `calendar` (
-> `id` int(10) unsigned NOT NULL auto_increment,
-> `creator` int(10) unsigned NOT NULL,
-> `start_time` datetime NOT NULL,
-> `end_date` date NOT NULL,
-> `circle` char(1) NOT NULL,
-> `alert_num` int(10) unsigned NOT NULL,
-> `alert_unit` varchar(10) NOT NULL,
-> `alert_sms` char(1) NOT NULL,
-> `title` tinytext NOT NULL,
-> `content` text NOT NULL,
-> `members` text NOT NULL,
-> PRIMARY KEY (`id`)
-> ) ENGINE=MyISAM DEFAULT CHARSET=gbk ;
Query OK, 0 rows affected (0.02 sec)
mysql> ALTER TABLE `calendar` CHANGE `content` `content` MEDIUMTEXT CHARACTER
-> SET gbk COLLATE gbk_chinese_ci NOT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table calendar\G
*************************** 1. row ***************************
Table: calendar
Create Table: CREATE TABLE `calendar` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`creator` int(10) unsigned NOT NULL,
`start_time` datetime NOT NULL,
`end_date` date NOT NULL,
`circle` char(1) NOT NULL,
`alert_num` int(10) unsigned NOT NULL,
`alert_unit` varchar(10) NOT NULL,
`alert_sms` char(1) NOT NULL,
`title` tinytext NOT NULL,
`content` mediumtext NOT NULL,
`members` mediumtext NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)
mysql>
[26 Aug 2006 18:38]
Calvin Sun
This is not storage engine specific issue. I can reproduce the problem with InnoDB. Also, if the default charset is set as latin1, the problem disappears.
[11 Sep 2006 12:01]
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/11688 ChangeSet@1.2235, 2006-09-11 16:51:03+05:00, bar@mysql.com +3 -0 Bug#21620 ALTER TABLE affects other columns Problem: for character sets having mbmaxlen==2, any ALTER TABLE changed TEXT column type to MEDIUMTEXT, due to wrong "internal length to create length" formula. Fix: removing rounding code introduced in early 4.1 time, which is not correct anymore.
[29 Sep 2006 11:25]
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/12832 ChangeSet@1.2283, 2006-09-29 16:24:11+05:00, bar@mysql.com +3 -0 Bug#21620 ALTER TABLE affects other columns Problem: for character sets having mbmaxlen==2, any ALTER TABLE changed TEXT column type to MEDIUMTEXT, due to wrong "internal length to create length" formula. Fix: removing rounding code introduced in early 4.1 time, which is not correct anymore.
[3 Oct 2006 20:01]
Chad MILLER
Available in 5.0.26.
[3 Oct 2006 20:15]
Chad MILLER
Available in 5.1.12-beta.
[5 Oct 2006 17:09]
Paul DuBois
Noted in 5.0.26, 5.1.12 changelogs. For character sets having a mbmaxlen value of 2, any ALTER TABLE statement changed TEXT columns to MEDIUMTEXT.
[10 Oct 2006 14:49]
Minfeng Zhang
The Problem still exist in MySQL 5.0.26 reproduce: Create a new database using Collation "gbk_chinese_ci", then run SQL: CREATE TABLE `mails` ( `id` int(10) unsigned NOT NULL auto_increment, `creator` int(10) unsigned NOT NULL, `start_time` datetime NOT NULL, `temp` char(1) NOT NULL, `backup` char(1) NOT NULL, `title` tinytext NOT NULL, `content` text NOT NULL, `members` text NOT NULL, `members_left` text NOT NULL, `members_backup` text NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk ; alert any "text" fields will cause other "text" fields turn to "mediumtext"
[10 Oct 2006 19:04]
MySQL Verification Team
Please see http://bugs.mysql.com/bug.php?id=23141 regarding version fixed.
Description: Changing one column with ALTER TABLE affects another column. How to repeat: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 42 to server version: 5.0.24-max-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> use test; Database changed mysql> CREATE TABLE `calendar` ( -> `id` int(10) unsigned NOT NULL auto_increment, -> `creator` int(10) unsigned NOT NULL, -> `start_time` datetime NOT NULL, -> `end_date` date NOT NULL, -> `circle` char(1) NOT NULL, -> `alert_num` int(10) unsigned NOT NULL, -> `alert_unit` varchar(10) NOT NULL, -> `alert_sms` char(1) NOT NULL, -> `title` tinytext NOT NULL, -> `content` text NOT NULL, -> `members` text NOT NULL, -> PRIMARY KEY (`id`) -> ) ENGINE=MyISAM DEFAULT CHARSET=gbk ; Query OK, 0 rows affected (0.05 sec) mysql> ALTER TABLE `calendar` CHANGE `content` `content` MEDIUMTEXT CHARACTER SET gbk COLLATE gbk_chinese_ci NOT NULL; Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> show create table calendar; +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | calendar | CREATE TABLE `calendar` ( `id` int(10) unsigned NOT NULL auto_increment, `creator` int(10) unsigned NOT NULL, `start_time` datetime NOT NULL, `end_date` date NOT NULL, `circle` char(1) NOT NULL, `alert_num` int(10) unsigned NOT NULL, `alert_unit` varchar(10) NOT NULL, `alert_sms` char(1) NOT NULL, `title` tinytext NOT NULL, `content` mediumtext NOT NULL, `members` mediumtext NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=gbk | +----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql>