MySQL Bugs: #44615: Mysql Craches On Select 'Like' queries on large tables

Bug #44615	Mysql Craches On Select 'Like' queries on large tables
Submitted:	1 May 2009 19:07	Modified:	24 Oct 2009 9:04
Reporter:	Steve Kirby	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: General	Severity:	S3 (Non-critical)
Version:	5.0.77	OS:	Linux
Assigned to:		CPU Architecture:	Any

Description:
Crashes everytime query is run (see below)

Crash Report:

090501 12:00:19 - mysqld got signal 11 ;
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=131072
max_used_connections=33
max_connections=50
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 239871 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

thd=0x7f3ec80787b0
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...
Cannot determine thread, fp=0x7f3ec80787b0, backtrace may not be correct.
Bogus stack limit or frame pointer, fp=0x7f3ec80787b0, stack_bottom=0x466b0000, thread_stack=262144, aborting backtrace.
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x178ab00 = select prospectnumber,firstname,lastname,city,state,zip,classcode,homephone,workphone,email,broker,ib,lastseen,lastedit from tbAccounts where 1 and (lastname like 'ki%' or Slastname like 'ki%') order by lastname,firstname asc limit 0,100
thd->thread_id=24721
The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains
information that should help you find out what is causing the crash.
090501 12:00:19 mysqld ended

How to repeat:
Run listed query

Suggested fix:
Report error only - should not kill server

Thank you for the problem report. Please, send your my.cnf file content and the results of:

free

Linux command. Send also the results of:

explain  select
prospectnumber,firstname,lastname,city,state,zip,classcode,homephone, workphone,email,broker,ib,lastseen,lastedit from tbAccounts   
where 1    and (lastname like 'ki%' or Slastname
like 'ki%')  order by lastname,firstname asc limit 0,100\G

show create table tbAccounts\G

show table status like 'tbAccounts';

Here is the results of the info you requested.  Thank you for your quick response...

