Percona Server moved to https://jira.percona.com/projects/PS

Bug #1086227
Comment #9

Comment 9 for bug 1086227

Revision history for this message

Przemek (pmalkowski) wrote on 2013-02-27:

Two more examples of this bug hit (Percona Server 5.5.28-29.3-log), one occur during optimize table, second during alter table:

-------------------------------------------------------------------------------------------------------

        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

-------------------------------------------------------------------------------------------------------

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

-------------------------------------------------------------------------------------------------------