===================================== 180821 1:48:33 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450907, signal count 5414216 --Thread 139956265826048 has waited at row0ins.c line 2033 for 814.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467740, rounds 8810490, OS waits 195116 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148071, rounds 5406037, OS waits 157275 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 9.35 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2152 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376526753 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 68.95 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B217D Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B217C, not started MySQL thread id 66506, OS thread handle 0x7f4cd84ec700, query id 210062431 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 814 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 814 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 814 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 2914 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 831.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 831.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 3, pwrites 0 ===================================== 180821 1:48:53 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450907, signal count 5414216 --Thread 139956265826048 has waited at row0ins.c line 2033 for 834.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467741, rounds 8810520, OS waits 195116 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148071, rounds 5406037, OS waits 157275 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2152 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376526753 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B217D Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B217C, not started MySQL thread id 66506, OS thread handle 0x7f4cd84ec700, query id 210062431 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 834 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 834 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 834 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 2934 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 180821 1:49:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450909, signal count 5414218 --Thread 139956265826048 has waited at row0ins.c line 2033 for 854.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467743, rounds 8810580, OS waits 195118 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148071, rounds 5406037, OS waits 157275 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 9.35 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2144 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376528133 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 69.00 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B2180 Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B217F, not started MySQL thread id 66507, OS thread handle 0x7f4cd8051700, query id 210062905 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 854 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 854 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 854 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 2954 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 862.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 862.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 3, pwrites 0 ===================================== 180821 1:49:33 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450911, signal count 5414220 --Thread 139956265826048 has waited at row0ins.c line 2033 for 874.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467744, rounds 8810610, OS waits 195119 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148072, rounds 5406067, OS waits 157276 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 9.35 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2144 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376529512 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 68.95 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B2182 Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B2181, not started MySQL thread id 66507, OS thread handle 0x7f4cd8051700, query id 210063361 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 874 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 874 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 874 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 2974 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 893.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 893.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 3, pwrites 0 ===================================== 180821 1:49:53 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450911, signal count 5414220 --Thread 139956265826048 has waited at row0ins.c line 2033 for 894.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467744, rounds 8810610, OS waits 195119 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148072, rounds 5406067, OS waits 157276 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2144 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376529512 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B2182 Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B2181, not started MySQL thread id 66507, OS thread handle 0x7f4cd8051700, query id 210063361 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 894 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 894 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 894 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 2994 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ ===================================== 180821 1:50:13 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450912, signal count 5414221 --Thread 139956265826048 has waited at row0ins.c line 2033 for 914.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467747, rounds 8810700, OS waits 195120 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148072, rounds 5406067, OS waits 157276 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 10.55 hash searches/s, 0.85 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2144 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376530921 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 70.45 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B2187 Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B2186, not started MySQL thread id 66508, OS thread handle 0x7f4cd84ec700, query id 210063873 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 914 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 914 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 914 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 3014 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 924.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: semaphore wait: --Thread 139956265826048 has waited at row0ins.c line 2033 for 924.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 3, pwrites 0 ===================================== 180821 1:50:33 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 5453872 1_second, 5453638 sleeps, 530731 10_second, 164629 background, 165435 flush srv_master_thread log flush and writes: 4820811 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 5450913, signal count 5414223 --Thread 139956265826048 has waited at row0ins.c line 2033 for 934.00 seconds the semaphore: X-lock on RW-latch at 0x7f4a4ab76a10 '&block->lock' a writer (thread id 139968020260608) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 4046 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 1467747, rounds 8810700, OS waits 195120 RW-shared spins 6670650, rounds 183969245, OS waits 5036552 RW-excl spins 148073, rounds 5406099, OS waits 157277 Spin rounds per wait: 6.00 mutex, 27.58 RW-shared, 36.51 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180821 1:00:00 Transaction: TRANSACTION 69B1FBB, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 66491, OS thread handle 0x7f4cd812c700, query id 210015724 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 0000069b1fbb; asc ;; 2: len 7; hex 60005480130e37; asc ` T 7;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 33 [0, 0, 0, 0, 0, 2, 31, 0, 0, 0, 0, 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 71429816 OS file reads, 72935534 OS file writes, 4539717 OS fsyncs 3 pending preads, 0 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 9, seg size 11, 50832 merges merged operations: insert 27751, delete mark 2835396, delete 727 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 3084 buffer(s) 9.35 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1223961507962 Log flushed up to 1223954310104 Last checkpoint at 1222723122002 Max checkpoint age 1738750649 Checkpoint age target 1684414692 Modified age 1120538677 Checkpoint age 1238385960 0 pending log writes, 0 pending chkp writes 32897050 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 11020533760; in additional pool allocated 0 Total memory allocated by read views 2144 Internal hash tables (constant factor + variable factor) Adaptive hash index 220531200 (169998728 + 50532472) Page hash 10625704 (buffer pool 0 only) Dictionary cache 54130625 (42501104 + 11629521) File system 83536 (82672 + 864) Lock system 32710672 (26563016 + 6147656) Recovery system 0 (0 + 0) Dictionary memory allocated 11629521 Buffer pool size 655359 Buffer pool size, bytes 10737401856 Free buffers 0 Database pages 651901 Old database pages 240623 Modified db pages 83483 Pending reads 3 Pending writes: LRU 0, flush list 126, single page 0 Pages made young 79139579, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 71426695, created 5156211, written 39158744 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 651901, 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 4 transactions active inside InnoDB 4 out of 1000 descriptors used ---OLDEST VIEW--- Normal read view Read view low limit trx n:o 69B2064 Read view up limit trx id 69B1F7F Read view low limit trx id 69B2064 Read view individually stored trx ids: Read view trx id 69B1F7F ----------------- Main thread process no. 3559, id 139956305295104, state: flushing log Number of rows inserted 42835424, updated 3175578, deleted 8803250, read 7376532300 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 68.95 reads/s ------------------------ LATEST DETECTED DEADLOCK ------------------------ 180618 17:21:08 *** (1) TRANSACTION: TRANSACTION 540D14B, ACTIVE 95 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 2756, OS thread handle 0x7f4cd820a700, query id 851315 192.168.246.11 obp updating delete from BatchStatus where batchJobId='001-20180618-171719-0004985' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540D14B lock_mode X locks rec but not gap waiting *** (2) TRANSACTION: TRANSACTION 540C954, ACTIVE 228 sec updating or deleting mysql tables in use 1, locked 1 305248 lock struct(s), heap size 27195832, 8682 row lock(s), undo log entries 4 MySQL thread id 2710, OS thread handle 0x7f4cd8535700, query id 855196 192.168.246.11 obp updating delete from BatchJob where id='001-20180618-171719-0004985' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock_mode X locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 835597 n bits 216 index `BatchStatus_IX1` of table `obs`.`BatchStatus` trx id 540C954 lock mode S waiting *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter 69B2189 Purge done for trx's n:o < 69B206E undo n:o < 0 History list length 2057 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 69B2188, not started MySQL thread id 66508, OS thread handle 0x7f4cd84ec700, query id 210064317 192.168.246.16 portal ---TRANSACTION 69B1FDC, not started MySQL thread id 66502, OS thread handle 0x7f4a1b823700, query id 210015861 192.168.246.11 obp ---TRANSACTION 69B208B, not started MySQL thread id 66489, OS thread handle 0x7f4cd8535700, query id 210021940 192.168.246.15 obp ---TRANSACTION 69B2089, not started MySQL thread id 66488, OS thread handle 0x7f4cd820a700, query id 210021930 192.168.246.15 obp ---TRANSACTION 69B206F, not started MySQL thread id 66476, OS thread handle 0x7f4cd820a700, query id 210021312 10.169.200.80 obp ---TRANSACTION 69B1F80, not started MySQL thread id 66241, OS thread handle 0x7f4cd812c700, query id 210015048 192.168.246.11 obp ---TRANSACTION 0, not started MySQL thread id 66109, OS thread handle 0x7f4cd8051700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69AC97D, not started MySQL thread id 66110, OS thread handle 0x7f4cd84ec700, query id 210061217 10.169.200.80 obp ---TRANSACTION 69B207F, not started MySQL thread id 65903, OS thread handle 0x7f4cd8535700, query id 210021876 192.168.246.11 obp ---TRANSACTION 69B2001, not started MySQL thread id 65790, OS thread handle 0x7f4a1b823700, query id 210016467 192.168.246.11 obp ---TRANSACTION 69B202C, not started MySQL thread id 65283, OS thread handle 0x7f4cd812c700, query id 210016978 192.168.246.11 obp ---TRANSACTION 69B2130, ACTIVE 934 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66353, OS thread handle 0x7f4cd8535700, query id 210049784 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '003-20180821-013500-0173292') ---TRANSACTION 69B212F, ACTIVE 934 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 58180, OS thread handle 0x7f4a1b823700, query id 210049783 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (NULL, NULL, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', NULL, NULL, NULL, NULL, 0, 0, NULL, NULL, 'SCHEDULED', NULL, 0, NULL, NULL, 'obpapd0102', 0, NULL, 'WAITING', NULL, NULL, '001-20180821-013500-6366234') ---TRANSACTION 69B212E, ACTIVE 934 sec inserting mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 66406, OS thread handle 0x7f4cd820a700, query id 210049779 192.168.246.11 obp update insert into BatchExecution (modifiedBy, modifiedOn, registeredBy, registeredOn, batchJobId, callbackResult, downlink, endedOn, errorCode, errorCount, errorIndex, errorMessage, executionDetail, executionType, parameter, readCount, requestId, retryId, serverId, skipCount, startedOn, status, statusInfo, uplink, id) values (null, null, '_ANONYMOUS_SUBJECT_ID______', '2018-08-21 01:35:00', '001-20180201-104634-0000217', null, null, null, null, 0, 0, null, null, 'SCHEDULED', null, 0, null, null, 'obpapd0101', 0, null, 'WAITING', null, null, '001-20180821-013500-0090783') ---TRANSACTION 69B1F7F, ACTIVE 3034 sec fetching rows mysql tables in use 3, locked 1 68867 lock struct(s), heap size 6142392, 940762 row lock(s), undo log entries 2295673 MySQL thread id 66477, OS thread handle 0x7f4cd812c700, query id 210019936 192.168.246.11 obp Sending data insert into TransactionLogContentBackup select B.* from TransactionLog A, TransactionLogContent B where A.id = B.transactionLogId and logCreatedOn >= '1970-01-01' and logCreatedOn < '2018-07-22' Trx read view will not see trx with id >= 69B2064, sees < 69B2064 ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Error: semaphore wait has lasted > 600 seconds InnoDB: We intentionally crash the server, because it appears to be hung. 180821 1:50:53 InnoDB: Assertion failure in thread 139956330473216 in file srv0srv.c line 2992 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html InnoDB: about forcing recovery. 180821 1:50:53 [ERROR] mysqld got signal 6 ; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. To report this bug, see http://kb.askmonty.org/en/reporting-bugs We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. Server version: 5.5.52-MariaDB key_buffer_size=67108864 read_buffer_size=4194304 max_used_connections=93 max_threads=513 thread_count=29 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4286321 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. Thread pointer: 0x0x0 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... stack_bottom = 0x0 thread_stack 0x48000 /usr/libexec/mysqld(my_print_stacktrace+0x3d)[0x7f4cde24c2dd] /usr/libexec/mysqld(handle_fatal_signal+0x515)[0x7f4cdde618d5] /lib64/libpthread.so.0(+0xf370)[0x7f4cdd592370] /lib64/libc.so.6(gsignal+0x37)[0x7f4cdbd411d7] /lib64/libc.so.6(abort+0x148)[0x7f4cdbd428c8] /usr/libexec/mysqld(+0x636cd7)[0x7f4cddff7cd7] /lib64/libpthread.so.0(+0x7dc5)[0x7f4cdd58adc5] /lib64/libc.so.6(clone+0x6d)[0x7f4cdbe0373d] The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. 180821 09:38:40 mysqld_safe A mysqld process already exists 180821 10:02:54 mysqld_safe Starting mysqld daemon with databases from /Data/mysql 180821 10:02:54 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 3617 ... 180821 10:02:54 [Warning] Could not increase number of max_open_files to more than 1024 (request: 3083) 180821 10:02:55 InnoDB: The InnoDB memory heap is disabled 180821 10:02:55 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180821 10:02:55 InnoDB: Compressed tables use zlib 1.2.7 180821 10:02:55 InnoDB: Using Linux native AIO 180821 10:02:55 InnoDB: Initializing buffer pool, size = 10.0G 180821 10:02:55 InnoDB: Completed initialization of buffer pool 180821 10:02:56 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1222723122002 180821 10:02:56 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 1222728364544 InnoDB: Doing recovery: scanned up to log sequence number 1222733607424 InnoDB: Doing recovery: scanned up to log sequence number 1222738850304 InnoDB: Doing recovery: scanned up to log sequence number 1222744093184 InnoDB: Doing recovery: scanned up to log sequence number 1222749336064 InnoDB: Doing recovery: scanned up to log sequence number 1222754578944 InnoDB: Doing recovery: scanned up to log sequence number 1222759821824 InnoDB: Doing recovery: scanned up to log sequence number 1222765064704 InnoDB: Doing recovery: scanned up to log sequence number 1222770307584 InnoDB: Doing recovery: scanned up to log sequence number 1222775550464 InnoDB: Doing recovery: scanned up to log sequence number 1222780793344 InnoDB: Doing recovery: scanned up to log sequence number 1222786036224 InnoDB: Doing recovery: scanned up to log sequence number 1222791279104 InnoDB: Doing recovery: scanned up to log sequence number 1222796521984 InnoDB: Doing recovery: scanned up to log sequence number 1222801764864 InnoDB: Doing recovery: scanned up to log sequence number 1222807007744 InnoDB: Doing recovery: scanned up to log sequence number 1222812250624 InnoDB: Doing recovery: scanned up to log sequence number 1222817493504 InnoDB: Doing recovery: scanned up to log sequence number 1222822736384 InnoDB: Doing recovery: scanned up to log sequence number 1222827979264 InnoDB: Doing recovery: scanned up to log sequence number 1222833222144 InnoDB: Doing recovery: scanned up to log sequence number 1222838465024 InnoDB: Doing recovery: scanned up to log sequence number 1222843707904 InnoDB: Doing recovery: scanned up to log sequence number 1222848950784 InnoDB: Doing recovery: scanned up to log sequence number 1222854193664 InnoDB: Doing recovery: scanned up to log sequence number 1222859436544 InnoDB: Doing recovery: scanned up to log sequence number 1222864679424 InnoDB: Doing recovery: scanned up to log sequence number 1222869922304 InnoDB: Doing recovery: scanned up to log sequence number 1222875165184 InnoDB: Doing recovery: scanned up to log sequence number 1222880408064 InnoDB: Doing recovery: scanned up to log sequence number 1222885650944 InnoDB: Doing recovery: scanned up to log sequence number 1222890893824 InnoDB: Doing recovery: scanned up to log sequence number 1222896136704 InnoDB: Doing recovery: scanned up to log sequence number 1222901379584 InnoDB: Doing recovery: scanned up to log sequence number 1222906622464 InnoDB: Doing recovery: scanned up to log sequence number 1222911865344 InnoDB: Doing recovery: scanned up to log sequence number 1222917108224 InnoDB: Doing recovery: scanned up to log sequence number 1222922351104 InnoDB: Doing recovery: scanned up to log sequence number 1222927593984 InnoDB: Doing recovery: scanned up to log sequence number 1222932836864 InnoDB: Doing recovery: scanned up to log sequence number 1222938079744 InnoDB: Doing recovery: scanned up to log sequence number 1222943322624 InnoDB: Doing recovery: scanned up to log sequence number 1222948565504 InnoDB: Doing recovery: scanned up to log sequence number 1222953808384 InnoDB: Doing recovery: scanned up to log sequence number 1222959051264 InnoDB: Doing recovery: scanned up to log sequence number 1222964294144 InnoDB: Doing recovery: scanned up to log sequence number 1222969537024 InnoDB: Doing recovery: scanned up to log sequence number 1222974779904 InnoDB: Doing recovery: scanned up to log sequence number 1222980022784 InnoDB: Doing recovery: scanned up to log sequence number 1222985265664 InnoDB: Doing recovery: scanned up to log sequence number 1222990508544 InnoDB: Doing recovery: scanned up to log sequence number 1222995751424 InnoDB: Doing recovery: scanned up to log sequence number 1223000994304 InnoDB: Doing recovery: scanned up to log sequence number 1223006237184 InnoDB: Doing recovery: scanned up to log sequence number 1223011480064 InnoDB: Doing recovery: scanned up to log sequence number 1223016722944 InnoDB: Doing recovery: scanned up to log sequence number 1223021965824 InnoDB: Doing recovery: scanned up to log sequence number 1223027208704 InnoDB: Doing recovery: scanned up to log sequence number 1223032451584 InnoDB: Doing recovery: scanned up to log sequence number 1223037694464 InnoDB: Doing recovery: scanned up to log sequence number 1223042937344 InnoDB: Doing recovery: scanned up to log sequence number 1223048180224 InnoDB: Doing recovery: scanned up to log sequence number 1223053423104 InnoDB: Doing recovery: scanned up to log sequence number 1223058665984 InnoDB: Doing recovery: scanned up to log sequence number 1223063908864 InnoDB: Doing recovery: scanned up to log sequence number 1223069151744 InnoDB: Doing recovery: scanned up to log sequence number 1223074394624 InnoDB: Doing recovery: scanned up to log sequence number 1223079637504 InnoDB: Doing recovery: scanned up to log sequence number 1223084880384 InnoDB: Doing recovery: scanned up to log sequence number 1223090123264 InnoDB: Doing recovery: scanned up to log sequence number 1223095366144 InnoDB: Doing recovery: scanned up to log sequence number 1223100609024 InnoDB: Doing recovery: scanned up to log sequence number 1223105851904 InnoDB: Doing recovery: scanned up to log sequence number 1223111094784 InnoDB: Doing recovery: scanned up to log sequence number 1223116337664 InnoDB: Doing recovery: scanned up to log sequence number 1223121580544 InnoDB: Doing recovery: scanned up to log sequence number 1223126823424 InnoDB: Doing recovery: scanned up to log sequence number 1223132066304 InnoDB: Doing recovery: scanned up to log sequence number 1223137309184 InnoDB: Doing recovery: scanned up to log sequence number 1223142552064 InnoDB: Doing recovery: scanned up to log sequence number 1223147794944 InnoDB: Doing recovery: scanned up to log sequence number 1223153037824 InnoDB: Doing recovery: scanned up to log sequence number 1223158280704 InnoDB: Doing recovery: scanned up to log sequence number 1223163523584 InnoDB: Doing recovery: scanned up to log sequence number 1223168766464 InnoDB: Doing recovery: scanned up to log sequence number 1223174009344 InnoDB: Doing recovery: scanned up to log sequence number 1223179252224 InnoDB: Doing recovery: scanned up to log sequence number 1223184495104 InnoDB: Doing recovery: scanned up to log sequence number 1223189737984 InnoDB: Doing recovery: scanned up to log sequence number 1223194980864 InnoDB: Doing recovery: scanned up to log sequence number 1223200223744 InnoDB: Doing recovery: scanned up to log sequence number 1223205466624 InnoDB: Doing recovery: scanned up to log sequence number 1223210709504 InnoDB: Doing recovery: scanned up to log sequence number 1223215952384 InnoDB: Doing recovery: scanned up to log sequence number 1223221195264 InnoDB: Doing recovery: scanned up to log sequence number 1223226438144 InnoDB: Doing recovery: scanned up to log sequence number 1223231681024 InnoDB: Doing recovery: scanned up to log sequence number 1223236923904 InnoDB: Doing recovery: scanned up to log sequence number 1223242166784 InnoDB: Doing recovery: scanned up to log sequence number 1223247409664 InnoDB: Doing recovery: scanned up to log sequence number 1223252652544 InnoDB: Doing recovery: scanned up to log sequence number 1223257895424 InnoDB: Doing recovery: scanned up to log sequence number 1223263138304 InnoDB: Doing recovery: scanned up to log sequence number 1223268381184 InnoDB: Doing recovery: scanned up to log sequence number 1223273624064 InnoDB: Doing recovery: scanned up to log sequence number 1223278866944 InnoDB: Doing recovery: scanned up to log sequence number 1223284109824 InnoDB: Doing recovery: scanned up to log sequence number 1223289352704 InnoDB: Doing recovery: scanned up to log sequence number 1223294595584 InnoDB: Doing recovery: scanned up to log sequence number 1223299838464 InnoDB: Doing recovery: scanned up to log sequence number 1223305081344 InnoDB: Doing recovery: scanned up to log sequence number 1223310324224 InnoDB: Doing recovery: scanned up to log sequence number 1223315567104 InnoDB: Doing recovery: scanned up to log sequence number 1223320809984 InnoDB: Doing recovery: scanned up to log sequence number 1223326052864 InnoDB: Doing recovery: scanned up to log sequence number 1223331295744 InnoDB: Doing recovery: scanned up to log sequence number 1223336538624 InnoDB: Doing recovery: scanned up to log sequence number 1223341781504 InnoDB: Doing recovery: scanned up to log sequence number 1223347024384 InnoDB: Doing recovery: scanned up to log sequence number 1223352267264 InnoDB: Doing recovery: scanned up to log sequence number 1223357510144 InnoDB: Doing recovery: scanned up to log sequence number 1223362753024 InnoDB: Doing recovery: scanned up to log sequence number 1223367995904 InnoDB: Doing recovery: scanned up to log sequence number 1223373238784 InnoDB: Doing recovery: scanned up to log sequence number 1223378481664 InnoDB: Doing recovery: scanned up to log sequence number 1223383724544 InnoDB: Doing recovery: scanned up to log sequence number 1223388967424 InnoDB: Doing recovery: scanned up to log sequence number 1223394210304 InnoDB: Doing recovery: scanned up to log sequence number 1223399453184 InnoDB: Doing recovery: scanned up to log sequence number 1223404696064 InnoDB: Doing recovery: scanned up to log sequence number 1223409938944 InnoDB: Doing recovery: scanned up to log sequence number 1223415181824 InnoDB: Doing recovery: scanned up to log sequence number 1223420424704 InnoDB: Doing recovery: scanned up to log sequence number 1223425667584 InnoDB: Doing recovery: scanned up to log sequence number 1223430910464 InnoDB: Doing recovery: scanned up to log sequence number 1223436153344 InnoDB: Doing recovery: scanned up to log sequence number 1223441396224 InnoDB: Doing recovery: scanned up to log sequence number 1223446639104 InnoDB: Doing recovery: scanned up to log sequence number 1223451881984 InnoDB: Doing recovery: scanned up to log sequence number 1223457124864 InnoDB: Doing recovery: scanned up to log sequence number 1223462367744 InnoDB: Doing recovery: scanned up to log sequence number 1223467610624 InnoDB: Doing recovery: scanned up to log sequence number 1223472853504 InnoDB: Doing recovery: scanned up to log sequence number 1223478096384 InnoDB: Doing recovery: scanned up to log sequence number 1223483339264 InnoDB: Doing recovery: scanned up to log sequence number 1223488582144 InnoDB: Doing recovery: scanned up to log sequence number 1223493825024 InnoDB: Doing recovery: scanned up to log sequence number 1223499067904 InnoDB: Doing recovery: scanned up to log sequence number 1223504310784 InnoDB: Doing recovery: scanned up to log sequence number 1223509553664 InnoDB: Doing recovery: scanned up to log sequence number 1223514796544 InnoDB: Doing recovery: scanned up to log sequence number 1223520039424 InnoDB: Doing recovery: scanned up to log sequence number 1223525282304 InnoDB: Doing recovery: scanned up to log sequence number 1223530525184 InnoDB: Doing recovery: scanned up to log sequence number 1223535768064 InnoDB: Doing recovery: scanned up to log sequence number 1223541010944 InnoDB: Doing recovery: scanned up to log sequence number 1223546253824 InnoDB: Doing recovery: scanned up to log sequence number 1223551496704 InnoDB: Doing recovery: scanned up to log sequence number 1223556739584 InnoDB: Doing recovery: scanned up to log sequence number 1223561982464 InnoDB: Doing recovery: scanned up to log sequence number 1223567225344 InnoDB: Doing recovery: scanned up to log sequence number 1223572468224 InnoDB: Doing recovery: scanned up to log sequence number 1223577711104 InnoDB: Doing recovery: scanned up to log sequence number 1223582953984 InnoDB: Doing recovery: scanned up to log sequence number 1223588196864 InnoDB: Doing recovery: scanned up to log sequence number 1223593439744 InnoDB: Doing recovery: scanned up to log sequence number 1223598682624 InnoDB: Doing recovery: scanned up to log sequence number 1223603925504 InnoDB: Doing recovery: scanned up to log sequence number 1223609168384 InnoDB: Doing recovery: scanned up to log sequence number 1223614411264 InnoDB: Doing recovery: scanned up to log sequence number 1223619654144 InnoDB: Doing recovery: scanned up to log sequence number 1223624897024 InnoDB: Doing recovery: scanned up to log sequence number 1223630139904 InnoDB: Doing recovery: scanned up to log sequence number 1223635382784 InnoDB: Doing recovery: scanned up to log sequence number 1223640625664 InnoDB: Doing recovery: scanned up to log sequence number 1223645868544 InnoDB: Doing recovery: scanned up to log sequence number 1223651111424 InnoDB: Doing recovery: scanned up to log sequence number 1223656354304 InnoDB: Doing recovery: scanned up to log sequence number 1223661597184 InnoDB: Doing recovery: scanned up to log sequence number 1223666840064 InnoDB: Doing recovery: scanned up to log sequence number 1223672082944 InnoDB: Doing recovery: scanned up to log sequence number 1223677325824 InnoDB: Doing recovery: scanned up to log sequence number 1223682568704 InnoDB: Doing recovery: scanned up to log sequence number 1223687811584 InnoDB: Doing recovery: scanned up to log sequence number 1223693054464 InnoDB: Doing recovery: scanned up to log sequence number 1223698297344 InnoDB: Doing recovery: scanned up to log sequence number 1223703540224 InnoDB: Doing recovery: scanned up to log sequence number 1223708783104 InnoDB: Doing recovery: scanned up to log sequence number 1223714025984 InnoDB: Doing recovery: scanned up to log sequence number 1223719268864 InnoDB: Doing recovery: scanned up to log sequence number 1223724511744 InnoDB: Doing recovery: scanned up to log sequence number 1223729754624 InnoDB: Doing recovery: scanned up to log sequence number 1223734997504 InnoDB: Doing recovery: scanned up to log sequence number 1223740240384 InnoDB: Doing recovery: scanned up to log sequence number 1223745483264 InnoDB: Doing recovery: scanned up to log sequence number 1223750726144 InnoDB: Doing recovery: scanned up to log sequence number 1223755969024 InnoDB: Doing recovery: scanned up to log sequence number 1223761211904 InnoDB: Doing recovery: scanned up to log sequence number 1223766454784 InnoDB: Doing recovery: scanned up to log sequence number 1223771697664 InnoDB: Doing recovery: scanned up to log sequence number 1223776940544 InnoDB: Doing recovery: scanned up to log sequence number 1223782183424 InnoDB: Doing recovery: scanned up to log sequence number 1223787426304 InnoDB: Doing recovery: scanned up to log sequence number 1223792669184 InnoDB: Doing recovery: scanned up to log sequence number 1223797912064 InnoDB: Doing recovery: scanned up to log sequence number 1223803154944 InnoDB: Doing recovery: scanned up to log sequence number 1223808397824 InnoDB: Doing recovery: scanned up to log sequence number 1223813640704 InnoDB: Doing recovery: scanned up to log sequence number 1223818883584 InnoDB: Doing recovery: scanned up to log sequence number 1223824126464 InnoDB: Doing recovery: scanned up to log sequence number 1223829369344 InnoDB: Doing recovery: scanned up to log sequence number 1223834612224 InnoDB: Doing recovery: scanned up to log sequence number 1223839855104 InnoDB: Doing recovery: scanned up to log sequence number 1223845097984 InnoDB: Doing recovery: scanned up to log sequence number 1223850340864 InnoDB: Doing recovery: scanned up to log sequence number 1223855583744 InnoDB: Doing recovery: scanned up to log sequence number 1223860826624 InnoDB: Doing recovery: scanned up to log sequence number 1223866069504 InnoDB: Doing recovery: scanned up to log sequence number 1223871312384 InnoDB: Doing recovery: scanned up to log sequence number 1223876555264 InnoDB: Doing recovery: scanned up to log sequence number 1223881798144 InnoDB: Doing recovery: scanned up to log sequence number 1223887041024 InnoDB: Doing recovery: scanned up to log sequence number 1223892283904 InnoDB: Doing recovery: scanned up to log sequence number 1223897526784 InnoDB: Doing recovery: scanned up to log sequence number 1223902769664 InnoDB: Doing recovery: scanned up to log sequence number 1223908012544 InnoDB: Doing recovery: scanned up to log sequence number 1223913255424 InnoDB: Doing recovery: scanned up to log sequence number 1223918498304 InnoDB: Doing recovery: scanned up to log sequence number 1223923741184 InnoDB: Doing recovery: scanned up to log sequence number 1223928984064 InnoDB: Doing recovery: scanned up to log sequence number 1223934226944 InnoDB: Doing recovery: scanned up to log sequence number 1223939469824 InnoDB: Doing recovery: scanned up to log sequence number 1223944712704 InnoDB: Doing recovery: scanned up to log sequence number 1223949955584 InnoDB: Doing recovery: scanned up to log sequence number 1223954310104 InnoDB: 1 transaction(s) which must be rolled back or cleaned up InnoDB: in total 2292889 row operations to undo InnoDB: Trx id counter is 69B2200 180821 10:03:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 46001461, file name /Log/mariadb/mariadb-bin.000273 InnoDB: Starting in background the rollback of uncommitted transactions 180821 10:03:28 InnoDB: Rolling back trx with id 69B1F7F, 2292889 rows to undo InnoDB: Progress in percents: 1180821 10:03:28 InnoDB: Waiting for the background threads to start 2180821 10:03:29 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 1223954310104 180821 10:03:29 [Note] Plugin 'FEEDBACK' is disabled. 180821 10:03:29 [Note] Recovering after a crash using /Log/mariadb/mariadb-bin 180821 10:03:29 [Note] Starting crash recovery... 180821 10:03:29 [Note] Crash recovery finished. 180821 10:03:29 [Note] Server socket created on IP: '0.0.0.0'. 180821 10:03:29 [Warning] 'user' entry 'root@obpdbd01' ignored in --skip-name-resolve mode. 180821 10:03:29 [Warning] 'user' entry '@obpdbd01' ignored in --skip-name-resolve mode. 180821 10:03:29 [Warning] 'user' entry 'obp@localhost ; ' ignored in --skip-name-resolve mode. 180821 10:03:29 [Warning] 'db' entry 'obs_qa obp@localhost ; ' ignored in --skip-name-resolve mode. 180821 10:03:29 [Warning] 'proxies_priv' entry '@ root@obpdbd01' ignored in --skip-name-resolve mode. 180821 10:03:29 [Note] Event Scheduler: Loaded 0 events 180821 10:03:29 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.52-MariaDB' socket: '/Data/mysql/mysql.sock' port: 3306 MariaDB Server 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 InnoDB: Rolling back of trx id 69B1F7F completed 180821 10:05:52 InnoDB: Rollback of non-prepared transactions completed 180904 14:34:00 mysqld_safe Starting mysqld daemon with databases from /Data/mysql 180904 14:34:00 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 3632 ... 180904 14:34:00 [Warning] Could not increase number of max_open_files to more than 1024 (request: 3083) 180904 14:34:00 InnoDB: The InnoDB memory heap is disabled 180904 14:34:00 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180904 14:34:00 InnoDB: Compressed tables use zlib 1.2.7 180904 14:34:00 InnoDB: Using Linux native AIO 180904 14:34:00 InnoDB: Initializing buffer pool, size = 10.0G 180904 14:34:01 InnoDB: Completed initialization of buffer pool 180904 14:34:02 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1237654587195 180904 14:34:02 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 1237654625233 180904 14:34:04 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 159603364, file name /Log/mariadb/mariadb-bin.000316 180904 14:34:06 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 1237654625233 180904 14:34:06 [Note] Plugin 'FEEDBACK' is disabled. 180904 14:34:06 [Note] Recovering after a crash using /Log/mariadb/mariadb-bin 180904 14:34:06 [Note] Starting crash recovery... 180904 14:34:06 [Note] Crash recovery finished. 180904 14:34:06 [Note] Server socket created on IP: '0.0.0.0'. 180904 14:34:06 [Warning] 'user' entry 'root@obpdbd01' ignored in --skip-name-resolve mode. 180904 14:34:06 [Warning] 'user' entry '@obpdbd01' ignored in --skip-name-resolve mode. 180904 14:34:06 [Warning] 'user' entry 'obp@localhost ; ' ignored in --skip-name-resolve mode. 180904 14:34:06 [Warning] 'db' entry 'obs_qa obp@localhost ; ' ignored in --skip-name-resolve mode. 180904 14:34:06 [Warning] 'proxies_priv' entry '@ root@obpdbd01' ignored in --skip-name-resolve mode. 180904 14:34:06 [Note] Event Scheduler: Loaded 0 events 180904 14:34:06 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.52-MariaDB' socket: '/Data/mysql/mysql.sock' port: 3306 MariaDB Server 180915 13:53:43 mysqld_safe Starting mysqld daemon with databases from /Data/mysql 180915 13:53:43 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 3758 ... 180915 13:53:43 [Warning] Could not increase number of max_open_files to more than 1024 (request: 3083) 180915 13:53:43 InnoDB: The InnoDB memory heap is disabled 180915 13:53:43 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180915 13:53:43 InnoDB: Compressed tables use zlib 1.2.7 180915 13:53:43 InnoDB: Using Linux native AIO 180915 13:53:43 InnoDB: Initializing buffer pool, size = 10.0G 180915 13:53:43 InnoDB: Completed initialization of buffer pool 180915 13:53:45 InnoDB: highest supported file format is Barracuda. InnoDB: The log sequence number in ibdata files does not match InnoDB: the log sequence number in the ib_logfiles! 180915 13:53:45 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Last MySQL binlog file position 0 71466696, file name /Log/mariadb/mariadb-bin.000333 180915 13:53:47 InnoDB: Waiting for the background threads to start 180915 13:53:48 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 1244789499295 180915 13:53:48 [Note] Plugin 'FEEDBACK' is disabled. 180915 13:53:48 [Note] Recovering after a crash using /Log/mariadb/mariadb-bin 180915 13:53:49 [Note] Starting crash recovery... 180915 13:53:49 [Note] Crash recovery finished. 180915 13:53:49 [Note] Server socket created on IP: '0.0.0.0'. 180915 13:53:49 [Warning] 'user' entry 'root@obpdbd01' ignored in --skip-name-resolve mode. 180915 13:53:49 [Warning] 'user' entry '@obpdbd01' ignored in --skip-name-resolve mode. 180915 13:53:49 [Warning] 'user' entry 'obp@localhost ; ' ignored in --skip-name-resolve mode. 180915 13:53:49 [Warning] 'db' entry 'obs_qa obp@localhost ; ' ignored in --skip-name-resolve mode. 180915 13:53:49 [Warning] 'proxies_priv' entry '@ root@obpdbd01' ignored in --skip-name-resolve mode. 180915 13:53:49 [Note] Event Scheduler: Loaded 0 events 180915 13:53:49 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.52-MariaDB' socket: '/Data/mysql/mysql.sock' port: 3306 MariaDB Server InnoDB: Warning: a long semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 241.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 241.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868415744 has waited at log0log.ic line 320 for 240.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755899193088 has waited at log0log.c line 1803 for 240.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867219712 has waited at btr0cur.c line 261 for 238.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867518720 has waited at btr0cur.c line 261 for 237.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867817728 has waited at btr0cur.c line 261 for 237.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866621696 has waited at btr0cur.c line 261 for 235.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865724672 has waited at log0log.ic line 320 for 87.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866023680 has waited at btr0cur.c line 261 for 84.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 239.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865126656 has waited at btr0pcur.c line 429 for 82.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba78b2cf0 '&block->lock' a writer (thread id 139755865724672) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3130 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755865724672 has waited at log0log.ic line 320 for 87.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864827648 has waited at log0log.ic line 320 for 57.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864528640 has waited at row0ins.c line 2033 for 1.0000 seconds the semaphore: X-lock on RW-latch at 0x7f1b9b5d95d0 '&block->lock' a writer (thread id 139755864827648) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.c line 1340 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755864827648 has waited at log0log.ic line 320 for 57.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 1 ===================================== 180917 10:49:57 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 24 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 102467 1_second, 102467 sleeps, 9424 10_second, 8346 background, 8346 flush srv_master_thread log flush and writes: 114175 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 100277, signal count 100165 --Thread 139755907585792 has waited at srv0srv.c line 3617 for 255.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755868415744 has waited at log0log.ic line 320 for 254.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755899193088 has waited at log0log.c line 1803 for 254.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755868116736 has waited at log0log.ic line 320 for 253.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755867219712 has waited at btr0cur.c line 261 for 252.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755867518720 has waited at btr0cur.c line 261 for 251.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755867817728 has waited at btr0cur.c line 261 for 251.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755866621696 has waited at btr0cur.c line 261 for 249.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755865724672 has waited at log0log.ic line 320 for 101.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755866023680 has waited at btr0cur.c line 261 for 98.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755865126656 has waited at btr0pcur.c line 429 for 96.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba78b2cf0 '&block->lock' a writer (thread id 139755865724672) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3130 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 --Thread 139755864827648 has waited at log0log.ic line 320 for 71.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 --Thread 139755864528640 has waited at row0ins.c line 2033 for 15.000 seconds the semaphore: X-lock on RW-latch at 0x7f1b9b5d95d0 '&block->lock' a writer (thread id 139755864827648) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.c line 1340 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 Mutex spin waits 8125, rounds 134199, OS waits 3663 RW-shared spins 107702, rounds 3129714, OS waits 96080 RW-excl spins 287, rounds 8734, OS waits 232 Spin rounds per wait: 16.52 mutex, 29.06 RW-shared, 30.43 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 180917 0:59:59 Transaction: TRANSACTION 6EEAA34, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1 MySQL thread id 895, OS thread handle 0x7f1b72d22700, query id 2480793 192.168.246.16 portal updating delete from portal_user where id=35 Foreign key constraint fails for table `portal`.`portal_approval`: , CONSTRAINT `FK_1um8l4rle2khpeps7movfc3td` FOREIGN KEY (`requester_id`) REFERENCES `portal_user` (`id`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 31 fields; 0: len 8; hex 8000000000000023; asc #;; 1: len 6; hex 000006eeaa34; asc 4;; 2: len 7; hex 200026003b2daf; asc & ;- ;; 3: len 13; hex 616e6f6e796d6f757355736572; asc anonymousUser;; 4: len 8; hex 8000125855ee3256; asc XU 2V;; 5: len 22; hex 6f62705f6d616e61676572406b6a62616e6b2e636f6d; asc obp_manager@kjbank.com;; 6: len 8; hex 8000125aa544e379; asc Z D y;; 7: SQL NULL; 8: len 24; hex 494957313545326b78453865523873594a324b4f71513d3d; asc IIW15E2kxE8eR8sYJ2KOqQ==;; 9: len 1; hex 01; asc ;; 10: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 11: len 9; hex ec9db4ec9d80ec9881; asc ;; 12: len 6; hex ed9689ec9b90; asc ;; 13: len 1; hex 00; asc ;; 14: len 1; hex 00; asc ;; 15: len 2; hex 6b6f; asc ko;; 16: len 44; hex 537a692f73632b373865384c6b3339594d424252754b6534755534674a38554a3159417471694b415331453d; asc Szi/sc+78e8Lk39YMBBRuKe4uU4gJ8UJ1YAtqiKAS1E=;; 17: SQL NULL; 18: len 88; hex 564c4d726a374e4131426d4735626f44493237646e6c62584757444a4a435345656b3064676e5177624c42613162453864525266365574614269316b4a455a726a572b6d466e65304b53747a71753754306469384b413d3d; asc VLMrj7NA1BmG5boDI27dnlbXGWDJJCSEek0dgnQwbLBa1bE8dRRf6UtaBi1kJEZrjW+mFne0KStzqu7T0di8KA==;; 19: len 8; hex 800012586df4a84e; asc Xm N;; 20: len 20; hex 3632383734393433323539393533363739353338; asc 62874943259953679538;; 21: len 1; hex 00; asc ;; 22: len 8; hex 494e414354495645; asc INACTIVE;; 23: SQL NULL; 24: len 20; hex 524f4c455f50524f56494445525f4d454d424552; asc ROLE_PROVIDER_MEMBER;; 25: len 1; hex 00; asc ;; 26: len 8; hex 800000000000000f; asc ;; 27: SQL NULL; 28: SQL NULL; 29: SQL NULL; 30: len 4; hex 80000000; asc ;; But in child table `portal`.`portal_approval`, in index `FK_1um8l4rle2khpeps7movfc3td`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 8; hex 8000000000000023; asc #;; 1: len 8; hex 8000000000000037; asc 7;; -------- FILE I/O -------- I/O thread 0 state: waiting for completed aio requests (insert buffer thread) I/O thread 1 state: waiting for completed aio requests (log thread) I/O thread 2 state: waiting for completed aio requests (read thread) I/O thread 3 state: waiting for completed aio requests (read thread) I/O thread 4 state: waiting for completed aio requests (read thread) I/O thread 5 state: waiting for completed aio requests (read thread) I/O thread 6 state: waiting for completed aio requests (read thread) I/O thread 7 state: waiting for completed aio requests (read thread) I/O thread 8 state: waiting for completed aio requests (read thread) I/O thread 9 state: waiting for completed aio requests (read thread) I/O thread 10 state: waiting for completed aio requests (read thread) I/O thread 11 state: waiting for completed aio requests (read thread) I/O thread 12 state: waiting for completed aio requests (read thread) I/O thread 13 state: waiting for completed aio requests (read thread) I/O thread 14 state: waiting for completed aio requests (read thread) I/O thread 15 state: waiting for completed aio requests (read thread) I/O thread 16 state: waiting for completed aio requests (read thread) I/O thread 17 state: waiting for completed aio requests (read thread) I/O thread 18 state: waiting for completed aio requests (write thread) I/O thread 19 state: waiting for completed aio requests (write thread) I/O thread 20 state: waiting for completed aio requests (write thread) I/O thread 21 state: waiting for completed aio requests (write thread) I/O thread 22 state: waiting for completed aio requests (write thread) I/O thread 23 state: waiting for completed aio requests (write thread) I/O thread 24 state: waiting for completed aio requests (write thread) I/O thread 25 state: waiting for completed aio requests (write thread) I/O thread 26 state: waiting for completed aio requests (write thread) I/O thread 27 state: waiting for completed aio requests (write thread) I/O thread 28 state: waiting for completed aio requests (write thread) I/O thread 29 state: waiting for completed aio requests (write thread) I/O thread 30 state: waiting for completed aio requests (write thread) I/O thread 31 state: waiting for completed aio requests (write thread) I/O thread 32 state: waiting for completed aio requests (write thread) I/O thread 33 state: waiting for completed aio requests (write thread) Pending normal aio reads: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 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 801953 OS file reads, 379048 OS file writes, 21283 OS fsyncs 0 pending preads, 1 pending pwrites 0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 5, seg size 7, 48 merges merged operations: insert 89, delete mark 2, delete 0 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249841, node heap has 25 buffer(s) 1.37 hash searches/s, 0.62 non-hash searches/s --- LOG --- InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 272.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755868415744 has waited at log0log.ic line 320 for 271.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755899193088 has waited at log0log.c line 1803 for 271.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755867219712 has waited at btr0cur.c line 261 for 269.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755867518720 has waited at btr0cur.c line 261 for 268.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755867817728 has waited at btr0cur.c line 261 for 268.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755866621696 has waited at btr0cur.c line 261 for 266.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 272.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868415744 has waited at log0log.ic line 320 for 271.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755899193088 has waited at log0log.c line 1803 for 271.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867219712 has waited at btr0cur.c line 261 for 269.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867518720 has waited at btr0cur.c line 261 for 268.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867817728 has waited at btr0cur.c line 261 for 268.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866621696 has waited at btr0cur.c line 261 for 266.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865724672 has waited at log0log.ic line 320 for 118.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866023680 has waited at btr0cur.c line 261 for 115.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865126656 has waited at btr0pcur.c line 429 for 113.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba78b2cf0 '&block->lock' a writer (thread id 139755865724672) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3130 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755865724672 has waited at log0log.ic line 320 for 118.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864827648 has waited at log0log.ic line 320 for 88.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864528640 has waited at row0ins.c line 2033 for 32.000 seconds the semaphore: X-lock on RW-latch at 0x7f1b9b5d95d0 '&block->lock' a writer (thread id 139755864827648) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.c line 1340 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755864827648 has waited at log0log.ic line 320 for 88.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755924371200 has waited at log0log.c line 3794 for 17.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865425664 has waited at srv0srv.c line 2412 for 9.0000 seconds the semaphore: Mutex at 0x7f1e359b4d60 '&srv_innodb_monitor_mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755863332608 has waited at btr0cur.c line 261 for 2.0000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 270.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 1 InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 303.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755868415744 has waited at log0log.ic line 320 for 302.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755899193088 has waited at log0log.c line 1803 for 302.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: a long semaphore wait: --Thread 139755867219712 has waited at btr0cur.c line 261 for 300.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755867518720 has waited at btr0cur.c line 261 for 299.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755867817728 has waited at btr0cur.c line 261 for 299.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: a long semaphore wait: --Thread 139755866621696 has waited at btr0cur.c line 261 for 297.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: semaphore wait: --Thread 139755907585792 has waited at srv0srv.c line 3617 for 303.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868415744 has waited at log0log.ic line 320 for 302.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755899193088 has waited at log0log.c line 1803 for 302.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867219712 has waited at btr0cur.c line 261 for 300.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867518720 has waited at btr0cur.c line 261 for 299.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755867817728 has waited at btr0cur.c line 261 for 299.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866621696 has waited at btr0cur.c line 261 for 297.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865724672 has waited at log0log.ic line 320 for 149.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755866023680 has waited at btr0cur.c line 261 for 146.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865126656 has waited at btr0pcur.c line 429 for 144.00 seconds the semaphore: S-lock on RW-latch at 0x7f1ba78b2cf0 '&block->lock' a writer (thread id 139755865724672) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0sel.c line 3130 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755865724672 has waited at log0log.ic line 320 for 149.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864827648 has waited at log0log.ic line 320 for 119.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755864528640 has waited at row0ins.c line 2033 for 63.000 seconds the semaphore: X-lock on RW-latch at 0x7f1b9b5d95d0 '&block->lock' a writer (thread id 139755864827648) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file buf0flu.c line 1340 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0ins.c line 2033 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755864827648 has waited at log0log.ic line 320 for 119.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755924371200 has waited at log0log.c line 3794 for 48.000 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755865425664 has waited at srv0srv.c line 2412 for 40.000 seconds the semaphore: Mutex at 0x7f1e359b4d60 '&srv_innodb_monitor_mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755863332608 has waited at btr0cur.c line 261 for 33.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: Warning: semaphore wait: --Thread 139755861538560 has waited at btr0cur.c line 261 for 18.000 seconds the semaphore: S-lock on RW-latch at 0x7f1ba721f360 '&block->lock' a writer (thread id 139755868116736) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file btr0sea.c line 947 Last time write locked in file /builddir/build/BUILD/mariadb-5.5.52/storage/xtradb/row/row0upd.c line 2177 InnoDB: Warning: Writer thread is waiting this semaphore: --Thread 139755868116736 has waited at log0log.ic line 320 for 301.00 seconds the semaphore: Mutex at 0x7f1e46e1a188 '&log_sys->mutex', lock var 1 waiters flag 1 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 1 InnoDB: ###### Diagnostic info printed to the standard error stream 180917 11:47:06 mysqld_safe Starting mysqld daemon with databases from /Data/mysql 180917 11:47:06 [Note] /usr/libexec/mysqld (mysqld 5.5.52-MariaDB) starting as process 3715 ... 180917 11:47:06 [Warning] Could not increase number of max_open_files to more than 1024 (request: 3083) 180917 11:47:06 InnoDB: The InnoDB memory heap is disabled 180917 11:47:06 InnoDB: Mutexes and rw_locks use GCC atomic builtins 180917 11:47:06 InnoDB: Compressed tables use zlib 1.2.7 180917 11:47:06 InnoDB: Using Linux native AIO 180917 11:47:06 InnoDB: Initializing buffer pool, size = 10.0G 180917 11:47:07 InnoDB: Completed initialization of buffer pool 180917 11:47:08 InnoDB: highest supported file format is Barracuda. InnoDB: Log scan progressed past the checkpoint lsn 1245287920107 180917 11:47:08 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... InnoDB: Doing recovery: scanned up to log sequence number 1245293162496 InnoDB: Doing recovery: scanned up to log sequence number 1245295493277 180917 11:47:10 InnoDB: Starting an apply batch of log records to the database... InnoDB: Progress in percents: 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 InnoDB: Apply batch completed InnoDB: Last MySQL binlog file position 0 163719656, file name /Log/mariadb/mariadb-bin.000335 180917 11:47:11 InnoDB: Waiting for the background threads to start 180917 11:47:12 Percona XtraDB (http://www.percona.com) 5.5.49-MariaDB-38.0 started; log sequence number 1245295493277 180917 11:47:12 [Note] Plugin 'FEEDBACK' is disabled. 180917 11:47:12 [Note] Recovering after a crash using /Log/mariadb/mariadb-bin 180917 11:47:13 [Note] Starting crash recovery... 180917 11:47:13 [Note] Crash recovery finished. 180917 11:47:13 [Note] Server socket created on IP: '0.0.0.0'. 180917 11:47:13 [ERROR] mysqld: Table './mysql/user' is marked as crashed and should be repaired 180917 11:47:13 [Warning] Checking table: './mysql/user' 180917 11:47:13 [ERROR] mysql.user: 1 client is using or hasn't closed the table properly 180917 11:47:13 [ERROR] mysqld: Table './mysql/db' is marked as crashed and should be repaired 180917 11:47:13 [Warning] Checking table: './mysql/db' 180917 11:47:13 [ERROR] mysql.db: 1 client is using or hasn't closed the table properly 180917 11:47:13 [Warning] 'user' entry 'root@obpdbd01' ignored in --skip-name-resolve mode. 180917 11:47:13 [Warning] 'user' entry '@obpdbd01' ignored in --skip-name-resolve mode. 180917 11:47:13 [Warning] 'user' entry 'obp@localhost ; ' ignored in --skip-name-resolve mode. 180917 11:47:13 [Warning] 'db' entry 'obs_qa obp@localhost ; ' ignored in --skip-name-resolve mode. 180917 11:47:13 [Warning] 'proxies_priv' entry '@ root@obpdbd01' ignored in --skip-name-resolve mode. 180917 11:47:13 [Note] Event Scheduler: Loaded 0 events 180917 11:47:13 [Note] /usr/libexec/mysqld: ready for connections. Version: '5.5.52-MariaDB' socket: '/Data/mysql/mysql.sock' port: 3306 MariaDB Server