Details
-
Bug
-
Status: Open (View Workflow)
-
Major
-
Resolution: Unresolved
-
10.11.7
-
Ubuntu 22.04, n.a.
Description
When we run MariaDB slave with parallel replication, for example:
SET GLOBAL slave_parallel_threads = 16; |
SET GLOBAL slave_parallel_max_queued = 16*1024*1024; |
to catch up a slave lag caused by a replication error, our monitoring alerts with Foreign Key errors (SHOW ENGINE INNODB STATUS) which we do NOT see otherwise. But application was fine and continued working.
This can be reproduced at will on our system.
------------------------
|
LATEST FOREIGN KEY ERROR
|
------------------------
|
2024-04-17 09:04:18 0x7f60580f3640 Transaction:
|
TRANSACTION 7212650525, ACTIVE 0 sec inserting
|
mysql tables in use 1, locked 1
|
6 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1
|
MariaDB thread id 25047, OS thread handle 140051770979904, query id 3086531 Write_rows_log_event::write_row(-1) on table `alerts`
|
insert into alerts (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,error,esc_step,alerttype,acknowledgeid,parameters) values (204407,4,473631,3,1713278885,1,'oli.sennhauser@fromdual.com','Zabbix server - Average: /etc/passwd has been changed on server Zabbix server','Trigger: /etc/passwd has been changed on server Zabbix server
|
Trigger status: PROBLEM
|
Trigger severity: Average
|
Trigger URL:
|
|
|
Item values: 765250043
|
1. Checksum of $1 (Zabbix server:vfs.file.cksum[/etc/passwd]): 765250043',3,'',1,0,null,'{}')
Foreign key constraint fails for table `zabbix`.`alerts`:
CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE in parent table, in index alerts_3 tuple:
|
DATA TUPLE: 2 fields;
|
0: len 8; hex 0000000000073a1f; asc : ;;
|
1: len 8; hex 0000000000031e77; asc w;;
|
But in parent table `zabbix`.`events`, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 12; compact format; info bits 0
|
0: len 8; hex 0000000000073a1e; asc : ;;
|
1: len 6; hex 0001ade84f9c; asc O ;;
|
2: len 7; hex be0000e2d010c2; asc ;;
|
3: len 4; hex 80000000; asc ;;
|
4: len 4; hex 80000000; asc ;;
|
5: len 8; hex 0000000000004095; asc @ ;;
|
6: len 4; hex e61e8f66; asc f;;
|
7: len 4; hex 80000001; asc ;;
|
8: len 4; hex 80000000; asc ;;
|
9: len 4; hex 8000000d; asc ;;
|
10: len 18; hex 53514c207468726561642073746f70706564; asc SQL thread stopped;;
|
11: len 4; hex 80000004; asc ;;
|
CREATE TABLE `events` ( |
`eventid` bigint(20) unsigned NOT NULL, |
`source` int(11) NOT NULL DEFAULT 0, |
`object` int(11) NOT NULL DEFAULT 0, |
`objectid` bigint(20) unsigned NOT NULL DEFAULT 0, |
`clock` int(11) NOT NULL DEFAULT 0, |
`value` int(11) NOT NULL DEFAULT 0, |
`acknowledged` int(11) NOT NULL DEFAULT 0, |
`ns` int(11) NOT NULL DEFAULT 0, |
`name` varchar(2048) NOT NULL DEFAULT '', |
`severity` int(11) NOT NULL DEFAULT 0, |
PRIMARY KEY (`eventid`), |
KEY `events_1` (`source`,`object`,`objectid`,`clock`), |
KEY `events_2` (`source`,`object`,`clock`) |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
|
|
CREATE TABLE `alerts` ( |
`alertid` bigint(20) unsigned NOT NULL, |
`actionid` bigint(20) unsigned NOT NULL, |
`eventid` bigint(20) unsigned NOT NULL, |
`userid` bigint(20) unsigned DEFAULT NULL, |
`clock` int(11) NOT NULL DEFAULT 0, |
`mediatypeid` bigint(20) unsigned DEFAULT NULL, |
`sendto` varchar(1024) NOT NULL DEFAULT '', |
`subject` varchar(255) NOT NULL DEFAULT '', |
`message` text NOT NULL, |
`status` int(11) NOT NULL DEFAULT 0, |
`retries` int(11) NOT NULL DEFAULT 0, |
`error` varchar(2048) NOT NULL DEFAULT '', |
`esc_step` int(11) NOT NULL DEFAULT 0, |
`alerttype` int(11) NOT NULL DEFAULT 0, |
`p_eventid` bigint(20) unsigned DEFAULT NULL, |
`acknowledgeid` bigint(20) unsigned DEFAULT NULL, |
`parameters` text NOT NULL, |
PRIMARY KEY (`alertid`), |
KEY `alerts_1` (`actionid`), |
KEY `alerts_2` (`clock`), |
KEY `alerts_3` (`eventid`), |
KEY `alerts_5` (`mediatypeid`), |
KEY `alerts_6` (`userid`), |
KEY `alerts_7` (`p_eventid`), |
KEY `alerts_4` (`status`), |
KEY `alerts_8` (`acknowledgeid`), |
CONSTRAINT `c_alerts_1` FOREIGN KEY (`actionid`) REFERENCES `actions` (`actionid`) ON DELETE CASCADE, |
CONSTRAINT `c_alerts_2` FOREIGN KEY (`eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE, |
CONSTRAINT `c_alerts_3` FOREIGN KEY (`userid`) REFERENCES `users` (`userid`) ON DELETE CASCADE, |
CONSTRAINT `c_alerts_4` FOREIGN KEY (`mediatypeid`) REFERENCES `media_type` (`mediatypeid`) ON DELETE CASCADE, |
CONSTRAINT `c_alerts_5` FOREIGN KEY (`p_eventid`) REFERENCES `events` (`eventid`) ON DELETE CASCADE, |
CONSTRAINT `c_alerts_6` FOREIGN KEY (`acknowledgeid`) REFERENCES `acknowledges` (`acknowledgeid`) ON DELETE CASCADE |
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
General Query Log:
24 Query select count(*) from usrgrp g,users_groups ug where ug.userid=3 and g.usrgrpid=ug.usrgrpid and g.users_status=1
|
24 Query select r.type,u.roleid,u.timezone from users u,role r where u.roleid=r.roleid and userid=3
|
24 Query select mediatypeid,default_msg,subject,message from opmessage where operationid=2
|
24 Query select value_type,valuemapid,units from items where itemid=50199
|
24 Query select h.proxy_hostid,h.description,i.itemid,i.name,i.key_,i.description,i.value_type,ir.error from items i join hosts h on h.hostid=i.hostid left join item_rtdata ir on ir.itemid=i.itemid where i.itemid=50199
|
24 Query select value_type,valuemapid,units from items where itemid=50199
|
24 Query select count(*) from usrgrp g,users_groups ug where ug.userid=28 and g.usrgrpid=ug.usrgrpid and g.users_status=1
|
24 Query select r.type,u.roleid,u.timezone from users u,role r where u.roleid=r.roleid and userid=28
|
24 Query select distinct hg.groupid from items i join functions f on i.itemid=f.itemid join hosts_groups hg on hg.hostid = i.hostid and f.triggerid=16488
|
24 Query select min(r.permission) from rights r join users_groups ug on ug.usrgrpid=r.groupid where ug.userid=28 and r.id in (6,29)
|
24 Query select m.mediatypeid,m.sendto,m.severity,m.period,mt.status,m.active from media m,media_type mt where m.mediatypeid=mt.mediatypeid and m.userid=3 and m.mediatypeid=1
|
24 Query select name,value from media_type_param where mediatypeid=1
|
|
|
24 Query insert into alerts (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,error,esc_step,alerttype,acknowledgeid,parameters) values (204225,4,472567,3,1713246543,1,'oli.sennhauser@fromdual.com','FromDual.maas2.prod2 - High: IO thread stopped','Trigger: IO thread stopped^M...1. Slave IO thread running (FromDual.maas2.prod2:FromDual.MySQL.slave.Slave_IO_Running): 0',3,'',1,0,null,'{}')
|
|
|
24 Query select null from operations where actionid=4 and (esc_step_to>1 or esc_step_to=0) and recovery=0 limit 1
|
24 Query select o.operationid,o.operationtype,o.esc_period,o.evaltype from operations o where o.actionid=4 and o.operationtype in (0,1) and o.esc_step_from<=1 and (o.esc_step_to=0 or o.esc_step_to>=1) and o.recovery=0
|
24 Query select conditiontype,operator,value from opconditions where operationid=2 order by conditiontype
|
24 Query select userid from opmessage_usr where operationid=2 union select g.userid from opmessage_grp m,users_groups g where m.usrgrpid=g.usrgrpid and m.operationid=2
|
24 Query select count(*) from usrgrp g,users_groups ug where ug.userid=3 and g.usrgrpid=ug.usrgrpid and g.users_status=1
|
24 Query select r.type,u.roleid,u.timezone from users u,role r where u.roleid=r.roleid and userid=3
|
24 Query select mediatypeid,default_msg,subject,message from opmessage where operationid=2
|
24 Query select value_type,valuemapid,units from items where itemid=50201
|
24 Query select h.proxy_hostid,h.description,i.itemid,i.name,i.key_,i.description,i.value_type,ir.error from items i join hosts h on h.hostid=i.hostid left join item_rtdata ir on ir.itemid=i.itemid where i.itemid=50201
|
24 Query select value_type,valuemapid,units from items where itemid=50201
|
|
|
577305 Query begin
|
577305 Query insert into events (eventid,source,object,objectid,clock,ns,value,name,severity) values (472564,0,0,16080,1713246302,38,1,'General Log is enabled',2)
|
577305 Query insert into event_tag (eventtagid,eventid,tag,value) values (21128,472564,'Application','Global Variables')
|
577305 Query insert into problem (eventid,source,object,objectid,clock,ns,name,severity) values (472564,0,0,16080,1713246302,38,'General Log is enabled',2)
|
577305 Query insert into problem_tag (problemtagid,eventid,tag,value) values (18607,472564,'Application','Global Variables')
|
577305 Query insert into escalations (escalationid,actionid,status,triggerid,itemid,eventid,r_eventid,acknowledgeid) values (36,11,0,16080,null,472564,null,null),(37,5,0,16080,null,472564,null,null),(38,14,0,16080,null,472564,null,null),(39,10,0,16080,null,472564,null,null),(40,4,0,16080,null,472564,null,null)
|
577305 Query update triggers set lastchange=1713246302,value=1 where triggerid=16080
|
577305 Query commit
|
|
|
577305 Query insert into events (eventid,source,object,objectid,clock,ns,value,name,severity) values (472564,0,0,16080,1713246302,38,1,'General Log is enabled',2)
|
FROM performance_schema.events_statements_summary_by_user_by_event_name
|
FROM performance_schema.events_statements_current AS esc
|
FROM performance_schema.events_statements_current AS esc
|
FROM performance_schema.events_statements_current AS esc
|
JOIN performance_schema.events_statements_current AS esc ON esc.thread_id = th.thread_id
|
SHOW PROCESSLIST:
| 16 | zabbix | localhost:51234 | zabbix | Sleep | 1 | | NULL | 0.000 |
|
| 17 | zabbix | localhost:51244 | zabbix | Sleep | 15 | | NULL | 0.000 |
|
| 18 | zabbix | localhost:51252 | zabbix | Sleep | 34 | | NULL | 0.000 |
|
| 22 | zabbix | localhost:51282 | zabbix | Sleep | 17 | | NULL | 0.000 |
|
| 23 | zabbix | localhost:51288 | zabbix | Sleep | 4 | | NULL | 0.000 |
|
| 25 | zabbix | localhost:51308 | zabbix | Sleep | 13 | | NULL | 0.000 |
|
| 24 | zabbix | localhost:51298 | zabbix | Sleep | 1 | | NULL | 0.000 |
|
| 30 | zabbix | localhost:51352 | zabbix | Sleep | 467 | | NULL | 0.000 |
|
| 31 | zabbix | localhost:51366 | zabbix | Sleep | 1 | | NULL | 0.000 |
|
| 33 | zabbix | localhost:51382 | zabbix | Sleep | 227 | | NULL | 0.000 |
|
| 42 | zabbix | localhost:51488 | zabbix | Sleep | 347 | | NULL | 0.000 |
|
| 36 | zabbix | localhost:51420 | zabbix | Sleep | 0 | | NULL | 0.000 |
|
| 34 | zabbix | localhost:51398 | zabbix | Sleep | 1187 | | NULL | 0.000 |
|
| 35 | zabbix | localhost:51414 | zabbix | Sleep | 107 | | NULL | 0.000 |
|
| 44 | zabbix | localhost:51508 | zabbix | Sleep | 57 | | NULL | 0.000 |
|
| 577304 | zabbix | localhost:53920 | zabbix | Sleep | 15 | | NULL | 0.000 |
|
| 577305 | zabbix | localhost:53930 | zabbix | Sleep | 0 | | NULL | 0.000 |
|
| 581354 | zabbix | localhost:39336 | zabbix | Sleep | 0 | | NULL | 0.000 |
|
| 585377 | zabbix | localhost:37408 | zabbix | Sleep | 0 | | NULL | 0.000 |
|
IMHO this FK Error should not be shown in SHOW ENGINE INNODB STATUS because it was fixed by the database or only in some case of "debug mode". Otherwise our monitoring cannot distinguish between problematic FK errors and non-problematic (fixed) FK errors.
Ideally IMHO the FK errors should be written to the error log anyway (and not to SHOW ENGINE INNODB STATUS). I think I made a feature request for this long time ago but cannot find it any more. And then the error could be suppressed in the error log with the default log_warnings setting and enabled with a higher log_warning setting (for debugging purposes)?