mysql get error 28_MySQL Got error 134错误排查

最新推荐文章于 2023-11-21 10:31:55 发布

亭中意

最新推荐文章于 2023-11-21 10:31:55 发布

阅读量514

点赞数

文章标签： mysql get error 28

本文链接：https://blog.csdn.net/weixin_36276175/article/details/113428257

版权

现象

Discuz论坛一张表反复崩溃，导致帖子页无法打开，日志如下：

150729 23:24:17 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './bbs/forum_threadaddviews.MYI'; try to repair it

150729 23:24:18 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './bbs/forum_threadaddviews.MYI'; try to repair it

150729 23:24:24 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './bbs/forum_threadaddviews.MYI'; try to repair it

150729 23:24:33 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150729 23:24:36 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './bbs/forum_threadaddviews.MYI'; try to repair it

...

150729 23:25:51 [ERROR] /usr/local/mysql/libexec/mysqld: Table './bbs/forum_threadaddviews' is marked as crashed and should be repaired

150729 23:25:53 [ERROR] /usr/local/mysql/libexec/mysqld: Table './bbs/forum_threadaddviews' is marked as crashed and should be repaired

150729 23:25:55 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150729 23:25:55 [ERROR] /usr/local/mysql/libexec/mysqld: Table './bbs/forum_threadaddviews' is marked as crashed and should be repaired

150729 23:25:56 [ERROR] /usr/local/mysql/libexec/mysqld: Table './bbs/forum_threadaddviews' is marked as crashed and should be repaired

15072923:24:17[ERROR]/usr/local/mysql/libexec/mysqld:Incorrectkeyfilefortable'./bbs/forum_threadaddviews.MYI';trytorepairit

15072923:24:18[ERROR]/usr/local/mysql/libexec/mysqld:Incorrectkeyfilefortable'./bbs/forum_threadaddviews.MYI';trytorepairit

15072923:24:24[ERROR]/usr/local/mysql/libexec/mysqld:Incorrectkeyfilefortable'./bbs/forum_threadaddviews.MYI';trytorepairit

