Bug #23141 Bug#21620 still exist in MySQL 5.0.26

• Submitted: 10 Oct 2006 14:57
• Modified: 10 Oct 2006 19:02
• Reporter: Minfeng Zhang
• Status: Closed
• Category: MySQL Server
• Severity: S3 (Non-critical)
• Version: 5.0.26
• OS: Windows (Win32)
• CPU Architecture: Any
• Tags: character sets, field type

[10 Oct 2006 14:57] Minfeng Zhang

Description:
Bug#21620 still exist in MySQL 5.0.26

ChangeSet@1.2235, 2006-09-11 16:51:03+05:00, [e-mail] +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.

How to repeat:
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 ;

alter any "TEXT" fields will cause other "TEXT" fields turn to "MEDIUMTEXT" type

[10 Oct 2006 19:02] MySQL Verification Team

Thank you for your bug report. This issue has been committed to our source repository of that product and will be incorporated into the next release.

If necessary, you can access the source repository and build the latest available version, including the bug fix. More information about accessing the source trees is available at

    http://dev.mysql.com/doc/en/installing-source.html

Additional Info:

F:\mysql-5.0.26-win32\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2 to server version: 5.0.26-community-nt

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.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.06 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>
-------------------------------------------------------------------
F:\mysql-5.0.27-win\bin>mysql -uroot test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.0.27 Source distribution

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.08 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` text NOT NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=gbk
1 row in set (0.00 sec)

mysql>