Open EPluribusUnum opened 1 year ago
Send it to me, pls
@hvlad , email sent.
Downloaded, thanks. Investigating. If you could produce a test case - it would help a lot.
@hvlad , database uploaded.
Run this 4 SQL parallel in 4 connection.
execute procedure rep_fk_tabla(100044372, 'ABC', 4, 0) execute procedure rep_fk_tabla(100044372, 'ABC', 4, 1) execute procedure rep_fk_tabla(100044372, 'ABC', 4, 2) execute procedure rep_fk_tabla(100044372, 'ABC', 4, 3)
I run these queries in parallel many times (on the fresh copy of DB every time) and get no crash. Results are pretty consistent - one query succeeds, three other fails with
Statement failed, SQLSTATE = 42000 Dynamic SQL Error -SQL error code = -104 -Unexpected end of command - line 1, column 1 -At block line: 38, col: 9 -At procedure 'REP_FK_TABLA' line: 87, col: 3
It happens because of attempt to run empty query using EXECUTE STATEMENT.
BTW, it requires UDF library UDF3s.dll and I found some but it could be outdated (29.05.2012)
@hvlad , I uploaded
run the command on snapshot
No luck :( Now all 4 statements run successfully for ~320 sec each, one of them returns
TRAN_START | SQL_SELECT | SQL_DELETE | WAIT_AND_REPEAT |
---|---|---|---|
28-FEB-2023 | <null> |
<null> |
<null> |
while other 3 have value 1000
in WAIT_AND_REPEAT
column
Now I I get a crash for a single SQL command. Uploaded a new database, the complete Firebird directory (v3.0.10.33601) and a new crash dump.
execute procedure rep_fk_tabla(100046633, 'LIBRA3S')
Looks like the issue is around exception handling.
In FK_TABLA_CALC_CELL_ALLOC function when I add an EXISTS precheck, no crash happens.
CREATE OR ALTER FUNCTION fk_tabla_calc_cell_alloc ( rpt_report_job_id xidn, cell_addr TYPE OF COLUMN fk_tabla_calc.cell_addr, thread_index TYPE OF COLUMN fk_tabla_calc.thread_index) RETURNS BOOLEAN AS BEGIN --IF (EXISTS(SELECT * FROM fk_tabla_calc c WHERE c.rpt_report_job_id = :rpt_report_job_id AND c.cell_addr = :cell_addr)) --THEN RETURN FALSE; BEGIN INSERT INTO fk_tabla_calc (rpt_report_job_id, cell_addr, thread_index) VALUES (:rpt_report_job_id, :cell_addr, :thread_index); RETURN TRUE; WHEN GDSCODE unique_key_violation DO BEGIN RETURN FALSE; END END END
Note: the EXISTS check workaround is only usabe when I run the rep_fk_tabla procedure in a single thread. When I run it multiple times in parallel on snapshot transactions the EXISTS check does not help, the only soution is the exception handling.
What if you change the exception handler to just set some return flag and do RETURN RES;
in the end of the function?
Reproduced, thanks.
@dyemanov yes, it helps, no crash
Confirmed, returning outside the exception block is a good workaround to avoid the crash.
Simplified test case with employee.fdb
create package die as begin function is_integer(s varchar(10)) returns boolean; end
create package body die as begin function is_integer(s varchar(10)) returns boolean as declare variable i integer; begin begin i = cast(s as integer); when any do begin return false; end end return true; end end
create or alter procedure GET_PROJECT_ID returns ( ID type of column PROJECT.PROJ_ID) as begin id = null; if (die.is_integer('1A1')) then begin id = 'INT'; end suspend; end
create or alter procedure INSERT_PROJECT as declare variable id type of column project.proj_id; begin select id from get_project_id into :id;
insert into PROJECT (PROJ_ID, PROJ_NAME, PRODUCT) values (:ID, 'NAME', 'SIMPLE'); end
FB 3.0.11: connection dies Unsuccessful execution caused by a system error.... Error reading data from the connection.
SQLCODE: -625 SQLSTATE: 23000 GDSCODE: 335544347
Hi *!
" Access violation. The code attempted to access a virtual address without privilege to do so. This exception will cause the Firebird server to terminate abnormally."
The dump was created with Firebird-3.0.11.33665-0_x64.zip
I'll send FTP details of the dump in PM.