SQL Collation Not Displaying Correctly

Question

I've asked this question before, but the post was marked as duplicate to this question and deleted.

I've read the post intensively and tried all the things suggested. Unfortunately the post didn't solve the problem I'm currently having. The notice on the deleted question referred me to asking a new question.

Original question:

I recently moved to a different hosting provider with the database backup of the previous provider.

Somehow the database values are not displaying correctly anymore.

For example, different languages like 信長の野望・創造パワーアップキット will output as ä¿¡é•·ã®é‡Žæœ›ãƒ»å‰µé€ .

Trademark icons in titles such as ™ will be displayed as â„¢.

What can I do to fix this? Considering the database is 1:1, I have no idea what the problem is here.

What I've done

My whole database is set to latin1_swedish_ci. I've changed it to utf8mb4_unicode_ci. This didn't make any changes.
I've specified charset=utf8mb4 in the PDO mysql connection DSN.
I've set the default charset to UTF8.

Is there anything I've missed/can do to fix this?

Small update: uploading the database to my local database does seem to output them correctly, which is a 1:1 installation. This is very frustrating.

Webpage has UTF8 encoding tag.
PHP info shows webpage is displayed in UTF8.

31-7-2020:

Running query

SHOW SESSION VARIABLES LIKE 'character_set_%'; SHOW SESSION VARIABLES LIKE 'collation_%';

gave me the following results:

"character_set_client"  "utf8mb4"
"character_set_connection"  "utf8mb4"
"character_set_database"    "utf8mb4"
"character_set_filesystem"  "binary"
"character_set_results" "utf8mb4"
"character_set_server"  "utf8mb4"
"character_set_system"  "utf8"
"character_sets_dir"    "/usr/share/mysql/charsets/"

"collation_connection"  "utf8mb4_general_ci"
"collation_database"    "utf8mb4_unicode_ci"
"collation_server"  "utf8mb4_unicode_ci"

Running query SET character_set_results = NULL; SET character_set_results = binary; didn't gave any results.

