I am working on a nodeJs project and using a npm package mysql2 for mysql database.
My MySql Configuration:-
let mysql = MYSQL.createConnection({
host: `${config.mysql.host}`,
user: `${config.mysql.user}`,
password: `${config.mysql.password}`,
database: `${config.mysql.db}`,
});
When I am using a query
async function getUsers ({pageNumber}) { // suppose pageNumber = 1
const [result] = await mysql.execute(
`SELECT * FROM user LIMIT ?,20;`,
[pageNumber]
);
return result;
}
The above code is working fine. But when i am trying to multiply any number with pageNumber,it throws error Error: Incorrect arguments to mysqld_stmt_execute
Ex.
async function getUsers ({pageNumber}) { // suppose pageNumber = 1
pageNumber = pageNumber * 20; // here we multiply 20 with pageNumber (20 is the row limit)
const [result] = await mysql.execute(
`SELECT * FROM user LIMIT ?,20;`,
[pageNumber]
);
return result;
}
above code throws the error.
Note:- type of pageNumber is number not string.
Please help.
This appears to be a bug introduced in MySQL version 8.0.22, I'm getting the same error after updating from 8.0.19. Not sure about the cause or a proper solution, in the interim I'm mapping the values to strings, as per https://github.com/sidorares/node-mysql2/issues/1239#issuecomment-718471799, which seems to work.
is like a buggy, use interpolation like this(yes is a bad practice, but you can make a solution meanwhile) :
const [result] = await mysql.execute(
`SELECT *
FROM user
LIMIT ${pageNumber},20;`
);
and, if the variable is a string, put ' ' around the interpolation
`SELECT * FROM user WHERE xname = '${x}' ;`