===================================== 2021-02-18 13:35:50 0x7ed60a718700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 30 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 6381726 srv_active, 0 srv_shutdown, 168 srv_idle srv_master_thread log flush and writes: 6381829 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 151961597 OS WAIT ARRAY INFO: signal count 226330119 RW-shared spins 447337477, rounds 1304679606, OS waits 8949009 RW-excl spins 701995222, rounds 1412021375, OS waits 6553967 RW-sx spins 155031885, rounds 693683114, OS waits 1752510 Spin rounds per wait: 2.92 RW-shared, 2.01 RW-excl, 4.47 RW-sx ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2021-01-07 09:40:34 0x7ed6170c3700 Transaction: TRANSACTION 76819422466, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 16 lock struct(s), heap size 1128, 8 row lock(s), undo log entries 1 MySQL thread id 3819975, OS thread handle 139457974777600, query id 5716004859 192.168.184.6 sa Updating DELETE FROM `optovision`.`pctimeentrylist` WHERE `OID`=1265 Foreign key constraint fails for table `optovision`.`pctimeentry`: , CONSTRAINT `FK_pcTimeEntry_TimeEntryList` FOREIGN KEY (`TimeEntryList`) REFERENCES `pctimeentrylist` (`OID`) Trying to delete or update in parent table, in index PRIMARY tuple: DATA TUPLE: 6 fields; 0: len 4; hex 800004f1; asc ;; 1: len 6; hex 0011e2cb5d02; asc ] ;; 2: len 7; hex 76000a803328a9; asc v 3( ;; 3: len 4; hex 80000000; asc ;; 4: SQL NULL; 5: len 21; hex 4c61636b204835204b6f6e66696775726174696f6e; asc Lack H5 Konfiguration;; But in child table `optovision`.`pctimeentry`, in index iTimeEntryList_pcTimeEntry, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 800004f1; asc ;; 1: len 4; hex 80033d81; asc = ;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2021-02-18 09:07:24 0x7ed90ab98700 *** (1) TRANSACTION: TRANSACTION 79730951688, ACTIVE 13 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 15 lock struct(s), heap size 1128, 13 row lock(s), undo log entries 1 MySQL thread id 7361340, OS thread handle 139457746417408, query id 13903195596 172.16.185.21 owstate Updating update `cora06` set `COA6OHID`=13198937,`COA6RCID`=0,`COA6OPID`=25311799,`COA6ODIS`=0.00,`COA6PPRC`=46.00,`COA6CURR`=NULL,`COA6RTBL`=NULL,`COA6CLMDSN`=NULL,`COA6CLMGRP`=NULL,`COA6CLMCSE`=NULL,`COA6CLMJGM`=NULL,`COA6CLMRSN`=NULL,`COA64FREE`=NULL where (`COA6PKEY` = 1359645) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 7734387 page no 6930 n bits 104 index PRIMARY of table `optovision`.`cora06` trx id 79730951688 lock_mode X locks rec but not gap waiting Record lock, heap no 32 PHYSICAL RECORD: n_fields 16; compact format; info bits 0 0: len 4; hex 8014bf1d; asc ;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 4; hex 80000000; asc ;; 4: len 4; hex 80c96659; asc fY;; 5: len 4; hex 81823a37; asc :7;; 6: len 6; hex 800000002e00; asc . ;; 7: SQL NULL; 8: len 3; hex 800000; asc ;; 9: SQL NULL; 10: SQL NULL; 11: SQL NULL; 12: SQL NULL; 13: SQL NULL; 14: SQL NULL; 15: SQL NULL; *** (2) TRANSACTION: TRANSACTION 79730951348, ACTIVE 15 sec fetching rows mysql tables in use 4, locked 4 15055 lock struct(s), heap size 1925240, 1965175 row lock(s), undo log entries 56 MySQL thread id 8879406, OS thread handle 139470652933888, query id 13903208628 172.16.185.22 sa Sending data # Auftragsrabatt, Auftragseingang gestern insert into w_checksale01(vorfallcode,`Vorfall`, `Prio`) SELECT '1005',concat('Auftragsrabatt Cort01.cot1rbtb-Auftragsrabatt >cot1ndel: ',COUNT(*)) as ErrorMsg, case when COUNT(*)=0 then 'OK' ELSE ' cora06 und cort01.cot1ndel und cort02 checken' END FROM corh01 JOIN cort01 ON cot1orid=co1orid LEFT JOIN cora06 ON coa6ohid=COT1orid AND coa6opid=COT1opid WHERE co1medt BETWEEN @datv AND @datb and abs(COT1rbtb)-ABS(round(COT1rbtb*coalesce(coa6odis,0)/100,2))