Android Question File.LastModified, trouble with (wrong) timestamp

[mw71]

Hi

I am using File.LastModified to check if the (SQLite) file has changed since the last backup.
I changed the file today (add an entry, 9/22/19), but the result is 9/19/19. I check with Totalcomander, the DB file is the change 19.9., the SHM and WAL file is 22.9.19. (correct timestamp).

Why have the db File the old/wrong Timestamp??, what can i do to get the correct timestamp)

 

[emexes]

I changed the file today (add an entry, 9/22/19), but the result is 9/19/19.

My first thought was UTC vs local time, but the difference seems too great for it to be that. So, tossing random ideas into the mix:

1/ are you sure the change actually made it into the database (or was it rolled back?) ?

2/ has the database file been closed and released by all programs?

 

[mw71]

hi,

the diffrence between UTC and Local Time are 2h, but the Device run on Local Time.
The Timestamp from the WAL File are correct

1. i think yes, i use the normal "INSERT INTO..." Statement and run the SQL.ExecNonQuery

2. yes, after Insert or Update the Database it's Close via SQL.Close

 

[emexes]

The Timestamp from the WAL File are correct

Another random idea:
- note the dates and times on the various files
- make another benign alteration, like: add a full stop to a name
- exit program
- check for changes dates and times

eg is the time on the database different to what it was back in post #1 ? by about 1.5 hours (ie, now minus then) ?

Reasoning is: if you make changes ten minutes apart, does the database modification time also change by ten minutes?

 

[mw71]

Reasoning is: if you make changes ten minutes apart, does the database modification time also change by ten minutes?

If i add or change an entry, the change Timestamp from the WAL File exact the Change Time.
The db File Timestamp not changed.

sometimes, i can not undestand when (i do all time the same), the wal File is delete and the db File have the correct Timestamp.

 

[emexes]

sometimes, i can not understand when (i do all time the same), the wal File is delete and the db File have the correct Timestamp.

To me, this sounds like the file is not being closed. The WAL (write-ahead log) is a list of changes that are going to be done to the database. When the changes have been done to the db file (and thus its time updated), only then the WAL would be deleted. Both of these things would happen at about the same time.

Is the db file actually under control of SQLite which is operating as a standalone program/process, ie something that your program *connects* to, that might still be running even after your program finishes and has exited?

Is there a FLUSH command you can use to tell SQLite to make sure everything is actually written to disk? Or if the change is part of a transaction (ie BEGIN), has the closing COMMIT somehow been bypassed? Does the COMMIT definitely happen?

 

[mw71]

To me, this sounds like the file is not being closed.

After i write (or update) the Database, i close it with SQL.Close. I think that's the right way.

Is the db file actually under control of SQLite which is operating as a standalone program/process, ie something that your program *connects* to, that might still be running even after your program finishes and has exited?

The Database is only use by my App

Or if the change is part of a transaction (ie BEGIN), has the closing COMMIT somehow been bypassed? Does the COMMIT definitely happen?

No, it's only a Single Execute with INSTER INTO....

 

[Erel]

I changed the file today (add an entry, 9/22/19), but the result is 9/19/19. I check with Totalcomander, the DB file is the change 19.9., the SHM and WAL file is 22.9.19. (correct timestamp).

What is the output of:

For Each f As String In File.ReadList(RelevantFolder)
 Log($"${f}: $date{File.LastModified(RelevantFolder, f)}"$)
Next

 

[mw71]

the Code does not work (Readlist need a File)
i use this Code:

For n = 0 To fileList.Size-1
  varFile = fileList.Get(n)
  Log($"${varFile}: $DateTime{File.LastModified(varBackup_qPhat, varFile)}"$)
Next

and Output:
Log_2019_09.db: 2019/09/22 19:52
Log_2019_09.db-wal: 2019/09/23 15:02
Log_2019_09.db-shm: 2019/09/23 15:02

 

[KMatle]

Log_2019_09.db: 2019/09/22 19:52
Log_2019_09.db-wal: 2019/09/23 15:02
Log_2019_09.db-shm: 2019/09/23 15:02

My two cents:

1. Changes are written to the wal- and shm files so the date & time is correct. The .bd file stays the same = old date
1. You Need to CLOSE the SQLite instance
2. Closing the instance = catalog is "commited" and the two files disapear (= good!) = db-file gets the "correct" date and time (last modified)
3. Only backup the db file!
4. Only backupd closed .db files (when no instance is using it)

 

[mw71]

hi,

1. the DB is Close via Close direct after Insert (or Update) a Dataset
2. that's the target, but i dont know why it dont work
3. Yes, i only backup the db file, but i think Erel need all File Timestamps
4. yes, it's normaly. The Backup start with a Service or when the App starts. The File is not open at this Time.

 

[Erel]

the Code does not work (Readlist need a File)

It should have been ListFiles instead of ReadList.

Anyway the output looks correct.

Closing the instance = catalog is "commited" and the two files disapear (= good!) = db-file gets the "correct" date and time (last modified)

That's not correct.
In WAL mode the database is made of three files. All three are required (if they exist). You can change the journal mode with a PRAGMA command.

 

[KMatle]

That's not correct.
In WAL mode the database is made of three files. All three are required (if they exist). You can change the journal mode with a PRAGMA command.

