Unexpected Behavior with Unnamed Placeholders When namedPlaceholders is Set to True

[josenobile]

I encountered an unexpected behavior in the mysql2 library related to the use of unnamed placeholders when the namedPlaceholders configuration is set to true.

Expected Behavior: According to the documentation, named placeholders are converted to unnamed ? on the client, and it's implied that unnamed placeholders should work regardless of the namedPlaceholders configuration. I expected that a query using unnamed placeholders would function correctly with both namedPlaceholders: true and namedPlaceholders: false.

Observed Behavior: When namedPlaceholders is set to false, the query functions as expected. However, when namedPlaceholders is set to true, the query fails with a syntax error, even though I'm not using any named placeholders in the query.

Code Example: Here's a snippet of the code in question:

async function addLesson(body) {
  try {
    const rows = await db.mysql.writter2.query(`insert into lessons SET ?`, body);
    return rows[0];
  } catch (err) {
    throw err;
  }
}

I am executing this function with the following data:

{
  disciplineId: 60727,
  establishmentId: 2213,
  // Other fields...
}

Error Message: With namedPlaceholders: true, the following error is produced: 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 'NULL' at line 1 code: 'ER_PARSE_ERROR', errno: 1064, sql: 'insert into lessons SET NULL', sqlState: '42000',

Environment: MySQL Server Version: 5.7.38 mysql2 Library Version: 3.6.0 Node.js Version: 20.5.1 Operating System: Fedora release 39 (Rawhide) / 6.1.21.2-microsoft-standard-WSL2+

This unexpected behavior is causing issues in our development and requires a workaround. Could you please provide some insight into whether this is a bug or an undocumented behavior? Any guidance or fixes would be highly appreciated. Thank you!

[tfrancois]

I ran into this very same issue and although I cannot figure out why it happens, I was able to bypass it by NOT using the object variation of the the SQL query operation.

So instead of:

...connection.query({ "sql": "<<SOME SQL HERE>>", "values": [ true, false ], "namedPlaceholders": false })

I reverted to the normal positional-based parameter arguments:

...connection.query("<<SOME SQL HERE>>", [ true, false ])

...worked for me without error. This however does not afford me the ability to modify the namedPlaceholders property setting at query level but at least the library doesn't reject with the Error code: 1064; SQLState: 42000 error(s). Hope this helps.