ERROR126 (HY000): Incorrectkeyfilefortable '/tmp/# SQL _ MySQL

ERROR126 (HY000): Incorrectkeyfilefortable bitsCN. comauthor: skate

Time: 2012/06/25

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

Case:

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

From the above situation, it seems like a space problem, because the previous insert operations are successful, check the disk space as follows:

[Root @ localhost ~] # Df-h

Filesystem Size Used Avail Use % Mounted on

/Dev/mapper/VolGroup00-LogVol00

5.3 GB 5.0G 50 M 100%/

/Dev/sda1 99 M 12 M 83 M 13%/boot

Tmpfs 125 M 0 125 M 0%/dev/shm

/Dev/sdb1 7.7G 183 M 7.2G 3%/data

View the mysql data directory

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)

As shown above, the space is full. then, adjust the variables "tmpdir" and "slave_load_tmpdir", restart the mysql service, and the result is 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,201 1, 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

This is why I forgot to change the variable value when changing the data directory.

---- End ----

BitsCN.com