Bug 119628 - EDITING: Firebird Embedded: error message when resetting auto increment a second time
Summary: EDITING: Firebird Embedded: error message when resetting auto increment a sec...
Status: RESOLVED WORKSFORME
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
6.0.6.2 release
Hardware: All macOS (All)
: medium normal
Assignee: Not Assigned
URL:
Whiteboard:
Keywords:
Depends on:
Blocks: Database-Firebird-Default
  Show dependency treegraph
 
Reported: 2018-08-31 22:38 UTC by kam3don
Modified: 2023-11-29 06:40 UTC (History)
2 users (show)

See Also:
Crash report or crash signature:


Attachments
A simple database for testing (5.96 KB, application/vnd.oasis.opendocument.database)
2018-08-31 22:38 UTC, kam3don
Details
a small example base with macro (12.90 KB, application/vnd.sun.xml.base)
2019-10-20 15:30 UTC, cam
Details

Note You need to log in before you can comment on or make changes to this bug.
Description kam3don 2018-08-31 22:38:00 UTC
Created attachment 144590 [details]
A simple database for testing

macOS High Sierra 10.13.6

Example: I have a table with records 1-30 with the primary key AutoValue set to Yes. I delete records 16-30. I don't want the next record I add to have a primary key of 31.

To reset this, I go to Tools > SQL and type in alter table TABLE_NAM alter FIELD_NAME restart with 15;

All is well, and I am able to add new records without a gap in the primary key sequence.

However, if I again delete some records and try to execute this SQL statement again, I receive the following error message:

1: firebird_sdbc error: *violation of PRIMARY or UNIQUE KEY constraint "INTEG_1" on table "TABLE_NAM" caused by 'alter table "TABLE_NAM" alter "FIELD_NAME" restart with NNNN;'

(NNNN being whatever number I input in my second attempt at restarting the sequence.)

The funny thing is, despite the error message, Base will do as I directed and restart the sequence once again.

Attached is the database I was using.
Comment 1 Alex Thurgood 2018-09-03 08:20:46 UTC
I get the following error message 

1: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Token unknown - line 1, column 42
*"Employee ID"
caused by
'isc_dsql_prepare'



when using 
Version: 6.2.0.0.alpha0+
Build ID: b8658c0e847afe1ed199c5fa7a2dd5d1d61ccbac
CPU threads: 4; OS: Mac OS X 10.13.6; UI render: default; 
Locale: fr-FR (fr_FR.UTF-8); Calc: threaded

and the SQL command:

ALTER TABLE "Employee Table" ALTER FIELD "Employee ID" RESTART WITH 16
Comment 2 Alex Thurgood 2018-09-03 08:24:57 UTC
Sorry, my bad, I misread the SQL statement. Retrying with the correct expression, I can reproduce this.

Confirming
Comment 3 Xisco Faulí 2018-09-04 11:59:32 UTC
(In reply to Alex Thurgood from comment #2)
> Sorry, my bad, I misread the SQL statement. Retrying with the correct
> expression, I can reproduce this.
> 
> Confirming

and what's the correct expression ?
Comment 4 Alex Thurgood 2018-09-04 14:34:13 UTC
alter table "Employee Table" alter "Employee ID" restart with 15;
Comment 5 QA Administrators 2019-09-05 09:34:56 UTC Comment hidden (obsolete)
Comment 6 cam 2019-10-20 15:30:44 UTC
Created attachment 155164 [details]
a small example base with macro
Comment 7 cam 2019-10-20 15:33:37 UTC
I just tried this Sunday, 20/10/2019 and the bug is still present.
Cordially

I use  LibreOffice :
Version: 6.2.7.1 (x64)
Build ID: 23edc44b61b830b7d749943e020e96f5a7df63bf
Threads CPU : 4; OS : Windows 10.0; UI Render : par défaut; VCL: win; 
Locale : fr-FR (fr_FR); Langue IHM : fr-FR
Calc: CL
Comment 8 Julien Nabet 2020-11-28 18:12:03 UTC
On pc Debian x86-64 with LO Debian package 7.0.3, I don't reproduce this.

Here are the steps:
- opened table
- deleted 16->end
- closed table
- launched in SQL Tools:
alter table "Employee Table" alter "Employee ID" restart with 15;
- opened table
- added row
-> id = 16 => ok
- deleted new row + row before
- launched in SQL Tools:
alter table "Employee Table" alter "Employee ID" restart with 14
-> id = 15 => ok

Any update with LO 7.0.3?
Also with a daily build of master (future 7.2.0) since Firebird has been upgraded from 3.0.0 to 3.0.7
Did I miss something?
Comment 9 QA Administrators 2021-05-28 04:51:02 UTC Comment hidden (obsolete)
Comment 10 Robert Großkopf 2021-05-28 14:16:55 UTC
Could see the same buggy behavior in LO 7.1.3.2 on OpenSUSE 15.2 64bit rpm Linux:

1. Delete the rows up to last "Employee ID" 20
2. SQL: ALTER TABLE "Employee Table" ALTER "Employee ID" RESTART WITH 20
3. Input some new values (Funny: Trying to input new values will set every new character to a new line - another bug?)
4. Delete  the rows up to last "Employee ID" 18
5. SQL: ALTER TABLE "Employee Table" ALTER "Employee ID" RESTART WITH 18

A warning appears like 
*violation of PRIMARY or UNIQUE KEY constraint "INTEG_18" on …
… but the "Employees ID" will be restarted right.
Comment 11 QA Administrators 2021-11-25 05:22:21 UTC Comment hidden (obsolete)
Comment 12 Robert Großkopf 2021-11-25 07:53:57 UTC
Info is given in comment 10, so I set this back to "NEW"
Comment 13 Julien Nabet 2021-11-28 13:30:55 UTC
I gave a new try on pc Debian x86-64 with master sources updated today with the first file and comment 10.
Sometimes I got this error:
"*Cursor is not open
caused by
'isc_dsql_fetch'"
that I don't understand but never had the one indicated in the bugtracker.

Can't help here=>uncc myself.
Comment 14 QA Administrators 2023-11-29 03:14:17 UTC Comment hidden (obsolete)
Comment 15 Robert Großkopf 2023-11-29 06:40:53 UTC
Couldn't reproduce the buggy behavior any more with 
Version: 7.6.3.1 (X86_64) / LibreOffice Community
Build ID: c4af5b1259bceea6e979e6fe2435dbee7a5a87c2
CPU threads: 6; OS: Linux 5.14; UI render: default; VCL: kf5 (cairo+xcb)
Locale: de-DE (de_DE.UTF-8); UI: de-DE
Calc: threaded

I will set this one to WORKSFORME.
Feel free to reopen if it could be reproduced on other systems.