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:
None 
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 11:53] Marc Delisle
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>
[14 Aug 2006 12:38] Miguel Solorzano
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] Miguel Solorzano
Please see http://bugs.mysql.com/bug.php?id=23141 regarding version fixed.