Page 1 of 1

Script with Transaction does not roll back

Posted: Wed 12 Dec 2018 12:28
by Zero-G.
This is my code, which I use to run a script:

Code: Select all

        Private Shared Function ExecuteScript(ByVal script As String) As Boolean
            Dim locReturnValue As Boolean = True

            Using locConnection As Devart.Data.MySql.MySqlConnection = New Devart.Data.MySql.MySqlConnection(mySQL.GetConnectionString)
                Dim locScript As Devart.Data.MySql.MySqlScript = New Devart.Data.MySql.MySqlScript(script, locConnection)
                AddHandler locScript.Progress, AddressOf OnProgress

                progressStatements = locScript.Statements.Count
                Dim locTransaction As Devart.Data.MySql.MySqlTransaction = locConnection.BeginTransaction()


                Catch ex As Exception
                    locReturnValue = False
                    Start.WriteToErrorLog(ex, LinqProvider.MessageCaption)

                End Try

                RemoveHandler locScript.Progress, AddressOf OnProgress
            End Using
            Return locReturnValue
        End Function
When an error occurs, then the rollback is not done.
What am I doing wrong?

THX a lot

Re: Script with Transaction does not roll back

Posted: Thu 20 Dec 2018 17:22
by Pinturiccio
We could not reproduce the issue. If the error occurs during a script execution, then transaction will roll back changes.

Please note that a DDL command, like creating an object, can't be rolled back. Only a DML command can be rolled back. Thus, if a DDL command is present in your script text, then changes made by this script won't be rolled back.

Please also note that only tables with the InnoDB storage engine support transactions. If a table is created with the MyISAM storage engine, then this table does not support transactions.

Re: Script with Transaction does not roll back

Posted: Sat 29 Dec 2018 19:15
by Zero-G.

Sorry for the late reply. It seems your post went straight into my spam…

Is there any chance to rollback DML on error?
Even directly in script?

THX for tips into this situation

Re: Script with Transaction does not roll back

Posted: Wed 02 Jan 2019 14:55
by Pinturiccio
It is not possible to rollback a DDL query. This is a MySQL restriction, not a Devart's one. For more information, please refer to ... -back.html