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 "no current record to fetch" if some record is to be deleted both by the statement itself and by some trigger fired during statement execution [CORE5142] #5425

Closed
firebird-issue-importer opened this issue Mar 9, 2016 · 12 comments

Comments

@firebird-issue-importer
Copy link

firebird-issue-importer commented Mar 9, 2016

Submitted by: Olaf Kluge (okl)

Relate to CORE5182

Attachments:
sql.txt

Votes: 2

A stored Procedure should delete some records.

ID some fields ID_AUTOINSERT
123 xxx null
234 xxx null
255 xxx 266
266 xxx null

Now there is a trigger after delete in this table who deletes:
if(old.ap = 69) then
begin
delete from tplan_kw_pos where id_autoinsert = http://old.id;
end

In this case, FB will delete the record with ID 266 during deleting ID 255 and the other statement intends to delete the record 266 too.

Now in FB 3 RC 2 I get this error message:

can't format message 13:492 -- message file C:\WINDOWS\SYSTEM32\firebird.msg not found.
no current record for fetch operation.

In FB 2.55, FB2.1 this was not a problem.

the trigger is required because the user can delete the one record only, in
this case the trigger deletes the referenced one with the id_autoinsert-id too. But if I delete the
entire Order, the record will be delete twice. How can I solve this in fb 3?

Commits: d5ec668 6a3d9d0

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 9, 2016

Commented by: @dyemanov

Does error happen with just one connection or two independent connections? Does the second statement delete the record after the first one or concurrently? Which statement does produce the error - first or second? Any chance for a reproducible test case?

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 9, 2016

Commented by: Olaf Kluge (okl)

Hello,

I get the error with one connection, the other clients does not call this stored procedure. This SP should delete all records incluedes a certain order, I think, during deleting the one record, the trigger deletes in the same table this with the referenced ID and the SP found not the marked record again and I get an error during SP-Call like this in my post.
By debugging, the statement in SP "delete from table where .. "

Herewith a sql file for creating test-table, insert records and execute for error.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Mar 9, 2016

Modified by: Olaf Kluge (okl)

Attachment: sql.txt [ 12915 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 4, 2016

Modified by: @dyemanov

summary: delete trigger during deleting (twice deleting and error no current record for fetch operation. => Error "no current record to fetch" if some record is to be deleted both by the statement itself and by some trigger fired during statement execution

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Apr 5, 2016

Commented by: Arioch (arioch)

maybe you can workaround (not solve) it by deleting in autonomous transaction

http://www.firebirdsql.org/refdocs/langrefupd25-psql-autonomous-trans.html

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 31, 2016

Commented by: Erik Jõeveer (erikj)

I have same problem with FB3.0 (no problems with FB2 or FB1.5).
It also involves delete trigger that deletes from same table. And in result it is not possible to delete multiple records from this table in one transaction. It seems that if record is already deleted by trigger then it is still visible in same transaction context and when second delete occurs then this error is thrown:
ISC ERROR CODE:335544348 ISC ERROR MESSAGE: no current record for fetch operation

CREATE TABLE TEST_TABLE (
ID NUMERIC(10,0) NOT NULL,
RELATED_ID NUMERIC(10,0)
);
ALTER TABLE TEST_TABLE ADD PRIMARY KEY (ID);
COMMIT;

SET TERM ^ ;
CREATE TRIGGER TEST_TABLE_D FOR TEST_TABLE
ACTIVE BEFORE DELETE POSITION 0
AS
begin
delete from TEST_TABLE where related_id=http://old.ID;
end
^
SET TERM ; ^
COMMIT;

INSERT INTO TEST_TABLE (ID,RELATED_ID) VALUES (1,NULL);
INSERT INTO TEST_TABLE (ID,RELATED_ID) VALUES (2,1);
COMMIT;

/* AND THIS WILL GIVE ERROR: */
DELETE FROM TEST_TABLE;

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 31, 2016

Commented by: @dyemanov

This issue is being worked on.

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Aug 31, 2016

Modified by: @dyemanov

assignee: Vlad Khorsun [ hvlad ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 8, 2016

Modified by: @dyemanov

Link: This issue relate to CORE5182 [ CORE5182 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 8, 2016

Modified by: @hvlad

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

resolution: Fixed [ 1 ]

Fix Version: 3.0.1 [ 10730 ]

Fix Version: 4.0 Alpha 1 [ 10731 ]

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 9, 2016

Modified by: @pavel-zotov

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

QA Status: No test => Done successfully

@firebird-issue-importer
Copy link
Author

firebird-issue-importer commented Sep 9, 2016

Modified by: @pavel-zotov

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

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

No branches or pull requests

2 participants