![mysqld 在任何语句上崩溃](https://linux22.com/image/532748/mysqld%20%E5%9C%A8%E4%BB%BB%E4%BD%95%E8%AF%AD%E5%8F%A5%E4%B8%8A%E5%B4%A9%E6%BA%83.png)
我重新启动了从属服务器以更改配置设置,以跳过连接时的反向主机名查找并启用慢查询日志。
我编辑了 /etc/my.cnf,只做了这些更改,然后使用 /etc/init.d/mysql restart 重新启动了 mysqld
一切似乎都很好,但是当我远程或本地连接到 msyqld 时,虽然连接正常,但有一个小问题是,每当您尝试发出任何类型的语句时,mysqld 都会崩溃。
客户端如下所示:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.1.31-1ubuntu2-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> show tables;
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 1
Current database: mydb
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR:
Can't connect to the server
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
ERROR 2003 (HY000): Can't connect to MySQL server on 'xx.xx.xx.xx' (61)
ERROR:
Can't connect to the server
ERROR 2006 (HY000): MySQL server has gone away
Bus error
mysqld 错误日志如下所示:
101210 16:35:51 InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:35:51 InnoDB: Assertion failure in thread 140245598570832 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:35:51 - mysqld got signal 6 ;
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=16777216
read_buffer_size=131072
max_used_connections=3
max_threads=600
threads_connected=3
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x18209220
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 = 0x7f8d791580d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f902a76a080]
/lib/libc.so.6(gsignal+0x35) [0x7f90291f8fb5]
/lib/libc.so.6(abort+0x183) [0x7f90291fabc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f902a7623ba]
/lib/libc.so.6(clone+0x6d) [0x7f90292abfcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18213c70 =
thd->thread_id=3
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.
101210 16:35:51 mysqld_safe Number of processes running now: 0
101210 16:35:51 mysqld_safe mysqld restarted
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
101210 16:35:54 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
101210 16:35:56 InnoDB: Started; log sequence number 456 143528628
101210 16:35:56 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 16:35:56 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 16:35:56 [Note] Event Scheduler: Loaded 0 events
101210 16:35:56 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
101210 16:36:11 InnoDB: Error: (1500) Couldn't read the MAX(job_id) autoinc value from the index (PRIMARY).
101210 16:36:11 InnoDB: Assertion failure in thread 139955151501648 in file handler/ha_innodb.cc line 2595
InnoDB: Failing assertion: error == DB_SUCCESS
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
101210 16:36:11 - mysqld got signal 6 ;
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=16777216
read_buffer_size=131072
max_used_connections=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x18588720
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 = 0x7f49d916f0d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f4c8a73f080]
/lib/libc.so.6(gsignal+0x35) [0x7f4c891cdfb5]
/lib/libc.so.6(abort+0x183) [0x7f4c891cfbc3]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x41b) [0x781f4b]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f4c8a7373ba]
/lib/libc.so.6(clone+0x6d) [0x7f4c89280fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x18599950 =
thd->thread_id=1
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.
101210 16:36:11 mysqld_safe Number of processes running now: 0
101210 16:36:11 mysqld_safe mysqld restarted
配置是
[mysqld_safe]
socket = /var/run/mysqld/mysqld.sock
nice = 0
[mysqld]
innodb_file_per_table
innodb_buffer_pool_size=10G
innodb_log_buffer_size=4M
innodb_flush_log_at_trx_commit=2
innodb_thread_concurrency=8
skip-slave-start
server-id=3
#
# * IMPORTANT
# If you make changes to these settings and your system uses apparmor, you may
# also need to also adjust /etc/apparmor.d/usr.sbin.mysqld.
#
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /DB2/mysql
tmpdir = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
#
# * Fine Tuning
#
key_buffer = 16M
max_allowed_packet = 16M
thread_stack = 128K
thread_cache_size = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover = BACKUP
max_connections = 600
#table_cache = 64
#thread_concurrency = 10
#
# * Query Cache Configuration
#
query_cache_limit = 1M
query_cache_size = 32M
#
skip-federated
slow-query-log
skip-name-resolve
更新:我按照说明进行操作http://dev.mysql.com/doc/refman/5.1/en/forcing-innodb-recovery.html并设置 innodb_force_recovery = 4 并且日志显示不同的错误但行为仍然相同:
101210 19:14:15 mysqld_safe mysqld restarted
101210 19:14:19 InnoDB: Started; log sequence number 456 143528628
InnoDB: !!! innodb_force_recovery is set to 4 !!!
101210 19:14:19 [Warning] 'user' entry 'root@PSDB102' ignored in --skip-name-resolve mode.
101210 19:14:19 [Warning] Neither --relay-log nor --relay-log-index were used; so replication may break when this MySQL server acts as a slave and has his hostname changed!! Please use '--relay-log=mysqld-relay-bin' to avoid this problem.
101210 19:14:19 [Note] Event Scheduler: Loaded 0 events
101210 19:14:19 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.1.31-1ubuntu2-log' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
101210 19:14:32 InnoDB: error: space object of table mydb/__twitter_friend,
InnoDB: space id 1602 did not exist in memory. Retrying an open.
101210 19:14:32 InnoDB: error: space object of table mydb/access_request,
InnoDB: space id 1318 did not exist in memory. Retrying an open.
101210 19:14:32 InnoDB: error: space object of table mydb/activity,
InnoDB: space id 1595 did not exist in memory. Retrying an open.
101210 19:14:32 - 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=16777216
read_buffer_size=131072
max_used_connections=1
max_threads=600
threads_connected=1
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1328077 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x1753c070
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 = 0x7f7a0b5800d0 thread_stack 0x20000
/usr/sbin/mysqld(my_print_stacktrace+0x29) [0x8b4f89]
/usr/sbin/mysqld(handle_segfault+0x383) [0x5f8f03]
/lib/libpthread.so.0 [0x7f7cbc350080]
/usr/sbin/mysqld(ha_innobase::innobase_get_index(unsigned int)+0x46) [0x77c516]
/usr/sbin/mysqld(ha_innobase::innobase_initialize_autoinc()+0x40) [0x77c640]
/usr/sbin/mysqld(ha_innobase::open(char const*, int, unsigned int)+0x3f3) [0x781f23]
/usr/sbin/mysqld(handler::ha_open(st_table*, char const*, int, int)+0x3f) [0x6db00f]
/usr/sbin/mysqld(open_table_from_share(THD*, st_table_share*, char const*, unsigned int, unsigned int, unsigned int, st_table*, bool)+0x57a) [0x64760a]
/usr/sbin/mysqld [0x63f281]
/usr/sbin/mysqld(open_table(THD*, TABLE_LIST*, st_mem_root*, bool*, unsigned int)+0x626) [0x641e16]
/usr/sbin/mysqld(open_tables(THD*, TABLE_LIST**, unsigned int*, unsigned int)+0x5db) [0x6429cb]
/usr/sbin/mysqld(open_normal_and_derived_tables(THD*, TABLE_LIST*, unsigned int)+0x1e) [0x642b0e]
/usr/sbin/mysqld(mysqld_list_fields(THD*, TABLE_LIST*, char const*)+0x22) [0x70b292]
/usr/sbin/mysqld(dispatch_command(enum_server_command, THD*, char*, unsigned int)+0x146d) [0x60dc1d]
/usr/sbin/mysqld(do_command(THD*)+0xe8) [0x60dda8]
/usr/sbin/mysqld(handle_one_connection+0x226) [0x601426]
/lib/libpthread.so.0 [0x7f7cbc3483ba]
/lib/libc.so.6(clone+0x6d) [0x7f7cbae91fcd]
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x1754d690 =
thd->thread_id=1
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.
答案1
看这里:http://bugs.mysql.com/bug.php?id=44030
菲利普
我也遇到了同样的问题。我尝试从 5.1.33 升级到 5.1.36,但每次访问数据库时 mysqld 仍然会崩溃。
一些细节,其中大部分可能无关紧要,但也许有些东西会跳出来:这是一台全新的笔记本电脑,我刚刚开始一个新项目。MD Turion X2 双核移动 RM-74,带 4GB RAM 磁盘上的数据库大小为 1.67GB 1GB RAM 被用作 ramdisk 使用 WAMP 项目安装程序在 MS Vista 64 上全新安装 5.1.33。很多 ALTER,因为这是一个开发环境。添加列、索引等。也重命名列。该错误是在编码会话之间出现的,而不是在编码会话期间。所有数据库模式都是使用 MySQL Administrator 创建的,所有数据操作都是使用 php 和 mysqli 完成的。所有与 MySQL 相关的都是全新的。抱歉,我无法提供数据文件。
无论如何,由于升级无法让我访问数据,而且自上次备份以来发生了很多工作,我想恢复/检索尽可能多的数据库模式,我并不关心数据。我实际上也检索了大部分数据。虽然我确信这个过程可以优化,但对我有用的是:C:\wamp\bin\mysql\mysql5.1.36\bin>mysqlshow.exe -u root 然后对每个表 C:\wamp\bin\mysql\mysql5.1.36\bin>mysqlshow.exe -u root 直到我收到错误 mysqlshow.exe:无法列出数据库中的列:,表::查询期间与 MySQL 服务器的连接丢失然后我重新启动了 mysqld,启动了 MySQL 控制台,并对导致崩溃的表执行 \u drop table。之后,除了丢失一张我可以轻松重新创建的小表之外,一切都恢复正常。
答案2
如果您对 mysqladmin 变量 'wait_timeout' 的首选值较低,则可能会出现此问题 | grep wait_timeout