CODEWITHSUNDEEP
mysqlmariadbtype-conversioncollationutf8mb4
Kotwarrior
Kotwarrior

Reputation: 108

Mysql convert table, collation not changing

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?

Upvotes: 3

Views: 719

Answers (1)

elenst
elenst

Reputation: 3987

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

Upvotes: 2

Related Questions