15072923:24:33[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

15072923:24:36[ERROR]/usr/local/mysql/libexec/mysqld:Incorrectkeyfilefortable'./bbs/forum_threadaddviews.MYI';trytorepairit

...

15072923:25:51[ERROR]/usr/local/mysql/libexec/mysqld:Table'./bbs/forum_threadaddviews'ismarkedascrashedandshouldberepaired

15072923:25:53[ERROR]/usr/local/mysql/libexec/mysqld:Table'./bbs/forum_threadaddviews'ismarkedascrashedandshouldberepaired

15072923:25:55[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

15072923:25:55[ERROR]/usr/local/mysql/libexec/mysqld:Table'./bbs/forum_threadaddviews'ismarkedascrashedandshouldberepaired

15072923:25:56[ERROR]/usr/local/mysql/libexec/mysqld:Table'./bbs/forum_threadaddviews'ismarkedascrashedandshouldberepaired

表forum_threadaddviews修复后，mysql日志仍然在刷ERROR错误：

[root@localhost log]# tail -f mysql.err

150730 0:03:17 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150730 0:05:29 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150730 0:05:45 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150730 0:06:34 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

150730 0:06:49 [ERROR] Got error 134 when reading table './bbs/forum_forumrecommend'

[root@localhostlog]# tail -f mysql.err

1507300:03:17[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

1507300:05:29[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

1507300:05:45[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

1507300:06:34[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

1507300:06:49[ERROR]Goterror134whenreadingtable'./bbs/forum_forumrecommend'

追查

通过MySQL错误日志，可以看到Got error 134在13年8月和15年7月集中爆发，符合论坛近期的表现

[root@localhost var]# grep "Got error 134" mysql.err |awk '{print $1}' |sort |uniq -c

3450 130112

213 130801

325 130802

266 130803

214 130804

216 130805

212 130806

271 130807

178 130808

170 130809

188 130810

194 130811

120 130812

136 130813

207 130814

185 130815

160 130816

181 130817

137 130818

1 150706

749 150713

1333 150714

1551 150715

1569 150716

1569 150717

1522 150718

1557 150719

1534 150720

1508 150721

1430 150722

1406 150723

1400 150724

1414 150725

1469 150726

1343 150727

1360 150728

1529 150729

44 150730

[root@localhostvar]# grep "Got error 134" mysql.err |awk '{print $1}' |sort |uniq -c

3450130112

213130801

325130802

266130803

214130804

216130805

212130806

271130807

178130808

170130809

188130810

194130811

120130812

136130813

207130814

185130815

160130816

181130817

137130818

1150706

749150713

1333150714

1551150715

1569150716

1569150717

1522150718

1557150719

1534150720

1508150721

1430150722

1406150723

1400150724

1414150725

1469150726

1343150727

1360150728

1529150729

44150730

Got error 134的表，几乎都是 forum_forumrecommend

[root@localhost var]# grep "Got error 134" mysql.err | grep "^1507" |awk '{print $NF}' |sort |uniq -c

24287 './bbs/forum_forumrecommend'

1 './bbs/forum_threadaddviews'

[root@localhostvar]# grep "Got error 134" mysql.err | grep "^1507" |awk '{print $NF}' |sort |uniq -c

24287'./bbs/forum_forumrecommend'

1'./bbs/forum_threadaddviews'

forum_threadaddviews表情况，也差不多是13年8月和15年7月比较集中

[root@localhost var]# grep "forum_threadaddviews" mysql.err |awk '{print $1}' |sort |uniq -c

8312 121222

4857 130101

4874 130112

12810 130126

264 130128

4260 130129

2873 130219

5507 130222

19385 130223

6753 130224

213 130801

325 130802

266 130803

214 130804

216 130805

212 130806

271 130807

178 130808

170 130809

188 130810

194 130811

120 130812

136 130813

207 130814

185 130815

160 130816

181 130817

137 130818

34149 150311

16083 150312

3944 150415

5343 150515

1491 150516

21939 150519

557 150625

26084 150626

22901 150629

36339 150706

84450 150713

54706 150714

43947 150715

1086 150717

42033 150718

52414 150719

77772 150720

70164 150721

73334 150723

41844 150724

40190 150725

65812 150726

27799 150727

39230 150728

23982 150729

[root@localhost var]#

[root@localhostvar]# grep "forum_threadaddviews" mysql.err |awk '{print $1}' |sort |uniq -c

8312121222

4857130101

4874130112

12810130126

264130128

4260130129

2873130219

5507130222

19385130223

6753130224

213130801

325130802

266130803

214130804

216130805

212130806

271130807

178130808

170130809

188130810

194130811

120130812

136130813

207130814

185130815

160130816

181130817

137130818

34149150311

16083150312

3944150415

5343150515

1491150516

21939150519

557150625

26084150626

22901150629

36339150706

84450150713

54706150714

43947150715

1086150717

42033150718

52414150719

77772150720

70164150721

73334150723

41844150724

40190150725

65812150726

27799150727

39230150728

23982150729

[root@localhostvar]#

Got error 134

Even though the MyISAM table format is very reliable (all changes to a table made by an SQL statement are written before the statement returns), you can still get corrupted tables if any of the following events occur:

The mysqld process is killed in the middle of a write.

An unexpected computer shutdown occurs (for example, the computer is turned off).

Hardware failures.

You are using an external program (such as myisamchk ) to modify a table that is being modified by the server at the same time.

A software bug in the MySQL or MyISAM code.

Typical symptoms of a corrupt table are: You get the following error while selecting data from the table:

Incorrect key file for table: '...'. Try to repair it

Queries don't find rows in the table or return incomplete results.

来自：http://dev.mysql.com/doc/refman/5.0/en/corrupted-myisam-tables.html

解决：

CHECK TABLE 表名

REPAIR TABLE 表名

CHECKTABLE表名

REPAIRTABLE表名

修复

检查forum_forumrecommend，可以看到确实有错误

mysql> check table forum_forumrecommend;

+-----------------------------+-------+----------+-----------------------------------------------------------+

+-----------------------------+-------+----------+-----------------------------------------------------------+

+-----------------------------+-------+----------+-----------------------------------------------------------+

mysql>checktableforum_forumrecommend;

+-----------------------------+-------+----------+-----------------------------------------------------------+

+-----------------------------+-------+----------+-----------------------------------------------------------+

+-----------------------------+-------+----------+-----------------------------------------------------------+

修复forum_forumrecommend

mysql> repair table forum_forumrecommend;

+-----------------------------+--------+----------+----------------------------------------+

+-----------------------------+--------+----------+----------------------------------------+

+-----------------------------+--------+----------+----------------------------------------+

2 rows in set (0.01 sec)

mysql> check table forum_forumrecommend;

+-----------------------------+-------+----------+----------+

+-----------------------------+-------+----------+----------+

+-----------------------------+-------+----------+----------+

1 row in set (0.00 sec)

mysql>repairtableforum_forumrecommend;

+-----------------------------+--------+----------+----------------------------------------+

+-----------------------------+--------+----------+----------------------------------------+

+-----------------------------+--------+----------+----------------------------------------+

2rowsinset(0.01sec)

mysql>checktableforum_forumrecommend;

+-----------------------------+-------+----------+----------+

+-----------------------------+-------+----------+----------+

+-----------------------------+-------+----------+----------+

1rowinset(0.00sec)

再次追查

看message，时间与上述问题比较吻合，因此可能是系统oom killer杀掉mysqld进程导致上述问题。

[root@localhost log]# grep -i "out of mem" messages* |grep mysqld

messages-20150705:Jun 29 04:21:21 localhost kernel: Out of memory: Kill process 14957 (mysqld) score 118 or sacrifice child

messages-20150705:Jun 29 04:29:02 localhost kernel: Out of memory: Kill process 4169 (mysqld) score 35 or sacrifice child

messages-20150705:Jun 29 21:17:52 localhost kernel: Out of memory: Kill process 5172 (mysqld) score 78 or sacrifice child

messages-20150705:Jun 29 21:21:36 localhost kernel: Out of memory: Kill process 14353 (mysqld) score 32 or sacrifice child

messages-20150705:Jun 29 21:21:39 localhost kernel: Out of memory: Kill process 14567 (mysqld) score 32 or sacrifice child

messages-20150705:Jun 30 01:44:37 localhost kernel: Out of memory: Kill process 15232 (mysqld) score 74 or sacrifice child

messages-20150705:Jul 1 00:11:28 localhost kernel: Out of memory: Kill process 31564 (mysqld) score 90 or sacrifice child

messages-20150705:Jul 1 17:32:53 localhost kernel: Out of memory: Kill process 9870 (mysqld) score 86 or sacrifice child

messages-20150712:Jul 7 01:23:53 localhost kernel: Out of memory: Kill process 18756 (mysqld) score 91 or sacrifice child

messages-20150712:Jul 7 01:23:54 localhost kernel: Out of memory: Kill process 3854 (mysqld) score 91 or sacrifice child

messages-20150712:Jul 11 01:06:10 localhost kernel: Out of memory: Kill process 4237 (mysqld) score 110 or sacrifice child

messages-20150712:Jul 11 01:06:10 localhost kernel: Out of memory: Kill process 16005 (mysqld) score 110 or sacrifice child

messages-20150719:Jul 13 10:19:33 localhost kernel: Out of memory: Kill process 16306 (mysqld) score 121 or sacrifice child

messages-20150726:Jul 22 21:58:46 localhost kernel: Out of memory: Kill process 29585 (mysqld) score 107 or sacrifice child

messages-20150726:Jul 22 22:12:49 localhost kernel: Out of memory: Kill process 10021 (mysqld) score 50 or sacrifice child

messages-20150726:Jul 24 16:51:24 localhost kernel: Out of memory: Kill process 12025 (mysqld) score 95 or sacrifice child

messages-20150726:Jul 24 16:51:24 localhost kernel: Out of memory: Kill process 21597 (mysqld) score 95 or sacrifice child

[root@localhostlog]# grep -i "out of mem" messages* |grep mysqld

messages-20150705:Jun2904:21:21localhostkernel:Outofmemory:Killprocess14957(mysqld)score118orsacrificechild

messages-20150705:Jun2904:29:02localhostkernel:Outofmemory:Killprocess4169(mysqld)score35orsacrificechild

messages-20150705:Jun2921:17:52localhostkernel:Outofmemory:Killprocess5172(mysqld)score78orsacrificechild

messages-20150705:Jun2921:21:36localhostkernel:Outofmemory:Killprocess14353(mysqld)score32orsacrificechild

messages-20150705:Jun2921:21:39localhostkernel:Outofmemory:Killprocess14567(mysqld)score32orsacrificechild

messages-20150705:Jun3001:44:37localhostkernel:Outofmemory:Killprocess15232(mysqld)score74orsacrificechild

messages-20150705:Jul100:11:28localhostkernel:Outofmemory:Killprocess31564(mysqld)score90orsacrificechild

messages-20150705:Jul117:32:53localhostkernel:Outofmemory:Killprocess9870(mysqld)score86orsacrificechild

messages-20150712:Jul701:23:53localhostkernel:Outofmemory:Killprocess18756(mysqld)score91orsacrificechild

messages-20150712:Jul701:23:54localhostkernel:Outofmemory:Killprocess3854(mysqld)score91orsacrificechild

messages-20150712:Jul1101:06:10localhostkernel:Outofmemory:Killprocess4237(mysqld)score110orsacrificechild

messages-20150712:Jul1101:06:10localhostkernel:Outofmemory:Killprocess16005(mysqld)score110orsacrificechild

messages-20150719:Jul1310:19:33localhostkernel:Outofmemory:Killprocess16306(mysqld)score121orsacrificechild

messages-20150726:Jul2221:58:46localhostkernel:Outofmemory:Killprocess29585(mysqld)score107orsacrificechild

messages-20150726:Jul2222:12:49localhostkernel:Outofmemory:Killprocess10021(mysqld)score50orsacrificechild

messages-20150726:Jul2416:51:24localhostkernel:Outofmemory:Killprocess12025(mysqld)score95orsacrificechild

messages-20150726:Jul2416:51:24localhostkernel:Outofmemory:Killprocess21597(mysqld)score95orsacrificechild

参考资料

[1]. http://blog.sina.com.cn/s/blog_4550f3ca0100x7kf.html

[2]. http://blog.csdn.net/hjue/article/details/1957256

[1].http://blog.sina.com.cn/s/blog_4550f3ca0100x7kf.html

[2].http://blog.csdn.net/hjue/article/details/1957256

博客能带货吗

亭中意

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
mysql get error 28_MySQL Got error 134错误排查

现象Discuz论坛一张表反复崩溃，导致帖子页无法打开，日志如下：150729 23:24:17 [ERROR] /usr/local/mysql/libexec/mysqld: Incorrect key file for table './bbs/forum_threadaddviews.MYI'; try to repair it150729 23:24:18 [ERROR] /usr/lo...
复制链接

扫一扫