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

Error message "index unexpectedly deleted" in database trigger on commit transaction [CORE4002] #4334

Closed
firebird-automations opened this issue Nov 30, 2012 · 6 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: Mikhail (s-m)

Is related to CORE3855

An error occurs when I trying to read the records from the global temporary table in the order of the index. Table must contains the blob field.
Script to reproduce the problem:

1. test.gtt_blob_DDL.sql:

SET SQL DIALECT 3;

SET NAMES WIN1251;

CREATE DATABASE 'D:\fb_dbs\TEST_GTT_BLOB.FDB'
USER 'SYSDBA' PASSWORD 'masterkey'
PAGE_SIZE 16384
DEFAULT CHARACTER SET WIN1251 COLLATION WIN1251;

CREATE GLOBAL TEMPORARY TABLE G1 (
ID INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;

CREATE GLOBAL TEMPORARY TABLE G2 (
ID INTEGER NOT NULL,
ID_G1 INTEGER NOT NULL,
TT VARCHAR(100) NOT NULL,
BB BLOB SUB_TYPE 1 SEGMENT SIZE 80 NOT NULL
) ON COMMIT DELETE ROWS;
COMMIT;

ALTER TABLE G1 ADD CONSTRAINT PK_G1 PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD PRIMARY KEY (ID);
COMMIT;
ALTER TABLE G2 ADD FOREIGN KEY (ID_G1) REFERENCES G1 (ID);
COMMIT;

SET TERM ^ ;

CREATE OR ALTER PROCEDURE TEST_GG
AS
declare I int = 0;
begin
in autonomous transaction do while (I<100) do
begin
insert into G1 (ID, TT, BB) values (:I, rand(), rand());

  insert into G2 \(ID, ID\_G1, TT, BB\) values \(:I\*1000\+1, :I, rand\(\), rand\(\)\);
  insert into G2 \(ID, ID\_G1, TT, BB\) values \(:I\*1000\+2, :I, rand\(\), rand\(\)\);
  insert into G2 \(ID, ID\_G1, TT, BB\) values \(:I\*1000\+3, :I, rand\(\), rand\(\)\);
  insert into G2 \(ID, ID\_G1, TT, BB\) values \(:I\*1000\+4, :I, rand\(\), rand\(\)\);
  insert into G2 \(ID, ID\_G1, TT, BB\) values \(:I\*1000\+5, :I, rand\(\), rand\(\)\);

  I=I\+1;
end

end^

COMMIT^

CREATE OR ALTER TRIGGER ON_COMMIT
ACTIVE ON TRANSACTION COMMIT POSITION 999
AS
declare variable ID integer;
declare variable TT varchar(100);
declare variable BB BLOB SUB_TYPE 1;

declare variable ID_G1 integer;
declare variable TT_2 varchar(100);
declare variable BB_2 BLOB SUB_TYPE 1;

declare I int=0;
begin
for select ID, TT, BB
from G1
order by ID
into :ID, :TT, :BB
do
begin
for select ID_G1, TT, BB
from G2
where ID_G1=:ID
order by ID
into :ID_G1, :TT_2, :BB_2
do
begin
I=I+1;
end
end
end^

COMMIT^
SET TERM ; ^

2. test.gtt_blob.sql:

CONNECT 'D:\fb_dbs\TEST_GTT_BLOB.FDB' USER 'SYSDBA' PASSWORD 'masterkey';
execute procedure TEST_GG;
COMMIT;

3. Execute scripts:

C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob_DDL.sql
Use CONNECT or CREATE DATABASE to specify a database

C:\Firebird\2_5\bin>isql.exe -i test.gtt_blob.sql
Use CONNECT or CREATE DATABASE to specify a database
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 2 in file test.gtt_blob.sql
Statement failed, SQLSTATE = 42000
index unexpectedly deleted
-At trigger 'ON_COMMIT' line: 14, col: 3
After line 3 in file test.gtt_blob.sql

C:\Firebird\2_5\bin>

Commits: 32ebb58 3e09208 0e766ce

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

assignee: Vlad Khorsun [ hvlad ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Link: This issue is related to CORE3855 [ CORE3855 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Fix for CORE3855 assign tx num to the blob record (it was never assigned before).
It is necessary to detect correct page space when blob will be stored on disk.
But tx num was assigned using blob's transaction, not current transaction.
This is wrong in case of autonomous transaction - blob binded to the outer transaction while record should contain number of storing transaction.

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0 Alpha 1 [ 10331 ]

Fix Version: 2.5.3 [ 10461 ]

Fix Version: 2.1.6 [ 10460 ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pcisar

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

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: Done successfully

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