[Solved] HSQLDB 2.3.3 checkpoint defrag changes all dates

Discuss the database features

[Solved] HSQLDB 2.3.3 checkpoint defrag changes all dates

Postby roblo525 » Fri Dec 04, 2015 3:29 am

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.
Last edited by roblo525 on Mon Dec 07, 2015 11:40 pm, edited 1 time in total.
OpenOffice 4.1.2 Base split with HSQLDB 2.3.3 on windows 7
roblo525
 
Posts: 4
Joined: Fri Jan 16, 2015 8:46 pm

Re: hsqldb 2.3.3 checkpoint defrag changes all dates in data

Postby Villeroy » Fri Dec 04, 2015 2:06 pm

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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: hsqldb 2.3.3 checkpoint defrag changes all dates in data

Postby roblo525 » Fri Dec 04, 2015 5:41 pm

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?
OpenOffice 4.1.2 Base split with HSQLDB 2.3.3 on windows 7
roblo525
 
Posts: 4
Joined: Fri Jan 16, 2015 8:46 pm

Re: hsqldb 2.3.3 checkpoint defrag changes all dates in data

Postby Villeroy » Fri Dec 04, 2015 7:45 pm

Close all connections.
Shut down your database cleanly:
Code: Select all   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24584
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: hsqldb 2.3.3 checkpoint defrag changes all dates in data

Postby roblo525 » Mon Dec 07, 2015 11:32 pm

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.
OpenOffice 4.1.2 Base split with HSQLDB 2.3.3 on windows 7
roblo525
 
Posts: 4
Joined: Fri Jan 16, 2015 8:46 pm

Re: [Solved] HSQLDB 2.3.3 checkpoint defrag changes all date

Postby MTP » Mon Aug 21, 2017 5:57 pm

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.
OpenOffice 4.1.1 on Windows 10, HSQLDB 1.8 split database
User avatar
MTP
Volunteer
 
Posts: 1618
Joined: Mon Sep 10, 2012 7:31 pm
Location: Midwest USA


Return to Base

Who is online

Users browsing this forum: No registered users and 10 guests