If it helps, here’s the dump from MariaDB:
| InnoDB | |
=====================================
2020-11-12 14:24:04 77b4 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 58 seconds
BACKGROUND THREAD
srv_master_thread loops: 1379 srv_active, 0 srv_shutdown, 185217 srv_idle
srv_master_thread log flush and writes: 186596
SEMAPHORES
OS WAIT ARRAY INFO: reservation count 6847
OS WAIT ARRAY INFO: signal count 7865
Mutex spin waits 106082, rounds 456253, OS waits 2562
RW-shared spins 4162, rounds 105368, OS waits 3315
RW-excl spins 2136, rounds 34209, OS waits 719
Spin rounds per wait: 4.30 mutex, 25.32 RW-shared, 16.02 RW-excl
LATEST FOREIGN KEY ERROR
2020-11-12 10:37:45 2a88 Transaction:
TRANSACTION 4706735, ACTIVE 66 sec inserting
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 145
MySQL thread id 240, OS thread handle 0x2a88, query id 584704 localhost 127.0.0.1 root update
insert into outagemanagement792.ACT_RU_EXECUTION
(
ID_,
PROC_INST_ID_,
BUSINESS_KEY_,
PROC_DEF_ID_,
ACT_ID_,
ACT_INST_ID_,
IS_ACTIVE_,
IS_CONCURRENT_,
IS_SCOPE_,
IS_EVENT_SCOPE_,
PARENT_ID_,
SUPER_EXEC_,
SUPER_CASE_EXEC_,
CASE_INST_ID_,
SUSPENSION_STATE_,
CACHED_ENT_STATE_,
SEQUENCE_COUNTER_,
TENANT_ID_,
REV_
)
values
(
‘02ceb883-24fd-11eb-800b-c8d9d2a6236e’,
‘59481736-24fb-11eb-b675-c8d9d2a6236e’,
NULL,
'MRPollerService:3:bf59a8fa-24fa-11eb-
Foreign key constraint fails for table outagemanagement792.act_ru_execution:
,
CONSTRAINT ACT_FK_EXE_PROCINST FOREIGN KEY (PROC_INST_ID_) REFERENCES act_ru_execution (ID_) ON DELETE CASCADE ON UPDATE CASCADE
Trying to add in child table, in index ACT_FK_EXE_PROCINST tuple:
DATA TUPLE: 2 fields;
0: len 36; hex 35393438313733362d323466622d313165622d623637352d633864396432613632333665; asc 59481736-24fb-11eb-b675-c8d9d2a6236e;;
1: len 36; hex 30326365623838332d323466642d313165622d383030622d633864396432613632333665; asc 02ceb883-24fd-11eb-800b-c8d9d2a6236e;;
But in parent table outagemanagement792.act_ru_execution, in index PRIMARY,
the closest match we can find is record:
PHYSICAL RECORD: n_fields 21; compact format; info bits 32
0: len 30; hex 35393438313733362d323466622d313165622d623637352d633864396432; asc 59481736-24fb-11eb-b675-c8d9d2; (total 36 bytes);
1: len 6; hex 00000047d1f2; asc G ;;
2: len 7; hex 5c0000401c022e; asc \ @ .;;
3: len 4; hex 80000009; asc ;;
4: len 30; hex 35393438313733362d323466622d313165622d623637352d633864396432; asc 59481736-24fb-11eb-b675-c8d9d2; (total 36 bytes);
5: SQL NULL;
6: SQL NULL;
7: len 30; hex 4d52506f6c6c6572536572766963653a333a62663539613866612d323466; asc MRPollerService:3:bf59a8fa-24f; (total 54 bytes);
8: len 30; hex 35393437376166342d323466622d313165622d623637352d633864396432; asc 59477af4-24fb-11eb-b675-c8d9d2; (total 36 bytes);
9: SQL NULL;
10: SQL NULL;
11: SQL NULL;
12: len 30; hex 35393438313733362d323466622d313165622d623637352d633864396432; asc 59481736-24fb-11eb-b675-c8d9d2; (total 36 bytes);
13: len 1; hex 80; asc ;;
14: len 1; hex 80; asc ;;
15: len 1; hex 81; asc ;;
16: len 1; hex 80; asc ;;
17: len 4; hex 80000001; asc ;;
18: len 4; hex 80000010; asc ;;
19: len 8; hex 80000000000000f4; asc ;;
20: SQL NULL;
TRANSACTIONS
Trx id counter 4709918
Purge done for trx’s n:o < 4709700 undo n:o < 0 state: running but idle
History list length 337
LIST OF TRANSACTIONS FOR EACH SESSION:
—TRANSACTION 4709917, not started
MySQL thread id 325, OS thread handle 0x77b4, query id 610782 localhost 127.0.0.1 root cleaning up
—TRANSACTION 4709913, not started
MySQL thread id 323, OS thread handle 0x6600, query id 610762 localhost 127.0.0.1 root cleaning up
—TRANSACTION 0, not started
MySQL thread id 138, OS thread handle 0x77b4, query id 610783 localhost ::1 root init
show engine innodb status
—TRANSACTION 4704184, not started
MySQL thread id 40, OS thread handle 0x5dac, query id 554607 localhost ::1 root cleaning up
—TRANSACTION 4704076, not started
MySQL thread id 4, OS thread handle 0x1d04, query id 554252 localhost ::1 root cleaning up
—TRANSACTION 0, not started
MySQL thread id 3, OS thread handle 0x1d04, query id 554257 localhost ::1 root cleaning up
—TRANSACTION 4709907, ACTIVE 81 sec
MySQL thread id 324, OS thread handle 0x6600, query id 610690 localhost 127.0.0.1 root cleaning up
Trx read view will not see trx with id >= 4709908, sees < 4709695
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
—TRANSACTION 4709796, ACTIVE 685 sec
MySQL thread id 322, OS thread handle 0x6600, query id 609752 localhost 127.0.0.1 root cleaning up
Trx read view will not see trx with id >= 4709797, sees < 4709599
Trx #rec lock waits 0 #table lock waits 0
Trx total rec lock wait time 0 SEC
Trx total table lock wait time 0 SEC
FILE I/O
I/O thread 0 state: native aio handle (insert buffer thread)
I/O thread 1 state: native aio handle (log thread)
I/O thread 2 state: native aio handle (read thread)
I/O thread 3 state: native aio handle (read thread)
I/O thread 4 state: native aio handle (read thread)
I/O thread 5 state: native aio handle (read thread)
I/O thread 6 state: native aio handle (write thread)
I/O thread 7 state: native aio handle (write thread)
I/O thread 8 state: native aio handle (write thread)
I/O thread 9 state: native aio handle (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o’s: 0, sync i/o’s: 0
Pending flushes (fsync) log: 0; buffer pool: 0
6695 OS file reads, 140804 OS file writes, 22428 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 4.21 writes/s, 0.48 fsyncs/s
INSERT BUFFER AND ADAPTIVE HASH INDEX
Ibuf: size 1, free list len 23529, seg size 23531, 1675 merges
merged operations:
insert 10880, delete mark 10, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
1.00 hash searches/s, 15.67 non-hash searches/s
LOG
Log sequence number 126587694483
Log flushed up to 126587694483
Pages flushed up to 126587694483
Last checkpoint at 126587694483
Max checkpoint age 84223550
Checkpoint age target 81591565
Modified age 0
Checkpoint age 0
0 pending log writes, 0 pending chkp writes
10488 log i/o’s done, 0.09 log i/o’s/second
BUFFER POOL AND MEMORY
Total memory allocated 1059717120; in additional pool allocated 0
Total memory allocated by read views 47168
Internal hash tables (constant factor + variable factor)
Adaptive hash index 30222864 (18110888 + 1559085240424)
Page hash 1133032 (buffer pool 0 only)
Dictionary cache 10122148 (9057232 + 1064916)
File system 1074416 (812272 + 262144)
Lock system 2545976 (2543096 + 2880)
Recovery system 0 (0 + 0)
Dictionary memory allocated 1064916
Buffer pool size 62719
Buffer pool size, bytes 1027588096
Free buffers 2701
Database pages 59279
Old database pages 21862
Modified db pages 0
Percent of dirty pages(LRU & free pages): 0.000
Max dirty pages percent: 75.000
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 346, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 6097, created 53182, written 124199
0.00 reads/s, 0.17 creates/s, 4.02 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 59279, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
ROW OPERATIONS
0 queries inside InnoDB, 0 queries in queue
2 read views open inside InnoDB
2 RW transactions active inside InnoDB
0 RO transactions active inside InnoDB
2 out of 1000 descriptors used
—OLDEST VIEW—
Normal read view
Read view low limit trx n:o 4709797
Read view up limit trx id 4709599
Read view low limit trx id 4709797
Read view individually stored trx ids:
Read view trx id 4709599
Read view trx id 4709695
Main thread id 7796, state: sleeping
Number of rows inserted 560055, updated 1852, deleted 3232, read 937132
0.81 inserts/s, 0.00 updates/s, 0.00 deletes/s, 5.00 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 0
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
END OF INNODB MONITOR OUTPUT
|
±-------±-----±--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
MariaDB [(none)]> show engine innodb status;