Bug Hunting Session
Bug 119962 - FireBird: Auto-Increase after copy table: Error inserting new data item.
Summary: FireBird: Auto-Increase after copy table: Error inserting new data item.
Status: NEW
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.1.1.2 release
Hardware: x86-64 (AMD64) All
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-09-18 18:15 UTC by Eigil Wiwe
Modified: 2019-04-17 10:53 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
Database table to demonstrate the error (3.89 KB, application/vnd.sun.xml.base)
2018-09-18 18:15 UTC, Eigil Wiwe
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Eigil Wiwe 2018-09-18 18:15:37 UTC
Created attachment 144991 [details]
Database table to demonstrate the error

FireBird: Auto-Increase after copy table: Error inserting new data item. 

firebird_sdbc error:
*violation of PRIMARY or UNIQUE KEY constraint "INTEG_9" on table "Letters"
*Problematic key value is ("ID" = 2)
caused by
'isc_dsql_execute'

I have prepared the table "Letter" by copying it from another table. There are 4 lines in the table and I tried to register a fifth line. But ("ID" = 2) The ID value counts up from 1 and increases by 1 at each new attempt.
After the table was copied, the reset value is zero. Therefore, the first entries fail because the key value is not allowed. But the restart value is incremented at each new attempt so that the value insertion succeeds when ID = 5

I have attached the file: Autoincrument error. Use it to see who the error behaves.

Do the following: '

Copy "Letters" to Letters4. (Definition and data)
Open Letter4 and try adding a new letter. It gives the above error message. Repeat the experiment until fifth succession succeeds.
Delete the fifth line again.
Delete Letters4

Kopier "Letters" til Letters4. (Definition og data)
Kopier "Letters2" til letters4 (tilknyt kun data).
Open Letters4 and try to insert a new letter. It gives the above error message. Repeat the experiment until it succeeds in the ninth time. 

The error can be corrected with: 
go to Tools> SQL and type in alter table TABLE_NAME change FIELD_NAME restart with 8; 
“alter table "Letters4" alter "ID" restart with 8;”
However, I think updating should work immediately after copying. 



Delete Letters4 and Repeat.
Copy "Letters" to Letters4. (Definition and data)
Copy "Letters3" to letters4 (attach only data).
Try to insert a new letter X, for example. It provides the above error message. Repeat the experiment until the thirteenth time succeeds. It only succeeded when an "X" comes in the letter field.
The table now shows 5 times ID = 12. The one ID-12 is right the four others are wrong. I can not figure out why This view does not reflect the true table.
Therefore close the table and open it again.
The four fake 12's are now filled with the correct ID key figures. Those who were not already filled in. 

We could have removed the error in the same way as in the previous example, but this reveals another error or maybe the actual error. 

Regarding the 12 numbers. In the update we show that an entry is attempted to insert a line with ID = 12, There must be a variable that sets the correct number for (12 + 1) and this variable Must be inserted in ID that is the primary key. But the variable will not be set. It is only increased by 1 in relation to its previous value.
Comment 1 Alex Thurgood 2018-09-19 11:24:48 UTC
(In reply to Eigil Wiwe from comment #0)

> Do the following: '
> 
> Copy "Letters" to Letters4. (Definition and data)
> Open Letter4 and try adding a new letter. It gives the above error message.
> Repeat the experiment until fifth succession succeeds.
> Delete the fifth line again.
> Delete Letters4
> 

Confirming with

Version: 6.1.1.2
Build ID: 5d19a1bfa650b796764388cd8b33a5af1f5baa1b
Threads CPU : 8; OS : Mac OS X 10.13.6; UI Render : par défaut; 
Locale : fr-FR (fr_FR.UTF-8); Calc: group threaded
Comment 2 Alex Thurgood 2018-09-19 11:28:51 UTC
So, it would appear that the code which manages the ID generator doesn't get the correct count, and causes the subsequent insertion errors.
Comment 3 Julien Nabet 2018-09-19 14:21:54 UTC
Function isAutoIncrement returns a sal_bool value (sal_True or sal_False), see
https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/ResultSetMetaData.cxx#186

According to https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.identity_columns.txt, each identity column is associated with a generator (RDB$GENERATOR_NAME). This generator provide s a number and stores it.
But it seems no mechanism exists in copy process to retrieve its state (or missed it).


Still with this same doc, notice too that the test lines 206/207 is wrong
206              if(iType == 1) // IDENTITY
207                  return true;
it should be:
206              if(iType == 0 || iType == 1) // IDENTITY
207                  return true;
Since 0 is for GENERATED ALWAYS (you can't override the identiy column in Insert statements - insert/update/...) and 1 for GENERATED BY DEFAULT
Now perhaps it's because construct method https://opengrok.libreoffice.org/xref/core/connectivity/source/drivers/firebird/Column.cxx#28 uses "GENERATED BY DEFAULT AS IDENTITY" but it's another story.