Two more examples of this bug hit (Percona Server 5.5.28-29.3-log), one occur during optimize table, second during alter table: ------------------------------------------------------------------------------------------------------- ---TRANSACTION 18F02058A, ACTIVE 590 sec updating or deleting mysql tables in use 2, locked 2 2 lock struct(s), heap size 376, 1 row lock(s) MySQL thread id 121533385, OS thread handle 0x4c78b940, query id 933988129 192.168.100.179 ubt.globS3.mysql Updating UPDATE P3Machine SET TamperStatus=0, MonitorStatus=(MonitorStatus & ~(1 | 8 | 16 | 32)), LastStatusUpdate=CURRENT_TIMESTAMP, LastHandshake=CURRENT_TIMESTAMP, UpgradeStatus=0 WHERE Id = 30016713 LIMIT 1 TABLE LOCK table `ubt_streamline`.`P3Machine` trx id 18F02058A lock mode IX RECORD LOCKS space id 88886 page no 30041 n bits 80 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 18F02058A lock_mode X locks rec but not gap ---TRANSACTION 18F020587, ACTIVE 590 sec dropping table 18 lock struct(s), heap size 3112, 27 row lock(s), undo log entries 14 MySQL thread id 121519857, OS thread handle 0x4eb99940, query id 933988100 localhost root rename result table OPTIMIZE TABLE `P3ConfigurationValue` TABLE LOCK table `SYS_TABLES` trx id 18F020587 lock mode IS RECORD LOCKS space id 0 page no 61095 n bits 208 index `CLUST_IND` of table `SYS_TABLES` trx id 18F020587 lock mode S RECORD LOCKS space id 0 page no 388855 n bits 240 index `CLUST_IND` of table `SYS_TABLES` trx id 18F020587 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id 18F020587 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 176 index `FOR_IND` of table `SYS_FOREIGN` trx id 18F020587 lock mode S TABLE LOCK table `SYS_INDEXES` trx id 18F020587 lock mode IS RECORD LOCKS space id 0 page no 94396 n bits 288 index `CLUST_IND` of table `SYS_INDEXES` trx id 18F020587 lock mode S TABLE LOCK table `SYS_STATS` trx id 18F020587 lock mode IX RECORD LOCKS space id 0 page no 154305 n bits 72 index `CLUST_IND` of table `SYS_STATS` trx id 18F020587 lock_mode X TABLE LOCK table `SYS_FIELDS` trx id 18F020587 lock mode IX TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: Error: Waited for 590 secs for hash index ref_count (1) to drop to 0. index: "PRIMARY" table: "devubt_streamline/#sql2-16ae-73e3ef1" InnoDB: Error: Waited for 595 secs for hash index ref_count (1) to drop to 0. index: "PRIMARY" table: "devubt_streamline/#sql2-16ae-73e3ef1" 130210 6:17:47 [Warning] Too many connections InnoDB: Error: Waited for 600 secs for hash index ref_count (1) to drop to 0. index: "PRIMARY" table: "devubt_streamline/#sql2-16ae-73e3ef1" 130210 6:17:51 InnoDB: Assertion failure in thread 1320786240 in file dict0dict.c line 1883 ------------------------------------------------------------------------------------------------------- ---TRANSACTION 1AB6986D7, ACTIVE 591 sec dropping table 18 lock struct(s), heap size 3112, 29 row lock(s), undo log entries 16 MySQL thread id 73516471, OS thread handle 0x4dcf4940, query id 564000147 192.168.100.183 root rename result table ALTER TABLE `P3MessageLog` DROP FOREIGN KEY `FK_P3MessageLog_2` TABLE LOCK table `SYS_TABLES` trx id 1AB6986D7 lock mode IS RECORD LOCKS space id 0 page no 149827 n bits 208 index `CLUST_IND` of table `SYS_TABLES` trx id 1AB6986D7 lock mode S RECORD LOCKS space id 0 page no 388855 n bits 240 index `CLUST_IND` of table `SYS_TABLES` trx id 1AB6986D7 lock mode S TABLE LOCK table `SYS_FOREIGN` trx id 1AB6986D7 lock mode IS RECORD LOCKS space id 0 page no 47 n bits 176 index `FOR_IND` of table `SYS_FOREIGN` trx id 1AB6986D7 lock mode S TABLE LOCK table `SYS_INDEXES` trx id 1AB6986D7 lock mode IS RECORD LOCKS space id 0 page no 97659 n bits 296 index `CLUST_IND` of table `SYS_INDEXES` trx id 1AB6986D7 lock mode S TABLE LOCK table `SYS_STATS` trx id 1AB6986D7 lock mode IX RECORD LOCKS space id 0 page no 154305 n bits 72 index `CLUST_IND` of table `SYS_STATS` trx id 1AB6986D7 lock_mode X TABLE LOCK table `SYS_FIELDS` trx id 1AB6986D7 lock mode IX TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---TRANSACTION 1AB6986A5, ACTIVE 591 sec updating or deleting mysql tables in use 2, locked 2 1695 lock struct(s), heap size 162232, 18815 row lock(s) MySQL thread id 73516470, OS thread handle 0x4e2cb940, query id 564000093 192.168.100.179 ubt.globS3.mysql Updating UPDATE P3Machine SET CoreVersion='1.9.0.8', WindowsVersion='Microsoft Windows NT 5.1.2600 Service Pack 3', InstalledPrograms='*** TABLE LOCK table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock mode IX RECORD LOCKS space id 98133 page no 17 n bits 80 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 18 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 29 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 64 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 81 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 94 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 105 n bits 80 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 128 n bits 80 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X RECORD LOCKS space id 98133 page no 149 n bits 88 index `PRIMARY` of table `ubt_streamline`.`P3Machine` trx id 1AB6986A5 lock_mode X TOO MANY LOCKS PRINTED FOR THIS TRX: SUPPRESSING FURTHER PRINTS ---------------------------- END OF INNODB MONITOR OUTPUT ============================ InnoDB: Error: Waited for 590 secs for hash index ref_count (1) to drop to 0. index: "FK_P3MessageLog_1" table: "ubt_streamline/#sql2-3162-461c5b7" InnoDB: Error: Waited for 595 secs for hash index ref_count (1) to drop to 0. index: "FK_P3MessageLog_1" table: "ubt_streamline/#sql2-3162-461c5b7" InnoDB: Error: Waited for 600 secs for hash index ref_count (1) to drop to 0. index: "FK_P3MessageLog_1" table: "ubt_streamline/#sql2-3162-461c5b7" 130227 3:18:04 InnoDB: Assertion failure in thread 1305430336 in file dict0dict.c line 1883 -------------------------------------------------------------------------------------------------------