Database corruption after deleting/selecting from uncommitted global temporary table from an uncommitted procedure in Firebird 3, 4 and 5

[qweqq]

Hello,

When deleting from an uncommitted global temporary table from an uncommitted procedure the database gets corrupted and cannot be opened again - we manage to reproduce this every time on 64bit Firebird 3.0.13, Firebird 4.0.6 and Firebird 5.0.3 on Windows Server 2019 and on 64bit Firebird 5.0.3 on Linux via the following isql script against the sample EMPLOYEE.FDB database:

set autoddl OFF;

create global temporary table GTTTABLE1 (
  COL1 varchar (100)
) on commit preserve rows;

set term ^ ;

create or alter procedure PROCEDURE1 ()
as
begin
  delete from GTTTABLE1;
end^

set term ; ^

execute procedure PROCEDURE1;

commit;
quit;

After running the script there are no errors but when trying to connect again to the database the following error is shown.

Statement failed, SQLSTATE = XX001
database file appears corrupt (EMPLOYEE.FDB)
-wrong page type
-page 2 is of wrong type (expected pointer, found SCN inventory)

Regards, Hristo Stefanov

[qweqq]

Another way to reproduce the issue using select instead of delete:

set autoddl OFF;

create global temporary table GTTTABLE1 (
  COL1 varchar (100)
) on commit preserve rows;

set term ^ ;

create or alter procedure PROCEDURE1 ()
returns (sdf bigint)
as
begin
  select count(*) from GTTTABLE1 into :sdf;
  suspend;
end^

set term ; ^

select * from PROCEDURE1;

commit;
quit;

[hvlad]

DML after DDL is not supported. I'll look for database corruption reason, thought. In any case - don't do it, never, It is not supported and could be dangerous.

[qweqq]

Thanks, we are aware it is not supported - the problem was found during regular manual work from an SQL IDE while experimenting and forgetting to commit but usually we get messages about tables not existing when we have not committed the DDL statement - losing the database was not what we imagined could happen - fortunately it was a copy of a production database and nothing of value was lost.

[nLeonardt95]

DML after DDL is not supported. I'll look for database corruption reason, thought. In any case - don't do it, never, It is not supported and could be dangerous.

This fits my problem, see #8802. When you say "DML after DDL is not supported". Why does Firebird allow this at all? Shouldn't there be a safety feature in place so that a DML after DDL results in an error such as “DML after DLL is not allowed/supported”?

And where is it documented that “DML after DDL is not supported”?

[EPluribusUnum]

@nLeonardt95 , not explicitly documented, but

https://firebirdsql.org/file/documentation/html/en/refdocs/fblangref50/firebird-50-language-reference.html#fblangref50-ddl "When a DDL statement is committed, the metadata for the object are created, altered or deleted."

[nLeonardt95]

The statement only explains to me what a DDL statement does. However, I don't see any connection to DML statement.

It's okay if that doesn't work for whatever reason. I would just like to have it confirmed by a reliable source. Hvlad's comment sounds as if it's clear that “DML after DDL is not supported.” It wasn't clear to me before.

I can take this into account in the future and do a RECONNECT after DDL statements before executing a DML statement. For the future, however, I would like to see that such a mixed statement does not simply run through but leads to an error. Because such a command unexpectedly breaks my database.

[aafemt]

Hvlad's comment sounds as if it's clear that “DML after DDL is not supported.”

Usually it has addition "in the same transaction" but you have found a weird case when this is not enough. Most likely this issue is addressed by currently developed shared metadata cache.

[nLeonardt95]

Could someone check my case with the adjustment/extension? I would be delighted if this could be fixed in Firebird 6.

Or are parts of the development already included in the Firebird 6 prerelease? Then I can test it myself.

[AlexPeshkoff]

Unfortunately GTT (specially on commit preserve) is the last feature still no supported by shared cache. Will use your test case when checking it.

[hvlad]

When you say "DML after DDL is not supported".

In the same transaction, of course.

Why does Firebird allow this at all? Shouldn't there be a safety feature in place so that a DML after DDL results in an error such as “DML after DLL is not allowed/supported”?

This behavior is inherited from IB codebase and hard to change.

And where is it documented that “DML after DDL is not supported”?

Not exactly in this words (while it is very well known fact) - you may look at chapter "Altering and dropping procedures in use" in IB6 "Data Definition Guide". It explains some internals. It is not 100% correct, but more that nothing.