CODEWITHSUNDEEP
javascriptmysqlnode.jsdatabasenpm
Aman Gupta
Aman Gupta

Reputation: 172

Error: Incorrect arguments to mysqld_stmt_execute

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.

Upvotes: 6

Views: 7626

Answers (5)

Buddhi Gayan
Buddhi Gayan

Reputation: 31

I had a similar issue with sending LIMIT and OFFSET values! It seems like .execute() doesn't handle placeholders for LIMIT and OFFSET correctly. I fixed it by switching to .query() instead, and it worked fine.

Maybe try this,

const [result] = await mysql.query(`SELECT * FROM user LIMIT ?,20;`,
[pageNumber]);

Upvotes: 1

Soham Nimbalkar
Soham Nimbalkar

Reputation: 11

Not the Standard Solution, but you can try this way

const pageSize = 8; 
const offset = (pageNumber - 1) * pageSize;

const query = 
  select column_names 
  from table_name where 
  some_column = ? and 
  another_column = ? 
  limit ${pageSize} 
  offset ${offset};

const [result] = await connection.execute(query, [column1, column2]);

Upvotes: 0

Sebastien
Sebastien

Reputation: 615

I've had the same issue and the problem seems to be with the type of the parameters that in some way are passed to the statement as double instead of int. I've found the solution on this page: https://github.com/sidorares/node-mysql2/discussions/2652. I've cast the parameters to string and everything worked as expected.

Compared to your query, I've also had to use LIMIT x OFFSET y instead of LIMIT x,y

Upvotes: 0

nativelectronic
nativelectronic

Reputation: 862

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

Upvotes: 0

hanetar
hanetar

Reputation: 51

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.

Upvotes: 5

Related Questions