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----