node-mysql2
node-mysql2 copied to clipboard
Published
20 hours ago •
sidorares
sidorares
Compatibility issue - Possible corruption of values with min and max values for a double.
Given the following example
import mysql from 'mysql2/promise'
import fs from 'fs'
class Test {
vendorProperties = {
"multipleStatements":true
,"typeCast":true
,"supportBigNumbers":true
,"bigNumberStrings":true
,"dateStrings":true
,"trace":true
,"user":"root"
,"password": "oracle"
,"host":"yadamu-db1"
,"database":"sys"
,"port":3306
, infileStreamFactory : (path) => {return fs.createReadStream(path)}
}
async createConnectionPool() {
let stack, operation
try {
stack = new Error().stack;
operation = 'mysql.createPool()'
this.pool = mysql.createPool(this.vendorProperties)
console.log('Pool Created')
} catch (e) {
throw e
}
}
async getConnectionFromPool() {
let stack
try {
stack = new Error().stack;
const connection = await this.pool.getConnection()
console.log('Connection obtained')
return connection
} catch (err) {
throw err
}
}
async closeConnection(options) {
if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
let stack;
try {
stack = new Error().stack
await this.connection.release()
this.connection = undefined;
} catch (e) {
this.connection = undefined;
throw e
}
}
};
async closePool(options) {
if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
let stack;
try {
stack = new Error().stack
await this.pool.end()
this.pool = undefined;
} catch (e) {
this.pool = undefined;
throw e
}
}
}
async executeSQL(sqlStatement,args) {
let stack
let results
try {
stack = new Error().stack;
const [results,fields] = await this.connection.query(sqlStatement,args)
return results;
} catch (e) {
throw e
}
}
async test() {
const data = [[1 , -1.7976931348623157e308],[2,1.7976931348623157e308]]
let results
try {
await this.createConnectionPool()
this.connection = await this.getConnectionFromPool()
results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
results = await this.executeSQL(`CREATE TEMPORARY table if not exists "t1"("key" smallint ,"double_col" double); `);
results = await this.executeSQL(`insert into "t1"("key","double_col") values ?`,[data]);
results = await this.executeSQL(`select "key", "double_col", cast("double_col" as char(64)) as "cast_result" from "t1"`);
console.log(results)
await this.closeConnection();
await this.closePool();
} catch (e) {
await this.closeConnection();
await this.closePool();
console.log(e)
}
}
}
const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))
I get
C:\Development\YADAMU>node src\scratch\mysql\dbIssue.js
Pool Created
Connection obtained
[
{
key: 1,
double_col: -1.7976931348623155e+308,
cast_result: '-1.7976931348623157e308'
},
{
key: 2,
double_col: 1.7976931348623155e+308,
cast_result: '1.7976931348623157e308'
}
]
Success
As you can see the values in question appear to have been stored correctly in the database, given the cast() returns the expected values, but the values retrieved appear to have been corrupted at the least significant digits. This is working with the lastest MYSQL Docker VM (9.x).
I als see the issue when I run MYSQL2 against the latest MYSQL 8.0 docker container.
MySQL version of test case
import mysql from 'mysql'
import fs from 'fs'
class Test {
vendorProperties = {
"multipleStatements":true
,"typeCast":true
,"supportBigNumbers":true
,"bigNumberStrings":true
,"dateStrings":true
,"trace":true
,"user":"root"
,"password": "oracle"
,"host":"yadamu-db1"
,"database":"sys"
,"port":33061
, infileStreamFactory : (path) => {return fs.createReadStream(path)}
}
async createConnectionPool() {
let stack, operation
try {
stack = new Error().stack;
operation = 'mysql.createPool()'
this.pool = mysql.createPool(this.vendorProperties)
console.log('Pool Created')
} catch (e) {
throw e
}
}
async getConnectionFromPool() {
const connection = await new Promise((resolve,reject) => {
this.pool.getConnection((err,connection) => {
if (err) {
reject(this.getDatabaseException(this.DRIVER_ID,err,stack,'mysql.Pool.getConnection()'))
}
resolve(connection)
})
})
return connection
}
async closeConnection(options) {
if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
let stack;
try {
stack = new Error().stack
await this.connection.release()
this.connection = undefined;
} catch (e) {
this.connection = undefined;
throw e
}
}
};
async closePool(options) {
if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
let stack;
try {
stack = new Error().stack
await this.pool.end()
this.pool = undefined;
} catch (e) {
this.pool = undefined;
throw e
}
}
}
executeSQL(sqlStatement,args) {
return new Promise((resolve,reject) => {
const stack = new Error().stack;
const sqlStartTime = performance.now()
this.connection.query(sqlStatement,args,async (err,results,fields) => {
console.log(results)
resolve(results)
})
})
}
async test() {
const data = [[1 , -1.7976931348623157e308],[2,1.7976931348623157e308]]
let results
try {
await this.createConnectionPool()
this.connection = await this.getConnectionFromPool()
results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
results = await this.executeSQL(`CREATE TEMPORARY table if not exists "t1"("key" smallint ,"double_col" double); `);
results = await this.executeSQL(`insert into "t1"("key","double_col") values ?`,[data]);
results = await this.executeSQL(`select "key", "double_col", cast("double_col" as char(64)) as "cast_result" from "t1"`);
console.log(results)
await this.closeConnection();
await this.closePool();
} catch (e) {
await this.closeConnection();
await this.closePool();
console.log(e)
}
}
}
const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))
results in
C:\Development\YADAMU>node src\scratch\mysql\dbIssue-MYSQL.js
Pool Created
OkPacket {
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverStatus: 0,
warningCount: 1,
message: '',
protocol41: true,
changedRows: 0
}
OkPacket {
fieldCount: 0,
affectedRows: 0,
insertId: 0,
serverStatus: 1,
warningCount: 0,
message: '',
protocol41: true,
changedRows: 0
}
OkPacket {
fieldCount: 0,
affectedRows: 2,
insertId: 0,
serverStatus: 1,
warningCount: 0,
message: '&Records: 2 Duplicates: 0 Warnings: 0',
protocol41: true,
changedRows: 0
}
[
RowDataPacket {
key: 1,
double_col: -1.7976931348623157e+308,
cast_result: '-1.7976931348623157e308'
},
RowDataPacket {
key: 2,
double_col: 1.7976931348623157e+308,
cast_result: '1.7976931348623157e308'
}
]
[
RowDataPacket {
key: 1,
double_col: -1.7976931348623157e+308,
cast_result: '-1.7976931348623157e308'
},
RowDataPacket {
key: 2,
double_col: 1.7976931348623157e+308,
cast_result: '1.7976931348623157e308'
}
]
Success
Simplified Test Case (No Table Required)..
import mysql from 'mysql2/promise'
import fs from 'fs'
class Test {
vendorProperties = {
"multipleStatements":true
,"typeCast":true
,"supportBigNumbers":true
,"bigNumberStrings":true
,"dateStrings":true
,"trace":true
,"user":"root"
,"password": "oracle"
,"host":"yadamu-db1"
,"database":"sys"
,"port":3306
, infileStreamFactory : (path) => {return fs.createReadStream(path)}
}
async createConnectionPool() {
let stack, operation
try {
stack = new Error().stack;
operation = 'mysql.createPool()'
this.pool = mysql.createPool(this.vendorProperties)
console.log('Pool Created')
} catch (e) {
throw e
}
}
async getConnectionFromPool() {
let stack
try {
stack = new Error().stack;
const connection = await this.pool.getConnection()
console.log('Connection obtained')
return connection
} catch (err) {
throw err
}
}
async closeConnection(options) {
if ((this.connection !== undefined) && (typeof this.connection.release === 'function')) {
let stack;
try {
stack = new Error().stack
await this.connection.release()
this.connection = undefined;
} catch (e) {
this.connection = undefined;
throw e
}
}
};
async closePool(options) {
if ((this.pool !== undefined) && (typeof this.pool.end === 'function')) {
let stack;
try {
stack = new Error().stack
await this.pool.end()
this.pool = undefined;
} catch (e) {
this.pool = undefined;
throw e
}
}
}
async executeSQL(sqlStatement,args) {
let stack
let results
try {
stack = new Error().stack;
const [results,fields] = await this.connection.query(sqlStatement,args)
return results;
} catch (e) {
throw e
}
}
async test() {
const minVal = -1.7976931348623157e308
const maxVal = 1.7976931348623157e308
let results
try {
await this.createConnectionPool()
this.connection = await this.getConnectionFromPool()
results = await this.executeSQL(`SET AUTOCOMMIT = 0, TIME_ZONE = '+00:00',SESSION INTERACTIVE_TIMEOUT = 600000, WAIT_TIMEOUT = 600000, SQL_MODE='ANSI_QUOTES,PAD_CHAR_TO_FULL_LENGTH', GROUP_CONCAT_MAX_LEN = 1024000, GLOBAL LOCAL_INFILE = 'ON'`);
results = await this.executeSQL(`select ? as "C1",cast(? as char(64)) as "C2",? as "C3",cast(? as char(64)) as "C4",${minVal} as "C5",cast(${minVal} as char(64)) as "C6",${maxVal} as "C7",cast(${maxVal} as char(64)) as "C8"`,[minVal,minVal,maxVal,maxVal]);
console.log(results)
const compare = {
C1: results[0].C1 === minVal
, C2: parseFloat(results[0].C2) === minVal
, C3: results[0].C3 === maxVal
, C4: parseFloat(results[0].C4) === maxVal
, C5: results[0].C5 === minVal
, C6: parseFloat(results[0].C6) === minVal
, C7: results[0].C7 === maxVal
, C8: parseFloat(results[0].C8) === maxVal
}
console.log(compare)
await this.closeConnection();
await this.closePool();
} catch (e) {
await this.closeConnection();
await this.closePool();
console.log(e)
}
}
}
const test = new Test();
test.test().then(() => console.log('Success')).catch((e) => console.log(e))
Results in
C:\Development\YADAMU>node src\scratch\mysql\dbleIssue2.js
Pool Created
Connection obtained
[
{
C1: -1.7976931348623155e+308,
C2: '-1.797693134862316e308',
C3: 1.7976931348623155e+308,
C4: '1.7976931348623157e308',
C5: -1.7976931348623155e+308,
C6: '-1.797693134862316e308',
C7: 1.7976931348623155e+308,
C8: '1.7976931348623157e308'
}
]
{
C1: false,
C2: false,
C3: false,
C4: true,
C5: false,
C6: false,
C7: false,
C8: true
}
Success
C:\Development\YADAMU>
Expected results are 'true' for C1 thru C8. I can use this test to 'duck-type' the error and code a workaround for moment.