Encoding issue

[tluyben]

New insights; the data in the tables of this company is definitely stored wrong (and I am not allowed to change or export/import), but that doesn't explain why it works in other drivers (I tried the .NET one too and it works as well) and not this one. It seems that the others do the last step (buffer.toString(encoding)) to the system encoding, not the client encoding? Could that be it?

---

I wrote the explanation below and decided to try one more thing:

On line;

node-mysql2/lib/parsers/text_parser.js

Line 162 in 943b8b2

const encodingExpr = `fields[${i}].encoding`;

I changed that to :

      const encodingExpr = `'utf8'`;

and that works... So connection, results & client are latin1, but when converting the Buffer to string, it uses utf8 now. So how do I set mysql to be latin1 for the connection, results and client, but have this encodingExpr be utf8?

It's the only way it works and it seems to be the 'normal way' for the php driver and cli? Not sure what the right way is but I don't really have the luxury of converting the db (for a variety of reasons) and so far it works for other mysql clients, so i'm curious where it goes off the rails.

Edit: for instance, is it possible to make a return the unconverted buffers for all encoded types?

======= my explanation below of the problem

I have a very old (and large) db I had to use; it's tables are charset = latin1. Now when I query from the mysql cli, the characters in the tables show up fine; japanese, korean, french etc are all fine. When I query from PHP, if I set the client, connection and results encoding to latin1, everything is fine too. But with mysql(2), even though I checked the encodings, I cannot get the output to show correctly; it shows the multibyte chars that are in the db.

So I checked my encoding settings with the mysql2 connection:

  [ 'character_set_client', 'latin1' ],
  [ 'character_set_connection', 'latin1' ],
  [ 'character_set_database', 'utf8mb4' ],
  [ 'character_set_filesystem', 'binary' ],
  [ 'character_set_results', 'latin1' ],
  [ 'character_set_server', 'utf8mb4' ],
  [ 'character_set_system', 'utf8mb3' ],
  [ 'character_sets_dir', '/usr/share/mysql/charsets/' ],
  [ 'collation_connection', 'latin1_swedish_ci' ],
  [ 'collation_database', 'utf8mb4_general_ci' ],
  [ 'collation_server', 'utf8mb4_general_ci' ]

This matches, 100%, the encodings at the time of the PHP connection on the same machine, same db. The settings on my mysql2 driver connection and php connection are both 'latin1' as can be seen above.

However, the following text stored in the latin1 encoded table in the db as ;

2ème partie

and shows up like this when querying with php or the mysql CLI or admin lite, but show up when querying with mysql2 as:

2ème partie

I debugged mysql2 a bit, and the code;

packet.readLengthCodedString(fields[3].encoding);

has the 'latin1' encoding (which I assume is right.

I tried to set different encodings in the connection setup of mysql2 (even though that doesn't make any sense) and that indeed made no difference or made it worse.

========

[sidorares]

this can probably be achieved with typeCast + .buffer(), that way you can skip any charset encoding

node-mysql2/lib/parsers/text_parser.js

Line 91 in 943b8b2

buffer: function() {

https://github.com/mysqljs/mysql#custom-type-casting

[tluyben]

Thanks, I am trying that; I tried it briefly yesterday but I couldn’t get it to work: the documentation helps (I couldn’t find it).

[sidorares]

( moving this to discussions as this is likely not a library issue and at this point don't see a reason to change / add api )

[tluyben]

I agree, but I think, at some time, it would be interesting to see why the other libs do work maybe?