B4J Question autocommit=true error in transaction.

[alwaysbusy]

I'm trying to run a transaction in B4J. When it failes (and is supposed to do a rollback), I receive the following error. I thought SQL.BeginTransaction was supposed to set the autocommit to false? And I don't seem to be able to find a property to regulate this. In the code I've 'created' an error by inserting a record in a non existing table called 'tIdentification2'in the second Query.

SQL.BeginTransaction
     Try
       ret = DBM.SQLInsertTrans(SQL, "INSERT INTO tExecutor(OTID, exCode, exDescription, exExTypID, exStatus, exOTCreationTag, exOTLastModifiedLoginID) VALUES(?, ?, ?, ?, ?, ?, ?)", Variables) 'GENTODOC     
       
       Variables.Clear
       Variables.add(authCurrentMasterID)
       
       Variables.Add(ret)
       
       ' generate barcode
       Variables.Add(ABMShared.GenerateBarCode(SQL, authCurrentMasterID, "Executor0001BCPrefix"))
         
       Variables.Add(ABMShared.STATUS_OK)
     
       Variables.Add("INSERT_EXECUTORS1_1") 'GENTODOC
       Variables.Add(page.ws.Session.GetAttribute("authLoginID2020"))
       
       ret = DBM.SQLInsertTrans(SQL, "INSERT INTO tIdentification2(OTID, idenExID, idenCode ,idenStatus, idenOTCreationTag, idenOTLastModifiedLoginID) VALUES(?, ?, ?, ?, ?, ?)", Variables) ' <--- non-existing table tIdentification.     
       
       SQL.TransactionSuccessful
     Catch
       SQL.Rollback   ' <----------- Error
       DBM.CloseSQL(SQL)
       myToastId = myToastId + 1   
       page.ShowToast("toast" & myToastId, "toastred", page.XTR("1034","There was an error inserting the record"), 5000)
       Return
     End Try

DBM.SQLInsertTrans()

Sub SQLInsertTrans(SQL As SQL, Query As String, Args As List) As Int 'ignore
   Dim res As Int
   Select Case DatabaseType
     Case 0 ' SQlite
       SQL.ExecNonQuery2(Query, Args)
       res   = SQLSelectSingleResult(SQL, "SELECT last_insert_rowid()", Null)
     Case 1 ' MySQL <-------- THIS ONE IS USED
       SQL.ExecNonQuery2(Query, Args)
       res = SQLSelectSingleResult(SQL, "SELECT LAST_INSERT_ID()", Null)
     Case 2 ' MSSQL
       Dim ABSQL As ABSQLExt
       res = ABSQL.ExecNonQuery3(SQL, Query, Args)       
   End Select   
   Return res
End Sub

Error log:

Error occurred on line: 611 (ExecutorsPage1)
com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true
   at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
   at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
   at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
   at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
   at com.mysql.jdbc.Util.handleNewInstance(Util.java:404)
   at com.mysql.jdbc.Util.getInstance(Util.java:387)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:919)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:898)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:887)
   at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:862)
   at com.mysql.jdbc.ConnectionImpl.rollback(ConnectionImpl.java:4617)
   at com.mchange.v2.c3p0.impl.NewProxyConnection.rollback(NewProxyConnection.java:860)
   at anywheresoftware.b4j.objects.SQL.Rollback(SQL.java:423)
   at com.onetwo.prog2100.executorspage1._executors1save_clicked(executorspage1.java:1056)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
   at anywheresoftware.b4a.keywords.Common.CallSub4(Common.java:462)
   at anywheresoftware.b4a.keywords.Common.CallSubNew2(Common.java:417)
   at com.onetwo.prog2100.executorspage1._page_parseevent(executorspage1.java:1303)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:612)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:229)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.GeneratedMethodAccessor1.invoke(Unknown Source)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
   at anywheresoftware.b4j.object.WebSocketModule$Adapter$1.run(WebSocketModule.java:126)
   at anywheresoftware.b4a.keywords.SimpleMessageLoop.runMessageLoop(SimpleMessageLoop.java:30)
   at anywheresoftware.b4a.StandardBA.startMessageLoop(StandardBA.java:26)
   at anywheresoftware.b4a.ShellBA.startMessageLoop(ShellBA.java:111)
   at anywheresoftware.b4a.keywords.Common.StartMessageLoop(Common.java:131)
   at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:301)
   at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:159)
   at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
   at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
   at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
   at java.lang.reflect.Method.invoke(Method.java:498)
   at anywheresoftware.b4a.BA.raiseEvent2(BA.java:93)
   at anywheresoftware.b4a.ShellBA.raiseEvent2(ShellBA.java:90)
   at anywheresoftware.b4a.BA.raiseEvent(BA.java:84)
   at com.onetwo.prog2100.main.main(main.java:29)

 

