OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

The .data file of a db don't update "last date modified

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Fri Jul 06, 2012 5:32 am    Post subject: The .data file of a db don't update "last date modified Reply with quote

Hello,
i've this strange problem with a .data file of i DB (i use the multi file mode DB):

when i open and update the db, the file is changed (i'm sure of this), but the last date modified is alwayls 25/05/2012 13.57
All the other database file have the last date modified updated correctly...

I've tried to copy the file on other drives and even to dropbox, but the date in the end is alwayls the same.

Since the file is obviously very important for the db, i'm a bit worried that it may be corrupted in some way.

Do you have any suggestions?

Thank's a lot
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jul 06, 2012 9:07 am    Post subject: Reply with quote

About the .data . . . you could try this, and, see if the 'date' reported by your computer operating system changes.
  1. Open your OpenOffice Base file ( *.odb )
  2. Press the Tables icon on the left
  3. From the Menu: Tools -> SQL...
  4. In the Command to execute box, enter:
    Code:
    checkpoint defrag

  5. Press the Execute button
  6. Press the Close button
  7. Close your OpenOffice Base file
  8. Check the date and time of your .data file
It is also important to note, if you are running HSQL as a SERVER, the .log file records all the changes, until, you shut down your server ( depending if you are running the database back-end as a SINGLE USER, or, a SERVER). Therefore, if running HSQL as a SERVER, the .data file will only be 'updated' when you shut it down, BUT, the .log file will have the database changes. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Mon Jul 09, 2012 2:50 am    Post subject: Reply with quote

Hi,
thank you for the explanation.

For clarity, I'm running the DB back-end as a SINGLE USER.

I've tried the checkpoint defrag, and the date has been updated to 09/07/2012 10.05 (and the .date file has been reducted form 32 Mb to 4...)

After those data update, i've updated various db records and i've closed and reopened the db many times, but the .data file 'date' is still 09/07/2012 10.15 (while all the other db file as the 'date' updated).

Since you said that it may be a shutdown problem, i've even tried the SQL 'shutdown' command after some records update, but the .data file has still the same date...
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jul 09, 2012 6:01 am    Post subject: Reply with quote

Please look at the response by DACM at the link below. specifically, whether you are using OpenOffice WITH the QuickStarter enabled Crying or Very sad :

http://user.services.openoffice.org/en/forum/viewtopic.php?f=13&t=54981

Just to be clear, looking at HSQL documentation, not knowing if you are running HSQL 1.8.0.10 or 2.2.8 ( or a similar version ) . . . per HSQL 1.8 documentation found at:

http://www.hsqldb.org/doc/1.8/guide/ch09.html

HSQL 1.8 Documentation: http://www.hsqldb.org/doc/1.8/guide/ch09.html wrote:

CHECKPOINT

CHECKPOINT [DEFRAG];

Closes the database files, rewrites the script file, deletes the log file and opens the database.

If DEFRAG is specified, this command also shrinks the .data file to its minimal size.


Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Wed Jul 11, 2012 12:53 am    Post subject: Reply with quote

Hi,
i post what DACM wrote on the other forum to keep all the information in the thread.

Quote:

(1) disable Quickstarter with production databases.
(2) try rebooting the computers at both locations.
(3) Recheck the 'data-source URL' at both locations for the correct path to the database files.
(4) Further troubleshooting might include checking the .properties file for modified=yes, after database updates have occurred but before database shutdown (with data in the .log file).



(1) i have the quickstarter disabled (i've disabled it long time ago)
(2) i've rebooted the computer (i not have the multi computer issue of the original DACM thread)
(3) I've checked the 'data surce url' (i've even moved all the file to another loaction and rechecked the url)
(4) i've found the modified=yes statement in my .properties file before the shutdown of the db, if this could help i post the entire content of my .properties file.

------------------------------------------
#HSQL Database Engine 1.8.0.10
#Wed Jul 11 09:48:13 CEST 2012
hsqldb.script_format=0
runtime.gc_interval=0
sql.enforce_strict_size=false
hsqldb.cache_size_scale=8
readonly=false
hsqldb.nio_data_file=true
hsqldb.cache_scale=14
version=1.8.0
hsqldb.default_table_type=cached
hsqldb.cache_file_scale=1
hsqldb.log_size=200
modified=yes
hsqldb.cache_version=1.7.0
hsqldb.original_version=1.8.0
hsqldb.compatible_version=1.8.0
------------------------------------------
There are further check i could do to understand what's heppened to my .data file?
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Wed Jul 11, 2012 6:04 am    Post subject: Reply with quote

darkshark wrote:
I've checked the 'data surce url' (i've even moved all the file to another loaction and rechecked the url)

