Android Question Order of log entries is not right. Am I doing anything wrong?

calloatti

Member
Licensed User
I am inserting records into a mysql table using the function "SqliteToMysqlData" in my db class. This is the full code of the class:

Line 219 is where the sub starts. I am starting a transaction in line 283, inserting the rows, then commit the transaction in line 307

db class:
Sub Class_Globals
    
    Public Kvs As KeyValueStore
    Public Sqlite As SQL
    Public Mysql As JdbcSQL
    
    Public EnableLog As Boolean = True
    
End Sub

Public Sub Initialize
    
    MysqlDisableStrictMode
    
    SqliteInit(False)
    
    KvsInit(False)
    
End Sub

Sub MysqlDisableStrictMode
    
    Private jo As JavaObject

    jo.InitializeStatic("android.os.Build.VERSION")

    If jo.GetField("SDK_INT") > 9 Then

        Private policy As JavaObject

        policy = policy.InitializeNewInstance("android.os.StrictMode.ThreadPolicy.Builder", Null)

        policy = policy.RunMethodJO("permitAll", Null).RunMethodJO("build", Null)

        Private sm As JavaObject

        sm.InitializeStatic("android.os.StrictMode").RunMethod("setThreadPolicy", Array(policy))

    End If
    
End Sub

Sub SqliteInit(ResetDB As Boolean)
    
    Private rp As RuntimePermissions

    Private datadir As String = rp.GetSafeDirDefaultExternal("")

    Sqlite.Close
    
    If ResetDB = True Then
        
        File.Delete(datadir, "sqlite.db") 'DELETES SQLITE DATABASE

    End If

    Sqlite.Initialize(datadir, "sqlite.db", True)

End Sub

Sub KvsInit(ResetKVS As Boolean)
    
    Private rp As RuntimePermissions
    
    If ResetKVS = True Then
        
        File.Delete(rp.GetSafeDirDefaultExternal(""),"keyvaluestore.dat")

    End If
    
    Kvs.Initialize(rp.GetSafeDirDefaultExternal(""), "keyvaluestore.dat")
    
End Sub


Sub SqliteTryExecNonQuery(sql As String) As Boolean 'Ignore
    
    Private result As Boolean

    Try

        Sqlite.ExecNonQuery(sql)
        
        result = True
        
    Catch
        
        result = False
        
    End Try
    
    If EnableLog Then Log("SQLITE:" & sql)

    If result = False Then
        
        LogException

    End If
    
    Return result

End Sub


Sub SqliteVacuum 'Ignore
    
    Private sql As String = "VACUUM"
    
    SqliteTryExecNonQuery(sql)
    
End Sub

Sub SqliteZap(table As String) 'Ignore DELETES ALL ROWS FROM A SQLITE TABLE
    
    Private sql As String = $"DELETE FROM "${table}""$
            
    SqliteTryExecNonQuery(sql)
    
End Sub

Sub SqliteDropTable(table As String) 'Ignore
    
    Private sql As String = $"DROP TABLE IF EXISTS "${table}""$
    
    SqliteTryExecNonQuery(sql)

End Sub

