Compatibility issue - Possible corruption of values with min and max values for a double. #2928

markddrake · 2024-08-07T02:40:32Z

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.

The text was updated successfully, but these errors were encountered:

markddrake · 2024-08-07T02:42:34Z

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

markddrake · 2024-08-11T15:54:04Z

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.

markddrake · 2024-09-28T19:06:56Z

Could i get an update on this issue, are you able to reproduce ?

markddrake mentioned this issue Oct 6, 2024

MySQL: Fetch Double as String. markddrake/YADAMU---Yet-Another-DAta-Migration-Utility#249

Closed

wellwelwel added the needs investigation label Oct 7, 2024

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

Compatibility issue - Possible corruption of values with min and max values for a double. #2928

Compatibility issue - Possible corruption of values with min and max values for a double. #2928

markddrake commented Aug 7, 2024 •

edited by wellwelwel

Loading

markddrake commented Aug 7, 2024 •

edited by wellwelwel

Loading

markddrake commented Aug 11, 2024

markddrake commented Sep 28, 2024

Compatibility issue - Possible corruption of values with min and max values for a double. #2928

Compatibility issue - Possible corruption of values with min and max values for a double. #2928

Comments

markddrake commented Aug 7, 2024 • edited by wellwelwel Loading

markddrake commented Aug 7, 2024 • edited by wellwelwel Loading

markddrake commented Aug 11, 2024

markddrake commented Sep 28, 2024

markddrake commented Aug 7, 2024 •

edited by wellwelwel

Loading

markddrake commented Aug 7, 2024 •

edited by wellwelwel

Loading