NodeJS, MariaDB pool connection commit not working

Question

I've searched already on the internet, but couldn't find a solution to my problem. I'm using NodeJS and a MariaDB with a pool connection. I get a connection from the pool, make a transaction (no errors) but I cannot see any chances inside my database. If I use the query without a transaction, just with pool.query(...) then it works fine. I know for just one query I wouldn't need a transaction, but I have just simplified the code for you.

pool.getConnection()
.then(conn =>{
    conn.beginTransaction()
        .then(() =>{
            return conn.query("UPDATE Users SET forename='Tom' WHERE user_id=8")
        })
        .then(()=>{
            console.log("commit")
            conn.commit()
            //conn.end() --> doesn't change anything

        })
        .catch((err)=>{
            console.log(err.message);
            conn.rollback()
    })
});

What's wrong here?

Thanks!

only thing working was creating a new connection for the query, but this seems strange to me — mcAngular2
– mcAngular2, Commented Nov 14, 2018 at 13:35
I agree. I assume that nothing is being logged by MariaDB either? Perhaps this issue is relevant: github.com/MariaDB/mariadb-connector-nodejs/issues/… (so instead of conn.commit() you should try and use conn.query('COMMIT')). And FWIW, I think that you still need to call conn.end() when you're done with the transaction, to release the connection back to the pool. — robertklep
– robertklep, Commented Nov 14, 2018 at 13:41

robertklep · Accepted Answer · 2018-11-14 14:12:20Z

1

Looks like this is a bug in the MariaDB driver, where conn.commit() doesn't actually commit. The bug is documented here.

In that comment, a workaround is suggested, by calling COMMIT manually:

conn.query('COMMIT');

Also make sure that you end the connection when you're done, to release the connection back into the pool:

.then(()=>{
  return conn.query("COMMIT").then(() => {
    return conn.end();
  });
})
.catch((err)=>{
  return conn.query("ROLLBACK").then(() => {
    conn.end();
    throw new Error(err)
  });
})

edited Nov 14, 2018 at 14:12

answered Nov 14, 2018 at 13:51

robertklep

205k38 gold badges421 silver badges413 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

mcAngular2 Over a year ago

yes that worked:) Where should I close the connections? Is this right?

mcAngular2 Over a year ago

does it also work like I did? Or is it neccessary to return conn.end()?

robertklep Over a year ago

@mcAngular2 it may work with your solution, but it would depend on how the MariaDB library is implemented. The reason why I use return conn.* is that that will cause the promises that are returned by each operation to be resolved before the next .then is executed, whereas using conn.query(...); conn.end() doesn't do that.

mcAngular2 Over a year ago

but how can I then know whether the connection was closed because of conn.rollback or conn.commit? I need to know whether everything worked or not

Diego Dupin Over a year ago

btw, this is jira.mariadb.org/browse/CONJS-52, and will be corrected in next release (in a day)

|

Diego Dupin · Accepted Answer · 2018-11-15 08:42:01Z

1

This is a bug, and as @robertklep indicate, a workaround is to execute a conn.query("COMMIT") command.

But is jira.mariadb.org/browse/CONJS-52, and is now corrected with the latest release (2.0.1)

answered Nov 15, 2018 at 8:42

Diego Dupin

1,3589 silver badges14 bronze badges

Collectives™ on Stack Overflow

NodeJS, MariaDB pool connection commit not working

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related