FirebirdSQL
Unable to drop / recreate table <T> until re-connect to DB if previous 'RECREATE TABLE <T>' statement failed
Consider following script:
set list on;
set count on;
set blob all;
set bail OFF;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
create table test(x int);
create index test_x on test(x);
recreate table test(id date primary key, pid boolean references test);
commit; ----- [ 1 ]
-- connect 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
drop index test_x;
drop table test;
recreate table test(x int);
This statement: recreate table test(id date primary key, pid boolean references test); - obviously will fail with
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-partner index segment no 1 has incompatible data type
But after this we will not able to drop existing index / table. An attempt to do that will fail with:
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "TEST_X" is in use
And this will be so until we re-connect to DB. Commit (marked as "[ 1 ]") does not help.
Checked on: WI-V3.0.14.33824; WI-V4.0.7.3231; WI-V5.0.4.1701; WI-T6.0.0.1244.
PS. Most likely a similar ticket already exists, but I couldn't find it
Checked just now. Result is the same:
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
create table test(x int);
create index test_x on test(x);
recreate table test(id date primary key, pid boolean references test);
commit; ------------ [ a ]
rollback; ---------- [ b ]
-- connect 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
drop index test_x;
drop table test;
recreate table test(x int);
Output:
drop index test_x;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "PUBLIC"."TEST_X" is in use
After line 12 in file idx-test2.sql
drop table test;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "PUBLIC"."TEST_X" is in use
After line 15 in file idx-test2.sql
recreate table test(x int);
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "PUBLIC"."TEST_X" is in use
After line 16 in file idx-test2.sql
(and same if i comment out commit; ----------- [ a ] and leave only rollback; ---------- [ b ])
And one more outcome, totally weird (as a result of my efforts to rework one of the tests :)). This is employee.fdb from FB 6.x (together with result of extracted metadata script): fb6x_employee_cannot_drop_table_test_at_all.zip
Unpack it and make connection using ISQL:
C:\FB\60SS\isql /:C:\FB\60SS\examples\empbuild\EMPLOYEE.FDB
Then try:
SQL> show table test;
Table: PUBLIC.TEST
X INTEGER Nullable
SQL> show index test;
PUBLIC.TEST2_X_Y_X INDEX ON TEST COMPUTED BY (x+1)
PUBLIC.TEST_X INDEX ON TEST(X)
PUBLIC.TEST_Y INDEX ON TEST(X)
PUBLIC.TEST_Z INDEX ON TEST(X)
So far - so good. But:
SQL> drop table test;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "PUBLIC"."TEST2_X_Y_X" is in use
SQL> drop index test2_x_y_x;
Statement failed, SQLSTATE = 42000
unsuccessful metadata update
-object INDEX "PUBLIC"."TEST2_X_Y_X" is in use
SQL> quit;
Unfortunately, i could not reproduce this using empty database and script like this:
set list on;
set count on;
set blob all;
set bail off;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
/* table: public.test, owner: sysdba */
create table public.test (x integer);
create index public.test2_x_y_x on test computed by (x+1);
create index public.test_x on test (x);
create index public.test_y on test (x);
create index public.test_z on test (x);
commit;
--connect 'localhost:r:\temp\tmp4test.fdb' user 'sysdba' password 'masterkey';
drop table test;
exit;
(it will work without any errors)
... although, maybe this will help (result of ```gfix -v -full``):
PZ Tue Aug 26 13:59:49 2025
Database: C:\FB\60SS\EXAMPLES\EMPBUILD\EMPLOYEE.FDB
Validation started
PZ Tue Aug 26 13:59:49 2025
bugcheck during scan of table 144 ("PUBLIC"."TEST")
PZ Tue Aug 26 13:59:49 2025
Database: C:\FB\60SS\EXAMPLES\EMPBUILD\EMPLOYEE.FDB
Validation aborted
invalid request BLR at offset 0
PZ Tue Aug 26 13:59:49 2025
Database: C:\FB\60SS\EXAMPLES\EMPBUILD\EMPLOYEE.FDB
internal Firebird consistency check (page in use during flush (210), file: cch.cpp line: 2752)
Did you tried rollback after failed commit ?
I found a case when rollback after commit can help, but i'm not sure whether this can relate to thi ticket or it is another problem.
Following script tries to create too much indices for same table and gets SQLSTATE = 54000 / ... / -cannot add index, index root page is full.
If we try after this to drop the table (WITHOUT rollback marked as "[ 1 ]") then error raises -deadlock / -read conflicts with concurrent update / -concurrent transaction number is ...:
set list on;
shell if exist r:\temp\tmp4test.fdb del r:\temp\tmp4test.fdb;
create database 'localhost:r:\temp\tmp4test.fdb' page_size 8192 user 'sysdba' password 'masterkey';
create table test(id int, h06 int);
commit;
set term ^;
execute block as
declare i int;
declare n int;
begin
i = 1;
n = power( 2, ( 8 + log(2, (select mon$page_size/8192 from mon$database)) ) ); -- page = 8k: 256; 16k: 512; 32k: 1024; ...
while (i <= n) do
begin
execute statement 'create index test_' || i || ' on test(h06)'
;
i = i + 1;
end
end
^
set term ;^
commit;
select t.max_number_of_created_indices, t.rdb$index_name as last_created_index_name
from (
select count(*)over() as max_number_of_created_indices, ri.*
from rdb$indices ri
where ri.rdb$index_name starts with upper('test_')
) t
order by cast(substring(t.rdb$index_name from 6) as int) desc rows 1
;
--rollback; ------------- [ 1 ]
drop table test;
quit;
Output (delay for <DeadlockTimeout> seconds will be when 'drop table' is executed):
Statement failed, SQLSTATE = 54000
unsuccessful metadata update
-cannot add index, index root page is full.
After line 23 in file idx-test4.sql
MAX_NUMBER_OF_CREATED_INDICES 256
LAST_CREATED_INDEX_NAME TEST_256
Statement failed, SQLSTATE = 40001
unsuccessful metadata update
-DROP TABLE "PUBLIC"."TEST" failed
-deadlock
-read conflicts with concurrent update
-concurrent transaction number is 5
After line 33 in file idx-test4.sql