WEBサービス創造記

WEBサービスを作ったり保守したりしてる人のメモブログです。

MySQLで外部キー制約関連のエラーが発生したときに詳細なログを見る

   

ERROR 1215 (HY000): Cannot add foreign key constraint...

とか、

Mysql2::Error: Cannot add or update a child row: a foreign key constraint fails (`foo`.`bar`...

みたいな外部キー制約絡みのエラーが発生したときに以下の手順で詳細ログをみれる。

mysql > SHOW ENGINE INNODB STATUS\G
LATEST FOREIGN KEY ERROR というセクションに詳細ログがある。
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2017-02-02 16:59:16 0x70000537a000 Transaction:
TRANSACTION 3990484, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 20, OS thread handle 123145389842432, query id 1703 127.0.0.1 root update
INSERT INTO `motors_series_entries` (`motor_number`, `stadium_tel_code`, `series_starts_on`, `racer_official_id`, `rate_of_finished_within_second`, `created_at`, `updated_at`) VALUES (44, 4, '2017-01-18', 7777, 70.02, '2017-02-02 07:59:16', '2017-02-02 07:59:16')
Foreign key constraint fails for table `boatrace-base-data_development`.`motors_series_entries`:
,
  CONSTRAINT `motors_series_entries_ibfk_2` FOREIGN KEY (`motor_number`, `stadium_tel_code`, `series_starts_on`) REFERENCES `motors_stadiums` (`motor_number`, `stadium_tel_code`, `renewed_series_starts_on`)
Trying to add in child table, in index PRIMARY tuple:
DATA TUPLE: 9 fields;
 0: len 4; hex 8000002c; asc    ,;;
 1: len 4; hex 80000004; asc     ;;
 2: len 3; hex 8fc232; asc   2;;
 3: len 4; hex 80001e61; asc    a;;
 4: len 6; hex 0000003ce3d4; asc    <  ;;
 5: len 7; hex 00000000000000; asc        ;;
 6: len 4; hex 3d0a8c42; asc =  B;;
 7: len 5; hex 999bc47ed0; asc    ~ ;;
 8: len 5; hex 999bc47ed0; asc    ~ ;;

But in parent table `boatrace-base-data_development`.`motors_stadiums`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 696e66696d756d00; asc infimum ;;

 - MySQL , ,