11

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*/?

1 Answer 1

11

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.

3
  • 7
    From the MySQL documentation: if you add a version number after the ! character, the syntax within the comment is executed only if the MySQL version is greater than or equal to the specified version number meaning the exact opposite of this answers first line is correct.
    – Torque
    Jan 20, 2020 at 9:41
  • Is it a bug? Or perhaps a newer version of mySQL became more backwards compatible? As of 5.7.37, it seems to dump as 40101 and no longer 50503
    – Trip
    Apr 19, 2022 at 12:08
  • @Trip - Character sets were added in 4.1.1, but utf8mb4 was not added until 5.5.3. Does this explain the issues?
    – Rick James
    Apr 19, 2022 at 13:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.