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

Jump to bottom

SIMILAR-TO leads FB to crash when checking blob with size > 2Gb for matching to string literal [CORE4893] #5186

Closed
firebird-automations opened this issue Jul 30, 2015 · 8 comments
Closed

SIMILAR-TO leads FB to crash when checking blob with size > 2Gb for matching to string literal [CORE4893] #5186

firebird-automations opened this issue Jul 30, 2015 · 8 comments
Assignees
@asfernandes
Labels
affect-version: 3.0 RC1 component: engine fix-version: 4.0 Beta 2 priority: major qa: cannot be tested type: bug

Comments

@firebird-automations
Copy link
Collaborator

Submitted by: @pavel-zotov

Doing on LI-V3.0.0.31942 SuperClassic, database with FW = OFF, FB config:

Servermode = SuperClassic
RemoteServicePort = 3330
DefaultDbCachePages = 512

BugCheckAbort=1

AuthClient = Legacy_Auth,Srp,Win_Sspi
AuthServer = Legacy_Auth,Srp
UserManager = Legacy_UserManager
WireCrypt = Disabled

ExternalFileAccess = Restrict /var/db/fb30
FileSystemCacheThreshold = 65536K

LockMemSize = 64M
LockHashSlots = 22111

MaxUserTraceLogSize = 99999
TempCacheLimit = 2147483647
#⁠TempDirectories = /dev/shm;/tmp
TempDirectories = /tmp/firebird

Test:
#⁠#⁠#⁠#⁠

recreate global temporary table gtt(b blob);
recreate sequence g;
commit;
insert into gtt(b) select list( gen_id( g, 1 ) )
from rdb$types,rdb$types,rdb$types,(select 1 k from rdb$types rows 15);
select char_length(b) from gtt;

      CHAR\_LENGTH

=====================
2346948497

SQL> select gen_id(g,0) from rdb$database;

           GEN\_ID

=====================
245805960

SQL> select 1 from gtt where b like '%,245805959,%';

CONSTANT

============
1

SQL> select 1 from gtt where b similar to '%,2,%'; ----------- worked VERY slowly, was interrupted by Ctrl-C
^C
CONSTANT

Statement failed, SQLSTATE = HY008
operation was cancelled
SQL> select 1 from gtt where b similar to '%,2,%'; ---------- was NOT interrupted, but after ~2 minutes produced:

CONSTANT

============
Statement failed, SQLSTATE = 08006
Error reading data from the connection.

Result is reproduced also if cancel last query by Ctrl-C -- see two attached files with stack-trace.

Commits: 28e1874

====== Test Details ======

Test can not be implemented because of huge size of data (more than 2 Gb).
I've cheched on WI-T4.0.0.1598 (08-sep-2019) and got following results:

select 1 from gtt where b like '%,245805959,%';

       1

Current memory = 182333584
Delta memory = 3520
Max memory = 182415760
Elapsed time = 36.354 sec
Buffers = 20000
Reads = 408126
Writes = 19886
Fetches = 815853

select 2 from gtt where b similar to '%,2,%'; -- worked VERY slowly, was interrupted by Ctrl-C

       2

Current memory = 4477299296
Delta memory = 4294965712
Max memory = 6624792864
Elapsed time = 51.449 sec
Buffers = 20000
Reads = 607589
Writes = 0
Fetches = 1214585

select 3 from gtt where b similar to '%,2,%'; -- was NOT interrupted, but after ~2 minutes produced: Error reading data from the connection.

       3

Current memory = 4477299296
Delta memory = 0
Max memory = 6624792864
Elapsed time = 69.458 sec
Buffers = 20000
Reads = 607589
Writes = 0
Fetches = 1214585

So, the problem was gone.
@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Link to .zip with stack-traces: https://yadi.sk/d/vY0KnR4LiBP4c

0xFF. Last time something wrong occurs with ability to attach files: I permanently get error from JIRA.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

I'd say that built-in functions are not always expected to work with blobs > 2GB. That said, the server surely should not crash but report an error.

@firebird-automations
Copy link
Collaborator Author

Commented by: @dyemanov

Did you check against v2.5? I doubt this is a v3 regression.

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

Something weird one may to see in FB 2.5.5.
Script (run this on new empty database):

===
set bail on;
set term ^;
execute block as
begin
execute statement 'drop sequence g';
when any do begin end
end
^
set term ;^
commit;
create sequence g;
recreate global temporary table gtt(b blob);
commit;
set bail off;

set list on;
select current_timestamp as dts_beg from rdb$database;
set echo on;
set stat on;
insert into gtt(b) select list( gen_id( g, 1 ) ) from rdb$types,rdb$types,rdb$types,(select 1 k from rdb$types rows 20);
set stat off;
select current_timestamp as dts_end from rdb$database;

Its STDOUT & STDERR will be:

$ /opt/fb25sc/bin/isql /3255:/var/db/fb25/e25.fdb -i qq.sql

DTS_BEG 2015-12-18 15:06:05.6770

set stat on;
insert into gtt(b) select list( gen_id( g, 1 ) ) from rdb$types,rdb$types,rdb$types,(select 1 k from rdb$types rows 20);
Statement failed, SQLSTATE = 08001
I/O error during "open O_CREAT" operation for file ""
-Error while trying to create file
-No such file or directory
After line 19 in file qq.sql
Current memory = 10311184
Delta memory = 274920
Max memory = 10323632
Elapsed time= 242.37 sec
Cpu = 0.00 sec
Buffers = 2048
Reads = 802
Writes = 787696
Fetches = 727872092
set stat off;
select current_timestamp as dts_end from rdb$database;

