欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

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

程序员文章站 2024-01-09 08:17:52
...
bitsCN.com 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----

bitsCN.com

上一篇:

下一篇: