Incorrect arguments to mysqld_stmt_execute (MySQL 8.0.22)

[perry-mitchell]

Hi, love this library!

Have been using it successfully across a large number of projects and it's worked flawlessly for me so far. Just now I've seen that I have a single test case failing on the CI server (Gitlab), MySQL 8, failing with the following error:

VError: Failed executing query: \"SELECT * FROM message WHERE chat_id = ? ORDER BY sent DESC LIMIT ? OFFSET ?\" [01ensmg6m4dea0gh7gsjgaa5gb, 50, 0]: Incorrect arguments to mysqld_stmt_execute
    at ConnectionPool.<anonymous> (/data/node_modules/@my-company/mysql-connection-pool/dist/ConnectionPool.js:128:27)
    at Generator.throw (<anonymous>)
    at rejected (/data/node_modules/@my-company/mysql-connection-pool/dist/ConnectionPool.js:6:65)
    at processTicksAndRejections (internal/process/task_queues.js:97:5)
caused by: Error: Incorrect arguments to mysqld_stmt_execute
    at PromiseConnection.execute (/data/node_modules/mysql2/promise.js:110:22)
    at ConnectionPool.<anonymous> (/data/node_modules/@my-company/mysql-connection-pool/dist/ConnectionPool.js:117:58)
    at Generator.next (<anonymous>)
    at fulfilled (/data/node_modules/@my-company/mysql-connection-pool/dist/ConnectionPool.js:5:58)

Now this error is wrapped by my own logic, but the cause I think it quite clear: Incorrect arguments to mysqld_stmt_execute. The query being passed to the execute method on the mysql2/promise library is SELECT * FROM message WHERE chat_id = ? ORDER BY sent DESC LIMIT ? OFFSET ? and the values are ["01ensmg6m4dea0gh7gsjgaa5gb", 50, 0]. For some reason this only fails on the CI, and not locally on any of my (or my colleagues') machines.

If I change this to query instead of execute, it works on the CI. If I form this query manually using the values that were passed-in, and run it, it also works.

Any idea what's happening here? Is there some failure in how the parameters are being transferred to the MySQL service before being inserted?

EDIT 1: I'm also using v2.2.5 of the library

EDIT 2: Seems after the suggestions made here that the issue, at least for me, is only with mysql2 and MySQL server 8.0.22. 8.0.21 seems to work fine.

[perry-mitchell]

Not sure it helps, but this is the usage:

export async function getChatMessages(
    chatID: string,
    limit: number,
    offset: number = 0,
    connection = getConnection()
): Promise<Array<MessageSlow>> {
    const [
        rows
    ] = await connection.execute(
        `SELECT * FROM message WHERE chat_id = ? ORDER BY sent DESC LIMIT ? OFFSET ?`,
        [chatID, limit, offset]
    );
    // Snip
}

[sidorares]

Could you try to prepare from mysql cli?

mysql> PREPARE stmt1 FROM 'SELECT * FROM message WHERE chat_id = ? ORDER BY sent DESC LIMIT ? OFFSET ?';

trying to make sure sql syntax is valid ( there are limitation on to what can be a placeholder in PS )

[sidorares]

hm, re reading your text, it works on local machine. Can you double check your local mysql server version is exactly the same as on CI?

[dimitar3web]

I have the same problem since i updated mysql from 8.0.21 -> 8.0.22. I am pretty sure that my queries are alright cause i didnt have any issues before version upgrade. I`ve got ubintu (20.04) which I updated yesterday and after installing the update this error started. I tested the queries on older version (before yesterdays update) and it seems to work without any problem. Any advice?

A workaround that I found yesterday was, pasring the bindedparms to string as such: bindedparams.map(i => i.toString()) ; If the params are left as integers the error appears

{
code: 'ER_WRONG_ARGUMENTS',
errno: 1210,
sqlState: 'HY000',
sqlMessage: 'Incorrect arguments to mysqld_stmt_execute'
}

[perry-mitchell]

Ok, that was a good recommendation @sidorares - I was not using the same version locally. I've updated my local to MySQL 8.0.22 and now it fails on many queries:

VError: Failed executing query: "
    SELECT
        m.chat_id,
        MAX(m.sent) AS sent,
        c.channel,
        c.is_completed
    FROM message AS m
    INNER JOIN chat AS c ON c.id = m.chat_id
    WHERE
        sent <= ? AND
        c.is_completed = 0
    GROUP BY m.chat_id
    LIMIT ?
" [2020-10-29 10:11:45, 10]: Incorrect arguments to mysqld_stmt_execute

Variables above being ["2020-10-29 10:11:45", 10]

[sidorares]

@ruiquelhas could you comment on this 8.0.21 -> 8.0.22 incompatibility?

[ruiquelhas]

Yeah, there were some major changes with regards to how prepared statements work in the server (type inferences and whatnot), and unfortunately some specific queries might break. I'll look at this specific example as well. Thanks for the mention @sidorares.

[ruiquelhas]

@perry-mitchell what's the column datatype of chat_id? Just so I can re-create the exact same scenario.

[perry-mitchell]

@ruiquelhas chat_id is a string, 26 chars. The values being inserted in that first query are ["01ensmg6m4dea0gh7gsjgaa5gb", 50, 0].

Confirming that 8.0.21 works for me, and 8.0.22 presents these prepare statement errors.

[ruiquelhas]

@ruiquelhas chat_id is a string, 26 chars. The values being inserted in that first query are ["01ensmg6m4dea0gh7gsjgaa5gb", 50, 0].

Confirming that 8.0.21 works for me, and 8.0.22 presents these prepare statement errors.

@perry-mitchell I mean on the table column itself. The MySQL column datatype. Which one of these?

[perry-mitchell]

@ruiquelhas varchar(30) sorry..

[bkarlson]

Just stumbled upon the same problem. A statement works fine when using query, but fails with execute:

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 '? ORDER BY video_id DESC LIMIT ?' at line 1

[bkarlson]

as a matter of fact, something's weird is on mysql side, working query cannot be prepared:

mysql> PREPARE stmt SELECT video_id FROM videos ORDER BY video_id DESC LIMIT 10;
ERROR 1064 (42000): 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 'SELECT video_id FROM videos ORDER BY video_id DESC LIMIT 10' at line 1


mysql> SELECT video_id FROM videos ORDER BY video_id DESC LIMIT 10;
+-----------+
| video_id  |
+-----------+
[...]
+-----------+
10 rows in set (0.00 sec)

[sidorares]

@bkarlson try this:

mysql> PREPARE stmt1 FROM 'SELECT video_id FROM videos ORDER BY video_id DESC LIMIT 1';