Subject DROP table statement hangs Firebird Server process with 100% CPU usage
Author
Post date 2018-06-15T13:05:13Z
I have also posted my problem on StackOverflow here:  https://stackoverflow.com/questions/50873708/
Also sent this to the mailing list, but I'm not sure how that works or if the mail actually went through.

I wanted to drop a big table in a big database (24GB). After issuing the DROP statement, the firebird server is using 100% CPU (for 15 hours now) and there is no disk I/O activity at all. Memory doesn't seem to be changing much either.

I can't seem to trace anything by attaching to the process, I only get:

> strace -p [fbserver PID]
strace: Process [fbserver PID] attached
futex(0x7f80e769c8b0, FUTEX_WAIT_PRIVATE, 2, NULL
This is firebird 2.5 super-server (firebird2.5-super/xenial,now 2.5.6.27020.ds4-2ubuntu1 amd64 [installed] ), running on an Ubuntu 14.04 server.

The table being dropped had only one foreign key to another fairly big table. Here is its DDL:

CREATE TABLE CONTEXTS (
POST_ID                 BIGINT,
REMOTE_ID           BIGINT UNIQUE,
TTIMESTAMP         BIGINT,
LATEST_CHECK    BIGINT,
CONTEXT               VARCHAR(32765),
PRIMARY KEY (POST_ID),
FOREIGN KEY (POST_ID) REFERENCES POSTS (POST_ID)

I can't connect to the server anymore by any means, even with isql-fb:
SQL> connect "/home/firebird/my_DB.fdb " user 'theadmin' password 'thepasswd';
Statement failed, SQLSTATE = 08004
connection rejected by remote interface

Tried to run gstat, but it can't connect:

> fbstat myDB -USER 'theadmin' -PAS 'thepasswd'

Database "/home/firebird/myDB.fdb"
Database header page information:
        Flags                   0
        Checksum                12345
        Generation              1660566
        Page size               4096
        ODS version             11.2
        Oldest transaction      1659374
        Oldest active           1659375
        Oldest snapshot         1659375
        Next transaction        1660356
        Bumped transaction      1
        Sequence number         0
        Next attachment ID      3998
        Implementation ID       24
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Apr 30, 2018 11:50:10
        Attributes              force write

    Variable header data:
        *END*

Database file sequence:
File /home/firebird/myDB.fdb is the only file
connection rejected by remote interface

The sweep interval is the default one (20000 if I recall correctly).

This doesn't look normal. Should I kill the Firebird server while it's supposedly dropping my table? Will this corrupt my database? Should I keep waiting and hope that it finishes, or is it stuck in a recursive loop of some kind and not doing anything? 
The database was being modified when the DROP statement was issued, but not that table directly. Do we have to close all connections to the DB prior to dropping a table even if it's not being modified?

Thanks for your time.