Mysql crashes by some strange queries

Question

I have some strange log entries in my debug where I see that the mysql crashes:

Apr 13 08:51:16 cronjob1 mysqld[22953]: Attempting backtrace. You can use the following information to find out
Apr 13 08:51:16 cronjob1 mysqld[22953]: where mysqld died. If you see no messages after this, something went
Apr 13 08:51:16 cronjob1 mysqld[22953]: terribly wrong...
Apr 13 08:51:16 cronjob1 mysqld[22953]: Cannot determine thread, fp=0x8543090, backtrace may not be correct.
Apr 13 08:51:16 cronjob1 mysqld[22953]: Bogus stack limit or frame pointer, fp=0x8543090, stack_bottom=0x44b70000, thread_stack=262144, aborting backtrace.
Apr 13 08:51:16 cronjob1 mysqld[22953]: Trying to get some variables.
Apr 13 08:51:16 cronjob1 mysqld[22953]: Some pointers may be invalid and cause the dump to abort...
Apr 13 08:51:16 cronjob1 mysqld[22953]: thd->query at 0x80ea1c0 = (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "kierownik działu" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "kierownik jakości" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "kierownik łódzkie" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "działu jakości" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "działu łódzkie" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, name, count_character  
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 FROM base.cities 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 WHERE country_id = 176 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011#011AND name = "jakości łódzkie" 
Apr 13 08:51:16 cronjob1 mysqld[22953]: #011#011#011#011#011 LIMIT 1) UNION ALL (SELECT city_id, na
Apr 13 08:51:16 cronjob1 mysqld[22953]: thd->thread_id=20686
Apr 13 08:51:16 cronjob1 mysqld[22953]: The manual page at http://www.mysql.com/doc/en/Crashing.html contains
Apr 13 08:51:16 cronjob1 mysqld[22953]: information that should help you find out what is causing the crash.
Apr 13 08:51:16 cronjob1 mysqld_safe[31297]: Number of processes running now: 0
Apr 13 08:51:16 cronjob1 mysqld_safe[31299]: restarted

The problem here is I don't know where this one #011#011#011#011#011# is coming from. I am using mysql_real_escape_string() for the query.

Have you tried Export + Drop + Import table? That should erase any footprint of this table in DB. — Shamit Verma, Mar 28 '11 at 05:50
_Why_ does MySQL not perform to your standards? Which specific query is giving you trouble? Can you optimize the query? Or execute less complicated queries? — sarnold, Mar 28 '11 at 05:53
@Shamit - yes I have done it. I have drop the whole DB as well. — , Mar 28 '11 at 07:37
@sarnold before I did the kill everything was fine. There are now the same quesries. — , Mar 28 '11 at 07:37
did you try using a "clean" kill by sending SIGTERM i.e `kill -15`? — , Mar 28 '11 at 09:21

score 1 · Answer 1 · edited Apr 16 '11 at 20:00

@Ignacio says stop using kill -9. Here is why:

MyISAM tables maintain the count of open file handles to the MyISAM table. If mysqld just up and dies without a shutdown process, that count stays as is. To remove its footprint you can do one of two things:

For example, if the MyISAM table is mydb.mytable

Option 1

While mysqld is shutdown,

cd /var/lib/mysql/mydb
myisamchk -r mytable.MYD

myisamchk documentation

Option 2

With mysqld running, do

CHECK TABLE tblname;
REPAIR TABLE tblname;

score 0 · Answer 2 · answered Apr 20 '11 at 21:56

This seems to be an encoding problem. There may be a symbol in the query string that cannot get interpreted by the connections charset. Try using a distinct charset for the connection or set a proper default in mysql.conf (my.conf). Print or log the query string before you fire it, to get an idea what the string after the mysql_real_escape_string() function looks like.

Mysql crashes by some strange queries

2 Answers2