How do you fix a MySQL “Incorrect key file” error when you can't repair the table?

Question

I'm trying to run a rather large query that is supposed to run nightly to populate a table. I'm getting an error saying Incorrect key file for table '/var/tmp/#sql_201e_0.MYI'; try to repair it but the storage engine I'm using (whatever the default is, I guess?) doesn't support repairing tables.

how do I fix this so I can run the query?

the tmp folder has a limit usually 2GB, try df -h to see it – Elzo Valugi Jan 18 '12 at 12:17 — Elzo Valugi, Jan 18 '12 at 12:17

MarkR · Accepted Answer · 2010-03-12 07:41:09Z

正确答案
The storage engine (MyISAM) DOES support repair table. You should be able to repair it.

If the repair fails then it's a sign that the table is very corrupted, you have no choice but to restore it from backups.

If you have other systems (e.g. non-production with same software versions and schema) with an identical table then you might be able to fix it with some hackery (copying the frm an MYI files, followed by a repair).

In essence, the trick is to avoid getting broken tables in the first place. This means always shutting your db down cleanly, never having it crash and never having hardware or power problems. In practice this isn't very likely, so if durability matters you may want to consider a more crash-safe storage engine.

marknt15 · Answer 2 · 2010-11-02 07:48:49Z

You must change the location of MySQL's temporary folder which is '/tmp' in most cases to a location with a bigger disk space. Change it in MySQL's config file.

Basically your server is running out of disk space where /tmp is located.

I got this issue after changing the location of the mysql data dir, because the fs was out of space.. so just asudo rm -rf /var/lib/mysql/ibdata1 did it for me. Thanks!!! — mimoralea, Feb 24 at 21:24

OmnipotentEntity · Answer 3 · 2013-04-12 22:25:08Z

You'll need to run this command from the MySQL prompt:

REPAIR TABLE tbl_name USE_FRM;

From MySQL's documentation on the Repair command:

The USE_FRM option is available for use if the .MYI index file is missing or if its header is corrupted. This option tells MySQL not to trust the information in the .MYI file header and to re-create it using information from the .frm file. This kind of repair cannot be done with myisamchk.

Thanks, fixed my table. But actually it should be REPAIR TABLE tbl_name USE_FRM — Koen., Feb 14 '12 at 20:42

Martin · Answer 4 · 2010-03-12 06:58:45Z

Your query is generating a result set so large that it needs to build a temporary table either to hold some of the results or some intermediate product used in generating the result.

The temporary table is being generated in /var/tmp. This temporary table would appear to have been corrupted. Perhaps the device the temporary table was being built on ran out of space. However, usually this would normally result in an "out of space" error. Perhaps something else running on your machine has clobbered the temporary table.

Try reworking your query to use less space, or try reconfiguring your database so that a larger or safer partition is used for temporary tables.

MySQL Manual - B.5.4.4. Where MySQL Stores Temporary Files

juergen d · Accepted Answer · 2014-12-15 13:04:10Z

Apply proper charset and collation to database, table and columns/fields.

I creates database and table structure using sql queries from one server to another. it creates database structure as follows:

database with charset of "utf8", collation of "utf8_general_ci"
tables with charset of "utf8" and collation of "utf8_bin".
table columns / fields have charset "utf8" and collation of "utf8_bin".

I change collation of table and column to utf8_general_ci, and it resolves the error.

来自 http://stackoverflow.com/questions/2428738/how-do-you-fix-a-mysql-incorrect-key-file-error-when-you-...

MySQL, Error 126: Incorrect key file for table

up vote9down vote favorite

1

I read the following question that has relevance, but the replies didn't satify me: MySQL: #126 - Incorrect key file for table

The problem

When running a query I get this error

