Details
-
Bug
-
Status: Stalled (View Workflow)
-
Critical
-
Resolution: Unresolved
-
10.6, 10.11, 11.4, 11.8
-
None
-
Can result in unexpected behaviour
-
Q4/2025 Server Maintenance
Description
Innochecksum tool with -S option parses the ibdata1 file. But it reads the doublewrite
buffer pages and reads the index id from it. This gives the summary that
index exist in ibdata1 even though page in dblwr belong to different tablespace altogether.
--source include/have_innodb.inc
|
--source include/have_sequence.inc
|
|
let MYSQLD_DATADIR=`select @@datadir`;
|
create table t1 (f1 int primary key, f2 char(200)) engine=innodb stats_persistent=0;
|
insert into t1 select seq, repeat('a', 200) from seq_1_to_16384;
|
SET GLOBAL innodb_max_dirty_pages_pct_lwm=0, innodb_max_dirty_pages_pct=0;
|
let $wait_condition =
|
SELECT variable_value = 0
|
FROM information_schema.global_status
|
WHERE variable_name = 'INNODB_BUFFER_POOL_PAGES_DIRTY';
|
--source include/wait_condition.inc
|
|
let $shutdown_timeout=0;
|
--source include/shutdown_mysqld.inc
|
--echo # Run the innochecksum to display undo log pages
|
let $resultlog=$MYSQLTEST_VARDIR/tmp/result.log;
|
|
exec $INNOCHECKSUM -S -r $MYSQLD_DATADIR/ibdata1 > $resultlog;
|
let $restart_parameters=;
|
--source include/start_mysqld.inc
|
select * from information_schema.innodb_sys_tables;
|
select * from information_schema.innodb_sys_indexes;
|
drop table t1;
|
Let's look into result_log file:
File::/home/thiru/source_file/server/10.6/bld_new-debug/mysql-test/var/mysqld.1/data//ibdata1
|
================PAGE TYPE SUMMARY==============
|
#PAGE_COUNT PAGE_TYPE
|
===============================================
|
137 Index page
|
0 Undo log page
|
2 Inode page
|
0 Insert buffer free list page
|
448 Freshly allocated page
|
1 Insert buffer bitmap
|
163 System page
|
1 Transaction system page
|
2 File Space Header
|
0 Extent descriptor page
|
0 BLOB page
|
0 Compressed BLOB page
|
0 Page compressed page
|
0 Page compressed encrypted page
|
0 Other type of page
|
 |
===============================================
|
Additional information:
|
Undo page type: 0
|
Undo page state: 0 active, 0 cached, 0 to_purge, 0 prepared, 0 other
|
index_id #pages #leaf_pages #recs_per_page #bytes_per_page
|
1 1 1 7 546
|
2 1 1 32 2111
|
3 1 1 12 882
|
4 1 1 20 897
|
5 1 1 7 231
|
11 1 1 0 0
|
12 1 1 0 0
|
13 1 1 0 0
|
14 1 1 0 0
|
15 1 1 0 0
|
23 126 126 67 15096
|
-4294967296 1 1 0 0
|
Here gives the impression that index id 23 exist in system tablespace. But it doesn't.
select * from information_schema.innodb_sys_tables;
|
TABLE_ID NAME FLAG N_COLS SPACE ROW_FORMAT ZIP_PAGE_SIZE SPACE_TYPE
|
11 SYS_FOREIGN 0 7 0 Redundant 0 System
|
12 SYS_FOREIGN_COLS 0 7 0 Redundant 0System
|
13 SYS_VIRTUAL 0 6 0 Redundant 0 System
|
15 mysql/innodb_index_stats 33 11 2 Dynamic0Single
|
14 mysql/innodb_table_stats 33 9 1 Dynamic0Single
|
16 mysql/transaction_registry 33 8 3 Dynamic0Single
|
18 test/t1 33 5 5 Dynamic 0 Single
|
select * from information_schema.innodb_sys_indexes;
|
INDEX_ID NAME TABLE_ID TYPE N_FIELDS PAGE_NOSPACE MERGE_THRESHOLD
|
11 ID_IND 11 3 1 302 0 50
|
12 FOR_IND 11 0 1 303 0 50
|
13 REF_IND 11 0 1 304 0 50
|
14 ID_IND 12 3 2 305 0 50
|
15 BASE_IDX 13 3 3 306 0 50
|
16 PRIMARY 14 3 2 3 1 50
|
17 PRIMARY 15 3 4 3 2 50
|
18 PRIMARY 16 3 1 3 3 50
|
19 commit_id 16 2 1 4 3 50
|
20 begin_timestamp 16 0 1 5 3 50
|
21 commit_timestamp 16 0 2 6 3 50
|
23 PRIMARY 18 3 1 3 5 50
|