Saturday, September 20, 2008

innodb index page format

Today I had to decode an innodb index page, so I documented the entire process here:

E:\mysql-enterprise-gpl-5.0.66a-winx64\bin>mysqld-nt --console --skip-grant-tables --skip-name-resolve
InnoDB: The first specified data file .\ibdata1 did not exist:
InnoDB: a new database to be created!
080919 14:29:00 InnoDB: Setting file .\ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:00 InnoDB: Log file .\ib_logfile0 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
080919 14:29:01 InnoDB: Log file .\ib_logfile1 did not exist: new to be created
InnoDB: Setting log file .\ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
080919 14:29:01 InnoDB: Started; log sequence number 0 0
080919 14:29:01 [Note] mysqld-nt: ready for connections.
Version: '5.0.66a-enterprise-gpl-nt' socket: '' port: 3306 MySQL Enterprise Server (GPL)


create table t1(a varchar(20) primary key, b varchar(20), c varchar(20),key(b),key(c,b))engine=innodb;
insert into t1(a,b,c) values ('aaaa','bbbbb','cccccc');
insert into t1(a,b,c) values ('aaaaaaa',null,'ccc');
insert into t1(a,b,c) values ('a','b',null);
insert into t1(a,b,c) values ('aaaaaaaaaa','bbb','c');



080919 14:37:59 [Note] mysqld-nt: Normal shutdown

080919 14:37:59 InnoDB: Starting shutdown...
080919 14:38:14 InnoDB: Shutdown completed; log sequence number 0 48536
080919 14:38:14 [Note] mysqld-nt: Shutdown complete



we have secondary indexes on this table

this is key(c,b) (directly from ibdata1):


000d0000h: 35 56 71 04 00 00 00 34 FF FF FF FF FF FF FF FF ; 5Vq....4ÿÿÿÿÿÿÿÿ
000d0010h: 00 00 00 00 00 00 BD 92 45 BF 00 00 00 00 00 00 ; ......½’E¿......
000d0020h: 00 00 00 00 00 00 00 02 00 C3 80 06 00 00 00 00 ; .........À.....
000d0030h: 00 B5 00 05 00 00 00 04 00 00 00 00 00 00 03 05 ; .µ..............
000d0040h: 00 00 00 00 00 00 00 00 00 11 00 00 00 00 00 00 ; ................
000d0050h: 00 02 15 F2 00 00 00 00 00 00 00 02 15 32 01 00 ; ...ò.........2..
000d0060h: 02 00 47 69 6E 66 69 6D 75 6D 00 05 00 0B 00 00 ; ..Ginfimum......
000d0070h: 73 75 70 72 65 6D 75 6D 04 05 06 00 00 00 10 FF ; supremum.......ÿ
000d0080h: EF 63 63 63 63 63 63 62 62 62 62 62 61 61 61 61 ; ïccccccbbbbbaaaa
000d0090h: 07 03 02 00 00 18 FF E9 63 63 63 61 61 61 61 61 ; ......ÿécccaaaaa
000d00a0h: 61 61 01 01 01 00 00 20 00 0B 62 61 0A 03 01 00 ; aa..... ..ba....
000d00b0h: 00 00 28 FF E3 63 62 62 62 61 61 61 61 61 61 61 ; ..(ÿãcbbbaaaaaaa
000d00c0h: 61 61 61 00 00 00 00 00 00 00 00 00 00 00 00 00 ; aaa.............




Let's reformat this page into the correct fields as seen by InnoDB:

0000: 35567104 -> FIL_PAGE_SPACE_OR_CHKSUM
0004: 00000034 -> FIL_PAGE_OFFSET
0008: FFFFFFFF -> FIL_PAGE_PREV
0012: FFFFFFFF -> FIL_PAGE_NEXT
0016: 000000000000BD92 -> FIL_PAGE_LSN
0024: 45BF -> FIL_PAGE_TYPE (#define FIL_PAGE_INDEX 17855)
0026: 0000000000000000 -> FIL_PAGE_FILE_FLUSH_LSN
0034: 00000000 -> FIL_PAGE_ARCH_LOG_NO_OR_SPACE_ID

0038: 0002 -> PAGE_N_DIR_SLOTS
0040: 00C3 -> PAGE_HEAP_TOP (195 ...)
0042: 8006 -> PAGE_N_HEAP (6 records in heap (remove 15th bit)
0044: 0000 -> PAGE_FREE
0046: 0000 -> PAGE_GARBAGE
0048: 00B5 -> PAGE_LAST_INSERT
0050: 0005 -> PAGE_DIRECTION (PAGE_NO_DIRECTION)
0052: 0000 -> PAGE_N_DIRECTION
0054: 0004 -> PAGE_N_RECS
0056: 0000000000000305 -> PAGE_MAX_TRX_ID (773)
0064: 0000 -> PAGE_LEVEL
0066: 0000000000000011 -> PAGE_INDEX_ID ( Page may be an index page where index id is 0 277385)
0074: 000000000000000215F2 -> PAGE_BTR_SEG_LEAF
0084: 00000000000000021532 -> PAGE_BTR_SEG_TOP

infimum:
0094: 01 -> info_bits=0, n_owned=1 (always 1 for the infimum)
0095: 00 -> heap number
0096: 02 -> status bits
0097: 0047 -> next record (71 bytes)
0099: 696E66696D756D00 -> "infimum"

supremum:
0107: 05000b -> extra bytes
0110: 0000 -> next record, zero since supremum is always last
0112: 73757072656D756D -> "supremum"

index row1:
0120: 040506 -> field offsets, starting with the last field.
0123: 00000010 -> extra bytes
0127: FFEF -> offset to next record (17 bytes back (offset 112))
0129: 636363636363 -> 'cccccc' (keypart1)
0135: 6262626262 -> 'bbbbb' (keypart2)
0140: 61616161 -> 'aaaa' (primary key appended)

index row2:
0144: 0703 -> field lengths, starting with the last field (excluding null!).
0146: 02000018 -> extra bytes
0150: FFE9 -> offset to next record (23 bytes back (offset 129))
0152: 636363 -> 'ccc' (keypart1)
0155: 61616161616161 -> 'aaaaaaa' (primary key appended)

index row3:
0162: 0101 -> field lengths, starting with the last field (excluding null!).
0164: 01000020 -> extra bytes
0168: 000B -> offset to next record (11 bytes (offset 181))
0170: 62 -> 'b' (keypart2)
0171: 61 -> 'a' (primary key appended)

index row4:
0172: 0A0301 -> field lengths
0175: 00000028 -> extra bytes
0179: FFE3 -> offset to next record (29 bytes back (offset 152!))
0181: 63 -> 'c' (keypart1)
0182: 626262 -> 'bbb' (keypart2)
0185: 61616161616161616161 -> 'aaaaaaaaaa' (primary key appended)



explanation of "extra bytes" still to be done

3 comments:

Anonymous said...

Thank you! That info helped me a lot to understand how to recover data from the .idb file after a crash.

Anonymous said...

I think, that you are not right. I am assured. I can defend the position. Write to me in PM.

Anonymous said...

A man who dares to waste bromide hour of one of these days has not discovered the value of life.

[url=http://www.orderofmen.com/forums/members/timwright.html#vmessage101]Gilda[/url]


Linda