mysql.sock损坏,在并发截断命令期间服务器崩溃后,MySQL INNODB损坏

我的服务器今天崩溃了,我认为是由于我们的一个INNODB表上的并发truncate table命令.服务器可以重新启动,但在启动后,每次我尝试发出SQL命令时,都会收到以下错误：

ERROR 2006 (HY000): MySQL server has gone away

这是日志中发生的事情：

121206 01:11:12 mysqld restarted

121206 1:11:13 InnoDB: Started; log sequence number 275 559321759

InnoDB: !!! innodb_force_recovery is set to 1 !!!

121206 1:11:13 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.95-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

InnoDB: Error: trying to load index PRIMARY for table

InnoDB: but the index tree has been freed!

121206 1:11:37 - 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=134217728

read_buffer_size=1048576

max_used_connections=1

max_connections=400

threads_connected=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 950272 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

thd=0x9900950

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...

Cannot determine thread, fp=0x46353fa0, backtrace may not be correct.

Stack range sanity check OK, backtrace follows:

(nil)

New value of fp=0x9900950 failed sanity check, terminating stack trace!

Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do

resolve it

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x993e500 =

thd->thread_id=1

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.

我在网上搜索过,我得到的提示是MySQL错误,但我不知道如何解决它.我使用的是MySQL 5.0.95版.

好像我必须创建一个新数据库并将旧数据转储到新数据库中,但如果我甚至不能向当前数据库发出任何SQL命令,我该怎么办？

—更新—

版本：’5.0.95-log’套接字：’/ var / lib / mysql / mysql.sock’端口：3306源码分配

InnoDB：错误：尝试为表加载索引PRIMARY

InnoDB：但索引树已被释放！

121206 4:13:41 – mysqld收到信号11;

这可能是因为你遇到了一个bug.这个二进制文件也有可能

或者它所链接的其中一个图书馆是腐败的,不正确的,

或配置错误.此错误也可能由硬件故障引起.

我们将尽力挖掘一些有助于诊断的信息

问题,但既然我们已经崩溃了,那肯定是错的

这可能会失败.

key_buffer_size=134217728

read_buffer_size=1048576

max_used_connections=1

max_connections=400

threads_connected=1

It is possible that mysqld could use up to

key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 950272 K

bytes of memory

Hope that's ok; if not, decrease some variables in the equation.

thd=0x17fb8950

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...

Cannot determine thread, fp=0x464a3fa0, backtrace may not be correct.

Stack range sanity check OK, backtrace follows:

(nil)

New value of fp=0x17fb8950 failed sanity check, terminating stack trace!

Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and follow instructions on how to resolve the stack trace. Resolved

stack trace is much more helpful in diagnosing the problem, so please do

resolve it

Trying to get some variables.

Some pointers may be invalid and cause the dump to abort...

thd->query at 0x17ff6500 =

thd->thread_id=3

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.

Number of processes running now: 0

121206 04:13:41 mysqld restarted

InnoDB: The log sequence number in ibdata files does not match

InnoDB: the log sequence number in the ib_logfiles!

121206 4:13:42 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...

121206 4:13:43 InnoDB: Started; log sequence number 275 559323148

121206 4:13:43 [Note] /usr/libexec/mysqld: ready for connections.

Version: '5.0.95-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 Source distribution

解决方法:

ASPECT#1

引起我注意的第一件事是这条线

InnoDB: Error: trying to load index PRIMARY for table /

这表明您有一个使用InnoDB存储引擎的表

InnoDB的有趣之处在于存储PRIMARY KEY的方式.它存储在称为gen_clust_index的结构中,或者通常称为聚集索引.

我的直接猜测是某个PRIMARY KEY条目太大了

请考虑一些关于使用长PRIMARY KEY的好,坏和丑的文章：

然后查看< DB Hidden>.< Table Hidden>需要重新设计.

ASPECT#2

关于平行截断表的猜想,这听起来有点危险.为什么？ InnoDB执行TRUNCATE TABLE作为DDL而不是DML.我以前写过这个：

ASPECT#3

一些调整建议

请将以下内容添加到my.ini中

[mysqld]

max_allowed_packet=1G

innodb_fast_shutdown=0

启动mysql

在另一个会话中,运行tail -f< errorlogfile>并观看InnoDB Crash Recovery.

如果mysql完全启动备份并且InnoDB崩溃恢复已经完成,请尝试立即关闭mysql.您可能需要调整InnoDB事务日志的大小.

对不起这些疯狂的建议,但我在这里失明.

请在问题中发布以下内容：

>你的整个my.cnf

>船上有多少RAM

更新2012-12-05 12:09 EDT

请执行以下操作：

步骤01)将这些更改添加到my.cnf

[mysqld]

max_allowed_packet=1G

innodb_fast_shutdown=0

innodb_thread_concurrency=0

步骤02)服务mysql重启

确保mysql出现

步骤03)您需要调整ib_logfile0和ib_logfile1的大小(24M可能太小)

service mysql stop

cd /var/lib/mysql

mv ib_logfile0 ib_logfile0.bak

mv ib_logfile1 ib_logfile1.bak

步骤04)将这些更改添加到my.cnf

[mysqld]

innodb_log_file_size=512M

innodb_log_buffer_size=8M

STEP 05)服务mysql启动

mysqld将重新创建ib_logfile0和ib_logfile1 512M

现在,试着看看会发生什么……

更新2012-12-05 12:18美国东部时间

更新2012-12-05 14:28美国东部时间

我从这个问题中编辑了对客户表的所有引用.

根本原因是ibdata1中的页面损坏,数据和索引页面混合在一起.我帮助安德鲁迁移数据,用innodb_file_per_table重新创建ibdata1,安德鲁重新加载数据.

标签：corruption,mysql,crash,innodb,index

来源： https://codeday.me/bug/20190805/1589646.html