다중 조인을 만들 때 tmp 테이블에 대한 MySQL 잘못된 키 파일

다중 조인을 만들 때 tmp 테이블에 대한 MySQL 잘못된 키 파일

---

나는 자주 도움을 위해 여기에 오지 않지만 이것에 꽤 좌절하고 누군가가 전에 그것을 만났기를 바랍니다.

둘 이상의 조인을 사용하여 테이블에서 레코드를 가져 오려고 할 때마다이 오류가 발생합니다.

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

따라서이 쿼리는 오류를 생성합니다.

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

그러나 이것은 :

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

그리고 이것도 마찬가지입니다.

SELECT * FROM `core_username`
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
ORDER BY `core_username`.`name` ASC LIMIT 1

원인은 무엇입니까? 나는 tmp 테이블을 복구하는 방법을 정말로 모르지만 매번 새로운 tmp 테이블이기 때문에 그것이 문제라고 생각하지 않습니다. 사용자 이름 테이블은 상당히 큽니다 (현재 233,718 개의 레코드).

어떤 도움이라도 대단히 감사하겠습니다.

업데이트 : 추가 테스트 후 결과를 주문하려고 할 때만 오류가 발생하는 것으로 보입니다. 즉,이 쿼리는 내가 기대하는 바를 제공합니다.

SELECT * FROM `core_username`
INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
LIMIT 1

하지만 다음을 추가하면 :

ORDER BY `core_username`.`name` ASC

오류가 발생합니다. 이것은 현재 사용중인 특정 웹 서버에서만 발생합니다. 데이터베이스를 다운로드하고 내 로컬 호스트와 다른 서버에서 동일한 작업을 시도하면 정상적으로 실행됩니다. MySQL 버전은 5.0.77입니다.

이것을 알고 나는 이 블로그 게시물에 설명 된대로 생성되는 tmp 테이블이 너무 커서 MySQL이 질식하는 일이 일어나고 있다고 확신합니다 . 그래도 해결책이 뭔지 모르겠지만 ...

---

임시 테이블에서이 오류가 발생하는 경우가 있습니다.

#126 - Incorrect key file for table '/tmp/#sql_64d_0.MYI'; try to repair it

/tmp폴더 공간이 부족 하기 때문일 수 있습니다 . 일부 Linux 설치에서는 /tmp자체 파티션에 있고 공간이 많지 않습니다. 대용량 MySQL 쿼리가이를 채 웁니다.

을 사용 df -h하여 \tmp자체 파티션에 있는지 여부 와 할당 된 공간 을 확인할 수 있습니다 .

자체 파티션에 있고 공간이 부족한 경우 다음 중 하나를 수행 할 수 있습니다.

(a) 파티션이 더 많은 공간을 갖도록 / tmp를 수정합니다 (메인 파티션으로 재 할당하거나 이동하여-예를 들어 여기 참조 ).
(b) MySql 구성을 변경 하여 다른 파티션 의 다른 임시 폴더 를 사용합니다./var/tmp

---

큰 테이블로 작업 할 때 수백 MB를 먹을 수 있으므로 쿼리를 실행하는 동안 MySQL tmpdir 사용 가능한 공간 (귀하의 경우 / tmp)을 확인하십시오. 이와 같은 것이 나를 위해 일했습니다.

$ while true; do df -h /tmp; sleep .5; done

---

이것을 실행

REPAIR TABLE `core_username`,`core_site`,`core_person`;

또는 다음을 수행하십시오.

select * from (
 SELECT * FROM `core_username`
 INNER JOIN `core_person` ON (`core_username`.`person_id` = `core_person`.`id`)
 INNER JOIN `core_site` ON (`core_username`.`site_id` = `core_site`.`id`)
 LIMIT 1)
ORDER BY `name` ASC

---

"ANALYZE TABLE"을 실행하면 도움이 될 수 있습니다.

We had this problem suddenly appear on a large table (~100M rows) and MySQL tried to use /tmp to write a temporary table of over 1GB, which failed as /tmp was limited to ~600M.

It turned out that the statistics for the InnoDB table were rather stale. After running "ANALYZE TABLE ...", the statistics were updated and the problem cleared. With the more accurate statistics, MySQL was able to optimize the query correctly and the large tmp file was no longer required.

We now run "mysqlcheck -Aa" periodically to keep all table statistics fresh.

---

I had this problem with a query on a table that had 500K+ records. It was giving me the same exact type of error, pointing to a .MYI file in the /tmp directory that was rarely there upon checking. I had already increased the heap and temp file sizes in the /etc/my.cnf file.

The problem with the query was the it did indeed contain a ORDER clause at the end, omitting it made the query work without error. It also had a LIMIT. I was trying to look at the most recent 5 records in the table. With the ORDER clause included it choked and gave the error.

What was happening was the mysqld was creating an internal temp table with ALL the records from the giant table to apply the ORDER.

The way that I got around this is to apply an additional WHERE condition, limiting the records from the giant table to some smaller set. I conveniently had a datetime field to do the filtering from.

I hope that helps someone.

---

On Unix, MySQL uses the value of the TMPDIR environment variable as the path name of the directory in which to store temporary files. If TMPDIR is not set, MySQL uses the system default, which is usually /tmp, /var/tmp, or /usr/tmp.

On Windows, Netware and OS2, MySQL checks in order the values of the TMPDIR, TEMP, and TMP environment variables. For the first one found to be set, MySQL uses it and does not check those remaining. If none of TMPDIR, TEMP, or TMP are set, MySQL uses the Windows system default, which is usually C:\windows\temp.

If the file system containing your temporary file directory is too small, you can use the --tmpdir option to mysqld to specify a directory in a file system where you have enough space.

In MySQL 5.0, the --tmpdir option can be set to a list of several paths that are used in round-robin fashion. Paths should be separated by colon characters (“:”) on Unix and semicolon characters (“;”) on Windows, NetWare, and OS/2.

---

I experience the same problem.

Here is my solution : 1. Dont use "select *". Just select field that you need. 2. Split the query. If the field you select is too much, splitting it to some query can be a result. You can "array_merge()" the result later if you want the variable that contain the result not changed.

On my case, I split the query to 5 queries, then array merge it using PHP.

The problem is lies on the mysql server. It is just a thing that application developer (such us me) don't has a previlege.

---

I had similar problem. In my own case, the problem occurred due to incorrect owner/permission. I just had to change the owner on my data directory to mysql user and this resolved the problem.

---

Only increase the file tmp, because mysql doesn't have space in it, for queries...

mount -o remount,size=[NEW MAX SIZE HERE] tmpfs /tmp

Links reference:

General error: 126 Incorrect key file for table ‘/tmp/#sql_254c_0.MYI’; try to repair it

[ERROR] /usr/sbin/mysqld: Incorrect key file for table '/mysqltmp/#sql_ca1a_0.MYI'; try to repair it

How to increase /tmp partition size

---

the index keys for one of the 3 tables might be bad, try running a repair command on all 3 tables.

---

Using the EXPLAIN keyword may help find out how to best optimize this query. Essentially, what you need to do is get the result set as small as possible as quickly as possible. If you have a result set of every row in core_username until the end, when you order it you run the risk of... this.

If you can do the ordering on core_username alone without a problem, you may want to get the min() row as a subquery.

참고URL : https://stackoverflow.com/questions/2090073/mysql-incorrect-key-file-for-tmp-table-when-making-multiple-joins