$ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:00:28 PDT 2008 Status ===================================== 081026 20:00:28 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 25 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228390 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5716, query id 160692343 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1228389, not started, process no 25013, OS thread id 2305843009335325264 MySQL thread id 5713, query id 160692338 localhost 127.0.0.1 root ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 0.00 hash searches/s, 0.00 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744274; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s No buffer pool page gets since the last printout -------------- ROW OPERATIONS -------------- 0 queries inside InnoDB, 0 queries in queue 1 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 648925926 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:00:28 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help] $ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:00:37 PDT 2008 Status ===================================== 081026 20:00:37 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 9 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228391 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5717, query id 160692346 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 ---TRANSACTION 0 1228390, ACTIVE 8 sec, process no 25013, OS thread id 2305843009335325264 fetching rows, thread declared inside InnoDB 143 mysql tables in use 3, locked 0 MySQL thread id 5713, query id 160692345 localhost 127.0.0.1 root Sending data select count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 and loginname='dan' group by roleid, ratingtypeid) sr1 join LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate Trx read view will not see trx with id >= 0 1228391, sees < 0 1228391 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 196735.58 hash searches/s, 968.45 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744282; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 650700486 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 197151.43 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:00:37 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help] $ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:00:43 PDT 2008 Status ===================================== 081026 20:00:43 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228391 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5718, query id 160692348 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 ---TRANSACTION 0 1228390, ACTIVE 14 sec, process no 25013, OS thread id 2305843009335325264 fetching rows, thread declared inside InnoDB 270 mysql tables in use 3, locked 0 MySQL thread id 5713, query id 160692345 localhost 127.0.0.1 root Sending data select count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 and loginname='dan' group by roleid, ratingtypeid) sr1 join LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate Trx read view will not see trx with id >= 0 1228391, sees < 0 1228391 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 222313.61 hash searches/s, 824.36 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744282; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 652039038 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 223054.82 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:00:43 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help] $ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:00:48 PDT 2008 Status ===================================== 081026 20:00:48 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 5 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228391 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5719, query id 160692350 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 ---TRANSACTION 0 1228390, ACTIVE 19 sec, process no 25013, OS thread id 2305843009335325264 fetching rows, thread declared inside InnoDB 80 mysql tables in use 3, locked 0 MySQL thread id 5713, query id 160692345 localhost 127.0.0.1 root Sending data select count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 and loginname='dan' group by roleid, ratingtypeid) sr1 join LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate Trx read view will not see trx with id >= 0 1228391, sees < 0 1228391 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 252256.55 hash searches/s, 935.41 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744282; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 653304789 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 253099.58 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:00:48 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help] $ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:00:54 PDT 2008 Status ===================================== 081026 20:00:54 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 6 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228391 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5720, query id 160692352 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 ---TRANSACTION 0 1228390, ACTIVE 25 sec, process no 25013, OS thread id 2305843009335325264 fetching rows, thread declared inside InnoDB 340 mysql tables in use 3, locked 0 MySQL thread id 5713, query id 160692345 localhost 127.0.0.1 root Sending data select count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 and loginname='dan' group by roleid, ratingtypeid) sr1 join LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate Trx read view will not see trx with id >= 0 1228391, sees < 0 1228391 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 211895.18 hash searches/s, 784.20 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744282; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 654580606 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 212600.73 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:00:54 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help] $ date;mysql --host=localhost -u root --port=3307 --protocol=tcp --password=... -e "SHOW INNODB STATUS" -B Sun Oct 26 20:01:00 PDT 2008 Status ===================================== 081026 20:01:01 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 7 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 98749, signal count 98563 Mutex spin waits 0, rounds 24497841, OS waits 12862 RW-shared spins 248561, OS waits 63839; RW-excl spins 422385, OS waits 18338 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080927 13:44:42 Transaction: TRANSACTION 0 1179168, ACTIVE 0 sec, process no 25013, OS thread id 2305843009337422416 updating or deleting, thread declared inside InnoDB 262 mysql tables in use 1, locked 1 65 lock struct(s), heap size 14320, undo log entries 2 MySQL thread id 4751, query id 145117199 localhost 127.0.0.1 root updating delete from LEO_Role where wbs like concat(@fromwbs,"%") Foreign key constraint fails for table `develop080401/TeamMember`: , CONSTRAINT `TeamMember_ibfk_2` FOREIGN KEY (`roleid`) REFERENCES `LEO_Role` (`ID`) Trying to delete or update in parent table, in index `PRIMARY` tuple: DATA TUPLE: 52 fields; 0: len 4; hex 80008b55; asc U;; 1: len 6; hex 00000011fe20; asc ;; 2: len 7; hex 00000030801397; asc 0 ;; 3: len 5; hex 4750482d35; asc GPH-5;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b33; asc 3;; 8: len 4; hex 80000000; asc ;; 9: len 27; hex 3030312e3030342e3030312e3030312e3030312e3030312e303038; asc 001.004.001.001.001.001.008;; 10: SQL NULL; 11: len 0; hex ; asc ;; 12: len 0; hex ; asc ;; 13: len 0; hex ; asc ;; 14: len 0; hex ; asc ;; 15: len 0; hex ; asc ;; 16: len 0; hex ; asc ;; 17: len 0; hex ; asc ;; 18: len 0; hex ; asc ;; 19: len 0; hex ; asc ;; 20: len 0; hex ; asc ;; 21: len 0; hex ; asc ;; 22: len 0; hex ; asc ;; 23: len 0; hex ; asc ;; 24: len 0; hex ; asc ;; 25: SQL NULL; 26: len 8; hex 8000124367a6cdee; asc Cg ;; 27: SQL NULL; 28: len 1; hex 58; asc X;; 29: len 0; hex ; asc ;; 30: len 0; hex ; asc ;; 31: len 0; hex ; asc ;; 32: len 0; hex ; asc ;; 33: len 0; hex ; asc ;; 34: len 0; hex ; asc ;; 35: len 0; hex ; asc ;; 36: SQL NULL; 37: SQL NULL; 38: SQL NULL; 39: SQL NULL; 40: SQL NULL; 41: SQL NULL; 42: SQL NULL; 43: len 0; hex ; asc ;; 44: SQL NULL; 45: len 4; hex 80000000; asc ;; 46: len 4; hex 80000000; asc ;; 47: len 4; hex 80000000; asc ;; 48: len 0; hex ; asc ;; 49: len 2; hex 8000; asc ;; 50: len 2; hex 8000; asc ;; 51: len 4; hex 80008a3f; asc ?;; But in child table `develop080401/TeamMember`, in index `roleid`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008b55; asc U;; 1: len 4; hex 80000072; asc r;; ------------ TRANSACTIONS ------------ Trx id counter 0 1228391 Purge done for trx's n:o < 0 1227808 undo n:o < 0 0 History list length 0 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 25013, OS thread id 2305843009341846096 MySQL thread id 5721, query id 160692354 localhost 127.0.0.1 root SHOW INNODB STATUS ---TRANSACTION 0 1227851, not started, process no 25013, OS thread id 2305843009338995280 MySQL thread id 5700, query id 160320719 saturn.spicenet.net 63.147.88.8 ddevelop080401 ---TRANSACTION 0 1228390, ACTIVE 32 sec, process no 25013, OS thread id 2305843009335325264 fetching rows, thread declared inside InnoDB 303 mysql tables in use 3, locked 0 MySQL thread id 5713, query id 160692345 localhost 127.0.0.1 root Sending data select count(*) from (select max(ratingdate) as mrd, LEO_RoleRating.roleid, ratingtypeid from LEO_RoleRating join SelectedRoles on LEO_RoleRating.roleid = SelectedRoles.roleid where status>4 and loginname='dan' group by roleid, ratingtypeid) sr1 join LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate Trx read view will not see trx with id >= 0 1228391, sees < 0 1228391 -------- FILE I/O -------- I/O thread 0 state: waiting for i/o request (insert buffer thread) I/O thread 1 state: waiting for i/o request (log thread) I/O thread 2 state: waiting for i/o request (read thread) I/O thread 3 state: waiting for i/o request (write thread) Pending normal aio reads: 0, aio writes: 0, ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0 Pending flushes (fsync) log: 0; buffer pool: 0 631323 OS file reads, 201400 OS file writes, 130551 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 0, seg size 2, 1623 inserts, 1623 merged recs, 1328 merges Hash table size 17393, used cells 7784, node heap has 16 buffer(s) 222024.28 hash searches/s, 823.17 non-hash searches/s --- LOG --- Log sequence number 1 2007349891 Log flushed up to 1 2007349891 Last checkpoint at 1 2007349891 0 pending log writes, 0 pending chkp writes 110932 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 35744282; in additional pool allocated 1048576 Buffer pool size 512 Free buffers 0 Database pages 496 Modified db pages 0 Pending reads 0 Pending writes: LRU 0, flush list 0, single page 0 Pages read 858749, created 29634, written 169179 0.00 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- ROW OPERATIONS -------------- 1 queries inside InnoDB, 0 queries in queue 2 read views open inside InnoDB Main thread process no. 25013, id 2305843009311666768, state: waiting for server activity Number of rows inserted 7260574, updated 179988, deleted 6847411, read 656140182 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 222764.75 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ [2008-10-26 20:01:01 dcohen@saturn /srv/www/htdocs/sonario/CPSEnergy080818/help]