mysql - 记录一次数据库长时间执行导致的 无法写入 mysql table became not writable due to a long sql execution

访问量: 150

参考: https://severalnines.com/database-blog/my-mysql-database-corrupted-what-do-i-do-now

参考: https://stackoverflow.com/questions/50239181/how-to-show-full-processlist-in-mysql-with-state-higher-than-x

今天发生了一次现象,我们 需要把某个表的某个列,从目前的 utf8 改成 utf8mb4, 这样的话就可以保存手机输入的特殊符号了.

不过我们在执行migration 的时候, migration 写的有误, 导致了 下面这条SQL 被执行了10小时:

ALTER TABLE `user_behaviers` CHANGE `params_text` `params_text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL

所以, 数据库的对应的表一直是不可写的状态.

解决办法:

在 processlist 中找到该命令,干掉即可. 

具体的过程:

1. 执行了migration 一小时之后,发现仍然不行,于是果断终止(在代码端ctrl + c ) . 以为任务已经执行完毕,结果是 任务仍然在继续执行. 

2. 在 代码的执行过程中发现,每次需要往db table 写入时,都 卡住

3. 登录mysql console , 发现仍然无法写入. 哪怕是空值都不行,卡住 .

4. 所以猜测肯定是任务卡住了.  

5. 使用 show table status;  没发现异常

6. 使用 checke table 来查看,都没问题. 都是 OK

7. 无法重启mysql, 因为在运行另外一个特别重要的db, 所以 使用 show processlist 来查看.

8. 基本发现问题, 于是 进一步使用 详细命令: 

mysql> select * from information_schema.processlist  where time > 100 and db = 'waf';
+--------+-------+-------------------------------------------------------+------+---------+--------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| ID     | USER  | HOST           | DB   | COMMAND | TIME   | STATE                           | INFO                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
+--------+-------+-------------------------------------------------------+------+---------+--------+---------------------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 659075 | xxxxx | ip-xxxxxl:43282| waf  | Query   |  36578 | Waiting for table metadata lock | INSERT INTO `user_behaviers` (`ip`, `member_id`, `time`, `params_text`, `cookie`, `uri`, `host`, `http_method`, `behavier_id`, `ip_id`, `device_id`) VALUES ('220.174.38.76', 38846, '2020-10-24 20:50:45', '{\"nick_name\"=>\"财神宝宝驾到                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  |
| 764885 | xxxxxx| ip-xxxxxl:43282| waf  | Query   |  36580 | copy to tmp table               | ALTER TABLE `user_behaviers` CHANGE `params_text` `params_text` TEXT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci DEFAULT NULL                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
| 777450 | xxxxx | ip-xxxxxl:43282| waf  | Query   |   4320 | Waiting for table metadata lock | INSERT INTO `user_behaviers` (`uri`, `ip_id`) VALUES ('/xxx/xxxxx/xxx/xxxx', 47877)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      |
| 777477 | xxxxx | ip-xxxxxl:43282| waf  | Query   |   4266 | Waiting for table metadata lock | INSERT INTO `user_behaviers` (`time`) VALUES ('2020-12-05 18:12:21')                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                         |
| 777297 | xxxxx | ip-xxxxxl:37368| waf  | Query   |   4672 | Waiting for table metadata lock | INSERT INTO `user_behaviers` (`time`, `uri`, `behavier_id`, `ip_id`) VALUES ('2020-12-05 16:43:22', '/mobile_apxxxxxt', 19, 47877)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                    |
| 767852 | xxxxx | ip-xxxxxl:40694| waf  | Query   |  27887 | Waiting for table metadata lock | INSERT INTO `user_behaviers` (`member_id`, `time`, `params_text`, `cookie`, `uri`, `host`, `http_method`, `behavier_id`, `ip_id`, `device_id`) VALUES (40033, '2020-12-05 11:14:55', '{\"app_version\"=>\"2.5.2\", \"nonce\"=>\"1607138095\", \"os_type\"=>\"android\", \"os_version\"=>\"10\", \"signature\"=>\"xxxxxxx\", \"public_key\"=>\"xxxddcfbfb\", \"model\"=>\"PD1938C\", \"device_type\"=>\"PD1938C\", \"lang\"=>\"zh\", \"market_id\"=>\"xxx\"}', '', '/xxxxxxs', 'nxxxxx.xxx.com', 'GET', 114, 126074, 123)                                              

9. 上面列中的 TIME, 单位是秒,是执行或者等待的时间.  所以,36578 (10个小时之前) 刚好就是我们执行一个错误的SQL语句的时间点.

于是干掉该进程就可以了.

下面是精简版:

mysql> select id, db, command, state from information_schema.processlist  where time > 100 and db = 'waf' limit 30;
+--------+------+---------+---------------------------------+
| id     | db   | command | state                           |
+--------+------+---------+---------------------------------+
| 659075 | waf  | Query   | Waiting for table metadata lock |
| 764885 | waf  | Query   | copy to tmp table               |
| 777450 | waf  | Query   | Waiting for table metadata lock |
| 777477 | waf  | Query   | Waiting for table metadata lock |
| 777297 | waf  | Query   | Waiting for table metadata lock |
| 767852 | waf  | Query   | Waiting for table metadata lock |
| 767338 | waf  | Query   | Waiting for table metadata lock |
| 777197 | waf  | Query   | Waiting for table metadata lock |
| 659076 | waf  | Sleep   |                                 |
| 659074 | waf  | Sleep   |                                 |
| 777584 | waf  | Sleep   |                                 |
| 767790 | waf  | Query   | Waiting for table metadata lock |
| 777272 | waf  | Query   | Waiting for table metadata lock |
| 767308 | waf  | Query   | Waiting for table metadata lock |
| 767330 | waf  | Query   | Waiting for table metadata lock |
| 767359 | waf  | Query   | Waiting for table metadata lock |
| 777497 | waf  | Query   | Waiting for table metadata lock |
+--------+------+---------+---------------------------------+
17 rows in set, 1 warning (0.01 sec)

10.  mysql >  kill xxxx   ( 全都干掉)

就可以了.

订阅/RSS Feed

Subscribe

分类/category