Bug 117118 - Firebird: Saving of data should be done automatically
Summary: Firebird: Saving of data should be done automatically
Status: RESOLVED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.0.3 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: jcsanz
URL:
Whiteboard: target:7.6.0 target:7.6.2 target:24.2...
Keywords: dataLoss
Depends on:
Blocks: Database-Firebird-Default Base-Without-Java
  Show dependency treegraph
 
Reported: 2018-04-20 06:03 UTC by Robert Großkopf
Modified: 2024-07-05 14:49 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Test-database: Delete the view, save and input new data in "tbl_test". (3.53 KB, application/vnd.oasis.opendocument.database)
2023-09-13 14:52 UTC, Robert Großkopf
Details
Video reproducing steps as in comment #18 (1.72 MB, video/mp4)
2023-09-13 20:39 UTC, jcsanz
Details
basic test database: create record, modify form, save changes, create additional record (25.09 KB, application/vnd.oasis.opendocument.database)
2023-09-14 08:15 UTC, Michael J
Details
here's the behavour described in comment #23 captured on creen (1.40 MB, video/mp4)
2023-09-14 08:16 UTC, Michael J
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Robert Großkopf 2018-04-20 06:03:07 UTC
Using HSQLDB you usually haven't to press "save" if only data have been changed. You have to leave the row and data will be saved automatically.

