Storage engines

InnoDB

Supports transactions, row-level locking, foreign keys and encryption for tables

[ Variabel | Penampungan Penyangga | Status InnoDB ]

=====================================
2026-04-01 23:22:24 0x12e8 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 55 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 297924 srv_active, 0 srv_shutdown, 832574 srv_idle
srv_master_thread log flush and writes: 1130495
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2039549
OS WAIT ARRAY INFO: signal count 1683049
RW-shared spins 2753899, rounds 10708116, OS waits 284542
RW-excl spins 149784, rounds 650531, OS waits 14347
RW-sx spins 5262, rounds 46160, OS waits 753
Spin rounds per wait: 3.89 RW-shared, 4.34 RW-excl, 8.77 RW-sx
------------------------
LATEST FOREIGN KEY ERROR
------------------------
2026-04-01 14:56:40 0x810 Transaction:
TRANSACTION 964211, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4999
mysql tables in use 347, locked 347
537 lock struct(s), heap size 57464, 313 row lock(s), undo log entries 32
MySQL thread id 10436983, OS thread handle 2064, query id 57749818 DESKTOP-BFN02VV 192.168.10.190 root Updating
delete from reg_periksa where no_rawat='2026/04/01/000136'
Foreign key constraint fails for table `sikrssi`.`rawat_jl_pr`:
,
  CONSTRAINT `rawat_jl_pr_ibfk_8` FOREIGN KEY (`no_rawat`) REFERENCES `reg_periksa` (`no_rawat`) ON UPDATE CASCADE
Trying to delete in parent table, in index PRIMARY tuple:
DATA TUPLE: 21 fields;
 0: len 17; hex 323032362f30342f30312f303030313336; asc 2026/04/01/000136;;
 1: len 6; hex 0000000eb673; asc      s;;
 2: len 7; hex 760000082a1f17; asc v   *  ;;
 3: len 3; hex 303037; asc 007;;
 4: len 3; hex 8fd481; asc    ;;
 5: len 3; hex 80c708; asc    ;;
 6: len 13; hex 32303230303230333130313134; asc 2020020310114;;
 7: len 6; hex 303737373538; asc 077758;;
 8: len 5; hex 4947444b20; asc IGDK ;;
 9: len 5; hex 4645425249; asc FEBRI;;
 10: len 52; hex 4b4550454e5548414e2053454a4154492c204b4f54412054454e4741482c204b4550454e5548414e2c20524f4b414e2048554c55; asc KEPENUHAN SEJATI, KOTA TENGAH, KEPENUHAN, ROKAN HULU;;
 11: len 4; hex 414e414b; asc ANAK;;
 12: len 8; hex 0000000000000000; asc         ;;
 13: len 1; hex 03; asc  ;;
 14: len 1; hex 02; asc  ;;
 15: len 1; hex 01; asc  ;;
 16: len 3; hex 42504a; asc BPJ;;
 17: len 4; hex 80000044; asc    D;;
 18: len 1; hex 01; asc  ;;
 19: len 1; hex 02; asc  ;;
 20: len 1; hex 01; asc  ;;

But in child table `sikrssi`.`rawat_jl_pr`, in index PRIMARY, there is a record:
PHYSICAL RECORD: n_fields 14; compact format; info bits 0
 0: len 17; hex 323032362f30342f30312f303030313336; asc 2026/04/01/000136;;
 1: len 8; hex 544a323630303939; asc TJ260099;;
 2: len 13; hex 32303232303730363130313939; asc 2022070610199;;
 3: len 3; hex 8fd481; asc    ;;
 4: len 3; hex 80c816; asc    ;;
 5: len 6; hex 000000000000; asc       ;;
 6: len 7; hex 80000000000000; asc        ;;
 7: len 8; hex 000000000088c340; asc        @;;
 8: len 8; hex 0000000000000000; asc         ;;
 9: len 8; hex 0000000000000000; asc         ;;
 10: len 8; hex 0000000000000000; asc         ;;
 11: len 8; hex 0000000000000000; asc         ;;
 12: len 8; hex 000000000088c340; asc        @;;
 13: len 1; hex 02; asc  ;;

------------------------
LATEST DETECTED DEADLOCK
------------------------
2026-03-31 14:37:03 0xeac
*** (1) TRANSACTION:
TRANSACTION 874390, ACTIVE 11 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1128, 28 row lock(s), undo log entries 31
MySQL thread id 9584949, OS thread handle 4456, query id 51547215 DESKTOP-M0DDQM1 192.168.10.6 root Update
insert into jurnal values('JR20260331000873','2026/03/31/000223','2026-03-31','14:36:52','U','PEMBATALAN PEMERIKSAAN LABORAT RAWAT JALAN PASIEN OLEH 2023100220284')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 375 page no 62370 n bits 112 index PRIMARY of table `sikrssi`.`jurnal` trx id 874390 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 16; hex 4a523236353630383033303030303031; asc JR26560803000001;;
 1: len 6; hex 000000000000; asc       ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 17; hex 323031392f30352f30382f303030303931; asc 2019/05/08/000091;;
 4: len 3; hex 94c103; asc    ;;
 5: len 3; hex 800000; asc    ;;
 6: len 1; hex 01; asc  ;;
 7: len 30; hex 50454d42455249414e204f42415420524157415420494e41502050415349; asc PEMBERIAN OBAT RAWAT INAP PASI; (total 58 bytes);

