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
This Is what I see in the IDE Log, the START TRANSACTION AND THE COMMIT are listed BEFORE the actual insertion of the rows.
But this is what I see in the general_log table of the mysql server:
Everything looks fine there.
Why is the log displaying events out of order? Am I doing anything wrong?
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 SubThis Is what I see in the IDE Log, the START TRANSACTION AND THE COMMIT are listed BEFORE the actual insertion of the rows.
But this is what I see in the general_log table of the mysql server:
Everything looks fine there.
Why is the log displaying events out of order? Am I doing anything wrong?
