[Err] 1194 – Table is marked as crashed and should be repaired

热度s:534℃ 评论:0 发布时间:2018-01-02 收录:已收录

[Err] 1194 - Table is marked as crashed and should be repaired

正在写代码,突然服务器超级卡,点开任务管理器发现CPU一直百分百,切换到进程,将CPU栏从高到低排列,瞬间发现mysqld.exe 这个进程CPU占用超高。

分析日志

启用mysql通用查询日志

set global general_log=on;

设置通用日志输出为表格形式

set global log_output='TABLE';

然后打开mysql数据裤查看 general_log 

SELECT * FROM 'general_log' LIMIT 0, 200

[Err] 1194 - Table is marked as crashed and should be repaired

发现最近mysql查询全部围绕的是 wp_it_options 这个表,查询的语句也是一直重复这几条语句

SELECT option_name, option_value FROM wp_it_options
SELECT option_name, option_value FROM wp_it_options WHERE autoload = 'yes'

手动执行该语句报错:

[SQL]SELECT option_name, option_value FROM wp_it_options WHERE autoload = 'yes'

[Err] 1194 - Table 'wp_it_options' is marked as crashed and should be repaired

wp_it_options 表标记有问题,需要修复。

我记得前几天查日志的时候,也看到过这个错误,当时我还用这种快速解决的方式解决过:

myisamchk -c -r ../data/lrwa/wp_it_options.MYI

不知道为什么又出现了。那么再一次解决吧。

修复mysql 1194 错误

解决 [Err] 1194 - Table is marked as crashed and should be repaired 问题可以用  mysqlcheck.exe 工具,先用 mysqlcheck.exe 查看一下吧。

D:\MySQL\bin>mysqlcheck.exe -u root -p lrwa
Enter password: **************
lrwa.wechat_subscribers_lite_history               OK
lrwa.wp_baidusubmit_sitemap                        OK
lrwa.wp_baidusubmit_urlstat                        OK
lrwa.wp_it_al_urls                                 OK
lrwa.wp_it_al_urls_index                           OK
lrwa.wp_it_commentmeta                             OK
lrwa.wp_it_comments                                OK
lrwa.wp_it_links                                   OK
lrwa.wp_it_login_log                               OK
lrwa.wp_it_options
warning  : Table is marked as crashed
warning  : 4 clients are using or haven't closed the table properly
error    : Found too long record (158) at 3122392
error    : Corrupt
lrwa.wp_it_postmeta                                OK
lrwa.wp_it_posts                                   OK
lrwa.wp_it_posts-bak                               OK
lrwa.wp_it_revslider_css                           OK
lrwa.wp_it_revslider_layer_animations              OK
lrwa.wp_it_revslider_navigations                   OK
lrwa.wp_it_revslider_sliders                       OK
lrwa.wp_it_revslider_slides                        OK
lrwa.wp_it_revslider_static_slides                 OK
lrwa.wp_it_rg_form                                 OK
lrwa.wp_it_rg_form_meta                            OK
lrwa.wp_it_rg_form_view                            OK
lrwa.wp_it_rg_lead                                 OK
lrwa.wp_it_rg_lead_detail                          OK
lrwa.wp_it_rg_lead_detail_long                     OK
lrwa.wp_it_rg_lead_meta                            OK
lrwa.wp_it_rg_lead_notes                           OK
lrwa.wp_it_sharebar                                OK
lrwa.wp_it_term_relationships                      OK
lrwa.wp_it_term_taxonomy                           OK
lrwa.wp_it_termmeta                                OK
lrwa.wp_it_terms                                   OK
lrwa.wp_it_usermeta                                OK
lrwa.wp_it_users                                   OK
lrwa.wp_it_woocommerce_api_keys                    OK
lrwa.wp_it_woocommerce_attribute_taxonomies        OK
lrwa.wp_it_woocommerce_downloadable_product_permissions OK
lrwa.wp_it_woocommerce_order_itemmeta              OK
lrwa.wp_it_woocommerce_order_items                 OK
lrwa.wp_it_woocommerce_payment_tokenmeta           OK
lrwa.wp_it_woocommerce_payment_tokens              OK
lrwa.wp_it_woocommerce_sessions                    OK
lrwa.wp_it_woocommerce_shipping_zone_locations     OK
lrwa.wp_it_woocommerce_shipping_zone_methods       OK
lrwa.wp_it_woocommerce_shipping_zones              OK
lrwa.wp_it_woocommerce_tax_rate_locations          OK
lrwa.wp_it_woocommerce_tax_rates                   OK
lrwa.wp_it_ws_alipay_orders                        OK
lrwa.wp_it_ws_alipay_ordersmeta                    OK
lrwa.wp_it_ws_alipay_products                      OK
lrwa.wp_it_ws_alipay_productsmeta                  OK
lrwa.wp_it_ws_alipay_templates                     OK
lrwa.wp_it_ws_alipay_templatesmeta                 OK

