| Bug #33395 | [ERROR] /usr/sbin/mysqld: Incorrect key file for table '/tmp/#sql_77d3_0.MYI'; | ||
|---|---|---|---|
| Submitted: | 20 Dec 2007 9:24 | Modified: | 17 Apr 2008 9:31 |
| Reporter: | Tobias Marx | Email Updates: | |
| Status: | No Feedback | Impact on me: | |
| Category: | MySQL Server: MyISAM storage engine | Severity: | S1 (Critical) |
| Version: | Ver 14.12 Distrib 5.0.51 | OS: | Linux (pc-linux-gnu (x86_64) ) |
| Assigned to: | CPU Architecture: | Any | |
| Tags: | Incorrect key file | ||
[20 Dec 2007 9:24]
Tobias Marx
[20 Dec 2007 9:25]
Tobias Marx
It is repeatable as it happens at every couple of minutes and at least once per hour.
[21 Dec 2007 17:21]
MySQL Verification Team
Thank you for the bug report. Could you please verify if you are running out of diskspace where MySQL stores temporary tables. Thanks in advance.
[31 Dec 2007 10:06]
Tobias Marx
Yes, it runs out of temporary disk space. my /tmp directory is a ramdisk otherwise it would be too slow. I have 8 Gigabytes of RAM and my /tmp dir is 4 Gigabytes. I have changed the my.cnf and disabled the temporary table cache and table cache. Still, this happens. I have now identified a view that is responsible for this. This leads to the following: ls -al /tmp total 1357792 -rw-rw---- 1 mysql mysql 497950720 Dec 31 10:04 #sql_6702_0.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_0.MYI -rw-rw---- 1 mysql mysql 489521152 Dec 31 10:04 #sql_6702_1.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_1.MYI -rw-rw---- 1 mysql mysql 400162816 Dec 31 10:04 #sql_6702_2.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_2.MYI ls -al /tmp total 3998164 -rw-rw---- 1 mysql mysql 1594097664 Dec 31 10:04 #sql_6702_0.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_0.MYI -rw-rw---- 1 mysql mysql 1695539200 Dec 31 10:04 #sql_6702_1.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_1.MYI -rw-rw---- 1 mysql mysql 636223488 Dec 31 10:04 #sql_6702_2.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_2.MYI -rw-rw---- 1 mysql mysql 160206848 Dec 31 10:04 #sql_6702_3.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_3.MYI ls -al /tmp total 2273380 -rw-rw---- 1 mysql mysql 2323382272 Dec 31 10:04 #sql_6702_0.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:04 #sql_6702_0.MYI drwxrwxrwt 4 root root 120 Dec 31 10:04 . drwxr-xr-x 22 root root 4096 Dec 19 08:30 .. drwxrwxrwt 2 root root 40 Dec 30 22:30 .ICE-unix drwxrwxrwt 2 root root 40 Dec 30 22:30 .X11-unix ls -al /tmp total 1164812 -rw-rw---- 1 mysql mysql 590348288 Dec 31 10:05 #sql_6702_0.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:05 #sql_6702_0.MYI -rw-rw---- 1 mysql mysql 516030464 Dec 31 10:05 #sql_6702_1.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:05 #sql_6702_1.MYI -rw-rw---- 1 mysql mysql 84017152 Dec 31 10:05 #sql_6702_2.MYD -rw-rw---- 1 mysql mysql 1024 Dec 31 10:05 #sql_6702_2.MYI drwxrwxrwt 4 root root 200 Dec 31 10:05 . drwxr-xr-x 22 root root 4096 Dec 19 08:30 .. drwxrwxrwt 2 root root 40 Dec 30 22:30 .ICE-unix drwxrwxrwt 2 root root 40 Dec 30 22:30 .X11-unix every couple of seconds it changes. Also: Filesystem Size Used Avail Use% Mounted on /dev/sda1 950M 107M 796M 12% / tmpfs 4.0G 4.0K 4.0G 1% /dev/shm /dev/sda5 4.7G 547M 4.2G 12% /usr /dev/sda6 4.7G 626M 4.1G 14% /var /dev/sda7 454G 162G 292G 36% /home none 4.0G 602M 3.4G 15% /tmp 1 second later: s15255624:~# df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 950M 107M 796M 12% / tmpfs 4.0G 4.0K 4.0G 1% /dev/shm /dev/sda5 4.7G 547M 4.2G 12% /usr /dev/sda6 4.7G 626M 4.1G 14% /var /dev/sda7 454G 162G 292G 36% /home none 4.0G 3.6G 374M 91% /tmp another second later: df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 950M 107M 796M 12% / tmpfs 4.0G 4.0K 4.0G 1% /dev/shm /dev/sda5 4.7G 547M 4.2G 12% /usr /dev/sda6 4.7G 626M 4.1G 14% /var /dev/sda7 454G 162G 292G 36% /home none 4.0G 4.0G 4.0K 100% /tmp another second later: df -h Filesystem Size Used Avail Use% Mounted on /dev/sda1 950M 107M 796M 12% / tmpfs 4.0G 4.0K 4.0G 1% /dev/shm /dev/sda5 4.7G 547M 4.2G 12% /usr /dev/sda6 4.7G 626M 4.1G 14% /var /dev/sda7 454G 162G 292G 36% /home none 4.0G 1.8G 2.2G 45% /tmp The view is: CREATE VIEW `viewLastPostsFrom` AS select `t`.`topic_id` AS `topic_id`,`t`.`forum_id` AS `forum_id`, `t`.`forum_domain` AS `forum_domain`,`t`.`topic_title` AS `topic_title`,`t`.`topic_title_url` AS `topic_title_url`, `t`.`topic_poster` AS `topic_poster`,`t`.`topic_time` AS `topic_time`,`t`.`topic_views` AS `topic_views`,`t`.`topic_replies` AS `topic_replies`,`t`.`topic_status` AS `topic_status`,`t`.`topic_vote` AS `topic_vote`,`t`.`topic_type` AS `topic_type`, `t`.`topic_first_post_id` AS `topic_first_post_id`,`t`.`topic_last_post_id` AS `topic_last_post_id`,`t`.`topic_moved_id` AS `topic_moved_id`,`f`.`cat_id` AS `cat_id`,`f`.`forum_name` AS `forum_name`,`f`.`forum_url` AS `forum_url` from (`forum_topics` `t` join `forum_forums` `f` on(((`t`.`forum_id` = `f`.`forum_id`) and (`f`.`forum_id` not in (5,29,45,59,63)))));
[31 Dec 2007 10:15]
Tobias Marx
I have also happened to see this: Table 'CHARACTER_SETS' is marked as crashed and should be repaired (in phpmyadmin when clicking on the view - but the second time it worked!)
[31 Dec 2007 10:16]
Tobias Marx
mysql.character_sets repair Error Table 'mysql.character_sets' doesn't exist mysql.character_sets repair error Corrupt
[1 Jan 2008 14:21]
Tobias Marx
Is there a way to "log" the queries/views that are responsible for those crashed tables? I would love to add a more detailled error case if anyone could point me into a direction that would identify the problem better.
[31 Jan 2008 22:19]
Sveta Smirnova
Thank you for the feedback. > Is there a way to "log" the queries/views that are responsible for those crashed tables? /tmp/#sql_XXX.MYI tables are temporary tables used to help execute a query. Most likely you reported not a bug and have problems because you really MySQL really needs to create such tables. Please read about EXPLAIN at http://dev.mysql.com/doc/refman/5.0/en/explain.html and if you still think this is because bug in MySQL code provide repeatable test case which we can copy and paste into mysql command line client to repeat the problem.
[1 Mar 2008 0:01]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
[17 Mar 2008 9:31]
Susanne Ebrecht
Tobias, we didn't get feedback from you. Can we close this bug report as not a bug or do you still have problems by following Svetas advices?
[17 Apr 2008 23:00]
Bugs System
No feedback was provided for this bug for over a month, so it is being suspended automatically. If you are able to provide the information that was originally requested, please do so and change the status of the bug back to "Open".
