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

how to debug a mysqld core file from an rpm install

You have a typical rpm installation of mysql, and the process is crashing. Here are the basic steps needed to find out more info about a crash:

• Configure the OS to be able to create corefiles. (Redhat details), (Solaris details)
  
• Tell mysqld to create a corefile by adding the following options to my.cnf:

[mysqld_safe]
core-file-size=unlimited

[mysqld]
core-file

Usually the corefile will be created in the datadir with a name like core.2921 where 2921 was the pid of the running process. The location is configurable on most OS's.

You'll need the following to study the core file:

• exact mysqld binary that created the core file
• the core file
• the glibc version of the original system (rpm -qa|grep -i glibc)
• the debuginfo package corresponding to the original mysql rpms.

Let's go through a hypothetical example next.
On my server I have installed MySQL-server-community-5.0.67-0.rhel5.x86_64.rpm and it's been crashing. I have a corefile called core.12345 which I've moved to a test system because I don't want to impact production while playing around with it.

On the production server we have glibc 2.3.4-2.36 installed. So to setup the test box to study the core I do this:

From dev.mysql.com download the MySQL-community-debuginfo-5.0.67-0.rhel5.x86_64.rpm
Download the glibc-2.3.4-2.36.x86_64.rpm from somewhere (in case test system isn't running same version). Next we extract the RPMS and launch gdb and tell it the path to load libraries and symbol files:

rpm2cpio MySQL-community-debuginfo-5.0.67-0.rhel5.x86_64.rpm | cpio -idvu
rpm2cpio glibc-2.3.4-2.36.x86_64.rpm | cpio -idvu

gdb ./mysqld --core ./core.12345
set solib-absolute-prefix .
file ./usr/lib/debug/usr/sbin/mysqld.debug

From here you should get reasonable output from GDB, such as "thread apply all bt" and "bt full" and continue to examine the corefile...

gypsy is resumed

In search of better qa tools, i have resumed work on my gypsy. Within hours i verified a bug i thought was not possible any time soon...

crash on prepared statement + cursor + geometry + too many open files !

The code is also in launchpad (bzr branch lp:gypsy) if anybody cares.

innodb plugin and new features!

check it out:
the announcement

plugin documentation

o) Fast Index Creation in the InnoDB Storage Engine
o) InnoDB Data Compression
o) InnoDB File Format Management
o) InnoDB INFORMATION_SCHEMA tables

yay!! gonna test the compression immediately :)