*** (2) TRANSACTION:
TRANSACTION 874407, ACTIVE 9 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
13 lock struct(s), heap size 1128, 5 row lock(s), undo log entries 29
MySQL thread id 9820332, OS thread handle 3756, query id 51547584 DESKTOP-E1A9E6K 192.168.10.62 root Update
insert into jurnal values('JR20260331000873','2026/03/31/000037','2026-03-31','14:36:54','U','PIUTANG PASIEN RAWAT JALAN 2026/03/31/000037 118540 BP. RONI PASRAH, DIPOSTING OLEH 2024110520352')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 375 page no 62370 n bits 112 index PRIMARY of table `sikrssi`.`jurnal` trx id 874407 lock mode S locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 16; hex 4a523236353630383033303030303031; asc JR26560803000001;;
 1: len 6; hex 000000000000; asc       ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 17; hex 323031392f30352f30382f303030303931; asc 2019/05/08/000091;;
 4: len 3; hex 94c103; asc    ;;
 5: len 3; hex 800000; asc    ;;
 6: len 1; hex 01; asc  ;;
 7: len 30; hex 50454d42455249414e204f42415420524157415420494e41502050415349; asc PEMBERIAN OBAT RAWAT INAP PASI; (total 58 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 375 page no 62370 n bits 112 index PRIMARY of table `sikrssi`.`jurnal` trx id 874407 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 8; compact format; info bits 0
 0: len 16; hex 4a523236353630383033303030303031; asc JR26560803000001;;
 1: len 6; hex 000000000000; asc       ;;
 2: len 7; hex 80000000000000; asc        ;;
 3: len 17; hex 323031392f30352f30382f303030303931; asc 2019/05/08/000091;;
 4: len 3; hex 94c103; asc    ;;
 5: len 3; hex 800000; asc    ;;
 6: len 1; hex 01; asc  ;;
 7: len 30; hex 50454d42455249414e204f42415420524157415420494e41502050415349; asc PEMBERIAN OBAT RAWAT INAP PASI; (total 58 bytes);

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 1005432
Purge done for trx's n:o < 1005432 undo n:o < 0 state: running but idle
History list length 0
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 281970589075240, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589071064, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589054360, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589037656, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589033480, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589025128, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589029304, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589016776, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589008424, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970588995896, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589020952, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589096120, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589004248, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970588991720, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589137880, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589087768, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589142056, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589108648, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589062712, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589041832, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589012600, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589104472, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589396792, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589363384, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589091944, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589284040, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589321624, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589204696, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589121176, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589046008, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589050184, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589225576, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589066888, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589000072, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970589058536, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
---TRANSACTION 281970588987544, not started
0 lock struct(s), heap size 1128, 0 row lock(s)
--------
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] , aio writes: [0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
401445 OS file reads, 5889212 OS file writes, 3971073 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.42 writes/s, 0.35 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 321, seg size 323, 27281 merges
merged operations:
 insert 45561, delete mark 6333, delete 1450
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 8850487, node heap has 3691 buffer(s)
Hash table size 8850487, node heap has 3384 buffer(s)
Hash table size 8850487, node heap has 322 buffer(s)
Hash table size 8850487, node heap has 926 buffer(s)
Hash table size 8850487, node heap has 735 buffer(s)
Hash table size 8850487, node heap has 775 buffer(s)
Hash table size 8850487, node heap has 2258 buffer(s)
Hash table size 8850487, node heap has 697 buffer(s)
362.58 hash searches/s, 64.00 non-hash searches/s
---
LOG
---
Log sequence number 21334116857
Log flushed up to   21334116857
Pages flushed up to 21334116857
Last checkpoint at  21334116848
0 pending log flushes, 0 pending chkp writes
1318872 log i/o's done, 0.15 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 34896609280
Dictionary memory allocated 3044864
Buffer pool size   2054912
Free buffers       1622204
Database pages     419920
Old database pages 155131
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 2682, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 400357, created 19563, written 3491828
0.00 reads/s, 0.00 creates/s, 0.18 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: 419920, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   256864
Free buffers       202083
Database pages     53165
Old database pages 19645
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 364, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50758, created 2407, written 479550
0.00 reads/s, 0.00 creates/s, 0.00 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: 53165, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   256864
Free buffers       202990
Database pages     52227
Old database pages 19298
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 360, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 49656, created 2571, written 541644
0.00 reads/s, 0.00 creates/s, 0.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: 52227, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   256864
Free buffers       202703
Database pages     52578
Old database pages 19427
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 377, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 49824, created 2754, written 226204
0.00 reads/s, 0.00 creates/s, 0.00 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: 52578, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   256864
Free buffers       202972
Database pages     52340
Old database pages 19340
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 335, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50235, created 2105, written 713779
0.00 reads/s, 0.00 creates/s, 0.07 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: 52340, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   256864
Free buffers       204000
Database pages     51234
Old database pages 18929
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 384, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 48781, created 2453, written 601868
0.00 reads/s, 0.00 creates/s, 0.00 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: 51234, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   256864
Free buffers       202573
Database pages     52781
Old database pages 19481
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 297, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50391, created 2390, written 282835
0.00 reads/s, 0.00 creates/s, 0.00 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: 52781, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   256864
Free buffers       202706
Database pages     52571
Old database pages 19419
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 293, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50190, created 2381, written 319154
0.00 reads/s, 0.00 creates/s, 0.09 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: 52571, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   256864
Free buffers       202177
Database pages     53024
Old database pages 19592
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 272, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 50522, created 2502, written 326794
0.00 reads/s, 0.00 creates/s, 0.00 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: 53024, 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
0 read views open inside InnoDB
Process ID=1804, Main thread ID=3276, state: sleeping
Number of rows inserted 807138, updated 234543, deleted 38534, read 51961425284
0.04 inserts/s, 0.00 updates/s, 0.00 deletes/s, 9953.35 reads/s
Number of system rows inserted 0, updated 0, deleted 0, read 1887395
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================