my.conf:
[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1

long_query_time         = 2
log-slow-queries        = /var/log/mysql-slow.log

# Added to tune performance
query_cache_type=1
query_cache_limit=2M
query_cache_size=64M

key_buffer=128M
table_cache=1024
thread_cache=64

interactive_timeout=100

max_allowed_packet=3145728
big-tables
set-variable = max_connections=50

# [myisamchk]
# key_buffer=128M
# sort_buffer=128M
# read_buffer=32M
# write_buffer=32M

# END Performance adds

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

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

[poser@boof ~]$ free
             total       used       free     shared    buffers     cached
Mem:      10261852    3455432    6806420          0     162612    2786640
-/+ buffers/cache:     506180    9755672
Swap:      2031608          0    2031608

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

mysql> explain  select
    -> prospectnumber,firstname,lastname,city,state,zip,classcode,homephone,
    -> workphone,email,broker,ib,lastseen,lastedit from tbAccounts   
    -> where 1    and (lastname like 'ki%' or Slastname
    -> like 'ki%')  order by lastname,firstname asc limit 0,100\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: tbAccounts
         type: index_merge
possible_keys: lastname,Slastname
          key: lastname,Slastname
      key_len: 102,102
          ref: NULL
         rows: 3966
        Extra: Using sort_union(lastname,Slastname); Using where; Using filesort
1 row in set (0.00 sec)

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

mysql> show create table tbAccounts\G
*************************** 1. row ***************************
       Table: tbAccounts
Create Table: CREATE TABLE `tbAccounts` (
  `prospectnumber` bigint(6) NOT NULL auto_increment,
  `prefix` varchar(100) NOT NULL default '',
  `firstname` varchar(100) NOT NULL default '',
  `midname` varchar(100) NOT NULL default '',
  `lastname` varchar(100) NOT NULL default '',
  `suffix` varchar(100) NOT NULL default '',
  `spousePreTitle` varchar(100) NOT NULL default '',
  `Sfirstname` varchar(100) NOT NULL default '',
  `Smidname` varchar(100) NOT NULL default '',
  `Slastname` varchar(100) NOT NULL default '',
  `Ssuffix` varchar(100) NOT NULL default '',
  `company` varchar(100) NOT NULL default '',
  `co` varchar(100) NOT NULL default '',
  `noAddress` varchar(10) NOT NULL default '',
  `nonStandard` varchar(10) NOT NULL default '',
  `addressline1` varchar(250) NOT NULL default '',
  `addressline2` varchar(250) NOT NULL default '',
  `country` varchar(100) NOT NULL default '',
  `city` varchar(100) NOT NULL default '',
  `state` varchar(100) NOT NULL default '',
  `zip` varchar(15) NOT NULL default '',
  `email` varchar(100) NOT NULL default '',
  `badAddress` varchar(10) NOT NULL default '',
  `homephone` varchar(15) NOT NULL default '',
  `workphone` varchar(15) NOT NULL default '',
  `foriegnphone` varchar(25) NOT NULL default '',
  `estDate` date NOT NULL default '0000-00-00',
  `classCode` varchar(10) NOT NULL default 'PRO',
  `initialMarketingCode` varchar(100) NOT NULL default '',
  `secondaryMarketingCode` varchar(100) NOT NULL default '',
  `comment1` text NOT NULL,
  `comment2` varchar(250) NOT NULL default '',
  `broker` varchar(25) NOT NULL default '',
  `STATUS` varchar(10) NOT NULL default '',
  `ib` varchar(100) NOT NULL default '',
  `balance` decimal(10,2) NOT NULL default '0.00',
  `wholesaler` tinyint(4) NOT NULL default '0',
  `brokerRecievedDate` date NOT NULL default '0000-00-00',
  `ibRecievedDate` date NOT NULL default '0000-00-00',
  `qualifiedFor` varchar(100) NOT NULL default '',
  `processCode` varchar(100) NOT NULL default '',
  `processDate` varchar(100) NOT NULL default '',
  `currentMarketingCode` varchar(100) NOT NULL default '',
  `date` date NOT NULL default '0000-00-00',
  `time` time NOT NULL default '00:00:00',
  `notes` varchar(255) NOT NULL default '',
  `lastseen` date NOT NULL default '0000-00-00',
  `lastedit` date NOT NULL default '0000-00-00',
  `lastmail` date NOT NULL default '0000-00-00',
  `lastpriority` date NOT NULL default '0000-00-00',
  `twentyfiveLetter` tinyint(4) NOT NULL default '0',
  `firstvisit` date NOT NULL default '0000-00-00',
  `lastemail` datetime NOT NULL default '0000-00-00 00:00:00',
  `workextnum` varchar(10) NOT NULL default '',
  `newnomore` tinyint(4) NOT NULL default '0',
  `birthdate` date NOT NULL default '0000-00-00',
  `Sbirthdate` date NOT NULL default '0000-00-00',
  `gstAcctNum` varchar(255) NOT NULL,
  `gstAcctNum2` varchar(50) NOT NULL,
  `iraTradeAuth` tinyint(4) NOT NULL,
  `setActive` date NOT NULL,
  `levyFlag` tinyint(4) NOT NULL,
  `beneficiary` varchar(150) NOT NULL,
  `beneficiaryCID` varchar(7) NOT NULL,
  `acvOnly` tinyint(4) NOT NULL,
  UNIQUE KEY `prospectNumber` (`prospectnumber`),
  KEY `lastname` (`lastname`),
  KEY `broker` (`broker`),
  KEY `wholesaler` (`wholesaler`),
  KEY `ib` (`ib`),
  KEY `firstname` (`firstname`),
  KEY `brokerRecievedDate` (`brokerRecievedDate`),
  KEY `classCode` (`classCode`),
  KEY `homephone` (`homephone`),
  KEY `workphone` (`workphone`),
  KEY `email` (`email`),
  KEY `date` (`date`),
  KEY `initialMarketingCode` (`initialMarketingCode`),
  KEY `secondaryMarketingCode` (`secondaryMarketingCode`),
  KEY `zip` (`zip`),
  KEY `foriegnphone` (`foriegnphone`),
  KEY `newnomore` (`newnomore`),
  KEY `lastseen` (`lastseen`),
  KEY `balance` (`balance`),
  KEY `addressline1` (`addressline1`),
  KEY `Slastname` (`Slastname`),
  KEY `Sfirstname` (`Sfirstname`),
  KEY `estDate` (`estDate`),
  KEY `lastmail` (`lastmail`),
  KEY `lastpriority` (`lastpriority`)
) ENGINE=MyISAM AUTO_INCREMENT=648893 DEFAULT CHARSET=latin1 PACK_KEYS=0
1 row in set (0.00 sec)

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

mysql> show table status like 'tbAccounts';
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| Name       | Engine | Version | Row_format | Rows   | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time          | Collation         | Checksum | Create_options | Comment |
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
| tbAccounts | MyISAM |      10 | Dynamic    | 546966 |            179 |    98336064 | 281474976710655 |    147621888 |         0 |         648893 | 2009-03-25 11:38:26 | 2009-05-04 07:53:30 | 2009-05-01 11:56:33 | latin1_swedish_ci |     NULL | pack_keys=0    |         | 
+------------+--------+---------+------------+--------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+----------------+---------+
1 row in set (0.00 sec)

Thank you for the feedback. The involved table was created with a previous (3.23,4.0,4.1) MySQL server version? and it is possible for you to provide a dump file with insert data with the enough size to get that behavior?. Thanks in advance.

Thanks for your reply.  Unfortunately, I would not be able to provide a dump of that table as it is a customer contacts table.  Like you said however, yes, this table has been up graded from earlier versions of mySQL.  Several times in fact.  That being the case, what would you think about this course of action?:

1. Create a new table with the same structure as "tbAccounts" called "tmp_table"

2. mysql%> insert into `tmp_table` select * from `tbAccounts`

3. mysql%> rename table `tbAccounts` `tbAccounts_old`

4. mysql%> rename table `tmp_table` `tbAccounts`

This would result in the application running off of the correct data in a table which was created by the current mysql version.

Would this even address the problem or I am looking in the wrong place?

Either way I will attempt the procedure tonight after hours and submit the results.

Per my last post I preformed the procedure discussed.  This resulted in the exact same results.  I was able to preform some tests that may provide some useful info.

I discovered that if I modify the query slightly, it does not crash the database.  This modifications are as such:

1. If I remove the "and (lastname like 'ki%' or Slastname like 'ki%')" and replace it with the simpler "and lastname like 'ki%'"

2. If I and add a character to the like compare string it does not crash (ie., change "and (lastname like 'ki%' or Slastname like 'ki%')" to "and (lastname like 'kir%' or Slastname like 'kir%')")

Hope this helps.

Thank you for the feedback. I did a small test populating on my own the table with 200k rows but against a current source server (so I need to test against a server 5.0.77) but before I would like to know the size of your table which you are able to repeat the issue. Thanks in advance.

About table size:
Rows     547327
Data 	 96,096 kb
Index 	145,990 kb
Total 	242,086 kb

Thank you for the feedback. Still I can't repeat even with more rows than your database, are you able to create a dump file with mysqldump client tool and then restore it in a fresh create table?. Thanks in advance.

Yes,

I completed that last night with no errors or crashes.

Thank you for the feedback.

> I completed that last night with no errors or crashes.

Do you still experience crashes when select or dump/reload solved the problem?

Thanks for the reply.

No, I never did fix the actual issue.   I had to go with the unfortunate workaround of limiting the queries in one of two ways depending on users input.  Either one of these modified queries returns with no crashes/errors:

1. Require the characters in the "... like 'nn%' ..." to be a minimum of 3 characters so "... like 'nn%' ..." is NOT allowed but "... like 'nnn%' ..." IS allowed.

OR

2. If the user submitted input (to the application UI) contains only two characters, the query will be modified to only use the like search on one field, so: 

"... (lastname like 'nn%' or Slastname like 'nn%') ..." 
becomes:
"... (lastname like 'ki%') ... "

This looks like because of upgrading an index got crashed.

Please repair indexes here. The easiest way is:

ALTER TABLE ... ENGINE=MyISAM;

This will copy your whole table once and will re-build indexes.

Another possibility of course is to drop the index and re-create it.

I have rebuilt the indexes and the problem is the same.  I will retest again after next upgrade.

I will set this to need feedback because we are waiting until you upgradet the next time.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".