Android Question sqLite Database PRAGMA wal_checkpoint(TRUNCATE) fails

[rgarnett1955]

Hi,

I use Write Ahead Log (WAL) files for most of my sqLite data bases as this makes for fast writes and robust handling of power failures. Richard Hpp of sqLite seems to me to reccommend this method.

When I close an app I like do do a checkpoint(TRUNCATE) on the db to commit all writes to the main file so the Wal file has no transactions. This ensures that the single database file contains all the latest data. I have been doing this with B4A with no problems but when I use virtually identical code with B4J I get the following error:

Waiting for debugger to connect...
Program started.
DB Integrity = OK
Set Journal Mode = WAL Mode = OK
Checkpoint PASSIVE = OK:  Busy = 0  Log = 1  Checkpointed = 1
Recent File Updated: \  Date: 2020-09-05 16:31:05
Recent File Updated: G:\B4J\fileEntryClass\Files\RecentFilesDB.db  Date: 2020-09-05 16:31:18
DB Integrity = OK
(SQLiteException) org.sqlite.SQLiteException: [SQLITE_LOCKED]  A table in the database is locked (database table is locked)
Error occurred on line: 807
java.lang.RuntimeException: Object should first be initialized (List).
    at anywheresoftware.b4a.AbsObjectWrapper.getObject(AbsObjectWrapper.java:32)
    at anywheresoftware.b4a.objects.collections.List.Get(List.java:105)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:234)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.debug.Debug.delegate(Debug.java:64)
    at b4j.example.fileget._checkrecentfilesdb(fileget.java:219)
    at b4j.example.main._mainform_closed(main.java:144)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
    at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.shell.Shell.runMethod(Shell.java:632)
    at anywheresoftware.b4a.shell.Shell.raiseEventImpl(Shell.java:237)
    at anywheresoftware.b4a.shell.Shell.raiseEvent(Shell.java:167)
    at jdk.internal.reflect.GeneratedMethodAccessor2.invoke(Unknown Source)
    at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.base/java.lang.reflect.Method.invoke(Method.java:566)
    at anywheresoftware.b4a.BA.raiseEvent2(BA.java:91)
    at anywheresoftware.b4a.shell.ShellBA.raiseEvent2(ShellBA.java:98)
    at anywheresoftware.b4a.BA$1.run(BA.java:216)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$10(PlatformImpl.java:428)
    at java.base/java.security.AccessController.doPrivileged(Native Method)
    at javafx.graphics/com.sun.javafx.application.PlatformImpl.lambda$runLater$11(PlatformImpl.java:427)
    at javafx.graphics/com.sun.glass.ui.InvokeLaterDispatcher$Future.run(InvokeLaterDispatcher.java:96)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication._runLoop(Native Method)
    at javafx.graphics/com.sun.glass.ui.win.WinApplication.lambda$runLoop$3(WinApplication.java:174)
    at java.base/java.lang.Thread.run(Thread.java:834)