[Erel]

I thought SQL.BeginTransaction was supposed to set the autocommit to false?

It does set it to false.

Are you always closing the SQL object?

Does it also happen in release mode?

 

[alwaysbusy]

Yes, I do close them. This even happens when it is the very first one. Not tried it in release mode yet.

I'm doing some reading up, and one cause could be because in mysql de tables are using MYISAM. Tried setting them to INNODB but still the same. Not even really sure if this is the cause. Maybe the SELECT breaks it.

What it needs to do in short is:

1. INSERT in table 1
2. do a SELECT to get the new created autonumber
3. use this newly autonumber in an INSERT in a second table 2

Should be common, so I'm missing something very basic.

 

[Erel]

I tested it with MySQL and didn't get this error. Rollback didn't really do anything because it was a MyISAM table which doesn't support it.

It is worth testing it in release mode.

 

[alwaysbusy]

Unfortunately (or fortunately because I would not like to loose debug mode) in release mode it gives the same result. I'll investigate further.

 

[alwaysbusy]

I ended up writing my own library to do a transaction. It does for me what it needs to do and I'm not sure if it is reusable in the existing SQL library, but it may give an idea of what I do differently compared to your transaction system. It does rollback correctly when the second insert fails.

Library code java:

public static final int THREAD_LOCK_TIMEOUT = 60000;
private ReentrantLock sqliteLock;
 
protected Map<String,String> Statements = new LinkedHashMap<String,String>();
protected Map<String,anywheresoftware.b4a.objects.collections.List> Args = new LinkedHashMap<String,anywheresoftware.b4a.objects.collections.List>();
protected Map<String,Long> Returns = new LinkedHashMap<String,Long>();
 
private void startLock() {
    if (this.sqliteLock != null) {
       try {
         if (!this.sqliteLock.tryLock(60000L, TimeUnit.MILLISECONDS)) {
           Thread.dumpStack();
           this.sqliteLock.lock();
         }
       } catch (InterruptedException e) {
    e.printStackTrace();
       }
    }
}

private void releaseLock() {
     if (this.sqliteLock != null) {
        this.sqliteLock.unlock();
     }
}

public void BeginTransaction() {
    Statements = new LinkedHashMap<String,String>();
    Args = new LinkedHashMap<String,anywheresoftware.b4a.objects.collections.List>();
    Returns = new LinkedHashMap<String,Long>();   
}
 
