FB bugchecks (index inconsistent) attempting to create index with key length > 3375 bytes (charset = NONE) when database page_size = 16384 [CORE4416]

[firebird-automations]

Submitted by: @pavel-zotov

Attachments: gdb-firebird.when-create-index-with-long-key.zip

Votes: 1

Script ('longkeys.sql'):

create database '192.168.0.220/3333:/var/db/fb30/tmpidxtest.fdb' page_size 16384; commit; connect '192.168.0.220/3333:/var/db/fb30/tmpidxtest.fdb';

recreate table t(id int primary key, s04 varchar(3375)); -- ok:3000; 3250; fails: 3500; 3375; commit; recreate sequence g; commit; create index t_s04 on t(s04); commit;

show version;

select current_timestamp from rdb$database; set stat on; insert into t select i, left(s, 3375) from (select gen_id(g,1) i, rpad('', 4000, uuid_to_char(gen_uuid())) s from rdb$types, rdb$types,(select 1 i from rdb$types rows 10)); set stat off; select current_timestamp from rdb$database; set stat on; set echo on; commit;

Test:

isql -i longkeys.sql 2>longkeys.err

Result:

⁠#⁠#⁠#⁠#⁠#⁠

1. Console:

ISQL Version: WI-V2.5.3.26730 Firebird 2.5 Server version: Firebird/linux AMD64 (access method), version "LI-T3.0.0.31082 Firebird 3.0 Alpha 2" Firebird/linux AMD64 (remote server), version "LI-T3.0.0.31082 Firebird 3.0 Alpha 2/tcp (oel64)/P12" Firebird/x86/Windows NT (remote interface), version "WI-V2.5.3.26730 Firebird 2.5/tcp (csprog)/P12" on disk structure version 12.0

    CURRENT\_TIMESTAMP

========================= 2014-05-02 14:13:18.0310

Current memory = 5332562684358320 Delta memory = 5332553788549464 Max memory = 5332476777132512 Elapsed time= 218.33 sec Buffers = 268543402 Reads = 39125124850654756 Writes 1152940647276085121 Fetches = 39180031719488332

2. File longkeys.err:

Statement failed, SQLSTATE = 08006 Error reading data from the connection. After line 16 in file longkeys.sql Statement failed, SQLSTATE = 08006 Unable to complete network request to host "192.168.0.220". -Error writing data to the connection. After line 18 in file longkeys.sql Statement failed, SQLSTATE = 08006 Unable to complete network request to host "192.168.0.220". -Error writing data to the connection. After line 21 in file longkeys.sql Statement failed, SQLSTATE = 08006 Unable to complete network request to host "192.168.0.220". -Error writing data to the connection. After line 22 in file longkeys.sql Statement failed, SQLSTATE = 08006 Unable to complete network request to host "192.168.0.220". -Error writing data to the connection. After line 22 in file longkeys.sql

Stacktrace see in attach.

[firebird-automations]

Modified by: @pavel-zotov

Attachment: gdb\-firebird\.when\-create\-index\-with\-long\-key\.zip \[ 12510 \]

[firebird-automations]

Commented by: @dyemanov

If you'd look at firebird.log, I'd see that it's a bugcheck, not the crash.

[firebird-automations]

Modified by: @dyemanov

summary: FB craches when attempt to create index with key length \> 3375 bytes \(charset = NONE\) when database page\_size = 16384 =\> FB bugchecks \(index inconsistent\) attempting to create index with key length \> 3375 bytes \(charset = NONE\) when database page\_size = 16384

[firebird-automations]

Commented by: @pavel-zotov

In firebird.log I see these lines:

oel64 Fri May 2 15:45:58 2014 Database: /var/db/fb30/tmpidxtest.fdb internal Firebird consistency check (index inconsistent (204), file: btr.cpp line: 1083)

oel64 Fri May 2 15:49:27 2014 /opt/fb30trnk/bin/fbguard: /opt/fb30trnk/bin/firebird terminated abnormally (-1)

Still can`t understand: if such errors are NOT "real" crashes but rather fall into category of "internal error or BUGCHECK" then... do we ever should set BugCheckAbort value to 1 ? If such reports are useless may be I have to turn off this BCA untill someone of FB-developers explicitly ask to set it to 1 ?

PS. Threshold length of key is 3259 (it is the max value when index can be created). The same is on 2.5.3

[firebird-automations]

Commented by: @dyemanov

Such reports are NOT useless, as bugchecks are unexpected problems that should never appear, so it makes all the sense to report them. I'm just about naming the issue properly. There's a big difference between real crash and artificial crash. Regular users (BugcheckAbort = 0 by default) would not see a crash, they would see an error instead.

[firebird-automations]

Commented by: @pavel-zotov

When BCA = 0, error message is sent to client, but result is the same: can`t create index at all:

Statement failed, SQLSTATE = XX000 internal Firebird consistency check (index inconsistent (204), file: btr.cpp line: 1083) After line 24 in file longkeys30.sql Statement failed, SQLSTATE = XX000 internal Firebird consistency check (can't continue after bugcheck) After line 26 in file longkeys30.sql

Key length in example is less than 25% of database page_size. It seems to me that there was no such problem in 2.5.2. Smth is broken ?

[firebird-automations]

Commented by: @dyemanov

Yes, it looks like a regression.

[firebird-automations]

Modified by: @dyemanov

Regression: 3\.0 Alpha 2 \[ 10560 \]

[firebird-automations]

Commented by: @dyemanov

Some observations:

1) Index can be created successfully, it contains 645K nodes, average length 3379 bytes (keys are uncompressable due to GEN_UUID, gstat reports compression ratio of 1.0). However, the index tree depth is 12! I believe this is because of the very wide keys.

2) What actually fails is insertion into the index. In this case, the index tree depth becomes 16 which is the hardcoded limit, hence the bugcheck. I suppose this is because of the space reservation on buckets which probably does not happen when the index is created from scratch, so the index becomes bigger in this case.

My personal conclusions:

* This is unlikely to be a regression, v2.5.2 should behave the same way. I cannot confirm it right now though.

* This is not a bug per se. Such deep index trees are too slow in operation to be practically useful, so I'd avoid raising the limit. Of course, the engine could be more polite and report an proper error instead of the bugcheck and this can be improved.

* It would be interesting to know, what would be generally faster - such a deep index with wide keys or a much shorter index with substring-based keys but followed by additional record retrievals to reject false positive matches.

[firebird-automations]

Modified by: @dyemanov

Regression: 3\.0 Alpha 2 \[ 10560 \] =\>

[firebird-automations]

Commented by: @pavel-zotov

> It would be interesting to know, what would be generally faster - such a deep index with wide keys > or a much shorter index with substring-based keys but followed by additional record retrievals > to reject false positive matches.

1. Which DDL should be used for such test - ? Or one may use this: recreate table t(id int primary key, s_base varchar(2048) ); - and create index based on expression like left( s_base, NN ) ?

2. Should such table have 'static' data or it has to be changed by concurrent attaches ?

[firebird-automations]

Commented by: @dyemanov

Pavel, let's continue via e-mail, details are off-topic here.

[firebird-automations]

Modified by: @dyemanov

Version: 2\.5\.3 \[ 10461 \]