Chinese column names become garbled in mysql2^1.0.0

[NijiharaTsubasa]

We have some pretty old tables with some Chinese column names like this:

CREATE TABLE `商城` (
 `商品类型` varchar(255) NOT NULL,
 `商品说明` varchar(255) NOT NULL,
 `价格` int(11) NOT NULL,
 `剩余` int(11) NOT NULL,
 PRIMARY KEY (`商品类型`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

We used to be using mysql2 1.0.0-rc.11 and everything is OK:

Today I was migrating the app to another server and npm install'd version 1.1.0 and everything is broken:

I also tested version 1.0.0 and no luck, the column names are still garbled, and I have to roll back to 1.0.0-rc.11

Some details:
Only varchar columns was good, name of other columns (like the int ones) all become garbled.
The charset of my database, table and varchar columns are all utf8.
The js file being executed is also utf8.
The charset of the connection is also utf8. ("set names utf8")

[sushantdhiman]

Can you try without set names utf8 ?

[NijiharaTsubasa]

@sushantdhiman No luck, everything is the same with or without set names utf8

[sushantdhiman]

@NijiharaTsubasa It seems issue might be on

node-mysql2/lib/packets/column_definition.js

Line 54 in b3c3194

this.name = StringParser.decode(this._buf.slice(_nameStart, _nameStart + _nameLength), this.encoding);

, may be we aren't passing right encoding.

Can you please add a PR with failing test case?

[sidorares]

@NijiharaTsubasa can you post what's returned in the fields array (third argument in callback - conn.query(sql, function(err, rows, fields) {...})?

What happens when you do set names utf8mb4?

Which tools you used to create table? Was it created with node client as well?

utf8 in mysql is complicated: when encoding is set as 'utf8' the cesu-8 is used in fact. If utf8mb4 charset is used, data is encoded with 'regular' utf8, but all chars in metadata (field names etc) outside of BMP replaced with '?' symbol.

I think what currently happens is:

1. when originally created, table names saved as utf8 (not cesu-8). Mysql stored them as is, but it's not aware that names encoded with utf8 ( 'normal' utf8 chars encoded with 4 and more bytes have different binary representation from cesu-8 )
2. if you use mysql2 1.0.0-rc.11 data is treated again as utf8 (which sometimes look like it is working, but in some other cases on the server it'll be broken: counting chars, sorting etc - mysql assumes encoding is cesu-8 and in fact we saved utf8)
3. if you use node-mysql2 1.1.0 it sees charset "utf8" and uses cesu-8 codec do decode strings.

Potential solution:

1. re-create empty table using node-mysql2 1.1.0.
2. on mysql server, select old table into newly created
3. drop old, rename new to old

[sidorares]

@NijiharaTsubasa see very lengthy discussion on #374 pr

[sidorares]

actually all chars from table names have 3 bytes when encoded using utf8, so my assumptions might be incorrect

> var a = Buffer.from('商品类型')
undefined
> a.length
12
> var a = Buffer.from('商品说明')
undefined
> a.length
12
> var a = Buffer.from('价格')
undefined
> a.length
6
> var a = Buffer.from('剩余')
undefined
> a.length
6

[NijiharaTsubasa]

@sidorares
Sorry I can't use my computer until October 7th so can't do some tests now but I can say that fields argument is also garbled.
Tables were all created using phpmyadmin by direct creating or via a sql file(utf8) directly imported (both tested). I will test create with mysql2 but I don't think it will work.

Maybe I will consider use utf8mb4 instead but some users of my program have a rather old mysql that don't support utf8mb4 maybe I'llask him update his first

[sidorares]

@NijiharaTsubasa when you back to your computer, can you provide a log output from select * from 商城 limit 1 when mysql2 client is created using debug: true option? (provided you don't have sensitive data there).