java.sql.SQLException: Incorrect string value: ‘\xF0\x9F\x96\xA4\xE2\x9C…’ for column

When your application tries to store 4-byte Unicode characters in a MySQL database you may get the error rmessage

java.sql.SQLException: Incorrect string value: ‘\x..\x..\x..\x..\x..\x…’ for column

 

To store 4-byte Unicode characters in MySQL you need

• a modern version of the database engine, version 5.5 or later,
• set the column collation to COLLATE utf8mb4_unicode_ci in the database,
• configure MySQL to use utf8mb4 in the character columns.

 

To set the default character set and server settings to utf8mb4

1. Open the C:\ProgramData\MySQL\MySQL Server 5.x\my.ini file
2. Under [client] add

   default-character-set = utf8mb4

3. Under [mysql] set the value to utf8mb4 in line

   default-character-set = utf8mb4

4. Under [mysqld] set the value to utf8mb4 in line

   character-set-server = utf8mb4

5. Under [mysqld] add the lines

   character-set-client-handshake = FALSE
   character-set-server = utf8mb4
   collation-server = utf8mb4_unicode_ci

Restart the MySQL server

1. Restart the MySQL database server for the changes to take effect
   
2. in MySQL Workbench reconnect the database.
   

Change the schema of an existing database

1. In MySQL Workbench connect to the database server,
2. Right-click the database and select Alter Schema,
   
3. Set the collation to utf8mb4_unicode_ci collation
   
4. Click the Appy button to save the changes,
   
5. Click the Apply button again to confirm the instructions,
   
6. Click the Finish button to execute the script.
   

To check the database settings execute the SQL query in MySQL Workbench

use MY_DATABASE_NAME;
SHOW VARIABLES WHERE Variable_name LIKE 'character\_set\_%' OR Variable_name LIKE 'collation%';

It should show

+--------------------------+--------------------+
| 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_unicode_ci |
| collation_database       | utf8mb4_unicode_ci |
| collation_server         | utf8mb4_unicode_ci |
+--------------------------+--------------------+

Repair and optimize all tables

Make sure you execute the mysqlcheck command to repair and optimize all tables to avoid silent update errors

On Windows

"C:\Program Files\MySQL\MySQL Server 5.7\bin\mysqlcheck" -u root -p --auto-repair --optimize --all-databases

The utility makes it easy to execute the repair and optimize commands for every table

# For each table
REPAIR TABLE table_name;
OPTIMIZE TABLE table_name;