ERROR 126 (HY000): Incorrect key file for table '/tmp/#s

页面导航:首页 > 数据库 > Mysql > ERROR 126 (HY000): Incorrect key file for table '/tmp/#s

ERROR 126 (HY000): Incorrect key file for table '/tmp/#s

来源: 作者: 时间:2016-02-03 08:56 【

author:skatetime:2012 06 25ERROR 126 (HY000): Incorrect key file for table 39; tmp sql_1d87_0 MYI 39;; try to repair it案例:mysql insert into t1 select * from t1;^[[AQuer
author:skate
time:2012/06/25
 
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_1d87_0.MYI'; try to repair it
 
案例:
> 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都成功了,查看磁盘空间如下:
[[email protected] ~]# 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
...
[[email protected] mysql]#  support-files/mysql.server  restart
Shutting down MySQL (Percona Server).....[  OK  ]
Starting MySQL (Percona Server)...[  OK  ]
 
[[email protected] 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, 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----
Tags:

文章评论

最 近 更 新
热 点 排 行
Js与CSS工具
代码转换工具

<