嗯,只有 wp_it_options 表有问题,我们添加 –auto-repair 参数自动修复试试,先备份哦。

D:\MySQL\bin>mysqlcheck.exe -u root -p lrwa --auto-repair
Enter password: **************
lrwa.wechat_subscribers_lite_history               OK
lrwa.wp_baidusubmit_sitemap                        OK
lrwa.wp_baidusubmit_urlstat                        OK
lrwa.wp_it_al_urls                                 OK
lrwa.wp_it_al_urls_index                           OK
lrwa.wp_it_commentmeta                             OK
lrwa.wp_it_comments                                OK
lrwa.wp_it_links                                   OK
lrwa.wp_it_login_log                               OK
lrwa.wp_it_options
warning  : Table is marked as crashed
warning  : 4 clients are using or haven't closed the table properly
error    : Found too long record (158) at 3122392
error    : Corrupt
lrwa.wp_it_postmeta                                OK
lrwa.wp_it_posts                                   OK
lrwa.wp_it_posts-bak                               OK
lrwa.wp_it_revslider_css                           OK
lrwa.wp_it_revslider_layer_animations              OK
lrwa.wp_it_revslider_navigations                   OK
lrwa.wp_it_revslider_sliders                       OK
lrwa.wp_it_revslider_slides                        OK
lrwa.wp_it_revslider_static_slides                 OK
lrwa.wp_it_rg_form                                 OK
lrwa.wp_it_rg_form_meta                            OK
lrwa.wp_it_rg_form_view                            OK
lrwa.wp_it_rg_lead                                 OK
lrwa.wp_it_rg_lead_detail                          OK
lrwa.wp_it_rg_lead_detail_long                     OK
lrwa.wp_it_rg_lead_meta                            OK
lrwa.wp_it_rg_lead_notes                           OK
lrwa.wp_it_sharebar                                OK
lrwa.wp_it_term_relationships                      OK
lrwa.wp_it_term_taxonomy                           OK
lrwa.wp_it_termmeta                                OK
lrwa.wp_it_terms                                   OK
lrwa.wp_it_usermeta                                OK
lrwa.wp_it_users                                   OK
lrwa.wp_it_woocommerce_api_keys                    OK
lrwa.wp_it_woocommerce_attribute_taxonomies        OK
lrwa.wp_it_woocommerce_downloadable_product_permissions OK
lrwa.wp_it_woocommerce_order_itemmeta              OK
lrwa.wp_it_woocommerce_order_items                 OK
lrwa.wp_it_woocommerce_payment_tokenmeta           OK
lrwa.wp_it_woocommerce_payment_tokens              OK
lrwa.wp_it_woocommerce_sessions                    OK
lrwa.wp_it_woocommerce_shipping_zone_locations     OK
lrwa.wp_it_woocommerce_shipping_zone_methods       OK
lrwa.wp_it_woocommerce_shipping_zones              OK
lrwa.wp_it_woocommerce_tax_rate_locations          OK
lrwa.wp_it_woocommerce_tax_rates                   OK
lrwa.wp_it_ws_alipay_orders                        OK
lrwa.wp_it_ws_alipay_ordersmeta                    OK
lrwa.wp_it_ws_alipay_products                      OK
lrwa.wp_it_ws_alipay_productsmeta                  OK
lrwa.wp_it_ws_alipay_templates                     OK
lrwa.wp_it_ws_alipay_templatesmeta                 OK

Repairing tables
lrwa.wp_it_options
info     : Found block with too small length at 3122392; Skipped
warning  : Number of rows changed from 41229 to 41228
status   : OK

提示OK,我们在执行上面日志中记录的那条语句,发现已经可以正常查询
[Err] 1194 - Table is marked as crashed and should be repaired
[Err] 1194 - Table is marked as crashed and should be repaired
这个时候CPU也已经不再过高了,虽然还有些不稳定,但是不稳定问题并不在是因为mysql了。

最后,附上两个 general_log 命令:

set global general_log=off; //关闭mysql通用日志
truncate table general_log;  //清空general_log日志

关于懒人网安

懒人网安这个站点是我很久以前建的,最开始是博客,后来改成了工作室的性质。
因为我爱折腾,又没有备份的习惯,导致该站数据裤和代码许多地方都存在问题,因此于 2016年8月 建立了本博客,弃用了 懒人网安,已经停止更新和维护。
其实我很为该站的安全担忧,因为已经没有在搭理它了,之所以还留着,无非就是该站还有点流量,权限我已经尽量限制,先暂时留着,等到了哪天必须舍弃的时候,在删掉做个301就好。

本文由 Mr.Wu 创作,除注明转载/出处外,均为本站原创,转载前请注明出处!

发表评论

电子邮件地址不会被公开。 必填项已用*标注

顶部
护眼