MySQL Bugs: #42628: column comment deleted after changing charset/collation for column by statement

Bug #42628	column comment deleted after changing charset/collation for column by statement
Submitted:	5 Feb 2009 16:45	Modified:	9 Mar 2009 9:42
Reporter:	Joachim Bartels	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Documentation	Severity:	S2 (Serious)
Version:	5.1.30, 5.1.31	OS:	Windows (Vista Ultimate)
Assigned to:	Paul DuBois	CPU Architecture:	Any
Tags:	ALTER TABLE, charset, collation, comment, modify

Description:
Comments for columns in tables will be deleted, when the table/column-collation is changed by a statement (example see below).

How to repeat:
Define a column in a table as charset 255, utf8 with utf8_general_ci collation.
Edit the comment for this column with e.g. "Test".

Edit the following SQL-statements:

ALTER TABLE `mydatabase`.`mytable` CHARACTER SET latin1 COLLATE latin1_german2_ci;
ALTER TABLE `mydatabase`.`mytable` MODIFY COLUMN `mycolumn` VARCHAR(255) CHARACTER SET latin1 COLLATE latin1_german2_ci;

The former comment "Test" will be deleted when executing the statement.

Suggested fix:
Make sure, that the comments for columns, that are changed by a statement, will not be deleted.

Verified just as described with 5.1.31:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.1.31-enterprise-gpl-pro-debug MySQL Enterprise Server - Pro Ed
ition Debug (GPL)

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

mysql> use test
Database changed
mysql> create table mycomm(c1 int, c2 varchar(255) comment 'column comment');
Query OK, 0 rows affected (0.63 sec)

mysql> show create table mycomm\G
*************************** 1. row ***************************
       Table: mycomm
Create Table: CREATE TABLE `mycomm` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(255) DEFAULT NULL COMMENT 'column comment'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.08 sec)

mysql> alter table mycomm character set utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.30 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mycomm\G
*************************** 1. row ***************************
       Table: mycomm
Create Table: CREATE TABLE `mycomm` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(255) CHARACTER SET latin1 DEFAULT NULL COMMENT 'column comment'
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

mysql> alter table mycomm modify column c2 varchar(255) character set utf8 colla
te utf8_general_ci;
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table mycomm\G
*************************** 1. row ***************************
       Table: mycomm
Create Table: CREATE TABLE `mycomm` (
  `c1` int(11) DEFAULT NULL,
  `c2` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

This may be even intended behaviour (in some RDBMSes ALTER TABLE ... MODIFY must provide all attributes of column, explicitely), but this is NOT clearly documented, and Oracle (where non-standard ALTER TABLE ... MODIFY initially comes from) does NOT work this way. So, still, I'd say this is a bug.

Thank you for your bug report. This issue has been addressed in the documentation. The updated documentation will appear on our website shortly, and will be included in the next release of the relevant products.

Unspecified attributes are not carried forward by intent. Otherwise, how would you remove them?

I've added the following to the ALTER TABLE statement to clarify the intended behavior:

When you use CHANGE or MODIFY, column_definition must include the
data type and all attributes that should apply to the new column,
other than index attributes such as PRIMARY KEY or UNIQUE. Attributes
present in the original definition but not specified for the new
definition are not carried forward. Suppose a column col1 is defined
as INT UNSIGNED DEFAULT 1 COMMENT 'my column' and you modify the
column as follows:

ALTER TABLE t1 MODIFY col1 BIGINT;

The resulting column will be defined as BIGINT, but will not include
the attributes UNSIGNED DEFAULT 1 COMMENT 'my column'. To retain
them, the statement should be: 

ALTER TABLE t1 MODIFY col1 BIGINT UNSIGNED DEFAULT 1 COMMENT 'my column';

Hi, Paul,

thanks for your response. One question, since I´m a newbie to MySQL - do you see a way of changing the charset of all columns in a DB by code or SQL and NOT deleting the comments?

In my case there were about 275 tables with lots of varchar columns that had to be changed from utf8 to latin1 (since I started with the "false" charset). I did some code to generate a SQL-statement using VBA and the table definitions provided by DAO. Alas I don´t see a way of integrating the existing comments into the new SQL-statement so the comments will be preserved. Also, there is no way of updating the column coments using a backup database (these tables are read-only). Would be great if you or someone else could provide a hint to a solution. TIA

Kind regards,

Joachim Bartels
AGA Media

Hi Joachim,

As long as you want to convert all character columns to the same character set, you can use:

ALTER TABLE tbl_name CONVERT TO CHARACTER SET charset_name [COLLATE collation_name];

This will retain column comments.