Error 'Cannot add or update a child row: a foreign key constraint fails故障解决

时间：2022-06-16 03:47:44

一大早的，某从库突然报出故障：SQL线程中断！

查看从库状态：

mysql> show slave status\G

 Slave_IO_State: Waiting for master to send event

  Master_Log_File: mysql-bin.026023

  Read_Master_Log_Pos: 230415889

  Relay_Log_File: relay-bin.058946

  Relay_Log_Pos: 54632056

  Relay_Master_Log_File: mysql-bin.026002

  Slave_IO_Running: Yes

  Slave_SQL_Running: No

  Last_Errno: 1452

  Last_Error: Error 'Cannot add or update a child row: a foreign key constraint fails (`zabbix`.`trigger_discovery`, CONSTRAINT `c_trigger_discovery_2` FOREIGN KEY (`parent_triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE)' on query. Default database: 'zabbix'. Query: 'insert into trigger_discovery (triggerdiscoveryid,triggerid,parent_triggerid,name) values  (1677,26249,22532,'Free inodes is less than 20% on volume {#FSNAME}'), (1678,26250,22532,'Free inodes is less than 20% on volume {#FSNAME}'), (1679,26251,22532,'Free inodes is less than 20% on volume {#FSNAME}')'

  Exec_Master_Log_Pos: 54631910

重点关注报错信息，定位问题，问题是：Cannot add or update a child row:a foreign key constraint fails ，涉及到的外键是：c_trigger_discovery_2

那这个外键的定义是什么呢？

报错信息中也有列出：

trigger_discovery`, CONSTRAINT `c_trigger_discovery_2`  FOREIGN KEY (`parent_triggerid`) REFERENCES `triggers` (`triggerid`) ON DELETE CASCADE

那明白了，是表trigger_discovery中的列`parent_triggerid`和表triggers中的列`triggerid`有外键关联，现在这里的数据插入出现了问题

那为什么会出现问题？

继续看报错，错误是从这里开始的：

insert into trigger_discovery (triggerdiscoveryid,triggerid,parent_triggerid,name) values  (1677,26249,22532,'Free inodes is less than 20% on volume {#FSNAME}')

上述外键对应的列parent_triggerid的值是22532，难道这个值在表triggers中有问题？

我们去表triggers中查看：

从库

mysql> select * from triggers where triggerid=22532;

Empty set (0.00 sec)

主库

mysql> select * from triggers where triggerid=22532;

+-----------+------------+--------------------------------------------------+-----+--------+-------+----------+------------+----------+-------+------------+------+-------------+-------+

| triggerid | expression | description                                      | url | status | value | priority | lastchange | comments | error | templateid | type | value_flags | flags |

+-----------+------------+--------------------------------------------------+-----+--------+-------+----------+------------+----------+-------+------------+------+-------------+-------+

|     22532 | {23251}<20 | Free inodes is less than 20% on volume {#FSNAME} |     |      0 |     0 |        2 |          0 |          |       |      13272 |    0 |           1 |     2 |

+-----------+------------+--------------------------------------------------+-----+--------+-------+----------+------------+----------+-------+------------+------+-------------+-------+

1 row in set (0.00 sec)

果然，从库中没有这个值对应的信息，但主库中是有的，原来是主从不一致导致的，从库中缺失这个值，主库中顺利插入了，但数据传到从库后，从库的外键约束限制了这一插入操作，所以SQL线程阻塞。

问题找到了，那怎么解决？

首先，为了让从库尽快恢复运行，就先把这个错误跳过吧：

mysql>SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;        #跳过一个事务

mysql>start slave;

接下来就是主从数据不一致的问题，可以使用pt-table-checksum来检查下不一致的数据，再进行同步，具体步骤如下：

在主库执行：

mysql>GRANT SELECT, PROCESS, SUPER, REPLICATION SLAVE,CREATE,DELETE,INSERT,UPDATE ON *.* TO 'USER'@'MASTER_HOST' identified  by 'PASSWORD';
注：创建用户，这些权限都是必须的，否则会报错

shell> ./pt-table-checksum --host='master_host' --user='user' --password='password' --port='port' --databases=zabbix --ignore-tables=ignore_table --recursion-method=processlist 
注：(1)因为涉及到的表太多，查看后发现很多表都有外键关联，错综复杂，而且因为是监控表，即使丢失一些也没什么关系，所以查出较大的且没有外键关联的表用ignore-tables选项排除，对其他表进行比对，如果表比较少的话直接指定--TABLES

    (2)recursion-method如果不设的话，会报错：Diffs cannot be detected because no slaves were found.  其参数有四：processlist/hosts/dsn/no,用来决定查找slave的方式是show full processlist还是show slave hosts还是直接给出slave信息，具体用法在另一随笔pt-table-checksum介绍中详述

shell>./pt-table-sync --print --replicate=percona.checksums h=master_host,u=user,p=password,P=port h=slave_host,u=user,p=password,P=port --recursion-method=processlist >pt.log
注：最好使用--print，不要直接使用--execute，否则如果弄出问题，就更麻烦了，打印出直接执行的语句，去从库执行就好了
将pt.log传到从库，直接执行，然后再次在主库上进行一致性检查，如果还有不一致的数据，记得登录mysql去把checksums表清空，然后再次进行检查同步，直到没有不一致的数据。
当然，如果主从数据反复出现不一致的话，那就要先去检查造成不一致的原因了，釜底抽薪才是硬道理。
相关文章