开发告知mysq测试环境l宕机,查看错误日志,发现如下错误:
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=402653184
read_buffer_size=2097152
max_used_connections=3001
max_threads=2000
threads_connected=1341
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8615684 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x2aad61449720
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 = 0x5b323f20 thread_stack 0x40000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1e06cf80 = SELECT COUNT(*) as total FROM (select count(*) as total from ad_log_cpt_show where ad_info_id=1907122 and show_time>=2013-03-18 and show_time<=2013-03-18 GROUP BY TO_DAYS(FROM_UNIXTIME(show_time, '%Y-%m-%d'))) tmp
thd->thread_id=186915794
thd->killed=NOT_KILLED
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.
130605 16:43:29 mysqld_safe mysqld from pid file /data/my2/mysql/db25522.pid ended
这个bug 应该是在4.0或5.0的版本,而我的数据库版本5.4,应该不错出现上述bug
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.4.1-beta-community |
+----------------------+
1 row in set (0.00 sec)
目前确认是两个方面: 一有可能参数设置问题 二 sql语句导致
SELECT COUNT(*) as total FROM (select count(*) as total from ad_log_cpt_show where ad_info_id=1907122 and show_time>=2013-03-18 and show_time<=2013-03-18 GROUP BY TO_DAYS(FROM_UNIXTIME(show_time, '%Y-%m-%d'))) tmp;
这个sql语句写的很不规范,看一下表结构便知 show_time 字段类型
mysql> desc ad_log_cpt_show;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ad_info_id | int(11) | NO | MUL | 0 | |
| show_time | int(11) | NO | MUL | 0 | |
| permanent_id | varchar(255) | NO | | | |
+--------------+--------------+------+-----+---------+-------+
这个问题持续关注一段时间,看mysql是否还会自动崩溃。
有类似问题的朋友,欢迎交流。
参考资料
http://bugs.mysql.com/bug.php?id=3003
http://dev.mysql.com/doc/mysql/en/crashing.html contains
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=402653184
read_buffer_size=2097152
max_used_connections=3001
max_threads=2000
threads_connected=1341
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 8615684 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x2aad61449720
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 = 0x5b323f20 thread_stack 0x40000
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1e06cf80 = SELECT COUNT(*) as total FROM (select count(*) as total from ad_log_cpt_show where ad_info_id=1907122 and show_time>=2013-03-18 and show_time<=2013-03-18 GROUP BY TO_DAYS(FROM_UNIXTIME(show_time, '%Y-%m-%d'))) tmp
thd->thread_id=186915794
thd->killed=NOT_KILLED
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.
130605 16:43:29 mysqld_safe mysqld from pid file /data/my2/mysql/db25522.pid ended
这个bug 应该是在4.0或5.0的版本,而我的数据库版本5.4,应该不错出现上述bug
mysql> select version();
+----------------------+
| version() |
+----------------------+
| 5.4.1-beta-community |
+----------------------+
1 row in set (0.00 sec)
目前确认是两个方面: 一有可能参数设置问题 二 sql语句导致
SELECT COUNT(*) as total FROM (select count(*) as total from ad_log_cpt_show where ad_info_id=1907122 and show_time>=2013-03-18 and show_time<=2013-03-18 GROUP BY TO_DAYS(FROM_UNIXTIME(show_time, '%Y-%m-%d'))) tmp;
这个sql语句写的很不规范,看一下表结构便知 show_time 字段类型
mysql> desc ad_log_cpt_show;
+--------------+--------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+--------------+------+-----+---------+-------+
| ad_info_id | int(11) | NO | MUL | 0 | |
| show_time | int(11) | NO | MUL | 0 | |
| permanent_id | varchar(255) | NO | | | |
+--------------+--------------+------+-----+---------+-------+
这个问题持续关注一段时间,看mysql是否还会自动崩溃。
有类似问题的朋友,欢迎交流。
参考资料
http://bugs.mysql.com/bug.php?id=3003
http://dev.mysql.com/doc/mysql/en/crashing.html contains