Query to mon$ tables does not return data when the encryption/decryption thread is running

[pavel-zotov]

Steps to reproduce:

• create table test(s varchar(1000) unique);

• add data:

insert into test(s) select lpad('', 1000, uuid_to_char(gen_uuid())) from rdb$types, rdb$types;
insert into test(s) select lpad('', 1000, uuid_to_char(gen_uuid())) from rdb$types, rdb$types;
commit;

• apply script:

set bail on;
set list on;
set autoddl off;
commit;

set term ^;
execute block returns(alter_db_sttm varchar(128)) as
    declare c int;
begin
    select m.mon$crypt_state from mon$database m into c;
    if ( c = 0 or c = 1 ) then
        begin
            alter_db_sttm = 'Run statement: ';
            suspend;
            alter_db_sttm = 'alter database ' || trim( iif(c = 0, 'encrypt with "DbCrypt" key Red', 'decrypt') );
            execute statement ( alter_db_sttm );
            suspend;
        end
    else
        begin
            alter_db_sttm = '### WAIT AND REPEAT LATER ### Process of ' || iif(c = 2, 'encryption', 'decryption') || ' NOT YET completed.';
            suspend;
            exception;
        end
end
^
set term ;^

commit;

select current_time from rdb$database;

set stat on;
set echo on;

    select
         m.mon$database_name as db_name
        ,a.mon$attachment_id
        ,a.mon$remote_protocol
        ,a.mon$remote_address
    from mon$attachments a
    join mon$database m on 1=1
    where a.mon$attachment_id = current_connection
;
set echo off;
set stat off;
select current_time from rdb$database;
show version;

Query to monitoring will not return data for some noticeable delay depending on host. On my machine this delay is about 40s, but in some cases (rarely) it can reduce down to 6-7 s.

This is example of (typical) output tail:

. . .
14:44:16.990     from mon$attachments a
14:44:16.990     join mon$database m on 1=1
14:44:16.990     where a.mon$attachment_id = current_connection
14:44:16.990 ;
14:44:57.896
14:44:57.896 DB_NAME                         D:\FB\fb40\examples\empbuild\employee.fdb
14:44:57.896 MON$ATTACHMENT_ID               966
14:44:57.896 MON$REMOTE_PROTOCOL             TCPv6
14:44:57.896 MON$REMOTE_ADDRESS              fe80::9086:5fd:de37:84d8%7/61685
14:44:57.896 
14:44:57.896 
14:44:57.896 Current memory = 91631024
14:44:57.896 Delta memory = -105792
14:44:57.896 Max memory = 92411648
14:44:57.896 Elapsed time = 40.908 sec
14:44:57.896 Buffers = 10240
14:44:57.896 Reads = 76189
14:44:57.896 Writes = 76198
14:44:57.896 Fetches = 77853
14:44:57.896 set echo off;
14:44:57.896 
14:44:57.896 CURRENT_TIME                    14:44:57.0000 Europe/Moscow

PS
Checked on 4.0.1.2580.
Demo encryption plugin is used in the test script.
Its description and URL to download can be found here:
https://ib-aid.com/download-demo-firebird-encryption-plugin

[pavel-zotov]

QA: test file had wrong prefix ("core_"), renamed to gh_6947_test.py

[pavel-zotov]

@@@ QA-issue @@@
See test for #6298 (Provide ability to see current state of DB encryption [CORE6048]).
Test for this ticket not needed and is marked as to be skipped.