MySQL Bugs: #70654: [ERROR] Table x contains y indexes inside InnoDB, which is different from MySQL

Bug #70654	[ERROR] Table x contains y indexes inside InnoDB, which is different from MySQL
Submitted:	18 Oct 2013 10:11	Modified:	1 Dec 2013 18:00
Reporter:	Roel Van de Paar	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S1 (Critical)
Version:	5.5.34	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Seen in MySQL Server 5.5.34:
2013-10-17 14:34:44 11889 [ERROR] Table test/table10_innodb_compressed contains 14 indexes inside InnoDB, which is different from the number of indexes 13 defined in the MySQL

In 5.5.34, this is also preceded by several of these:
2013-10-17 14:32:17 11889 [ERROR] /ssd/mysql-5.5.33.t12-linux-x86_64-debug/bin/mysqld: Deadlock found when trying to get lock; try restarting transaction
2013-10-17 14:32:17 11889 [ERROR] /ssd/mysql-5.5.33.t12-linux-x86_64-debug/bin/mysqld: Sort aborted: Deadlock found when trying to get lock; try restarting tran
saction

In 5.6.14, the same Deadlock warnings are shown, but it does not fail in regards the number of indexes

In 5.7.2-m12, none of it is shown, no Deadlock, no indexes error (!)

How to repeat:
1. wget http://cdn.mysql.com/Downloads/MySQL-5.5/mysql-5.5.34-linux2.6-x86_64.tar.gz
2. Use RQG @ Revno: 931 roel.vandepaar@percona.com-20131017105851-i737r0zi1scyu6o7
3. $ cat cmd33_MS
ps -ef | grep 'cmdrun_33' | grep -v grep | awk '{print $2}' | xargs sudo kill -9 > /dev/null 2>&1
rm -Rf /ssd/472623/cmdrun_33
mkdir /ssd/472623/cmdrun_33
cd /ssd/randgen
bash -c "set -o pipefail; perl /ssd/randgen/runall.pl --queries=100000000 --seed=8528 --duration=400 --querytimeout=60 --short_column_names --reporter=Shutdown,Backtrace,QueryTimeout,ErrorLog,ErrorLogAlarm --mysqld=--log-output=none --mysqld=--log-output=FILE --mysqld=--general_log --mysqld=--general_log_file=out.sql --mysqld=--sql_mode=ONLY_FULL_GROUP_BY --grammar=conf/percona_qa/34411/34411.yy --gendata=conf/percona_qa/34411/34411.zz --threads=15 --basedir=/ssd/mysql-5.6.14-linux-glibc2.5-x86_64 --mask-level=1 --notnull --mysqld=--innodb_change_buffering=all --mysqld=--skip-innodb_doublewrite --mysqld=--innodb_flush_log_at_trx_commit=0 --mysqld=--innodb_log_buffer_size=1048576 --mysqld=--innodb_log_file_size=10485761 --mysqld=--innodb_log_files_in_group=10 --mysqld=--query_cache_type=1 --mtr-build-thread=761 --mask=37502 --vardir1=/ssd/472623/cmdrun_33 > /ssd/472623/cmdrun33.log 2>&1"
tail -n1 /ssd/472623/cmdrun33.log

Known 5.5 bug, a simple concurrent DDL workload will cause it last time i checked
it's fixed in 5.6/5.7.

Bug 12890606 - INNODB COMPLAINS ABOUT NUMBER OF INDEXES DIFFERENT FROM WHAT IS DEFINED IN MYSQL

Ok, see it is logged in another bug system already. Thanks

Sample testcase for 5.5

-----------
delimiter ;
drop table if exists t1;
create table t1(a int,b int,c int)engine=innodb;
drop procedure if exists p1;
delimiter $
create procedure p1()
begin
  declare continue handler for sqlexception begin end;
  repeat
    if rand()>0.5 then alter ignore table t1 add unique key a(a); end if;
	if rand()>0.5 then alter ignore table t1 add key b(b); end if;
	if rand()>0.5 then alter ignore table t1 add unique key ba(b,a); end if;
	if rand()>0.5 then alter ignore table t1 add key cba(c,b,a); end if;
	if rand()>0.5 then alter ignore table t1 add key abc(a,b,c); end if;
	if rand()>0.5 then alter ignore table t1 add key cb(c,b); end if;
	if rand()>0.5 then alter ignore table t1 drop key a; end if;
	if rand()>0.5 then alter ignore table t1 drop key b; end if;
	if rand()>0.5 then alter ignore table t1 drop key ba; end if;
	if rand()>0.5 then alter ignore table t1 drop key cba; end if;
	if rand()>0.5 then alter ignore table t1 drop key abc; end if;
	if rand()>0.5 then alter ignore table t1 drop key cb; end if;
	if rand()>0.5 then show table status like 't1'; end if;
  until 1=2 end repeat;
end $

delimiter ;
-----------

call p1();   #call in two connections

Shane, nice. Thanks

It says that it is duplicate but it does not say duplicate of what bug. Will be nice to have a version it is fixed. Is it fixed in 5.5 as well?