Percona XtraDB Cluster - HA scalable solution for MySQL

Status for TOI threads

Reported by Raghavendra D Prabhu on 2013-09-02
6
This bug affects 1 person
Affects Status Importance Assigned to Milestone
Percona XtraDB Cluster
Status tracked in Trunk
5.6
Undecided
Unassigned
Trunk
Undecided
Raghavendra D Prabhu

Bug Description

From InnoDB status:

 ---TRANSACTION 75A, not started
 MySQL thread id 24, OS thread handle 0x7fa1fc0a1700, query id 1697 localhost 127.0.0.1 root checking permissions
 DROP TABLE IF EXISTS c

From GDB trace:

Thread 4 (Thread 0x7fa1fc0a1700 (LWP 63486)):
#0 0x00007fa20f1e703f in pthread_cond_wait@@GLIBC_2.3.2 () from /usr/lib/libpthread.so.0
#1 0x00007fa20cce5b25 in wait (this=0x7fa1fc09dff0, cond=...) at galerautils/src/gu_lock.hpp:56
#2 galera::Monitor<galera::ReplicatorSMM::CommitOrder>::enter (this=this@entry=0x3099a08, obj=...) at galera/src/monitor.hpp:121
#3 0x00007fa20cce078a in galera::ReplicatorSMM::to_isolation_begin (this=0x3098f90, trx=0x7fa194017300) at galera/src/replicator_smm.cpp:965
#4 0x00007fa20ccf4074 in galera_to_execute_start (gh=<optimized out>, conn_id=24, key=<optimized out>, key_len=1, query=0x7fa194008560, query_len=90, global_seqno=0x38eb398) at galera/src/wsrep_provider.cpp:583
#5 0x000000000068369b in wsrep_TOI_begin (table_list=<optimized out>, table_=0x0, db_=0x0, thd=0x38e97b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/wsrep_mysqld.cc:1115
#6 wsrep_to_isolation_begin (thd=thd@entry=0x38e97b0, db_=db_@entry=0x0, table_=table_@entry=0x0, table_list=<optimized out>) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/wsrep_mysqld.cc:1250
#7 0x00000000005a1d08 in mysql_execute_command (thd=thd@entry=0x38e97b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_parse.cc:3657
#8 0x00000000005a6de1 in mysql_parse (thd=thd@entry=0x38e97b0, rawbuf=rawbuf@entry=0x7fa194004c10 "DROP TABLE IF EXISTS c", length=length@entry=22, parser_state=parser_state@entry=0x7fa1fc0a05e0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_parse.cc:6471
#9 0x00000000005a7a53 in wsrep_mysql_parse (thd=thd@entry=0x38e97b0, rawbuf=0x7fa194004c10 "DROP TABLE IF EXISTS c", length=22, parser_state=parser_state@entry=0x7fa1fc0a05e0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_parse.cc:6278
#10 0x00000000005a8a29 in dispatch_command (command=command@entry=COM_QUERY, thd=thd@entry=0x38e97b0, packet=packet@entry=0x38ecac1 "DROP TABLE IF EXISTS c ", packet_length=packet_length@entry=23) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_parse.cc:1254
#11 0x00000000005aa934 in do_command (thd=0x38e97b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_parse.cc:885
#12 0x00000000006582da in do_handle_one_connection (thd_arg=thd_arg@entry=0x38e97b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_connect.cc:1512
#13 0x000000000065850e in handle_one_connection (arg=0x38e97b0) at /media/Tintin/Work/code/percona-xtradb-cluster/trunk/Percona-Server/sql/sql_connect.cc:1420
#14 0x00007fa20f1e30a2 in start_thread () from /usr/lib/libpthread.so.0
#15 0x00007fa20da46a2d in clone () from /usr/lib/libc.so.6

===============================================

So, as can be seen, the transaction is not waiting for permissions but is instead waiting on TOI.

Added.

===========================================================================

 LIST OF TRANSACTIONS FOR EACH SESSION:
 ---TRANSACTION 0, not started
 MySQL thread id 30, OS thread handle 0x7fe36c6bd700, query id 3120 localhost root sleeping
 show engine innodb status
 ---TRANSACTION A98, not started
 MySQL thread id 26, OS thread handle 0x7fe36c6fe700, query id 2999 localhost 127.0.0.1 root Waiting for table metadata lock
 DROP TABLE `table100_innodb_compressed_int`
 ---TRANSACTION ADA, not started
 MySQL thread id 25, OS thread handle 0x7fe36c73f700, query id 3018 localhost 127.0.0.1 root Preparing for TO isolation
 DROP TABLE IF EXISTS s
 ---TRANSACTION AA1, not started
 MySQL thread id 23, OS thread handle 0x7fe3701e2700, query id 2963 localhost 127.0.0.1 root Preparing for TO isolation
 DROP TABLE `table100_innodb_compressed_int_autoinc`
 ---TRANSACTION ADD, not started
 MySQL thread id 20, OS thread handle 0x7fe3702a5700, query id 3020 localhost 127.0.0.1 root Waiting for table metadata lock
 DELETE FROM `table100_innodb_compressed_int` WHERE `col_varchar_latin1_key` = 168 ORDER BY `col_bigint`,`col_bigint_key`,`col_char_12_latin1`,`col_char_12_latin1_key`,`col_char_12_utf8`,`col_char_12_utf8_key`,`c ol_int`,`col_int_key`,`col_varchar_latin1`,`col_varchar_latin1_key`,`col_varchar_utf8`,`col_varchar_utf8_key`,`pk` LIMIT 1
 ---TRANSACTION AA8, not started
 MySQL thread id 19, OS thread handle 0x7fe3702e6700, query id 3066 localhost 127.0.0.1 root Preparing for TO isolation
 ALTER TABLE q RENAME `table1_innodb_compressed_key_pk_parts_2_char`
 ---TRANSACTION A87, not started
 MySQL thread id 13, OS thread handle 0x7fe3881d4700, query id 3010 localhost 127.0.0.1 root Preparing for TO isolation
 CREATE TABLE x LIKE `table1_innodb_default_key_pk_parts_2_char`
 ---TRANSACTION AF4, ACTIVE 318 sec
 mysql tables in use 1, locked 1
 8 lock struct(s), heap size 1248, 51 row lock(s), undo log entries 1
 MySQL thread id 16, OS thread handle 0x7fe38804f700, query id 3105 localhost 127.0.0.1 root wsrep in pre-commit stage
 DELETE FROM `table1000_innodb_default_int_autoinc` WHERE `col_int_key` IN ( 'g' , 3398041600 , 5 , 89 , 160 ) ORDER BY `col_bigint`,`col_bigint_key`,`col_char_12_latin1`,`col_char_12_latin1_key`,`col_char_12_utf 8`,`col_char_12_utf8_key`,`col_int`,`col_int_key`,`col_varchar_latin1`,`col_varchar_latin1_key`,`col_varchar_utf8`,`col_varchar_utf8_key`,`pk` LIMIT 1
 TABLE LOCK table `test`.`table1000_innodb_default_int_autoinc` trx id AF4 lock mode IX

 =============================================================================

Changed in percona-xtradb-cluster:
milestone: none → 5.5.33-23.7.6
status: New → Fix Committed
assignee: nobody → Raghavendra D Prabhu (raghavendra-prabhu)
Changed in percona-xtradb-cluster:
status: Fix Committed → Fix Released
To post a comment you must log in.
This report contains Public information  Edit
Everyone can see this information.

Other bug subscribers