Error: a record lock wait happens in a dictionary operation, index `CLUST_IND` of table `SYS_STATS`

[Elena Stepanova]

InnoDB: Error: a record lock wait happens in a dictionary operation!
InnoDB: index `CLUST_IND` of table `SYS_STATS`.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com
InnoDB: Assertion failure in thread 139707405539088 in file lock0lock.c line 1803
InnoDB: Failing assertion: 0

#5 0x00007f10385f7ee6 in abort () from /lib64/libc.so.6
#6 0x0000000000ac3359 in lock_rec_enqueue_waiting (type_mode=3, block=0x7f102fc869f0, heap_no=15,
    index=0x1a915d8, thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/lock/lock0lock.c:1803
#7 0x0000000000ac3c28 in lock_rec_lock_slow (impl=0, mode=3, block=0x7f102fc869f0, heap_no=15,
    index=0x1a915d8, thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/lock/lock0lock.c:2081
#8 0x0000000000ac3ead in lock_rec_lock (impl=0, mode=3, block=0x7f102fc869f0, heap_no=15,
    index=0x1a915d8, thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/lock/lock0lock.c:2138
#9 0x0000000000acac5f in lock_clust_rec_read_check_and_lock (flags=0, block=0x7f102fc869f0,
    rec=0x7f1037a6833a "", index=0x1a915d8, offsets=0x7f102a474460, mode=LOCK_X, gap_mode=0,
    thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/lock/lock0lock.c:5592
#10 0x00000000009b5699 in sel_set_rec_lock (block=0x7f102fc869f0, rec=0x7f1037a6833a "",
    index=0x1a915d8, offsets=0x7f102a474460, mode=3, type=0, thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/row/row0sel.c:1004
#11 0x00000000009b64f5 in row_sel (node=0x7f1018024420, thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/row/row0sel.c:1581
#12 0x00000000009b71e0 in row_sel_step (thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/row/row0sel.c:2101
#13 0x0000000000b10a4f in que_thr_step (thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/que/que0que.c:1234
#14 0x0000000000b10d83 in que_run_threads_low (thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/que/que0que.c:1319
#15 0x0000000000b10e97 in que_run_threads (thr=0x7f1018026dc8)
    at Percona-Server/storage/innobase/que/que0que.c:1356
#16 0x0000000000b110c7 in que_eval_sql (info=0x7f101800e3a8,
    sql=0xce8a90 "PROCEDURE DROP_TABLE_PROC () IS\nsys_foreign_id CHAR;\ntable_id CHAR;\nindex_id CHAR;\nforeign_id CHAR;\nfound INT;\nDECLARE CURSOR cur_fk IS\nSELECT ID FROM SYS_FOREIGN\nWHERE FOR_NAME = :table_name\nAND TO_B"..., reserve_dict_mutex=0, trx=0x7f101801e398)
    at Percona-Server/storage/innobase/que/que0que.c:1445
#17 0x00000000009a8cf5 in row_drop_table_for_mysql (name=0x7f102a474f40 "tmp/#sql416a_6_13a",
    trx=0x7f101801e398, drop_db=0)
    at Percona-Server/storage/innobase/row/row0mysql.c:3443
#18 0x000000000097a119 in ha_innobase::delete_table (this=0x7f10180180e0,
    name=0x1bd5ee0 "Percona-Server/mysql-test/var/tmp/#sql416a_6_13a")
    at Percona-Server/storage/innobase/handler/ha_innodb.cc:8382
#19 0x0000000000771856 in handler::ha_delete_table (this=0x7f10180180e0,
    name=0x1bd5ee0 "Percona-Server/mysql-test/var/tmp/#sql416a_6_13a")
    at Percona-Server/sql/handler.cc:3631
#20 0x00000000005bcd82 in rm_temporary_table (base=0x1355570,
    path=0x1bd5ee0 "Percona-Server/mysql-test/var/tmp/#sql416a_6_13a")
    at Percona-Server/sql/sql_base.cc:6040
