NodeJS, MariaDB pool connection commit not working

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!

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

Does it work without using a pool?

– robertklep
Nov 14 '18 at 13:29

no, doesn't work either:/

– mcAngular2
Nov 14 '18 at 13:32

only thing working was creating a new connection for the query, but this seems strange to me

– mcAngular2
Nov 14 '18 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
Nov 14 '18 at 13:41

using conn.query("commit") fixed it:)

– mcAngular2
Nov 14 '18 at 13:47

add a comment |

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!

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

Does it work without using a pool?

– robertklep
Nov 14 '18 at 13:29

no, doesn't work either:/

– mcAngular2
Nov 14 '18 at 13:32

only thing working was creating a new connection for the query, but this seems strange to me

– mcAngular2
Nov 14 '18 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
Nov 14 '18 at 13:41

using conn.query("commit") fixed it:)

– mcAngular2
Nov 14 '18 at 13:47

add a comment |

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!

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

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!

javascript node.js database promise mariadb

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

edited Nov 14 '18 at 16:15

Amit Prasad

573315

edited Nov 14 '18 at 16:15

Amit Prasad

573315

edited Nov 14 '18 at 16:15

Amit Prasad

573315

asked Nov 14 '18 at 13:26

mcAngular2

1169

asked Nov 14 '18 at 13:26

mcAngular2

1169

asked Nov 14 '18 at 13:26

mcAngular2

1169

Does it work without using a pool?

– robertklep
Nov 14 '18 at 13:29

no, doesn't work either:/

– mcAngular2
Nov 14 '18 at 13:32

only thing working was creating a new connection for the query, but this seems strange to me

– mcAngular2
Nov 14 '18 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
Nov 14 '18 at 13:41

using conn.query("commit") fixed it:)

– mcAngular2
Nov 14 '18 at 13:47

add a comment |

Does it work without using a pool?

– robertklep
Nov 14 '18 at 13:29

no, doesn't work either:/

– mcAngular2
Nov 14 '18 at 13:32

only thing working was creating a new connection for the query, but this seems strange to me

– mcAngular2
Nov 14 '18 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
Nov 14 '18 at 13:41

using conn.query("commit") fixed it:)

– mcAngular2
Nov 14 '18 at 13:47

Does it work without using a pool?

– robertklep
Nov 14 '18 at 13:29

no, doesn't work either:/

– mcAngular2
Nov 14 '18 at 13:32

only thing working was creating a new connection for the query, but this seems strange to me

– mcAngular2
Nov 14 '18 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
Nov 14 '18 at 13:41

using conn.query("commit") fixed it:)

– mcAngular2
Nov 14 '18 at 13:47

add a comment |

2 Answers
2

active

oldest

votes

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 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

1

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

– Diego Dupin
Nov 14 '18 at 17:22

|
show 3 more comments

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 '18 at 8:42

Diego Dupin

22125

add a comment |

Your Answer

StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});

}
});

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53301324%2fnodejs-mariadb-pool-connection-commit-not-working%23new-answer', 'question_page');
}
);

Post as a guest

Name

Required, but never shown

2 Answers
2

active

oldest

votes

2 Answers
2

active

oldest

votes

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 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

1

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

– Diego Dupin
Nov 14 '18 at 17:22

|
show 3 more comments

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 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

1

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

– Diego Dupin
Nov 14 '18 at 17:22

|
show 3 more comments

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 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

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 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

edited Nov 14 '18 at 14:12

answered Nov 14 '18 at 13:51

robertklep

137k18234244

answered Nov 14 '18 at 13:51

robertklep

137k18234244

answered Nov 14 '18 at 13:51

robertklep

137k18234244

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

1

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

– Diego Dupin
Nov 14 '18 at 17:22

|
show 3 more comments

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

1

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

– Diego Dupin
Nov 14 '18 at 17:22

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

– mcAngular2
Nov 14 '18 at 13:53

@mcAngular2 see edit

– robertklep
Nov 14 '18 at 13:56

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

– mcAngular2
Nov 14 '18 at 14:01

@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.

– robertklep
Nov 14 '18 at 14:03

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

– Diego Dupin
Nov 14 '18 at 17:22

|
show 3 more comments

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 '18 at 8:42

Diego Dupin

22125

add a comment |

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 '18 at 8:42

Diego Dupin

22125

add a comment |

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 '18 at 8:42

Diego Dupin

22125

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 '18 at 8:42

Diego Dupin

22125

answered Nov 15 '18 at 8:42

Diego Dupin

22125

answered Nov 15 '18 at 8:42

Diego Dupin

22125

answered Nov 15 '18 at 8:42

Diego Dupin

22125

add a comment |

draft saved

draft discarded

Thanks for contributing an answer to Stack Overflow!

Please be sure to answer the question. Provide details and share your research!

But avoid …

Asking for help, clarification, or responding to other answers.

Making statements based on opinion; back them up with references or personal experience.

To learn more, see our tips on writing great answers.

draft saved

draft discarded

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Sign up or log in

StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});

Post as a guest

Name

Required, but never shown

Name

Required, but never shown

Name

Required, but never shown

This page is only for reference, If you need detailed information, please check here

搜尋此網誌

Ndtyjky