Question 269006 in Wordpress Question posted in
The official Wordpress documentation can be found here.

WordPress created Collation Mess and is now unable to read it since MySQL8?

I am kind of a noob when it comes to coding. I can do some basic PHP and HTML – and that’s how far it goes.

I have a wordpress site sind 2009 and in the time being it created a real mess in the collations. I got:

latin1_swedish_ci (never had word swedish on my site? Everything is in german)
latin1_german2_ci
utf8mb3_general_ci
utf8mb3_unicode_ci
and even some
ascii_general_ci

Now everything was fine until MySQL8 came along.

Since the Umlauts are now broken I looked into my database – and saw that they are also stored "wrong". I guess my wordpress sent UTF-8 into some latin1 table.

But until now everything displayed fine.

So I tried to update the whole database collation – which I recognized does not influence the single table collation.

And when I try to change a single table collation (my idea is to make them all utf-8_mb4_general_ci – it gives me a warning that charakters may be changed – and can not be reverted.

So I am little hesitant how to proceed from here. And unsure. Will converting the collations in all tables solve the problem?

I can add a:

define('DB_CHARSET', 'utf8');
define('DB_COLLATE', '');

to my wpconfig

But as I have more than 1.000 posts on the website I got a horror to have to do it all manually afterwards because I screwed up.

Also there are 241 tables in the database, so it may take some time do them all by one.

My goal is to display the umlauts äöüß and so on correctly in my WP front- and backend again.

Thanks for any ideas and help! 🙂

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    I found a working solution for now (with the help of my hosters support - they were really kind and helpful):

    We changed in my wp-config

    define('DB_CHARSET', 'utf8');

    to

    define('DB_CHARSET', 'latin1');

    and that did the trick for now. My site works again and the umlauts are displaying correctly.

    So now I can take some time and "recode" a clone of my database to UTF-8 - as I think this will be more future - proof.

    Thanks for all the help! :-)

    Greetings,

    André


  2. 0

    I found this "Converting Database Character Sets" from the WordPress Codex. It appears to be happening for sites that are older than 2010 when this was written. It seems to describe what you’d need to do to fix your data to get proper utf8 support.

    A key section stands out:

    Every site is different when it comes to database charset conversions
    […]
    If you are reading this now then you will probably have to do this by hand, as most of the plugins written for the purpose have stopped working in the years since WP changed its default behavior.

    and this is a quick summary of the technique the suggest:

    Converting columns to blob, then back to original format with new charset
    […]
    The solution is to first convert all text-containing fields to their binary ("BLOB") counterpart data-type (which have no charset), then alter them back to their normal data-type along with the desired new character set.

    You may also be interested in this other discussion "Please explain how WordPress works with MySQL character set and collation at a low level" and the replies. It mentions a system that WP uses to detect what character set to use. That may have been what malfunctioned.

    It would also seem you’re not the only person to run into this kind of thing with MYSQL 8.

    Good luck! I found this while trying to help someone else with weird character encodings that occurred on their older posts, not specifically for UTF8 support, but it seems to be the same rough problem.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search