Description:
If I combine CONVERT TO CHARACTER SET and MODIFY COLUMN in one ALTER TABLE statement I expect that the table gets converted and that the column gets modified, but that doesn't happen: only the conversion is done.
I tried to do this to convert a table to utf8 and then change the columns which used latin1_general_cs to utf8_bin (there is no utf8_general_cs). This is needed as CONVERT TO CHARACTER SET modifies ALL columns. It looks this must be done in two statements, which might be okay if there was transactional DDL support.
Related: Bug #71939
How to repeat:
mysql [footest] > create table t1 (id int, val1 varchar(100) collate latin1_general_cs, val2 varchar(100) collate latin1_general_ci);
Query OK, 0 rows affected (0.00 sec)
mysql [footest] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`val1` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
`val2` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
mysql [footest] > ALTER TABLE t1 CONVERT TO CHARACTER SET utf8, MODIFY COLUMN `val1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [footest] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`val1` varchar(100) DEFAULT NULL,
`val2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql [footest] > drop table t1;
Query OK, 0 rows affected (0.00 sec)
mysql [footest] > CREATE TABLE `t1` (
-> `id` int(11) DEFAULT NULL,
-> `val1` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_cs DEFAULT NULL,
-> `val2` varchar(100) CHARACTER SET latin1 COLLATE latin1_general_ci DEFAULT NULL
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.01 sec)
mysql [footest] > ALTER TABLE t1 MODIFY COLUMN `val1` varchar(100) CHARACTER SET utf8 COLLATE utf8_bin DEFAULT NULL, CONVERT TO CHARACTER SET utf8;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql [footest] > show create table t1\G
*************************** 1. row ***************************
Table: t1
Create Table: CREATE TABLE `t1` (
`id` int(11) DEFAULT NULL,
`val1` varchar(100) DEFAULT NULL,
`val2` varchar(100) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
Suggested fix:
Make sure CONVERT TO and MODIFY COLUMN work okay together or don't allow them to be used together in one statement.
