Bug 120353 - Base optional fields now seem to be mandatory
Summary: Base optional fields now seem to be mandatory
Status: RESOLVED DUPLICATE of bug 120245
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.2.1 release
Hardware: x86-64 (AMD64) Linux (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2018-10-05 20:03 UTC by tim
Modified: 2018-12-05 20:19 UTC (History)
8 users (show)

See Also:
Crash report or crash signature:


Attachments
Test database (10.68 KB, application/vnd.oasis.opendocument.database)
2018-10-05 21:08 UTC, tim
Details

Note You need to log in before you can comment on or make changes to this bug.
Description tim 2018-10-05 20:03:02 UTC
Description:
I have mysql (mariadb) tables with optional fields.  Base use to allow this, but now seems to insist every field must have a value.

Steps to Reproduce:
1.I save a record with some fields omitted


Actual Results:
Base gives an error saying:
"Error writing data to database"
"Input required in field 'Rate'. Please enter a value."


Expected Results:
Should update the data table with some fields omitted


Reproducible: Always


User Profile Reset: No



Additional Info:
Version: 6.1.2.1
Build ID: 1:6.1.2~rc1-0ubuntu0.18.04.1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: threaded
Comment 1 tim 2018-10-05 20:14:49 UTC
I cannot submit my real database easily, so I will have to try and reproduce on a new test database.  I will do so when I can get time.  I may have to revert to an older version to keep my system working.

I searched for similar issues but didn't find any.

This was OK on 6.1.1 rc2 until the 6.1.2 rc1 update I received today on ubuntu.
Comment 2 tim 2018-10-05 21:08:43 UTC
Created attachment 145420 [details]
Test database

It turned out to be simple to replicate this issue. The attached is a self-contained database (HSQLDB) with one table (table1) and one form (table1).

One can enter data directly into the table with no problem, omitting fields f2 and/or f3, both of which are defined as optional (f1 is auto-incremented primary key).

Using form table1, neither field f2 nor f3 can be omitted.
Comment 3 MM 2018-10-05 23:28:32 UTC
Unconfirmed on ubuntu 16.04 x64 with Version: 6.2.0.0.alpha0+
Build ID: ad6adb1bfadf49af3187a0bb3ceffbf355e9eed1
CPU threads: 2; OS: Linux 4.4; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:master, Time: 2018-09-29_02:45:20
Locale: en-US (en_US.UTF-8); Calc: threaded

No problem entering data and no errors.
Comment 4 frofa 2018-10-06 06:39:00 UTC
Is this a duplicate of Bug 120289 - Form makes all fields obligatory? If so, it looks like the problem has been fixed in subsequent LO Base releases
Comment 5 Julien Nabet 2018-10-06 07:23:09 UTC
It's fixed from next release 6.1.3.
Don't hesitate to reopen this tracker if you still reproduce this with this next version.

*** This bug has been marked as a duplicate of bug 120245 ***
Comment 6 tim 2018-10-06 08:05:36 UTC
Now if only I had searched for 'obligatory' rather than 'mandatory' :)

I'll revert to 6.0 for normal use, await 6.1.3, and try a dev 6.2.
Comment 7 tim 2018-10-06 08:43:15 UTC
Thanks.

I confirm the bug is fixed in:

Version: 6.1.3.0.0+
Build ID: 8ef25505303dcd744d20abf7e328ce1f0eda4dbf
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-10-05_08:35:00
Locale: en-GB (en_GB.UTF-8); Calc: threaded

I won't try 6.2 now, and have reverted to 6.0.6 for normal use.
Comment 8 tim 2018-10-12 09:54:47 UTC
Unfortunately the fix has not got into the ubuntu pre-release:

Version: 6.1.3.1
Build ID: 1:6.1.3~rc1-0ubuntu0.18.04.1
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
Locale: en-GB (en_GB.UTF-8); Calc: threaded

I checked against a slightly later version of daily builds that I downloaded for another issue and the fix is not there either:

Version: 6.1.3.0.0+
Build ID: 7bafe2441480e2b88d999b30b7f117f05e72c3b3
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-10-08_08:54:10
Locale: en-GB (en_GB.UTF-8); Calc: group threaded
Comment 9 Julien Nabet 2018-10-13 13:52:31 UTC
I don't reproduce this on master sources updated today.
It seems I can't help here.
Comment 10 tim 2018-10-13 16:30:28 UTC
I've just downloaded the latest overnight 6.1.4 build as below.  The issue is still present.  It was fixed in 6.1.3 as of 5th October, the the fix disappeared.

