Issue Details (XML | Word | Printable)

Key: CORE-4002
Type: Bug Bug
Status: Closed Closed
Resolution: Fixed
Priority: Major Major
Assignee: Vlad Khorsun
Reporter: Mikhail
Votes: 0
Watchers: 2
Operations

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

Error message "index unexpectedly deleted" in database trigger on commit transaction

Created: 30/Nov/12 03:37 AM   Updated: 23/Apr/13 02:15 PM
Component/s: Engine
Affects Version/s: 2.5.3
Fix Version/s: 3.0 Alpha 1, 2.1.6, 2.5.3

Time Tracking:
Not Specified

Environment: Windows XP Sp3
Issue Links:
Relate
 

Planning Status: Unspecified


 Description  « Hide
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>



 All   Comments   Work Log   Change History   Version Control   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 30/Nov/12 07:59 AM
Fix for CORE-3855 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.