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';

[bkarlson]

well okay, that was a lame act on my side with mysql, yet this statement works with query but not with execute

const sqlVideos = `SELECT video_id FROM videos ORDER BY video_id DESC LIMIT ?`; 
pool.query(sqlVideosToProcess, [batchSize], async (err, rows) => {..

[sidorares]

@bkarlson probably not relevant to the topic, but why async callback?

[sidorares]

what error with execute? You have an error in your SQL syntax ?

[bkarlson]

@bkarlson probably not relevant to the topic, but why async callback?

I'm just refactoring old mysql code to mysql2 with promises, and stumbled upon this issue. Now wondering whether the rest of my queries would work...

[bkarlson]

what error with execute? You have an error in your SQL syntax ?

yes

[ruiquelhas]

@perry-mitchell what about the datatype of the sent column?

[ruiquelhas]

@sidorares from a preliminary analysis, I'm not able to reproduce this issue with the mysql CLI or other wire protocol implementations with prepared statement support. Again, this seems to be related to the changes in how prepared statements work with regards to type inference and whatnot. As described in the 8.0.22 server release notes.

Important Change: A prepared statement is now prepared only once, when executing PREPARE, rather than once each time it is executed. In addition, a statement inside a stored procedure is also now prepared only once, when the stored procedure is first executed. This change enhances performance of such statements, since it avoids the added cost of repeated preparation and rollback of preparation structures, the latter being the source of several bugs.

From what I can tell, one of the problems is that when encoding the COM_STMT_EXECUTE this driver is mapping all JavaScript number values to MYSQL_TYPE_DOUBLE and ignoring the type hint available in the COM_STMT_PREPARE Response.

In this specific case, if we force the driver to encode the value using a MYSQL_TYPE_LONGLONG type (which is the one suggested by the server after the prepare stage), it seems to work fine.

In any case, I'm still trying to figure out the entire thing. In the meantime, we can take this "offline" and discuss how can this be addressed from the client POV, because the server will own these changes.

[perry-mitchell]

what about the datatype of the sent column?

@ruiquelhas It's a timestamp

[sidorares]

Important Change: A prepared statement is now prepared only once, when executing PREPARE, rather than once each time it is executed.

@ruiquelhas can you clarify this?

That is also current behaviour or mysql2 driver: it sends COM_PREPARE only once for the first .execute() call and later if query is the same statement id is re used. I read server changelog the same way: when you do PREPARE command, after parsing it server uses body of the prepare argument and caches result ( previously each new PREPARE would generate new statement id ) - is that correct?

[sidorares]

mapping all JavaScript number values to MYSQL_TYPE_DOUBLE and ignoring the type hint available in the COM_STMT_PREPARE Response.

yes. Initially this driver was using string type for all parameters relying on server to do all the translation to actual expected types, and right now types are inferred from JS parameters ( and not on based on what we know server expects as a parameter ) - added in #353 and #705

also ref https://github.com/sidorares/node-mysql2/pull/516#issuecomment-459656963 ( I'm sure I discussed "we need to use types from prepare response to serialize parameters" somewhere before but can't find it )

[ruiquelhas]

Important Change: A prepared statement is now prepared only once, when executing PREPARE, rather than once each time it is executed.

@ruiquelhas can you clarify this?

That is also current behaviour or mysql2 driver: it sends COM_PREPARE only once for the first .execute() call and later if query is the same statement id is re used. I read server changelog the same way: when you do PREPARE command, after parsing it server uses body of the prepare argument and caches result ( previously each new PREPARE would generate new statement id ) - is that correct?

Yeah, the client flow is/was fine. That just mentions the change that happened in the server, which internally was always preparing statements once for each execution. That isn't the case anymore, and the statements are now prepared effectively once (only when PREPARE is called i.e. when COM_STMT_PREPARE is sent). This caused some changes in the way dynamic parameters used in prepared statements are resolved (in particular when it comes to derive their type). Up until now, you could get away with sending different parameter types in the COM_STMT_EXECUTE, and the statement would basically be re-prepared using that type. This isn't the case now, and even though I haven't explored it in full, I guess, at least, COM_STMT_EXECUTE will have to use the types hinted by the COM_STMT_PREPARE Response.

If you are interested, the full write-up of those server changes is available here.

[dalalmj]

Any update to this? Facing same error on 8.0.22 and on 8.0.19. Works with query, fails with execute (works properly on 8.0.14)

[sidorares]

@dalalmj no update on my side, reading @ruiquelhas comment I guess we need to make sure that types used for serialising parameters are exactly those returned by COM_STMT_PREPARE response ( right now we use dynamically whatever is parameters - number / string / buffer etc ) - might be wrong, need to double check

[ruiquelhas]

@dalalmj if it happens on 8.0.19 then it's probably not related to this specific issue. The changes I mentioned were only introduced by the MySQL server in 8.0.22.

[dalalmj]

@ruiquelhas, my mistake, I tried installing 8.0.19, but now see that apt-get always installs latest 8.0.22.

[dalalmj]

@sidorares, just FYI, instead of passing javascript number type, if value is passed as "string" (even though column in int), it works!

[abstain23]

mysql => 8.0.22 mysql2 => 2.2.5

const statement = SELECT moment.id as id, moment.content as content, moment.createAt createTime, moment.updateAt updataTime, JSON_OBJECT('id', users.id, 'username', users.username) author FROM moment LEFT JOIN users ON moment.user_id = users.id LIMIT ? OFFSET ?;

connection.execute(statement, [size + "", page + ""])

replace number with characters

[damianobarbati]

So we can't use node-mysql2 + prepared statements on mysql 8.0.22, is this correct? :stumble:

@YangJ0605 did you actually apply some kind of monkey patch or did you rewrite all of your queries?

@sidorares my current workaround:

        const originalQuery = connection.execute;
        connection.execute = async function(...args) {
            // array handling
            const { query, replacements } = array2list(...args);
            args[0] = query;
            args[1] = replacements;

            // workaround for 8.0.22 => https://github.com/sidorares/node-mysql2/issues/1239
            for (const key in args[1]) {
                const value = args[1][key];
                if (typeof value === 'number')
                    args[1][key] = String(value);
            }

            // optional query logging
            if (logFunc)
                logFunc(query2executable(query, replacements));

            const result = await originalQuery.apply(connection, args);
            return result;
        }

[mohsen-ghafouri]

mysql => 8.0.22 mysql2 => 2.2.5

const statement = SELECT moment.id as id, moment.content as content, moment.createAt createTime, moment.updateAt updataTime, JSON_OBJECT('id', users.id, 'username', users.username) author FROM moment LEFT JOIN users ON moment.user_id = users.id LIMIT ? OFFSET ?;

connection.execute(statement, [size + "", page + ""])

replace number with characters

await conn.execute(query, [skip.toString(), pageSize.toString()]); work for me, thanks

[marnixhoh]

Hi, I ran into this issue too after upgrading mysql to 8.0.23 . @YangJ0605 's solution works, but it is pretty suboptimal to always have to convert numbers to strings when executing queries. This problem does not appear when using mysql 8.0.21 ...

@sidorares Is there a fix for this on the roadmap? Thanks :)

[AnnAngela]

I just switched my cloud MySQL database server provider, and faced this same problem.

My code runs on mysql2@2.2.5 + mysql-client 8.0.25-0ubuntu0.20.04.1, and the new provider only has mysql server 8.0.18 - Source distribution, and I got the problem.

(But I DID NOT face this problem with mysql server 8.0.22 - Source distribution which providing from the previous provider, so it seems like something changed from 8.0.18 to 8.0.22)

The problem occured while running connection.execute with json:

await connection.execute("INSERT INTO `__sth__` (`id`, `time`, `__json__`) VALUES (NULL, ?, ?);", [new Date().toISOString(), ["__some_json__"]]);

and my workaround is using JSON.stringify:

await connection.execute("INSERT INTO `__sth__` (`id`, `time`, `__json__`) VALUES (NULL, ?, ?);", [new Date().toISOString(), JSON.stringify(["__some_json__"])]);

But this workaround is annoying and I hope there wiil be a solution without any workaround.

[ccwu]

This seems to be still be present in 8.0.24 which I recently moved to when I switched providers. It was working on a an older mySQL (of which the version I have to check). A version of @YangJ0605 where I .toString() parameters works and I have done an interim build. Not sure how the prepared statements work, but my gut tells me doing such is a risk for injection. So @YangJ0605's solution may be safer (a little messy when reading code)

Is there a sense of fix timeline or a target branch we can follow to be notified when merged.

[solsson]

This issue is present with MariaDB 10.5.10 as well. I have been unable to find which MariaDB release that introduced the corresponding change to prepared statements.

I tried @vlasky's interim fix but it's insufficient in my case. Like @AnnAngela's example we're passing JSON values.

Update: I extended @vlasky's patch with the equivalence of using JSON.stringify on object params. Currently evaluating the following monkeypatch applied at runtime to our docker image:

sed -i.org 's|number|number-MONKEYPATCH-https://github.com/vlasky/node-mysql2/commit/0357a637649d03b6c051f11fc317aca87d4cf4f9|' /usr/src/app/node_modules/mysql2/lib/packets/execute.js
sed -i 's|type = Types.JSON|// MONKEYPATCH-https://github.com/sidorares/node-mysql2/issues/1239#issuecomment-840986787 type = Types.JSON|' /usr/src/app/node_modules/mysql2/lib/packets/execute.js

[wen-kai]

replacing our mysql2 references with @vlasky's interim fix was the quickest solution to avoid touching all prepared statement references/arguments in our DB layer.

looking forward to reverting back to the original package when this breaking change gets fixed 🙏

[marnixhoh]

I just noticed that this error only occurs for me in the LIMIT and OFFSET clauses. I thought that these would expect integers, but they apparently do not.

At first I thought all integers would have to be cast to strings, but this turns out to NOT be the case. Again only the LIMIT and OFFSET clauses. Hope this helps someone else realize that fixing this across a code base is not a huge task.

[vlasky]

I have discovered that my interim fix has issues, at least when tested with Percona Server 8.0.23, Percona XtraDB Cluster 8.0.23 and presumably that same official MySQL version.

Having looked at the general query log, it's clear that the server is frequently unnecessarily re-preparing statements. This causes a performance hit and a constant increase in prepared statement handles, as shown by status variable Prepared_stmt_count.

It seems that the proper solution needs to be implemented, in which node-mysql2 respects the the type hint provided in the COM_STMT_PREPARE response and converts data type of the parameters accordingly.

[pintarj]

As @ruiquelhas has stated all the numbers are passed by the driver as DOUBLE types. I know, that JavaScript sucks at dealing with integers, but there is a range of integers that can be safely represented using JavaScript's number.

My proposal is to pass those safe integers as integers (therefore LONGLONG) and not as doubles. So the current code (lib/packets/execute.js) that now is:

case 'number':
  type = Types.DOUBLE;
  length = 8;
  writer = Packet.prototype.writeDouble;
  break;

would become something like:

case 'number':
  if (Number.isSafeInteger(value)) {
    type = Types.LONGLONG;
    length = 8;
    writer = Packet.prototype.writeInt64; // not implemented yet
  } else {
    type = Types.DOUBLE;
    length = 8;
    writer = Packet.prototype.writeDouble;
  }
  break;

This would solve this issue (I've tested locally) and maybe prevent many other ones. Am I missing something? What do you think?

[sidorares]

@pintarj I'm planning to start working on this soon, the intention is to use type from prepare response type hints - in your example prepare likely returned integer type for that parameter

[alisson-acioli]

Hello, is this problem still persisting? I am passing this now on 08/2023

[sidorares]

@alisson-acioli yes, to a degree. You can work around it by casting your data to a more compatible type ( for example, pass number as a string ). We plan to add api to set mysql type of a parameter explicitly

[mikiU2022]

this problems persist... it's dec/2023.....

[sidorares]

@mikiU2022 its still November on my calendar, but thanks for the ping

[BlockifyTechnologies]

      const sql = `SELECT * FROM DB.Users WHERE uid > ? ORDER BY uid LIMIT ?`;
      const values = [0, 50];

      try {
        const [results] = await connection.execute(sql, values);
        console.log("SQL Result: ", results);
      } catch (error) {
        console.error("Error executing SQL query:", error);
      }

Why would a query as simple as this give me the error of incorrect arguments ?

[sidorares]

@BlockifyTechnologies short explanation: currently argument type is inferred from JS type ( you can see mapping here ). Previously this worked fine as the server was able to convert to type actually expected by the statement, but after version 8.0.22 this behaviour changed, see https://github.com/sidorares/node-mysql2/issues/1239#issuecomment-718827355

Workaround for you right now: force it to be sent as string, const values = ['0', '50'];. In the future we'll add ability to explicitly set parameter type, the api would be something like this: const values = [mysql.types.LONGLONG(0), mysql.types.LONGLONG(50)]; - that way 0 and 50 are sent as LONGLONG instead of default DOUBLE. Also we plan to potentially change default type to be the one returned from prepare call, but this can be unreliable ( execute('SELECT ? as data') would have a type, likely LONG, set in a response but in reality its "unknown type" )

[BlockifyTechnologies]

@sidorares const values = [mysql.types.LONGLONG(0), mysql.types.LONGLONG(50)]; this seems like the perfect path to take. Hope this won't take too long! Thanks

[perry-mitchell]

We wrote a query wrapper in house to do this automatically.. and haven't had to think about it since. This particular problem can be abstracted away somewhat easily :)