mysql - 记录一次数据库长时间执行导致的 无法写入 mysql table became not writable due to a long sql execution
访问量: 1408
参考: https://severalnines.com/database-blog/my-mysql-database-corrupted-what-do-i-do-now
今天发生了一次现象,我们 需要把某个表的某个列,从目前的 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 ( 全都干掉)
就可以了.