Mysql – Error 1366: Incorrect string value: ‘\xF0\x9F\x98\x8A\x0AC…’ for column ‘text_body’ at row 1

I’ve recently written a detailed guide on how to switch from MySQL’s utf8 to utf8mb4. If you follow the steps there, everything should work correctly. Here are direct links to each individual step in the process:

• Step 1: Create a backup
• Step 2: Upgrade the MySQL server
• Step 3: Modify databases, tables, and columns
• Step 4: Check the maximum length of columns and index keys
• Step 5: Modify connection, client, and server character sets
• Step 6: Repair and optimize all tables

I suspect that your problem can be solved by following step 5. Hope this helps!

Since nobody answer the question and I finally figured it out. I'll answer my own question and hope it may help someone later.

Data is being inserted by stored procedure, alter dababase, table charset and collations will not override charset and collation setting in stored procedure, nor the changes in my.cnf.

After all the database chartsets, collations are correct, drop and reinsert the stored procedure. Before drop, the procedure was like this:

| MAILSERVER | sp_getDataForNewReward         | PROCEDURE | root@localhost | 2014-05-19 23:08:38 | 2014-05-19 23:08:38 | DEFINER       |         | utf8                 | utf8_general_ci      | latin1_swedish_ci  |

After drop and reinsert, it become this:

| MAILSERVER | sp_insertDeliveredEmail        | PROCEDURE | root@localhost | 2015-01-15 23:38:24 | 2015-01-15 23:38:24 | DEFINER       |         | utf8mb4              | utf8mb4_unicode_ci   | utf8mb4_unicode_ci |

Then the procedure will work properly. When stored procedure being created, it will get the charset and collation from the database setting, so after database setting, it need a "refresh".