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
yadamu@1.0.0 C:\Development\YADAMU\src
+-- @aws-sdk/client-s3@3.507.0
+-- @aws-sdk/lib-storage@3.507.0
+-- @azure/storage-blob@12.17.0
+-- @electron/remote@2.1.2
+-- bootstrap-icons@1.11.3
+-- bootstrap@5.3.2
+-- cookie-parser@1.4.6
+-- csv-parser@3.0.0
+-- electron-packager@17.1.2
+-- electron@31.3.1
+-- express-session@1.18.0
+-- express@4.18.2
+-- font-awesome@4.7.0
+-- ibm_db_electron@npm:ibm_db@3.2.3
+-- ibm_db@3.2.3
+-- install@0.13.0
+-- jquery@3.7.1
+-- mariadb@3.3.0
+-- mime-types@2.1.35
+-- mongodb@6.3.0
+-- mssql@11.0.1
+-- mysql@2.18.1
+-- mysql2@3.11.0
+-- npm@10.4.0
+-- oracledb@6.3.0
+-- pg-copy-streams@6.0.6
+-- pg-query-stream@4.5.3
+-- pg@8.11.3
+-- readable-stream@4.5.2
+-- snowflake-sdk@1.9.3
+-- uuid@10.0.0
`-- wkx@0.5.0

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.