Using Firebird needs to press "save" of the main-window. Whithout pressing "save" all data of this session will be lost. 
Note: The "Save"-button of the table will be activated while changing data. But it will be deactivated when reaching the next row. It's only the "Save"-button of the document which shows: Changed data won't be saved without pressing "Save".
Comment 1 Buovjaga 2018-04-23 11:12:39 UTC
Sounds like a good enhancement to achieve parity with HSQLDB.
Comment 2 Alex Thurgood 2018-04-23 12:41:05 UTC
It is debatable whether this is a RFE or in fact a bug. Current behaviour is transparent for the user when using a hsqldb embedded database, and from what I recall this behaviour was introduced into the code deliberately - if so, then we should offer the same behaviour to users who switch to default embedded Firebird.
Comment 3 Lionel Elie Mamane 2018-04-23 13:02:27 UTC
(In reply to robert from comment #0)
> Using HSQLDB you usually haven't to press "save" if only data have been
> changed. You have to leave the row and data will be saved automatically.

Horrible hacks to do that were the source for some of the weirdness and corruption issues in embedded HSQL. It was decided that for Firebird, that error would not be repeated.
Comment 4 Alex Thurgood 2018-04-24 09:28:35 UTC
(In reply to Lionel Elie Mamane from comment #3)


> Horrible hacks to do that were the source for some of the weirdness and
> corruption issues in embedded HSQL. It was decided that for Firebird, that
> error would not be repeated.


Can I take that as a "WONTFIX" then ?
Comment 5 Robert Großkopf 2018-04-24 14:14:03 UTC
Shouldn't be a WONTFIX. 

The Save-button in the table is deactivated every time I switch from a input to next row. Then it is activated while I input new values. This behavior suggests: Data are saved. But data aren't saved, you have to save them a second time with the whole document.

I know: Every created table, every created query or form will be saved twice. But a user, who gets databases from me, doesn't change the forms, queries, tables ... He expects, like in webforms or while connecting Base to an external database: Saved data are saved. I haven't to press "Save" again.
Comment 6 Alex Thurgood 2018-05-09 10:55:05 UTC
(In reply to Lionel Elie Mamane from comment #3)


> Horrible hacks to do that were the source for some of the weirdness and
> corruption issues in embedded HSQL. It was decided that for Firebird, that
> error would not be repeated.


In that case, some kind of solution for reports like bug 117513 will need to be provided.
Comment 7 Drew Jensen 2018-05-09 11:25:14 UTC
(In reply to Lionel Elie Mamane from comment #3)
> (In reply to robert from comment #0)
> > Using HSQLDB you usually haven't to press "save" if only data have been
> > changed. You have to leave the row and data will be saved automatically.
> 
> Horrible hacks to do that were the source for some of the weirdness and
> corruption issues in embedded HSQL. It was decided that for Firebird, that
> error would not be repeated.

The hacks, IIRC, were a result of two issues. First the use of HSQL 1.8 which uses multiple files for the database storage. Second use of compression for OO files. 

The request from the Base developers early on was to not compress the ODB file to get around that but the project management team would not go along with that, the file would remain compressed like the others.

HSQL 2.0 was supposed to help precisely because it moved all the different files used into a single file - but that never happened.

IDK with the embedded firebird engine if there is a single file for everything. I suspect there is. If so than there should be a non-terrible way to offer something closer to what users expectations are.
Comment 8 Drew Jensen 2018-05-09 11:41:21 UTC
sorry - double post again.

There is another choice - rethink the decision to use compression on the ODB file and let the database engine do what it does naturally. 

I suppose though that would open up a whole other set of risks given the code base.
Comment 9 Robert Großkopf 2022-02-08 10:55:15 UTC
This enhancement is the same as it will be executed by the following macro:

SUB DataFlush
	DIM oDatasource AS OBJECT
	DIM oConnection AS OBJECT
	oDatasource = thisDatabaseDocument.CurrentController
	IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
	oData = oDatasource.DataSource()
	oData.flush
END SUB

At this moment I connect this procedure to 
Tools → Customize → Events → View is going to be closed.

Data will be saved and I have only save separately by dialog or button when there has something else been changed.
Comment 10 jcsanz 2023-03-06 22:02:41 UTC
I've sent a patch https://gerrit.libreoffice.org/c/core/+/148368 I think solves this problem, but I need some comments about it.

With the patch the database is stored with changes, as asked in the bug, when the connection is disposed, but I haven't disabled notifyDatabaseModified(), so database is shown as modified if you modify some data, and therefore ask to save changes when closing although the changes are going to be saved regarding the answer.

On the other side, showing database modified the users realize that there are changes without stored in the database and can save them while they are working (without close connection).
Should I disable notifyDatabaseModified()? what do you think?
Comment 11 jcsanz 2023-03-28 20:35:59 UTC
I've sent a new version of the patch, in this new version:
- The database is stored with changes when the connection is disposing, but unlike the previous version, this is done in a new function 
- A new function storeDatabase() has been created to store data. This function replaces part of the code in documentEventOccurred() and is used to store database when disposing connection
- notifyDatabaseModified() has been disabled, so no notification about changes in database are shown to user; this has been done because the data is going to be store without user intervention, so don't need any notification.

Can someone review the patch and make the necessary comments or approve it?
Comment 12 Commit Notification 2023-04-24 12:32:39 UTC
jucasaca committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/9227fbabe0a33134f56aefdd8ec16024f006a659

tdf#117118 - Saves data automatically in Firebird embedded database

It will be available in 7.6.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 13 Buovjaga 2023-06-13 09:17:22 UTC
Can this be closed as fixed?
Comment 14 jcsanz 2023-06-13 09:41:15 UTC
(In reply to Buovjaga from comment #13)
> Can this be closed as fixed?

Yes, it can. I've done
Comment 15 Robert Großkopf 2023-07-17 13:38:25 UTC
Have tested it with LO 7.6.0.1. Seems there are some problems with automatic saving in Firebird.

1. Had created views from a query. Could see this views, but sometimes the views have been gone when starting the *.odb-file next time.

2. Input data through a form: Have just need 3 times to get the data really saved. Data were saved in the form, could been seen in the table, but have been gone file has been opened next time. Note: One of the fields is an image control, but the image I saved is only 90kB.

I couldn't find reproducable steps to get such a behavior but the (old) solution without automatically saving is more stable.
Comment 16 jcsanz 2023-07-18 16:58:17 UTC
(In reply to Robert Großkopf from comment #15)
> Have tested it with LO 7.6.0.1. Seems there are some problems with automatic
> saving in Firebird.
> 
> 1. Had created views from a query. Could see this views, but sometimes the
> views have been gone when starting the *.odb-file next time.

The way the new database saving feature works is exactly the same as before. The only difference is that instead of triggering the database save method from a button it is done from an event, so I doubt there is any difference in behavior that can be blamed on this change.

> 2. Input data through a form: Have just need 3 times to get the data really
> saved. Data were saved in the form, could been seen in the table, but have
> been gone file has been opened next time. Note: One of the fields is an
> image control, but the image I saved is only 90kB.
>

Interaction with a form is not affected by the way the database is saved. The database is restored to a temporary file and interacted with from the form. This process has not been modified at all by the patch.

> I couldn't find reproducable steps to get such a behavior but the (old)
> solution without automatically saving is more stable.

In any case, as always, being able to reproduce the problem would help a lot in its resolution.
Comment 17 Michael J 2023-09-13 12:22:07 UTC
I have to confirm Roberts' problems from Comment #15 running 7.6.0 on Arch Linux.

This happens to me working on an .odb file containing an embedded Firebird database (migrated from embedded HSQL quite some time ago). Roughly one in 3 to 4 times opening the .odb file and editing, none of the records created via forms are saved, regardless of having pressed the "save" buttons in the form or the main menu.

(Un)fortunately, I cannot reliably reproduce this behaviour - it seems to happen more often if the file in question is open/edited over a prolonged period of time.

The file is stored in an Veracrypt container formatted as ext4, maybe there is something going wrong merging the temp file? Downgrading to 7.5.6 fixes the problem, however.
Comment 18 Robert Großkopf 2023-09-13 14:52:32 UTC
Created attachment 189553 [details]
Test-database: Delete the view, save and input new data in "tbl_test".

Have tested a little bit with
Version: 7.6.1.2 (X86_64) / LibreOffice Community
Build ID: f5defcebd022c5bc36bbb79be232cb6926d8f674
CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

If I open a Firebird database and create something or delete something, which has to been saved I have to press "Save". After I have pressed "Save" all following input won't be send any more to database.

Open the attached database.
Delete "viw_person".
Save database file.
Open table "tbl_test" for input data.
Write some content in field "value", for example '1' in first row, '2' in second row - I did this for 5 rows, all primary keys will be shown right.
Close the table.
Close Base.
Close LO.
Reopen LO.
Reopen the database file.
Open "tbl_test" to have a look.
No data will be shown here.

Changing data in "tbl_test" without changing anything else, which has to be saved by pressing "Save", closing and reopening will work. But if there has to been press "Save" all changes after this action will be lost.
Comment 19 jcsanz 2023-09-13 20:13:49 UTC
(In reply to Michael J from comment #17)
> This happens to me working on an .odb file containing an embedded Firebird
> database (migrated from embedded HSQL quite some time ago). Roughly one in 3
> to 4 times opening the .odb file and editing, none of the records created
> via forms are saved, regardless of having pressed the "save" buttons in the
> form or the main menu.
That is very strange. The main menu button still does the same function it did before my patch, the only change is that the button does not show when there are changes.
What my patch does is to save the changes when closing the .odb file (as Robert suggested he does with a macro), regardless of whether or not the changes were saved previously. And the way to save the data is exactly the same as before, I have not changed anything in that code.
Comment 20 jcsanz 2023-09-13 20:39:48 UTC
Created attachment 189560 [details]
Video reproducing steps as in comment #18

I have reproduce the steps as in Robert's comment #18 and no errors are showing to me.
See attached video
Comment 21 Robert Großkopf 2023-09-14 05:55:37 UTC
(In reply to jcsanz from comment #20)
> Created attachment 189560 [details]
> Video reproducing steps as in comment #18
> 
> I have reproduce the steps as in Robert's comment #18 and no errors are
> showing to me.
> See attached video

All steps are right. I do the same and when reopening the database - there are no data in tbl_test.
Tested here with LO 7.6.1.2 in OpenSUSE 15.4 64bit rpm Linux and KDE.
It's the same when starting with parameter SAL_USE_VCLPLUGIN=gen: No data.

Which system do you use?
Comment 22 Robert Großkopf 2023-09-14 06:20:55 UTC
(In reply to jcsanz from comment #20) 
> I have reproduce the steps as in Robert's comment #18 and no errors are
> showing to me.
> See attached video

One difference I see, but it couldn't be the reason for saving or not.
I can't open the database file directly on my system. It will open the LO-version of OpenSUSE, which doesn't support Firebird. So I first open LO (more than 30 different versions installed here), then open the database file from recent documents.
Comment 23 Michael J 2023-09-14 08:15:30 UTC
Created attachment 189565 [details]
basic test database: create record, modify form, save changes, create additional record

I can now reliably recreate the problem with Roberts test database as well as the one attached.

Steps to Reproduce:
1) Create a new record in form or table view.
2) Make a basic change to the database file, e.g. change the position of a form field, which would prompt the save button in the main window to indicate unsaved changes.
3) Hit the "Save" button in the main window.
4) Create one or more records after having saved the "basic" changes.
5) Close and reopen the .odb file.

Expected Behaviour: All records are saved permanently.
Observed Behaviour: Any record created after hitting the "Save" button is lost after closing and reopening.
Comment 24 Michael J 2023-09-14 08:16:49 UTC
Created attachment 189566 [details]
here's the behavour described in comment #23 captured on creen
Comment 25 jcsanz 2023-09-14 14:12:14 UTC
(In reply to Robert Großkopf from comment #21)
> Which system do you use?
Version: 7.6.0.3 (X86_64) / LibreOffice Community
Build ID: 69edd8b8ebc41d00b4de3915dc82f8f0fc3b6265
CPU threads: 16; OS: Windows 10.0 Build 22621; UI render: Skia/Raster; VCL: win
Locale: es-ES (es_ES); UI: es-ES
Calc: CL threaded Jumbo

(In reply to Robert Großkopf from comment #22)
> One difference I see, but it couldn't be the reason for saving or not.
> I can't open the database file directly on my system.
No difference opening it from the file or from Base. As you suspect that is not the reason.

The problem is reproducible every time the *Save* button is pushed, don't mind what has been done before, also by clicking on the button before doing anything else and editing the table directly.

I've tested the error, and is always reproducible in:
-----------------------
Version: 7.6.1.2 (X86_64) / LibreOffice Community
Build ID: f5defcebd022c5bc36bbb79be232cb6926d8f674
CPU threads: 3; OS: Linux 5.15; UI render: default; VCL: kf5 (cairo+xcb)
Locale: es-ES (es_ES.UTF-8); UI: es-ES
Calc: threaded
-----------------------


Since the problem is not reproducible in Windows and it is reproducible in many different Linux environments, I think is Linux specific. (also it should be tested on MacOS).

I don't have a Linux development environment, so it will take some time to me to prepare it and investigate.

Given that now this is an error and not an enhancement like was at the beginning, perhaps we should change the *title* and the *importance*, what do you think, @Robert?
Comment 26 Robert Großkopf 2023-09-14 14:42:21 UTC
(In reply to jcsanz from comment #25)
> (In reply to Robert Großkopf from comment #21)
> 
> Given that now this is an error and not an enhancement like was at the
> beginning, perhaps we should change the *title* and the *importance*, what
> do you think, @Robert?

Importance should be high, because of dataloss. But this is a new bug, which doesn't appear (in Linux) before this enhancement has been created.

I will open a new bug and link to this bug.
Comment 27 Alex Thurgood 2023-09-19 15:32:16 UTC
Repro with 

Version: 7.6.1.2 (AARCH64) / LibreOffice Community
Build ID: f5defcebd022c5bc36bbb79be232cb6926d8f674
CPU threads: 8; OS: Mac OS X 13.4; UI render: Skia/Raster; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded


This works correctly with 
Version: 7.5.6.2 (AARCH64) / LibreOffice Community
Build ID: f654817fb68d6d4600d7d2f6b647e47729f55f15
CPU threads: 8; OS: Mac OS X 13.4; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

==>> regression
Comment 28 Alex Thurgood 2023-09-19 15:34:25 UTC
With
Version: 7.5.6.2 (AARCH64) / LibreOffice Community
Build ID: f654817fb68d6d4600d7d2f6b647e47729f55f15
CPU threads: 8; OS: Mac OS X 13.4; UI render: default; VCL: osx
Locale: fr-FR (fr_FR.UTF-8); UI: fr-FR
Calc: threaded

I get asked upon closure whether I want to save the changes made, i.e. the added tuples. Clicking on Save then writes those changes to the ODB file and closes the file.

After shutting down LO and re-opening the ODB the added tuples are still there, as they should be.
Comment 29 Alex Thurgood 2023-09-19 15:38:28 UTC
Sorry for comments 27 and 28, I meant to add them to bug 157237.
Comment 30 Xisco Faulí 2023-09-20 11:36:14 UTC
On linux, I'm getting this error when I'm closing the document

warn:connectivity.firebird:10832:10832:connectivity/source/drivers/firebird/Util.cxx:58: firebird_sdbc error:
*I/O error during "open" operation for file "/tmp/lu108321pk2y.tmp/lu108321pk5i.tmp/firebird.fdb"
*Error while trying to open file
*No such file or directory
*Exiting before completion due to errors
caused by
'isc_service_query'
Comment 31 Robert Großkopf 2023-09-20 13:14:54 UTC
(In reply to Xisco Faulí from comment #30)
> On linux, I'm getting this error when I'm closing the document
> 
> warn:connectivity.firebird:10832:10832:connectivity/source/drivers/firebird/
> Util.cxx:58: firebird_sdbc error:
> *I/O error during "open" operation for file
> "/tmp/lu108321pk2y.tmp/lu108321pk5i.tmp/firebird.fdb"
> *Error while trying to open file
> *No such file or directory

Indeed: The database file will be removed from tmp files after pressing the button for saving data.

Seems firebird.fdb will be closed after it has be opening when file has been opened and data will be saved. But firebird.fbk (compressed file) will still be open in LO 7.4.7.2 and also LO 7.5.5.2. Since LO 7.6 both files will be closed and there is no connection to the database any more.
Comment 32 jcsanz 2023-09-20 21:44:21 UTC
I've revered the patch until further investigation.
Comment 33 Commit Notification 2023-09-21 05:08:13 UTC
Juan C. Sanz committed a patch related to this issue.
It has been pushed to "libreoffice-7-6":

https://git.libreoffice.org/core/commit/149735f7bfbd8bdfeb405f281af0d1c17d8327ec

Revert "tdf#117118 - Saves data automatically in Firebird embedded database"

It will be available in 7.6.2.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 34 Commit Notification 2023-09-21 05:08:16 UTC
Juan C. Sanz committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/fbf71a4c21106fbed7c070ba940809fd84c76dfe

Revert "tdf#117118 - Saves data automatically in Firebird embedded database"

It will be available in 24.2.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 35 Xisco Faulí 2023-09-28 13:41:29 UTC
lowering importance again now that the problematic commit has been reverted
Comment 36 Commit Notification 2024-02-01 13:15:32 UTC
Juan c. Sanz committed a patch related to this issue.
It has been pushed to "master":

https://git.libreoffice.org/core/commit/829374381d9c9191746d528b52411ad757e39a08

tdf#117118 - Saves data automatically in Firebird embedded database

It will be available in 24.8.0.

The patch should be included in the daily builds available at
https://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More
information about daily builds can be found at:
https://wiki.documentfoundation.org/Testing_Daily_Builds

Affected users are encouraged to test the fix and report feedback.
Comment 37 Robert Großkopf 2024-02-02 15:28:32 UTC
Have tested this again with
Version: 24.8.0.0.alpha0+ (X86_64) / LibreOffice Community
Build ID: 3fc0eb2bc8b439bda286e0c87a9814d90cc9d9d5
CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: en-US
Calc: threaded

Seems the fix works well.
Could edit a table.
Could save.
Could input content.
Content is there.

And also, very important:
Bug 117513 has been solved.
Could input data.
Could close external form.
Data will be saved in the database.

Great!