Firebird 5 partial index creation causes server hang up

[tonne1]

Testing WI-T5.0.0.973 Firebid 5.0 Beta 1. Creation of partial index causes server to hang up or end in an endless loop. This happens intermittently, therefore the test case needs to be repeated a few times:

preparation:

CREATE TABLE TBL ( NUM integer );

now run the follwing two statements a few times if the error does not show immediately, 2, 3 times is usually enough

`set term ^;
execute block
as
declare i int;
begin
delete from tbl;
i = 0;
while (:i < 100000) do
begin
insert into tbl values (0);
i = i + 1;
end
execute statement 'CREATE INDEX TBL_P1 ON TBL (num) WHERE num > 0';
end^
set term ;^

DROP INDEX TBL_P1;`

This results in

Starting transaction...
Preparing statement:
execute block
as
declare i int;
declare cnt int;
begin
delete from tbl;
i = 0;
while (:i < 100000) do
begin
insert into tbl values (0);
i = i + 1;
end
execute statement 'CREATE INDEX TBL_P1 ON TBL (num) WHERE num > 0';
end
Statement prepared (elapsed time: 0.001s).
-- line 7, column 3
PLAN (TBL NATURAL)
Parameters: 0

Executing statement...
Statement executed (elapsed time: 6.450s).
2079896 fetches, 614241 marks, 713 reads, 112 writes.
100002 inserts, 1 updates, 100000 deletes, 15 index, 100001 seq.
Delta memory: 1273616 bytes.
RDB$INDEX_SEGMENTS: 1 inserts.
RDB$INDICES: 1 inserts. 1 updates.
TBL: 100000 inserts. 100000 deletes.
0 rows affected directly.
Total execution time: 6.481s
Script execution finished.
Committing transaction...
*** IBPP::SQLException ***
Context: Transaction::Commit

SQL Message : -902
Unsuccessful execution caused by a system error that precludes successful execution of subsequent statements

Engine Code : 335544726
Engine Message :
Error reading data from the connection.

Sometimes the test case causes the server to get stuck in an endless loop instead.
The error does not always show up, so until now I cannot provide a better test case which always produces the error. It does not matter if the test is done within the EB, this is just for convenience, The same error can be observed if the statements (delete, fill, index creation, index drop) are run separately and manually.

The error does not show up if the table is empty.

[hvlad]

I can't reprodice it so far.
I see no COMMIT's in the script but there is

Committing transaction...

message. I've tried with and without COMMITS after EXEC BLOCK and DROP INDEX - not reproduced.

Could you create test for isql (not FlameRobin), or provide exact steps to reproduce with FR ?

[hvlad]

I've reproduced bugcheck when run with COMMIT before DROP INDEX and no COMMIT after DROP INDEX.

internal Firebird consistency check (invalid SEND request (167), file: Statement.cpp line: 405)

Is it what you observe ? Check firebird.log for bugcheck message, please.

[tonne1]

I have only two occurrences of

internal Firebird consistency check (invalid SEND request (167), file: Statement.cpp line: 405)

in firebird.log, but I got the error at least 50 times during 60 tests. The database usually does not degrade because of the test, at least not visible in the logs. The usual outcome of the test is just "Error reading data from the connection", and I have to reconnect to continue testing.

You can run the statements (delete, bulk insert, create index, drop index) separately and commit each of them, that's how I did most of the tests. TX was configured as read commited and wait, I doubt that wait plays a role here, I'll check.

It looks like the database (page cache, index pages?) gets messed up gradually. The page size of the test DB is 16K, And it seems to be important that there are a lot of records in the table before the index gets created. Try to increase the loop count to 1 Mio or more. Also, the error always happens when committing the create index statement.

I try to come up with a test case for isql. Thanks for looking into this.

[tonne1]

Seems I cannot reproduce the error with isql, but there the index does not get created or I'm overlooking something.
The following should do. Please copy to a script file and adjust path and pwd:

---

connect 'D:\temp\testbox.fdb'
user 'sysdba' password ;

RECREATE TABLE TBL ( NUM integer );
commit;

set term ^;
execute block
as
declare i int;
begin
i = 0;
while (:i < 1000000) do
begin
insert into tbl values (0);
i = i + 1;
end
end^
set term ;^
commit;

CREATE INDEX TBL_P1 ON TBL (num) WHERE num > 0;
commit;

---

Then isql -q -i d:\temp\pi_query.sql

but there is no index. No error message, nothing in firebird.log.

[tonne1]

update: whether the partial index gets created with isql depends on the loop counter in the EB of the isql script. I found that with a loop count of

<= 953850 PI gets created
>= 953870 console hangs, endless loop, max cpu
>= 954000 PI is not created

A/B test: a regular index always gets created with this script, no matter what the loop counter is.

[hvlad]

Check next snapshot build, please.

[tonne1]

Tested with WI-T5.0.0.1080 Firebid 5.0 Beta 2: Works immaculate now ;-) Thanks Vlad!

[hvlad]

Thanks, but... current snapshot is 5.0.0.1050, not 1080 :)

And, unfortunately, patch was committed few minutes after build number was bumped, thus snapshot build of 5.0.0.1050 might not have this fix :( It depend on what exact commit was build.

Just checked

• snapshot build from github (https://github.com/FirebirdSQL/snapshots/releases/tag/snapshot-master) have no fix,
• snapshot build at our old placement (https://web.firebirdsql.org/downloads/snapshot_builds/win/5.0/) have this fix.

[tonne1]

Of course, sorry, it is 5.0.0.1050 ;-) And yes, I have used the github version.

The last build I was running was 1038, also from github, and I just verified that the partial index bug is still there with 1038. But with 1050 github-DL, partial index creation runs fine, passes all tests where I got errors before. It seems to contain some fix ;-)

Timestamps of firebird.exe build 1050:
github: 2023-05-16 07:00
old place: 2023-05-17 04:19