MySQL Bugs: #64901: InnoDB: Assertion failure in thread 34387022112 in file rem0cmp.cc line 584

Bug #64901	InnoDB: Assertion failure in thread 34387022112 in file rem0cmp.cc line 584
Submitted:	7 Apr 2012 18:43	Modified:	4 May 2012 23:03
Reporter:	charlie barkin	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.6.4-m7, 5.6.5-m8	OS:	Any (FreeBSD 7.2-STABLE amd64, Mac OS X 10.5.6)
Assigned to:		CPU Architecture:	Any
Tags:	innodb

Description:
Hello

Some days ago I'm decided to test fulltext search with innodb engine. It look works, but mysqld crashs randomly with such errors:

120407 5:16:47 InnoDB: Assertion failure in thread 34387022112 in file rem0cmp.cc line 584
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
120407 5:16:47 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=8388608
max_used_connections=32
max_threads=60
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4555496 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

120407 18:22:57 InnoDB: Assertion failure in thread 34387025312 in file fts0que.cc line 2778
InnoDB: Failing assertion: ret == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
120407 18:22:57 - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=134217728
read_buffer_size=8388608
max_used_connections=50
max_threads=60
thread_count=1
connection_count=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 4555496 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

How to repeat:
I don't know how to repeat this crashes .. I have a base with a few huge tables with a lot of text fields and a lot of queries with fulltext search.

Suggested fix:
May be strings "fts0que.cc line 2778" and "rem0cmp.cc line 584" could say something imortant to mysql developers.

fts0que.cc line 2778 is in function fts_query_read_node()
/* Lookup the word in our rb tree, it must exist. */
	ret = rbt_search(query->word_freqs, &parent, term);

	ut_a(ret == 0);

rem0cmp.cc line 584 is in function cmp_data_data_slow_like_suffix()

	ut_error;	// FIXME:
	return(1);

It looks like cmp_data_data_slow_like_suffix shouldn't be used. But it is, in cmp_data_data_like_suffix().  cmp_data_data_like_suffix() is called from cmp_dfield_dfield_like_suffix().  cmp_dfield_dfield_like_suffix() is called from eval_cmp_like() in the case of op IB_LIKE_SUFFIX.  So I assume somebody runs a SQL with LIKE '%str' or similar.

testcase for the suffix % bug:

drop table if exists t1;
create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
title varchar(200),body text,fulltext(title,body)) engine=innodb;
insert into t1 set body='test';
select * from t1 where match(title,body) against('%test');

Thank you for the bug report. Verified just as described using Shane's text case:

macbook-pro:trunk openxs$ bin/mysql -uroot test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1
Server version: 5.6.5-m8-debug Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> drop table if exists t1;
Query OK, 0 rows affected (0.02 sec)

mysql> create table t1 (FTS_DOC_ID bigint unsigned auto_increment not null primary key,
    -> title varchar(200),body text,fulltext(title,body)) engine=innodb;
Query OK, 0 rows affected (0.13 sec)

mysql> insert into t1 set body='test';
Query OK, 1 row affected (0.01 sec)

mysql> select * from t1 where match(title,body) against('%test');
ERROR 2013 (HY000): Lost connection to MySQL server during query
...

120409 17:42:25  InnoDB: test/FTS_0000000000000021_STOPWORDS not found.
ft_init_ext()
keynr=1, '%test'
NL search
120409 17:42:26  InnoDB: Assertion failure in thread 4511842304 in file fts0que.cc line 850
InnoDB: Failing assertion: ret == 0
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.6/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
14:42:26 UTC - mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help
diagnose the problem, but since we have already crashed, 
something is definitely wrong and this may fail.

key_buffer_size=8388608
read_buffer_size=131072
max_used_connections=1
max_threads=151
thread_count=1
connection_count=1
It is possible that mysqld could use up to 
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 68018 K  bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

Thread pointer: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = 0 thread_stack 0x40000
120409 17:42:29 mysqld_safe mysqld restarted

...

Added to changelog for 5.6.6: 

Including a % character in a query using an InnoDB FULLTEXT index
could cause a crash. (FULLTEXT indexes for InnoDB tables are a new
feature, still under development.)