pt-fk-error-logger displays same Foreign key error repeatedly

[Gopal]

pt-fk-error-logger reports the same FK Error message again and again.

perl pt-fk-error-logger h=sedodbdevha1.i.sedorz.net -udba -p$dba_pw
percona-toolkit-2.2.17
MySQL 5.1
Output -
160616 8:53:13 Transaction:
TRANSACTION 0 174698243, ACTIVE 0 sec, process no 26029, OS thread id 139991371470592 inserting, thread declared inside InnoDB 500
mysql tables in use 2, locked 2
3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 47407, query id 1241485 172.29.14.249 sdbrw update
insert into Table_Commentator(fk_Table,fk_member,idx) values (200000000
,2342334,123)
Foreign key constraint fails for table `dbsedo`.`Table_Commentator`:
,
  CONSTRAINT `Table_Commentator_ibfk_1` FOREIGN KEY (`fk_Table`) REFERENCES `Table` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
Trying to add in child table, in index `idxTableCommentator_Table` tuple:
DATA TUPLE: 2 fields;
 0: len 3; hex ffffff; asc ;; 1: len 3; hex 000edb; asc ;;

But in parent table `dbsedo`.`Table`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 3; hex 029ea7; asc ;; 1: len 6; hex 000009c71b45; asc E;; 2: len 7; hex 00000040022f9e; asc @ / ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 4; hex 002205f1; asc " ;; 6: len 3; hex 000003; asc ;; 7: len 19; hex 66656e73746572736368656962656e2e636f6d; asc fensterscheiben.com;; 8: len 19; hex 66656e73746572736368656962656e2e636f6d; asc fensterscheiben.com;; 9: len 3; hex 00003c; asc <;; 10: len 1; hex 01; asc ;; 11: len 2; hex 8a00; asc ;; 12: len 8; hex 80001255f0f4c476; asc U v;; 13: len 8; hex 80001255f1410fb6; asc U A ;; 14: SQL NULL; 15: SQL NULL; 16: len 1; hex 80; asc ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: len 1; hex 80; asc ;; 21: len 8; hex 80001255f0f4c476; asc U v;; 22: len 4; hex 56f3a5f2; asc V ;;

160616 8:53:13 Transaction:
TRANSACTION 0 174698243, ACTIVE 0 sec, process no 26029, OS thread id 139991371470592 inserting, thread declared inside InnoDB 500
mysql tables in use 2, locked 2
3 lock struct(s), heap size 368, 1 row lock(s), undo log entries 1
MySQL thread id 47407, query id 1241485 172.29.14.249 sdbrw update
insert into Table_Commentator(fk_Table,fk_member,idx) values (200000000
,2342334,123)
Foreign key constraint fails for table `dbsedo`.`Table_Commentator`:
,
  CONSTRAINT `Table_Commentator_ibfk_1` FOREIGN KEY (`fk_Table`) REFERENCES `Table` (`id`) ON DELETE CASCADE ON UPDATE NO ACTION
Trying to add in child table, in index `idxTableCommentator_Table` tuple:
DATA TUPLE: 2 fields;
 0: len 3; hex ffffff; asc ;; 1: len 3; hex 000edb; asc ;;

But in parent table `dbsedo`.`Table`, in index `PRIMARY`,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 3; hex 029ea7; asc ;; 1: len 6; hex 000009c71b45; asc E;; 2: len 7; hex 00000040022f9e; asc @ / ;; 3: SQL NULL; 4: len 1; hex 01; asc ;; 5: len 4; hex 002205f1; asc " ;; 6: len 3; hex 000003; asc ;; 7: len 19; hex 66656e73746572736368656962656e2e636f6d; asc fensterscheiben.com;; 8: len 19; hex 66656e73746572736368656962656e2e636f6d; asc fensterscheiben.com;; 9: len 3; hex 00003c; asc <;; 10: len 1; hex 01; asc ;; 11: len 2; hex 8a00; asc ;; 12: len 8; hex 80001255f0f4c476; asc U v;; 13: len 8; hex 80001255f1410fb6; asc U A ;; 14: SQL NULL; 15: SQL NULL; 16: len 1; hex 80; asc ;; 17: SQL NULL; 18: SQL NULL; 19: SQL NULL; 20: len 1; hex 80; asc ;; 21: len 8; hex 80001255f0f4c476; asc U v;; 22: len 4; hex 56f3a5f2; asc V ;;

[Sveta Smirnova (svetasmirnova)]

Thank you for the report.

Verified as described with versions 2.2.20 and 3.0.2.

To repeat:

- Create 2 tables, parent and child
- Produce any FK error, so it stays in SHOW ENGINE INNODB STATUS
- Do not do any other job on the server
- Wait at least 15 seconds after the tool prints the error first time and you will see it will print it again and again

This behavior is different from the documented at https://www.percona.com/doc/percona-toolkit/3.0/pt-fk-error-logger.html: " Foreign key errors are uniquely identified by their timestamp. Only new (more recent) errors are printed or saved."

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-1358