Apache OpenOffice (AOO) Bugzilla – Issue 44445
SQL Query Error when attempting to remove primary key
Last modified: 2006-05-31 14:29:06 UTC
When editing a previously saved table in an ODB database, it is possible to remove the primary key status of a row. This appears to work at first, but an error message is displayed when attempting to save the table after this change. The error under "More" reads: SQL Status: 37000 Error code: -11 Unexpected token: PRIMARY in statement [ALTER TABLE "Document" DROP PRIMARY] ("Document" is the name of the table being edited)
That is fixed in a newer version m80 or newer.
close
This issue has resurfaced in 2.0.1 RC5 :-(( Create a new HSQLDB ODB database document via File - New - Database Accept the defaults. Use the Table Design function to create a table containing one field, eg : ch1, type varchar Accept the defaults. Make ch1 a primary key by RMB clicking on the appropriate entry. Save the table as Table1. Close the table design window. Now create a view. Add the table Table1. Select the field ch1 and double click to add it to the bottom pane. Save the View as View1. Close the View Design window. Now double click on Table1 in the table entry and alter the name of ch1 to ch2. On saving an error message appears relating to the ALTER TABLE statement and the use of the primary key. Close the window. Close the database document. Re-open the database document. Double click on Table1 : Error message, the field name has been changed despite the previous warning message. The view is no longer available. Data loss. This ought to be a stopper. You shouldn't lose your data. Alex
setting oooqa and regression keywords since it was stated as fixed in an earlier development version. The problem occurs in 2.0.1 RC5
setting platform to all, since the problem has been confirmed on Windows and Linux reassign to msc
setting to P2 since data is corrupted in an easy to encounter way
Alex, that's not really the same issue, is it? I would propose to submit a new issue with you (excellent) description how to reproduce, and IMO it surely qualifies for 2.0.2. But I am hesitant to use this issue here, which originally only describes that you cannot drop a primary key.
Confirmed the new statement of the bug on 2.0.1RC4/Linux and also 2.0.0 final / Windows. I haven't tried reproducing original ODB report. Suggest removing "regression" keyword.
@Frank The problem is apparently caused by the ALTER TABLE statement in both cases being mishandled. The fact that the ALTER TABLE statement tries to remove the PK at the same time as changing the field name, and reports failure but still carries out the statement, is in effect as if I had directly entered an ALTER TABLE DROP PK statement. However, that said, there may be some subtleties in HSQLDB statements that I have not fully understood. If you feel that this would be better handled in a separate issue, Sophie Gautier has already opened one up under Issue (wrong component, but that can be corrected). Issue 59584 http://www.openoffice.org/issues/show_bug.cgi?id=59584 Alex
Okay, I re-resolve this then. IMO, the original bug "SQL Query Error when attempting to remove primary key" talked about not being able at all to remove a primary key. The new bug says that - when you change the name of a column referenced in a view (which BTW doesn't depend on the primary key), the database cannot be opened anymore - when trying to rename a primary key column, only a partial change is made: only the renaming, but not the transfer of the primary key
Hi, I tested the both things fs mention. No one occur anymore. So I close this issue now. Bye Marc