# Converting only collation **URL:** \ **Category:** Other MySQL® Questions\ **Created:** [September 27, 2021, 11:57pm UTC](https://forums.percona.com/t/converting-only-collation/12313 "2021-09-27T23:57:15Z")\ **Posts on this page:** 4\ **Page:** 1
**Author:** ![Venkat](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/venkat/32/5328_2.png) [@Venkat](https://forums.percona.com/u/Venkat)\ **Post date:** [September 27, 2021, 11:57pm UTC](https://forums.percona.com/t/converting-only-collation/12313/1 "2021-09-27T23:57:15Z")
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? ---
**Author:** ![Jyoti\_Rajai](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jyoti_rajai/32/4482_2.png) [@Jyoti\_Rajai](https://forums.percona.com/u/Jyoti_Rajai)\ **Post date:** [September 28, 2021, 3:18am UTC](https://forums.percona.com/t/converting-only-collation/12313/2 "2021-09-28T03:18:13Z")
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 : ```auto 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> ``` ---
**Author:** ![Venkat](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/venkat/32/5328_2.png) [@Venkat](https://forums.percona.com/u/Venkat)\ **Post date:** [September 28, 2021, 5:04am UTC](https://forums.percona.com/t/converting-only-collation/12313/3 "2021-09-28T05:04:50Z")
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. ---
**Author:** ![Jyoti\_Rajai](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/jyoti_rajai/32/4482_2.png) [@Jyoti\_Rajai](https://forums.percona.com/u/Jyoti_Rajai)\ **Post date:** [September 28, 2021, 6:21am UTC](https://forums.percona.com/t/converting-only-collation/12313/4 "2021-09-28T06:21:18Z")
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.