Before doing the checkpoint I add a dummy resord to the only user table in the db viz:

    addOrUpdateFile(fileNameSel, pathNameSel, (pathNameSel & "\" & fileNameSel), "n", 1000)
    
    ...
    
'======================================================================================================
Private Sub addOrUpdateFile(fileName As String, path As String, fullPath As String,  fileNotFound As Char, fileSize As Long) As Boolean
    'Check if full path exists
    Dim qryStr As String
    Dim dateStr As String
    DateTime.DateFormat = "yyyy-MM-dd"
    DateTime.TimeFormat = "HH:mm:ss"
    dateStr = DateTime.Date(DateTime.Now) & " " & DateTime.Time(DateTime.Now)
    
    qryStr = _
    $"SELECT COUNT(fullPath) FROM tblRecentFiles WHERE fullPath = ?"$
    
    Dim cur As ResultSet
    
'    sqlRecent.BeginTransaction
    Try
        cur = sqlRecent.ExecQuery2(qryStr, Array As String(fullPath))
    Catch
        Log(LastException)
    End Try
'    sqlRecent.TransactionSuccessful
    
    cur.NextRow
    If cur.GetLong2(0) = 0 Then
        qryStr = _
                $"INSERT INTO tblRecentFiles (
                  fileName,
                  path,
                  fullPath,
                  dateFirstOpened,
                  dateLastOpened,
                  fileNotFound,
                  fileSize)
                  VALUES (?,
                          ?,
                          ?,
                          ?,
                          ?,
                          ?,
                          ?)"$
                          
        sqlRecent.BeginTransaction
        Try
            sqlRecent.ExecNonQuery2(qryStr, Array As String(fileName, path, fullPath, dateStr, dateStr, fileNotFound, fileSize))
            Log("Recent File Added: " & fullPath &  "  Date: " & dateStr)
        Catch
            Log(LastException)
        End Try
        sqlRecent.TransactionSuccessful
        
    Else
        qryStr = _
                $"UPDATE tblRecentFiles
                    SET
                        dateLastOpened     = ?,
                        fileNotFound     = ?,
                        fileSize         = ?
                    WHERE fullPath         = ?"$
    
        sqlRecent.BeginTransaction
        Try
            sqlRecent.ExecNonQuery2(qryStr, Array As String(dateStr, fileNotFound, fileSize, fullPath))
            Log("Recent File Updated: " & fullPath &  "  Date: " & dateStr)
        Catch
            Log(LastException)
        End Try
        sqlRecent.TransactionSuccessful
        
    End If
    Return True
End Sub

If I then close the app and call:

Sub MainForm_Closed
    '                            logOn, setJournal, integrityCheck, doFullCheckPoint, setAutoCheckPoint) As Boolean
    If fGet.checkRecentFilesDB(True,  "",         True,           True,             -3) = False Then
        'TODO
    End If
End Sub

'======================================================================================================
Public Sub checkRecentFilesDB(logOn As Boolean, setJournal As String, integrityCheck As Boolean, doFullCheckPoint As Boolean, setAutoCheckPoint As Int) As Boolean
    'Check the database
    Dim qryStr As String
    Dim qryOutList As List
    Dim row() As String
    
    If integrityCheck Then
        qryStr = "PRAGMA integrity_check;"
        qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
        row = qryOutList.Get(0)
        If row(0).CompareTo("ok") <> 0 Then
            If logOn Then Log("DB Integrity = BAD")
            Return False
        End If
    
        If logOn Then Log("DB Integrity = OK")
    End If
    
    
    If setJournal <> "" Then
        qryStr = "PRAGMA journal_mode=" &  setJournal & ";"
        qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
        row = qryOutList.Get(0)
        If row(0).CompareTo("wal") <> 0 Then
            If logOn Then Log("Set Journal Mode = " &  setJournal & " Mode = FAILED")
            Return False
        End If
        
        If logOn Then Log("Set Journal Mode = " &  setJournal & " Mode = OK")
    End If
    
    
    If doFullCheckPoint Then
        qryStr = "PRAGMA wal_checkpoint(TRUNCATE);"
        Try
            qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
        Catch
            Log(LastException)
            If logOn Then Log("Checkpoint TRUNCATE = Failed")
            Return False
        End Try

        row = qryOutList.Get(0)
        If row(0) < 0 Then
            If logOn Then Log("Checkpoint TRUNCATE = Failed")
            Return False
        End If

        If logOn Then Log("Checkpoint TRUNCATE = OK:  Busy = " & row(0) & "  Log = " & row(1) & "  Checkpointed = " & row(2))
    End If
    

    If setAutoCheckPoint <> -3 Then
        Select setAutoCheckPoint
            Case -1
                qryStr = "PRAGMA wal_autocheckpoint=-1;"     'No checkpoint
            Case 0
                qryStr = "PRAGMA wal_autocheckpoint=1000;"  'sqLite Default
            Case Else
                qryStr = "PRAGMA wal_autocheckpoint=" & setAutoCheckPoint & ";" 'User setting
        End Select
            
        qryOutList = ExecuteMemoryTable(sqlRecent, qryStr, Null, 2)
        row = qryOutList.Get(0)
        If row(0) < 0 Then
            If logOn Then Log("DB Checkpoint Value Setting = Failed")
            Return False
        End If
        
        If logOn Then Log("DB Checkpoint Value Setting = OK")
    End If
    Return True
End Sub

The sqLite db still thinks the transaction is open and complains the db is locked and the error is thrown.

With b4A there is a Endtransation method that isn't available in B4J. Viz:

'======================================================================================================
Private Sub addOrUpdateFile(fileName As String, path As String, fullPath As String,  fileNotFound As Char, fileSize As Long) As Boolean
    'Check if full path exists
    Dim qryStr As String
    Dim dateStr As String
    DateTime.DateFormat = "yyyy-MM-dd"
    DateTime.TimeFormat = "HH:mm:ss"
    dateStr = DateTime.Date(DateTime.Now) & " " & DateTime.Time(DateTime.Now)
    
    qryStr = _
    $"SELECT COUNT(fullPath) FROM tblRecentFiles WHERE fullPath = ?"$
    
    Dim cur As ResultSet
    
    Try
        cur = sqlRecent.ExecQuery2(qryStr, Array As String(fullPath))
    Catch
        Log(LastException)
    End Try
    
    cur.NextRow
    If cur.GetLong2(0) = 0 Then
        qryStr = _
                $"INSERT INTO tblRecentFiles (
                  fileName,
                  path,
                  fullPath,
                  dateFirstOpened,
                  dateLastOpened,
                  fileNotFound,
                  fileSize)
                  VALUES (?,
                          ?,
                          ?,
                          ?,
                          ?,
                          ?,
                          ?)"$
                          
        sqlRecent.BeginTransaction
        Try
            sqlRecent.ExecNonQuery2(qryStr, Array As String(fileName, path, fullPath, dateStr, dateStr, fileNotFound, fileSize))
            Log("Recent File Added: " & fullPath &  "  Date: " & dateStr)
        Catch
            Log(LastException)
        End Try
        sqlRecent.TransactionSuccessful
        sqlRecent.EndTransaction            'Fails if this commented out
        
    Else
        qryStr = _
                $"UPDATE tblRecentFiles
                    SET
                        dateLastOpened     = ?,
                        fileNotFound     = ?,
                        fileSize         = ?
                    WHERE fullPath         = ?"$
    
        sqlRecent.BeginTransaction
        Try
            sqlRecent.ExecNonQuery2(qryStr, Array As String(dateStr, fileNotFound, fileSize, fullPath))
            Log("Recent File Updated: " & fullPath &  "  Date: " & dateStr)
        Catch
            Log(LastException)
        End Try
        sqlRecent.TransactionSuccessful
        sqlRecent.EndTransaction            'Fails if this commented out
        
    End If
    Return True
