NodeJS – Handling utf8_general_ci Issues

jsonMySQLquery

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

Best Answer

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.

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.

PASSWORD FUNCTIONS

Please run the following:

show variables like 'old_passwords';
select password('rolando'),old_password('rolando');

Depending on where old_passwords is enabled or not, you should get either the following:

MySQL 5.0 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.1 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords off

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | OFF   |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+-------------------------------------------+-------------------------+
| password('rolando')                       | old_password('rolando') |
+-------------------------------------------+-------------------------+
| *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409        |
+-------------------------------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

MySQL 5.5 with old_passwords on

mysql> show variables like 'old_passwords';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| old_passwords | ON    |
+---------------+-------+
1 row in set (0.00 sec)

mysql> select password('rolando'),old_password('rolando');
+---------------------+-------------------------+
| password('rolando') | old_password('rolando') |
+---------------------+-------------------------+
| 05e97e95241a4409    | 05e97e95241a4409        |
+---------------------+-------------------------+
1 row in set (0.00 sec)

mysql>

PASSWORDS STORED

Please run this query:

SELECT COUNT(1) password_length_count,password_length FROM
(SELECT LENGTH(password) password_length FROM mysql.user) A
GROUP BY password_length;

If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work.

PASSWORDS FROM CLIENT

In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.

Best Answer

Related Solutions

Mysql – Unexplained InnoDB timeouts

Mysql – Issue with connecting to the thesql database

PASSWORD FUNCTIONS

PASSWORDS STORED

PASSWORDS FROM CLIENT

Related Question