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;

MySQL Installation

To install the MySQL database server, download the installer from https://dev.mysql.com/downloads/

  1. Download the MySQL Community Server from https://dev.mysql.com/downloads/
  2. On the Generally Available (GA) Releases tab select the operating system and click the Go to Download Page button,
  3. Click the Download button next to the web or offline installer,
  4. If you don’t want to register at Oracle, click the No thanks, just start my download link,

On Windows

Install the Visual C++ Redistributable Packages for Visual Studio 2013

  1. In your web browser navigate to https://www.microsoft.com/en-us/download/details.aspx?id=40784
  2. Download both 32 and 64-bit packages
  3. Install both packages. The 64-bit package requires a reboot after installation.

Install MySQL

  1. Execute the downloaded MySQL installer.

Failed to open file ‘…’ , error 2 and error 22

How to restore a MySQL database in Windows

To restore a MySQL database on a Windows machine:

  • Place the database backup file in the root directory of your C: drive (trust me, this will make your life easier)
  • Open the Command Prompt
  • Start the MySQL command prompt
  • mysql -u root -p

    This command will prompt you for the MySQL root password

  • Switch to the new database
    use db;
  • Import the database
    source C:/BACKUP_FILE_NAME.sql

    To avoid the Failed to open file ‘…’ , error 2 and error 22 error message
    You can have spaces in the name of the backup file, but not in the directory names in the path
    Do not use quotes around the file path
    Use forward slashes
    Do not end the command with semicolon (;)