Node-mysql2: Problem using execute and array parameters

[HugoMuller]

Hi,

mysql2 is a good alternative to mysql, but there is a little bug I guess.

I try to prepare a statement with an array parameter, that should be turned into a list. It works with query but not with execute.

const ids = [1, 2, 3];
connection.query('SELECT id FROM mytable WHERE id IN (?)', [ids]);
// that's okay, I get 3 rows
[
  TextRow { id: 1 },
  TextRow { id: 2 },
  TextRow { id: 3 }
]

connection.execute('SELECT id FROM mytable WHERE id IN (?)', [ids]);
// I get only 1 row
[
  TextRow { id: 1 }
]

It seems that only the first element of the array is taken into account.

I'm using mysql2 v1.1.2.

[sidorares]

This is the way prepared statements work, I think we should document that better

.execute() under the hood is doing prepare + execute commands

query vs execute step by step:

query:

• format sql on the client: 'SELECT id FROM mytable WHERE id IN (?)' + [ [1,2,3] ] becomes 'SELECT id FROM mytable WHERE id IN (1, 2, 3)'
• send COM_QUERY command with 'SELECT id FROM mytable WHERE id IN (1, 2, 3)'
• read fields + rows

execute:

• send COM_PREPARE command with SELECT id FROM mytable WHERE id IN (?) as query parameter
• read prepared statement result (id + parameters + result fields if known from query ). For this query there is one parameter ( one ? ). id is a number, usually starts with 1 for every connection ( not unique server-wise, you can't prepare statement in one connection and use in another )
• send COM_EXECUTE command using statement id + parameters. Parameters must be simple mysql types, and currently all parameters coerced to strings and serialised as strings (with exception for Buffer parameters, they sent as is). In your example is "please execute stmt with id 1 and one parameter which is a string "1,2,3"

Result from prepare step above is cached, with next execute commands with same query only 'COM_EXECUTE' part is performed

I agree that this is confusing but this is how prepared statement work. We should improve this with better documentation and maybe some parameter validation warnings/errors like _"hey, are you sure you want to send your {foo: 'bar'} parameter to prepared statement? It'll be sent as [Object object]!''

I'll keep this issue open to track improvements in documentation - feel free to suggest your

[BorePlusPlus]

Late to the party as usual, but is the issue essentially that you'd have to prepare new statement every time someone used an array of a different size?

[sidorares]

@BorePlusPlus not exactly. Prepared statements api does not turn array parameters into list unlike non-prepared version. You can work around this by providing as much placeholders as you have elements in the array but this will result in new prepared statement created on the server