Incorrect key file for table '/tmp/... ' wrong resolution-record

This article mainly describes the incorrect key file for table '/tmp/... ' Error discovery and simple workaround for this record.

Welcome to reprint, please indicate the author, source.     Author: Zhang blog:http://space.itpub.net/26355921 qq:176036317 If you have questions, please contact. Suddenly received development call said MySQL database execution SQL error, let's troubleshoot the reason.

Login to the MySQL server to see the error log:
140904 12:06:20 [ERROR]/usr/local/mysql5.5/bin/mysqld:incorrect key file for table '/tmp/#sql_5608_1. Myi '; Try to repair it
140904 12:06:20 [ERROR] Got a error from unknown thread,/tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_ write.c:223


See/TMP problem, first look at the Tmpdir:


Mysql> Show variables like ' tmpdir ';
+---------------+-------+
| variable_name | Value |
+---------------+-------+
| Tmpdir | /tmp |
+---------------+-------+
1 row in Set (0.00 sec)


Said/TMP under the table need to repair, through Tmpdir, is a temporary sort table. Check on the Internet, said the problem is due to the Tmpdir directory under the lack of space.
To view disk space:
[Root@localhost data]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 15G 31G 33%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


/tmp belongs to/root directory, space and 31G, feel impossible.


Look at the slow log, find a very suspicious statement, take 500,000 of the data to sort, and some statements take more than 2 million of the data to sort, but also from time and development said time is very consistent:
# time:140904 16:13:50
# User@host:staff_reader[staff_reader] @ [10.35.15.68]
# query_time:133.653564 lock_time:0.000186 rows_sent:5384178 rows_examined:20886933
Use Hotel_inventory;
SET timestamp=1409818430;
SELECT COL1,COL2,COL3....,COL7
From table1 nolock WHERE
Available_time >= ' 2014-09-03 ' and Available_time <= date_add (' 2014-09-03 ', interval)
Order BY Hotel_id,room_type_id,available_time;


Look at the details of the table accessed by the statement:

Number of table rows is 20886994:
Mysql> Select COUNT (*) from table1;
+----------+
| COUNT (*) |
+----------+
| 20886994 |
+----------+


Table size is 7G:
[root@localhost]# Du-sch *|grep SUM_INV_ROOM.IBD
6.9G SUM_INV_ROOM.IBD


There are 4 instances on this machine:
[root@192.168.97.149 hotel_inventory]# Ps-ef|grep Mysqld_safe
Root 21185 1 0 2013? 00:00:00/bin/sh/usr/local/mysql5.5/bin/mysqld_safe--defaults-file=/data/mysql6025/etc/my6025.cnf
Root 22525 1 0 2013? 00:00:00/bin/sh/usr/local/mysql5.5/bin/mysqld_safe--defaults-file=/data/mysql6027/etc/my6027.cnf
Root 24275 1 0 2013? 00:00:00/bin/sh/usr/local/mysql5.5/bin/mysqld_safe--defaults-file=/data/mysql6024/etc/my6024.cnf
Root 37864 1 0 2013? 00:00:00/bin/sh/usr/local/mysql5.5/bin/mysqld_safe--defaults-file=/data/mysql6026/etc/my6026.cnf
Root 60463 5879 0 16:18 pts/6 00:00:00 grep mysqld_safe


With a total size of around 30G, all data are extracted simultaneously:
[root@192.168.97.149 hotel_inventory]# du-sch/data/mysql602*/data/hotel_inventory/* |grep sum_inv_room.ibd
6.9g/data/mysql6024/data/hotel_inventory/sum_inv_room.ibd
7.1g/data/mysql6025/data/hotel_inventory/sum_inv_room.ibd
7.9g/data/mysql6026/data/hotel_inventory/sum_inv_room.ibd
6.5g/data/mysql6027/data/hotel_inventory/sum_inv_room.ibd


Now that I see these phenomena, I am sure that this statement is the result of simultaneous execution. At this time to develop feedback, and development said [Staff_reader] @ [10.35.15.68] This user is not theirs. No way, keep looking. I executed the above statement manually, and found that there were not many temporary files at all. It is time to conclude that this statement is not the result.
So this time we can only wait for the problem to reappear. Looked at the disk space, under normal circumstances,/root directory free space is 31G, and then I wrote a script has been monitoring the availability of space is 31G, if not, give me a QQ mailbox email.


