Collation problem

Support
1

What issue or error are you experiencing?

I am exporting a wordpress database using Wp Migrate plugin. The site looks good on production, but when I import the database into my local wordpress site, using local (off course) some spanish characters that are supposed to be shown with an accent, are “broken”, meaning that I see some other characters.

I think this is a collation issue. I am importing the database by going to the Adminer that local provides, droping all the tables, and importing the sql file. When I check the table information, I can already see that the characters are “corrupted” on the database.

production (looks good)
image

local development with local (accent is “broken”)
image

mysql database is using utf8_general_ci which I think is fine.

image
image740×335 19 KB

I don’t understand what is the issue.

System Details

  • Local Version:
    7.1.2+6410

  • Operating System (OS) and OS version:
    Windows 10

Local Logs

Attach your Local Logs here (Help Doc - Retrieving Local’s Log)

Security Reminder
Local does a pretty good job of scrubbing private info from the logs and the errors it produces, however there’s always the possibility that something private can come through. Because these are public forums, always review the screenshots you are sharing to make sure there isn’t private info like passwords being displayed.

2

Hi @brfignoni

Does the Local MySQL version match the live site?

As a quick fix, you could try running some Search and Replaces to get things fixed up. Something like this might work for example:

wp search-replace '•' '•' --all-tables &&
wp search-replace "’" "'" --all-tables &&
wp search-replace '“' '"' --all-tables &&
wp search-replace 'â€"' '-' --all-tables &&
wp search-replace '…' '…' --all-tables &&
wp search-replace 'â€' '"' --all-tables &&
wp search-replace '×' '×' --all-tables &&
wp search-replace 'ø' 'ø' --all-tables &&
wp search-replace '©' '©' --all-tables &&
wp search-replace 'Â' '' --all-tables &&
wp search-replace 'Å«' 'ū' --all-tables &&
wp search-replace 'â„¢' '™' --all-tables &&
wp search-replace "é" "é" --all-tables &&
wp search-replace "è" "è" --all-tables &&
wp search-replace "ö" "ö" --all-tables &&
wp search-replace "ä" "ä" --all-tables &&
wp search-replace "Ã¥" "å" --all-tables &&
wp search-replace "É" "é" --all-tables &&
wp search-replace "À‰" "é" --all-tables &&
wp search-replace "à§" "ç" --all-tables &&
wp search-replace "§" "ç" --all-tables &&
wp search-replace "àª" "ê" --all-tables &&
wp search-replace "à®" "î" --all-tables &&
wp search-replace "à¢" "â" --all-tables &&
wp search-replace "àç" "ç" --all-tables &&
wp search-replace "Ã" "à" --all-tables

This resource has been shared around the forums before and might be helpful for further insight and troubleshooting as well:

https://codex.wordpress.org/Converting_Database_Character_Sets

3

@Nick-B thanks for your quick answer.

Not only my MySQL versions do not match, but I just realized the production site uses a different collation.

producttion: latn1_swedish_ci
local: utf8_general_ci

production

image
image471×571 12.1 KB

local
image

I haven’t tried yet. But I bet using the same collation as production fixes this issue.
I will update the status here once I try.

Thanks!

4

Sounds great @brfignoni! Hopefully that does the trick. Thank you for the update!

5

This topic was automatically closed 90 days after the last reply. New replies are no longer allowed.