In LibreOffice we used the command SHUTDOWN COMPACT to compact databases, now in LO-Firebird when I use this command it gives me an error:
1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 1
*SHUTDOWN
caused by
‘isc_dsql_prepare’
Does anybody know how to compact the database?
OMG. That means it will keep growing and growing?
The parts of database (pages) that were used by such data are marked as empty and Firebird will reuse them next time it needs to write new data.
Unfortunately, depending upon the data deleted (such as my example) it can take quite some time to recover the unused space. If you decide the start with embedded images then later change it to linked images the space unused can be 10’s of mb. Even deleting the table & creating anew will not recover the space. I still see this as a problem/bug. Doing this on my simple test left me with a 134.3mb file with only two images (about 25mb).
Yes, totally I agree with you. I not found any bug report with this topic, maybe it’s time for it.
While, perhaps an extension for this job
Hello,
This appears to be a potentially major problem especially if dealing with embedded images. A test using two embedded images created a firebird.fbk file of 27.0mb. Then one record in the DB was deleted but the database size remained unchanged.
Then extracted the firebird.fbk file from Base and used the firebird backup utility to restore and again backup the DB. This reduced the database to under 10mb. gbak (backup utility) commands used:
Restore:
sudo gbak -c -v -user SYSDBA firebird.fbk firebird.fdb
Then Backup:
sudo gbak -v -t -user SYSDBA firebird.fdb firebird.fbk
When the new backup was again inserted into the Base file there were no problems but the size was considerably reduced.
This should be reported as a bug on Bugzilla as there seems to be no method to do this without external steps.
Also of note, deleting a record did not reduce the DB size but after doing this, if new records were added the DB seems to reuse the deleted area for the new records.
Still appears to be a problem.
I am just wondering if simply making a new (empty) FB database in Base and then just dragging in the tables, queries, forms etc from the old FB Base file (and then saving) would do the job? NOTE: I have tried on a couple of my FB Base files, and there does seem to be a small decrease in file-size with this procedure, but they are pretty compact files to start with (i.e. no deleted embedded images were used).
I see no reason this would not work. Tried a quick test with the embedded image DB but as I suspected this is a bug and will not copy. Will try a bit later with some different type of files and post again but see no reason it will not work.
Don’t know what is more trouble though - creating & transferring to a new Base file or re-sizing as in my answer. Think @mauricio comment about an extension may be another good alternative.
OK a new test.
New firebird embedded Base file. Copied into it a large table of 81K+ records (20 cols of text) and saved. Resulting file was 3.1mb. Deleted all but 10 records. Saved file (expected) was still 3.1mb.
Created new firebird Base file. Copied above table into it & saved. Result was file size of 3.8K.
I started view the defects in Firebird, for that, I prefer Postgresql and directly connect with Python. But while, I will see is possible develop an extension for this.
1 Like
Personally use PostgreSQL also but have others set up to answer questions here.
Look forward to seeing what you may come up with.
i have been using LO with FB embedded(e) and with FB external(x) on a duplicate DB: -
- the FBx .fdb file will grow, and grow and grow and grow … until a Restore-with-garbage-collection is done
-FBe on LO stores only a .fbx file > >> therefor I assume it is unpacked and Restored as part of Base startup (???) - - - if so, then presumably this a rapid Restore-WITHOUT-garbage-collection (???), using the switches available in gbak.
If that is all correct, then maybe the LO Base code only needs to expose the gbak switch as an option for Base user - e.g. “compact DB at next Base startup?(this will slow down file opening) Y/N” …
!LoveULibre!
1 Like
just a quick follow up …
i looked back over some .odb that had some forms/reports/macros copied both from earlier versions of LO(6) and between ‘duplicate’ projects using Base(firebird emb.) and Base+FireBird Ext.
both have had some aggressively-non-optimized routines which involve some large (in context) delete-insert cycles, and I initially thought that some bloating in the .odb files would track back to this …
… then I thought about that a bit more - and then looked in the .odb zip , where several items are of note regarding unexpectedly large sizes for Forms and Reports;
1 - at some point a .png or JPEG had been swapped for higher resolution source (oops) and maybe some residual/corrupt file for a form which previously deleted an image
2 - several old reports had a folder called ObjectReplacements, with a single large ‘report file’ - don’t know what it is or where from (relic from LO6?), but experiment so far shows no ill effects by simply deleting this folder and file
Tidying these up significantly reduced the .odb file size!
But I also noticed that old(copied)Forms were still maybe bigger that they ought reasonably to be, compared to a comparable newly created form in LO7.4 for example.
I found that copying the elements (i.e. with select-all in design-view) into a new blank form, rather than copying the whole form straight between .odbs, reduced file size again by a noticeable amount, although I don’t know what ‘redundant data’ was stripped out …
Overall this reduced .odb size by 70%+ !!! - so worth considering if you have copied forms between LO/Base versions.
I also quickly checked mass deletes from tables - and this did indeed reduce the size of saved .odb by a viable amount(e.g. -20Kb in this case) - so my previous comments above are misplaced …(phew)
…!LoveYouLibre!