Type Name Status InnoDB ===================================== 2023-04-14 10:42:20 0x7f46086de700 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 55 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 12504361 srv_active, 0 srv_shutdown, 4305 srv_idle srv_master_thread log flush and writes: 12508605 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 892364587 OS WAIT ARRAY INFO: signal count 64133697560 RW-shared spins 152548513557, rounds 237729945754, OS waits 673128733 RW-excl spins 4897775199, rounds 9369100104, OS waits 37273423 RW-sx spins 29336974, rounds 281614301, OS waits 1999630 Spin rounds per wait: 1.56 RW-shared, 1.91 RW-excl, 9.60 RW-sx ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2023-04-14 10:41:42 0x7f45b835a700 Transaction: TRANSACTION 54576880080, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 3 lock struct(s), heap size 1128, 1 row lock(s) MySQL thread id 3343436652, OS thread handle 139937419994880, query id 115645339651 58.76.184.32 ltcare Update INSERT INTO hompy_counter ( hpmfkey /* 홈페이지 FK */ , hpcdate /* 일자 */ , hpctcnt /* 방문횟수 */ ) VALUES ( '' /* 홈페이지 FK */ , '20230414' /* 일자 * Foreign key constraint fails for table `ltcare`.`hompy_counter`: , CONSTRAINT `hompy_counter_ibfk_1` FOREIGN KEY (`hpmfkey`) REFERENCES `hompy_master` (`hpmmgno`) ON DELETE CASCADE in parent table, in index PRIMARY tuple: DATA TUPLE: 5 fields; 0: len 4; hex 80000000; asc ;; 1: len 8; hex 3230323330343134; asc 20230414;; 2: len 6; hex 000cb50919d0; asc ;; 3: len 7; hex a90001400523f1; asc @ # ;; 4: len 4; hex 80000001; asc ;; But in parent table `ltcare`.`hompy_master`, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 24; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000000000; asc ;; 2: len 7; hex 80000000000000; asc ;; 3: len 4; hex 80000004; asc ;; 4: len 4; hex 80000001; asc ;; 5: len 26; hex ecbc80ec96b4ed8facec9a94ec9691ec9b9028eb8db0ebaaa829; asc ( );; 6: len 8; hex 3230313630313031; asc 20160101;; 7: len 8; hex 3230393931323331; asc 20991231;; 8: len 4; hex 64656d6f; asc demo;; 9: len 1; hex 31; asc 1;; 10: len 10; hex 31323038313537343635; asc 1208157465;; 11: len 0; hex ; asc ;; 12: len 11; hex 3332383230303030303539; asc 32820000059;; 13: len 0; hex ; asc ;; 14: len 1; hex 32; asc 2;; 15: len 2; hex 8002; asc ;; 16: len 2; hex 82a4; asc ;; 17: len 4; hex 20202020; asc ;; 18: len 1; hex 31; asc 1;; 19: len 4; hex 80000000; asc ;; 20: len 18; hex 73736b616e674068616e67616e672e636f6d; asc sskang@hangang.com;; 21: len 13; hex 3031302d373631382d35313834; asc 010-7618-5184;; 22: len 5; hex 9998e2e6f9; asc ;; 23: len 5; hex 99af8d0578; asc x;; ------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-04-14 10:19:31 0x7f44dff69700 *** (1) TRANSACTION: TRANSACTION 54576399096, ACTIVE 0 sec inserting mysql tables in use 4, locked 4 LOCK WAIT 15 lock struct(s), heap size 1128, 176 row lock(s), undo log entries 2 MySQL thread id 3342717313, OS thread handle 139937470985984, query id 115616479022 58.76.184.38 ltcare Sending data INSERT IGNORE INTO change_transport_driver ( ctmfkey , carfkey , ctddate , ctdmodk , ctdmodn , ctdafdk , ctdafdn ) SELECT a.ctmfkey , a.carmgno , '20230414' , a.cardrsk AS ctdmodk , (SELECT stmname FROM staff_master WHERE stmmgno = a.cardrsk) AS ctdmodn , a.cardrs2 AS ctdafdk , (SELECT stmname FROM staff_master WHERE stmmgno = a.cardrs2) AS ctdafdn FROM car_master a WHERE a.ctmfkey = '1714' AND a.carmgno = ' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 20304 page no 86939 n bits 200 index PRIMARY of table `ltcare`.`change_transport_driver` trx id 54576399096 lock mode S locks rec but not gap waiting Record lock, heap no 132 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 0: len 4; hex 800006b2; asc ;; 1: len 4; hex 80000543; asc C;; 2: len 8; hex 3230323330343134; asc 20230414;; 3: len 6; hex 000cb501c2ae; asc ;; 4: len 7; hex 7f0000013c07d5; asc < ;; 5: len 4; hex 80006b29; asc k);; 6: len 9; hex ecb59ceca095eab8b0; asc ;; 7: len 4; hex 800700b5; asc ;; 8: len 1; hex 53; asc S;; 9: len 9; hex ec9db4eab780eba180; asc ;; 10: len 4; hex 80006b29; asc k);; 11: len 9; hex ecb59ceca095eab8b0; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 0; hex ; asc ;; 17: len 4; hex 80000000; asc ;; 18: len 0; hex ; asc ;; 19: len 4; hex 80006b29; asc k);; 20: len 9; hex ecb59ceca095eab8b0; asc ;; 21: len 5; hex 99afc89a9f; asc ;; 22: len 5; hex 99afdca4df; asc ;; *** (2) TRANSACTION: TRANSACTION 54576399047, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 14 lock struct(s), heap size 1128, 7 row lock(s) MySQL thread id 3342717347, OS thread handle 139933791983360, query id 115616479832 58.76.184.38 ltcare Update INSERT INTO work_limit_amount ( ctmfkey -- INT NOT NULL COMMENT '* 센터 FK', , pamfkey -- INT NOT NULL COMMENT '* 수급자 FK', , wlayymm -- VARCHAR(6) NOT NULL COMMENT '* visit, transport 작업 년월(YYMM)', , wlalimt -- INT NOT NULL COMMENT '* 한도금액', , wlaover -- CHAR(1) DEFAULT 'N' NOT NULL COMMENT '* 한도 초과 여부(Y:over)', ) VALUES *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 20304 page no 86939 n bits 200 index PRIMARY of table `ltcare`.`change_transport_driver` trx id 54576399047 lock_mode X locks rec but not gap Record lock, heap no 132 PHYSICAL RECORD: n_fields 23; compact format; info bits 0 0: len 4; hex 800006b2; asc ;; 1: len 4; hex 80000543; asc C;; 2: len 8; hex 3230323330343134; asc 20230414;; 3: len 6; hex 000cb501c2ae; asc ;; 4: len 7; hex 7f0000013c07d5; asc < ;; 5: len 4; hex 80006b29; asc k);; 6: len 9; hex ecb59ceca095eab8b0; asc ;; 7: len 4; hex 800700b5; asc ;; 8: len 1; hex 53; asc S;; 9: len 9; hex ec9db4eab780eba180; asc ;; 10: len 4; hex 80006b29; asc k);; 11: len 9; hex ecb59ceca095eab8b0; asc ;; 12: len 4; hex 80000000; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 4; hex 80000000; asc ;; 16: len 0; hex ; asc ;; 17: len 4; hex 80000000; asc ;; 18: len 0; hex ; asc ;; 19: len 4; hex 80006b29; asc k);; 20: len 9; hex ecb59ceca095eab8b0; asc ;; 21: len 5; hex 99afc89a9f; asc ;; 22: len 5; hex 99afdca4df; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 20820 page no 12304 n bits 400 index ctmfkey of table `ltcare`.`work_limit_amount` trx id 54576399047 lock_mode X locks rec but not gap waiting Record lock, heap no 103 PHYSICAL RECORD: n_fields 9; compact format; info bits 0 0: len 4; hex 800006b2; asc ;; 1: len 4; hex 8000810f; asc ;; 2: len 6; hex 323032333034; asc 202304;; 3: len 6; hex 000000000000; asc ;; 4: len 7; hex 80000000000000; asc ;; 5: len 4; hex 8019f320; asc ;; 6: len 1; hex 4e; asc N;; 7: len 5; hex 99afc89ac2; asc ;; 8: len 5; hex 99afc89ac2; asc ;; *** WE ROLL BACK TRANSACTION (2) ------------ TRANSACTIONS ------------ Trx id counter 54576900029 Purge done for trx's n:o < 54576899797 undo n:o < 0 state: running History list length 115 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 422075298751792, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298718064, not started mysql tables in use 70, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298705416, ACTIVE 0 sec starting index read mysql tables in use 8, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459476, OS thread handle 139939165955840, query id 115646363515 58.76.184.38 ltcare Sending data SELECT * FROM ( SELECT a.stmmgno , a.ctmfkey , a.stmiden , a.stmpass , a.stmname , a.stmnumb , a.stmmalf , a.stmbsdt , a.stmcgrp , a.stmauth , a.stmwgbn , a.stmmtel , a.stmotel , a.stmzcod , a.stmpa ---TRANSACTION 422075298667472, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298642176, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298654824, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298659040, ACTIVE 0 sec fetching rows mysql tables in use 67, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459470, OS thread handle 139932408641280, query id 115646363395 58.76.184.36 ltcare Sending data SELECT *, CONCAT(center_room.ctbfkey, '_', center_room.ctrflor, '_', center_room.ctrgrup) ctbflor, (SELECT twkinfl FROM transport_work WHERE pamfkey = pammgno AND twkstdt LIKE '20230414' ORDER BY twkmgno DESC LIMIT 1) twkinfl, (SELECT cttdate FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcdate, (SELECT cttstdt FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcfrdt, ( Trx read view will not see trx with id >= 54576900027, sees < 54576899790 ---TRANSACTION 422075298680120, ACTIVE 0 sec mysql tables in use 67, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459469, OS thread handle 139932301559552, query id 115646363407 58.76.184.36 ltcare Sending data SELECT *, CONCAT(center_room.ctbfkey, '_', center_room.ctrflor, '_', center_room.ctrgrup) ctbflor, (SELECT twkinfl FROM transport_work WHERE pamfkey = pammgno AND twkstdt LIKE '20230414' ORDER BY twkmgno DESC LIMIT 1) twkinfl, (SELECT cttdate FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcdate, (SELECT cttstdt FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcfrdt, ( Trx read view will not see trx with id >= 54576900028, sees < 54576899790 ---TRANSACTION 422075298747576, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298692768, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298629528, ACTIVE 0 sec fetching rows mysql tables in use 36, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459457, OS thread handle 139933760956160, query id 115646363204 58.76.184.36 ltcare Sending data SELECT res.* FROM ( SELECT ppp.* , IF( ppp.ctmgubn <= '2' , IF( ppp.pamotdt IS NOT NULL AND ppp.padredt = '' , 'X' , IF( ppp.pamotdt IS NULL OR ppp.padredt <= ppp.e_date , IF( ppp.paofrdt IS NOT NULL AND ppp.paotodt >= ppp.e_date Trx read view will not see trx with id >= 54576900024, sees < 54576899790 ---TRANSACTION 422075298591584, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298907784, ACTIVE 0 sec mysql tables in use 7, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459455, OS thread handle 139938819925760, query id 115646362721 58.76.184.36 ltcare Sending data SELECT stmmgno , stmiden , stmname , IFNULL(ssh.sshstat, '1') stmstat , stmauth , stmmalf , stmbsdt , IFNULL( (SELECT sjhkind FROM staff_job_history WHERE stmfkey = staff_master.stmmgno AND sjhdate <= DATE_FORMAT( NOW(), '%Y%m%d' ) ORDER BY sjhdate DESC, sjhmgno DESC Trx read view will not see trx with id >= 54576900010, sees < 54576899790 ---TRANSACTION 422075298945728, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298933080, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298920432, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298798168, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298903568, not started mysql tables in use 3, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298726496, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 54576900022, ACTIVE 0 sec 6 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 2 MySQL thread id 3343459441, OS thread handle 139933301946112, query id 115646363387 58.76.184.36 ltcare Trx read view will not see trx with id >= 54576900023, sees < 54576899790 ---TRANSACTION 422075298852976, ACTIVE 0 sec fetching rows mysql tables in use 9, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459440, OS thread handle 139937442420480, query id 115646362541 58.76.184.36 ltcare Sending data SELECT SUM( IF( SUBSTR( x.yyyymmdd, 1, 6 ) = '202304' AND x.date_s IS NULL AND x.day_s NOT IN('5', '6'), 1, 0 ) * 8 ) AS gijun_time , SUM( IF( x.date_s IS NOT NULL AND ( x.yyyymmdd = '' OR x.yyyymmdd = '' ), 1, 0 ) ) AS carryable FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmd Trx read view will not see trx with id >= 54576900003, sees < 54576899790 ---TRANSACTION 422075298793952, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298646392, ACTIVE 0 sec mysql tables in use 67, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459435, OS thread handle 139932366411520, query id 115646363496 58.76.184.36 ltcare Creating sort index SELECT *, CONCAT(center_room.ctbfkey, '_', center_room.ctrflor, '_', center_room.ctrgrup) ctbflor, (SELECT twkinfl FROM transport_work WHERE pamfkey = pammgno AND twkstdt LIKE '20230414' ORDER BY twkmgno DESC LIMIT 1) twkinfl, (SELECT cttdate FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcdate, (SELECT cttstdt FROM contract_master WHERE '20230414' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcfrdt, ( Trx read view will not see trx with id >= 54576900029, sees < 54576899790 ---TRANSACTION 422075298701200, ACTIVE 0 sec fetching rows mysql tables in use 9, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459431, OS thread handle 139937450714880, query id 115646361580 58.76.184.38 ltcare Creating sort index SELECT dppmgno, ctmfkey, dpp_tbl, dpp_txt, dpptkey, dppgubn, dppname, dpppkey, dppknd1, dppknd2, dppsidt, dppptxt , IF( dppmtel <> '' , dppmtel , CASE WHEN dppgubn = 'S' THEN (SELECT stmmtel FROM staff_master x WHERE x.ctmfkey = document_people.ctmfkey AND x.stmmgno = document_people.dpppkey) WHEN dppgubn = 'P' THEN (SELECT pammtel FROM patient_master x WHERE x.ctmfkey = document_people.ctmfkey AND x.pammgno = document_people.dpppkey) W Trx read view will not see trx with id >= 54576899981, sees < 54576899790 ---TRANSACTION 54576900012, ACTIVE 0 sec fetching rows mysql tables in use 9, locked 0 6 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 3 MySQL thread id 3343459416, OS thread handle 139932291729152, query id 115646363183 58.76.184.38 ltcare Creating sort index SELECT dppmgno, ctmfkey, dpp_tbl, dpp_txt, dpptkey, dppgubn, dppname, dpppkey, dppknd1, dppknd2, dppsidt, dppptxt , IF( dppmtel <> '' , dppmtel , CASE WHEN dppgubn = 'S' THEN (SELECT stmmtel FROM staff_master x WHERE x.ctmfkey = document_people.ctmfkey AND x.stmmgno = document_people.dpppkey) WHEN dppgubn = 'P' THEN (SELECT pammtel FROM patient_master x WHERE x.ctmfkey = document_people.ctmfkey AND x.pammgno = document_people.dpppkey) W Trx read view will not see trx with id >= 54576900013, sees < 54576899790 ---TRANSACTION 422075298895136, ACTIVE 0 sec mysql tables in use 59, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459410, OS thread handle 139933273384704, query id 115646361139 58.76.184.38 ltcare Creating sort index SELECT DISTINCT current_yc.*, current_yk.*, current_hs.*, current_ns.*, current_pp.*, cpp07_01 + cpp07_02 + cpp07_03 + cpp07_04 + cpp07_05 + cpp07_06 + cpp07_07 + cpp07_08 + cpp07_09 + cpp07_10 + cpp07_11 AS cpp07_tt, twkinfl, twksttm, IF(twkchck = 'Y' OR twkfrst = 'Y', twkrlst, twksttm) twk_start, IF(twkchck = 'Y' OR twktost = 'Y', twkrlen, twkentm) twk_end, ( SELECT COUNT(*) FROM nursing_daily_nursing WHERE numfkey = (SELECT nummgno FROM nursing_master WHERE nursing_master.ctmfkey = patient_master.ctmfkey AND pamfkey Trx read view will not see trx with id >= 54576900014, sees < 54576899790 ---TRANSACTION 422075298874056, ACTIVE 0 sec mysql tables in use 70, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459409, OS thread handle 139938825762560, query id 115646363252 58.76.184.36 ltcare Sending data SELECT *, CONCAT(center_room.ctbfkey, '_', center_room.ctrflor, '_', center_room.ctrgrup) ctbflor, (SELECT twkinfl FROM transport_work WHERE pamfkey = pammgno AND twkstdt LIKE '20230403' ORDER BY twkmgno DESC LIMIT 1) twkinfl, (SELECT cttdate FROM contract_master WHERE '20230403' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcdate, (SELECT cttstdt FROM contract_master WHERE '20230403' BETWEEN cttstdt AND cttendt AND pamfkey = pammgno ORDER BY cttdate DESC, cttmgno DESC LIMIT 1) pvcfrdt, ( Trx read view will not see trx with id >= 54576900025, sees < 54576899790 ---TRANSACTION 422075298831896, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 54576900015, ACTIVE 0 sec 4 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1 MySQL thread id 3343459401, OS thread handle 139932448884480, query id 115646362966 58.76.184.36 ltcare Trx read view will not see trx with id >= 54576900016, sees < 54576899790 ---TRANSACTION 422075298730712, ACTIVE 0 sec mysql tables in use 9, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459396, OS thread handle 139938900813568, query id 115646360647 58.76.184.38 ltcare Sending data SELECT SUM( IF( SUBSTR( x.yyyymmdd, 1, 6 ) = '202304' AND x.date_s IS NULL AND x.day_s NOT IN('5', '6'), 1, 0 ) * 8 ) AS gijun_time , SUM( IF( x.date_s IS NOT NULL AND ( x.yyyymmdd = '' OR x.yyyymmdd = '' ), 1, 0 ) ) AS carryable FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmd Trx read view will not see trx with id >= 54576899957, sees < 54576899790 ---TRANSACTION 54576899936, ACTIVE 0 sec mysql tables in use 24, locked 0 21 lock struct(s), heap size 1128, 1076 row lock(s), undo log entries 34 MySQL thread id 3343459391, OS thread handle 139938903271168, query id 115646361169 58.76.184.36 ltcare Sending data WITH w_holy AS ( SELECT x.* FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) ) AS day_n FROM date_range a LEFT JOIN Trx read view will not see trx with id >= 54576899939, sees < 54576898665 ---TRANSACTION 422075298857192, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298777088, ACTIVE 0 sec mysql tables in use 9, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459383, OS thread handle 139933758805760, query id 115646360825 58.76.184.36 ltcare Sending data SELECT SUM( IF( SUBSTR( x.yyyymmdd, 1, 6 ) = '202304' AND x.date_s IS NULL AND x.day_s NOT IN('5', '6'), 1, 0 ) * 8 ) AS gijun_time , SUM( IF( x.date_s IS NOT NULL AND ( x.yyyymmdd = '' OR x.yyyymmdd = '' ), 1, 0 ) ) AS carryable FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmd Trx read view will not see trx with id >= 54576899961, sees < 54576899790 ---TRANSACTION 422075298671688, ACTIVE 0 sec fetching rows mysql tables in use 8, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459373, OS thread handle 139938803029760, query id 115646362898 58.76.184.36 ltcare Sending data WITH w_sd AS ( SELECT s.* FROM ( SELECT MIN( DATE_FORMAT( solar_date, '%Y%m%d' ) ) AS date_s , spename FROM lunar2solar a , cal_specialday b WHERE DATE_FORMAT( solar_date, '%Y%m' ) LIKE '2023%' AND ( mmdd <> '0501' ) AND ( ( b.solar = '1' Trx read view will not see trx with id >= 54576900014, sees < 54576899790 ---TRANSACTION 422075298587368, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298861408, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298810816, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 54576900028, ACTIVE 0 sec 7 lock struct(s), heap size 1128, 3 row lock(s), undo log entries 1 MySQL thread id 3343459255, OS thread handle 139938810402560, query id 115646363487 58.76.184.38 ltcare ---TRANSACTION 422075298912000, ACTIVE 0 sec fetching rows mysql tables in use 24, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459263, OS thread handle 139932400039680, query id 115646353739 58.76.184.36 ltcare Creating sort index WITH date_info AS ( SELECT yyyymmdd , IF(WEEKOFYEAR(yyyymmdd) BETWEEN month_first_day_week_of_year AND month_last_day_week_of_year, WEEKOFYEAR(yyyymmdd), IF(WEEKOFYEAR(yyyymmdd) > month_last_day_week_of_year, month_first_day_week_of_year, month_last_day_week_of_year)) AS week_of_year , month_first_day , month_last_day FROM ( SELECT IF(month_first_day_week_day = 0, month_first_day, DATE_FORMAT(DATE_ADD(mont Trx read view will not see trx with id >= 54576899857, sees < 54576898665 ---TRANSACTION 422075298865624, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298802384, ACTIVE 0 sec fetching rows mysql tables in use 9, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459246, OS thread handle 139938902656768, query id 115646360286 58.76.184.36 ltcare Sending data WITH w_sd AS ( SELECT s.* FROM ( SELECT MIN( DATE_FORMAT( solar_date, '%Y%m%d' ) ) AS date_s , spename FROM lunar2solar a , cal_specialday b WHERE DATE_FORMAT( solar_date, '%Y%m' ) LIKE '2023%' AND ( mmdd <> '0501' ) AND ( ( b.solar = ' Trx read view will not see trx with id >= 54576899949, sees < 54576898665 ---TRANSACTION 422075298684336, ACTIVE 0 sec fetching rows mysql tables in use 24, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343459234, OS thread handle 139932432602880, query id 115646352752 58.76.184.36 ltcare Creating sort index WITH date_info AS ( SELECT yyyymmdd , IF(WEEKOFYEAR(yyyymmdd) BETWEEN month_first_day_week_of_year AND month_last_day_week_of_year, WEEKOFYEAR(yyyymmdd), IF(WEEKOFYEAR(yyyymmdd) > month_last_day_week_of_year, month_first_day_week_of_year, month_last_day_week_of_year)) AS week_of_year , month_first_day , month_last_day FROM ( SELECT IF(month_first_day_week_day = 0, month_first_day, DATE_FORMAT(DATE_ADD(mont Trx read view will not see trx with id >= 54576899846, sees < 54576898665 ---TRANSACTION 54576899832, ACTIVE 0 sec fetching rows mysql tables in use 24, locked 0 16 lock struct(s), heap size 1128, 229 row lock(s), undo log entries 21 MySQL thread id 3343459216, OS thread handle 139938913408768, query id 115646352188 58.76.184.36 ltcare Sending data WITH w_holy AS ( SELECT x.* FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) ) AS day_n FROM date_range a LEFT JOIN Trx read view will not see trx with id >= 54576899833, sees < 54576898665 ---TRANSACTION 422075298886704, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 54576899790, ACTIVE 0 sec mysql tables in use 24, locked 0 16 lock struct(s), heap size 1128, 457 row lock(s), undo log entries 12 MySQL thread id 3343459050, OS thread handle 139938918016768, query id 115646349171 58.76.184.36 ltcare Sending data WITH w_holy AS ( SELECT x.* FROM ( SELECT * , DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) AS date_n , WEEKDAY( a.yyyymmdd ) AS day_s , WEEKDAY( DATE_FORMAT( DATE_ADD(a.yyyymmdd, INTERVAL 1 day), '%Y%m%d' ) ) AS day_n FROM date_range a LEFT JOIN Trx read view will not see trx with id >= 54576899794, sees < 54576898665 ---TRANSACTION 54576899856, ACTIVE 0 sec mysql tables in use 37, locked 0 7 lock struct(s), heap size 1128, 8 row lock(s), undo log entries 6 MySQL thread id 3343459047, OS thread handle 139935502407424, query id 115646363498 58.76.184.36 ltcare Sending data SELECT * , IF(LENGTH(ctmregn) = 10 AND ctmpbrn = ctmregn AND LENGTH(ctmpbid) > 0, '1','0') AS popbill_use , (SELECT COUNT(*) FROM center_care_group WHERE ctmfkey = a.ctmmgno) care_group_count , (SELECT rsmfkey FROM reseller_center_map WHERE ctmfkey = a.ctmmgno) rsmfkey , (SELECT rcmstat FROM reseller_center_map WHERE ctmfkey = a.ctmmgno) rcmstat , (SELECT stmname FROM staff_master WHERE stmmgno = cta.stmfky2) stmnam2 , (SELECT stmname FROM staff_ Trx read view will not see trx with id >= 54576899857, sees < 54576898665 ---TRANSACTION 54576899974, ACTIVE 0 sec 4 lock struct(s), heap size 1128, 2 row lock(s) MySQL thread id 3343458455, OS thread handle 139938913715968, query id 115646361532 58.76.184.36 ltcare Trx read view will not see trx with id >= 54576899981, sees < 54576899790 ---TRANSACTION 422075298604232, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 0 0 lock struct(s), heap size 1128, 0 row lock(s) MySQL thread id 3343457492, OS thread handle 139933750413056, query id 115646363476 58.76.184.38 ltcare Sending data SELECT pamfkey FROM transport_work WHERE ctmfkey = '21965' AND twkstdt LIKE '202304%' AND twkampl <> 'E' AND twkampl <> '' GROUP BY pamfkey Trx read view will not see trx with id >= 54576900028, sees < 54576899790 ---TRANSACTION 422075298937296, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298595800, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298764440, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298675904, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298739144, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298696984, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298882488, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298756008, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298734928, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298600016, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298625312, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298621096, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298840328, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298848760, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298996320, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298844544, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298688552, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298637960, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298578936, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298823464, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298815032, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298574720, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298806600, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298650608, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298819248, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298772872, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298743360, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298722280, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298612664, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298633744, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298713848, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298583152, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298570504, not started 0 lock struct(s), heap size 1128, 0 row lock(s) ---TRANSACTION 422075298566288, not started 0 lock struct(s), heap size 1128, 0 row lock(s) -------- 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 (write thread) I/O thread 7 state: waiting for completed aio requests (write thread) I/O thread 8 state: waiting for completed aio requests (write thread) I/O thread 9 state: waiting for completed aio requests (write thread) Pending normal aio reads: [0, 0, 0, 0] , aio writes: [0, 0, 0, 0] , ibuf aio reads:, log i/o's:, sync i/o's: Pending flushes (fsync) log: 0; buffer pool: 0 284802061 OS file reads, 2610841761 OS file writes, 523927625 OS fsyncs 79.27 reads/s, 16384 avg bytes/read, 669.15 writes/s, 133.03 fsyncs/s ------------------------------------- INSERT BUFFER AND ADAPTIVE HASH INDEX ------------------------------------- Ibuf: size 1, free list len 85096, seg size 85098, 5976619 merges merged operations: insert 14826891, delete mark 8317569, delete 1956003 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 160435843, node heap has 211729 buffer(s) Hash table size 160435843, node heap has 331747 buffer(s) Hash table size 160435843, node heap has 137043 buffer(s) Hash table size 160435843, node heap has 117115 buffer(s) Hash table size 160435843, node heap has 82579 buffer(s) Hash table size 160435843, node heap has 480304 buffer(s) Hash table size 160435843, node heap has 183520 buffer(s) Hash table size 160435843, node heap has 224421 buffer(s) 1950474.85 hash searches/s, 390930.46 non-hash searches/s --- LOG --- Log sequence number 15650224773051 Log flushed up to 15650224529874 Pages flushed up to 15648679848607 Last checkpoint at 15648679388724 0 pending log flushes, 0 pending chkp writes 1831642637 log i/o's done, 471.93 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total large memory allocated 649076932608 Dictionary memory allocated 4199240 Buffer pool size 38782236 Free buffers 8190 Database pages 37005588 Old database pages 13660102 Modified db pages 546006 Percent of dirty pages(LRU & free pages): 1.475 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 3439600536, not young 17913793833 1176.00 youngs/s, 12345.85 non-youngs/s Pages read 284800876, created 37972028, written 656816972 79.27 reads/s, 7.15 creates/s, 174.72 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 37005588, unzip_LRU len: 0 I/O sum[101416]:cur[1792], unzip sum[0]:cur[0] ---------------------- INDIVIDUAL BUFFER POOL INFO ---------------------- ---BUFFER POOL 0 Buffer pool size 4848308 Free buffers 1023 Database pages 4626025 Old database pages 1707633 Modified db pages 70475 Percent of dirty pages(LRU & free pages): 1.523 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 431495477, not young 2274964859 155.00 youngs/s, 1116.31 non-youngs/s Pages read 35708667, created 4742822, written 84297125 9.93 reads/s, 0.56 creates/s, 21.55 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4626025, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 1 Buffer pool size 4847704 Free buffers 1024 Database pages 4625398 Old database pages 1707402 Modified db pages 68696 Percent of dirty pages(LRU & free pages): 1.485 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 430012745, not young 2228439322 145.63 youngs/s, 578.01 non-youngs/s Pages read 35481064, created 4747898, written 82045243 9.80 reads/s, 0.71 creates/s, 23.93 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4625398, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 2 Buffer pool size 4847704 Free buffers 1024 Database pages 4625804 Old database pages 1707552 Modified db pages 67602 Percent of dirty pages(LRU & free pages): 1.461 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 429585430, not young 2219542246 141.43 youngs/s, 1865.15 non-youngs/s Pages read 35537044, created 4749564, written 81910371 9.62 reads/s, 0.84 creates/s, 22.36 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4625804, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 3 Buffer pool size 4847704 Free buffers 1024 Database pages 4625845 Old database pages 1707567 Modified db pages 68377 Percent of dirty pages(LRU & free pages): 1.478 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 423408734, not young 2153592692 136.42 youngs/s, 1963.16 non-youngs/s Pages read 34857312, created 4744835, written 82100248 10.24 reads/s, 0.75 creates/s, 21.53 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4625845, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 4 Buffer pool size 4847704 Free buffers 1024 Database pages 4625769 Old database pages 1707539 Modified db pages 67495 Percent of dirty pages(LRU & free pages): 1.459 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 436379372, not young 2320603144 149.52 youngs/s, 2552.46 non-youngs/s Pages read 36160513, created 4725107, written 82100478 11.02 reads/s, 0.44 creates/s, 22.11 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4625769, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 5 Buffer pool size 4847704 Free buffers 1024 Database pages 4625302 Old database pages 1707366 Modified db pages 67724 Percent of dirty pages(LRU & free pages): 1.464 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 430809233, not young 2200766403 142.80 youngs/s, 887.38 non-youngs/s Pages read 35750812, created 4746665, written 81422161 9.16 reads/s, 0.65 creates/s, 20.36 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 0 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4625302, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 6 Buffer pool size 4847704 Free buffers 1024 Database pages 4626461 Old database pages 1707794 Modified db pages 67768 Percent of dirty pages(LRU & free pages): 1.464 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 425576469, not young 2238932694 146.49 youngs/s, 1409.28 non-youngs/s Pages read 35334339, created 4754985, written 81274734 10.85 reads/s, 1.73 creates/s, 20.84 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4626461, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] ---BUFFER POOL 7 Buffer pool size 4847704 Free buffers 1023 Database pages 4624984 Old database pages 1707249 Modified db pages 67869 Percent of dirty pages(LRU & free pages): 1.467 Max dirty pages percent: 75.000 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 432333076, not young 2276952473 158.71 youngs/s, 1974.09 non-youngs/s Pages read 35971125, created 4760152, written 81666612 8.65 reads/s, 1.47 creates/s, 22.05 writes/s Buffer pool hit rate 999 / 1000, young-making rate 0 / 1000 not 1 / 1000 Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s LRU len: 4624984, unzip_LRU len: 0 I/O sum[12677]:cur[224], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 25 read views open inside InnoDB Process ID=149565, Main thread ID=139939203237632, state: sleeping Number of rows inserted 2750959977, updated 944807915, deleted 835118443, read 96590999809239 684.90 inserts/s, 345.65 updates/s, 177.03 deletes/s, 20341135.23 reads/s Number of system rows inserted 0, updated 0, deleted 0, read 1 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================