trying to change an ID-field, primary not set to auto-numbering change to autonumbering, rules to strange observations: it looks, as if the sdbc is pointing to the wrong field and generates the wrong sql-statement. pls have a look to attached screenshots martin (LO 5.4.1/32 portable on W10/64 H)
Created attachment 136366 [details] screenshot on SDBC-mismatch
Created attachment 136367 [details] screenshot on SDBC-mismatch
Changed title to better reflect screenshot submissions
@mhonline : So your description is rather lacking in detailed information as you don't say how you created the table, or what additional attributes you have given to the different fields that one can see in the screenshot. I have tried to reproduce using the default behaviour of LibreOffice using the manual way to create a new table in the GUI (not using the table creation wizard). I took the defaults as suggested in the UI. For the INT column, I defined it as primary key with no auto(increment)value. Defined the remaining fields with the defaults provided by the LO UI, then saved. Closed the ODB file, then closed LibreOffice. Re-opened LibreOffice, then reloaded the ODB file, opened the table for editing and clicked on Autovalue = YES after selecting the priID field. When I attempt to resave the table, I get the following error message : "The column "priID" could not be changed. Should the column instead be deleted and the new format appended ?" Clicking on the "More" button shows the following error message: SQL Status: IM001 Changing autoincrement property of existing column is not supported If I then click on "Yes" in answer to the question to delete/new append, I see the following : Error while saving the table design firebird_sdbc error: *unsuccessful metadata update *ALTER TABLE testprimkey failed *SQL error code = -607 *Invalid command *Specified domain or source column SQL_LONG does not exist caused by ALTER TABLE "testprimkey" ADD "priID" SQL_LONG NOT NULL This isn't the same error message as you have posted in your screenshot. If you have done anything different than what I have described, please provide complete detailed step-by-step instructions here. Tested on Version: 5.4.1.2 Build ID: ea7cb86e6eeb2bf3a5af73a8f7777ac570321527 CPU threads: 4; OS: Mac OS X 10.12.6; UI render: default; Locale: fr-FR (fr_FR.UTF-8); Calc: group
Dear Bug Submitter, This bug has been in NEEDINFO status with no change for at least 6 months. Please provide the requested information as soon as possible and mark the bug as UNCONFIRMED. Due to regular bug tracker maintenance, if the bug is still in NEEDINFO status with no change in 30 days the QA team will close the bug as INSUFFICIENTDATA due to lack of needed information. For more information about our NEEDINFO policy please read the wiki located here: https://wiki.documentfoundation.org/QA/Bugzilla/Fields/Status/NEEDINFO If you have already provided the requested information, please mark the bug as UNCONFIRMED so that the QA team knows that the bug is ready to be confirmed. Thank you for helping us make LibreOffice even better for everyone! Warm Regards, QA Team MassPing-NeedInfo-Ping-20180404
Dear Bug Submitter, Please read this message in its entirety before proceeding. Your bug report is being closed as INSUFFICIENTDATA due to inactivity and a lack of information which is needed in order to accurately reproduce and confirm the problem. We encourage you to retest your bug against the latest release. If the issue is still present in the latest stable release, we need the following information (please ignore any that you've already provided): a) Provide details of your system including your operating system and the latest version of LibreOffice that you have confirmed the bug to be present b) Provide easy to reproduce steps – the simpler the better c) Provide any test case(s) which will help us confirm the problem d) Provide screenshots of the problem if you think it might help e) Read all comments and provide any requested information Once all of this is done, please set the bug back to UNCONFIRMED and we will attempt to reproduce the issue. Please do not: a) respond via email b) update the version field in the bug or any of the other details on the top section of our bug tracker Warm Regards, QA Team MassPing-NeedInfo-20180502
Reopening this. Alex Thurgood had no problem recreating it. Who cares if the error message is a bit different, the fact remains the code is broken in every build of Base right up to the first drop, yesterday, of the binaries for 6.2 Alpha0. It shouldn't be up to the user to post an example for something this simple, and obviously wrong, in fact the fact it is in the wild for so long calls the entire QA process (and the commitment of TDF to produce a quality product) into question. As for the difference in error messages - if anyone had tried for real that is really easy to understand. With the HSQLdb engine data changes actually are written to DISC. // pause here for a second OK, now here is what happened. Since the system had to DROP and then ADD the column back and since that can't be run as a single transaction the DROP column worked, the ADD column failed. After clearing the error message box the Table Editor is now displaying a layout for the table that DOES NOT match the table on disc (for firebird it is the table in memory and here that is going to come in handy). OK - so you try to close the table editor an guess what it prompts the user to save the table design again - if the user says yes a different error is triggered, it is the error reported by the OP in this issue. If the ODB used HSQLdb that is it, the table has been altered by dropping the column and it must be added back by the user - in the case of firebird just don't save the file and the change is discarded, along with an other changes like data added to different tables and such (all would be lost).
Created attachment 142282 [details] test ODB So here is a firebird embedded ODB for testing. You have three tables, one view and a form. The table EmployeesTasks will be for testing purposes here. Tables Employees and Tasks share a dependency via the view View1 and you may not alter any of the related fields in Employees or Tasks while that view is in the schema.
How to reproduce the bug. 1 Download the attached ODB file. 2 Start Libreoffice if it is not running. 3 Open the file you downloaded in step 1 4 Go to the Tables section in the Base main window. 5 Select the table EmployeesTasks 6 Right click the mouse, on the table, and select edit. 7 When the table editor opens, select the primary key field 'ID' 8 Change the Auto Increment control from No to Yes 9 Click save changes 10 An error message informs you that the column must be dropped and added to the end of the table. Answer yes. What actually happens: An error message box opens again, during the final step of the change (adding the column to the end of the table) with a bad SQL statement. The command is trying to use a string constant of a data type descriptor in place of the actual data type name. Clear the error message. The table editor continues to show that a column ID exists, but it no longer does in the actual Table in the schema. Close the table editor. You are asked if you want to save the changes, if you say yes a new error message is displayed with the word 'GENERATED' in it - this is because Base used the table descriptor stored in Base, not from the schema, and is trying to alter a column that no longer exists. Clear that and close the table editor again, this time do not save the saves when prompted. Open the table itself and you will see that the ID field is gone. Close the file, but do not save it and it is ready to test all over again. Expected results: It works. The ID field is moved to the end of the table, type integer and Auto Increment is true.
ps - if you find that when you reopen the file it is still marked as needing saving, remember that currently that file was not actually closed by the process. To make that stop you need to either save the file for explicitly or exit libreoffice and restart it However, it seems the connection to the data engine is closed when the ODB file is closed in the UI (even if the file is not released), you know that because the table in the schema is again displayed with the ID field intact.
Changing from Windows to all platforms given that I can duplicate the OP report precisely using Linux.
I am on a Mac (High Sierra 10.13.6) and have run into a similar problem when using the embedded Firebird database (LO 6.0.6.2). After a table has been saved, I am unable to change the primary key AutoValue from No to Yes. When I try to save a table after making this change, I get an error message: "Warning The column "Field Name" could not be changed. Should the column instead be deleted and the new format appended?" This is unfortunate, as there are certain ways of creating tables, such as dragging in a Calc file, which don't allow you to set the AutoValue when creating the table. In the embedded HSQLDB we were able to edit these fields after saving the tables.
Created attachment 144595 [details] Embedded Firebird Database A simple test database (Firebird embedded) I was using when I discovered this problem.
additional info: issue for FB-tables still exists in LO 6.1.2.1 besides that: When changing the field-name of the field formerly used as primary-key (expl: ID to ID-No) the table will become inaccessable: no records viewable + no further datarecords attachable (table-view with *-new does not come up) m.
I can still reproduce this on LO 7.0.0.3 with Firebird embedded. Bug 131295 might be related. Putting it quite frankly: For me, the table designer is a core feature for creating and working with databases. It is currently so broken, and apparently has been for years, that I have to question the viability of LibreOffice Base as is. I'm in favor of increasing the importance of this bug to at least "Major" as in it's current state the usability of LibreOffice Base is severely impeded. Version: 7.0.0.3 (x64) Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e CPU threads: 8; OS: Windows 10.0 Build 19041; UI render: Skia/Raster; VCL: win Locale: de-CH (de_CH); UI: en-US Calc: threaded
I am having this issue too. Am finding creating/editing tables in Firebird/LO base a nightmare. (About to report another bug) I am finding that what is displayed in the edit table GUI often does not reflect the actual situation. If you edit a table and the change doesn't seem to have been made, close the table (saving or at least trying to save. if you get an error message close without saving if nec) and reopen. Often your change will have been made. If it hasn't try exiting LO and reopening. 'After a table has been saved, I am unable to change the primary key AutoValue from No to Yes. When I try to save a table after making this change, I get an error message: "Warning The column "Field Name" could not be changed. Should the column instead be deleted and the new format appended?"' My main work round is create new tables from calc or from copying without a primary key and then edit the table and add one. (It will be at the end of the table, so if required use SQL to move the field to desired position, you won't see the field has moved until you have closed and reopened the table editor) ) Otherwise change the field to autovalue, try to save, you'll get an error message. Ignore it, close without further saving. Open it and the problem field will have been deleted and you can enter a new one. Just updated and still have same problem Version: 7.0.0.3 (x64) Build ID: 8061b3e9204bef6b321a21033174034a5e2ea88e CPU threads: 4; OS: Windows 10.0 Build 18363; UI render: Skia/Raster; VCL: win Locale: en-GB (en_GB); UI: en-US Calc: threaded
I think it's not LO related but more Firebird related. After having removed "ID" column + refresh tables, I tried these on SQL: ALTER TABLE "EmployeesTasks" ADD "ID" INTEGER NOT NULL or ALTER TABLE "EmployeesTasks" ADD "ID" INTEGER GENERATED BY DEFAULT AS IDENTITY both indicated on console: warn:connectivity.firebird:63575:63575:connectivity/source/drivers/firebird/Util.cxx:57: firebird_sdbc error: *unsuccessful metadata update *Cannot make field ID of table EmployeesTasks NOT NULL because there are NULLs present https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html indicates: "Identity columns are implicitly NOT NULL (non-nullable)." http://tracker.firebirdsql.org/browse/CORE-5019 indicates Dmitry Yemanov added a comment - 17/Nov/15 10:52 AM "... Adding NOT NULL columns over a populated table is not allowed anymore, because such operations violate the NOT NULL constraint..." Dmitry Yemanov is one of the author of Firebird (see https://firebirdsql.org/en/news/interview-with-dmitry-yemanov-firebird-core-developer/)
(In reply to Julien Nabet from comment #17) > ... > Dmitry Yemanov added a comment - 17/Nov/15 10:52 AM > "... Adding NOT NULL columns over a populated table is not allowed anymore, > because such operations violate the NOT NULL constraint..." > ... Just realized that in our case, the table is empty, so bug of Firebird or do we use a too old version (3.0.0)? Could someone give a try on Firebird directly?
(In reply to QA Administrators from comment #6) > Estimado remitente de errores: > > Lea este mensaje en su totalidad antes de continuar. > > Su informe de error se cierra como INSUFFICIENTDATA debido a inactividad y > una falta de información que se necesita para poder precisar > reproducir y confirmar el problema. Le recomendamos que vuelva a realizar la > prueba > su error contra la última versión. Si el problema persiste > presente en la última versión estable, necesitamos lo siguiente > información (ignore la que ya haya proporcionado): > > a) Proporcione detalles de su sistema, incluido su > sistema y la última versión de LibreOffice que tenga > confirmó que el error está presente > > b) Proporcione pasos fáciles de reproducir: cuanto más simples, mejor > > c) Proporcione cualquier caso de prueba que nos ayude a confirmar el > problema. > > d) Proporcione capturas de pantalla del problema si cree que podría ayudar > > e) Leer todos los comentarios y proporcionar la información solicitada > > Una vez hecho todo esto, vuelva a configurar el error en NO CONFIRMADO > e intentaremos reproducir el problema. Por favor no: > > a) responder por correo electrónico > > b) actualice el campo de versión en el error o cualquiera de los otros > detalles > en la sección superior de nuestro rastreador de errores > > Un cordial saludo, > Equipo de control de calidad > > MassPing-NeedInfo-20180502 ================================================ Hola encuentro un error muy recurrente en el programa "isc_dsql_prepare", para la función de incrementos de variable automático en columnas como id-automático, e probado de 2 formas el incremento y en ninguna de los dos me permite ponerlo en modo automático: a) por medio de la creación manual de nuevas tablas, me manda el error (-104, IM001, 751,-607, 1, 68, ). b) por medio de el asistente automático de tablas, una vez que se le asigna automático a la columna de id, ocurre el mismo problema error (-104, IM001, 751,-607, 1, 68, ) el problema parece venir de la función /build/libreoffice/parts/libreoffice/build/connectivity/source/drivers/firebird/Util.cxx:68 creo suponer que los "PERMISOS" no dejan que la función realice su trabajo, pero este error se viene presentando desde libreoffice 3 hasta el mas actual que tengo probando en mi maquina LibreOffice 7.0.2.2, puede ser que se pierda el control del incrementó o que por alguna razón la función pierda los permisos Saludos
Firebird 3 will only allow to add a incremented field to a table without content. It couldn't change a field to an incremented field. « An identity column cannot be altered to become a regular column. The reverse is also true. Firebird 4 will introduce the option to alter an identity column to a regular column. Identity columns are implicitly NOT NULL (non-nullable).» See: https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref30/firebird-30-language-reference.html#fblangref30-ddl-tbl-create ALTER TABLE "Table1" ADD "ID" INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY; This will add a field "ID" to "Table1" as autovalue, if "Table1" has no content. This is possible through Tools → SQL. This isn't possible in GUI. If I want to change a field in GUI to autovalue in a table without content I will get firebird_sdbc error: *Dynamic SQL Error *SQL error code = -104 *Token unknown - line 1, column 48 *GENERATED caused by 'isc_dsql_prepare' /home/buildslave/source/libo-core/connectivity/source/drivers/firebird/Util.cxx:68 Seems the code isn't defined as a known code for Firebird in LO. Tested with LO 7.3.0.3 on OpenSUSE 15.3 64bit rpm Linux.
*** Bug 117527 has been marked as a duplicate of this bug. ***