There are further check i could do to understand what's heppened to my .data file?

Please do the following:
  1. Start your OpenOffice Base file ( *.odb )
  2. From the Menu: Edit -> Database -> Properties...
  3. Copy to your Clipboard, exactly what is in the box: Datasource URL
  4. Paste what is in your clipboard here, so DACM and I can 'review' it
Specifically, I want to know if shutdown=true; is a part of your Database URL. Smile

darkshark wrote:
(4) i've found the modified=yes statement in my .properties file before the shutdown of the db, if this could help i post the entire content of my .properties file.

Please tell me / us if AFTER[ you shut down ( close ) your Open Office Base file, if the .properties file for the line, modifield= if it is yes or no .

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Thu Jul 12, 2012 10:32 pm    Post subject: Reply with quote

- This is the datasource url of the DB:

hsqldb:file:/Documents and Settings/Laboratorio/Documenti/Dropbox/AlimPET/Petbase/PetBase;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

- After the shutdown of the db in the .properties file i have a modified=no line.

Thanks again for the help
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Fri Jul 13, 2012 6:23 am    Post subject: Reply with quote

darkshark:

Thank-you for posting your exact URL you are using to your database.

Yes, it does contain the statement shutdown=true which is what I wanted to confirm. And, you are using hsqldb.jar version 1.8.0.10 per the content of your .properties file, the top line and the last line.

According to HSQL version 1.8 docuementation:

HSQL 1.8 documentation: http://www.hsqldb.org/doc/1.8/guide/ch09.html#checkpoint-section wrote:

CHECKPOINT

CHECKPOINT [DEFRAG]

Closes the database files, rewrites the script file, deletes the log file and opens the database.

If DEFRAG is specified, this command also shrinks the .data file to its minimal size.


Therefore, from the Menu: Tools -> SQL...

when you issue the command:

Code:
checkpoint defrag

the .data file will be rewritten, and, the new date/time AND any 'empty' space will be removed.

Bottom line, I would recommend you do an occasional checkpoint defrag when you have made changes to the database, such as:
  1. INSERT of new data ( adding rows )
  2. UPDATE of current data ( changing content values for existing rows )
  3. DELETE of data ( removing rows from a table )
  4. ALTER of table definitions ( changing, adding or dropping of fields in a table row )
  5. CREATE new tables ( adding of additional tables to the database )
  6. DROP a table ( completely remove a table from the database definition )
I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
dacm
Super User
Super User


Joined: 07 Jan 2010
Posts: 769

PostPosted: Fri Jul 13, 2012 8:08 am    Post subject: Reply with quote

darkshark wrote:
...I've tried the checkpoint defrag, and the date has been updated...

...i've even tried the SQL 'shutdown' command after some records update, but the .data file has still the same date...

These two results just don't add up. Confused

Hmmm...you have a fairly large database (multi-megabyte)...and you're running it from a Dropbox folder...so uploading the updates to the cloud may take some time...I wonder if Dropbox is locking the .log or .data file while uploading the updates, thereby blocking the date-time update by HSQLDB, or worse, blocking the incorporation of the .log file into the .data file upon shutdown/checkpoint.

Perhaps troubleshoot:
(1) Copy your database folder to a non-Dropbox location.
(2) Also make a copy of your .odb file (and save to the new location).
(3) Update the Data-source URL in the .odb file to reflect the new database folder location.
(4) And then test for proper date-time update of your .data file in the new location
_________________
Soli Deo gloria
Tutorial: avoiding data loss with Base + Splitting 'Embedded databases'
Back to top
View user's profile Send private message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Mon Jul 16, 2012 1:34 am    Post subject: Reply with quote

I've done the following operations:

1) copied all the db files on another hard drive (anyway out of the dropbox folder)
2) updated the datasource url to match the new location
3) executed a checkpoint defrag and closed the db
---> the date of the .data file has been updated <----
4) reopened the db and updated a random record
5) checked the .properties files and found the modified=yes line
6) checked the .log file and found the record update
7) closed the db
8) checked the .properties files and found the modified=no line
6) checked that the .log file was deleted
---> the date of the .data file hasn't been updated <----

