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.
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"'
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".
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!).
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.
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!
Reported this to Firebird: http://tracker.firebirdsql.org/browse/CORE-5551
(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.
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.
(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.
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.
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.
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.