Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Creating self-referential FK crashes database (bug-check) whether constraint violation had place [CORE3925] #4260

Closed
firebird-automations opened this issue Sep 14, 2012 · 16 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Arioch (arioch)

/*** met this bug during refactoring the legacy database ***/
/*** database is default in IBExpert Dialect 3, UTF-8, SYSDBA/masterkey ****/
/*** Crashing Firebird 2.5.1 Win64 on creating self-referential FK ***/

CREATE DOMAIN T_TEXT_V064 AS
VARCHAR(64) CHARACTER SET WIN1251
COLLATE PXW_CYRL;

CREATE DOMAIN T_TEXT_V256 AS
VARCHAR(255) CHARACTER SET WIN1251
COLLATE PXW_CYRL;

CREATE DOMAIN T_IDLONG AS INTEGER;

COMMIT;

CREATE TABLE EVENTS (
ID_EVENT T_IDLONG NOT NULL ,
EVENT_NAME T_TEXT_V256 NOT NULL COLLATE PXW_CYRL,
ID_RE_EVENT T_IDLONG );

ALTER TABLE EVENTS ADD CONSTRAINT PK_EVENTS PRIMARY KEY (ID_EVENT);

COMMIT;

INSERT into events values (10, 'abcdefgh', 0);
/**** this data was old-style. Should have pre-NULLed ID_RE_EVENT, but did not noticed it.
Then suddenly... ***/

COMMIT;

update RDB$RELATION_FIELDS set RDB$FIELD_SOURCE = 'T_TEXT_V064'
where (RDB$FIELD_NAME = 'EVENT_NAME') and (RDB$RELATION_NAME = 'EVENTS');
COMMENT ON COLUMN EVENTS.EVENT_NAME IS 'unit IBEvents; const
IB_MAX_EVENT_LENGTH = 64;' ;

COMMIT;

ALTER TABLE EVENTS
ADD CONSTRAINT FK_EVENTS_ANSWERS
FOREIGN KEY (ID_RE_EVENT)
REFERENCES EVENTS(ID_EVENT)
ON DELETE CASCADE
ON UPDATE CASCADE;

commit;
/* violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "FK_EVENTS_ANSWERS" on table "EVENTS".
Foreign key reference target does not exist. */

rollback;

delete from events;

/* Unsuccessful execution caused by a system error that precludes
successful execution of subsequent statements.
internal gds software consistency check (partner index description not found
(175), file: idx.cpp line: 1346). */

Commits: 9847e01 3f794b6

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The direct manipulation of system tables for the purpose described here is not supported.

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

How can i do it in FB 2.1.x ? IBExpert does it this way, and SQL docs i saw show no other method for 2.1.x
We cannot dump 2.1.x support as of now

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

Drop the constraint, change the domain and then re-add the constraint.

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

that field seems not participate in contraint. It is rather wierd hack so i kept it, but constraint if FK integer to integer, not about varchar column

@firebird-automations
Copy link
Collaborator Author

Commented by: Sean Leyne (seanleyne)

The issue is reproducible on v2.1.

It can be reproduced with a much smaller (on point) script:

CREATE TABLE EVENTS (
ID_EVENT Integer NOT NULL ,
EVENT_NAME VARCHAR(255) CHARACTER SET WIN1251 NOT NULL COLLATE PXW_CYRL,
ID_RE_EVENT Integer );

ALTER TABLE EVENTS ADD CONSTRAINT PK_EVENTS PRIMARY KEY (ID_EVENT);
COMMIT;

INSERT into events values (10, 'abcdefgh', 0);
COMMIT;

ALTER TABLE EVENTS
ADD CONSTRAINT FK_EVENTS_ANSWERS
FOREIGN KEY (ID_RE_EVENT)
REFERENCES EVENTS(ID_EVENT)
ON DELETE CASCADE
ON UPDATE CASCADE;

commit;
/* violation of FOREIGN KEY constraint "".
violation of FOREIGN KEY constraint "FK_EVENTS_ANSWERS" on table "EVENTS".
Foreign key reference target does not exist. */