Version: 6.1.4.0.0+
Build ID: 7ea7b86e7731f8cc1366ea632653fecc97267378
CPU threads: 8; OS: Linux 4.15; UI render: default; VCL: gtk2; 
TinderBox: Linux-rpm_deb-x86_64@70-TDF, Branch:libreoffice-6-1, Time: 2018-10-13_04:47:20
Locale: en-GB (en_GB.UTF-8); Calc: group threaded

It may be quite  while before I can run a 6.2 as my normal version.
Comment 11 Julien Nabet 2018-10-13 16:49:18 UTC
Alex/Robert: do you reproduce this one too or got some idea here?
Comment 12 tim 2018-10-13 16:59:07 UTC
Just to say that I detected the issue on my live system, and have now retested against the demo database I attached to this report, and the issue is still unresolved on ubuntu with the standard (ie non-ubuntu) LO download.  

I could retest under Windows 10 if that would help.  Let me know.
Comment 13 Robert Großkopf 2018-10-13 17:55:55 UTC
(In reply to Julien Nabet from comment #11)
> Alex/Robert: do you reproduce this one too or got some idea here?

Have tested it with
Version: 6.1.3.1
Build-ID: a9670562c26181ec3afbe381c9ff499ae88c98b7
CPU-Threads: 6; BS: Linux 4.12; UI-Render: Standard; VCL: kde4; 
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group threaded

Works as expected.

Note 1: Both fields in the form are set to "Input required" = 'yes'.
If you set 
Properties > Data > Input required > No
there won't appear an error.

Note 2: If you create fields in a form the default-value for "Input required" is 'yes'. Many users would be irritated. They haven't used this function (because it doesen't work the right way). Now it works and the input is required on every field, because the default is set for this.

With OpenSUSE 15 and LO 6.1.3.1 this bug seems to be resolved.
Comment 14 tim 2018-10-13 19:36:08 UTC
So I, and presumably large numbers of other people, will have to check every single field, on every single form and subform because a very long-standing bug has been fixed that we didn't know about?  

Gulp.  That's going to be a heck of a lot of work.  I've many tens of forms and many tens of subforms in my 2 main applications that used to work, mainly based on database optional/mandatory settings and sometimes database default values (so a form field can be omitted but a default set from the database).

Are you all quite sure that this is what is required by everyone?


It's also a bit strange to me that the behaviour I saw was:

6.1.2 insisted fields be filled in due to "Error writing data to database"

6.1.3.0 of 5/10/18 then worked OK for me

6.1.3.0 of 8/10/18 and subsequent versions insist that fields that are optional in the database be filled in and still claim that there is an "Error writing data to database", not that a field on the form has 'input required', which is not the same thing at all.

I have to say I'd expect the mandatory/optional status of a database field would override anything set on the form, or at least be given the option to allow this.
Comment 15 Robert Großkopf 2018-10-14 07:40:20 UTC
If a field is set to "Input required" you decide this for the input in the form. You should get a message from the GUI, if the input is missing. This message is translated to the GUI-language.

If a field is set to "Entry required" in the table-definition you should get a message from the database, if there is no entry. This message isn't translated.

OK, many people haven't had a look at control > properties > data > input required. It would have been better for the form-wizards to set "Input required" to 'No' as default-value. But nobody recognized this before, because the GUI-settings had only worked together with the SQL-settings.

One example:
You have an addressbook. You need postcode, street and so on for the addresses. You could set this to "Entry required". But now you will decide: I don't need the address if there is an email. So "Input required" could be set by macro to all this fields and if there is an input for the address you could switch by macro the input for email isn't needed. This behaviour you couldn't get by "Entry required" in SQL.

