Bug 116982 - FIREBIRD : Migration : import of table with autoincrement leads to integrity violation when attempting to add new record
Summary: FIREBIRD : Migration : import of table with autoincrement leads to integrity ...
Status: VERIFIED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.0.0.alpha0+
Hardware: All All
: high major
Assignee: Not Assigned
URL:
Whiteboard: target:6.1.0
Keywords: bisected, regression
Depends on:
Blocks: Database-Firebird-Migration
  Show dependency treegraph
 
Reported: 2018-04-13 08:16 UTC by Alex Thurgood
Modified: 2018-04-17 08:36 UTC (History)
3 users (show)

See Also:
Crash report or crash signature:


Attachments
Test hsqldb file for migration to firebird (4.01 KB, application/vnd.oasis.opendocument.database)
2018-04-13 08:17 UTC, Alex Thurgood
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Alex Thurgood 2018-04-13 08:16:15 UTC
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
Comment 1 Alex Thurgood 2018-04-13 08:17:48 UTC
Created attachment 141322 [details]
Test hsqldb file for migration to firebird
Comment 2 Xisco Faulí 2018-04-13 10:23:05 UTC
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
Comment 3 Lionel Elie Mamane 2018-04-13 10:31:55 UTC
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.
Comment 4 Robert Großkopf 2018-04-14 09:30:26 UTC
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>;
Comment 5 Robert Großkopf 2018-04-14 09:44:22 UTC
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.
Comment 6 Robert Großkopf 2018-04-14 20:12:12 UTC
(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.
Comment 7 Commit Notification 2018-04-15 19:29:52 UTC
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.
Comment 8 Xisco Faulí 2018-04-17 08:36:55 UTC
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