rollback;

delete from events;

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

Just checked Sean's script on FB win64 v.3.0rc2

after the last "delete..." statement we still have a bugcheck.

SQL> delete from events;
Statement failed, SQLSTATE = XX000
internal Firebird consistency check (partner index description not found (175),
file: idx.cpp line: 1268)

Please, fill the affected versions field with 2.1.7, 2.5.5, 3.0

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

Also maybe Sean's script can be added to QA regression testing suite ?

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

After i run it in win64 3rc2 isql embedded server mode (fbserver service is not started) after i run this test and after i type "QUIT;" isql crashes with

Имя события проблемы: APPCRASH
Имя приложения: isql.exe
Версия приложения: 3.0.0.32366
Отметка времени приложения: 56d546bf
Имя модуля с ошибкой: Engine12.DLL
Версия модуля с ошибкой: 3.0.0.32366
Отметка времени модуля с ошибкой: 56d54715
Код исключения: c0000005
Смещение исключения: 0000000000255927
Версия ОС: 6.1.7601.2.1.0.256.48
Код языка: 1049
Дополнительные сведения 1: b6aa
Дополнительные сведения 2: b6aa51008599888f9ba77660493a437d
Дополнительные сведения 3: ebb8
Дополнительные сведения 4: ebb884e093773972e3d81a4a78b27908

@firebird-automations
Copy link
Collaborator Author

Modified by: @dyemanov

Version: 3.0 RC2 [ 10048 ]

Version: 2.5.5 [ 10670 ]

Version: 3.0 RC1 [ 10584 ]

Version: 2.5.4 [ 10585 ]

Version: 2.5.3 Update 1 [ 10650 ]

Version: 2.1.7 [ 10651 ]

Version: 2.5.3 [ 10461 ]

Version: 2.5.2 Update 1 [ 10521 ]

Version: 2.5.2 [ 10450 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

still crashes FB 3.0.4 x64

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
internal Firebird consistency check (partner index description not found (175), file: idx.cpp line: 1273).

Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements.
internal Firebird consistency check (can't continue after bugcheck).

Consequent connections to FDB file lead to kind of random, inconsistent operations.

Stopping Firebird Windows service becomes impossible, only killing it.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

status: Open [ 1 ] => Resolved [ 5 ]

resolution: Fixed [ 1 ]

Fix Version: 3.0.5 [ 10885 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Basil A. Sidorov (basid)

Simplest primer:
create table test(key integer not null primary key, ref integer);
insert into test(key,ref) values(1,-1);
commit;
alter table test add constraint fk_key_ref foreign key (ref) references test(key);
delete from test;
commit;

And - no Firebird hang on service stop/restart (Windows 7 SP1 x64)

WI-V3.0.4.33054 Firebird 3.0
WI-V3.0.4.33054 Firebird 3.0/tcp (basid)/P15:C
WI-V3.0.4.33054 Firebird 3.0/tcp (basid)/P15:C
Database: 'localhost/13040:employee', User: SYSDBA
SQL> create table test(key integer not null primary key, ref integer);
SQL> insert into test(key,ref) values(1,-1);
SQL> commit;
SQL> alter table test add constraint fk_key_ref foreign key (ref) references test(key);
Statement failed, SQLSTATE = 23000
violation of FOREIGN KEY constraint "FK_KEY_REF" on table "TEST"
-Foreign key reference target does not exist
-Problematic key value is ("REF" = -1)
SQL> delete from test;
Statement failed, SQLSTATE = XX000
internal Firebird consistency check (partner index description not found (175), file: idx.cpp line: 1273)
SQL> commit;
Statement failed, SQLSTATE = XX000
internal Firebird consistency check (can't continue after bugcheck)

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Resolved [ 5 ]

QA Status: Done successfully

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

status: Resolved [ 5 ] => Closed [ 6 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: Arioch (arioch)

Can this fix be pushed into FB25 sources?

Yes, i know, there is no plants for 2.5.post-9 releases as of yet, but plans change sometimes.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment