Converting Database Character Sets Incorrect String Issue

  • Resolved Steven

    (@shyzer)


    6 years ago

    I have a very old WordPress database with a lot of latin1_swedish_ci tables collations. Using this codex guide, I properly changed most tables with the 3-step SQL method on both my live and localhost setup.

    However, a few tables throw similar error that I can’t figure out. When attempting to execute this:

    alter table wp_options change option_value option_value LONGTEXT character set latin1;
alter table wp_options change option_value option_value LONGBLOB;
alter table wp_options change option_value option_value LONGTEXT character set utf8mb4;

    I get an error:

    alter table wp_options change option_value option_value LONGTEXT character set utf8mb4

    #1366 – Incorrect string value: ‘\x92s Fre…’ for column ‘option_value’ at row 158

    How exactly do you find either the incorrect sting or row 158?

Viewing 7 replies - 1 through 7 (of 7 total)
  • Moderator James Huff

    (@macmanx)

    The easiest way to track that down is to use phpMyAdmin, typically found in your hosting account’s control panel.

    That will give you a graphical way to browse your database, where you’ll simply head over to the 158th row of wp_options table, or just search for \x92s Fre…

    Thread Starter Steven

    (@shyzer)

    Hey James, thanks for the reply! Unfortunately, I tried both of those methods earlier and come up empty.

    My phpMyAdmin doesn’t list rows, so I’ve tried using both the default sorting method and then sorting by option_value. In both instances, line 158 doesn’t appear to have any incorrect strings.

    When I search for the string, nothing is returned. I’ve tried searching without the slash and even just “Fre” but nothing shows up.

    • This reply was modified 6 years ago by Steven.
    Moderator James Huff

    (@macmanx)

    Hm, you might need to double-check your search parameters, it looks like the query in your screenshot only searched 24 rows.

    If that still doesn’t work, try the steps at https://pento.net/2014/04/07/wordpress-and-utf-8/ instead. That worked fine for me 4 years ago.

    Thread Starter Steven

    (@shyzer)

    You’re right! I thought my search worked in real time, but now I see the “filter rows” only filters the visible 25 rows on screen as opposed to all of them.

    Would you have any tips on refining the search parameters? I tried %x92% with 0 results. A similar search of %fre% produces 286 rows, but x92 doesn’t appear in any of them.

    Moderator James Huff

    (@macmanx)

    No luck with just \x92s?

    Dion

    (@diondesigns)

    Assuming you have already changed the collation to uft8mb4_unicode_ci, a much better way to convert the character set is via the following query:

    UPDATE wp_options SET option_value = CONVERT(CONVERT(CONVERT(option_value USING latin1) USING binary) USING utf8);

    Please make a backup of the wp_options table before running the query!

    Thread Starter Steven

    (@shyzer)

    No luck with just \x92s

    Unfortunately not.

    However, the method you provided worked for me! I now have all my tables in InnoDB utf8mb4_unicode_ci. I really wish that page was listed as a resource on the codex!

    One final question: my database default storage engine is still set to MyISAM latin1. Is the only way to change that in my.cnf?

    • This reply was modified 6 years ago by Steven.
Viewing 7 replies - 1 through 7 (of 7 total)
  • The topic ‘Converting Database Character Sets Incorrect String Issue’ is closed to new replies.