TranslatePress encountered SQL error: Illegal mix of collations

Hi there,

A) Does it affect my plugin, since I’m NOT using “Automatic Translation” (I only have the free version, so far)?
It shouldn’t affect it. I took a quick look at your site and the translations seemed to work fine. Also, you can still use Automatic Translation with the free version as long as you use Google Translate.

B) How can I solve and correct this error?
The SQL errors detected can occur for various reasons including missing tables, missing permissions for the SQL user to create tables or perform other operations, problems after a site migration or changes to SQL server configuration.

What you can do in this situation:

Plan A.
Go to Settings -> TranslatePress -> General tab and Save Settings. This will regenerate the tables using your current SQL settings.
Check if no more errors occur while browsing your website in a translated language. Look at the timestamps of the errors to make sure you are not seeing the old errors. Only the most recent 5 errors are displayed.

Plan B.
If your problem isn’t solved, try the following steps:
1. Create a backup of your database
2. Create a copy of each translation table where you encounter errors. You can copy the table within the same database (trp_dictionary_en_us_es_es_COPY for example) — perform this step only if you want to keep the current translations
3. Remove the trouble tables by executing the DROP function on them
4. Go to Settings -> TranslatePress -> General tab and Save Settings. This will regenerate the tables using your current SQL server.
5. Copy the relevant content from the duplicated tables (trp_dictionary_en_us_es_es_COPY for example) in the newly generated table (trp_dictionary_en_us_es_es) — perform this step only if you want to keep the current translations
6. Test it to see if everything is working. If something went wrong, you can restore the backup that you’ve made at the first step.
Check if no more errors occur while browsing your website in a translated language. Look at the timestamps of the errors to make sure you are not seeing the old errors. Only the most recent 5 errors are displayed.

Plan C.
If your problem still isn’t solved, try asking your hosting about your errors. The most common issue is missing permissions for the SQL user, such as the Create Tables permission.

Best regards,

Thank you, Cristiandraghia.

I have tried plan “A” several times, the error still occurs every day, listing 5 errors whose timestamp is always different from the 5 of the day before.
Today it’s:

Logged errors
These are the most recent 5 errors logged by TranslatePress:

1
last_error Illegal mix of collations for operation ‘ IN ‘
disable_automatic_translations Yes
details Error running get_string_rows()
date_time 2020-11-02 11:56:43
timestamp 1604318203
2
last_error Illegal mix of collations for operation ‘ IN ‘
disable_automatic_translations Yes
details Error running get_string_rows()
date_time 2020-11-02 10:43:32
timestamp 1604313812
3
last_error Illegal mix of collations for operation ‘ IN ‘
disable_automatic_translations Yes
details Error running get_string_rows()
date_time 2020-11-02 10:43:14
timestamp 1604313794
4
last_error Illegal mix of collations for operation ‘ IN ‘
disable_automatic_translations Yes
details Error running get_string_rows()
date_time 2020-11-02 10:06:33
timestamp 1604311593
5
last_error Illegal mix of collations for operation ‘ IN ‘
disable_automatic_translations Yes
details Error running get_string_rows()
date_time 2020-11-02 07:57:19
timestamp 1604303839

So to plan “B”:

“2. Create a copy of each translation table where you encounter errors.”

Sorry if this is a stupid question, but how do I find the tables with errors?
Actually I don’t encounter any errors. Not on the website and not (at least not obviously enough for me) in phpMyAdmin. There’s just this error note every day in the top of the backend (Admin): “TranslatePress encountered SQL errors. Check out the errors.” Otherwise I wouldn’t notice anything.

So what am I looking for (and where)?

But now browsing through the tables (in “wp_trp_dictionary_en_us_fr_fr”) I find quite many tables with “original_id” = “Null“. Is that possibly an error?

I will also contact my host for plan “C”…

Hi again,

It looks like the error happens when a query is done on the “trp_dictionary” and not on the “trp_gettext”, when Translation Editor is accessed.

In plan B you pretty much have to delete all tables and then you have to “Save Setting” so that the tabels will be created again in the actual collation.

As said before, if something goes wrong, you can restore the backup you made previously.

Best regards,

Thank you Cristian, I seem to have fixed it. Wasn’t exactly as you suggested, but your advice was more than helpful.

The actual solution was very simple.

What I did was this:

In phpMyAdmin I had 5 tables of the “transpatepress” plugin:

wp_trp_dictionary_en_us_fr_fr
wp_trp_gettext_en_us
wp_trp_gettext_fr_fr
wp_trp_original_meta
wp_trp_original strings

Then I clicked on each of them and then clicked on “Operations”.
There I changed the collation to “utf8mb4_unicode_520_ci” and pressed “Go”.
I did this for all the 5 tables.

After that the error in the backend still appeared for about 1 more day, but only showed errors from the previous day. After 2 days no error was shown anymore.

I don’t know if that is the proper way to solve it, but it worked when nothing else seemed to work. Please comment, if anyone wants to add information or correct me.

Thanks

Hi again,

I’m happy to hear that you solved it.
Thanks for writing the way you fixed it, this might prove useful for others who face a similar problem.

Best regards,
Cristian

Hi everyone! Thanks for your ideas.
I have the same problem and as Giraffe75 suggested I see that the collation is already “utf8mb4_unicode_ci” and I don’t know how to solve this.
Any other ideas? ??