Mysql: parseLengthCodedNumber: JS precision range exceeded, number is >= 53 bit

[jatinder85]

Inserting a negative id on auto_increment primary key INT column, throws JS precision range exceeded error. The same insert statement works with mysql directly, but not with node-mysql.

node Version: 0.10.35
node-mysql Version: 2.7.0

Sample Insert Query

var user  = {id: -999, name: 'unknown'};
var query = connection.query('INSERT INTO user SET ?', user, function(err, result) {
...
});

Sample Table Structure

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1022512 DEFAULT CHARSET=latin1;

Stack Trace and Debug Logs

<-- HandshakeInitializationPacket
{ protocolVersion: 10,
  serverVersion: '5.6.22',
  threadId: 54,
  scrambleBuff1: <Buffer 78 2a 63 38 2c 70 71 69>,
  filler1: <Buffer 00>,
  serverCapabilities1: 63487,
  serverLanguage: 8,
  serverStatus: 2,
  serverCapabilities2: 32895,
  scrambleLength: 21,
  filler2: <Buffer 00 00 00 00 00 00 00 00 00 00>,
  scrambleBuff2: <Buffer 3a 24 44 6c 70 6e 56 46 3b 34 31 38>,
  filler3: <Buffer 00>,
  pluginData: 'mysql_native_password',
  protocol41: true }

--> ClientAuthenticationPacket
{ clientFlags: 455631,
  maxPacketSize: 0,
  charsetNumber: 33,
  filler: undefined,
  user: 'metadata-admin',
  scrambleBuff: <Buffer ce 9a 3e 1d fa 54 8d c5 64 68 de c4 40 77 0e 13 b2 86 79 cf>,
  database: 'metadata',
  protocol41: true }

<-- OkPacket
{ fieldCount: 0,
  affectedRows: 0,
  insertId: 0,
  serverStatus: 2,
  warningCount: 0,
  message: '',
  protocol41: true,
  changedRows: 0 }

--> ComQueryPacket
{ command: 3,
  sql: 'INSERT INTO user SET `id` = -999, `login` = \'unknown\', `name` = \'unknown\', `email` = \'\'' }

<-- OkPacket
{ fieldCount: 0,
  affectedRows: 1,
  insertId: undefined,
  serverStatus: undefined,
  warningCount: undefined,
  message: undefined,
  protocol41: true }


Error: parseLengthCodedNumber: JS precision range exceeded, number is >= 53 bit: "18446744073709550617"
    at Parser.parseLengthCodedNumber (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Parser.js:236:18)
    at OkPacket.parse (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/packets/OkPacket.js:17:30)
    at Protocol._parsePacketDebug (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Protocol.js:276:12)
    at Protocol._parsePacket (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Protocol.js:251:10)
    at Parser.write (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Parser.js:77:12)
    at Protocol.write (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Protocol.js:39:16)
    at Socket.<anonymous> (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/Connection.js:96:28)
    at Socket.emit (events.js:95:17)
    at Socket.<anonymous> (_stream_readable.js:765:14)
    at Socket.emit (events.js:92:17)
    --------------------
    at Protocol._enqueue (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/protocol/Protocol.js:135:48)
    at Connection.query (/Users/ja5058/Documents/YP/git/mothra-js-2/node_modules/mysql/lib/Connection.js:201:25)
    at Object.<anonymous> (/Users/ja5058/Documents/YP/git/mothra-js-2/app/fail.js:20:24)
    at Module._compile (module.js:456:26)
    at Object.Module._extensions..js (module.js:474:10)
    at Module.load (module.js:356:32)
    at Function.Module._load (module.js:312:12)
    at Function.Module.runMain (module.js:497:10)
    at startup (node.js:119:16)
    at node.js:929:3

[dougwilson]

Hi! We can definitely take a look :) Can you write down the version of Node.js you are using and the version of this module you are using when you get that error? Can you also provide the full stack trace of the error?

[jatinder85]

Updated the comment above with version and stack trace.

FYI: If I remove auto_increment from the "id" column, there is no issue.

Thanks!

[dougwilson]

So the issue is that when the column is auto increment, MySQL sends the insertId value as a BIGINT over the protocol after an INSERT. Because it is negative, it is represented as two's compliment, which means it is outside the range of precision of JavaScript.

I'll see if there is some way to work-around this, but I the meantime, you can use the appropriate connection flags from the documentation to enable big number support.

[jatinder85]

Okay thanks for the info, yeah I can enable big number support for now.

[dougwilson]

Cool. I'm working on a possible fix, so perhaps I may have it fixed tonight :)

[dougwilson]

The fix is much, much larger than I originally expected, so I just wanted to give an update that I may not get a fix in tonight, but it is actively being worked on :)

[jatinder85]

Ok thanks for the update. Appreciate you working on it.

[dougwilson]

After extensive research, this is a bug in the MySQL protocol (see https://bugs.mysql.com/bug.php?id=69228 for the original report on negative numbers in auto increment packets). You will have to keep big number support enabled if you want to use negative numbers in auto increment.

There is also a note buried in the MySQL manual regarding this: https://dev.mysql.com/doc/refman/5.6/en/innodb-auto-increment-traditional.html

The behavior of the auto-increment mechanism is not defined if you assign a negative value to the column, or if the value becomes bigger than the maximum integer that can be stored in the specified integer type.

[jonapgar]

Hi there, just in case anyone else has a similar issue. I ran into this bug when doing something like this .

INSERT ... ON DUPLICATE KEY UPDATE ID = LAST_INSERT_ID(ID) ...

I was using this technique to fetch out the ID of the row that was inserted OR updated. The issue was that in some tables the ID field was not actually an auto-increment field, it was a base64 string that occasionally was preceded with a negative sign ('-') and MySQL will parse this string into a huge positive number, triggering the precision error.

Lesson learned for me here is not to use LAST_INSERT_ID unless you are dealing with an auto-increment field only... which is sort of obvious I guess :)