After a while, sure enough to receive the mail, at this time on the server to see disk space, in the continued growth:




To view disk space conditions:
[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 30G 17G 65%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 33G 14G 71%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 35G 11G 77%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[Root@192.168.97.149 tmp]# Du-sch *
88K mha4mysql-node-0.53
2.1G #sql_5608_0. myd
4.0K #sql_5608_0. myi
3.6G #sql_5608_1. myd
4.0K #sql_5608_1. myi
1.9G #sql_5b44_0. myd
4.0K #sql_5b44_0. myi
4.9G #sql_5b44_1. myd
4.0K #sql_5b44_1. myi
2.0G #sql_621a_0. myd
4.0K #sql_621a_0. myi
749M #sql_621a_1. myd
4.0K #sql_621a_1. myi
2.2G #sql_9753_0. myd
4.0K #sql_9753_0. myi
4.2G #sql_9753_1. myd
4.0K #sql_9753_1. myi
22G Total


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 37G 9.5G 80%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 41G 5.2G 89%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 46G 29M 100%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data
[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 46G 22M 100%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data




[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 46G 0 100%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


Full of space at the same time, see/data/mysql6025/log/mysqld.err Error:
140904 16:03:59 [ERROR]/usr/local/mysql5.5/bin/mysqld:incorrect key file for table '/tmp/#sql_5608_1. Myi '; Try to repair it
140904 16:03:59 [ERROR] Got a error from unknown thread,/tmp/mysql5.5_install/tmp/mysql-5.5.30/storage/myisam/mi_ write.c:223


Through the slow log, it appears that the above very suspicious statement. But before the possibility of excluding it, it can only continue to find, found a very short statement, looks like nothing, but the execution time is very long:
Select MIN (sum_inv_room_id), MAX (sum_inv_room_id) from (select S.*, 20140909 as Dw_insert_dt_wid from Sum_inv_room s WHERE (1 = 1)) as T1;


During this time of error, basically these two SQL are running.




After the error is returned, disk space is gradually restored:


........................................................


[root@192.168.97.149 tmp]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 38G 8.6G 82%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


[root@192.168.97.149 log]# Df-h
FileSystem Size Used Avail use% mounted on
/dev/sda2 49G 15G 31G 33%/
Tmpfs 32G 0 32G 0%/dev/shm
/DEV/SDA1 985M 44M 891M 5%/boot
/DEV/SDA4 219G 6.5G 201G 4%/opt
/DEV/SDB1 1.4T 176G 1.2T 14%/data


The final space usage returns to normal.




Then manually take this statement to perform a bit:
Select MIN (col1), MAX (col1) from (select S.*, 20140909 as Dw_insert_dt_wid from table1 s WHERE (1 = 1)) as T1;


Sure enough, we saw disk space soar and find out why. Single statement execution, see the resulting temporary files up to more than 10 g. A total of 31G of space, 4 instances, all produce more than 10 g, of course, can't carry.


(As for the specific reason why such a statement would produce such a large temporary file, I do not understand the principle, do this record first.) ）


Through and development communication, Development said that is timed to extract data. And the 4 instances on the machine start at the same time.


So the following solutions are given:
1. The MySQL parameter tmpdir set to the large directory down, but this need parameter is READ_ONLY variable, need to restart the database
2. Serial execution of these 4 statements
3. Modify the SQL of the extracted data

Finally, the development of a 2nd method, the Fixed-point data extraction of the SQL serial execution, the problem is temporarily resolved, at least half a month did not appear this problem.

I've had this problem today:

last_errno:126

Last_error:error ' incorrectkey file for table '/tmp/#sql_1ce3_0. Myi '; Try to repair it ' on query. DefaultDatabase: ' np044 '. Query: ' Update t_src_biz_type t,t_biz_type b set b.source_system_id=t.source_system_id,b.system_id=t.system_id

......

I direct start slave, can start normally, (*^__^*) Xi hee ...