Sub SqliteToMysql(sqlitetable As String, mysqltable As String, DropBefore As Boolean) As Boolean 'ignore
    
    'CREATES A MYSQL TABLE FROM AN SQLITE TABLE. THE SQLITE TABLE MUST HAVE BEEN CREATED USING MYSQL_TO_SQLITE (BECAUSE OF COLUMN TYPES)

    Private rs As ResultSet

    Private Sql As StringBuilder

    Private result As Boolean = False
    
    If Mysql.IsInitialized = False Then
    
        Return result
    
    End If
    
    Sql.Initialize
    
    Sql.Append($"PRAGMA table_info("${sqlitetable}")"$)
    
    If EnableLog Then Log("SQLITE:" & Sql.ToString)

    rs = Sqlite.ExecQuery(Sql.ToString)
    
    If rs.RowCount = 0 Then
        
        rs.Close
        
        Return result
        
    End If
    
    If DropBefore = True Then
        
        MysqlDropTable(mysqltable)
                
    End If
    
    Sql.Initialize
    
    Sql.Append($"CREATE TABLE IF NOT EXISTS `${mysqltable}` ("$ & CRLF)

    Private firstrow As Boolean = True
    
    Private primarykey As String = ""

    Do While rs.NextRow
        
        If firstrow = False Then Sql.Append(", " & CRLF) Else firstrow = False
        
        Sql.Append($"`${rs.GetString("name")}` "$)
        
        Sql.Append($"${rs.GetString("type")}"$)
        
        If rs.GetInt("notnull") = 1 Then
            
            Sql.Append(" NOT NULL")
            
        End If
    
        If rs.GetString("dflt_value") <> Null Then
            
            Sql.Append(" DEFAULT " & rs.GetString("dflt_value"))
            
        End If
        
        If rs.GetInt("pk") = 1 Then primarykey = $"PRIMARY KEY (`${rs.GetString("name")}`)"$

    Loop
    
    rs.Close

    If primarykey.Length > 0 Then
        
        Sql.Append("," & CRLF & primarykey)
        
    End If
    
    Sql.Append(")")

    Sql.Append(" ENGINE=InnoDB DEFAULT CHARSET=utf8")

    'CREATE MYSQL TABLE
    
    result = MysqlTryExecNonQuery(Sql.ToString)
    
    Return result
    
End Sub

Sub SqliteToMysqlData(sqlitetable As String, mysqltable As String, wherefilter As String, orderby As String, deleterows As Boolean) As Boolean 'Ignore
    
    'INSERT/UPDATE ROWS FROM A SQLITE TABLE TO A MYSQL TABLE
    
    Private result As Boolean = False
    
    Private sqls As StringBuilder
    
    Private sqlm As StringBuilder
    
    If Mysql.IsInitialized = False Then
    
        Return result
    
    End If
    
    sqls.Initialize

    sqlm.Initialize

    Private rs As ResultSet

    sqls.append($"SELECT * FROM "${sqlitetable}""$)
    
    If wherefilter.Length > 0 Then
    
        sqls.Append(" WHERE " & wherefilter)
        
    End If

    If orderby.Length > 0 Then
    
        sqls.append(" ORDER BY " & orderby)
        
    End If

    rs = Sqlite.ExecQuery(sqls.ToString)
    
    sqlm.Append("REPLACE INTO `" & mysqltable & "`(")
    
    For icol = 0 To rs.ColumnCount - 1
        
        If icol > 0 Then sqlm.Append(",")
            
        sqlm.Append(rs.GetColumnName(icol))
            
    Next
        
    sqlm.Append(")")
    
    sqlm.Append(" VALUES(")
    
    For icol = 0 To rs.ColumnCount - 1
        
        If icol > 0 Then sqlm.Append(",")
            
        sqlm.Append("?")
            
    Next
        
    sqlm.Append(")")
    
    Private params(rs.ColumnCount) As String
    
    MysqlStartTransaction
    
    If deleterows = True Then
        
        MysqlZap(mysqltable)
        
    End If

    Try
    
        For i = 0 To rs.RowCount - 1
    
            rs.Position = i

            For x = 0 To rs.ColumnCount - 1
            
                params(x) = rs.GetString2(x)
            
            Next
        
            Mysql.ExecNonQuery2(sqlm.ToString, params)
        
        Next
    
        MysqlCommit
            
        result = True

    Catch

        LogException
        
        MysqlRollback
            
        result = False

    End Try
    
    rs.Close
    
    If EnableLog Then Log("SQLITE:" & sqls.ToString)

    If EnableLog Then Log("MYSQL :" & sqlm.ToString)
    
    Return result
    
End Sub

Sub LogException() 'ignore
    
    If LastException.IsInitialized Then
                
        Log(LastException.Message)
                
    End If
    
End Sub

