Converting only collation

MySQL & MariaDB Other MySQL® Questions
1

We have utf8_generail_ci on few varchar columns. Would like them to be utf8_unicode_ci. It looks like, the alter table only considers, collation change along with character set change (even if same character set).

alter table order_detail modify invoiced_sku varchar(50) collate utf8_unicode_ci;

This is a blocking operation. Is there a way to change collation only?

1 Like
2

Hi @Venkat ,

I tried to re-create your issue. But it works with me. I am using MySQL Version - 5.7.31-log MySQL Community Server (GPL).

Can I know which MySQL Version are you using ?
What is default character set & collation for Server , Schema , order_detail table ?

Here are logs of issue re-created by me :

mysql> create table jyoti_character_set
    -> ( id int,
    -> name varchar(20)
    -> ) CHARACTER SET latin1 COLLATE latin1_bin;
Query OK, 0 rows affected (0.03 sec)

mysql> 
mysql> 
mysql> show create table jyoti_character_set ; 
+---------------------+--------------------------------------------------------------------------------------+
| Table               | Create Table                          |
+---------------------+--------------------------------------------------------------------------------------+
| jyoti_character_set | CREATE TABLE `jyoti_character_set` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+---------------------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>  alter table jyoti_character_set add column  col1 CHAR(10) CHARACTER SET utf8 COLLATE utf8_general_ci;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> 
mysql> 
mysql> show create table jyoti_character_set ; 
+---------------------+---------------------------------------------+
| Table               | Create Table                                                                             |
+---------------------+---------------------------------------------+
| jyoti_character_set | CREATE TABLE `jyoti_character_set` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `col1` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> insert into jyoti_character_set values (1,'asf','asd');
Query OK, 1 row affected (0.01 sec)

mysql> insert into jyoti_character_set select * from jyoti_character_set ; 
Query OK, 32 rows affected (0.01 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql>  show create table jyoti_character_set ; 
+---------------------+---------------------------------------------+
| Table               | Create Table                                                                             |
+---------------------+---------------------------------------------+
| jyoti_character_set | CREATE TABLE `jyoti_character_set` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `col1` char(10) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+---------------------+---------------------------------------------+
1 row in set (0.00 sec)

mysql> alter table jyoti_character_set modify column  col1 CHAR(10) COLLATE utf8_unicode_ci;
Query OK, 64 rows affected (0.07 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> show create table jyoti_character_set ; 
+---------------------+---------------------------------------------------------------------+
| Table               | Create Table                                                                                                     |
+---------------------+---------------------------------------------------------------------+
| jyoti_character_set | CREATE TABLE `jyoti_character_set` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) COLLATE latin1_bin DEFAULT NULL,
  `col1` char(10) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 COLLATE=latin1_bin |
+---------------------+---------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
1 Like
3

Hi Jyoti,

Thanks for your response. I realize I hadn’t stated my question clearly enough. The table is utf8 character set and utf8_unicode_ci collation for most columns. But for some varchar columns historically collation was set to utf8_general_ci. We want to convert those columns to utf8_unicode_ci. The alter table modify column works fine, but it considers that as column type change (though type and size are still same). Such change works in COPY mode and not INPLACE. That’s a blocking operation. Yes, we can indeed use percona online schema change tool etc. But my question was, whether only collation change is possible, without change to data type. The collation change is not a physical change and only sort order, so could be possible as a quicker non-blocking operation. We are using MySQL 5.7.

alter table order_detail modify column invoiced_sku varchar(50) collate utf8_unicode_ci, algorithm=inplace;
ERROR 1846 (0A000): ALGORITHM=INPLACE is not supported. Reason: Cannot change column type INPLACE. Try ALGORITHM=COPY.

1 Like
4

The INPLACE algorithm will not work because you are changing collation on a column, you need to specify COPY algorithm. INPLACE algorithm only works when metadata of table are changed.

2 Likes