ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_1d87_0.MYI'; try to repair it

ゝ一世哀愁。 2022-06-10 02:27 73阅读 0赞

author：skate  
time:2012/06/25

**ERROR 126 (HY000): Incorrect key file for table '/tmp/\#sql\_1d87\_0.MYI'; try to repair it**

案例：

mysql> insert into t1 select \* from t1;  
^\[\[AQuery OK, 24576 rows affected (0.44 sec)  
Records: 24576  Duplicates: 0  Warnings: 0

mysql> insert into t1 select \* from t1;  
Query OK, 49152 rows affected (1.90 sec)  
Records: 49152  Duplicates: 0  Warnings: 0

mysql> insert into t1 select \* from t1;  
ERROR 126 (HY000): Incorrect key file for table '/tmp/\#sql\_1d87\_0.MYI'; try to repair it

从上面的情况看，好似空间的问题，因为前几次insert都成功了，查看磁盘空间如下：

\[root@localhost ~\]\# df -h  
Filesystem            Size  Used Avail Use% Mounted on  
/dev/mapper/VolGroup00-LogVol00  
                      5.3G  5.0G   50M 100% /  
/dev/sda1              99M   12M   83M  13% /boot  
tmpfs                 125M     0  125M   0% /dev/shm  
/dev/sdb1             7.7G  183M  7.2G   3% /data

在查看mysql数据目录情况  
mysql> show variables like '%dir%';  
\+-----------------------------------------+------------------------------------+  
| Variable\_name                           | Value                              |  
\+-----------------------------------------+------------------------------------+  
| basedir                                 | /usr/local/mysql                   |  
| binlog\_direct\_non\_transactional\_updates | OFF                                |  
| character\_sets\_dir                      | /usr/local/mysql/share/charsets/   |  
| datadir                                 | /usr/local/mysql/data/             |  
| innodb\_data\_home\_dir                    | /data/mysql/data                   |  
| innodb\_log\_group\_home\_dir               | /data/mysql/data                   |  
| innodb\_max\_dirty\_pages\_pct              | 75                                 |  
| lc\_messages\_dir                         | /usr/local/mysql/share/            |  
| plugin\_dir                              | /usr/local/mysql/lib/mysql/plugin/ |  
| slave\_load\_tmpdir                       | /tmp                               |  
| tmpdir                                  | /tmp                               |  
\+-----------------------------------------+------------------------------------+  
11 rows in set (0.01 sec)

从上面看确实是空间满了，然后调整变量“tmpdir”和“slave\_load\_tmpdir”，然后重启mysql服务，结果就ok了

\[mysqld\]  
...  
tmpdir=/data/mysql  
slave\_load\_tmpdir=/data/mysql  
...

\[root@localhost mysql\]\#  support-files/mysql.server  restart  
Shutting down MySQL (Percona Server).....\[  OK  \]  
Starting MySQL (Percona Server)...\[  OK  \]

\[root@localhost mysql\]\# bin/mysql  
Welcome to the MySQL monitor.  Commands end with ; or \\g.  
Your MySQL connection id is 1  
Server version: 5.5.24-log Source distribution

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its  
affiliates. Other names may be trademarks of their respective  
owners.

Type 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.

mysql> insert into t2 select \* from t2;  
ERROR 1046 (3D000): No database selected  
mysql> use test;  
Database changed  
mysql> insert into t2 select \* from t2;  
Query OK, 98304 rows affected (4.48 sec)  
Records: 98304  Duplicates: 0  Warnings: 0

这个就是我在改数据的目录时，忘记改这个变量值了。

\----end----

发表评论取消回复

表情：

评论列表（有 0 条评论，73人围观）

还没有评论，来说两句吧...

相关阅读

相关 MySQL执行语句报Incorrect key file for table '/tmp/#sql_.MYI'; try to repair it

ySQL执行语句报Incorrect key file for table '/tmp/\sql\_4c80\_0.MYI'; try to repair it ...

亦凉/ 2024年04月17日 23:50/ 0 赞/ 18 阅读

相关 MySQL插入中文时编码错误：ERROR 1366 (HY000): Incorrect string value: '' for column '' at row 1...

转自：[https://my.oschina.net/gwlCode/blog/3004454][https_my.oschina.net_gwlCode_blog_30044

浅浅的花香味﹌/ 2023年06月02日 09:25/ 0 赞/ 53 阅读

相关 mysql 1366 hy000_mysql ERROR 1366 (HY000)

我是在ubuntu下使用得mysql. 当执行下面语句的时候 mysql>insert into role(code,name) values(200,'管理员'); 出

短命女/ 2022年11月08日 11:28/ 0 赞/ 226 阅读

相关 ERROR 1820 (HY000): Unknown error 1820,ERROR 1046 (3D000):

MySQL登录进去之后，执行语句出现报错： root@localhost 09:05: [(none)]> use mysql; ERROR 1820 (H

深碍√TFBOYSˉ_/ 2022年09月10日 14:16/ 0 赞/ 194 阅读

相关 ERROR 1366 (HY000): Incorrect string value-解决方案

遇到的问题在cmd中插入数据的时候报错 mysql> insert into category(cid,cname) values('c005','家电');

绝地灬酷狼/ 2022年06月10日 04:10/ 0 赞/ 262 阅读

相关 ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_1d87_0.MYI'; try to repair it

author：skate time:2012/06/25 ERROR 126 (HY000): Incorrect key file for table '/tmp

ゝ一世哀愁。/ 2022年06月10日 02:27/ 0 赞/ 74 阅读

相关 ERROR 1813 (HY000) at line 404: Tablespace for table '`xxx`.`xxxxx`' exists.

<table> <tbody> <tr> <td> <div> 1 </div> <div> 2

╰+哭是因爲堅強的太久メ/ 2022年05月24日 04:54/ 0 赞/ 115 阅读

相关 ERROR 1406 (22001): Data too long for column 'c_name' at row 1&&ERROR 1366 (HY000): Incorrect string

> 问题描述： mysql> use flyingcat; Database changed mysql> source E:\wamp64\www\

分手后的思念是犯贱/ 2022年03月21日 14:36/ 0 赞/ 205 阅读

相关 mysql表损坏 Incorrect key file for table ‘xxxx‘; try to repair it 解决方法

数据损坏原因 MySQL表损坏一般是数据损坏，引起损坏的原因可能是由于磁盘损坏、系统崩溃或者MySQL服务器被崩溃等外部原因。例如有人使用kill -9终止进程，导致My

矫情吗；*/ 2022年03月08日 16:28/ 0 赞/ 198 阅读

相关 ERROR 1114 (HY000): The table 'test1' is full

查询任何一条语句都显示 The table '/home/mysql/data3015/tmp/\sql\_13975\_23' is full 查看了下数据库

野性酷女/ 2022年02月03日 09:53/ 0 赞/ 216 阅读