public boolean Commit(SQL sql) {
     boolean success = true;
     java.util.List<PreparedStatement> preparedStatements = new ArrayList<PreparedStatement>();
     try {
       sql.connection.setAutoCommit(false); // disable autocommit
     } catch (SQLException e) {
       e.printStackTrace();
     }
     try {
      startLock();  // lock
      for (Entry<String,String> entry: Statements.entrySet()) {       
         PreparedStatement ps;
         if (entry.getValue().toUpperCase().startsWith("INSERT ")) {  // so the prepared statement returns it created autonumbers
           ps = sql.connection.prepareStatement(entry.getValue(), PreparedStatement.RETURN_GENERATED_KEYS);
         } else {
           ps = sql.connection.prepareStatement(entry.getValue());
         }
         anywheresoftware.b4a.objects.collections.List myFillArgs = Args.get(entry.getKey());
         if (myFillArgs!=null) {
           for (int i=0;i<myFillArgs.getSize();i++) {
             Object obj = myFillArgs.Get(i);
             if (obj instanceof String) {
               String key = ((String) myFillArgs.Get(i)).toLowerCase();
               Long myReturn = Returns.getOrDefault(key, null);  // check if the value has to be filled with a generated ID from a previous statement
               if (myReturn!=null) {
                 ps.setObject(i + 1, myReturn);
               } else {
                 ps.setObject(i + 1, myFillArgs.Get(i));
               }
             } else {
               ps.setObject(i + 1, myFillArgs.Get(i));
             }
           }
         }
         ps.executeUpdate();
         preparedStatements.add(ps);
         if (entry.getValue().toUpperCase().startsWith("INSERT ")) {
           ResultSet generatedKey = ps.getGeneratedKeys();  // get the generated ID
           if (generatedKey!=null) {
             if (generatedKey.next()) {
               Long tmpKey = generatedKey.getLong(1);
               Returns.put(entry.getKey(), tmpKey);
             }
           }         
         }
       
      }     
      sql.connection.commit();  // commit
     } catch (SQLException e) {
       success = false;
       e.printStackTrace();
       if (sql.connection!=null) {
         try {
           sql.connection.rollback();  // rollback everything you've done
         } catch (SQLException rollE) {
           rollE.printStackTrace();
         }
       }
     } finally {
       for (int i=0;i<preparedStatements.size();i++) {   // close all the prepared statements used
         if (preparedStatements.get(i)!=null) {
           try {
             preparedStatements.get(i).close();
           } catch (SQLException e) {
             e.printStackTrace();
           }
         }
       }
       try {
         sql.connection.setAutoCommit(true); // enable autocommit
       } catch (SQLException e) {
         e.printStackTrace();
       }
       releaseLock();  // unlock
     }
     return success;
}

public void AddCommand(String queryName, String statement, anywheresoftware.b4a.objects.collections.List args) {
     queryName = queryName.toLowerCase();
     Statements.put(queryName, statement);
     anywheresoftware.b4a.objects.collections.List clonedArgs = new anywheresoftware.b4a.objects.collections.List();
     clonedArgs.Initialize();
     for (int i=0;i<args.getSize();i++) {
       clonedArgs.Add(args.Get(i));
     }
     Args.put(queryName, clonedArgs);
}

Usage in B4J:

Dim SQL As SQL = DBM.GetSQL ' from the pool

Dim ABSQL As ABSQLExt
ABSQL.BeginTransaction
' first query
Dim Variables as List
Variables.Initialize
Variables.Add(Executors1exCode.Text)
Variables.Add(Executors1exDescription.Text)
Variables.Add(exExTypID)
Variables.Add(ABMShared.STATUS_OK)
Variables.Add("INSERT_EXECUTORS1_1")
Variables.Add(authLoginID2020)   
ABSQL.AddCommand("Q1", "INSERT INTO tExecutor(OTID, exCode, exDescription, exExTypID, exStatus, exOTCreationTag, exOTLastModifiedLoginID) VALUES(?, ?, ?, ?, ?, ?, ?)", Variables)
   
' second query, using the inserted ID from Q1
Variables.Clear
Variables.add(authCurrentMasterID)     
Variables.Add("Q1")  '<--- links this field to the newly created result ID of the first query named Q1
Variables.Add(BarCode)       
Variables.Add(ABMShared.STATUS_OK)  
Variables.Add("INSERT_EXECUTORS1_1")
Variables.Add("authLoginID2020")       
ABSQL.AddCommand("Q2", "INSERT INTO tIdentification(OTID, idenExID, idenCode ,idenStatus, idenOTCreationTag, idenOTLastModifiedLoginID) VALUES(?, ?, ?, ?, ?, ?)", Variables)
   
If ABSQL.Commit(SQL) = False Then ' returns false if the transaction failed somewhere and did a rollback for you
     DBM.CloseSQL(SQL) ' close
     myToastId = myToastId + 1 
     page.ShowToast("toast" & myToastId, "toastred", page.XTR("1034","There was an error inserting the record"), 5000)
     Return
End If

DBM.CloseSQL(SQL) ' close

 

[Erel]

I'm unable to reproduce this issue here. Rollback works as expected (after changing the table to InnoDB engine).

