Issue
This document describes how to convert your MySQL database (DB) from the utf8
/utf8mb3
character set to the utf8mb4
.
In this article we use utf8
/utf8mb3
together because utf8
is an alias for utf8mb3
.
It is highly recommended to do this. According to article in MySQL Documentation - https://dev.mysql.com/doc/mysql-g11n-excerpt/8.0/en/charset-unicode-utf8.html.
The utf8mb3 character set is deprecated, and you should expect it to be removed in a future MySQL release.
Please use utf8mb4 instead.
utf8 is currently an alias for utf8mb3 , but it is now deprecated as such, and utf8 is expected subsequently to become a reference to utf8mb4 .
To avoid ambiguity about the meaning of utf8 , consider specifying utf8mb4 explicitly for character set references.
|
Resolution
Changing the Default MySQL Character Set to utf8mb4
The procedures below cover making the utf8mb4
DB versions using mysqldump
.
-
Edit the
client
section of/etc/my.cnf
(the MySQL configuration file) to read:
[client] default-character-set=utf8mb4
-
Edit the
mysql
section in/etc/my.cnf
to read:
[mysql] default-character-set=utf8mb4
-
Edit the
mysqld
section in/etc/my.cnf
to read:
[mysqld] character-set-server=utf8mb4 collation-server=utf8mb4_general_ci init-connect='SET NAMES utf8mb4'
IMPORTANT! After you finish editing the file, restart the MySQL server.
Converting a Database Containing Tables
If your CloudBees CD/RO (CloudBees Flow) server already has a populated MySQL DB and you are getting a warning about deprecation of utf8mb3
, use the following procedures to convert your database.
Linux and MacOS
The following steps show how to create a DB dump, edit the DB dump so that the correct charset and collation are used, and then restore the new DB.
-
Open a terminal window and move to a temporary directory.
-
Dump out the DB:
mysqldump -h db_server_host_name -u username -p password -c -e --single-transaction -B dbname > dump.sql
The arguments in this command clean up the character sets and provide a dump that will not cause problems if you are moving the DB to a different DB server or need to restore the DB on a reverted system. For complete descriptions of these arguments, see https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html.
When you run this command, a DB dump is generated into dump.sql
.
-
Next, create a backup of
dump.sql
by entering:
cp dump.sql dump-fixed.sql
You will modify dump-fixed.sql
and will keep dump.sql
as a backup.
-
Edit the dump file and correct the incorrect character that have been used. You can use any search-and-replace editor or program such as VIM:
sed -i 's/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/' dump-fixed.sql
This command converts all tables to utf8mb4 by replacing all table suffixes with DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci
.
-
Run commands to drop and recreate database with new charset and collation. And to restore the DB structure and data from the dump file:
mysql -h db_server_host_name -u username -p password -e "drop database dbname" mysql -h db_server_host_name -u username -p password -e "create database dbname character set utf8mb4 collate utf8mb4_general_ci" mysql -h db_server_host_name -u username -p password < dump-fixed.sql
Example
mysqldump -h db_server_host_name -u username -p password -c -e --single-transaction -B dbname > dump.sql cp dump.sql dump-fixed.sql sed -i 's/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/' dump-fixed.sql mysql -h db_server_host_name -u username -p password -e "drop database dbname" mysql -h db_server_host_name -u username -p password -e "create database dbname character set utf8mb4 collate utf8mb4_general_ci" mysql -h db_server_host_name -u username -p password < dump-fixed.sql
Windows
The following steps show how to create a DB dump, edit it so that the correct character set (utf8mb4
) and collation (utf8mb4_general_ci
) are used, and then restore the new DB . For this, first download Super Sed (Win32 executable, zipped).
-
Open a command window and move to a temporary folder.
-
Enter:
\bin\mysqldump -h [db_server_host_name] -u [username] -p [password] -c -e --single-transaction -B [dbname] -r dbdump.sql copy dbdump.sql dump-fixed.sql ssed -i "s/) ENGINE=InnoDB.*/) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci;/" dump-fixed.sql [your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] -e "drop database dbname" [your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] -e "create database dbname character set utf8mb4 collate utf8mb4_general_ci" [your mysql dir]\bin\mysql -h [db_server_host_name] -u [username] -p[password] [dbname] < dump-fixed.sql
For complete descriptions of these arguments, see https://dev.mysql.com/doc/refman/8.0/en/mysqldump.html.