Breaking out from the MySQL character-set hell

The MySQL charset hell. Read to know how you fall in it, what to do if you find yourself in it and how to migrate from latin1 to utf8mb4.

Image for post
Image for post

Emergence and Rise of UTF-8

Image for post
Image for post
Shows the usage of the main encodings on the web from 2001 to 2012 as recorded by Google.

MySQL — Collation and Character Set

Latin1 and MySQL

MySQL Character-Set Hell — Reproducing The Problem

/*The commands below will allow your mysql client to act as a misconfigured application*/  SET SESSION CHARACTER_SET_CLIENT = latin1;
SET SESSION CHARACTER_SET_RESULTS = latin1;
SET SESSION CHARACTER_SET_CLIENT = utf8mb4;
SET SESSION CHARACTER_SET_RESULTS = utf8mb4;

Fixing The Problem

[client]
default-character-set = utf8mb4

[mysql]
default-character-set = utf8mb4

[mysqld]
character_set_server=utf8mb4
collation_server=utf8mb4_unicode_ci
#The following should be set if you are using mysql version 5.6 or lower
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
[mysqld]
character_set_client: utf8mb4
character_set_database: utf8mb4
character_set_results: utf8mb4
character_set_connection: utf8mb4
character_set_server: utf8mb4
collation_connection: utf8mb4_unicode_ci
collation_server: utf8mb4_unicode_ci
innodb_file_format=barracuda
innodb_file_per_table=1
innodb_large_prefix=1
mysql -h host -u username -p
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| 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 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.05 sec)
mysqldump --verbose -h host -u username -p --add-drop-database --opt --skip-set-charset --default-character-set=latin1 --skip-extended-insert --databases databasename > dump_file.sql
perl -i -pe 's/DEFAULT CHARACTER SET latin1/DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci/' dump_file.sql
perl -i -pe 's/DEFAULT CHARSET=latin1/DEFAULT CHARSET=utf8mb4 COLLATE utf8mb4_unicode_ci ROW_FORMAT=DYNAMIC/' dump_file.sql
mysql --verbose -h host -u username -p < dump_file.sql
mysqlcheck --verbose -h host -u root -p --auto-repair --optimize --all-databases
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';
+--------------------------+--------------------+
| 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 |
| collation_connection | utf8mb4_general_ci |
| collation_database | utf8mb4_unicode_ci |
| collation_server | utf8mb4_unicode_ci |
+--------------------------+--------------------+
10 rows in set (0.05 sec)
SET NAMES 'utf8mb4';

Nth Encoded Characters

Application Precautions

Conclusion

Sources

A techie by heart. Code to enjoy, like to build stuff, contribute to FOSS and learn new technologies. LFC Fan #YNWA.

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store