3

I have a website with a chat system. This chat system has a table 'messages' and one of the columns is 'messagetext' set to utf8_general_ci(MySQL). In my website, I use php to query database. The results are ok.

In my app, i use NodeJS as backend but the results are like that: " ασδασδ ". There are greek words stored in this column.

I have tried iconv-lite,iconv,utf8,decode Json.

My thought, in the website it's getting the value 'ασδασÎ' from column and decoding to utf8, that's how can I see the right results. BUT in app this is not happening, the results are the value instead of the actual word.

What can I do?

(from Comment)

CREATE TABLE messages (
    id int(11) NOT NULL AUTO_INCREMENT, 
    sender int(11) NOT NULL, 
    reciever int(11) NOT NULL, 
    seen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', 
    datetime datetime NOT NULL, 
    messagetext text NOT NULL, 
    userseen varchar(3) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT 'no', 
    PRIMARY KEY (id)
) ENGINE=InnoDB AUTO_INCREMENT=94 DEFAULT CHARSET=utf8
2
  • Ref: identical SO post. Show some table structure/ mysql server versions. SHOW SESSION VARIABLES LIKE '%character_set%' from within your app and backend might show some useful results.
    – danblack
    Oct 31 '18 at 2:23
  • Thank you for your response. I got this: character_set_client utf8mb4 character_set_connection utf8mb4 character_set_database utf8 character_set_filesystem binary character_set_results utf8mb4 character_set_server latin1 character_set_system utf8
    – Bill
    Oct 31 '18 at 2:35
2

node.js needs something like this to start with:

var connection = mysql.createConnection({ ... , charset : 'utf8'});

Yeah, ασδασδ looks Greek to me. What you have is "Mojibake". See https://stackoverflow.com/questions/38363566/trouble-with-utf8-characters-what-i-see-is-not-what-i-stored for discussion of what causes Mojibake.

Do not use any form of encode/decode/conv -- it only compounds the problem. If that link does not help you, provide the extra details that it requests.

See also https://www.npmjs.com/package/utf8

The form should mention utf-8:

<form method="post" action="/your/url/" accept-charset="UTF-8">

Analysis...

'Κ' (Greek Kappa) is the 2 bytes, hex 'CE9A', in utf8. 'Κ' in latin1 is hex 'CE9A' (2 bytes) 'Κ', if converted to utf8 is hex 'C38E C5A1' 'ÃŽÅ¡' is that that looks like when interpreted as latin1.

Note: this last hex is what you show in your Comment.

So, you seem to have "double encoding".

SELECT CONVERT(BINARY(CONVERT(CONVERT(BINARY(CONVERT('ÃŽÅ¡' USING latin1)) USING utf8mb4) USING latin1)) USING utf8mb4);  --> 'Κ'

What is missing is where "latin1" is occurring in the flow of the data.

16
  • I set charset:utf8 in db connection and used var text = 'ασδασδ'; console.log(utf8.encode('ασδασÎ')); but I got: Ã�±Ã�Æ�Ã�´Ã�±Ã�Æ�Ã� . I have read this question about Mojibake but i have done all these steps.
    – Bill
    Oct 31 '18 at 14:46
  • @BillyPap - Show us the entire string for the db connection. Show us the table definition. I assume you really want to work with ασδασδ, not after it has been Mojibaked. utf8.encode only clouds the issue.
    – Rick James
    Oct 31 '18 at 16:53
  • my db connection is -> var db = mysql.createConnection({ host: config.host, user: config.user, password: config.password, database: config.base, charset : 'utf8' }); and table messages is ->utf8mb4_unicode_ci / column messagetext -> utf8mb4_unicode_ci
    – Bill
    Oct 31 '18 at 18:05
  • I just noticed something. When I write Καλησπέρα in a form and then send it to db, the words in column are -> ΚαλησπέÏα . I used a utf8 encoder/decoder and the actual utf8 encoding in the word Καλησπέρα is ÎαληÏÏέÏα . So the problem i think is encoding string to utf8. What do you think about it? How can i convert this ΚαλησπέÏα to this ÎαληÏÏέÏα ? You can try this here: cafewebmaster.com/online_tools/utf_decode. Write Καλησπέρα, encode this and then decode. You will see that the result is different from the stored result in db -> ΚαλησπέÏα.
    – Bill
    Oct 31 '18 at 22:22
  • Symbols like š, ƒ, € are boxes.
    – Bill
    Oct 31 '18 at 22:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.