#21 0x00000000005b6446 in close_temporary (table=0x1bd52d0, free_share=true, delete_table=true)
    at Percona-Server/sql/sql_base.cc:2244
#22 0x00000000005b633b in close_temporary_table (thd=0x1cb6550, table=0x1bd52d0, free_share=true,
    delete_table=true) at Percona-Server/sql/sql_base.cc:2216
#23 0x00000000006948a1 in mysql_alter_table (thd=0x1cb6550, new_db=0x1cc6190 "test",
    new_name=0x1cc5c30 "t1", create_info=0x7f102a477250, table_list=0x1cc5c68,
    alter_info=0x7f102a477330, order_num=0, order=0x0, ignore=false)
    at Percona-Server/sql/sql_table.cc:6849
#24 0x00000000008ad103 in Alter_table_statement::execute (this=0x1cc6308, thd=0x1cb6550)
    at Percona-Server/sql/sql_alter.cc:106
#25 0x000000000060e93a in mysql_execute_command (thd=0x1cb6550)
    at Percona-Server/sql/sql_parse.cc:4582
#26 0x0000000000611468 in mysql_parse (thd=0x1cb6550,
    rawbuf=0x1cc5b50 "ALTER TABLE t1 CHANGE COLUMN f1 f1 INT DEFAULT 2", length=48,
    parser_state=0x7f102a478ae0) at Percona-Server/sql/sql_parse.cc:5811
#27 0x00000000006055ae in dispatch_command (command=COM_QUERY, thd=0x1cb6550,
    packet=0x1cb9681 "ALTER TABLE t1 CHANGE COLUMN f1 f1 INT DEFAULT 2", packet_length=48)
    at Percona-Server/sql/sql_parse.cc:1061
#28 0x00000000006048a7 in do_command (thd=0x1cb6550)
    at Percona-Server/sql/sql_parse.cc:788
#29 0x00000000006e7bce in do_handle_one_connection (thd_arg=0x1cb6550)
    at Percona-Server/sql/sql_connect.cc:1484
#30 0x00000000006e768c in handle_one_connection (arg=0x1cb6550)
    at Percona-Server/sql/sql_connect.cc:1391
#31 0x00007f10398ffa4f in start_thread () from /lib64/libpthread.so.0
#32 0x00007f103869482d in clone () from /lib64/libc.so.6

~percona-core/percona-server/5.5
bzr version-info
revision-id: <email address hidden>
date: 2012-10-31 17:45:42 +0100
revno: 344

RQG grammar:

thread2_init:
  CREATE TABLE t1 (i INT) ENGINE=InnoDB ; CREATE TEMPORARY TABLE t1 (i INT, KEY(i)) SELECT 1 AS i; ALTER TABLE t1 ADD COLUMN f1 INT DEFAULT 1 ;

thread1:
  OPTIMIZE TABLE t1 | ANALYZE TABLE t1 ;

query:
  ALTER TABLE t1 CHANGE COLUMN f1 f1 INT DEFAULT 2 ;

# End of RQG grammar

RQG command line:

perl runall.pl --threads=6 --duration=600 --queries=100M --mysqld=--innodb-use-sys-stats-table=1 --grammar=test.yy --skip-gendata --basedir1=<basedir>

[Valerii Kravchuk (valerii-kravchuk)]

I was able to repeat this (from the error log):

121107 10:52:44 InnoDB: Error: a record lock wait happens in a dictionary operation!
InnoDB: index `CLUST_IND` of table `SYS_STATS`.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com
121107 10:52:44 InnoDB: Error: dict X latch held in srv_suspend_mysql_thread

[Alexey Kopytov (akopytov)]

Is this repeatable with upstream InnoDB?

[Valerii Kravchuk (valerii-kravchuk)]

Alexey,

As far as I remember upstream InnoDB does not have this variable, --innodb-use-sys-stats-table=1. I had not (yet) tried to try without it, but I assume it is essential for the error.

[Alexey Kopytov (akopytov)]

Valerii,

Right, I misread the output. Of course, there are neither --innodb-use-sys-stats-table nor SYS_STATS in upstream InnoDB.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-2828