InnoDB: ###### Diagnostic info printed to the standard error stream InnoDB: Warning: a long semaphore wait: --Thread 4644 has waited at btr0sea.c line 1134 for 924.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 00000000220AFE68 created in file btr0sea.c line 178 a writer (thread id 4644) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffff Last time read locked in file btr0sea.c line 1057 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\btr\btr0sea.c line 1382 InnoDB: Warning: a long semaphore wait: --Thread 2684 has waited at row0purge.c line 680 for 921.00 seconds the semaphore: S-lock on RW-latch at 0000000140127080 created in file dict0dict.c line 738 a writer (thread id 4644) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0ins.c line 1550 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0mysql.c line 3151 InnoDB: Warning: a long semaphore wait: --Thread 3996 has waited at btr0cur.c line 1929 for 920.00 seconds the semaphore: X-lock on RW-latch at 00000000220AFE68 created in file btr0sea.c line 178 a writer (thread id 4644) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffff Last time read locked in file btr0sea.c line 1057 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\btr\btr0sea.c line 1382 InnoDB: Warning: a long semaphore wait: --Thread 3280 has waited at row0ins.c line 2027 for 920.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 InnoDB: Warning: a long semaphore wait: --Thread 668 has waited at row0ins.c line 2027 for 919.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 InnoDB: Warning: a long semaphore wait: --Thread 4224 has waited at row0ins.c line 2027 for 919.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 InnoDB: Warning: a long semaphore wait: --Thread 5000 has waited at row0ins.c line 2027 for 919.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 InnoDB: ###### Starts InnoDB Monitor for 30 secs to print diagnostic info: InnoDB: Pending preads 0, pwrites 0 ===================================== 140911 8:31:49 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 20 seconds ----------------- BACKGROUND THREAD ----------------- srv_master_thread loops: 56073 1_second, 56073 sleeps, 5573 10_second, 2667 background, 2651 flush srv_master_thread log flush and writes: 64294 ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 4003577, signal count 18944453 --Thread 4644 has waited at btr0sea.c line 1134 for 942.00 seconds the semaphore: X-lock (wait_ex) on RW-latch at 00000000220AFE68 created in file btr0sea.c line 178 a writer (thread id 4644) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffff Last time read locked in file btr0sea.c line 1057 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\btr\btr0sea.c line 1382 --Thread 2684 has waited at row0purge.c line 680 for 939.00 seconds the semaphore: S-lock on RW-latch at 0000000140127080 created in file dict0dict.c line 738 a writer (thread id 4644) has reserved it in mode exclusive number of readers 0, waiters flag 1, lock_word: 0 Last time read locked in file row0ins.c line 1550 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0mysql.c line 3151 --Thread 3996 has waited at btr0cur.c line 1929 for 938.00 seconds the semaphore: X-lock on RW-latch at 00000000220AFE68 created in file btr0sea.c line 178 a writer (thread id 4644) has reserved it in mode wait exclusive number of readers 1, waiters flag 1, lock_word: ffffffff Last time read locked in file btr0sea.c line 1057 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\btr\btr0sea.c line 1382 --Thread 3280 has waited at row0ins.c line 2027 for 938.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 --Thread 668 has waited at row0ins.c line 2027 for 937.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 --Thread 4224 has waited at row0ins.c line 2027 for 937.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 --Thread 5000 has waited at row0ins.c line 2027 for 937.00 seconds the semaphore: X-lock on RW-latch at 00000001472BF3C0 created in file buf0buf.c line 938 a writer (thread id 3996) 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 3908 Last time write locked in file ..\..\..\mysql-5.5.39\storage\innobase\row\row0upd.c line 2144 --Thread 4576 has waited at dict0dict.c line 351 for 1.00 seconds the semaphore: Mutex at 000000002CD7FDA8 created file dict0dict.c line 725, lock var 1 waiters flag 1 Mutex spin waits 254411693, rounds 446502597, OS waits 2071160 RW-shared spins 5583200, rounds 27946126, OS waits 310712 RW-excl spins 8599389, rounds 91662438, OS waits 1456610 Spin rounds per wait: 1.76 mutex, 5.01 RW-shared, 10.66 RW-excl ------------------------ LATEST DETECTED DEADLOCK ------------------------ 140911 8:07:54 *** (1) TRANSACTION: TRANSACTION A5BE5DD70, ACTIVE 2 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s) MySQL thread id 24, OS thread handle 0xcd0, query id 62156527 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='15487316721073743506', ts5min='0', pldCount='13', mrrTs='0' on duplicate key update pldCount=pldCount+'13', mrrTs='0' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2243380 n bits 336 index `PRIMARY` of table `p2production1`.`node_comm_cube` trx id A5BE5DD70 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 15 PHYSICAL RECORD: n_fields 8; compact format; info bits 32 0: len 8; hex 0000000000000000; asc ;; 1: len 8; hex d6ee020019640041; asc d A;; 2: len 8; hex 8000000000000000; asc ;; 3: len 6; hex 000a5be5db82; asc [ ;; 4: len 7; hex 19000f40052015; asc @ ;; 5: len 4; hex 80000000; asc ;; 6: len 8; hex 8000000000000000; asc ;; 7: len 4; hex 800000cf; asc ;; *** (2) TRANSACTION: TRANSACTION A5BE5DD8C, ACTIVE 1 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 1 16 lock struct(s), heap size 3112, 2 row lock(s) MySQL thread id 27, OS thread handle 0x1388, query id 62156555 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='12028552207253201370', ts5min='0', pldCount='41', mrrTs='0' on duplicate key update pldCount=pldCount+'41', mrrTs='0' *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 2243380 n bits 336 index `PRIMARY` of table `p2production1`.`node_comm_cube` trx id A5BE5DD8C lock_mode X locks gap before rec Record lock, heap no 15 PHYSICAL RECORD: n_fields 8; compact format; info bits 32 0: len 8; hex 0000000000000000; asc ;; 1: len 8; hex d6ee020019640041; asc d A;; 2: len 8; hex 8000000000000000; asc ;; 3: len 6; hex 000a5be5db82; asc [ ;; 4: len 7; hex 19000f40052015; asc @ ;; 5: len 4; hex 80000000; asc ;; 6: len 8; hex 8000000000000000; asc ;; 7: len 4; hex 800000cf; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 2243380 n bits 336 index `PRIMARY` of table `p2production1`.`node_comm_cube` trx id A5BE5DD8C lock_mode X locks gap before rec insert intention waiting Record lock, heap no 15 PHYSICAL RECORD: n_fields 8; compact format; info bits 32 0: len 8; hex 0000000000000000; asc ;; 1: len 8; hex d6ee020019640041; asc d A;; 2: len 8; hex 8000000000000000; asc ;; 3: len 6; hex 000a5be5db82; asc [ ;; 4: len 7; hex 19000f40052015; asc @ ;; 5: len 4; hex 80000000; asc ;; 6: len 8; hex 8000000000000000; asc ;; 7: len 4; hex 800000cf; asc ;; *** WE ROLL BACK TRANSACTION (1) ------------ TRANSACTIONS ------------ Trx id counter A5BE919E7 Purge done for trx's n:o < A5BE90A20 undo n:o < 1 History list length 3738 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION A5BE9197A, not started MySQL thread id 94304, OS thread handle 0x1224, query id 0 event_scheduler Clearing ---TRANSACTION A5BE919E6, ACTIVE 937 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 27, OS thread handle 0x1388, query id 63881532 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='9218305625969139584', ts5min='0', pldCount='18', mrrTs='0' on duplicate key update pldCount=pldCount+'18', mrrTs='0' ---TRANSACTION A5BE919D0, ACTIVE 937 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 69, OS thread handle 0x1080, query id 63881510 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='16207894308402233486', ts5min='0', pldCount='14', mrrTs='0' on duplicate key update pldCount=pldCount+'14', mrrTs='0' ---TRANSACTION A5BE919C2, ACTIVE 937 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 26, OS thread handle 0x29c, query id 63881496 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='13613820923036827731', ts5min='0', pldCount='27', mrrTs='0' on duplicate key update pldCount=pldCount+'27', mrrTs='0' ---TRANSACTION A5BE919AA, ACTIVE 938 sec inserting, thread declared inside InnoDB 500 mysql tables in use 1, locked 1 1 lock struct(s), heap size 376, 0 row lock(s) MySQL thread id 24, OS thread handle 0xcd0, query id 63881472 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='17072585436857368735', ts5min='0', pldCount='2', mrrTs='0' on duplicate key update pldCount=pldCount+'2', mrrTs='0' ---TRANSACTION A5BE91992, ACTIVE 938 sec updating or deleting, thread declared inside InnoDB 498 mysql tables in use 1, locked 1 2 lock struct(s), heap size 376, 1 row lock(s), undo log entries 1 MySQL thread id 32, OS thread handle 0xf9c, query id 63881448 localhost 127.0.0.1 root update insert into node_comm_cube set nodeId='0', hostId='4462504833270768200', ts5min='0', pldCount='71', mrrTs='0' on duplicate key update pldCount=pldCount+'71', mrrTs='0' ---TRANSACTION A5BE9197C, ACTIVE 942 sec dropping table 11 lock struct(s), heap size 3112, 6 row lock(s) MySQL thread id 94304, OS thread handle 0x1224, query id 0 event_scheduler Clearing ---TRANSACTION A5BE9196F, ACTIVE 943 sec, thread declared inside InnoDB 69 mysql tables in use 80, locked 0 , holds adaptive hash latch MySQL thread id 94305, OS thread handle 0xc88, query id 63850896 23.23.229.30 mysql ( select n.nodeId, lpad(hex(n.nodeId), 16, '0') as 'guids', if(n.friendlyName <> '', n.friendlyName, concat(mid(lpad(conv(n.nodeId, 10, 16), 16, '0'), 1, 4) ,'-', mid(lpad(conv(n.nodeId, 10, 16), 16, '0'), 5, 4) ,'-', mid(lpad(conv(n.nodeId, 10, 16), 16, '0'), 9, 4) ,'-', mid(lpad(conv(n.nodeId, 10, 16), 16, '0'), 13, 4) )) as nodeName, mfg.type as 'type', timediff(now(), from_unixtime(aa.timeStamp)) as 'recency', if(aa.timeStamp >= (1410422466), 'OK', 'No comm') as 'commStatus', aa.timeStamp, round(va.value,1) as 'voltage', round(aa.value, Trx read view will not see trx with id >= A5BE91970, sees < A5BE9196E -------- FILE I/O -------- I/O thread 0 state: wait Windows aio (insert buffer thread) I/O thread 1 state: wait Windows aio (log thread) I/O thread 2 state: wait Windows aio (read thread) I/O thread 3 state: wait Windows aio (read thread) I/O thread 4 state: wait Windows aio (read thread) I/O thread 5 state: wait Windows aio (read thread) I/O thread 6 state: wait Windows aio (write thread) I/O thread 7 state: wait Windows aio (write thread) I/O thread 8 state: wait Windows aio (write thread) I/O thread 9 state: wait Windows aio (write thread) Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] , ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 1988761 OS file reads, 49273422 OS file writes, 675154 OS fsyncs 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 1346, seg size 1348, 217689 merges merged operations: insert 915319, delete mark 20026528, delete 1588886 discarded operations: insert 0, delete mark 0, delete 0 Hash table size 21249871, node heap has 65468 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 53672723565162 Log flushed up to 53672723565048 Last checkpoint at 53671804415171 0 pending log writes, 0 pending chkp writes 23474326 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 10989076480; in additional pool allocated 0 Dictionary memory allocated 4823271 Buffer pool size 655360 Free buffers 7 Database pages 589885 Old database pages 217730 Modified db pages 177071 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages made young 8850365, not young 0 0.00 youngs/s, 0.00 non-youngs/s Pages read 1974461, created 1233687, written 25327293 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: 589885, unzip_LRU len: 0 I/O sum[0]:cur[0], unzip sum[0]:cur[0] -------------- ROW OPERATIONS -------------- 6 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread id 2684, state: flushing log Number of rows inserted 194240469, updated 251487781, deleted 160752091, read 6570747226 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- 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. 140911 8:32:01 InnoDB: Assertion failure in thread 3196 in file srv0srv.c line 2502 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.