On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,632 Users
  • 2,269,769 Discussions
  • 7,916,800 Comments

Discussions

Best way to convert MySQL 5.7.29 from utf8 to utf8mb4

daniel_hauke
daniel_hauke Member Posts: 211 Bronze Badge
edited Mar 17, 2020 3:14AM in MySQL Community Space

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

Answers

  • Dave Stokes-MySQL Community Team-Oracle
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Feb 20, 2020 1:06PM

    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

    Dave Stokes

    MySQL Community Manager

    daniel_hauke
  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Feb 24, 2020 2:52AM

    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
    Sunny kichloo BangaloreMember Posts: 2,460 Gold Trophy
    edited Feb 25, 2020 12:27AM

    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
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Mar 9, 2020 8:09AM

    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
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Mar 9, 2020 11:25AM

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

    Dave Stokes

    MySQL Community Manager

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Mar 13, 2020 8:23AM

    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
    Dave Stokes-MySQL Community Team-Oracle MySQL Community Manager TexasMember Posts: 374 Employee
    edited Mar 13, 2020 11:22AM

    Is your connection UTF8MB4 too?

    Dave Stokes

    MySQL Community Manager

  • daniel_hauke
    daniel_hauke Member Posts: 211 Bronze Badge
    edited Mar 17, 2020 3:14AM

    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