[Solved] HSQLDB 2.3.3 checkpoint defrag changes all dates

[roblo525]

I recently updated my hsqldb1.8 database to 2.3.3. When I issued the checkpoint defrag command every date in every date field was changed to an arbitrary date like 1/16/1970 and 1/17/1970. Any ideas? I am using openoffice 4.0 and hsqldb 2.3.3 on a windows 7 and same results with windows 10.

Thanks for the help.

[Villeroy]

I ran checkpoint defrag on all my HSQL demo databases using HSQL 2.3.3 and in one case I could reproduce the problem. Restarting the office suite fixed the problem.

[roblo525]

I was able to reproduce the situation in a single user environment and then correct it by restarting the suite. However I am unable to accomplish the same within a multi-user environment. The dates remain incorrect. I even copied the data files and ran it in a localhost environment and was unable to fix the date issue. For now I am just not running the checkpoint defrag command. I still do not understand why that command would have such an effect on the data. Seems like a bug. Any Ideas on how to correct this?

[Villeroy]

Close all connections.
Shut down your database cleanly:

Code: Select all

java.exe -jar "C:\hsqldb\lib\sqltool.jar" --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://192.168.15.1/DatabaseName,User=SA,Password=YourDBPassword

adjust the path to hsqltool.jar, the IP, the database name and the log-in.

[roblo525]

Villeroy,

I was able to recreate the situation in a multi-user environment and your answer to the problem worked like a charm! I was shutting the database down using this code:

java.exe -jar "C:\hsqldb\lib\sqltool.jar" --sql "shutdown;" --inlineRC url=jdbc:hsqldb:hsql://localhost,User=SA,Password=YourDBPassword

When the database is shut down like this, not only did it mess up the dates in every table, it also started spitting out io errors and java heap errors. Eventually it would not allow any new records to be entered.
I tried recovering the database by issuing the new shutdown command but the files would not work properly after being shutdown and restarted so many times using the wrong shutdown command. Thank Goodness for backups!!

Thank you very much for the fast and professional help. This forum has been a wealth of information and help in the building of my database. Keep up the good work.

[MTP]

I don't think it is the localhost that caused this error. It just happened to me (checkpoint defrag setting all dates to two days 1970), and running the shutdown .bat file WITH the localhost instead of the IP (and then restarting) fixed the problem.

Maybe it's something about running the defrag command from inside Base with Tools→SQL? If I want to shrink the .data file again I might try it with a script instead of from Base.