2.0 release

[sidorares]

I feel like 2.0 is ready for release, but I'd like to hear feedback from those using node-mysql in production. If we don't have blockers, let's release 2.0 - I have lots planned changes which require minor version bump (ssl, compression, prepared statements, resultAsArray flag etc)

( see also similar discussion 6 month ago - #526 )

[dougwilson]

I have been using it since alpha8 in a few production apps and have seen to issues so far in my uses cases (using the built-in connection pool, streaming queries, simple placeholders).

[sidorares]

If there is no other objections I'll tag and publish current master as 2.0 in, say, 2 days

[felixge]

@sidorares I just sent this out: https://twitter.com/felixge/status/420052809930014720 , hopefully a few more people will comment.

[yocontra]

Do you have a full changelog?

[yocontra]

By that I mean something a little smaller than what's in Changes.md that sums up what was added/removed

[felixge]

@contra so you want a less detailed changelog? Between what versions?

[yocontra]

@felixge I read the changelog and it doesn't look like there were any breakages between 0.9.6 and 2.0 so I should be good to go.

[felixge]

@contra the big difference between 0.9.6 is mysql.createClient() vs mysql.createConnection() and the semantic differences between the two (0.9.6 clients would try to reconnect, 2.0 connections are not reconnect-able).

[sidorares]

@contra - Cant't tell much about 0.9 to 2, but there are lots of small features added between first 2-alphaX and latest rc, each deserving minor version bump and at least one deprecation (soon to be backward incompatibility): pooled connection .end() renamed to .release()

[HansOspina]

I have been using using 2.0 Alpha(currently 2.0.0-alpha9) at production for a while, executing all the regular crud operations, placeholders and connection pooling. No issues. Thanks a lot for your work!

[chilts]

We're using the v2 release candidates and so far happy with it. Many thanks for all your help and hard work! :)

• https://github.com/mozilla/fxa-auth-server

[bcokca]

We are using 2.0.0-alpha7 and find it very successful. Its really good job 👍 Thank you very much :)

[remy]

I'm just commenting now (before I forget), I'm using 2.0.0-alpha7 and I've got a data corruption issue (it's being used in jsbin.com). I'm right now working on a paired down example of the issue. It may turn out not to be the module, but either way, I'll confirm on this thread.

[sidorares]

thanks @remy - I'll wait for your results

[remy]

I know isn't a great deal of use, but I'm trying to work out why the database isn't saving the characters in the same encoding as it's being presented (the database is using uft8mb4). Here's a comparison of what is being presented (i.e. that goes through the node-mysql module) and what's saved in the database:

I was actually seeing something worse before, but I'm struggling to replicate now: the user content would be saved, and could be retrieved correctly, but after I restarted my server, the data would be retrieved and appear corrupted (I'll see if I can dig out an older example in the database).

[sidorares]

as far as I know driver always serialises string as utf8. It looks like command line client displays utf8 cyrillic interpreted as if it's uft8mb4

[remy]

The thing is, the command line is actually fine (or used to be when working with [email protected]):

[remy]

Okay, there's definitely a problem between 0.9 and 2.0.

Here's the same code being run, except the first is output from 0.9 and the second is from 2.0 - you can see the second is corrupted output (the row in the database is cyrillic and renders correctly from the mysql cli tool) - make sure to scroll to the right in the code below.

0.9.6:

remy@jsbin:~/tmp/mysql-issue$ node index.js
[ { id: 1833638,
    javascript: '',
    html: '<p>Eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. <span id="iamrelative">Я элемент c position:relative. Я приподнят над остальным текстом на 3px и сдвинут вправо на 2px. </span>Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. </p>\n\n<div class="floatingblock" id="iamrelativeblock"></div>\n<div class="floatingblock" id="iamrelativeblock"></div>\n',
    created: Wed Dec 05 2012 09:57:53 GMT+0000 (UTC),

2.0.0-rc2:

remy@jsbin:~/tmp/mysql-issue$ node index.js
[ { id: 1833638,
    javascript: '',
    html: '<p>Eaque ipsa quae ab illo inventore veritatis et quasi architecto beatae vitae dicta sunt explicabo. <span id="iamrelative">? ??????? c position:relative. ? ????????? ??? ????????? ??????? ?? 3px ? ??????? ?????? ?? 2px. </span>Nemo enim ipsam voluptatem quia voluptas sit aspernatur aut odit aut fugit, sed quia consequuntur magni dolores eos qui ratione voluptatem sequi nesciunt. </p>\n\n<div class="floatingblock" id="iamrelativeblock"></div>\n<div class="floatingblock" id="iamrelativeblock"></div>\n',
    created: Wed Dec 05 2012 09:57:53 GMT+0000 (UTC),

This is the code I'm using for the example: https://github.com/remy/mysql-issue/blob/master/index.js

[dougwilson]

@remy we could take a look into this, but it would be even easier if you could do the following to help with repro:

1. Include a CREATE TABLE statement so we can create a table with the same column setup. Be sure to include the charset and collation in the statements.
2. Include the cyrillic string in the JS file so we an insert the same data and try and read it back.

[remy]

@dougwilson

This is the CREATE TABLE:

CREATE TABLE `sandbox` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `javascript` mediumtext COLLATE utf8mb4_unicode_ci,
  `html` mediumtext COLLATE utf8mb4_unicode_ci,
  `created` datetime DEFAULT NULL,
  `last_viewed` datetime DEFAULT NULL,
  `url` char(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `active` char(1) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'y',
  `reported` datetime DEFAULT NULL,
  `streaming` char(1) COLLATE utf8mb4_unicode_ci DEFAULT 'n',
  `streaming_key` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `streaming_read_key` char(32) COLLATE utf8mb4_unicode_ci NOT NULL,
  `active_tab` varchar(10) COLLATE utf8mb4_unicode_ci NOT NULL,
  `active_cursor` int(11) NOT NULL,
  `revision` int(11) DEFAULT '1',
  `css` mediumtext COLLATE utf8mb4_unicode_ci,
  `settings` mediumtext COLLATE utf8mb4_unicode_ci,
  PRIMARY KEY (`id`),
  KEY `viewed` (`last_viewed`),
  KEY `url` (`url`(191)),
  KEY `streaming_key` (`streaming_key`),
  KEY `spam` (`created`,`last_viewed`),
  KEY `revision` (`url`(191),`revision`)
) ENGINE=InnoDB AUTO_INCREMENT=1585585 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

Note that ut8mb4 requires a mysql config setup (beyond the scope of node).

Including the data and writing and reading back in the JS masks the issue. If there's a bug in mysql@2 it's consistently encoding (incorrectly) and decoding to the original text.

A better test is write the string in JS (as you've suggested) and read out in PHP or the mysql cli. Agree?

The problem that I'm highlight is that 0.9.x was correctly encoding when writing, but 2.0.0 is not - but I've seen in jsbin.com in production that it is consistent in the encoding error, so new data (written in node-mysql@2) isn't affected. But that doesn't cater for the previous 8mil+ rows that were created (mostly) with node-mysql < 2.

[dougwilson]

@remy are you sure it wasn't the old mysql that was encoding it incorrectly? Are you able to read it out from PHP when it was inserted with the old mysql 0.9.x? At the very least, I need to insert the same data you have in your database. If it needs to be done using the older mysql library, at least give me a file with your string that I can run using the old module so I can get the same data inserted.

[dougwilson]

Oh, also @remy can you make a new issue for this? That way this issue doesn't get hijacked with this issue.

[remy]

@dougwilson that's a fair comment (though if 0.9.x was corrupted the whole time, I've got a metric shit tonne of of duff rows in the database... - but let's not assume the worse). I'll write a php test too (based on the legacy jsbin code) to see what happens and open a new issue for this.

[sidorares]

It looks like a bug in 2.0. Steps to reproduce:

in mysql client

mysql> CREATE TABLE sandbox ( text mediumtext COLLATE utf8mb4_unicode_ci ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
Query OK, 0 rows affected (0.03 sec)
mysql> insert into sandbox (text) values (1, "test test кириллица test test");
Query OK, 1 row affected (0.00 sec)
mysql> select * from sandbox;
+----------------------------------------+
| text                                   |
+----------------------------------------+
| test test кириллица test test          |
+----------------------------------------+
1 row in set (0.00 sec)

node.js (I installed both 0.9.6 and 2rc2 by doing npm install and then renaming corresponding node_modules folder):

var mysql96 = require('mysql-0.9.6');
var conn = mysql96.createClient({
  user: 'root',
  password: '',
  database: 'test',
  charset: 'UTF8MB4'
});

conn.query('select * from sandbox', function(err, rows) { console.log('0.9.6:', rows[0]); } );
conn.end();

var mysql2rc2 = require('mysql-2rc2');
var conn2 = mysql2rc2.createConnection({
  user: 'root',
  password: '',
  database: 'test',
  charset: 'UTF8MB4'
});

conn2.query('select * from sandbox', function(err, rows) { console.log('2.0rc2:', rows[0]); } );
conn2.end();

Results:

0.9.6: { text: 'test test кириллица test test' }
2.0rc2: { text: 'test test ????????? test test' }

So @remy - it looks that your db content created by 0.9.6 is correct one and you won't need migration

[sidorares]

And this is select using console client after inserting using 0.9.6 and 2.0:

mysql> select * from sandbox;
+-----------------------------------------------------------------------+
| text                                                                  |
+-----------------------------------------------------------------------+
| test test кириллица test test                                         |
| test insert from 2.0 кириллица test test                     |
| test insert from 0.9.6 кириллица test test                            |
+-----------------------------------------------------------------------+

[dougwilson]

I think it is because you need to set charset to UTF8MB4_GENERAL_CI in the connection configuration. Can you try that and see if it makes a difference?

[dougwilson]

Basically the argument to charset needs to be a property defined in https://github.com/felixge/node-mysql/blob/master/lib/protocol/constants/charsets.js otherwise it seems the driver and MySQL get out of sync on what they except each other to talk in. The default is UTF8_GENERAL_CI.

[sidorares]

Yep, just found it myself - there is no UTF8MB4 in constants. After changing it to UTF8MB4_GENERAL_CI it works just fine:

mysql-issue git:master ❯ node index.js                                                                                                                                                        
0.9.6: { text: 'test test кириллица test test' }
2.0rc2: { text: 'test test кириллица test test' }

@remy - could try with your data when charset set to UTF8MB4_GENERAL_CI?

[dougwilson]

If this is confirmed, I think there should be an error raised if charset is specified to be an unknown value. This would have shown the issue much earlier :)

[sidorares]

Yes, I think we should add this check

[felixge]

@dougwilson thanks for figuring it out. Throwing an error would definitely be great for this.

[remy]

@sidorares good god man, this is for you:

Phew, I'm seriously relieved this is fixed - I was getting a touch worried that I was going to be stuck in the land of weird data.

My work is done here.

[sidorares]

@remy You are welcome :)

[sidorares]

✔ npm version 2.0.0 && git push && npm publish