Error message "index unexpectedly deleted" in database trigger on commit
transaction
------------------------------------------------------------------------------------
Key: CORE-4002
URL: http://tracker.firebirdsql.org/browse/CORE-4002
Project: Firebird Core
Issue Type: Bug
Components: Engine
Affects Versions: 2.5.3
Environment: Windows XP Sp3
Reporter: Mikhail
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>
--
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
http://tracker.firebirdsql.org/secure/Administrators.jspa
-
For more information on JIRA, see: http://www.atlassian.com/software/jira
------------------------------------------------------------------------------
Keep yourself connected to Go Parallel:
TUNE You got it built. Now make it sing. Tune shows you how.
http://goparallel.sourceforge.net
Firebird-Devel mailing list, web interface at
https://lists.sourceforge.net/lists/listinfo/firebird-devel
