Wordpress site: Random Character Coding error

Question

I have a Wordpress site which randomly shows an error like:

WordPress database error: [Incorrect string value: '\xF0\x9F\x92\x97\xF0\x9F...' for    column 'option_value' at row 1] UPDATE `wp_options` SET `option_value` = 'a:95:{

This is a placeholder site; the data was exported from the existing live using PhpMyAdmin into a .sql file. I suspect there is some kind of character encoding issues? I have exported by whatever the 'default may have been as well as UTF8. In the imported database I have tried both character encoding and collation as UTF8 (which it is currently) as well as 'Latin1 ISO8899 Western Europe' and 'utf_general_ci' as encoding and collation, respectively, but no luck.

I notice that the error happens when I go to the site after an interval.

What can I do? Thanks!

score 2 · Accepted Answer · answered Aug 07 '12 at 06:56

2

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:

answered Aug 07 '12 at 06:56

Mathias Bynens

144,855
52
216
248

Mathias, yes, the upgrading of MySQL Server was on my mind but then we found out that one of the Word Press Plugin (something to do with tracking users) was the only one causing problems. We removed that and the problem went away. Your 'answer' still seems like a very good option for our needs. Thanks! – IrfanClemson Aug 07 '12 at 12:33

score 1 · Answer 2 · answered Jun 30 '12 at 17:37

1

The problem could be an encoding mismatch. utf8 can store characters up to 3 bytes, utf8mb4 can store characters up to 4 bytes. I guess the data in your .sql file contains a rare symbol unsupported by utf8. Try importing the .sql file anew, but set encoding to utf8mb4. I had the same problem in Joomla some time ago.

answered Jun 30 '12 at 17:37

afaf12

5,163
9
35
58

Ok. Trying if that is an option... Thanks! – IrfanClemson Jun 30 '12 at 17:40
Not an option. Here is the DDL: CREATE DATABASE `XXXXXXX` CHARACTER SET 'utf8' COLLATE 'utf8_general_ci'; (May be I can do an 'alter' statement to try UTf8mb4. Or may be try to change the 'option_value' column from Longtext to something else? Thanks. – IrfanClemson Jun 30 '12 at 17:43
I would try changing it to ...CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_general_ci'. – afaf12 Jun 30 '12 at 17:53

score 0 · Answer 3 · answered Nov 25 '12 at 17:03

I encountered the same problem today.
After tried many times, I found out the reason and fix it at last.
For applications that store data using the default MySQL character set and collation (latin1, latin1_swedish_ci), so you need to specify the character set and collation to utf8/utf8_general_ci when your create your database or table.
e.g.:
        $sql = "CREATE TABLE " . $table_name . " (
        id mediumint(9) NOT NULL AUTO_INCREMENT,
        bookname varchar(128) NOT NULL,
        author varchar(64) NOT NULL,
        PRIMARY KEY  (id),
        KEY (bookname)
        )CHARACTER SET utf8 COLLATE utf8_general_ci;";

Reference:
《mysql create table problem? SOLVED!!!!!!!!!!!》
http://forums.mysql.com/read.php?121,193883,193883
《10.1.5. Configuring the Character Set and Collation for Applications》
http://dev.mysql.com/doc/refman/5.0/en/charset-applications.html

Hoping this can help you.

Wordpress site: Random Character Coding error

3 Answers3