![MySQL 查询立即失败](https://linux22.com/image/1362861/MySQL%20%E6%9F%A5%E8%AF%A2%E7%AB%8B%E5%8D%B3%E5%A4%B1%E8%B4%A5.png)
我最近将一个大型数据库(约 5GB)从 SQLdump 加载到本地 MySQL 服务器数据库中。尝试几次后加载成功,最初我可以毫无问题地进行查询,尽管查询耗时约 1.5 分钟(这不是理想的情况,但还不错)。
然而现在,查询在几秒钟后就消失了
ERROR 2013 (HY000): Lost connection to MySQL server during query
我甚至有一个
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
这非常奇怪,因为它在第一个小时左右运行正常(无论是通过mysql-workbench
还是通过标准命令行界面)。
我已经将max_allowed_packet
大小my.conf
增加到 64MB,但这没有影响。有什么想法吗?我在 Ubuntu 12.04(3.3.6-030306-通用内核)。
呃。所以这看起来像是一个错误,或者可能是某种硬件故障。error.log 显示大量十六进制,然后是
p c -> y;
InnoDB: End of page dump
130509 18:25:51 InnoDB: Page checksum 3880653435, prior-to-4.0.14-form checksum 3737975691
InnoDB: stored checksum 386314462, prior-to-4.0.14-form stored checksum 3737975691
InnoDB: Page lsn 4 759090297, low 4 bytes of lsn at page end 759090297
InnoDB: Page number (if stored to page already) 240815,
InnoDB: space id (if created with >= MySQL-4.1.1 and stored already) 0
InnoDB: Page may be an update undo log page
InnoDB: Page may be an index page where index id is 208
InnoDB: (index "PRIMARY" of table "chembl_15"."compound_structures")
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 240815.
InnoDB: You may have to recover from a backup.
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
InnoDB: If the corrupt page is an index page
InnoDB: you can also try to fix the corruption
InnoDB: by dumping, dropping, and reimporting
InnoDB: the corrupt table. You can use CHECK
InnoDB: TABLE to scan your table for corruption.
InnoDB: See also http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
InnoDB: Ending processing because of a corrupt database page.
130509 18:25:51
InnoDB: Assertion failure in thread 139737190622976 in file buf0buf.c line 3629
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.5/en/forcing-innodb-recovery.html
InnoDB: about forcing recovery.
23:25:51 UTC - 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=2
max_threads=151
thread_count=2
connection_count=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 346682 K bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
Thread pointer: 0x0
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 = 0 thread_stack 0x30000
/usr/sbin/mysqld(my_print_stacktrace+0x29)[0x7f172cfd87b9]
/usr/sbin/mysqld(handle_fatal_signal+0x483)[0x7f172ce9e8f3]
/lib/x86_64-linux-gnu/libpthread.so.0(+0xfcb0)[0x7f172bbebcb0]
/lib/x86_64-linux-gnu/libc.so.6(gsignal+0x35)[0x7f172b257425]
/lib/x86_64-linux-gnu/libc.so.6(abort+0x17b)[0x7f172b25ab8b]
/usr/sbin/mysqld(+0x5f2b81)[0x7f172d075b81]
/usr/sbin/mysqld(+0x620cc9)[0x7f172d0a3cc9]
/usr/sbin/mysqld(+0x5ad1c0)[0x7f172d0301c0]
/lib/x86_64-linux-gnu/libpthread.so.0(+0x7e9a)[0x7f172bbe3e9a]
/lib/x86_64-linux-gnu/libc.so.6(clone+0x6d)[0x7f172b314ccd]
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.
130509 18:25:51 [Note] Plugin 'FEDERATED' is disabled.
130509 18:25:51 InnoDB: The InnoDB memory heap is disabled
130509 18:25:51 InnoDB: Mutexes and rw_locks use GCC atomic builtins
130509 18:25:51 InnoDB: Compressed tables use zlib 1.2.3.4
130509 18:25:51 InnoDB: Initializing buffer pool, size = 128.0M
130509 18:25:52 InnoDB: Completed initialization of buffer pool
130509 18:25:52 InnoDB: highest supported file format is Barracuda.
InnoDB: The log sequence number in ibdata files does not match
InnoDB: the log sequence number in the ib_logfiles!
130509 18:25:52 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...
130509 18:25:52 InnoDB: Waiting for the background threads to start
130509 18:25:53 InnoDB: 5.5.31 started; log sequence number 21293128389
130509 18:25:53 [Note] Server hostname (bind-address): '127.0.0.1'; port: 3306
130509 18:25:53 [Note] - '127.0.0.1' resolves to '127.0.0.1';
130509 18:25:53 [Note] Server socket created on IP: '127.0.0.1'.
130509 18:25:53 [Note] Event Scheduler: Loaded 0 events
130509 18:25:53 [Note] /usr/sbin/mysqld: ready for connections.
Version: '5.5.31-0ubuntu0.12.04.1' socket: '/var/run/mysqld/mysqld.sock' port: 3306 (Ubuntu)
InnoDB: Database page corruption on disk or a failed
InnoDB: file read of page 240815.
InnoDB: You may have to recover from a backup.
130509 18:27:15 InnoDB: Page dump in ascii and hex (16384 bytes):
len 16384; hex
有什么想法吗 :-(
答案1
所以我不知道是什么原因造成的,但看起来
InnoDB: It is also possible that your operating
InnoDB: system has corrupted its own file cache
InnoDB: and rebooting your computer removes the
InnoDB: error.
完全正确 - 我重新启动了系统,现在可以再次查询而不会失败。我想知道这是否与以下事实有关:后加载数据库我安装了 MySQL-workbench,尽管此后它仍然能工作一个小时左右。
有一件事可能相关 - 我的操作系统运行在只有 20 GB 的 SSD 上,而 MySQL 数据文件夹运行在 HDD 上(~750 GB)。我想知道这种拆分是否会导致一些缓存问题(应该不会,但谁知道呢)。
更新
三天过去了,仍未出现任何损坏...
答案2
我遇到过类似的情况,在我的情况下,重新启动运行数据库的虚拟机可以解决问题,而重新启动 mysql 却没有。我的理解是,这表明存在 RAM 错误。
如果硬盘本身已经关闭并重新启动,就像重新启动虚拟机的主机环境一样,那么磁盘系统及其缓存中的硬件故障的可能性就会更大,并且
当我调试服务器时,我没有想到要询问 ECC Ram 的问题,现在我很后悔。不过,我们的托管服务提供商很乐意将 Ram 换成新的 DIMM。
如果服务器重启不能解决问题,那么很可能不是 RAM 错误。