当前位置: 代码迷 >> 综合 >> [error] [mysql]:1034 Incorrect key file for table 'table_name'; try to repair it
  详细解决方案

[error] [mysql]:1034 Incorrect key file for table 'table_name'; try to repair it

热度:20   发布时间:2024-01-06 14:55:42.0

[error] [mysql]:“1034 Incorrect key file for table ‘table_name’; try to repair it”

现象描述: mysql中大数据表执行alter增加字段时,/tmp的目录爆满,并且报错"1034 Incorrect key file for table ‘table_name’; try to repair it"。

故障分析
1、查询MySQL官网得知:

*“ALTER TABLE运行时会对原表进行临时复制,在副本上进行更改,然后删除原表,再对新表进行重命名。在执行ALTER TABLE时,其它用户可以阅读原表,但是对表的更新和修改的操作将被延迟,直到新表生成为止。新表生成后,这些更新和修改信息会自动转移到新表上。

2、通过分析可知在执行sql的时候,创建临时表进行排序的时候,/tmp的空间不足。

当事务开始时,它将缓冲区语句分配一个binlog_cache_size大小的缓冲区(我这里设置的是32768bytes)。 如果一个语句大于此,线程将打开一个临时文件来存储事务(默认是存放在/tmp/目录下)。当线程结束时,临时文件会自动被删除。上面就是因为事务里面的临时文件超过缓存区大小时,被放到/tmp目录下了,但是这个临时文件实在太大了,导致磁盘空间不足告警了。

MYSQL使用tmpdir情况:

  1. 执行计划file sort 文件名字MY开头
lsof|grep delete
如:/tmp/MYdRH1GW (deleted)
  1. 大事物binary log缓存 文件名字ML开头
lsof|grep delete

如:/tmp/MLq9INFu (deleted)

  1. 压缩的tempory table
CREATE TEMPORARY TABLE tmp_table1(id int) ROW_FORMAT=COMPRESSED ;
ls /tmp/

如:

# sql6b82_6_7.frm
# sql6b82_6_7.ibd
  1. online DDL 涉及排序比如add key
alter table testsort add key(id);
lsof|grep delete

如:

/tmp/ibCxlYQg (deleted)
/tmp/ib51nvZ1 (deleted)

设置 innodb_tmpdir可以将这类文件放到指定的目录

故障处理

  1. 无法扩展/tmp的大小,所以就修改mysql中tmpdir的位置,由于该参数是只读参数,只能在my.cnf中修改,重启生效
    enter description here

2.创建临时目录,并且修改权限

chmod 777 /data/tmp  #必须修改权限,让启动mysql的账户是可读写的. 此处是在root下创建,也可以切换到mysql用户下,然后创建目录,这样默认mysql就有读写权限了.

3、修改my.cnf

tmpdir = /data/tmp

4、重启mysql实例

5、查看tmpdir变量值
enter description here
备注:tmpdir变量已经修改生效了,后续执行SQL过程中生成的临时文件都会存储在这个目录下了.

6、重新执行sql语句,执行成功

  相关解决方案