ERROR 126 (HY000): Incorrect key file for table`

The question

When I'm trying to find the problem I cant't find one, so I don't know how to fix it with the repair command. Is there any pointers to how I can find the problem causing this issue in any other way then I already have tried?

The query

mysql>       SELECT
    ->         Process.processId,
    ->         Domain.id AS domainId,
    ->         Domain.host,
    ->         Process.started,
    ->         COUNT(DISTINCT Joppli.id) AS countedObjects,
    ->         COUNT(DISTINCT Page.id)   AS countedPages,
    ->         COUNT(DISTINCT Rule.id)   AS countedRules
    ->       FROM Domain
    ->         JOIN CustomScrapingRule
    ->           AS Rule
    ->           ON Rule.Domain_id = Domain.id
    ->           LEFT JOIN StructuredData_Joppli
    ->             AS Joppli
    ->             ON Joppli.CustomScrapingRule_id = Rule.id
    ->         LEFT JOIN Domain_Page
    ->           AS Page
    ->           ON Page.Domain_id = Domain.id
    ->         LEFT JOIN Domain_Process
    ->           AS Process
    ->           ON Process.Domain_id = Domain.id
    ->       WHERE Rule.CustomScrapingRule_id IS NULL
    ->       GROUP BY Domain.id
    ->       ORDER BY Domain.host;
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_2b5_4.MYI'; try to repair it

mysqlcheck

root@scraper:~# mysqlcheck -p scraper
Enter password: 
scraper.CustomScrapingRule                         OK
scraper.Domain                                     OK
scraper.Domain_Page                                OK
scraper.Domain_Page_Rank                           OK
scraper.Domain_Process                             OK
scraper.Log                                        OK
scraper.StructuredData_Joppli                      OK
scraper.StructuredData_Joppli_Product              OK

counted rows

mysql> select count(*) from CustomScrapingRule;
+----------+
| count(*) |
+----------+
|       26 |
+----------+
1 row in set (0.04 sec)

mysql> select count(*) from Domain;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.01 sec)

mysql> select count(*) from Domain_Page;
+----------+
| count(*) |
+----------+
|   134288 |
+----------+
1 row in set (0.17 sec)

mysql> select count(*) from Domain_Page_Rank;
+----------+
| count(*) |
+----------+
|  4671111 |
+----------+
1 row in set (11.69 sec)

mysql> select count(*) from Domain_Process;
+----------+
| count(*) |
+----------+
|        2 |
+----------+
1 row in set (0.02 sec)

mysql> select count(*) from Log;
+----------+
| count(*) |
+----------+
|       41 |
+----------+
1 row in set (0.00 sec)

mysql> select count(*) from StructuredData_Joppli;
+----------+
| count(*) |
+----------+
|    11433 |
+----------+
1 row in set (0.16 sec)

mysql> select count(*) from StructuredData_Joppli_Product;
+----------+
| count(*) |
+----------+
|   130784 |
+----------+
1 row in set (0.20 sec)

Update

Disk usage

root@scraper:/tmp# df -h
Filesystem      Size  Used Avail Use% Mounted on
/dev/xvda1       20G  4.7G   15G  26% /
none            4.0K     0  4.0K   0% /sys/fs/cgroup
udev            237M  4.0K  237M   1% /dev
tmpfs            49M  188K   49M   1% /run
none            5.0M     0  5.0M   0% /run/lock
none            245M     0  245M   0% /run/shm
none            100M     0  100M   0% /run/user

edited Dec 15 '14 at 13:04

juergen d
97.1k983132

asked Sep 25 '13 at 11:00

Erik Landvall
2,06521749

2

what about the disk space issue? – Sebas Sep 25 '13 at 11:02

500GB hd space, no blobs in db, I don't see how this could be the issue. It really isn't that big av an database for the moment. The project is just in testing so we have a lot more resources then currently needed. Although, I will look it over and update with the information – Erik Landvall Sep 25 '13 at 11:07

@Sebas Though I was wrong about the current disk size we should still have an ok margin I believe.. – Erik Landvall Sep 25 '13 at 11:19

add a comment

2 Answers

active oldest votes

up vote11down voteaccepted

It appears that your query is returning a large intermediate result set requiring the creation of a temporary table and that the configured location for mysql temporary disk tables (/tmp) is not large enough for the resulting temporary table.

You could try increasing the tmpfs partition size by remounting it:

mount -t tmpfs -o remount,size=1G tmpfs /tmp

You can make this change permanent by editing /etc/fstab

If you are unable to do this you could try changing the location of disk temporary tables by editing the "tmpdir" entry in your my.cnf file (or add it if it is not already there). Remember that the directory you choose should be writable by the mysql user

You could also try preventing the creation of an on disk temporary table by increasing the values for the mysql configuration options:

tmp_table_size
max_heap_table_size

to larger values. You will need to increase both of the above parameters

Example:

set global tmp_table_size = 1G;
set global max_heap_table_size = 1G;

edited Oct 14 '13 at 18:50

answered Oct 10 '13 at 12:46

Cillier
56327

It should also be mentioned that the query in this case should be formatted in a different way to be able to avoid this issue. It was a lot faster to use multiple queries in this case. – Erik Landvall Oct 14 '13 at 11:50

add a comment

up vote-2down vote

You just need to repair your table which use in search query. this problem generally occur on search query.

go to "table_name" -> operation- > repair (just one click) effect may take some time to apply

answered Feb 6 at 9:42

Kaushal Sachan
71

He didn't mention using any gui for mysql and you describe steps to be performed on some gui obviously (since you say it's "just one click"). – Dimitris Zorbas Feb 9 at 15:43

add a comment

Cillier · Accepted Answer · 2013-10-14 18:50:58Z

It appears that your query is returning a large intermediate result set requiring the creation of a temporary table and that the configured location for mysql temporary disk tables (/tmp) is not large enough for the resulting temporary table.

You could try increasing the tmpfs partition size by remounting it:

mount -t tmpfs -o remount,size=1G tmpfs /tmp

You can make this change permanent by editing /etc/fstab

If you are unable to do this you could try changing the location of disk temporary tables by editing the "tmpdir" entry in your my.cnf file (or add it if it is not already there). Remember that the directory you choose should be writable by the mysql user

You could also try preventing the creation of an on disk temporary table by increasing the values for the mysql configuration options:

tmp_table_size
max_heap_table_size

to larger values. You will need to increase both of the above parameters

Example:

set global tmp_table_size = 1G;
set global max_heap_table_size = 1G;

It should also be mentioned that the query in this case should be formatted in a different way to be able to avoid this issue. It was a lot faster to use multiple queries in this case. — Erik Landvall, Oct 14 '13 at 11:50

Kaushal Sachan · Answer 7 · 2015-02-06 09:42:00Z

You just need to repair your table which use in search query. this problem generally occur on search query.

go to "table_name" -> operation- > repair (just one click) effect may take some time to apply

He didn't mention using any gui for mysql and you describe steps to be performed on some gui obviously (since you say it's "just one click"). — Dimitris Zorbas, Feb 9 at 15:43

You are here

MySQL, Error 126: Incorrect key file for table

How do you fix a MySQL “Incorrect key file” error when you can't repair the table?

5 Answers

MySQL, Error 126: Incorrect key file for table

The problem

The question

The query

mysqlcheck

counted rows

Update

Disk usage

2 Answers

Your Answer

友情链接

搜索表单

用户登录

You are here

MySQL, Error 126: Incorrect key file for table

How do you fix a MySQL “Incorrect key file” error when you can't repair the table?

5 Answers

MySQL, Error 126: Incorrect key file for table

The problem

The question

The query

mysqlcheck

counted rows

Update

Disk usage

2 Answers

Your Answer

友情链接