Bug 108068 - Can't change Primary Key's name with Firebird Embedded db.
Summary: Can't change Primary Key's name with Firebird Embedded db.
Status: RESOLVED NOTOURBUG
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
QA Contact:
URL:
Whiteboard:
Keywords:
Depends on:
Blocks:
 
Reported: 2017-05-24 16:06 UTC by Howard Johnson
Modified: 2017-05-30 09:26 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
screen shot with final fatal error message (63.53 KB, image/png)
2017-05-24 16:06 UTC, Howard Johnson
Details

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

Unnecessary trouble when trying to rename a field while using Firebird Embedded.

Steps to recreate:

Step 1 - Create a simple database
  Open LibreOffice
  Select File, New, Database.
  Select Firebird Embedded
 
  Create Table in Design View...
    On line 1 enter: Field Name = "ID"  , and Field Type = "Integer"
    On line 2 enter: Field Name = "Test", and Feild Type = "Text"
    Set line 1 as the Primary Key.  Set Line 1 to AutoValue=Yes.

  Save the table.

Step 2 - Test it
  Open it and add text into Test field and hit return.  
  Result normal: ID gets zero, and cursor moves to next line.
  Close it

Step 3 - Edit the new table
  Rename ID to ID2
  Save


Unexpected result 1: "Warning", and 
> The column "ID2" could not be changed.  Should the column instead be deleted and the new format appended?

My comment:  My gosh, guys, what's up with this?  Identifiers have been around for 50+ years.  In 2017 one should be able to easily rename a field in every database, even a Key field.  The concept of separation between name and functionality is a long standing tradition in all computer languages.  I am shocked really, by this prompt which must have been put in either by someone in a hurry or by someone who doesn't really care about Base.


So just to see what happens next, I then click "Yes" to continue.


Unexpected result 2: "Primary Key Affected", and "The column "ID" belongs to the primary key.  If the column is deleted, the primary key will also be delted.  Do you really want to continue?

My comment: More of the same as above.  Please get rid of these two messages and just rename the field.


Again just to see what happens next, I click "Yes" to continue.


Unexpected result 3: fatal error message.  (See the attached screen shot.)

My comment: 3 strikes and we're out.  :-(



EXPECTED RESULT of a field rename request:  ID should simply be renamed to ID2.
 
-- No prompts or other messages should appear.  
-- No questions should be asked.  
-- No errors should be reported.

No matter how much work it takes this needs to be fixed.


Thanks for looking into this.
Comment 1 Julien Nabet 2017-05-25 07:13:20 UTC
On pc Debian x86-64 with master sources updated yesterday, I could reproduce this except the nitpick that first value is 1 and not 0
I noticed this on console:
warn:connectivity.firebird:12867:1:connectivity/source/drivers/firebird/Statement.cxx:122: isc_dsql_execute failed
warn:connectivity.firebird:12867:1:connectivity/source/drivers/firebird/Util.cxx:52: firebird_sdbc error:
*unsuccessful metadata update
*ALTER TABLE Table1 failed
*action cancelled by trigger (1) to preserve data integrity
*Cannot update index segment used by an Integrity Constraint
caused by
'ALTER TABLE "Table1" ALTER COLUMN "ID"  TO "ID2"'
Comment 2 Lionel Elie Mamane 2017-05-25 10:58:42 UTC
From the Firebird 2.5 documentation:

 * It will not be possible to change the name of a column
   that is included in any constraint: PRIMARY KEY , UNIQUE
   key, FOREIGN KEY , column constraint or the CHECK constraint
   of the table.

 * Renaming a column will also be disallowed if the column
   is used in any trigger, stored procedure or view.


I agree this is unfortunate, but this needs to be enhanced / fixed in Firebird, so is best tracked in the firebird bugtracker.

On the LibreOffice side, we could, to better work around this firebird limitation, instead of trying to drop and recreate the column, try to drop the primary key, change the name and create the primary key again. This would solve this particular scenario, but there will always be other scenarios left.

Note the user can remove the primary key, then rename, then recreate the primary key. Will work, but not good from a user-friendliness point of view.

