☰

Mysql 因一些奇怪的查询而崩溃

tag-icon • tag-icon mysql query
Mysql 因一些奇怪的查询而崩溃

我在调试中发现一些奇怪的日志条目，其中我发现 mysql 崩溃了：

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

这里的问题是我不知道这个#011#011#011#011#011#是从哪里来的。我正在使用 mysql_real_escape_string() 进行查询。

答案1

@Ignacio 说停止使用 kill -9。原因如下：

MyISAM 表维护 MyISAM 表的打开文件句柄数。如果 mysqld 刚启动并死机而没有关闭进程，则该计数保持不变。要删除其占用空间，您可以执行以下两项操作之一：

例如，如果 MyISAM 表是 mydb.mytable

选项1

当 mysqld 关闭时，

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

myisamchk文档

选项 2

在 mysqld 运行时，执行

CHECK TABLE tblname;
REPAIR TABLE tblname;

答案2

这似乎是一个编码问题。查询字符串中可能存在无法由连接字符集解释的符号。尝试为连接使用不同的字符集或在 mysql.conf (my.conf) 中设置适当的默认值。在触发查询字符串之前打印或记录查询字符串，以了解 mysql_real_escape_string() 函数后的字符串是什么样子的。

相关内容