===================================== 2019-12-08 01:43:38 7f07f4dfb700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 20201496 srv_active, 0 srv_shutdown, 4218642 srv_idle srv_master_thread log flush and writes: 24420137 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 75618509 --Thread 139672445486848 has waited at ibuf0ibuf.cc line 4685 for 1.0000 seconds the semaphore: X-lock (wait_ex) on RW-latch at 0x7f0841bc4e40 '&block->lock' a writer (thread id 139672445486848) has reserved it in mode wait exclusive number of readers 1, waiters flag 0, lock_word: ffffffffffffffff Last time read locked in file buf0flu.cc line 1073 Last time write locked in file /home/buildbot/buildbot/build/storage/xtradb/ibuf/ibuf0ibuf.cc line 4956 OS WAIT ARRAY INFO: signal count 65685389 Mutex spin waits 1226159267, rounds 1164244169, OS waits 19225086 RW-shared spins 60637986, rounds 1572859787, OS waits 48783303 RW-excl spins 10340967, rounds 314512628, OS waits 7214559 Spin rounds per wait: 0.95 mutex, 25.94 RW-shared, 30.41 RW-excl ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2019-03-18 15:39:40 7f07dc724700 Transaction: TRANSACTION 552495226, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1184, 1 row lock(s) MySQL thread id 236922, OS thread handle 0x7f07dc724700, query id 32387733 150.206.12.105 tworld update INSERT INTO TARGET (TARGET_GROUP_NO, SVC_MNG_NO, TID, CHILD_YN) VALUES ('4151', 7261434070, '', 'N') Foreign key constraint fails for table "MTPUSHDB"."TARGET": , CONSTRAINT "FK_TARGET_GROUP_TO_TARGET" FOREIGN KEY ("TARGET_GROUP_NO") REFERENCES "TARGET_GROUP" ("TARGET_GROUP_NO") ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index "PRIMARY" tuple: DATA TUPLE: 8 fields; 0: len 8; hex 8000000000001037; asc 7;; 1: len 8; hex 80000001b0d0b0d6; asc ;; 2: len 6; hex 000020ee687a; asc hz;; 3: len 7; hex 850001e9883314; asc 3 ;; 4: len 0; hex ; asc ;; 5: len 1; hex 4e; asc N;; 6: len 8; hex 8000000006b37477; asc tw;; 7: SQL NULL; But in parent table "MTPUSHDB"."TARGET_GROUP", in index "PRIMARY", the closest match we can find is record: PHYSICAL RECORD: n_fields 13; compact format; info bits 0 0: len 8; hex 8000000000001033; asc 3;; 1: len 6; hex 000020ee650b; asc e ;; 2: len 7; hex d60001802c012a; asc , *;; 3: len 6; hex 444952454354; asc DIRECT;; 4: len 8; hex 8000000000001378; asc x;; 5: len 30; hex ecbd9ceba781ed948ceb9facec8aa420ec8381ed92882041707020507573; asc App Pus; (total 34 bytes); 6: len 7; hex 31313130383932; asc 1110892;; 7: len 8; hex 3230313930333138; asc 20190318;; 8: len 6; hex 313533363433; asc 153643;; 9: len 4; hex 8007a120; asc ;; 10: len 4; hex 8007955f; asc _;; 11: SQL NULL; 12: SQL NULL; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2019-05-14 01:05:45 7f07e71b5700 *** (1) TRANSACTION: TRANSACTION 595784947, ACTIVE 14 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 721847, OS thread handle 0x7f07e4324700, query id 191950563 150.206.12.90 tworld update INSERT INTO FCM_TOKEN ( TID, DEVICE_TOKEN, UUID, MDN, OS_TYPE, USE_YN, REG_DATE ) VALUES ( 'kjkj3366@naver.com', 'dU9zxa-kZ8g:APA91bEs448-d2a6gM_SpEptBRQKHloAiTP6DsB9dITmqUmR_-hML4qGfo32_JO_HIeJbkJsA85tqduQY1CS1Nw-Z5lxAkc2wf2hBktKmtlKTTM8XkEDH6UF-MpQuGtbYLWnpngB y8ZL', 'ECAC8C77-02F2-4876-85E8-073F7DAA250F', NULL, '03', 'Y', NOW() ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2199 page no 34154 n bits 536 index "TID" of table "MTPUSHDB"."FCM_TOKEN" trx table locks 1 total table locks 10 trx id 595784947 lock_mode X locks ga p before rec insert intention waiting lock hold time 0 wait time before grant 0 *** (2) TRANSACTION: TRANSACTION 595784991, ACTIVE 5 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1 MySQL thread id 731191, OS thread handle 0x7f07e71b5700, query id 191950753 150.206.12.89 tworld update INSERT INTO FCM_TOKEN ( TID, DEVICE_TOKEN, UUID, MDN, OS_TYPE, USE_YN, REG_DATE ) VALUES ( 'kjkj3366@naver.com', 'dU9zxa-kZ8g:APA91bEs448-d2a6gM_SpEptBRQKHloAiTP6DsB9dITmqUmR_-hML4qGfo32_JO_HIeJbkJsA85tqduQY1CS1Nw-Z5lxAkc2wf2hBktKmtlKTTM8XkEDH6UF-MpQuGtbYLWnpngB y8ZL', 'ECAC8C77-02F2-4876-85E8-073F7DAA250F', NULL, '03', 'Y', NOW() ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 2199 page no 34154 n bits 536 index "TID" of table "MTPUSHDB"."FCM_TOKEN" trx table locks 1 total table locks 10 trx id 595784991 lock mode S locks ga p before rec lock hold time 0 wait time before grant 0 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 2199 page no 34154 n bits 536 index "TID" of table "MTPUSHDB"."FCM_TOKEN" trx table locks 1 total table locks 10 trx id 595784991 lock_mode X locks ga p before rec insert intention waiting lock hold time 0 wait time before grant 0 *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 814810528 Purge done for trx's n:o < 814810527 undo n:o < 0 state: running but idle History list length 1961 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 814801820, not started MySQL thread id 2687412, OS thread handle 0x7f07e62db700, query id 1069056768 150.206.12.106 tworld cleaning up ---TRANSACTION 814805263, not started MySQL thread id 2687399, OS thread handle 0x7f079264b700, query id 1069070406 150.206.12.106 tworld cleaning up ---TRANSACTION 814803114, not started MySQL thread id 2687260, OS thread handle 0x7f07e256e700, query id 1069061900 150.206.12.89 tworld cleaning up ---TRANSACTION 814803671, not started MySQL thread id 2687239, OS thread handle 0x7f08415b6700, query id 1069064161 150.206.12.105 tworld cleaning up ---TRANSACTION 814800011, not started MySQL thread id 2680301, OS thread handle 0x7f07dc600700, query id 1069049639 150.206.12.90 tworld cleaning up ---TRANSACTION 814796097, not started MySQL thread id 2679529, OS thread handle 0x7f0792801700, query id 1069034122 150.206.12.89 tworld cleaning up ---TRANSACTION 814805814, not started MySQL thread id 2678842, OS thread handle 0x7f0885092700, query id 1069072668 150.206.12.89 tworld cleaning up ---TRANSACTION 814810502, not started MySQL thread id 2678841, OS thread handle 0x7f0896d23700, query id 1069090392 150.206.12.105 tworld cleaning up ---TRANSACTION 814810485, not started MySQL thread id 2678467, OS thread handle 0x7f089c891700, query id 1069090345 150.206.12.105 tworld cleaning up ---TRANSACTION 814810099, not started MySQL thread id 2672744, OS thread handle 0x7f07e256e700, query id 1069088948 150.206.12.89 tworld cleaning up ---TRANSACTION 814810081, not started MySQL thread id 2672742, OS thread handle 0x7f08415b6700, query id 1069088906 150.206.12.106 tworld cleaning up ---TRANSACTION 814810510, not started MySQL thread id 2672513, OS thread handle 0x7f0896d23700, query id 1069090413 150.206.12.89 tworld cleaning up ---TRANSACTION 814810462, not started MySQL thread id 2671003, OS thread handle 0x7f089c891700, query id 1069090298 150.206.12.90 tworld cleaning up ---TRANSACTION 814800005, not started MySQL thread id 2669088, OS thread handle 0x7f07f06d0700, query id 1069049625 150.206.12.90 tworld cleaning up ---TRANSACTION 814810422, not started MySQL thread id 2669087, OS thread handle 0x7f079264b700, query id 1069090185 150.206.12.105 tworld cleaning up ---TRANSACTION 814810200, not started MySQL thread id 2660979, OS thread handle 0x7f079264b700, query id 1069089393 150.206.12.106 tworld cleaning up ---TRANSACTION 814810509, not started MySQL thread id 2651936, OS thread handle 0x7f07e256e700, query id 1069090406 150.206.12.90 tworld cleaning up ---TRANSACTION 814810518, not started MySQL thread id 2650310, OS thread handle 0x7f08415b6700, query id 1069090427 150.206.12.90 tworld cleaning up ---TRANSACTION 814798062, not started MySQL thread id 2643792, OS thread handle 0x7f07e256e700, query id 1069042243 150.206.12.90 tworld cleaning up ---TRANSACTION 814810486, not started MySQL thread id 2643435, OS thread handle 0x7f07e6292700, query id 1069090380 150.206.12.90 tworld cleaning up ---TRANSACTION 814809706, not started MySQL thread id 2624963, OS thread handle 0x7f07e6292700, query id 1069087360 150.206.12.89 tworld cleaning up ---TRANSACTION 814810454, not started MySQL thread id 2624524, OS thread handle 0x7f07e256e700, query id 1069090306 150.206.12.90 tworld cleaning up ---TRANSACTION 814810492, not started MySQL thread id 2604111, OS thread handle 0x7f07e6292700, query id 1069090377 150.206.12.90 tworld cleaning up ---TRANSACTION 814808924, not started MySQL thread id 2535493, OS thread handle 0x7f07e2493700, query id 1069084422 150.206.12.105 tworld cleaning up ---TRANSACTION 814809436, not started MySQL thread id 38, OS thread handle 0x7f08415b6700, query id 1069086579 150.19.42.85 tworld cleaning up ---TRANSACTION 814810505, not started MySQL thread id 7, OS thread handle 0x7f07e72d9700, query id 1069090430 closing tables ALTER TABLE ICAS_CUSTOMER_NEW RENAME ICAS_CUSTOMER ---TRANSACTION 814810500, not started MySQL thread id 4, OS thread handle 0x7f07e736b700, query id 1069090428 committed 258025776 ---TRANSACTION 814810507, not started MySQL thread id 6, OS thread handle 0x7f07e7322700, query id 1069090401 committed 258025774 ---TRANSACTION 544710915, not started MySQL thread id 3, OS thread handle 0x7f07e7dfe700, query id 0 Waiting for background binlog tasks ---TRANSACTION 814810527, ACTIVE 1 sec renaming table 14 lock struct(s), heap size 2936, 13 row lock(s), undo log entries 4 MySQL thread id 7, OS thread handle 0x7f07e72d9700, query id 1069090430 closing tables ALTER TABLE ICAS_CUSTOMER_NEW RENAME ICAS_CUSTOMER Trx #rec lock waits 0 #table lock waits 0 Trx total rec lock wait time 0 SEC Trx total table lock wait time 0 SEC TABLE LOCK table "SYS_TABLES" trx id 814810527 lock mode IX lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 8 n bits 200 index "CLUST_IND" of table "SYS_TABLES" trx table locks 5 total table locks 1 trx id 814810527 lock_mode X lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 8 n bits 200 index "CLUST_IND" of table "SYS_TABLES" trx table locks 5 total table locks 1 trx id 814810527 lock_mode X locks gap before rec lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 9 n bits 352 index "ID_IND" of table "SYS_TABLES" trx table locks 5 total table locks 1 trx id 814810527 lock_mode X locks rec but not gap lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 9 n bits 352 index "ID_IND" of table "SYS_TABLES" trx table locks 5 total table locks 1 trx id 814810527 lock mode S lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 9 n bits 352 index "ID_IND" of table "SYS_TABLES" trx table locks 5 total table locks 1 trx id 814810527 lock mode S locks gap before rec lock hold time 1 wait time before grant 0 TABLE LOCK table "SYS_TABLESPACES" trx id 814810527 lock mode IX lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 307 n bits 296 index "SYS_TABLESPACES_SPACE" of table "SYS_TABLESPACES" trx table locks 5 total table locks 1 trx id 814810527 lock_mode X l ock hold time 1 wait time before grant 0 TABLE LOCK table "SYS_DATAFILES" trx id 814810527 lock mode IX lock hold time 1 wait time before grant 0 RECORD LOCKS space id 0 page no 308 n bits 328 index "SYS_DATAFILES_SPACE" of table "SYS_DATAFILES" trx table locks 5 total table locks 1 trx id 814810527 lock_mode X lock hold time 1 wait time before grant 0 TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---TRANSACTION 814810521, ACTIVE 1 sec 1 lock struct(s), heap size 360, 0 row lock(s), undo log entries 1 MySQL thread id 1, OS thread handle 0x7f0898bb4700, query id 1069090429 applied write set 258025779 Trx #rec lock waits 37328 #table lock waits 0 Trx total rec lock wait time 199 SEC Trx total table lock wait time 0 SEC TABLE LOCK table "MTPUSHDB"."FCM_TOKEN" trx id 814810521 lock mode IX lock hold time 1 wait time before grant 0