Mysql convert table, collation not changing

714 views Asked by At

I'm using mariadb (" 10.1.20-MariaDB-1~trusty") with utf8mb4. Now I'm in the process of converting all tables to "row_format = dynamic" and table collation "utf8mb4_unicode_ci". I've noticed that there are some rogue tables in my database that still have "utf8mb4_general_ci" as collation, like this one:

use database;

SHOW TABLE STATUS WHERE COLLATION != "utf8mb4_unicode_ci";

    | Name                       | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time | Check_time | Collation          | Checksum | Create_options     | Comment |
+----------------------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+-------------+------------+--------------------+----------+--------------------+---------+
| table                    | InnoDB |      10 | Dynamic    |    5 |           3276 |       16384 |               0 |        32768 |         0 |           NULL | 2016-12-21 21:12:18 | NULL        | NULL       | utf8mb4_general_ci |     NULL | row_format=DYNAMIC |  

Then of course i would run something like this:

ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Which would finish without error. Checking Table Status again afterwards, still reads

Collation = utf8mb4_general_ci

for that table.

Dumping and importing that same database into my local 5.6.32-78.0 Percona Server and doing the same there will result in the table collation being converted to utf8mb4_unicode_ci as desired.

Does anyone have an idea what might be the cause for that?

1

There are 1 answers

2
elenst On

Most likely there are no columns in the table to convert, so the operation is skipped. Try to run

ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, FORCE;

or

ALTER TABLE table CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci, ALGORITHM=COPY;

A bug report has been created based on this question: https://jira.mariadb.org/browse/MDEV-11637