Probably -VE Testcase : Error: Cannot read properties of undefined (reading 'on') at PromisePoolConnection.query (C:\Development\YADAMU\src\node_modules\mysql2\promise.js:94:22)

[markddrake]

Migrating from mysql to mysql2.

Attempting to use INFILE LOCAL to load a file into a staging table

Code

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) => {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() {
	  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 "YADAMU_STAGING"("DATA" JSON)`);
        results = await this.executeSQL(`LOAD DATA LOCAL INFILE 'x:/stagingArea/export/json/oracle/HR.json' INTO TABLE "YADAMU_STAGING" FIELDS ESCAPED BY ''`);
 	    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\infileExample.js
Pool Created
Connection obtained
Error: Cannot read properties of undefined (reading 'on')
    at PromisePoolConnection.query (C:\Development\YADAMU\src\node_modules\mysql2\promise.js:94:22)
    at Test.executeSQL (file:///C:/Development/YADAMU/src/scratch/mysql/infileExample.js:93:50)
    at Test.test (file:///C:/Development/YADAMU/src/scratch/mysql/infileExample.js:107:30)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: undefined,
  errno: undefined,
  sql: undefined,
  sqlState: undefined,
  sqlMessage: undefined
}
Success
C:\Development\YADAMU>

Environment

C:\Development\YADAMU>node -v
v20.7.0

C:\Development\YADAMU>cd src

C:\Development\YADAMU\src>npm ls
[email protected] C:\Development\YADAMU\src
+-- @aws-sdk/[email protected]
+-- @aws-sdk/[email protected]
+-- @azure/[email protected]
+-- @electron/[email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- ibm_db_electron@npm:[email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
+-- [email protected]
`-- [email protected]

MySQL 9.0.1 from official docker container.

[markddrake]

Sure I've missed something simple but can't see what.

[wellwelwel]

Hi @markddrake, based on related issues, what happens if you return the content from createReadStream?

- infileStreamFactory : (path) => {fs.createReadStream(path)}
+ infileStreamFactory : (path) => fs.createReadStream(path)

[markddrake]

That fixed it - Thank you. I've been able to run my testcase, and my real-world regression tests with that change made.

It was blindly obvious once you pointed that out. That said maybe it would be better if the code checked that it has a valid stream before starting to attach events to it it, and throw a more meaningful error if what it gets is not a stream - minor enhancement I know.

[markddrake]

I think there's an enhancement request for a better error message here.

[wellwelwel]

@markddrake, would you like to contribute? The message could be based on how mysql handles this behavior.

[markddrake]

I don't think (haven't looked) that mysql has to deal with this particular issue. AFAIK The whole inputStreamFactory() concept is specific to mysql2.