Decoding InnoDB foreign key errors

When debugging InnoDB foreign key errors, the most detailed error message is found in the LATEST FOREIGN KEY ERROR section of show engine innodb status. This will helpfully include a binary dump of the index tuple that failed, something like this:

Foreign key constraint fails for table `foo`.`#sql-7_146`:
,
CONSTRAINT `some_fk` FOREIGN KEY (`bar_id`) REFERENCES `bar` (`id`)
Trying to add in child table, in index some_fk tuple:
DATA TUPLE: 2 fields;
0: len 8; hex 00000000250763b5; asc % c ;;
1: len 4; hex 84d43d99; asc = ;

In this case the IDs in the tuple were an unsigned bigint and a signed int, thus the 8 and 4 byte representations. While this is certainly a step on the way to identifying the row that violated the foreign key integrity, having it printed in decimal certainly would have saved some headache. You’d think that these would be fairly trivial to convert from hex to decimal, and you’d be correct for the unsigned long, you can tack on a 0x prefix and use python f. ex: python -c 'print(0x00000000250763b5), and it would print the correct ID of 621,241,269. (You can also use mysql itself, select conv('00000000250763b5', 16, 10) will return the same thing). However if you try this with the int, you’ll get 2,228,501,913 back, which to a sharp eye immediately seems a bit sus since it’s larger than the int max (~2.1B).

jeremycole on Stack Overflow has luckily shared the crucial bit of information on what is going on here:

80000003 is the hex representation of the bytes stored for the integer 3 (InnoDB internally flips the high bit)

This nugget of wisdom is all you need to be able to identify your offending row, in my case flip the leading 8 to a 0 and it converts easily.