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

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 [CORE1847] #2276

Open
firebird-automations opened this issue Apr 18, 2008 · 11 comments

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @hvlad

Votes: 2

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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Verified bug exists in 2.0.0 too

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

Version: 2.0.3 [ 10200 ]

Version: 2.0.2 [ 10130 ]

Version: 2.0.1 [ 10090 ]

Version: 2.0.0 [ 10091 ]

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Corrected typo in description

@firebird-automations
Copy link
Collaborator Author

Modified by: @hvlad

summary: Blob not found error if blob field is used in after update trigger and two updates in the same transaction under PSQL savepoint is perormed => 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

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: Vincent Kwinsey (vincent_kwinsey)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

Its definitely another issue. If you have reproducilble test case, please, register it at tracker.

@firebird-automations
Copy link
Collaborator Author

Commented by: Vincent Kwinsey (vincent_kwinsey)

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.

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

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

@firebird-automations
Copy link
Collaborator Author

Commented by: Honza Hubeny (hubeny)

it was verified by me that this issue exists also on 2.1.1 and 2.1.3

@firebird-automations
Copy link
Collaborator Author

Commented by: @hvlad

So far there is no fix for this bug.
Therefore or not access blobs in the AFTER trigger or do it in BEFORE trigger.

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