mysql won't let me change collation type

Question

I'm trying to convert my table collation type from unicode to general, but mysql isn't allowing it. No errors are returned, but when I view the table, I can see that no changes are being made.

mysql> show variables like '%coll%';
+----------------------+-----------------+
| Variable_name        | Value           |
+----------------------+-----------------+
| collation_connection | utf8_general_ci |
| collation_database   | utf8_unicode_ci |
| collation_server     | utf8_unicode_ci |
+----------------------+-----------------+
3 rows in set (0.00 sec)

mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                      |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somecolumn` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> alter table abc convert to charset utf8 collate utf8_general_ci;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> show create table abc;
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table    | Create Table                                                                                                                                                                                                      |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc_test | CREATE TABLE `abc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somecolumn` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci ROW_FORMAT=DYNAMIC |
+----------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

Answer 1

I tried with mysql 5.7.18, and everything looks just fine. So which version are you using?

I set the collation variables same as yours, and created table and change the collation by the statements copied from above. And at last I got:

mysql> show create table abc;
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                       |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| abc   | CREATE TABLE `abc` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `somecolumn` bigint(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC |
+-------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> select version();
+--------------+
| version()    |
+--------------+
| 5.7.18-debug |
+--------------+

The "SHOW CREATE TABLE" doesn't print collation info because utf8_general_ci is the default collation of utf8.

Answer 2

Just use alter like this ALTER TABLE table_name COLLATE='utf8_general_ci';

Your table already has UTF8 charset. So, In my opinion you don't need to set it again. I can see that you are trying to convert it to charset utf8 again with your alter query.