0

I have a mysql-front and a database set up by a previous coworker/partner and I know almost nothing about managing the database.

It worked fine with å ä ö for years but then my web hotel has moved the database a number of times and I think it was one of those times that thousands of å ä and ö started looking like Ã¥ ä and ö both when viewing in mysqlfront and on the website. Also ü and other special characters are shown wrong.

If I (edit: right click a column in the object browser it says "charachter set Latin1 and Collation Latin1_swedish_ci whatever that means) I (unwisely enough) changed another column to Swe7-Swedish and it messed up that specific column more and turned all å ä and ö into ??, ?? and ?? so that column is lost forever i guess?

Is there anything I can do to turn the rest of the database back to å ä and ö that could be explained in a way that a person who knows very little can do it?

Thank you.

Rick James
  • 135,179
  • 13
  • 127
  • 222
David
  • 11
  • 1
  • 3
  • `latin1` is certainly not a unicode-capable encoding. – zerkms Apr 20 '17 at 22:31
  • ok. I don't know how the change to latin happened but is there a way to get it back? – David Apr 20 '17 at 23:10
  • Hopefully you have backups. You will need to set the collation to utf8_unicode_ci for all tables and columns. But if the character information has been lost, you may not be able to reinstate it. Make a backup before you try anything, and change a column to that encoding to see if it works. If not, restore the backup. – Sloan Thrasher Apr 20 '17 at 23:30
  • Thank you. There are no backups from before that as far as I know and I will try to do what you say at some point (backup and change that back) and hopefully the information is still there in most tables, because it is different signs showing for different "missing letters". However I don't have experience in backups or most things but at some point i will prioritize to learn. – David Apr 23 '17 at 12:07

1 Answers1

1

Mojibake. Look for that term here .

  • The bytes to be stored need to be UTF-8-encoded. Fix this.
  • The connection when INSERTing and SELECTing text needs to specify utf8 or utf8mb4. Fix this.
  • The column needs to be declared CHARACTER SET utf8 (or utf8mb4). Fix this.
  • HTML should start with <meta charset=UTF-8>.

If the data needs fixing, see this .

Please provide SHOW CREATE TABLE and the connection parameters and SELECT HEX(col)...

å in HEX is

E5 for latin1
C3A5 for utf8 (or utf8mb4)
C383C2A5 for "double encoding"
Community
  • 1
  • 1
Rick James
  • 135,179
  • 13
  • 127
  • 222
  • Thank you, I do really appreciate the answer. However I have extremely basic experience with mysqlfront, basically just roaming around and changing text here and there and adding columns by trial and error, so I am not at the level where I yet know how to do the things you say. I will either learn or try to hire someone i can trust for web issues as soon as possible and let them look a this. – David Apr 23 '17 at 12:11
  • @David - If it is any consolation, character set issues trip up even the professionals. Good luck. – Rick James Apr 23 '17 at 15:44
  • I ended up here with a completely different issue with the same underlying misconception. I was using BeautifulSoup to modify then render something in selenium. Your mention of 'double encoding' pointed me in the right direction, to '.decode('utf-8')' my apparently double-encoded strings. True comment about character encoding tripping up even professionals ;P – Jack Davidson Jan 27 '19 at 23:43
  • @JackDavidson - Double encoding is sneaky. Text goes in OK, and comes out OK. But it is wrongly encoded in the table. Screwed up comparisons and `SELECT HEX` are among the few ways it shows its ugly head. – Rick James Jan 28 '19 at 00:39