bigint insert ids error

[sirian]

Error happens while node-mysql parses insertid (auto increment bigint field) after row insertion in OkPacket:

Parser.prototype.parseLengthCodedNumber: "parseLengthCodedNumber: JS precision range exceeded number is >= 53 bit ..."

how to fix it?

[kai-koch]

If you do an INSERT or UPDATE you must use a string representation of the number..
E.g.:

INSERT INTO tempTable SET BigIntField = '23339090909090909786786768687687'

If you try to read from a big int field or from count(), see Issue #373

[dougwilson]

The problem @sirian is having is that there is a INSERT ... statement to a table which has a AUTO_INCREMENT primary key which is now counting above 18014398509481983. For example, this will reproduce the issue:

CREATE TABLE `t` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `c` VARCHAR(2) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=72057594037927935 DEFAULT CHARSET=utf8;

connection.query('INSERT INTO `t` SET `c` = ?;', ['a'], function (err, result) {
  if (err) throw err;
  console.log('New row ID: %s', result.insertId);
});

As far as I can tell, there is no way to work-around this without something being changed in node-mysql source. This is not related to issue #373.

[dresende]

Big numbers are converted to Number by default now. If you're using such big numbers you need to create a custom typeCast.

connection.query({
  sql: "SELECT * FROM " + table,
  typeCast: function (field, next) {
    if (field.type == 'LONGLONG') {
      return field.string();
    }

    return next();
  }
}, function (err, results) {
  // ...
});

[dougwilson]

@dresende does the custom typecast even apply to parsing insertId from the OkPacket? As far as I can tell it does not and there is no way to insert into a table with a BIGINT primary key because of that.

[sirian]

AFAIK, type casting doesn't apply when parsing insertId

[kai-koch]

I tested it.
The typecast-function does _NOT_ apply to result.insertId!

function typeCastBigInt(field, next) {
    if (field.type === 'LONGLONG') {
        return field.string();
    }
    return next();
}
function handleResult(err, result) {
    if (err) {
        throw err;
    }
    console.log('New row ID: %s', result.insertId); // needs to be %s since Big Ints have to be represented as string in JS
}
var mysql  = require('mysql');
var dbConfig = require('./dbConfig');
dbConfig.database = 'test';
var connection = mysql.createConnection(dbConfig);
connection.connect();
var sql = 'INSERT INTO `t` SET `c` = ' + connection.escape('a') + ';';
connection.query({sql: sql, typeCast: typeCastBigInt}, handleResult);
connection.end();

Error: parseLengthCodedNumber: JS precision range exceeded,
number is >= 53 bit: "72057594037927940"

The row is correctly inserted into the table from above, though. [ t.id = 72057594037927935, t.c= 'a' ]
But that is to be expected, since it is an auto-id created by the mysql-server.

[sirian]

hope this bug will be fixed ASAP, don't want to migrate on smth else

[kai-koch]

The problem lies in Parser.js#L174.
There should be something to indicate on connection level, that Big Ints have to be type casted instead of simply throwing an error. Like in previous ALPHA-versions.

[dresende]

We can't use typeCast here. Parser must be able to convert the value to string when exceeds the limit.

[dresende]

I have a patch using bignumber.js. I'm going to make a pull request.

[dresende]

Look at #382 . @kai-koch could you test it? Please comment if you have a better idea.

[kai-koch]

Works with my example code.

[dougwilson]

@kai-koch how the pull request currently is, you'll usually have a JS Number in insertId, but sometimes a JS String if the number was too large.

[kai-koch]

How would the general behaviour of Big Ints be changed by this?
As far as I can see a Big Number is simply returned as string. Will this be default or opt in behaviour for all big ints node-mysql returns.

I followed the discussion thru the different issues concerning big ints, when it was decided to have custom typeCast-functions. There should be at least the same behaviour every where a big int is returned. I personally rely on strings on LongLong fields, since no precision is lost.

I haven't locked through bignumber.js, but how does it impact performance?

(@dougwilson referred to the unedited post. I decided to eloborate my question more in this comment)

BTW: My IDE naggs that byte is a reserved word, you might want to change the local variable name if the code is refactored in the future. ;-P

[dresende]

This was fixed since #382 landed in master. Will probably release a new alpha version.