Encoding issues

[dougwilson]

This module just fails so much with character encodings. If the server is not sending UTF-8 back, everything will just be garbage. Because of the parsing architecture of this module, making this work cannot be done in a backwards-compatible way.

[meepen]

This is a pretty big thing I am hoping will get fixed.

[dresende]

Can you elaborate? I'm not able to reproduce.

[dougwilson]

This module needs to decode the incoming strings from the row packets according to the charset specified in the field packets for that given sequence, not just using what was configured as the charset properties in the connection config (since this is just for the initial negotiation). A good example is to set a column to a latin1 charset, store a character that has the high bit set in that column, connect with this module using the default settings to a server that ignores the charset given by the client and instead uses the latin1 charset of that column and then try to query that row. The character will be decoded as if it was UTF-8, but it is not, typically resulting in the Unicode replacement character to be returned instead.

[dougwilson]

Since this is just a tracking issue for something the current maintainers are aware of as an issue, it was not necessary to go into great detail here. If you would like me to put together an actual reproduction, rather than just a description, I'd be happy to do so :)! But I'll need to get home from vacation in a week or so first so I have the time to actually do that (and then, it will be done after I complete all the tasks that have accumulated while I have been away on vacation).

I feel like the explanations is pretty clear above, so let me know if it isn't and what any specific questions are :)

[dresende]

When does the server ignore the charset given by the client? Can it enforce another one? If so, do you know how I can simulate that?

[sidorares]

I just wanted to understand issue and created test example to reproduce ( mysql2 is also very dumb in treating fields charsets, it assumes that server is using utf8 after connection )

var mysql = require('../index.js');

var c = mysql.createConnection({ debug: true, user: 'user', password: 'pass', database: 'test'})
c.query('CREATE TABLE charset_test ( test varchar(500) ) CHARACTER SET latin1 COLLATE latin1_danish_ci', (err, rows, fields) => console.log(err, fields, rows))
c.query("delete from charset_test", (err, rows, fields) => console.log(err, fields, rows));
var textExamples = ['ютф восемь', 'test тест テスト փորձարկում পরীক্ষা kiểm tra  ', ' ტესტი પરીક્ષણ  מבחן פּרובירן اختبار परीक्षण   '];
textExamples.forEach( (val) => {
  c.query("insert into charset_test(test) values(?)", [val], (err, rows, fields) => console.log(err, fields, rows));
});

c.query("select *  from charset_test", (err, rows, fields) => console.log(err, fields, rows));

last query prints garbage

[dresende]

Thank you, will try it :)

[dresende]

The problem starts with the INSERT. I'm not sure the problem exists on SELECT. I'm trying to insert those test values using Sequel Pro and it prints exactly the same garbage 👊

[sidorares]

so the problem is 1) default charset may be different from what client asked 2) result charset can be anything. When we read strings from network packets instead of always doing `readString(..., 'utf8') we need to use field charset ( always sent with column definition packet - http://dev.mysql.com/doc/internals/en/com-query-response.html#column-definition )

[dresende]

MySQL client on OSX (macports):

mysql> delete from charset_test;
Query OK, 6 rows affected (0.01 sec)

mysql> insert into charset_test values ('ютф восемь');
Query OK, 1 row affected, 1 warning (0.00 sec)

mysql> select * from charset_test;
+------------+
| test       |
+------------+
| ??? ?????? |
+------------+
1 row in set (0.00 sec)

[sidorares]

@dresende maybe it's not a good example :) try to insert something non-utf8 ( but in the table encoding ) and then read with mysql client. I'll try to make a better one