Data file 'modification date' not updating in split HSQLDB database in Windows 10/11 when file size 16M

pabloboyo · August 11, 2023, 8:50pm

I have developed a split HSQLDB 2.3.2 database application using resources found in this forum and the OO Forum (thanks to everyone!). The database sits on a cloud share and is used by me on LinuxMint 21.1 with LO 7.4.7.2 and another user with Windows 11 also using LO 7.4.7.2 but never at the same time.
The mydb.data file grew to 16M and I noticed that changes made to the data on the Windows machine were not being uploaded to the share whereas all the other files were synchronizing correctly.
This caused errors when opening the database in LinuxMint.
After much head scratching and testing I established that the Windows machine was saving the data changes to the mydb.data file but it was not updating the modified date stamp on the file, so the cloud server client was not uploading the changed file to the server.
This was tested on a local copy (not cloud shared) of the database on both windows and Linux. The windows machine did not update the files’ modified date stamp whilst it was updated on the Linux machine. I ran the same test on a Windows 10 virtual machine and the same issue occurred, so it is not just a windows 11 problem.

I ran the sql command SHUTDOWN COMPACT on a working copy of the database which significantly reduced the mydb.data file size down to 3M (I know, I should do db maintenance more often). This smaller data file size now gets its modified date updated by the Windows machine and normal function has returned.
Has anyone else seen this behavior? This could have a significant impact on date based backup systems as the files will appear to be unchanged based on the Windows modified date stamp. Do I have to be limited to less than 16M data file size when the system is running on Windows machines?

Villeroy · August 12, 2023, 7:35pm

I can use HSQLDBs with hundreds MB in size.
Connect to local databases and sync to cloud when done. Done means “office suite completely closed including any quick-starter.”

pabloboyo · August 13, 2023, 2:11pm

Thanks for your comment Villeroy.
What I do not understand is when I am done with the local (16M) database and close the office suite why does windows not update the file modified date stamp on mydb.data file while it does update the date modified timestamps on the files mydb.backup mydb.properties and mydb.script
Linux updates the filestamps on all four files.
Since compacting the database Windows is updating the timestamps on all four files.
If no one else has seen this phenomena then it must be somehow unique to my database and the two different versions of windows I have used for testing/troubleshooting

mariosv · August 13, 2023, 11:16pm

Perhaps because the database, which is opened by the HSQLDB server, is still running, and therefore can remain open even if the LibreOffice odb file is closed. Maybe LO does not send the correct command to the server to close the database.