March 5, 2023, 12:18pm
The idea is:
Add transaction features to the MySQL node like
BEGIN (start transaction)
maybe even add the ability to lock / unlock tables
My use case:
My use case right now is to update tables without having a few ms between deleting the table and reinserting all rows.
But there will be more in depth use cases for me as soon as I create more workflows which communicate with our databases.
I think it would be beneficial to add this because:
To be able to roll back certain MySQL queries or executing multiple MySQL commands at once.
Any resources to support this?
Are you willing to work on this?
Afaik you are already working on an overhaul for the MySQL node, but if there’s anything I can help with, sure. (:
March 9, 2023, 12:56pm
Right now, each MySQL node opens a separate connection to the database, because of that normal transactions also do not work with the MySQL Execute node, as normal transactions only have an effect on the connection they are executed in.
As a workaround, you can use XA transactions:
But I have not tried that yet.
To use normal transactions and/or build dedicated transaction nodes, you probably need to implement the following feature request.
I like this idea and it could apply to more than just the database nodes it would also be useful for the FTP node. The way I have seen it implemeted before is you can output a “session id” and if you are using that the connection will be kept open for the life of the workflow and you can then pass that id instead of using credentials.
In theory we could automatically handle that but I suspect it wouldn’t be an easy or quick feature to add.