HiveBrain v1.2.0
Get Started
← Back to all entries
snippetsqlModerate

How to make mysqldump use /*!40101 SET character_set_client = utf8mb4 */;?

Submitted by: @import:stackexchange-dba··
0
Viewed 0 times
utf8mb4mysqldumpmakehowuse40101setcharacter_set_client

Problem

When I use mysqldump to export mysql database, it always produce a dump.sql containing

...some other things...
/*!40101 SET character_set_client = utf8*/;
...some other things...


This is the mysqldump command I use:

mysqldump -u root -p databaseName -R -E --single-transaction --default-character-set=utf8mb4 > dump.sql


The charset of the mysql database is utf8mb4 rather than utf8, the characters related variables is:

+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8mb4 |
| character_set_connection | utf8mb4 |
| character_set_database | utf8mb4 |
| character_set_filesystem | binary |
| character_set_results | utf8mb4 |
| character_set_server | utf8mb4 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
| collation_connection | utf8mb4_unicode_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+----------------------------+


Why does mysqldump always add
/!40101 SET character_set_client = utf8/ rather than
/!40101 SET character_set_client = utf8mb4/?

What happens if /!40101 SET character_set_client = utf8/ is used?

Can we have mysqldump use /!40101 SET character_set_client = utf8mb4/?

Solution

That special type of comment says "leave this as a comment if running a version older than 4.1.1; else execute it".

utf8mb4 came in in 5.5.3, so the 'correct' version would be

/*!50503 SET character_set_client = utf8mb4*/


If your version of mysqldump and mysql are newer than 5.5.3, no harm is done by leaving it 40101. If you do try to load it on a mysql older than 5.5.3, the SET will probably complain.

As for why mysqldump does not say 50503 -- I suspect that is a bug. File it at http://bugs.mysql.com.

Code Snippets

/*!50503 SET character_set_client = utf8mb4*/

Context

StackExchange Database Administrators Q#137620, answer score: 11

Revisions (0)

No revisions yet.