If you prefer to set all fields of a form to "Input required" = 'no' you could ark all input-fields together (Hold Alt + CTRL and click on the fields) and set this for all fields.
Comment 16 tim 2018-10-14 08:47:56 UTC
(In reply to robert from comment #15)
> If a field is set to "Input required" you decide this for the input in the
> form. You should get a message from the GUI, if the input is missing. This
> message is translated to the GUI-language.
> 
> If a field is set to "Entry required" in the table-definition you should get
> a message from the database, if there is no entry. This message isn't
> translated.
> 
> OK, many people haven't had a look at control > properties > data > input
> required. It would have been better for the form-wizards to set "Input
> required" to 'No' as default-value. But nobody recognized this before,
> because the GUI-settings had only worked together with the SQL-settings.
> 
> One example:
> You have an addressbook. You need postcode, street and so on for the
> addresses. You could set this to "Entry required". But now you will decide:
> I don't need the address if there is an email. So "Input required" could be
> set by macro to all this fields and if there is an input for the address you
> could switch by macro the input for email isn't needed. This behaviour you
> couldn't get by "Entry required" in SQL.
> 
> If you prefer to set all fields of a form to "Input required" = 'no' you
> could ark all input-fields together (Hold Alt + CTRL and click on the
> fields) and set this for all fields.

Thanks.  I understand all that. but it's still going to be a lot of work (a least a day of checking & cross-checking  & fixing).  I hope other users feel they have the time.

I still don't think the GUI error messages are correct, since, as I wrote, I get a message saying the database field is missing when it is optional in the database, but has 'input required' on the form.
Comment 17 Robert Großkopf 2018-10-14 09:44:05 UTC
(In reply to tim from comment #16)

> I still don't think the GUI error messages are correct, since, as I wrote, I
> get a message saying the database field is missing when it is optional in
> the database, but has 'input required' on the form.

I have had a look at this in the example-database. The error-message of the database indeed doesn't appear. The message, which appears, is always the same. It is the message of the GUI, which is available in different languages.

If you try the same in the table this message would appear for a field with "Entry required":
"Fehler beim Einfügen des neuen Datensatzs"
"Attempt to insert null into a non-nullable column: column: f2 table: Table1 in statement [INSERT INTO "Table1" ( "f3") VALUES ( ?)]"
(Only the header is translated, SQL-error appears).

In the form the header for the message is misleading: It is only an "Error writing data to database" if there is "Entry required". It could be something like "Error submitting form" if there is "Input required".
Comment 18 tim 2018-10-14 09:50:09 UTC
(In reply to robert from comment #17)
> (In reply to tim from comment #16)
> 
> > I still don't think the GUI error messages are correct, since, as I wrote, I
> > get a message saying the database field is missing when it is optional in
> > the database, but has 'input required' on the form.
> 
> I have had a look at this in the example-database. The error-message of the
> database indeed doesn't appear. The message, which appears, is always the
> same. It is the message of the GUI, which is available in different
> languages.
> 
> If you try the same in the table this message would appear for a field with
> "Entry required":
> "Fehler beim Einfügen des neuen Datensatzs"
> "Attempt to insert null into a non-nullable column: column: f2 table: Table1
> in statement [INSERT INTO "Table1" ( "f3") VALUES ( ?)]"
> (Only the header is translated, SQL-error appears).
> 
> In the form the header for the message is misleading: It is only an "Error
> writing data to database" if there is "Entry required". It could be
> something like "Error submitting form" if there is "Input required".

Sorry if I was not clear, but that's what I was trying to say.  The form message is indeed misleading.  It tells me there's a database problem, when it is a form problem.
Comment 19 tim 2018-10-14 16:16:37 UTC
I have now fixed most of my forms and sub-forms (I think) and attempted to test as many variations as I can think of. This was non-trivial.  I hope not too many others fall into this trap.

I have also reverted to the ubuntu ppa pre-release of 6.1.3.1 which (as expected) matches the behaviour of the libreoffice version.

So this can, I guess, be set to 'resolved' again.  However, before someone else does that can I ask that attention is paid to the GUI messages.  If a database field is optional, but is 'required' in the form, the error messages should make this clear, not state that it's an error writing to the database.
Comment 20 Xisco Faulí 2018-10-15 08:03:25 UTC
(In reply to tim from comment #19)
> I have now fixed most of my forms and sub-forms (I think) and attempted to
> test as many variations as I can think of. This was non-trivial.  I hope not
> too many others fall into this trap.
> 
> I have also reverted to the ubuntu ppa pre-release of 6.1.3.1 which (as
> expected) matches the behaviour of the libreoffice version.
> 
> So this can, I guess, be set to 'resolved' again.  However, before someone
> else does that can I ask that attention is paid to the GUI messages.  If a
> database field is optional, but is 'required' in the form, the error
> messages should make this clear, not state that it's an error writing to the
> database.

Let's close this one as dupe of bug 120245.
For the message problem, please, create a new report.
Thanks

*** This bug has been marked as a duplicate of bug 120245 ***
Comment 21 tim 2018-10-15 08:24:32 UTC
I've raised #120603 for the message problem.
Comment 22 Peter Nowee 2018-10-15 10:35:21 UTC

*** This bug has been marked as a duplicate of bug 120245 ***
Comment 23 Gerhard Schaber 2018-10-16 11:36:37 UTC
Having a default that required a user input for every single field breaks things and is really annoying. Is there an easier way to stay compatible without having to edit every field in every form (multi-selection helps a little, but it is still very annoying to do this for every form)
Comment 24 Julien Nabet 2018-10-16 11:50:34 UTC
(In reply to Gerhard Schaber from comment #23)
> Having a default that required a user input for every single field breaks
> things and is really annoying. Is there an easier way to stay compatible
> without having to edit every field in every form (multi-selection helps a
> little, but it is still very annoying to do this for every form)

LO tries already to be compatible with MsOffice or ODF bugs (see at/atm atmosphere unit), it would be really annoying in addition to find a way to stay compatible with older bugs of LO itself.

Now perhaps it could be interesting to have a tool to extract some xml file of the odb and put "false" for every "Input required" field.
Comment 25 tim 2018-10-16 13:57:27 UTC
I suggest that because this is a fix of a bug, Libreoffice should include a setting somewhere in Tools, Options, to allow 'Input Required' to be ignored for a while (say a year or 2?), with a warning message that support for it will not last forever.
Comment 26 Gerhard Schaber 2018-10-17 10:22:06 UTC
Hello. I was not trying to suggest to stay compatible by emulating the old behavior. It would just be great, if LO could detect that a file was stored with a faulty version and suggest to fix the settings automatically upon loading such a file. Or, as mentioned, by providing a tool that does the modifications for you.
Comment 27 Lionel Elie Mamane 2018-12-05 15:44:03 UTC
(In reply to Gerhard Schaber from comment #26)
> Hello. I was not trying to suggest to stay compatible by emulating the old
> behavior. It would just be great, if LO could detect that a file was stored
> with a faulty version and suggest to fix the settings automatically upon
> loading such a file. Or, as mentioned, by providing a tool that does the
> modifications for you.

(In reply to Robert Großkopf from comment #15)
> OK, many people haven't had a look at control > properties > data > input
> required.

In LibreOffice 6.1.4, forms controls with "Input required: yes" last saved by earlier versions of LibreOffice will have "Input required" set to "no". That is part of the fix to bug 121188.

This is a small dataloss (of the value of the "input required" setting for each control) when an odb is used both in older and later versions of LibreOffice, but will massively help backwards compatibility. The least of two evils.

So the only completely annoying version is LibreOffice 6.1.3. I apologise to the people that did the work of setting "input required" to "no" manually in LibreOffice 6.1.3.
Comment 28 tim 2018-12-05 20:19:26 UTC
(In reply to Lionel Elie Mamane from comment #27)
> (In reply to Gerhard Schaber from comment #26)
> > Hello. I was not trying to suggest to stay compatible by emulating the old
> > behavior. It would just be great, if LO could detect that a file was stored
> > with a faulty version and suggest to fix the settings automatically upon
> > loading such a file. Or, as mentioned, by providing a tool that does the
> > modifications for you.
> 
> (In reply to Robert Großkopf from comment #15)
> > OK, many people haven't had a look at control > properties > data > input
> > required.
> 
> In LibreOffice 6.1.4, forms controls with "Input required: yes" last saved
> by earlier versions of LibreOffice will have "Input required" set to "no".
> That is part of the fix to bug 121188.
> 
> This is a small dataloss (of the value of the "input required" setting for
> each control) when an odb is used both in older and later versions of
> LibreOffice, but will massively help backwards compatibility. The least of
> two evils.
> 
> So the only completely annoying version is LibreOffice 6.1.3. I apologise to
> the people that did the work of setting "input required" to "no" manually in
> LibreOffice 6.1.3.
Thanks for the apology.  I believe you now have the best compromise solution.