Description: The enclosed test ODB file has two tables, each with an autoincrement ID primary key. When opening the file in master, the silent conversion to firebird creates the tables, but no data entry is possible. When attempting to add a new record (ading a name to the name to the fname field of table fname, I get the following error : Error inserting the new record: firebird_sdbc error: *violation of PRIMARY or UNIQUE KEY constraint "INTEG_1" on table "fname" *Problematic key value is ("id" = 0) caused by 'isc_dsql_execute' Steps to Reproduce: 1. Open the ODB file in master/daily build. 2. Open table FNAME 3. Try adding a new name to the fname field. Actual Results: Failure to insert new record: Error code: 1 firebird_sdbc error: *violation of PRIMARY or UNIQUE KEY constraint "INTEG_1" on table "fname" *Problematic key value is ("id" = 1) caused by 'isc_dsql_execute' Expected Results: The new record should be created correctly and the ID counter increased by 1 Reproducible: Always User Profile Reset: No Additional Info: User-Agent: Mozilla/5.0 (Macintosh; Intel Mac OS X 10.13; rv:59.0) Gecko/20100101 Firefox/59.0
Created attachment 141322 [details] Test hsqldb file for migration to firebird
Regression introduced by: author Tamas Bunth <tamas.bunth@collabora.co.uk> 2018-03-25 13:26:57 +0200 committer Tamás Bunth <btomi96@gmail.com> 2018-04-07 17:08:26 +0200 commit 159dd28651788a19848eae56693ad06ed947414d (patch) tree b69013db1c0fa45677b59d58999ce0d8ebebd76c parent 1a9bfdd8976d28fa3a56726bdcae9f2b294d6c6d (diff) dbaccess: Enable hsql migration by default Also make Firebird driver not experimental anymore. Bisected with: bibisect-linux64-6.1 Adding Cc: to Tamas Bunth
This looks like the autoincrement column is created in firebird (by means of a sequence / generator, that is an object that returns a new, fresh, incremented number each time it is required), but the generator is not initialised with the next value it should give (something like MAX(existing values in the table) or whatever HSQLDB would have returned for the next insertion), so it starts with 0.
Have tested the following: Opened a migrated Firebird database. Tried to input a new row in a table with autoincrement. Next value has to be 10. When I want to go to next row it shows *Problematic key value is ("id" = 0) Click "OK" then again *Problematic key value is ("id" = 1) Click "OK" ... and so on. The generated key-value will switch every time I try to input the row again up to the value which works. Don't know if there is another way to set the start-value of autoincrement. There isn't a function like ALTER TABLE "tablename" ALTER COLUMN "fieldname" RESTART WITH <new value>; available, because the autoincement value is set by a generator. I don't know where to get the name of the generator. The name isn't shown in the error-code. Could be with the name this will work: SET GENERATOR gen_t1_id TO <new value>;
This is the code for getting all generators: SELECT * FROM RDB$GENERATORS; ... but don't know the right generator. Could we save the name of the generator in the description of the field, which should be the autovalue-field? For HSQLDB you haven't to know this, because the code only need the table and the field.
(In reply to robert from comment #5) > This is the code for getting all generators: > > SELECT * FROM RDB$GENERATORS; > > ... but don't know the right generator. > > Could we save the name of the generator in the description of the field, > which should be the autovalue-field? > > For HSQLDB you haven't to know this, because the code only need the table > and the field. Have found this: SELECT RDB$FIELD_NAME, RDB$RELATION_NAME, RDB$GENERATOR_NAME FROM RDB$RELATION_FIELDS WHERE RDB$GENERATOR_NAME IS NOT NULL Will show the generatorname for the fields of the tables with autoincrement value. Generatornames are RDB$1, RDB$2 and so on ... ALTER SEQUENCE RDB$1 RESTART WITH 10; Sets the generator to 10. This should be the last value of the autoincrement-field. Not the value, which should be generated as next value! Next autoincremented value will be 11, when "RESTART WITH 10" is set - a little bit confusing.
Tamas Bunth committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=22cfac8edeb3371e458b9f9533478c6e5a81287e tdf#116982 dbahsql: fix autoincrementation It will be available in 6.1.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.
Verified in Version: 6.1.0.0.alpha0+ Build ID: b36ea44dcbdb862b0ac6e6cdaf27225b43dc0c7e CPU threads: 4; OS: Linux 4.13; UI render: default; VCL: gtk2; Locale: ca-ES (ca_ES.UTF-8); Calc: group