Sub MysqlTryExecNonQuery(sql As String) As Boolean 'Ignore
    
    Private result As Boolean

    Try

        Mysql.ExecNonQuery(sql)
        
        result = True
        
    Catch
        
        result = False
        
    End Try
    
    If EnableLog Then Log("MYSQL :" & sql)

    If result = False Then LogException
        
    Return result
    
End Sub

Sub MysqlOpen As ResumableSub 'Ignore
    
    If Mysql.IsInitialized Then
        
        Return True
    
    End If

    Private mysqldriver As String = "com.mysql.jdbc.Driver"
    Private mysqlurl As String
    Private mysql_server As String = Kvs.Get("mysql_server")
    Private mysql_port As String = Kvs.GetDefault("mysql_port","3306")
    Private mysql_user As String = Kvs.Get("mysql_user")
    Private mysql_pass As String = Kvs.Get("mysql_pass")
    Private mysql_database As String = Kvs.Get("mysql_database")
    
    mysqlurl = "jdbc:mysql://" & mysql_server & ":" & mysql_port & "/" & mysql_database & "?autoReconnect=true&useSSL=false"
    
    If EnableLog Then Log(mysqlurl)
    
    Mysql.InitializeAsync("mysql", mysqldriver, mysqlurl, mysql_user, mysql_pass)
    
    wait for mysql_ready(result As Boolean)

    If result = False Then LogException
        
    Return result
    
End Sub

Sub MysqlClose 'Ignore

    If EnableLog Then Log("MYSQL.CLOSE")
    
    Mysql.Close

End Sub

Sub MysqlZap(table As String)
    
    Private sql As String = $"TRUNCATE TABLE `${table}`"$
    
    MysqlTryExecNonQuery(sql)
    
End Sub

Sub MysqlDropTable(table As String)
    
    Private sql As String = $"DROP TABLE IF EXISTS `${table}`"$
    
    MysqlTryExecNonQuery(sql)

End Sub

Sub MysqlToSqlite(mysqltable As String, sqlitetable As String, DropBefore As Boolean) As Boolean 'Ignore
    
    Private result As Boolean
    
    Private sqls As StringBuilder
    
    Private sqlm As String
    
    Private rs As JdbcResultSet

    'GET MYSQL TABLE STRUCTURE
    
    sqlm = $"SHOW COLUMNS FROM `${mysqltable}`"$
        
    Try
            
        rs = Mysql.ExecQuery(sqlm)
            
        result = True

    Catch

        result = False

    End Try
        
    If result = False Then
        
        LogException

        Return result
        
    End If

    If DropBefore = True Then
        
        SqliteDropTable(sqlitetable)
        
    End If

    'BUILD SQLITE CREATE TABLE COMMAND
    
    Private primarykey As String = ""
    
    sqls.Initialize
        
    sqls.Append($"CREATE TABLE IF NOT EXISTS "${sqlitetable}" ("$ & CRLF)

    Private firstrow As Boolean = True
    
    Do While rs.NextRow
                
        If firstrow = False Then sqls.Append(", " & CRLF) Else firstrow = False
        
        sqls.Append($""${rs.Getstring("field")}""$)
        
        sqls.Append(" " & rs.Getstring("type").Replace(" unsigned", ""))
                
        If rs.Getstring("key") = "PRI" Then
                    
            primarykey = $"PRIMARY KEY ("${rs.Getstring("field")}")"$
                    
        End If
        
        If rs.Getstring("null") = "NO" Then
            
            sqls.Append(" NOT NULL")
        
        End If
        
        If rs.Getstring("default") <> Null Then
            
            sqls.Append(" DEFAULT '" & rs.Getstring("default") & "'")
        
        End If
        
        If rs.Getstring("type").Contains(" unsigned") Then
            
            sqls.Append(" CHECK(" & rs.Getstring("field") & " >= 0)")
            
        End If
                    
    Loop
    
    If primarykey.Length > 0 Then
        
        sqls.Append("," & CRLF & primarykey & ")")
        
    Else
        
        sqls.Append(")")
        
    End If
    
    rs.Close 'CLOSE MYSQL RECORDSET
            
    result = SqliteTryExecNonQuery(sqls.ToString)
    
    Return result
        