Sigh... :( It seems that my .data file is cursed...

Just to workaround the problem, since it seems that the.data date update only with a checkpoit defrag, it could be possible to set an automatic chekpoint defrag each time the db is closed?

Anyway thanks again for all the help! :D
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jul 16, 2012 6:59 am    Post subject: Reply with quote

darkshark:

Let us review one other possibility, remote as it might be.

A database defined table within HSQL Version 1.8 ( as you have indicated above you are using ) can be:
  1. CACHED
  2. VIEW
  3. TEXT
  4. MEMORY

A VIEW, comes from data stored in other TABLES.

A CACHED table is stored in the .data file.

A TEXT table definition ( table name, field names, and, file location ) is stored in the .script file.

A MEMORY table is stored in memory, but, when the database is shut down ( closed ) the data is stored in the .script file.

Having said all of this, open a NEW Query, and, run this SQL:

Code:
SELECT
   "A"."TABLE_TYPE",
   "A"."HSQLDB_TYPE",
   "A"."TABLE_NAME",
   "B"."COLUMN_NAME",
   "B"."TYPE_NAME",
   "B"."ORDINAL_POSITION",
   "B"."COLUMN_SIZE"
FROM "INFORMATION_SCHEMA"."SYSTEM_TABLES" AS "A",
     "INFORMATION_SCHEMA"."SYSTEM_COLUMNS" AS "B"
WHERE "A"."TABLE_SCHEM" = 'PUBLIC'
  AND "A"."TABLE_NAME" = "B"."TABLE_NAME"
ORDER BY "A"."TABLE_TYPE",
         "A"."TABLE_NAME",
         "B"."ORDINAL_POSITION"

The above Query, will show in the first returned column, the "TABLE_TYPE" ( TABLE or VIEW ) and the second column will show the "HSQLDB_TYPE" ( CACHED, TEXT, MEMORY ), the third column will return your "TABLE_NAME".

Please refer to HSQL 1.8 documentation, for more information.

http://www.hsqldb.org/doc/1.8/guide/ch09.html#create_table-section

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jul 16, 2012 7:30 am    Post subject: Reply with quote

darkshark:

I do not regularly use HSQL 1.8 as my database back-end. At the current time, I use HSQL 2.2.8.

However, I did set exactly as you described, that is, within OpenOffice, use HSQL 1.8 running outside of the OpenOffice compressed file ( .odb ).

And, yes, after adding a few records, the DATE of the .data file did NOT change, but, the data was / is there as entered.

Bottom line, it works as needed, recording all of the data and database changes as needed. Smile

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
darkshark
General User
General User


Joined: 08 Feb 2010
Posts: 32

PostPosted: Tue Jul 17, 2012 10:50 am    Post subject: Reply with quote

This is very very strange...

I'm sure that until the happening of the problem, the date of the .data file was updated after the shutdown of the db.
I'll explain you why:

I use a sync program that copy one way the db files from the pc where the db run, to another pc where another user can read the db data locally (i run the sync manually only after i've closed the db).
Until the happening of the problem the other user have all the db "updated" with the data i insert on the other pc after i've run the sync program.
Everything gone right for more than a year... until at one point the other user said that his copy of the db was not updated, and after some check i've discovered that the sync program don't update the .data file because the date is alwayls the same....

I was thinking that something gone corrupted in my .data file, but now that you say that you have got the same "problem" i'm really disappointed...
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Tue Jul 17, 2012 11:34 am    Post subject: Reply with quote

In a previous post ( towards the top ) . . . you indicated ( after I asked ) said:

darkshark wrote:
- This is the datasource url of the DB:

Code:
hsqldb:file:/Documents and Settings/Laboratorio/Documenti/Dropbox/AlimPET/Petbase/PetBase;default_schema=true;shutdown=true;hsqldb.default_table_type=cached;get_column_name=false

Please, make a change to the above . . . so it reads:

Code:
hsqldb:file:/Documents and Settings/Laboratorio/Documenti/Dropbox/AlimPET/Petbase/PetBase;default_schema=true;shutdown=true;shutdown compact;hsqldb.default_table_type=cached;get_column_name=false


The difference . . . I added the following: ;shutdown compact after ;shutdown=true

The intent, according to HSQL 1.8 documentation:

HSQL 1.8 documentation: http://www.hsqldb.org/doc/1.8/guide/ch09.html#shutdown-section wrote:

SHUTDOWN COMPACT

Writes out a new .script file which contains the data for all the tables, including CACHED and TEXT tables. It then deletes the existing text table files and the .data file before rewriting them. After this, it backs up the .data file in the same way as normal SHUTDOWN. This operation shrinks all files to the minimum size.

Since, it will be 'rewriting' the .data it will have an 'updated' date and time, which, is what you are asking.

In my sample, going against an HSQL 1.8 database, this did work. That is, it updated the .data file date and time, as well as shrinking it to its smallest size. Smile

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group