mysql2/promise: INSERT statement not valid in transaction query

Question

Thanks for your time. I'm having trouble with creating a transaction query using the mysql2/promise package.

Here's the query:

    await db.execute(`START TRANSACTION`);
    await db.execute(`INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)`, [...userDetails]);
    await db.execute(`INSERT INTO account VALUES (?, ?, ?, ?, ?, ?)`, [...accountDetails]);
    await db.execute(`COMMIT`);

And here's the error I get:

Error: This command is not supported in the prepared statement protocol yet
code: 'ER_UNSUPPORTED_PS',
  errno: 1295,
  sql: 'START TRANSACTION',
  sqlState: 'HY000',
  sqlMessage: 'This command is not supported in the prepared statement protocol yet'

I'm wondering if has something to do with my querying? I believe INSERT statements should be perfectly fine in a transaction block. I've also tried combining each query into one string, but that doesn't seem to work either.

The error is with `start transaction`, not the inserts. And is this MySQL or MariaDB? — Schwern, Jan 01 '22 at 18:31
@Schwern MySQL 8.0, but thanks for answering the question already! — Shockch4rge, Jan 01 '22 at 19:03

score 7 · Accepted Answer · answered Jan 01 '22 at 18:48

MySQL limits which statements can be done in prepared statements, start transaction is not allowed. See SQL Syntax Allowed In Prepared Statements and here is a demonstration.

execute is always using and caching prepared statements. This is good if the query is complex (MySQL doesn't have to parse it every time) or takes arguments (for security).

However, if your query is simple and does not take any arguments there's no need to prepare it. Use query which just runs the SQL. This both avoids the error you're getting, and it avoids filling up the prepared statement cache.

    await db.query(`START TRANSACTION`);
    await db.execute(`INSERT INTO user VALUES (?, ?, ?, ?, ?, ?)`, [...userDetails]);
    await db.execute(`INSERT INTO account VALUES (?, ?, ?, ?, ?, ?)`, [...accountDetails]);
    await db.query(`COMMIT`);

score 0 · Answer 2 · answered May 03 '23 at 21:56

I think that the answer to the question is correct but the code (example) is not and should not be used. Although the statements might work, it does not create a valid transaction because a new connection to the database might have been made.

The correct way would be to get a connection handle:

let conn = await this.getConnection();
await conn.query('START TRANSACTION');
// do your stuff here
await conn.execute('...')'
await conn.query('COMMIT');
conn.release();

This ensures a valid transaction (the START and COMMIT should be on the same connection). Calling directly the database object does not give you that guarantee unless you specified only one connection.

mysql2/promise: INSERT statement not valid in transaction query

2 Answers2