Bug 108058 - After renaming Primary Key AutoValue feature fails
Summary: After renaming Primary Key AutoValue feature fails
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
5.3.3.2 release
Hardware: All All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-05-24 14:14 UTC by Howard Johnson
Modified: 2022-12-25 09:10 UTC (History)
1 user (show)

See Also:
Crash report or crash signature:

Attachments
screen shot with table and error message (44.73 KB, image/png)
2017-05-24 14:14 UTC, Howard Johnson 		Details
View All Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this bug.
Description Howard Johnson 2017-05-24 14:14:14 UTC 
Created attachment 133517 [details]
screen shot with table and error message

In a simple table after renaming the Primary Key the AutoValue feature fails when trying to insert a new record.  Error message is "Error inserting the new rocord", and "Attempt to insert null into a non-nullable column: column: ID2 table: Table1 in statement [INSERT INTO "Table1" ("text") VALUES (?)]"

How to recreate this:

Step 1 create a simple table:

  Open LibreOffice and select:
    File
       New
          Database
            * Create a new database
                Select: HSQLDB Embedded
                Next>>
                Select either:
                   * No, do not register the database, OR
                   * Yes, ...

                Click Finish
                Click Save

  Then select: Create a Table in Design View...

  On line 1 enter: Field Name = "ID"   | Field Type = "Integer"
  On line 2 enter: Fidld Name = "Test" | Field Type = "Text"

  Right click on green arrow left of ID and select Primary Key

  Below set AutoValue to Yes

  Close the window
    Do you want to save the changes?  Save to table name "Table1".
    Click Yes


Step 2 test the table:
  Double click on "Table1" to open it.
  Type something in text and hit return.
  ID becomes "0".
  If you do this again ID becomes "1".

Step 3 edit the table and rename the primary key:
  Now close the table.
  Right click on "Table 1" and select "Edit".
  Rename "ID" to "ID2".
  Close the design and save.

Step 4 test the table again:
  Again click on "Table1" to open it.
  on line#2 type something in the text field and hit enter.

  Expected result is that ID2 increments to the next value.

  Result is the attached error message.


The only way I have found to fix this is to delete the table and start over. 

This is a very basic malfunction that a new Base user will likely run into.  I ran into this when I first started using base and it made me feel like Base was probably a pile of junk.  I know better now, but this issue is still not resolved and I just ran into it again myself a few days ago.

Thanks for looking into this.
Comment 1 Alex Thurgood 2017-05-24 14:59:26 UTC 
(In reply to Howard Johnson from comment #0)


> The only way I have found to fix this is to delete the table and start over. 

No, that shouldn't be necessary, just delete the Primary Key from the table in the UI, then use an ALTER TABLE statement via the Tools > SQL menu entry :


http://ooo-forums.apache.org/en/forum/viewtopic.php?f=61&t=64482#p286456




> 
> This is a very basic malfunction that a new Base user will likely run into. 

This is a well documented known limitation, as the above link shows. If you still have a problem with the auto increment function not working once you have deleted the primary key from the table, then issued the ALTER TABLE command, there might be reason to file a bug report for that.
Comment 2 Howard Johnson 2017-05-24 17:32:26 UTC 
Alex, 

First let me say that I very much appreciate your looking into this and all of the other work you've done on LO!

But just to be clear, I did not submit this report or the others to find workarounds, but rather to provide the basis to start to fix these issues in the first place.  I want Base to work better, not to have to send users looking for obscure answers related to very introductory and simple issues like renaming a field.

All in all, it's pretty clear to me that we're in a pretty bad way in terms of overall LO Base maintenance.

Other than that I'm not sure what to think.  I've been told by a couple of guys that the LO code base is relatively very big, and complex.  So it is.

What in your opinion is needed to improve the overall efforts to address some of these fundamental Base issues?


(FYI: I have again tried to build LO just so I could take a look at it.  But I again ran into the same problem that I ran into when I first tried this, documented here:  https://ask.libreoffice.org/en/question/96902/building-lo-w-mariadb-installed-fails/)
Comment 3 Robert Großkopf 2017-05-25 13:59:27 UTC 
The buggy behavior is:
You change the fieldname but
AutoValue will be shown in the editor and wouldn't be changed to "No".

When I save the table an will do
View > Refresh Tables
or close the database and reopen the database
the AutoValue has been changed for the changed field to "No".

You have only to set it back to "Yes" and save the table - works here.

Two solutions for this:
1. When changing a fieldname (not deleting!) fieldtype and AutoValue shouldn't be changed.
2. When changing the fieldname and AutoValue will be changed by this command, it should be shown in the GUI of the editor.

I would prefer the first solution, but could be it is a problem of the internal HSQLDB, which couldn't be changed.
Comment 4 QA Administrators 2018-05-26 02:42:41 UTC Comment hidden (obsolete)
Comment 5 OldIronSides1797 2018-12-24 03:13:19 UTC 
I submitted the following bug which may be related.

Bug 122300 - LibreOffice Base: Table Creation Wizard: Primary Key "AutoValue" does not populate after error.
Comment 6 QA Administrators 2020-12-24 03:52:21 UTC Comment hidden (obsolete)
Comment 7 QA Administrators 2022-12-25 03:21:28 UTC Comment hidden (obsolete)
Comment 8 Robert Großkopf 2022-12-25 09:10:07 UTC 
Bug ist still the same in LO 7.4.3.2 on OpenSUSE 15.3 64bit rpm Linux