0

I'm trying to move a site and I'm really baffled with the problems I'm having trying to sort out the encoding issues with the database.

The database on new server is using utf8 and all tables imported have utf8. Checking the exported sql file with 'file db.sql' it also says: UTF-8 Unicode text as well as it has it defined in the sql queries in the sql file.

in php.ini I've set default_charset = "UTF-8" and apache.conf I've set AddDefaultCharset UTF-8 as well have I added to the sites-enabled (regular and https) in Apache: AddDefaultCharset UTF-8

In the HTML/PHP document header I have:

  <meta http-equiv="content-type" content="text/html; charset=UTF-8">

I've checked the PHP documents served by the new web server with 'file' and these as well are: HTML document, UTF-8 Unicode text

Besides all of this, encoding is not correct neither in the mysql client nor when served via the web server. For example, ö becomes ö, å becomes Ã¥ and ä becomes ä. utf8_decode in PHP does display the text properly so it seems to be correct encoding at least?

If someone can give me a hint to where I might have gone wrong it would be greatly appreciated. I'm completely clueless. To me it all seems to have been set to UTF-8 yet these issues persist.

EDIT/add: Response headers when fetching a document from the new server also says: Content-Type text/html; charset=UTF-8

EDIT 2: Stackoverflow suggests that this has already been solved, but I do not find a solution that works from the link provided.

Cœur
  • 37,241
  • 25
  • 195
  • 267
Looren
  • 31
  • 4
  • So, you've mangled your data while re-importing it and your database is now actually storing "ö"…? – deceze Aug 21 '17 at 08:20
  • No, database already has the chars like ö when looking at it in plain text before doing the import to mysql. – Looren Aug 21 '17 at 08:35
  • So your data *is* messed up at the database level. Probably an incorrect export from the old database?! – deceze Aug 21 '17 at 08:36
  • When viewing the database in phpmyadmin at the current mysql server (not new one) the chars are messed up there as well. Although I can not speak for the web server/mysql as the website is on web hosting, I do know that the headers from server and php documents are utf-8. – Looren Aug 21 '17 at 08:40
  • I really have no idea where this went wrong. All DB-text looks fine on the current website. – Looren Aug 21 '17 at 08:40
  • Well, somewhere you *exported* and then *imported* the data, right? Somewhere there you chose the wrong (connection) encoding for the im-/export. – deceze Aug 21 '17 at 08:44
  • Yes, and when doing export I select "UTF-8" as "Character set of the file" in phpmyadmin at the current web hoster although database/tables are already set to UTF-8. But even tho I do select this, when browsing the current database in phpmyadmin at the current provider chars are still messed up. – Looren Aug 21 '17 at 08:50
  • That is, chars are messed up before they are exported. But there is no problem displaying them on the current website. – Looren Aug 21 '17 at 09:11
  • Then you've been using an unsound encoding configuration in your old host. See [Handling Unicode Front To Back In A Web App](http://kunststube.net/frontback/) for a long explanation. See for *a* solution. https://stackoverflow.com/questions/20237474/strange-character-encoding-of-stored-data-old-script-is-showing-them-fine-new/20237671#20237671 – deceze Aug 21 '17 at 09:16
  • Thank you. The SQL query for conversion to utf8 unveiled the problem. Apparently the charset is latin1 even tho I've strictly set it to utf8. Very odd that they would do this. Now i'll just have to find a way to do the CONVERT(BINARY CONVERT to the whole database. Thanks again – Looren Aug 21 '17 at 09:26
  • See Mojibake in http://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for an explanation of what led to the garbage. – Rick James Aug 22 '17 at 04:51
  • You cannot say for sure what is the table "already has" without using `SELECT HEX(...)`. Otherwise you are at the mercy of various processes trying to "help" you. – Rick James Aug 22 '17 at 04:53

0 Answers0