Mysql table crashes after kill -9

Question

After MySQL lags, I must kill it with -9.

Since I have killed the MySQL process, there is one static MyISAM table with 40k rows that has problems. Every time when I want to optimize it with OPTIMIZE table, MySQL restarts and crashes other tables too. I have repaired the table many times, dropped the whole DB and loaded it again but nothing helps.

Before, when I have kill the MySQL process from the console I had never this problem. How can I fix this?

Update

RolandoMySQLDBA, thanks for te good advices. I have tried both, but I think my problem is anywhere else. In my log files I have this log:

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 from where is coming this one "#011#011#011#011#011#" . For the query I am using mysql_real_escape_string().

How can I solve the problem?

Answer 1

@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;