[FB4] internal Firebird consistency check (missing pointer page in DPM_data_pages (243), file: dpm.cpp line: 780)
dmitry-lipetsk opened this issue · 6 comments
dmitry-lipetsk commented
I get this error when I execute the following SQL with database from attached archive:
select * from ddl_test_table_24728
pavel-zotov commented
J4I. Attempt to applying metadata (extracted using 'isql -x') from this DB leads to:
set bail on;
set echo on;
--SET SQL DIALECT 1;
--set names utf8;
shell if exist e:\temp\tmp4test.fdb del /q /f e:\temp\tmp4test.fdb;
create database 'localhost:e:\temp\tmp4test.fdb' page_size 16384 user sysdba password 'masterkey';
-- ...
CREATE DOMAIN TA_DEC_16_15_D1_1_2_NN AS NUMERIC(15, 15)[2] NOT NULL;
CREATE DOMAIN TA_DEC_16_16_D1_1_2 AS NUMERIC(15, 16)[2];
Statement failed, SQLSTATE = HY104
Dynamic SQL Error
-SQL error code = -842
-Scale must be between zero and precision
-At line 1, column 50
dmitry-lipetsk commented
Extractor works incorrectly.
Original source code of this DB (1 dialect) contains the following SQL
/* .... */
CREATE DOMAIN T_DECIMAL_16_0 AS DECIMAL(16,0); CREATE DOMAIN TA_DEC_16_0_D1_1_2 AS DECIMAL(16,0)[1:2];
CREATE DOMAIN T_DECIMAL_16_1 AS DECIMAL(16,1); CREATE DOMAIN TA_DEC_16_1_D1_1_2 AS DECIMAL(16,1)[1:2];
CREATE DOMAIN T_DECIMAL_16_2 AS DECIMAL(16,2); CREATE DOMAIN TA_DEC_16_2_D1_1_2 AS DECIMAL(16,2)[1:2];
CREATE DOMAIN T_DECIMAL_16_3 AS DECIMAL(16,3); CREATE DOMAIN TA_DEC_16_3_D1_1_2 AS DECIMAL(16,3)[1:2];
CREATE DOMAIN T_DECIMAL_16_4 AS DECIMAL(16,4); CREATE DOMAIN TA_DEC_16_4_D1_1_2 AS DECIMAL(16,4)[1:2];
CREATE DOMAIN T_DECIMAL_16_5 AS DECIMAL(16,5); CREATE DOMAIN TA_DEC_16_5_D1_1_2 AS DECIMAL(16,5)[1:2];
CREATE DOMAIN T_DECIMAL_16_6 AS DECIMAL(16,6); CREATE DOMAIN TA_DEC_16_6_D1_1_2 AS DECIMAL(16,6)[1:2];
CREATE DOMAIN T_DECIMAL_16_7 AS DECIMAL(16,7); CREATE DOMAIN TA_DEC_16_7_D1_1_2 AS DECIMAL(16,7)[1:2];
CREATE DOMAIN T_DECIMAL_16_8 AS DECIMAL(16,8); CREATE DOMAIN TA_DEC_16_8_D1_1_2 AS DECIMAL(16,8)[1:2];
CREATE DOMAIN T_DECIMAL_16_9 AS DECIMAL(16,9); CREATE DOMAIN TA_DEC_16_9_D1_1_2 AS DECIMAL(16,9)[1:2];
CREATE DOMAIN T_DECIMAL_16_10 AS DECIMAL(16,10); CREATE DOMAIN TA_DEC_16_10_D1_1_2 AS DECIMAL(16,10)[1:2];
CREATE DOMAIN T_DECIMAL_16_11 AS DECIMAL(16,11); CREATE DOMAIN TA_DEC_16_11_D1_1_2 AS DECIMAL(16,11)[1:2];
CREATE DOMAIN T_DECIMAL_16_12 AS DECIMAL(16,12); CREATE DOMAIN TA_DEC_16_12_D1_1_2 AS DECIMAL(16,12)[1:2];
CREATE DOMAIN T_DECIMAL_16_13 AS DECIMAL(16,13); CREATE DOMAIN TA_DEC_16_13_D1_1_2 AS DECIMAL(16,13)[1:2];
CREATE DOMAIN T_DECIMAL_16_14 AS DECIMAL(16,14); CREATE DOMAIN TA_DEC_16_14_D1_1_2 AS DECIMAL(16,14)[1:2];
CREATE DOMAIN T_DECIMAL_16_15 AS DECIMAL(16,15); CREATE DOMAIN TA_DEC_16_15_D1_1_2 AS DECIMAL(16,15)[1:2];
CREATE DOMAIN T_DECIMAL_16_16 AS DECIMAL(16,16); CREATE DOMAIN TA_DEC_16_16_D1_1_2 AS DECIMAL(16,16)[1:2];
CREATE DOMAIN T_DECIMAL_17_0 AS DECIMAL(17,0); CREATE DOMAIN TA_DEC_17_0_D1_1_2 AS DECIMAL(17,0)[1:2];
CREATE DOMAIN T_DECIMAL_17_1 AS DECIMAL(17,1); CREATE DOMAIN TA_DEC_17_1_D1_1_2 AS DECIMAL(17,1)[1:2];
CREATE DOMAIN T_DECIMAL_17_2 AS DECIMAL(17,2); CREATE DOMAIN TA_DEC_17_2_D1_1_2 AS DECIMAL(17,2)[1:2];
CREATE DOMAIN T_DECIMAL_17_3 AS DECIMAL(17,3); CREATE DOMAIN TA_DEC_17_3_D1_1_2 AS DECIMAL(17,3)[1:2];
CREATE DOMAIN T_DECIMAL_17_4 AS DECIMAL(17,4); CREATE DOMAIN TA_DEC_17_4_D1_1_2 AS DECIMAL(17,4)[1:2];
CREATE DOMAIN T_DECIMAL_17_5 AS DECIMAL(17,5); CREATE DOMAIN TA_DEC_17_5_D1_1_2 AS DECIMAL(17,5)[1:2];
CREATE DOMAIN T_DECIMAL_17_6 AS DECIMAL(17,6); CREATE DOMAIN TA_DEC_17_6_D1_1_2 AS DECIMAL(17,6)[1:2];
CREATE DOMAIN T_DECIMAL_17_7 AS DECIMAL(17,7); CREATE DOMAIN TA_DEC_17_7_D1_1_2 AS DECIMAL(17,7)[1:2];
CREATE DOMAIN T_DECIMAL_17_8 AS DECIMAL(17,8); CREATE DOMAIN TA_DEC_17_8_D1_1_2 AS DECIMAL(17,8)[1:2];
CREATE DOMAIN T_DECIMAL_17_9 AS DECIMAL(17,9); CREATE DOMAIN TA_DEC_17_9_D1_1_2 AS DECIMAL(17,9)[1:2];
CREATE DOMAIN T_DECIMAL_17_10 AS DECIMAL(17,10); CREATE DOMAIN TA_DEC_17_10_D1_1_2 AS DECIMAL(17,10)[1:2];
CREATE DOMAIN T_DECIMAL_17_11 AS DECIMAL(17,11); CREATE DOMAIN TA_DEC_17_11_D1_1_2 AS DECIMAL(17,11)[1:2];
CREATE DOMAIN T_DECIMAL_17_12 AS DECIMAL(17,12); CREATE DOMAIN TA_DEC_17_12_D1_1_2 AS DECIMAL(17,12)[1:2];
CREATE DOMAIN T_DECIMAL_17_13 AS DECIMAL(17,13); CREATE DOMAIN TA_DEC_17_13_D1_1_2 AS DECIMAL(17,13)[1:2];
CREATE DOMAIN T_DECIMAL_17_14 AS DECIMAL(17,14); CREATE DOMAIN TA_DEC_17_14_D1_1_2 AS DECIMAL(17,14)[1:2];
CREATE DOMAIN T_DECIMAL_17_15 AS DECIMAL(17,15); CREATE DOMAIN TA_DEC_17_15_D1_1_2 AS DECIMAL(17,15)[1:2];
CREATE DOMAIN T_DECIMAL_17_16 AS DECIMAL(17,16); CREATE DOMAIN TA_DEC_17_16_D1_1_2 AS DECIMAL(17,16)[1:2];
CREATE DOMAIN T_DECIMAL_17_17 AS DECIMAL(17,17); CREATE DOMAIN TA_DEC_17_17_D1_1_2 AS DECIMAL(17,17)[1:2];
CREATE DOMAIN T_DECIMAL_18_0 AS DECIMAL(18,0); CREATE DOMAIN TA_DEC_18_0_D1_1_2 AS DECIMAL(18,0)[1:2];
CREATE DOMAIN T_DECIMAL_18_1 AS DECIMAL(18,1); CREATE DOMAIN TA_DEC_18_1_D1_1_2 AS DECIMAL(18,1)[1:2];
CREATE DOMAIN T_DECIMAL_18_2 AS DECIMAL(18,2); CREATE DOMAIN TA_DEC_18_2_D1_1_2 AS DECIMAL(18,2)[1:2];
CREATE DOMAIN T_DECIMAL_18_3 AS DECIMAL(18,3); CREATE DOMAIN TA_DEC_18_3_D1_1_2 AS DECIMAL(18,3)[1:2];
CREATE DOMAIN T_DECIMAL_18_4 AS DECIMAL(18,4); CREATE DOMAIN TA_DEC_18_4_D1_1_2 AS DECIMAL(18,4)[1:2];
CREATE DOMAIN T_DECIMAL_18_5 AS DECIMAL(18,5); CREATE DOMAIN TA_DEC_18_5_D1_1_2 AS DECIMAL(18,5)[1:2];
CREATE DOMAIN T_DECIMAL_18_6 AS DECIMAL(18,6); CREATE DOMAIN TA_DEC_18_6_D1_1_2 AS DECIMAL(18,6)[1:2];
CREATE DOMAIN T_DECIMAL_18_7 AS DECIMAL(18,7); CREATE DOMAIN TA_DEC_18_7_D1_1_2 AS DECIMAL(18,7)[1:2];
CREATE DOMAIN T_DECIMAL_18_8 AS DECIMAL(18,8); CREATE DOMAIN TA_DEC_18_8_D1_1_2 AS DECIMAL(18,8)[1:2];
CREATE DOMAIN T_DECIMAL_18_9 AS DECIMAL(18,9); CREATE DOMAIN TA_DEC_18_9_D1_1_2 AS DECIMAL(18,9)[1:2];
CREATE DOMAIN T_DECIMAL_18_10 AS DECIMAL(18,10); CREATE DOMAIN TA_DEC_18_10_D1_1_2 AS DECIMAL(18,10)[1:2];
CREATE DOMAIN T_DECIMAL_18_11 AS DECIMAL(18,11); CREATE DOMAIN TA_DEC_18_11_D1_1_2 AS DECIMAL(18,11)[1:2];
CREATE DOMAIN T_DECIMAL_18_12 AS DECIMAL(18,12); CREATE DOMAIN TA_DEC_18_12_D1_1_2 AS DECIMAL(18,12)[1:2];
CREATE DOMAIN T_DECIMAL_18_13 AS DECIMAL(18,13); CREATE DOMAIN TA_DEC_18_13_D1_1_2 AS DECIMAL(18,13)[1:2];
CREATE DOMAIN T_DECIMAL_18_14 AS DECIMAL(18,14); CREATE DOMAIN TA_DEC_18_14_D1_1_2 AS DECIMAL(18,14)[1:2];
CREATE DOMAIN T_DECIMAL_18_15 AS DECIMAL(18,15); CREATE DOMAIN TA_DEC_18_15_D1_1_2 AS DECIMAL(18,15)[1:2];
CREATE DOMAIN T_DECIMAL_18_16 AS DECIMAL(18,16); CREATE DOMAIN TA_DEC_18_16_D1_1_2 AS DECIMAL(18,16)[1:2];
CREATE DOMAIN T_DECIMAL_18_17 AS DECIMAL(18,17); CREATE DOMAIN TA_DEC_18_17_D1_1_2 AS DECIMAL(18,17)[1:2];
CREATE DOMAIN T_DECIMAL_18_18 AS DECIMAL(18,18); CREATE DOMAIN TA_DEC_18_18_D1_1_2 AS DECIMAL(18,18)[1:2];FB4 creates these domains without any problems.
hvlad commented
The database is corrupted, relation ddl_test_table_24728(17796) have no PP assigned, run and see:
select * from rdb$pages where rdb$relation_id = 17796;
I doubt we can do something with this info.
hvlad commented
There are two such broken tables:
select r.rdb$relation_id, r.rdb$relation_name
from rdb$relations r left join rdb$pages p on r.rdb$relation_id = p.rdb$relation_id
where r.rdb$relation_type = 0
and p.rdb$relation_id is null;
RDB$RELATION_ID RDB$RELATION_NAME
=============== ===============================================================
17796 DDL_TEST_TABLE_24728
20295 DDL_TEST_TABLE_28253
dmitry-lipetsk commented
I have the following information about DDL_TEST_TABLE_24728 table:
[THR:035256] [2024-05-25 19:23:39] * START TEST [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1]
[THR:035256] [2024-05-25 19:23:39] *
[THR:035256] [2024-05-25 19:23:39] * PREPARE
[THR:035256] [2024-05-25 19:23:39] * RUN
[THR:035256] [2024-05-25 19:23:39] [test] Set lock to DDL work
[THR:035256] [2024-05-25 19:23:39] [test] [TEST SUPPORT OF DDL STMT]
[THR:035256] [2024-05-25 19:23:39] [test] *DDL :1
[THR:035256] [2024-05-25 19:23:39] [test] *DDL LEVEL :READCOMMITTED
[THR:035256] [2024-05-25 19:23:39] [test] *DML LEVEL :REPEATABLEREAD
[THR:035256] [2024-05-25 19:23:39] [test] create pooled data source...
[THR:035256] [2024-05-25 19:23:39] [test] connect to database ...OK
[THR:035256] [2024-05-25 19:23:39] [test] session.create(ds)
[THR:035256] [2024-05-25 19:23:39] [test] Set Session Prop "Session AutoCommit"=FALSE [0]
[THR:035256] [2024-05-25 19:23:39] [test] Set Session Prop "Session AutoCommit DDL"=1L
[THR:035256] [2024-05-25 19:23:39] [test] Set Session Prop "Session AutoCommit DDL Level"=4096L
[THR:035256] [2024-05-25 19:23:39] [test] STEP #0: [CREATION OF TEST OBJECTS]
[THR:035256] [2024-05-25 19:23:39] [test] START TRANSACTION [READCOMMITTED]...
[THR:035256] [2024-05-25 19:23:39] [test] GENERATE ID [GEN_ID_DDL]. Count:1... range: 24728...24728
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"create table DDL_TEST_TABLE_24728 (ID INTEGER)"
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"create generator GEN_TEST_DDL_DEL_COUNT_24728"
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"create trigger AD_DDL_TEST_TABLE_24728 for DDL_TEST_TABLE_24728
after delete
as
declare variable x integer
;begin
x=gen_id(GEN_TEST_DDL_DEL_COUNT_24728,1);
end"
[THR:035256] [2024-05-25 19:23:39] [test] COMMIT TRANSACTION [RETAINING=false]...
[THR:035256] [2024-05-25 19:23:39] [test] STEP #1: [INSERT TEST DATA]
[THR:035256] [2024-05-25 19:23:39] [test] START TRANSACTION [REPEATABLEREAD]...
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"insert into DDL_TEST_TABLE_24728 (id) values(1)"
[THR:035256] [2024-05-25 19:23:39] [test] COMMIT TRANSACTION [RETAINING=false]...
[THR:035256] [2024-05-25 19:23:39] [test] STEP #2: [EXECUTE DDL AND DML STATEMENTS]
[THR:035256] [2024-05-25 19:23:39] [test] START TRANSACTION [REPEATABLEREAD]...
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"alter trigger AD_DDL_TEST_TABLE_24728 inactive"
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"delete from DDL_TEST_TABLE_24728"
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"alter trigger AD_DDL_TEST_TABLE_24728 active"
[THR:035256] [2024-05-25 19:23:39] [test] COMMIT TRANSACTION [RETAINING=false]...
[THR:035256] [2024-05-25 19:23:39] [test] STEP #3: [VERIFY GENERATOR]
[THR:035256] [2024-05-25 19:23:39] [test] START TRANSACTION [REPEATABLEREAD]...
[THR:035256] [2024-05-25 19:23:39] [test] Create command ...
[THR:035256] [2024-05-25 19:23:39] [test] Prepare Command:
"select gen_id(GEN_TEST_DDL_DEL_COUNT_24728,0) as x from dual"
[THR:035256] [2024-05-25 19:23:39] [test] EXECUTE COMMAND (or=true,rpi=true) [NO PARAMS] ...
[THR:035256] [2024-05-25 19:23:39] [test] FETCH(FORWARD)... OK
[THR:035256] [2024-05-25 19:23:39] [test] GET FIELD "X" ...
[THR:035256] [2024-05-25 19:23:39] [test] OK [I4[S_OK][0]]
[THR:035256] [2024-05-25 19:23:39] [test] COMPARE I4[S_OK][0] WITH I4[S_OK][0] ...
[THR:035256] [2024-05-25 19:23:39] [test] COMMIT TRANSACTION [RETAINING=false]...
[THR:035256] [2024-05-25 19:23:39] [test] STEP #4: [DROP TEST OBJECTS]
[THR:035256] [2024-05-25 19:23:39] [test] START TRANSACTION [REPEATABLEREAD]...
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"drop trigger AD_DDL_TEST_TABLE_24728"
[THR:035256] [2024-05-25 19:23:39] [test] EXEC IMMED:
"drop table DDL_TEST_TABLE_24728"
[THR:035256] [2024-05-25 19:23:41] ERROR: [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1] [LCPI.IBProvider.5] [winsock] Сбой чтения данных из порта подключения. Ошибка WinSock: 10054.
[THR:035256] [2024-05-25 19:23:41] ERROR: [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1] [LCPI.IBProvider.5] Ошибка подтверждения транзакции. Транзакция будет отменена.
[THR:035256] [2024-05-25 19:23:41] ERROR: [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1] [LCPI.IBProvider.5] [winsock] Сбой записи данных в порт подключения. Ошибка WinSock: 10054.
[THR:035256] [2024-05-25 19:23:41] ERROR: [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1] [LCPI.IBProvider.5] Ошибка отката транзакции.
[THR:035256] [2024-05-25 19:23:41] ERROR: [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1] [oledb client library] Execution of command
[THR:035256] [2024-05-25 19:23:41] *
[THR:035256] [2024-05-25 19:23:41] * REAL TIME: 16481258 [00:00:01.6481258]
[THR:035256] [2024-05-25 19:23:41] * USER TIME: 156250 [00:00:00.0156250]
[THR:035256] [2024-05-25 19:23:41] * KERNEL TIME: 0 [00:00:00]
[THR:035256] [2024-05-25 19:23:41] * TOTAL TIME: 156250 [00:00:00.0156250]
[THR:035256] [2024-05-25 19:23:41] *
[THR:035256] [2024-05-25 19:23:41] * ERROR COUNT: 5
[THR:035256] [2024-05-25 19:23:41] * WARNING COUNT: 0
[THR:035256] [2024-05-25 19:23:41] *
[THR:035256] [2024-05-25 19:23:41] * STOP TEST [cmd.005.ddl.002.ddl_read_committed.dml_repeat_read.ddl_auto_commit_1]
hvlad commented
So, there was some unknown error when table DDL_TEST_TABLE_24728 was dropped.
Obvoiusly, it was not handled correctly.
Anyway, reproducible test is needed to fix the bug and to verify the fix.