MySQL—异常处理

今天在一台测试服务器发现异常,开发人员发来一条sql语句发现无法执行,一直处于等待过程中,sql语句如下:

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

登录服务器,连接mysql发现登录不上

查看错误日志,报错如下:

  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


其中比较关键的这句:

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

检查服务器磁盘使用情况,发现负载较高,

df -h命令发现磁盘100%使用率,ibtmp1文件占用49G

故障处理:

删除了服务器上的一些日志文件,这时mysql可以登录

运行 show processlist命令

发现有几条sql处于等待状态

正是这几个sql导致的tmp文件暴增

sql语句如下:

desc (SELECT
	lam.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
FROM
	live.live_anchor_management lam
	LEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_id
	AND lpd.pic_property = 0
	LEFT JOIN customer cc ON lam.cus_id = cc.cus_id
	LEFT JOIN live.live_category lc ON lam.classification = lc.lc_id
WHERE
	lam.verify_status = 0
	AND lam.live_status = 1
	AND lam.sort = 1
ORDER BY
	lam.sticky_time DESC
	LIMIT 9999999999 ) UNION
	DISTINCT (
SELECT
	lam.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
FROM
	live.live_anchor_management lam
	LEFT JOIN live.live_picture_dict lpd ON lam.anchor_id = lpd.reference_id
	AND lpd.pic_property = 0
	LEFT JOIN customer cc ON lam.cus_id = cc.cus_id
	LEFT JOIN live.live_category lc ON lam.classification = lc.lc_id
WHERE
	verify_status = 0
	AND live_status = 1
	)

重启数据库服务

恢复正常

转载于:https://www.cnblogs.com/ykyk1229/p/10233639.html

  • 0
    点赞
  • 1
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值