MySQL "Incorrect String Value" Error with utf8mb4 encoding

Question

I've been having a problem inserting some data into a varchar field of an InnoDB MySQL table with a PHP script. Initially I thought the problem was caused by using utf8 character encoding because I did some research on the error I was getting, "Incorrect String Value" and found a couple of helpful posts here about switching my tables over to utf8mb4.

So I switched it to 'utf8mb4'. I did this in a couple of different places, in the server settings, the table settings, the database settings, and also in php via mysqli_set_charset. I also tried changing the MySQL setting 'character-set-client-handshake' to see if that would make a difference, but I still got the error even after restarting the MySQL server.

I decided that I would see if my PHP script was the problem so I opened up MySQL Workbench and entered the same query there. Success!

So, now I am really puzzled. Why can I run the query in MySQL workbench but not via PHP? Here is my code.

In MySQL Workbench, this query works:

UPDATE books SET notes = 'Renâe Boivin, jeweller by Cailles, Franðcoise'  
WHERE asin LIKE '0704370905'

This is my PHP code for running that same query:

$dbc = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);
mysqli_set_charset($dbc, 'utf8mb4');
$sql = "UPDATE books SET notes = 'Renâe Boivin, jeweller by Cailles,   
Franðcoise' WHERE asin
LIKE '0704370905'";

That code generates this error:

"Error: Incorrect string value: '\xE2e Boi...' for column 'notes' at row 2"

Any ideas? Maybe I'm just overlooking something really simple. I appreciate any suggestions. Thank you!

What's the character encoding of your PHP file? It'll depend on your editor, but typically (a) under the File menu somewhere, or when you save a file, you'll be able to specify the encoding, and (b) you'll want to use UTF-8, unless there's a very good reason not to. (Your problem is almost certainly that you're not actually sending UTF-8 data to the server because your query isn't saved in UTF-8 in the PHP file itself. MySQL Workbench will be using UTF-8.) — Matt Gibson, Oct 12 '15 at 16:25
Thank you Matt!! Sorry it took me so long to reply. I found the File Encoding setting under the File menu in the editor I'm using (PHP Storm) and it allowed me to convert it to UTF-8. I also was able to do this on the data file that I was trying to process, and this fixed all of the error messages. Yay! I really appreciate your help. — Linnea Huxford, Oct 12 '15 at 23:53
No problem. I've added a real answer now we've confirmed that that was the issue. — Matt Gibson, Oct 13 '15 at 07:04

score 1 · Accepted Answer · answered Oct 13 '15 at 07:04

When you have text in SQL embedded in a PHP file, the encoding of the PHP file itself comes into play. You need to make sure that your PHP file itself is saved in UTF-8. How you do that depends on your editor. For PHP it's also important to choose the "without BOM" (Byte Order Mark) option when saving a unicode file.

score -2 · Answer 2 · edited May 23 '17 at 12:19

Try forcing MySQL to only accept utf8 by using

mysqli_set_charset('utf8');

Then you can try brute-forcing PHP into utf-8

header('Content-Type: text/html; charset=utf-8');

As well as ensuring the string you are sending is of utf-8 encoding. There are many previous StackOverflow questions to do this: PHP: Convert any string to UTF-8 without knowing the original character set, or at least try

MySQL "Incorrect String Value" Error with utf8mb4 encoding

2 Answers2