node-mysql doesn't respect connection encoding

[angryziber]

node-mysql encodes all queries using utf8. This works fine, if mysql server's default encoding is also set to utf8, but storing of international characters is broken otherwise.

When opening a connection, the server specifies its default encoding, which is usually latin1 on new installations.
node-mysql can be fixed by sending SET NAMES 'utf8'; on connection if server uses something non-utf8.

As a workaround, to "fix" your mysql server, put 'default-character-set=utf8' into my.cnf/my.ini file.

And (optionally) run this on an already existing database:
alter database YOUR_DB character set utf8 collate utf8_general_ci;
alter table YOUR_TABLE charset=utf8;

[felixge]

What could this module do to handle this? Sending SET NAMES ... automatically seems bad.

[angryziber]

Why bad? If you encode everything you send as utf-8, then you should ensure that server will use the same encoding.
MySQL sends expected encoding in the greeting message after you have opened the connection, so I suggest sending SET NAMES if server expects some other encoding by default.

[angryziber]

Otherwise I see funny ????? question marks in the tables without any error or warning.

[felixge]

Why bad?

Because issuing user-level queries automatically isn't something most people would expect their mysql driver to do. But I'll look into this.

[Koc]

what about reconnecting?

1. create client
2. send SET NAMES query
3. service mysql restart
4. autoreconnect done, but SET NAMES query does not sent

IMHO connection encoding should be supported by this library

[felixge]

IMHO connection encoding should be supported by this library

I'm still unhappy how re-connects are handled ATM. Once I get time, I'll make the connection handling more simple to reason about. I'll probably also emit a 'connect' event that takes a parameter indicating if this is the first connect or a re-connect.

[felixge]

This remains an issue with 2.0.0-alpha. I'm thinking the proper way to do this is using https://github.com/bnoordhuis/node-iconv when available to deal with different encodings. I won't have time anytime soon to do this so. Patches & Discussion on the mailing list are more than welcome.

[walker]

Would this be a reason that when I queried the following table with the new 2.0.0 alpha, I ended up with an encoding error in the results which caused the module itself to return an error instead of the results?

I downgraded to 0.9.6 and it's working fine...

CREATE TABLE `services` (
  `id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `name` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `client_id` char(36) COLLATE utf8_unicode_ci NOT NULL,
  `created` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `auth_type` varchar(50) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `credentials` varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `data` text COLLATE utf8_unicode_ci,
  `type` varchar(20) COLLATE utf8_unicode_ci DEFAULT 'mymessages',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

INSERT INTO `services` VALUES ('4b9f32c0-12c2-c27c-8ee7-03bfe4a30462','messages','16eca16a-59f5-4569-bed2-234dd1cdd88e','2012-05-29 20:52:40','mymessages','{\"username\":\"revere\",\"password\":\"2$3sghAfgh\"}',NULL,'phone');
INSERT INTO `services` VALUES ('4c6192a0-aaed-4326-9034-bfcf314507bb', 'Gimme Gimme', '6b80f0fc-42b1-616f-a243-d390baeb1740', '2012-06-06 20:52:40', 'gimmegimme', '{\"key\":\"4EF60F29-EFDA-A9D0-4557-1E38A585F780\"}',NULL,'phone-email');

(Sorry, put it on the wrong issue, just now)

[felixge]

@walker No, if you are using utf8 you should be fine. Please open a new ticket for this.

FWI: I was not able to reproduce your problem:

connection.query('SELECT * FROM services', function(err, rows, fields) {
  if (err) throw err;

  console.log(rows);
});

Got me this output:

[ { id: '4b9f32c0-12c2-c27c-8ee7-03bfe4a30462',
    name: 'messages',
    client_id: '16eca16a-59f5-4569-bed2-234dd1cdd88e',
    created: Tue, 29 May 2012 18:52:40 GMT,
    auth_type: 'mymessages',
    credentials: '{"username":"revere","password":"2$3sghAfgh"}',
    data: null,
    type: 'phone' },
  { id: '4c6192a0-aaed-4326-9034-bfcf314507bb',
    name: 'Gimme Gimme',
    client_id: '6b80f0fc-42b1-616f-a243-d390baeb1740',
    created: Wed, 06 Jun 2012 18:52:40 GMT,
    auth_type: 'gimmegimme',
    credentials: '{"key":"4EF60F29-EFDA-A9D0-4557-1E38A585F780"}',
    data: null,
    type: 'phone-email' } ]

I ended up with an encoding error in the results which caused the module itself to return an error instead of the results?

If you see errors, it is super helpful if you include them in your bug report ... just saying ; ). Again, new ticket please, this is unrelated.

[dresende]

I agree with @felixge , there's nothing wrong with node-mysql. I believe you're probably trying to see the results in another client like phpmyadmin or something that does not read data in utf8 by default so you''ll see weird stuff.