Weird characters in MySQL database

[DennisHermannsen]

Hi,

We're currently working on upgrading our WHMCS 5.3 installation to 7.6, but we're running into some issues with the database. Characters such as æ, ø and å are shown as weird characters: https://screenshots.firefox.com/eef575Q4TAx6gmqo/chosting.dk

I've tried everything I can possibly think of, including this: https://billing.holodyn.com/knowledgebase/61/Converting-a-Database-from-latin1-to-UTF-8.html

 

I'm even exporting and importing on the same server.

Does anyone have a clue on what's going on?

[yggdrasil]

What happens if you change the encoding in your browser? Does it show correctly? 

If yes, the problem is on the encoding of your files (templates). If you changed the database to UTF-8, something which is not actually necessarily but recommended for multilingual, make sure your files are also UTF-8.

Check your templates first and see how they are encoded.

[DennisHermannsen]

The weird characters are present in the database - not just on the website.

[DennisHermannsen]

I've just verified that the .tpl files are encoded using UTF8.

The weird thing is that the weird characters are also present in the old database, but they display fine on the old website. Any ideas?

[yggdrasil]

32 minutes ago, DennisMidjord said:

I've just verified that the .tpl files are encoded using UTF8.

The weird thing is that the weird characters are also present in the old database, but they display fine on the old website. Any ideas?

Yes. If the characters are displayed in the old database as well, but was displaying fine on your site, that means the encoding was different on the website files. Check the old templates encoding, maybe you had them to Windows Encoding and the new ones are UTF-8?

Edited August 22, 2018 by yggdrasil

[brian!]

what happens if you create a new kb article containing Danish (I assume) characters? does that show correctly, or are the characters in that new article weird too?

[DennisHermannsen]

5 minutes ago, yggdrasil said:

Yes. If the characters are displayed in the old database as well, but was displaying fine on your site, that means the encoding was different on the website files. Check the old templates encoding, maybe you had them to Windows Encoding and the new ones are UTF-8?

I've just checked. They're in UTF-8 as well.

4 minutes ago, brian! said:

what happens if you create a new kb article containing Danish (I assume) characters? does that show correctly, or are the characters in that new article weird too?

That works fine: https://screenshots.firefox.com/1EBXwpk8TRp3gHY7/chosting.dk (the last one).

I can also rename the 'broken' articles, categories etc. to contain the proper UTF 8 character.

[yggdrasil]

Let me get this correctly. Your MySQL/MariaDB database is now on UTF-8. Your files are also on UTF-8. If you create a new articles or news it displays correct but the old data does not. Is this information correct?

Edited August 22, 2018 by yggdrasil

[DennisHermannsen]

Hi!

The database is still encoded in latin1_swedish_ci. I've found the following solution to my problem: https://stackoverflow.com/a/12756911

I've heard from someone that utf8 should be changed to utf8mb4. Is that correct?

[yggdrasil]

I would not change the database encoding if it was working before. Its advised to use UTF-8 for databases with strange characters but that is a suggestion if you are starting with a new database. That is not your situation here. If you already where using latin1 and it was working, don't touch it.

If the new data you are saving with WHMCS displays fine, that is one more reason not to change it because it means its working correctly. Its just the old data that is corrupted and this is easy to fix. Use any database manager, like phpmyadmin or adminer and just do a system wide search and replace operation for every broken character on all tables. Make sure to do this for each broken character, then replace it with the correct one. Now your data will display fine again.

That would be the quickest and easiest solution. It will just take you a few minutes and you are not touching anything regarding encoding. Neither on files, neither on your database.

What ever approach you go, make sure to make a backup before any database operation or change.

Edited August 22, 2018 by yggdrasil

[DennisHermannsen]

Hi @yggdrasil

Thanks for the info!

I tried search and replace for a start, but that was way too time consuming. It's also displaying incorrect characters for the end of a <strong> HTML tag and such.

This might be a stupid question, but what could go wrong by changing the encoding of the tables? By default, tables are encoded in utf8 now.

[yggdrasil]

18 hours ago, DennisMidjord said:

Hi @yggdrasil

Thanks for the info!

I tried search and replace for a start, but that was way too time consuming. It's also displaying incorrect characters for the end of a <strong> HTML tag and such.

This might be a stupid question, but what could go wrong by changing the encoding of the tables? By default, tables are encoded in utf8 now.

No, you don't have to replace every single letter manually. That would be indeed time consuming. You just do this on the whole database at once.

Example, if the correct character is ø but it shows as ó, you would just search and replace all ó for ø.

It does not mean you have to do this one by one. You only have 3 different characters in Danish or Norwegian, so you will do this search and replace only 3 times. Just 5 minutes tops.