I've tested it with this code:

Sub Handle(req As ServletRequest, resp As ServletResponse)
   Dim sql As SQL = Main.pool.GetConnection
   sql.BeginTransaction
   Try
     sql.ExecNonQuery2("INSERT INTO animals VALUES (8, ?, NULL)", Array("sdfsdf"))
     sql.ExecNonQuery2("INSERT INTO animdddals VALUES (10, ?, NULL)", Array("sdfsdf"))
     sql.TransactionSuccessful
   Catch
     sql.Rollback
     Log(LastException)
   End Try
   sql.Close
End Sub

 

[alwaysbusy]

The difference is my second query needs the AutoIncremented id from the first one:

e.g. I need something like this:

table Clients
ClientID (int AutoIncrement)
ClientName (string)

table Vendors
VendorID (int AutoIncrement)
VendorName (string)

table BarCodes
barcodeID (int AutoIncrement)
barcodeType (string)
barcodeParentID (int) <--- this is a link to OR clientID OR VendorID
barcodeValue (string)

Sub Handle(req As ServletRequest, resp As ServletResponse)
  Dim sql As SQL = Main.pool.GetConnection
  sql.BeginTransaction
  Try
  sql.ExecNonQuery2("INSERT INTO Clients(ClientName) VALUES (?)", Array("Alain Bailleul"))   <--- here a new ClientID is created
  dim clientID as int = SQL.ExecQuerySingleResult2("SELECT LAST_INSERT_ID()", Null)  <--- I retrieve the clientID
  sql.ExecNonQuery2("INSERT INTO BarCodes(barcodeType, barcodeParentID, barcodeValue) VALUES (?,?,?)", Array("CLIENT", clientID, "123456789"))  <--- I use the clientID
  sql.TransactionSuccessful
  Catch
  sql.Rollback
  Log(LastException)
  End Try
  sql.Close
End Sub

if the second ExecNonQuery2 fails, it has to rollback the first ExecNonQuery2, so also removing "Alain Bailleul" from the Clients table.

This is a simplified representation. We use this because we only have to search in one table if the user scans a BarCode. Then we know it is a client or a vender and can act accordingly.

The point is it should rollback ALL queries done between BeginTranslation and TranslationSuccessful IF the second INSERT failes. And I think it does not work just because of the SELECT I do inbetween.

The main reason I do not have to do this SELECT to get the new ID in my Java library code, is because I use:

ps = sql.connection.prepareStatement(entry.getValue(), PreparedStatement.RETURN_GENERATED_KEYS);

and can immidiately re-use this AutoIncremented value with:

ResultSet generatedKey = ps.getGeneratedKeys(); // get the generated ID
 if (generatedKey!=null) {
 if (generatedKey.next()) {
 Long tmpKey = generatedKey.getLong(1);
 Returns.put(entry.getKey(), tmpKey);
 }
 }

But it is OK Erel I have a solution for my problem with my library now. It is a weird database structure, but in our case we do need it like this as the barcodeType can be hundreds of types and it would we to heavy to search in all hundred tables if a scanned barcode matches.

Alain

 

[mindful]

AB I have the user registration where I do the same as you:
1. BeginTransaction
2. Insert into users table
3. Get LAST_INSERT_ID
4. Insert into logins table
5. Get LAST_INSERT_ID
6. Insert into accounts table
7. TransactionSuccessful

if i rename the table accounts to accounts3 I get exeption and it rollsback

The exeption is : (QueryException) org.mariadb.jdbc.internal.util.dao.QueryException: Error preparing query: Table 'mydb.accounts3' doesn't exist

Oh and I use MariaDB instead of MySQL ... so maybe the problem is with the mysql connector you are using ...

 

[alwaysbusy]

so maybe the problem is with the mysql connector you are using

Quite possible, or the setup of my database. I use the mysql-connector-java-5.1.37-bin.jar and pools.

My exception is: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Can't call rollback when autocommit=true

 

[walt61]

Hi AB, I just had the same error and tracked it back to an 'sql1.ExecNonQuery2' that had a missing parameter in the 'Args' list. Just in case you'd be having the same issue - it wasn't obvious.