End Sub

Sub MysqlToSqliteData(mysqltable As String, sqlitetable As String, wherefilter As String, orderby As String, deleterows As Boolean) As Boolean 'Ignore
    
    'INSERTS/UPDATES ROWS FROM A MYSQL TABLE TO A SQLITE TABLE
    
    Private sqlm As StringBuilder
    
    Private sqls As StringBuilder
    
    Private result As Boolean = False
    
    Private rs As JdbcResultSet
    
    If deleterows = True Then
        
        SqliteZap(sqlitetable)
        
    End If
    
    sqlm.Initialize
    
    sqls.Initialize
    
    sqlm.Append($"SELECT * FROM `${mysqltable}`"$)

    If wherefilter.Length > 0 Then
    
        sqlm.Append(" WHERE " & wherefilter)
        
    End If

    If orderby.Length > 0 Then
    
        sqlm.Append(" ORDER BY " & orderby)
        
    End If

    rs = Mysql.ExecQuery(sqlm.ToString)
            
    Private params(rs.ColumnCount) As String
        
    sqls.Append($"INSERT OR REPLACE INTO "${sqlitetable}"("$)
    
    For icol = 0 To rs.ColumnCount - 1
        
        If icol > 0 Then sqlm.Append(",")
            
        sqls.Append(rs.GetColumnName(icol))
            
    Next

    sqls.Append(")")

    sqls.Append($" VALUES("$)

    For icol = 0 To rs.ColumnCount - 1
        
        If icol > 1 Then sqls.Append(",")
            
        sqls.Append("?")
            
    Next
        
    sqls.Append(")")

    Sqlite.BeginTransaction
    
    Try
        
        Do While rs.NextRow
        
            For icol = 0 To rs.ColumnCount - 1
            
                params(icol) = rs.GetString2(icol)
                
            Next
        
            Sqlite.ExecNonQuery2(sqls.ToString, params)
            
        Loop
        
        Sqlite.TransactionSuccessful
        
        result = True
    
    Catch

        result = False
            
        LogException
        
    End Try

    Sqlite.EndTransaction
    
    rs.Close 'CLOSE MYSQL RECORDSET
    
    If EnableLog Then Log("SQLITE:" & sqls.ToString)

    If EnableLog Then Log("MYSQL :" & sqlm.ToString)

    Return result
    
End Sub

Sub MysqlGeneralLog(enable As Boolean) 'Ignore
    
    If enable = True Then
    
        MysqlTryExecNonQuery("SET global log_output = 'table'")
        MysqlTryExecNonQuery("SET global general_log = 1")
    
    Else
    
        MysqlTryExecNonQuery("SET global general_log = 0")
    
    End If

End Sub

Sub MysqlStartTransaction 'Ignore
    
    Private sql As String
    
    sql = "START TRANSACTION"
    
    Mysql.ExecNonQuery(sql)
    
    If EnableLog Then LogColor("MYSQL :" & sql, Colors.Yellow)
    
End Sub

Sub MysqlCommit 'Ignore
    
    Private sql As String
    
    sql = "COMMIT"
    
    Mysql.ExecNonQuery(sql)
    
    If EnableLog Then LogColor("MYSQL :" & sql, Colors.Yellow)
    
End Sub

Sub MysqlRollback 'Ignore
    
    Private sql As String
    
    sql = "ROLLBACK"
    
    Mysql.ExecNonQuery(sql)
    
    If EnableLog Then LogColor("MYSQL :" & sql, Colors.Yellow)
    
End Sub

This Is what I see in the IDE Log, the START TRANSACTION AND THE COMMIT are listed BEFORE the actual insertion of the rows.

Image1.png


But this is what I see in the general_log table of the mysql server:

Image2.png


Everything looks fine there.

Why is the log displaying events out of order? Am I doing anything wrong?
 
Top