Query doesn't get committed after a failed pool.query() that had a START TRANSACTION in it

[arikw]

Hello, I'm executing 2 queries as follows:

const mysql = require('mysql2/promise');

const pool = mysql.createPool(poolConfig = {
   // ...
   multipleStatements: true
});

await pool.query(/*sql*/`
  START TRANSACTION;

  -- something that causes an SQL error like a duplicate entry
  -- ...

  COMMIT;
`);

await pool.query(/*sql*/`
  INSERT INTO example SET foo = 'bar';
`);

The 1st query fails as expected and (to the best of my knowledge) running pool.query() with START TRANSACTION should automatically rollback on failure.

The problem is that the 2nd query doesn't get committed to the DB until another transaction happens (say until running again the 1st query).

Is this a bug or an expected behavior?

[sidorares]

yes if I understand your code correctly this is expected. the pool.query() is a shortcut for "get connection from the pool; perform query; release connection back to the pools list of idle connections", so the second query is likely happening in a connection different from 1st query. Why do you expect it to be auto committed - is that currently configured for all connections somehow? ( SET autocommit = 1 or some other means )

[arikw]

By "committed" I mean that I'm expecting the insertion that happens in the 2nd query to appear immediately in the DB. Yes, autocommit is enabled.

So if I have a failed transaction and then I'm running an insertion query without a transaction (no explicit START TRANSACTION), I'm not seeing any changes in the DB until I'm running another query with\without a transaction.

If I restart my node server and only run the 2nd query, the changes appear immediately in the DB and I'm seeing that the query() returns a ResultSetHeader with a serverStatus: 2 which means SERVER_STATUS_AUTOCOMMIT flag is on (see all flags here). If I'm running the 2nd query (insertion without a transaction) after the 1st one (transaction + failure), I'm seeing serverStatus: 3, which means SERVER_STATUS_AUTOCOMMIT and SERVER_STATUS_IN_TRANS flags are on.

[sidorares]

I'm not sure what's server behaviour in that case. The driver sends multi statements query in one COM_QUERY command, from your description it seems like transaction is not aborted on error and next query ( which can be on the same or on the different connection, in your example it's probably same connection ) is executed as part of the same transaction.

[sidorares]

@arikw can you try answer to this SO question: https://stackoverflow.com/questions/19905900/mysql-transaction-roll-back-on-any-exception

basically it looks like it confirms my suspicion ( sql error does not automatically rolls back transaction unless explicitly configured to do so )

[arikw]

@sidorares thanks for the suggestion. Instead of introducing a special PROCEDURE to our system, as suggest in SO, I'm running a query('ROLLBACK;') when query() returns with a failure, and that solved the issue.

Is there a way to know that the last error occurred while executing a transaction and avoid running ROLLBACK if not needed?

[sidorares]

Is there a way to know that the last error occurred while executing a transaction and avoid running ROLLBACK if not needed?

just wrap your queries with try / catch and only do rollback in catch section