Issue Details (XML | Word | Printable)

Key: CORE-3925
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Arioch
Votes: 0
Watchers: 8
Operations

If you were logged in you would be able to see more operations.
Firebird Core

Creating self-referential FK crashes database (bug-check) whether constraint violation had place

Created: 14/Sep/12 01:41 PM   Updated: 26/Apr/19 05:20 AM
Component/s: Engine
Affects Version/s: 2.5.1, 2.5.2, 2.5.2 Update 1, 2.5.3, 2.1.7, 2.5.3 Update 1, 2.5.4, 3.0 RC1, 2.5.5, 3.0 RC2
Fix Version/s: 3.0.5, 4.0 Beta 2

Environment: Win7x64 / Firebird Win64 SuperServer 2.51 or 2.5.2rc1

QA Status: Done successfully


 Description  « Hide
/*** 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). */

 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Sean Leyne added a comment - 19/Sep/12 06:33 PM
The direct manipulation of system tables for the purpose described here is not supported.

Arioch added a comment - 20/Sep/12 06:21 AM - edited
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

Sean Leyne added a comment - 20/Sep/12 02:14 PM
Drop the constraint, change the domain and then re-add the constraint.

Arioch added a comment - 20/Sep/12 02:28 PM
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

Sean Leyne added a comment - 20/Sep/12 02:40 PM
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;

Arioch added a comment - 05/Apr/16 03:20 PM
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

Arioch added a comment - 05/Apr/16 03:22 PM
Also maybe Sean's script can be added to QA regression testing suite ?

Arioch added a comment - 05/Apr/16 03:25 PM
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

Arioch added a comment - 18/Apr/19 02:59 PM
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.

Basil A. Sidorov added a comment - 24/Apr/19 03:08 AM
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)