mysql> select # sql doesn't know my extreme protocol 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 # gearing or better and loginname='dan' group by roleid, ratingtypeid) sr1 join -- LEO_RoleRating -- weird problem related to transactions/locking?(*) LEO_RoleRating sr2 where sr1.roleid=sr2.roleid and sr1.ratingtypeid=sr2.ratingtypeid and mrd=sr2.ratingdate; -> -> -> -> -> -> -> -> -> -> -> +----------+ | count(*) | +----------+ | 2716 | +----------+ 1 row in set (31.67 sec) mysql> SHOW INNODB STATUS; ===================================== 080729 8:08:46 INNODB MONITOR OUTPUT ===================================== Per second averages calculated from the last 55 seconds ---------- SEMAPHORES ---------- OS WAIT ARRAY INFO: reservation count 42597, signal count 42613 Mutex spin waits 0, rounds 15494060, OS waits 8010 RW-shared spins 115598, OS waits 24287; RW-excl spins 233988, OS waits 8080 ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 080713 10:27:57 Transaction: TRANSACTION 0 973412, ACTIVE 0 sec, process no 25013, OS thread id 2305843009335325264 updating or deleting, thread declared inside InnoDB 437 mysql tables in use 1, locked 1 45 lock struct(s), heap size 6752, undo log entries 1 MySQL thread id 1504, query id 8260762 Troycpe2-187.ptcnh.net 74.85.109.188 gkdevelop080401 updating delete from LEO_Role where wbs like concat( NAME_CONST('fromwbs',_latin1'001.004.001.001'),"%") Foreign key constraint fails for table `develop080401/LEO_RoleFilling`: , CONSTRAINT `LEO_RoleFilling_ibfk_1` 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 80008a85; asc ;; 1: len 6; hex 0000000eda64; asc d;; 2: len 7; hex 00000028270110; asc (' ;; 3: len 21; hex 4d616e6167696e67204d6574616c6c757267697374; asc Managing Metallurgist;; 4: len 0; hex ; asc ;; 5: len 4; hex 80000026; asc &;; 6: len 4; hex 80000000; asc ;; 7: len 4; hex 80008b22; asc ";; 8: len 4; hex 80000000; asc ;; 9: len 19; hex 3030312e3030342e3030312e3030312e303031; asc 001.004.001.001.001;; 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 8000124355b6ed94; asc CU ;; 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 80000216; asc ;; But in child table `develop080401/LEO_RoleFilling`, in index `_RoleID`, there is a record: PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80008a85; asc ;; 1: len 4; hex 8000006d; asc m;; ------------ TRANSACTIONS ------------ Trx id counter 0 1036871 Purge done for trx's n:o < 0 1035892 undo n:o < 0 0 History list length 3 Total number of lock structs in row lock hash table 0 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1036870, not started, process no 25013, OS thread id 2305843009337946704 MySQL thread id 2599, query id 21462218 localhost 127.0.0.1 root SHOW INNODB STATUS -------- 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 247380 OS file reads, 81855 OS file writes, 44271 OS fsyncs 0.45 reads/s, 27525 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, 755 inserts, 755 merged recs, 744 merges Hash table size 17393, used cells 7676, node heap has 16 buffer(s) 133340.68 hash searches/s, 544.97 non-hash searches/s --- LOG --- Log sequence number 1 1263544799 Log flushed up to 1 1263544799 Last checkpoint at 1 1263544799 0 pending log writes, 0 pending chkp writes 36236 log i/o's done, 0.00 log i/o's/second ---------------------- BUFFER POOL AND MEMORY ---------------------- Total memory allocated 31915754; 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 319679, created 15008, written 85218 0.76 reads/s, 0.00 creates/s, 0.00 writes/s Buffer pool hit rate 1000 / 1000 -------------- 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 4006823, updated 70780, deleted 3989725, read 317197677 0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 133757.91 reads/s ---------------------------- END OF INNODB MONITOR OUTPUT ============================ 1 row in set, 1 warning (0.00 sec) mysql>