Problems with promise based pools after updating to 3.6.0

[BauerPh]

I get this Error after updating to 3.6.0:

/home/phx/phxAssistant/api/node_modules/mysql2/promise.js:356
    const localErr = new Error();
                     ^
Error: The first argument must be of type string or an instance of Buffer, ArrayBuffer, or Array or an Array-like Object. Received undefined

it works fine with the previous version 3.5.2

Here is my call:

const pool = require('mysql2')
  .createPool({
    host: process.env.DB_HOST,
    user: process.env.DB_USER,
    password: process.env.DB_PASS,
    database: process.env.DB_DATABASE,
    connectionLimit: 5,
    multipleStatements: true,
    dateStrings: true,
    typeCast: function (field, next) {
      if (field.type === 'TINY' && field.length == 1) {
        return field.string() === '1'; // true / false
      } else if (field.type === 'BLOB' && (field.length == 4294967295 || field.db === '')) {
        let value = field.string();
        if (/^-?\d+$/.test(value)) return value;
        try {
          let res = JSON.parse(value);
          return res;
        } catch (e) {
          return value;
        }
      }
      return next();
    },
  })
  .promise();

exports.query = async function (...args) {
  return (await pool.query(...args))[0];
};

[sidorares]

Hi @BauerPh could you add some code to your example that can replicate the issue on a fresh database so I can debug it locally? Thanks

[jgonera]

@sidorares I'm running into the same issue when using mysql2 with sql-template-tag package. mysql2 3.5.2 works, 3.6.1 results in the error mentioned above. Example of problematic code:

import mysql from 'mysql2/promise'
import sql from 'sql-template-tag'

const db = mysql.createPool(...)
await db.query(sql`SELECT 1`)

If I do the following, 3.6.2 works though:

import mysql from 'mysql2/promise'
import sql from 'sql-template-tag'

const db = mysql.createPool(...)
const query = sql`SELECT 1`
await db.query({ sql: query.sql, values: query.values })

[sidorares]

@jgonera looks like a side effect of https://github.com/sidorares/node-mysql2/pull/2159/files#diff-f35455f30798a56117f979b6ad64a99cbc915318f08795935d68c80ab2223743R623-R626

sql-template-tag uses getters to return different 'view' of the query for mysql and pg - https://github.com/blakeembrey/sql-template-tag/blob/ee664d608d8ca82c2eb01302e9efd00b1b02b6fa/src/index.ts#L76-L81

simpler example demonstrating the issue:

import sql from 'sql-template-tag'

const query = sql`SELECT ${"test"}`
console.log('via getters: ', query.sql, query.values)
const options = { ...sql }
console.log('after spread: ', options.sql, options.values)

outputs

via getters:  SELECT ? [ 'test' ]
after spread:  undefined undefined

Possible solution would be to change the code in connection.js/createQuery from

options = {
        ...options,
        ...sql
      };

to

options = {
        ...options,
        sql: sql.sql, 
        values: sql.values
      };

Do you want to test that and create PR @jgonera ?

[BauerPh]

your solution worked for me. PR created, please review.

I only changed it in connection.js/createQuery,
but maybe it also needs to be changed in "connection.js/execute"??:
https://github.com/sidorares/node-mysql2/pull/2159/files#diff-f35455f30798a56117f979b6ad64a99cbc915318f08795935d68c80ab2223743R623-R626

[sidorares]

yes, probably needs to be added to execute as well

[BauerPh]

fixed v3.6.2
#2238

[jgonera]

@BauerPh thank you for the fix and sorry for not getting to it earlier!

[fiacc]

Hi, the issue still seems to exist in 3.6.2 when used with https://github.com/nearform/sql . The fix in #2238 explicitly sets the sql option but not the values option.

const run = async () => {

    const SQL = require('@nearform/sql');
    const { createConnection } = require('mysql2/promise')

    const con = await createConnection(config);
    const id = 1;
    const sql = SQL`
                SELECT * FROM mytable WHERE ID = ${id}
    `;

    await con.query(sql)
    await con.close()
};

run();
/*
Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1
    at PromiseConnection.query (/node_modules/mysql2/promise.js:94:22)
    at run (/repro.js:15:15)
    at process.processTicksAndRejections (node:internal/process/task_queues:95:5) {
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'SELECT * FROM mytable WHERE ID = ?',
  sqlState: '42000',
  sqlMessage: "You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1"
}
*/

[sidorares]

@fiacc could you submit a PR with values added as well?

[sidorares]

I'm still open to a first class tagged template literal support out of the box, see #1539 ( but also #1539 (comment) )