Best way to convert MySQL 5.7.29 from utf8 to utf8mb4

daniel_hauke

Hi everybody,

we are struggeling with problems on our MySQL Database (5.7.29, Commercial Edition) with the configured default character-set utf8 so we have to convert this database so that it uses utf8mb4 as default characterset.

What is the preferred way to do this?

I think creating a new MySQL Database with the new default characterset utf8mb4 and then doing an export / import of the data is better then executing a bunch of sql statements which are altering the structure of the database, tables and so on.

Has anybody done this before?

Any help would be appreciated.

Greetings,

Daniel

Dave Stokes-MySQL Community Team-Oracle

I would recommend 1) using the upgraded check utility in the new MySQL shell (mysqlsh) and 2) using ALTER TABLE to change the tables, see https://dev.mysql.com/doc/refman/8.0/en/charset-unicode-conversion.html

daniel_hauke

Hi Dave,

thanks for your answer.

But was if i wanted to keep the old mysql database with utf8 characterset and create a new one with utf8mb4 characterset.

Is it possible to do an export and import from the old to the new mysql database? Is the data correctly converted into utf8mb4 with this procedure?

Greetings Daniel

Sunny kichloo

It is better if you use alter command to do this conversion.

Below doc is with respect to 5.5 but it is has desired steps:

https://mathiasbynens.be/notes/mysql-utf8mb4#column-index-length

daniel_hauke

Hi,

but why is that so? Why is it better to do a conversion using alter commands and not doing an export/import.

Also the requirement was to keep the old database without any changes.

Greetings Daniel

Dave Stokes-MySQL Community Team-Oracle

Speed.  Running ALTER TABLE should be orders of magnitude faster that the backup, delete, restore, import process.

daniel_hauke

Hi Dave,

i did some tests. I created a mysql database using utf8.

Then i changed the database, tables and so on to utf8mb4 as described here:

After that i tried to update a column with the following statement:

[email protected] mysqltest1 [mysqltest] SQL> UPDATE mysqltest.table1 SET TEST = 'foo  bar' WHERE counter = 1;

But i still get this error:

ERROR 1366 (HY000): Incorrect string value: '\xF0\x9D\x8C\x86ba...' for column 'TEST' at row 1

Do you have any idea how to fix this?

As you can see my database is using utf8mb4:

SQL> SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

+--------------------------+--------------------+

| Variable_name            | Value              |

+--------------------------+--------------------+

| character_set_client    | utf8              |

| character_set_connection | utf8              |

| character_set_database  | utf8mb4            |

| character_set_filesystem | binary            |

| character_set_results    | utf8              |

| character_set_server    | utf8mb4            |

| character_set_system    | utf8              |

| collation_connection    | utf8_general_ci    |

| collation_database      | utf8mb4_general_ci |

| collation_server        | utf8mb4_general_ci |

+--------------------------+--------------------+

And:

+----------+---------+--------------------+

| database | charset | collation          |

+----------+---------+--------------------+

| mysqltest  | utf8mb4 | utf8mb4_unicode_ci |

+----------+---------+--------------------+

1 row in set (0.00 sec)

Greetings Daniel

Dave Stokes-MySQL Community Team-Oracle

Is your connection UTF8MB4 too?

daniel_hauke

Hi,

i am connecting directly from my mysql server and i inserted the following lline into my my.cnf:

[client]
default-character-set = utf8mb4

Do i have to change any other setting for my client connection?

Edit:

I did an test an issued the following command inside the mysql.

charset utf8mb4

After that the update statement went fine.

So why is the my.cnf parameter ignored ?

Greetings Daniel