Issue Details (XML | Word | Printable)

Key: CORE-1847
Type: Bug Bug
Status: Open Open
Priority: Major Major
Assignee: Unassigned
Reporter: Vlad Khorsun
Votes: 2
Watchers: 2
Operations

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

Blob not found error if blob field is used in after update trigger and two updates in the same transaction under PSQL savepoint is performed

Created: 18/Apr/08 09:30 AM   Updated: 08/Nov/09 12:49 PM
Component/s: Engine
Affects Version/s: 2.0.0, 2.0.1, 2.0.2, 2.0.3, 2.5 Initial, 2.1.0
Fix Version/s: None


 Description  « Hide
RECREATE TABLE HIS_TEMP (
    HID INTEGER,
    HVALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80
);

RECREATE TABLE HIS_TEMP1 (
    HID INTEGER,
    HVALUE BLOB SUB_TYPE 1 SEGMENT SIZE 80
);


CREATE OR ALTER TRIGGER HIS_TEMP_AU FOR HIS_TEMP
ACTIVE AFTER UPDATE POSITION 0
AS
BEGIN
  INSERT INTO his_temp1 VALUES (old.hid, old.hvalue);
END

COMMIT;

INSERT INTO his_temp VALUES (1, 'BLOB_0');
COMMIT;

EXECUTE BLOCK AS
BEGIN
  UPDATE HIS_TEMP SET HVALUE = 'BLOB_1' WHERE HID = 1;
  UPDATE HIS_TEMP SET HVALUE = 'BLOB_2' WHERE HID = 1;
END

BLOB not found.
At trigger 'HIS_TEMP_AU' line: 5, col: 3.


If i change trigger event by BEFORE UPDATE all is ok


 All   Comments   Change History   Subversion Commits      Sort Order: Ascending order - Click to sort in descending order
Vlad Khorsun added a comment - 18/Apr/08 09:34 AM
Verified bug exists in 2.0.0 too

Vlad Khorsun added a comment - 18/Apr/08 09:36 AM
Corrected typo in description

Vlad Khorsun added a comment - 18/Apr/08 09:44 AM
Blob not found is raised when second run of trigger tried to open old.hvalue. Obviously at this moment it must point to a 'BLOB_1' blob.
But it was already removed by second UPDATE as update_in_place() is called for subsequent updates of record in the same transaction and previous intermediate version is deleted immediately.

BEFORE UPDATE trigger fired before update_in_place() so it can access 'BLOB_1' blob without a problem.

Vincent Kwinsey added a comment - 14/Jul/08 12:50 PM
I am not sure, whether my issue is the same as reported one - but I am still investigating. I have prorgam that uses SP and executes it together with lot of select and updates. When I exectue 'select * from mon$statements' and leave this transaction open and then execute complex logic from my program, then all is OK, but when I commit the transaction for 'select * from mon$statements', then there is error 'BLOB not found'. It is funny, but can be repeated. How mon$... tables are maintained - if triggers are used, then my case can be explained somehow.

Vlad Khorsun added a comment - 14/Jul/08 03:25 PM
Its definitely another issue. If you have reproducilble test case, please, register it at tracker.

Vincent Kwinsey added a comment - 04/Aug/08 04:45 AM - edited
OK - I have tried to get more details how my [14/Jul/08 12:50 PM] can be repeated, - but without success, here is data from IBMonitor:
"1481 09:44:27 SQL Stmt: INTRBASE - Fetch
1482 09:44:27 SQL Vendor: INTRBASE - isc_dsql_fetch
1483 09:44:27 SQL Stmt: INTRBASE - EOF
1484 09:44:27 SQL Stmt: INTRBASE - Reset
1485 09:44:27 SQL Vendor: INTRBASE - isc_dsql_free_statement
1486 09:44:27 SQL Blob IO: INTRBASE - Get Len
1487 09:44:27 SQL Vendor: INTRBASE - isc_dsql_allocate_statement
1488 09:44:27 SQL Vendor: INTRBASE - isc_open_blob
1489 09:44:27 SQL Vendor: INTRBASE - isc_sqlcode
1490 09:44:27 SQL Error: INTRBASE - BLOB not found
1491 09:44:27 SQL Error: INTRBASE - Unmapped SQL Error Code: -901
1492 09:44:27 SQL Vendor: INTRBASE - isc_dsql_free_statement"

So - the error arises at the call or ' isc_open_blob', but my problem is that I am using BDE (IDAPI) and this call of ' isc_open_blob' is inside the BDE API functions - so - I guess - no chances to catch that some arguments to isc_open_blob can be bad or so. Actually - it is hard to distuinguish whether this is BDE error, FB 2.x error or one or another software has violated some protocol which e.g. is used by isql or other program to transalate SQL commands into FB API calls. The only certain fact is, that Delphi code, that worked with FB1.5.x now is giving 'BLOB not found' with FB2.x.

Vlad Khorsun added a comment - 04/Aug/08 05:10 AM
I don't understand - first you told about 'select * from mon$statements', then about 'Delphi code, that worked with FB1.5.x' ;)

If you have reproducible test case (application, script, etc) - please create new tracker entry and attach it there

Honza Hubeny added a comment - 08/Nov/09 12:30 PM
it was verified by me that this issue exists also on 2.1.1 and 2.1.3

Vlad Khorsun added a comment - 08/Nov/09 12:49 PM
So far there is no fix for this bug.
Therefore or not access blobs in the AFTER trigger or do it in BEFORE trigger.