java.sql.SQLException: Incorrect string value:, UTF-8 to utf8mb4 MySQL

Alter DB and Table to utf8mb4

 
ALTER DATABASE CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;  
ALTER TABLE UGCList CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;  

Check variables

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

For MySQL configuration, add two lines for utf8mb4

 [mysqld] 
 character-set-server = utf8mb4  
 collation-server = utf8mb4_unicode_ci  

In your program call SQL, use SET NAMES before update or insert

 String set_names = "SET NAMES 'utf8mb4'";  
 ps = con.prepareStatement(set_names);  
 ps.executeUpdate();  

SET NAMES indicates what character set the client will use to send SQL statements to the server. Thus, SET NAMES 'utf8mb4' tells the server, “future incoming messages from this client are in character set utf8mb4.” It also specifies the character set that the server should use for sending results back to the client.

UTF-8 and utf8mb4

There are known issues storing 4byte utf characters in some versions of MySQL. Apparently you must use utf8mb4 to represent 4 byte UTF characters, as the normal utf8 character set can only represent characters up to 3 bytes in length and so can't store character which are outside of the



Restart MySQL

sudo /etc/init.d/mysql restart