Yes, I was wrong here. You need to backup all the files but... The *.wal file only persists, when a previous process did not close the db correctly or when the app shuts down due to an exception.

So let me change my statement: I like to close processes "clean" when the job is done. In that case the wal-journal is written to the db and there will be only one file. For me it's the cleaner version because then we know that there are no issues with our app.

However. Most important thing is that everything works. Either with or without the two other files

@mw71 : Try to close, open and close the db and see what the date & time says.


From the docs:

4. The WAL File
...

The WAL file exists for as long as any database connection has the database open. Usually, the WAL file is deleted automatically when the last connection to the database closes. However, if the last process to have the database open exits without cleanly shutting down the database connection, or if the SQLITE_FCNTL_PERSIST_WAL file control is used, then the WAL file might be retained on disk after all connections to the database have been closed. The WAL file is part of the persistent state of the database and should be kept with the database if the database is copied or moved. If a database file is separated from its WAL file, then transactions that were previously committed to the database might be lost, or the database file might become corrupted. The only safe way to remove a WAL file is to open the database file using one of the sqlite3_open() interfaces then immediately close the database using sqlite3_close().

The WAL file format is precisely defined and is cross-platform.

 

[mw71]

Yes, I was wrong here. You need to backup all the files but... The *.wal file only persists, when a previous process did not close the db correctly or when the app shuts down due to an exception.

as I have already said (several times), the file is opened, the record is written, the DB is closed (with SQL.Close), no exception.....
For safty in Activity_Pause a second SQL.Close command (normaly it do nothing)

@mw71 : Try to close, open and close the db and see what the date & time says.

The App (and DB) is Closed, open the DB "read Only" (only a SELECT...., no Insert or Update or....), only the SHM File will be get a new Timestamp.
Now i have, after open, write and and close, add a SQL.Initialize and direct after a SQL.Close. That's Workaround works, but it's not nice.

You can change the journal mode with a PRAGMA command.

what is the correct command?
journal_mode=off ?
what are the effects?

 

[emexes]

as I have already said (several times), the file is opened, the record is written, the DB is closed (with SQL.Close), no exception.....

It is understandable that it sounds like we're not hearing you when you say you've closed the file, but at the same time, the symptom sure looks like: that file is not closed.

A scenario that matches both observations is: perhaps the file is not closed straight away. Is it possible that SQLite does some operations asynchronously? Or perhaps there is a delay with Android between the file being closed by one process/thread and the file timestamp change being visible from another. Perhaps the writes are buffered/sequenced for performance (agreed: elevator sequencing doesn't really make sense for a non-mechanical disk, but... who knows?). Perhaps your program is outrunning flash memory write speed.

What happens if you insert a Sleep in-between the SQL.Close and reading the file timestamps? Try 10 seconds, 1 second, 100 ms, 10 ms.

For safety in Activity_Pause a second SQL.Close command (normally it do nothing)

That observation is compatible with the above hypotheses.

Now i have, after open, write and and close, add a SQL.Initialize and direct after a SQL.Close.

That is plausibly compatible with the above hypotheses too - the SQL.Initialize would probably cause a flush of all pending interactions with the file system, or perhaps it simply takes long enough that it gives enough time for the file timestamp change to become visible.

That's Workaround works, but it's not nice.

I think we're all with you on this! Sometimes a bunch of reasonable actions interact to produce an unexpected side-effect. Ain't programming grand?!?!

 

[mw71]

hi,

i have check PRAGMA Journal_Mode, it's wal (more in the SQLite Docu, i think you can understand it bettes as i )

And i have add a sleep(100) before SQL.Close, looks nice, no wal/shm File is in the Folder and the Timestamp is correct......

 

[emexes]

And i have add a sleep(100) before SQL.Close

If you're not too tired of all this investigation: could you try it with and without the Sleep(100), see if that is indeed the "cure".

Also, I expected that the Sleep(100) would be needed *after* the SQL.Close, not before the SQL.Close.

If it is making a difference when *before* the SQL.Close, then I am confused. But still, if it works... then I am happy too.

 

[mw71]

with sleep(100) BEFORE, it wors somtimes, somtimes not :-(

now i test with sleep(100) befor and AFTER, looks like the same
but more test's: it's possible thats this is a problem in the Code that i have not found at this time.

 

[emexes]

with sleep(100) BEFORE, it works somtimes, somtimes not :-(

We are on the home stretch!!!

Right now, I am thinking that Android is flushing file operations from memory to disk on some regular basis. I vaguely remember that Unix works like this too, with a separate process (daemon) that goes to work when things get quiet or buffers get full.

So that would match your latest results of the delay "fixing" the problem, even before the SQL.Close, but only sometimes. If this thinking is correct, then I would expect that the longer you make the delay, the more it will "fix" the problem.

But I still expect it will work better if the delay is after the SQL.Close; I doubt that the file timestamp is updated after every file write, because that would double the number of disk operations per file write: one to update the data, another to update the timestamp. What is more likely is that the timestamp is only updated when the file is closed, or every say 100 file writes or 5 seconds or something like that.

 

[Erel]

I think that you are doing a mistake. Either assume that the database is made of all three files or change the journal mode. You don't need it to be in wal mode.