DTS_END 2015-12-18 15:10:08.0430

Firstly I've thought that there is no space for GTT data - but:

$ env|grep -i "firebird\|tmp\|temp"
MC_TMPDIR=/tmp/mc-root
FIREBIRD_TMP=/tmp/firebird

$ df -h
Filesystem Size Used Avail Use% Mounted on
/dev/sda4 243G 49G 182G 22% /
tmpfs 16G 0 16G 0% /dev/shm
/dev/sda2 194M 50M 135M 28% /boot
/dev/sda1 200M 260K 200M 1% /boot/efi
/dev/sdb1 275G 199G 63G 77% /u01
192.168.0.61:/u01/u4backupset/RACDB
1.3T 797G 378G 68% /u4bkp_RACDB

So, 182 G - it should be enough for storing such blob (IMO).

Database has size about 3 Gb when this fault has occured:

$ ls -l /var/db/fb25/e25.fdb
-rw-r----- 1 firebird firebird 3277156352 Dec 18 15:17 /var/db/fb25/e25.fdb

Trace:

2015-12-18T15:10:08.0420 (551:0x7f819f2357a0) FAILED EXECUTE_STATEMENT_FINISH
/var/db/fb25/e25.fdb (ATT_3, SYSDBA:NONE, NONE, TCPv4:192.168.0.220)
/opt/fb25sc/bin/isql:549
(TRA_10, CONCURRENCY | WAIT | READ_WRITE)

Statement 87:
-------------------------------------------------------------------------------
insert into gtt(b) select list( gen_id( g, 1 ) ) from rdb$types,rdb$types,rdb$types,(select 1 k from rdb$types rows 20)
0 records fetched
242364 ms, 800 read(s), 787696 write(s), 727871981 fetch(es), 240202496 mark(s)

Table Natural Index Update Insert Delete Backout Purge Expunge
***************************************************************************************************************
RDB$TYPES 238091300

2015-12-18T15:10:08.0420 (551:0x7f819f2357a0) ERROR AT jrd8_execute
/var/db/fb25/e25.fdb (ATT_3, SYSDBA:NONE, NONE, TCPv4:192.168.0.220)
/opt/fb25sc/bin/isql:549
335544344 : I/O error during "open O_CREAT" operation for file ""
335544733 : Error while trying to create file
2 : No such file or directory

PS.
ISQL Version: LI-V2.5.6.26958 Firebird 2.5
Server version:
LI-V2.5.6.26958 Firebird 2.5
LI-V2.5.6.26958 Firebird 2.5/tcp (oel64)/P12
LI-V2.5.6.26958 Firebird 2.5/tcp (oel64)/P12

@firebird-automations
Copy link
Collaborator Author

Commented by: @pavel-zotov

PPS. Sorry, I forgot to show my firebird.conf:

$ cat firebird.conf

RemoteServicePort = 3255

DefaultDbCachePages = 2048
LockHashSlots = 22111

ExternalFileAccess = Restrict /var/db/fb25
TempDirectories = /dev/shm;/tmp
MaxUserTraceLogSize = 99999
FileSystemCacheThreshold = 1000000
TempCacheLimit = 2147483647

NB:
TempDirectories = /dev/shm;/tmp -- here '/dev/shm' is at the start of list. This device has capacity = 16 Gb.
But (AFAIK) GTT data are stored exactly in the folder which is defined by env. FIREBIRD_TMP=/tmp/firebird -- am I right ?

@firebird-automations
Copy link
Collaborator Author

Modified by: @asfernandes

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

resolution: Fixed [ 1 ]

Fix Version: 4.0 Beta 2 [ 10888 ]

assignee: Adriano dos Santos Fernandes [ asfernandes ]

@firebird-automations
Copy link
Collaborator Author

Modified by: @pavel-zotov

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

QA Status: No test => Cannot be tested

Test Details: Test can not be implemented because of huge size of data (more than 2 Gb).
I've cheched on WI-T4.0.0.1598 (08-sep-2019) and got following results:

select 1 from gtt where b like '%,245805959,%';

       1

Current memory = 182333584
Delta memory = 3520
Max memory = 182415760
Elapsed time = 36.354 sec
Buffers = 20000
Reads = 408126
Writes = 19886
Fetches = 815853

select 2 from gtt where b similar to '%,2,%'; -- worked VERY slowly, was interrupted by Ctrl-C

       2

Current memory = 4477299296
Delta memory = 4294965712
Max memory = 6624792864
Elapsed time = 51.449 sec
Buffers = 20000
Reads = 607589
Writes = 0
Fetches = 1214585

select 3 from gtt where b similar to '%,2,%'; -- was NOT interrupted, but after ~2 minutes produced: Error reading data from the connection.

       3

Current memory = 4477299296
Delta memory = 0
Max memory = 6624792864
Elapsed time = 69.458 sec
Buffers = 20000
Reads = 607589
Writes = 0
Fetches = 1214585

So, the problem was gone.

@firebird-automations
Copy link
Collaborator Author

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
Assignees

@asfernandes asfernandes

Labels
affect-version: 3.0 RC1 component: engine fix-version: 4.0 Beta 2 priority: major qa: cannot be tested type: bug
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@asfernandes @firebird-automations