Error: Incorrect arguments to mysqld_stmt_execute

[iuroc]

router.get('/list', async (req, res) => {
    const conn = await pool.getConnection()
    const stat = await conn.prepare('SELECT * FROM `reply_app` LIMIT ? OFFSET ?')
    const [result] = await stat.execute([2, 0]) as [RowDataPacket[], any]
    res.send(result)
    conn.release()
})

After changing both 'page' and 'pageSize' to string types, it works, but shouldn't they be passed as numbers originally? This is strange.

router.get('/list', async (req, res) => {
    const conn = await pool.getConnection()
    const stat = await conn.prepare('SELECT * FROM `reply_app` LIMIT ? OFFSET ?')
    const [result] = await stat.execute(['2', '0']) as [RowDataPacket[], any]
    res.send(result)
    conn.release()
})

[sidorares]

Likely related to #1239

The root cause is that after statement expects INT/LONGINT parameter ( try to log results of the prepare() - it returns expected parameter types ) and we send type based on JS value passed, JS Number is sent as mysql DOUBLE, which triggers mysqld_stmt_execute error. I guess when types are completely off ( string vs int ) server performs conversion but not for double -> int. This behaviour changed from version 8.0.22

We do plan to give more control over how execute() parameters are serialised, see #1239 (comment)

[erman999]

I was having this issue recently. It returned me ER_WRONG_ARGUMENTS as an error code. Strangely it wasn't happening in all numeric parameter queries but some of them. I was able to run the query without any error from mysql console but having issue while using mysql2 package. First I thought it was causing from MySQL version but then I realised sending numeric parameters may cause this issue. I type casted query parameters from number to string and everything works fine now.

Solution: Use String() function for numeric parameters.

Example

If below parameters ['Morty', 14] return error

const [rows, fields] = await connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Morty', 14]
);

Type cast numeric values to string as ['Morty', String(14)]

const [rows, fields] = await connection.execute(
  'SELECT * FROM `table` WHERE `name` = ? AND `age` > ?',
  ['Morty', String(14)]
);