(In reply to Julien Nabet from comment #1)
> I noticed this on console:
> warn:connectivity.firebird:12867:1:connectivity/source/drivers/firebird/
> Statement.cxx:122: isc_dsql_execute failed
> warn:connectivity.firebird:12867:1:connectivity/source/drivers/firebird/Util.
> cxx:52: firebird_sdbc error:
> *unsuccessful metadata update
> *ALTER TABLE Table1 failed
> *action cancelled by trigger (1) to preserve data integrity
> *Cannot update index segment used by an Integrity Constraint
> caused by
> 'ALTER TABLE "Table1" ALTER COLUMN "ID"  TO "ID2"'

We should show this error message to the user. Would give hir a hint how to deal with the situation rather than the misleading "propose to drop/recreate the column".
Comment 3 Julien Nabet 2017-05-25 11:40:06 UTC
Link to the doc: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-altraltrto

Lionel: yes it should be more relevant to give the error message (which appears on console!).
Comment 4 Julien Nabet 2017-05-25 11:56:10 UTC
Lionel: I thought about throwing with ::dbtools::throwSQLException here:
    121     if (aErr)
    122         SAL_WARN("connectivity.firebird", "isc_dsql_execute failed");
after the SAL_WARN
(see http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Statement.cxx#121) then I would have retrieved the message in 
dbaccess/source/ui/tabledesign/TableController.cxx (see http://opengrok.libreoffice.org/xref/core/dbaccess/source/ui/tabledesign/TableController.cxx#1118)
but don't know how to retrieve the error message from Firebird engine.
Comment 5 Howard Johnson 2017-05-25 12:42:06 UTC
I had no idea, sorry for jumping up and down about this; now I will go jump up and down at the firebird team.  :-)



Till then, is it possible to put this up on the screen:


>>To rename this field in Firebird you must either delete this field, and then re-create it with the new name, or you might want to copy it, into a new field first with the new name, and then delete the original field once this is done.

From the Firebird 2.5 documentation (https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html):

 * It will not be possible to change the name of a column
   that is included in any constraint: PRIMARY KEY , UNIQUE
   key, FOREIGN KEY , column constraint or the CHECK constraint
   of the table.

 * Renaming a column will also be disallowed if the column
   is used in any trigger, stored procedure or view.


Thanks guys!
Comment 6 Howard Johnson 2017-05-25 12:47:02 UTC
Reported this to Firebird:  http://tracker.firebirdsql.org/browse/CORE-5551
Comment 7 Lionel Elie Mamane 2017-05-25 18:42:26 UTC
(In reply to Julien Nabet from comment #4)
> Lionel: I thought about throwing with ::dbtools::throwSQLException (...)

> but don't know how to retrieve the error message from Firebird engine.

From the error message you quoted from the console, we see that this message comes from
http://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Util.cxx#52
so look there, we see it is function
firebird::StatusVectorToString
so that's your answer :)
See how it is called from


Throwing an SQLException is indeed in general the way to do that kind of things. In general LibreOffice then has a decent dialog for the error message, that shows the contents of the SQLException behind a "more" button (or something like that).

Here, there's a mechanism that implements the
 The column "ID2" could not be changed.
message, that probably interferes with that. Possibly, the SQLException is already thrown, but caught (and ignored) by that mechanism. Then, I think the right thing to do is to change that dialog box to have the "more" button to show the actual SQL error, like the default mechanism.
Comment 8 Commit Notification 2017-05-30 05:56:50 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "master":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=182a62e0f199d26df0ae76ebf224c5d4b0fc3e26

Related tdf#108068: Retrieve error from Firebird engine

It will be available in 5.5.0.

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

Affected users are encouraged to test the fix and report feedback.
Comment 9 Lionel Elie Mamane 2017-05-30 06:01:38 UTC
(In reply to Commit Notification from comment #8)
> Julien Nabet committed a patch related to this issue.
> It has been pushed to "master":
> 
> http://cgit.freedesktop.org/libreoffice/core/commit/
> ?id=182a62e0f199d26df0ae76ebf224c5d4b0fc3e26
> 
> Related tdf#108068: Retrieve error from Firebird engine

For full clarity, this patch does not address this bug per se, but my "We should show this error message to the user." in comment 2.
Comment 10 Commit Notification 2017-05-30 07:05:55 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=c5ea8e46626affeee5465330554e4864947b163b&h=libreoffice-5-4

Related tdf#108068: Use new name of column when it's been changed

It will be available in 5.4.0.1.

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

Affected users are encouraged to test the fix and report feedback.
Comment 11 Commit Notification 2017-05-30 07:22:13 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-5-3":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=7b7c43f3c86eeeb7c092621464a747604a1cd48a&h=libreoffice-5-3

Related tdf#108068: Use new name of column when it's been changed

It will be available in 5.3.4.

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

Affected users are encouraged to test the fix and report feedback.
Comment 12 Commit Notification 2017-05-30 09:26:05 UTC
Julien Nabet committed a patch related to this issue.
It has been pushed to "libreoffice-5-4":

http://cgit.freedesktop.org/libreoffice/core/commit/?id=a987fd3b320b02705d9b5239040024c03af5476f&h=libreoffice-5-4

Related tdf#108068: Retrieve error from Firebird engine

It will be available in 5.4.0.1.

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

Affected users are encouraged to test the fix and report feedback.