Inserting UTF-8 encoded string into UTF-8 encoded mysql table fails with "Incorrect string value"

Viewed 39086

Inserting UTF-8 encoded string into UTF-8 encoded table gives incorrect string value.

PDOException: SQLSTATE[HY000]: General error: 1366 Incorrect string value: '\xF0\x9D\x84\x8E i...' for column 'body_value' at row 1: INSERT INTO

I have a character, in a string that mb_detect_encoding claims is UTF-8 encoded. I try to insert this string into a MySQL table, which is defined as (among other things) DEFAULT CHARSET=utf8

Edit: Drupal always does SET NAMES utf8 with optional COLLATE (atleast when talking to MySQL).

Edit 2: Some more details that appear to be relevant. I grab some text from a PostgreSQL database. I stick it onto an object, use mb_detect_encoding to verify that it's UTF-8, and persist the object to the database, using node_save. So while there is an HTTP request that triggers the import, the data does not come from the browser.

Edit 3: Data is denormalized over two tables:

SELECT character_set_name FROM information_schema.COLUMNS C WHERE table_schema = "[database]" AND table_name IN ("field_data_body", "field_revision_body") AND column_name = "body_value";

>+--------------------+
| character_set_name |
+--------------------+
| utf8               |
| utf8               |
+--------------------+

Edit 4: Is it possible that the character is "to new"? I'm more than a little fuzzy on the relationship between unicode and UTF-8, but this wikipedia article, implies that the character was standardized very recently.

I don't understand how that can fail with "Incorrect string value".

4 Answers

I fixed the error: SQLSTATE[HY000]: General error: 1366 Incorrect string value ...... with this method:

I use utf8mb4_unicode_ci for database database Set utf8mb4_unicode_ci for all tables tables

Set longblog datatype for column (not text, longtext.... you need big datatype to store 4 bytes of your content) fields

It is okay now. If you use laravel, continue to edit config/database.php

'charset' => 'utf8mb4',
'collation' => 'utf8mb4_unicode_ci',

laravel

If you use function strtolower, replace it with mb_strtolower Notice: you have to put <meta charset="utf-8"> on your head tag

Related