Script with Transaction does not roll back

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
Zero-G.
Posts: 396
Joined: Sat 09 Dec 2006 12:20

Script with Transaction does not roll back

Post by Zero-G. » Wed 12 Dec 2018 12:28

Hey
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)
                locConnection.Open()
                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()

                Try

                    locScript.Execute()
                    locTransaction.Commit()
                Catch ex As Exception
                    locReturnValue = False
                    locTransaction.Rollback()
                    Start.WriteToErrorLog(ex, LinqProvider.MessageCaption)

                Finally
                    locConnection.Close()
                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

Pinturiccio
Devart Team
Posts: 2377
Joined: Wed 02 Nov 2011 09:44

Re: Script with Transaction does not roll back

Post by Pinturiccio » Thu 20 Dec 2018 17:22

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.

Zero-G.
Posts: 396
Joined: Sat 09 Dec 2006 12:20

Re: Script with Transaction does not roll back

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

Hey

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

Pinturiccio
Devart Team
Posts: 2377
Joined: Wed 02 Nov 2011 09:44

Re: Script with Transaction does not roll back

Post by Pinturiccio » Wed 02 Jan 2019 14:55

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 https://dev.mysql.com/doc/refman/8.0/en ... -back.html

Post Reply