MySql ROLLBACK not working with recordset

Databases
1

I have a Xojo 2020r1 desktop project using a mysqlcommunityserver database.
I am updating several tables using RecordSet.Edit and RecordSet.Update.

I have preceded the series of table updates with the following SQL commands:

db.SQLExecute(“SET autocommit=0;”)
db.SQLExecute(“START TRANSACTION”)

If an error occurs in updating any of the tables, I use this code to cancel the transaction:

db.RollbackTransaction

However, if an error occurs in one of the tables, the tables that were updated without error using RecordSet.Edit and RecordSet.Update seemed to ignore the ROLLBACK command and committed to an update anyway. Completely disregarding the ROLLBACK!

I also tried the db.Rollback and db.SQLExecute(“ROLLBACK”) with the same results.

I checked to make sure that db.CommitTransaction was not applied in any part of my code.

Why is ROLLBACK not working. Is this a bug or am I doing something wrong?

2

You shouldnt need to turn autocommit off as when you issue the start transaction everything until a commit or rollback IS part of a transaction. Thats true in autocommit mode.

What sort of tables are these ? InnoDB or NDB ?

As far as I know only those 2 types are transaction safe and other table types may behave as you describe

4

Thank you for your reply Norman. I am working with MariaDB tables. If what you are saying is true, that InnoDB and NDB tables are the ONLY tables that are transaction safe, what methods do I use to incorporate a rollback for MariaDB tables?

5

Maria, as far as I know, can / defaults to, the InnoDB storage engine so your tables should be transaction safe unless you have something custom set up where they aren’t

6

I do not believe the database is customized in any fashion. It seems like RecordSet.Update is implicitly committing the update.

7

Try the current syntax:

Try
  DB.BeginTransaction
  DB.ExecuteSQL("CREATE TABLE blah blah blah blah ....")
  DB.CommitTransaction
Catch error As DatabaseException
  MsgBox("Error: " + error.Message)
  DB.RollbackTransaction
End Try
8

From the Manual:

Editing with MySQL

MySQL cannot guarantee the contents of a RowSet after issuing an Update call (after having previously called EditRow). This means you should not try to modify the contents of a RowSet in a loop. Instead select just the single row you wish to modify.

9

Don’t use .Edit() .Update()
Send SQL statements directly instead.

10

Not sure if this is related or not. But I am now getting a StackOverFlowException on db.BeginTransaction. I did not get that error with db.SQLExecute(“START TRANSACTION”).

11

I wouldn’t expect a StackOverFlowException at such early point. Maybe you found some bug needing attention.

12

Editing with MySQL

“MySQL cannot guarantee the contents of a RowSet after issuing an Update call (after having previously called EditRow). This means you should not try to modify the contents of a RowSet in a loop. Instead select just the single row you wish to modify.”

Yes. I am only editing one row at a time.

13

So there’s no need of a transaction if you are dealing with atomic operations.

14

Oh. I’m sorry. I misunderstood. I meant one row at a time for each table for a series of tables.

15

I never use .edit() .update()
I write my full SQL sentences directly to avoid “unknown and undesired things occurring behind de scenes”.
I think it would be one way of trying to avoid the problems you are dealing with.

16

Ok. That makes sense Rick!
However, I am having trouble finding a “best practice” example of editing several columns of a table using DB.ExecuteSQL. The reason I was using RecordSet.Edit and RecordSet.Update is because it seems to be a streamlined method for editing several columns of a table.

17

Something by these lines. Nothing tested, even mistypes are possible, written just trying to show a basic idea

Var rows As RowSet = ... // let's skip the populating the rowset part
...
For Each row As DatabaseRow In rows // let's walk on the rowset

// Update a table
Var sql As String
sql = "UPDATE my_table SET my_field1=?, my_other_field=? WHERE ID=?"
Try
  db.ExecuteSQL(sql, row.Column("my_field1").StringValue+" more this: "+change, "the other value", row.Column("ID").IntegerValue)
Catch error As DatabaseException
  MessageBox("DB Error: " + error.Message)
End Try

Next
18

Thank you Rick!
I will try this method and maybe this will resolve the rollback issue.

1 Like
19

Start transaction at the top, send lots of direct updates, if no error commit, else rollback.

20

I found the ROLLBACK issue. After examining the structure of the tables in the database with phpMyAdmin, I discovered that the tables are of the MyISAM Storage Engine type, which does not support transactions. Basically, using ROLLBACK with MyISAM is useless.