今天在一臺(tái)測(cè)試服務(wù)器發(fā)現(xiàn)異常，開發(fā)人員發(fā)來(lái)一條sql語(yǔ)句發(fā)現(xiàn)無(wú)法執(zhí)行，一直處于等待過(guò)程中，sql語(yǔ)句如下：

1 delete from live.* where _id = 'xxxxx'

登錄服務(wù)器，連接mysql發(fā)現(xiàn)登錄不上

查看錯(cuò)誤日志，報(bào)錯(cuò)如下：

1 2019-01-07T02:12:40.663747Z 61131 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired size 67108864 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html 2 2019-01-07T02:12:41.120936Z 61131 [Warning] InnoDB: 1048576 bytes should have been written. Only 1032192 bytes written. Retrying for the remaining bytes. 3 2019-01-07T02:12:41.121006Z 61131 [Warning] InnoDB: Retry attempts for writing partial data failed. 4 2019-01-07T02:12:41.121023Z 61131 [ERROR] InnoDB: Write to file /data/mysql/3306/innodb/ibtmp1failed at offset 50735349760, 1048576 bytes should have been written, only 1032192 were written. Operating system error number 28. Check that your OS and file system support files of this size. Check also that the disk is not full or a disk quota exceeded. 5 2019-01-07T02:12:41.121056Z 61131 [ERROR] InnoDB: Error number 28 means 'No space left on device' 6 2019-01-07T02:12:41.121068Z 61131 [Note] InnoDB: Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/operating-system-error-codes.html 7 2019-01-07T02:12:41.121078Z 61131 [Warning] InnoDB: Error while writing 67108864 zeroes to /data/mysql/3306/innodb/ibtmp1 starting at offset 50679775232 8 2019-01-07T02:12:41.121129Z 61131 [ERROR] /usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_5' is full 9 2019-01-07T02:12:41.137490Z 61133 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired size 10502144 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at http://dev.mysql.com/doc/refman/5.7/en/ operating-system-error-codes.html 10 2019-01-07T02:12:41.137706Z 61133 [Warning] InnoDB: Retry attempts for writing partial data failed. 11 2019-01-07T02:12:41.137722Z 61133 [Warning] InnoDB: Error while writing 10502144 zeroes to /data/mysql/3306/innodb/ibtmp1 starting at offset 50736381952 12 2019-01-07T02:12:41.137761Z 61133 [ERROR] /usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_7' is full 13 2019-01-07T02:39:40.209105Z 61221 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file /data/mysql/3306/innodb/ibtmp1, desired [root

其中比較關(guān)鍵的這句：

1 /usr/local/mysql/bin/mysqld: The table '/data/mysql/3306/tmp/#sql_61f8_5' is full

檢查服務(wù)器磁盤使用情況，發(fā)現(xiàn)負(fù)載較高，

df -h命令發(fā)現(xiàn)磁盤100%使用率，ibtmp1文件占用49G

故障處理：

刪除了服務(wù)器上的一些日志文件，這時(shí)mysql可以登錄

運(yùn)行 show processlist命令

發(fā)現(xiàn)有幾條sql處于等待狀態(tài)

正是這幾個(gè)sql導(dǎo)致的tmp文件暴增

sql語(yǔ)句如下：

desc (SELECTlam.anchor_id,lam.cus_id,lam.anchor_nick_name,lam.theme,lam.classification,lam.live_status,times,lam.l_group,lam.screencap,lam.l_record,lam.sort,cc.ol_number,lpd.pic_name,lpd.pic_path,lam.appllication_time,lam.start_broadcasting_time,lc.lc_name classificationName FROMlive.live_anchor_management lamLEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_idAND lpd.pic_property = 0LEFT JOIN customer cc ON lam.cus_id = cc.cus_idLEFT JOIN live.live_category lc ON lam.classification = lc.lc_id WHERElam.verify_status = 0AND lam.live_status = 1AND lam.sort = 1 ORDER BYlam.sticky_time DESCLIMIT 9999999999 ) UNIONDISTINCT ( SELECTlam.anchor_id,lam.cus_id,lam.anchor_nick_name,lam.theme,lam.classification,lam.live_status,times,lam.l_group,lam.screencap,lam.l_record,lam.sort,cc.ol_number,lpd.pic_name,lpd.pic_path,lam.appllication_time,lam.start_broadcasting_time,lc.lc_name classificationName FROMlive.live_anchor_management lamLEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_idAND lpd.pic_property = 0LEFT JOIN customer cc ON lam.cus_id = cc.cus_idLEFT JOIN live.live_category lc ON lam.classification = lc.lc_id WHEREverify_status = 0AND live_status = 1)

重啟數(shù)據(jù)庫(kù)服務(wù)

恢復(fù)正常

轉(zhuǎn)載于:https://www.cnblogs.com/ykyk1229/p/10233639.html

總結(jié)

以上是生活随笔為你收集整理的MySQL—异常处理的全部?jī)?nèi)容，希望文章能夠幫你解決所遇到的問(wèn)題。

如果覺(jué)得生活随笔網(wǎng)站內(nèi)容還不錯(cuò)，歡迎將生活随笔推薦給好友。