End Sub

What is going on.

This seems like a bug.

The transactions are getting written correctly to the DB and are completed, so why is the checkpoint command complaining that the sqLite db in B4J is locked?

Why isn't their an end transaction command in B4J like there is in B4A.

I have attached the B4A and B4J projects as zips

 

[Erel]

Sounds to me that you are making things much more complicated than they need to be.

When I close an app I like do do a checkpoint(TRUNCATE) on the db to commit all writes to the main file so the Wal file has no transactions

Why it is important?

SQL.TransactionSuccessful commits the transaction.

The project is too large for me to help you. If you think that there is an issue then create small project that demonstrates it. Do remember that all of the SQL features are actually implemented by the underlying driver.

 

[Erel]

Another thing to check is whether jFileWatcher raises events on background threads. If will cause you many issues if this is the case.

 

[rgarnett1955]

Hi Erel,

I am a bit of a database fiend. I love em!

I jdon't like having journals hanging around. It's not good practice in my view. As has happened to me before I have copied a db file without the journals onto another system then wondered why the transactions didn't appear. I will cook up a cut down project for you to have a look at. I kind of suspect that it might be a driver thing, that's why I updated the sqlite driver to the latest, but it didn't help.

I've used the checkpoint command in B4A, Qt, an embedded amalgamation running on an STM32H7 and the free windows C++ compiler without any problems. Works every time as long as you do your inserts and updates properly. It only adds two or three lines of code so it's no big deal to do it.

I will do a bit more on it and let you know how I get on. I would like to get it working in this project even though its not critical in this application, but in another one it might well be.

Best regards
Rob