in which client are you looking at the result set? your application or mysql console? — K4M, Jul 29 '20 at 00:57
Is there [anything here](https://stackoverflow.com/a/279279/7644018) that may be helpful or related? — Paul T., Jul 29 '20 at 01:06
When you say "the webpage", if you mean your web application, then you need to consider whole other things like page encoding in your HTML page for example. I mean the whole pipeline from the database to your web app. See if you can read the records correctly using a database client that supports UTF8. That can tell you if the problem is with the database or the web app. If that's not what meant, then please clarify. — K4M, Jul 30 '20 at 00:17
@K4M The weird thing is here, it worked on my previous installation (from the hosting where I've moved from), and on my localhost installation. It just outputs these weird characters on my new hosting installation. The webpage is set to display content just like the previous installation, in UTF8. PHP is also set to charset UTF8. — Appel Flap, Jul 30 '20 at 15:44
Looking at all the other things on this question as well as the answers to the question you linked, I'm pretty sure my answer is what you're looking for – plainly because of a process of elimination, and I've also been stuck on this exact problem myself. Let me know if you have questions, and if my answer helps or not. :) — Labu, Jul 31 '20 at 15:14

K4M · Answer 1 · 2020-07-31T18:06:03.823

5

Changing the collation or character set in database does NOT change existing column & tables collation settings. Similarly, changing character set of a column does NOT change the existing data in the columns. See what each column is set to use. Start from there.

select COLUMN_NAME, CHARACTER_SET_NAME, COLLATION_NAME from information_schema.columns
In your mysql client, run the following to see what's the connection settings are

SHOW SESSION VARIABLES LIKE 'character_set_%'; SHOW SESSION VARIABLES LIKE 'collation_%';
In your mysql client, you can ask server not to do any conversion and see if that helps (i.e. do a SELECT on the table and see how it looks).

SET character_set_results = NULL; SET character_set_results = binary;

See here for more details

https://dev.mysql.com/doc/refman/5.7/en/charset-connection.html

edited Jul 31 '20 at 18:06

answered Jul 30 '20 at 17:39

K4M

1,030
3
11

Thanks for your answer. `CHARACTER_SET_NAME` and `CHARACTER_NAME` are all set to `utf8_general_ci`, what would be the way to proceed with this info? – Appel Flap Jul 30 '20 at 17:55
Have you checked the data though a mysql client? do they look okay through mysql client? – K4M Jul 30 '20 at 18:57
Yes, the data looks correct. I've used the same database on my localhost installation where it is being displayed correctly. – Appel Flap Jul 30 '20 at 19:59
You need to do the same with the new SQL installation, i.e. connect with mysql (not through your web app) to the new SQL and see if mysql clients show the data correctly. – K4M Jul 30 '20 at 20:10
I've tested it with HeidiSQL, languages with special characters do appear as described in my question, but that's also for the webversion of mySQL. – Appel Flap Jul 30 '20 at 20:57
so, just to make: you connected HeidiSQL to connect to your new mysql instance on the NEW cloud host and see that the data appears correct and normal? if that's the case, then the problem has to be somewhere in the web pipeline (i.e starting from connection string all to way to browser) and has nothing to do with your mysql. – K4M Jul 30 '20 at 21:40
Yes, connected using HeidiSQL to verify if the data appears alright which it does to me - now, to verify more; it is possible that `ä¿¡é•·ã®é‡Žæœ›ãƒ»å‰µé€ ` can be somehow someway be displayed as `信長の野望・創造パワーアップキット` due to some encoding method, correct? It's not weird that data is displayed in the database like that? – Appel Flap Jul 30 '20 at 23:08
You should not see `ä¿¡é•·ã®é‡Žæœ›ãƒ»å‰µé€ ` coming from the database inside HeidiSQL, unless your connection settings inside HeidiSQL is widely different. Try the suggestions in my answer (updated for this) – K4M Jul 30 '20 at 23:31
Changing the collation of the column in question from `latin1_swedish_ci` to `utf8mb4_unicode_ci` worked, even though this seemed to fail for changing the whole database to `utf8mb4_unicode_ci`. After changing it, it prompted me to attempt to convert the column into the new collation. After accepting the conversion, the data now appears as UTF8, with all characters now supported. I'm not sure how or why this now works, but I know I will never touch collation again in a million years and leave it as is forever. I'm so glad this issue is now resolved - thanks to everyone that gave their feedback – Appel Flap Jul 31 '20 at 23:56
@AppelFlap I assume this is not satisfied you as an answer. – K4M Aug 08 '20 at 04:41
I have tried all the things suggested which sadly did not fix my issue. I do appreciate your input and efforts in trying to helping me out! – Appel Flap Aug 08 '20 at 19:13

score 0 · Answer 2 · answered Jul 29 '20 at 03:25

try this =

1- select database => Operations => Collation = utf8mb4

2- execute this = mysql_query("SET CHARACTER SET utf8");

3- write in your header = header('Content-Type: text/html; charset=utf-8');

4- convert all charachter to normal charachter =

alter proc dbo.specialcharacterreplacer

@tblname varchar(1000),
@column_name varchar(1000)

as
begin

--declare @obj VARCHAR(MAX),
--set @obj= 'select *from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = '@tblname''
--exec @obj


declare @Sql VARCHAR(MAX)
set @Sql = '
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ò'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ö'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ð'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ô'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''õ'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ó'''+ ', '+'''o'''+') 

update ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''×'''+ ', '+'''x'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''f'''+ ', '+'''f'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ò'''+ ', '+'''O'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ó'''+ ', '+'''O'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ô'''+ ', '+'''O'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Õ'''+ ', '+'''O'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ö'''+ ', '+'''O'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ñ'''+ ', '+'''n'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ñ'''+ ', '+'''N'''+')

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''è'''+ ', '+'''e'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''é'''+ ', '+'''e'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ê'''+ ', '+'''e'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ë'''+ ', '+'''e'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''È'''+ ', '+'''E'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''É'''+ ', '+'''E'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ê'''+ ', '+'''E'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ë'''+ ', '+'''E'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''á'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ã'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''â'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''à'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ä'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''å'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ª'''+ ', '+'''a'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''æ'''+ ', '+'''ae'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''À'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Á'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Â'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ã'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Å'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ä'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Æ'''+ ', '+'''A'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''æ'''+ ', '+'''AE'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ù'''+ ', '+'''u'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ú'''+ ', '+'''u'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''û'''+ ', '+'''u'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ü'''+ ', '+'''u'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''µ'''+ ', '+'''u'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ù'''+ ', '+'''U'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ú'''+ ', '+'''U'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Û'''+ ', '+'''U'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ü'''+ ', '+'''U'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ý'''+ ', '+'''Y'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ÿ'''+ ', '+'''Y'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ÿ'''+ ', '+'''y'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ý'''+ ', '+'''y'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''þ'''+ ', '+'''þ'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ç'''+ ', '+'''C'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ç'''+ ', '+'''c'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''œ'''+ ', '+'''ce'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Œ'''+ ', '+'''CE'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ì'''+ ', '+'''I'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Í'''+ ', '+'''I'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Î'''+ ', '+'''I'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ï'''+ ', '+'''I'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ì'''+ ', '+'''i'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''í'''+ ', '+'''i'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''î'''+ ', '+'''i'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ï'''+ ', '+'''i'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Ð'''+ ', '+'''D'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Þ'''+ ', '+'''D'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''ß'''+ ', '+'''B'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''Š'''+ ', '+'''S'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''š'''+ ', '+'''s'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''§'''+ ', '+'''S'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''½'''+ ', '+'''1/2'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¼'''+ ', '+'''1/4'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¾'''+ ', '+'''3/4'''+') 

UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''©'''+ ', '+'''Copyright '''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''®'''+ ', '+'''Registered trademark  '''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''‰'''+ ', '+'''%'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¿'''+ ', '+'''?'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''º'''+ ', '+'''0'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''¹'''+ ', '+'''1'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''²'''+ ', '+'''2'''+') 
UPDATE ' + @tblname + ' SET ' + @column_name+ ' = REPLACE('+@column_name + ', ' + '''³'''+ ', '+'''3'''+') 
 
' -- add your special character here inside 
exec (@sql)
end

go
EXEC dbo.specialcharacterreplacer @tblname = 'insert tablename here', @column_name ='insert columnname  here'

Hello, thanks for your reply. I've executed your 3 steps, however it did not make any changes. Step 4, I'm not sure if this is the solution as this would require me to put entire languages (Chinese, etc) in these replace queries. — Appel Flap, Jul 29 '20 at 20:27

Labu · Answer 3 · 2020-07-31T15:12:16.087

0

This is not the file or database. The "bug" is set when you first connect to the MySQL server.

When you connect to the MySQL server you need to provide an encoding, otherwise the server falls back to a default, which is: latin1

So, instead of connecting like this:

mysql -p -u someuser -h somehost

Make sure to connect like this:

mysql --default-character-set=utf8 -p -u someuser -h somehost

Once you have connected like this, you need to re-import your entire database.

I usually do it as follows:

cat mysqldumpfile.sql | mysql --default-character-set=utf8 -p -u someuser -h somehost myDatabase

edited Jul 31 '20 at 15:12

answered Jul 31 '20 at 15:04

Labu

2,572
30
34

Hello Labu, thank you for your answer. Please excuse my lack of knowledge about SSH. I have logged into SSH `mysql --default-character-set=utf8 -p -u [username] -p [password]`, and made my way into the database, which currently shows `MariaDB [databaseName]>`, as I don't know my host I've used the command `cat database.sql | mysql --default-character-set=utf8 -p -u [username] -p [password] [dbUsername]`, it doesn't import it and only shows an arrow `->`. What can I do? – Appel Flap Jul 31 '20 at 17:24
Try the import (`cat` etc) command without already being connected to the server. – Labu Aug 01 '20 at 11:22

score 0 · Answer 4 · answered Sep 04 '20 at 01:52

These 12 hex characters E4BFA1 E995B7 E381AE E9878E, when treated as MySQL's CHARACTER SET utf8 (or utf8mb4), will display as 信長の野. If treated as latin1, it displays as ä¿¡é•·ã®é‡Ž. The term for this is Mojibake.

This tells me that probably the problem is on the output side of things. But please provide SELECT HEX(col)... from your table. It should give you E4..., but it might give you C3A4C2BFC2A1C3A9E280A2C2B7C3A3C281C2AEC3A9E280A1C5BD. In this case, you have a different problem -- namely "double encoding", which happened on the input side. Or possibly when converting. Double encoding is especially nasty in that it shows correctly while being stored incorrectly.

Go here to see some more on Mojibake and double encoding.

Similarly ™ (E284A2) --> â„¢, which might actually be stored as C3A2E2809EC2A2.

"Webpage has UTF8 encoding tag." -- Good. But browsers tend to be forgiving. That is, the browser might see the double encoding and "fix" it for you on the screen.

http://mysql.rjweb.org/doc.php/charcoll#fixes_for_various_cases provides fixes for various cases, but first you need to carefully decide which case you really have.

score 0 · Answer 5 · answered Sep 02 '21 at 07:00

0

For me none of the above worked well

$conn->set_charset('utf8mb4'); //worked

$conn->query('set character_set_results=utf8'); //not worked

and diamonds turned into to proper characters.

answered Sep 02 '21 at 07:00

Attila Ragó

33
7

SQL Collation Not Displaying Correctly

What I've done

5 Answers5