Mysql – Incorrect key file for table error. How to change location of MySQL’s temp folder to another part of drive with more disk space

errorsMySQLstored-procedurestemporary-tables

I am using MySQL with Sequel Pro on a Mac Pro OSX Mavericks and am trying to run the code for a stored procedure in order to add two new columns to a table. The procedure does math operations on columns from two different tables, one of which is the same table as the destination/output table 'starting_pitcher_stats'.

I keep getting the following error when I call the procedure:

Incorrect key file for table '/var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/#sql43_1_82.MYI'; try to repair it

Here is the code for the procedure:

DROP PROCEDURE IF EXISTS starting_pitcher_std_wRC;
    DELIMITER $$
    CREATE PROCEDURE starting_pitcher_std_wRC()
    BEGIN
        DECLARE pit_id CHAR(10);
        DECLARE gdate DATE;
        DECLARE seq INT;
        DECLARE YEARID INT;
        DECLARE wOBAsc VARCHAR (255);
        DECLARE stdwOBA DOUBLE;
        DECLARE wOBAgame DOUBLE;
        DECLARE lgwOBAgame DOUBLE;
        DECLARE lgstdwOBA DOUBLE;
        DECLARE lgRoverPAg DOUBLE;
        DECLARE stdlgRoverPA DOUBLE;
        DECLARE stdwRC DOUBLE;
        DECLARE wRCg DOUBLE;
        DECLARE prev_year YEAR(4);
        DECLARE end_of_cursor BOOLEAN;

        DECLARE no_table CONDITION FOR SQLSTATE '42S02';

        DECLARE c1 CURSOR FOR
          SELECT s.Starting_Pitcher, s.Game_Date, s.Game_Number, s.YEAR_ID,
                 s.wOBAScale, s.std_wOBA, s.wOBA_game, l.lg_wOBA_game,
                 l.lg_std_wOBA, l.lg_RoverPA_g, l.std_lg_RoverPA,
                 s.wRC_g, s.std_wRC 
            FROM starting_pitcher_stats AS s,
                 lg_starting_pitcher_game_log AS l
           ORDER BY s.Starting_Pitcher, s.Game_Date, s.Game_Number;

        DECLARE CONTINUE HANDLER FOR NOT FOUND
          SET end_of_cursor := TRUE;

        SET end_of_cursor := FALSE;  -- reset
        SET prev_year := 0;          -- reset control-break

        OPEN c1;

        fetch_loop: LOOP
          FETCH c1 INTO pit_id, gdate, seq, YEARID, wOBAsc, stdwOBA,
                wOBAgame, lgwOBAgame, lgstdwOBA, lgRoverPAg, stdlgRoverPA,
                wRCg, stdwRC;
          IF end_of_cursor THEN
            LEAVE fetch_loop;
          END IF;

          -- check control-break conditions
          IF YEAR(gdate) != prev_year THEN
            SET stdwRC := 0;
            SET wRCg := 0;
            SET prev_year := YEAR(gdate);
          END IF;

            SET wRCg := (((wOBAgame - lgwOBAgame)/wOBASc)+(lgRoverPAg))*BFPgame;
            SET stdwRC := (((stdwOBA - lgstdwOBA)/wOBASc)+(stdlgRoverPA))*stdBFP;

          UPDATE starting_pitcher_stats
            SET s.std_wRC =stdwRC,
            s.wRC_g =wRCg
              WHERE s.YEAR_ID=YEARID
              AND s.Game_date=gdate
              AND s.Game_Number=seq
              AND s.Starting_Pitcher = pit_id;

        END LOOP;
        CLOSE c1;
      END
      $$

  DELIMITER ;

Following the error I get all Null values in the two new columns I am trying to create.

I did see responses to similar posts with a similar error each addressing different possible causes for the error, but it's unclear which problem type inspired my error.

I'm hoping I just need to change the location of MySQL's temporary folder to a location with more space as one person recommends in the following thread. They say I need to change it in MySQL's config file, but not sure how to do it:

https://stackoverflow.com/questions/2428738/how-do-you-fix-a-mysql-incorrect-key-file-error-when-you-cant-repair-the-tabl

Here is a screen shot of the table structure of the destination table of the stored procedure I'm trying to call:

enter image description here

Can someone please point me in the right direction of how I can repair the folder or remedy the problem? If you need more information, please let me know so I can provide it.

Thank you in advance.

UPDATE:
when I run df -h to determine size and disk space used in the entire mounted file system if disk space is a problem as previous posts often suggest is the source of the error I'm getting, this is the result:

Filesystem      Size   Used  Avail Capacity  iused     ifree %iused  Mounted on
/dev/disk2s2   298Gi  200Gi   97Gi    68% 52622616  25436210   67%   /
devfs          187Ki  187Ki    0Bi   100%      646         0  100%   /dev
/dev/disk0s2   149Gi  288Mi  148Gi     1%    73766  38914980    0%   /Volumes/Disk 2                       
map -hosts       0Bi    0Bi    0Bi   100%        0         0  100%   /net
map auto_home    0Bi    0Bi    0Bi   100%        0         0  100%   /home
/dev/disk1s2   931Gi  254Gi  677Gi    28% 66541860 177564806   27%   /Volumes/DATASTORE_Z

when I do the same command with the path listed in the error (excluding the .MYI file, because it says access denied when I do include it) it shows that the folder(directory?) in question is occupying is 68% full:

df -h /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/: 

Filesystem     Size   Used  Avail Capacity  iused    ifree %iused  Mounted on
/dev/disk2s2  298Gi  201Gi   97Gi    68% 52624601 25434225   67%   /

If it's true that the temporary files produced in executing my above stored procedure exceed the amount of space available, would it make sense to move /var/folders/zz/zyxvpxvq6csfxvn_n000009800002_/T/ to another file system with more space like /dev/disk1s2?

If so, would it be as easy as using mv to do so? I'm being cautious here because I don't want to cause more problems by doing so given that the problem here may not be what I think it is.

Thank you in advance for any feedback on the above and what I should do to resolve the problem.

Best Answer

Change tmpdir; see http://dev.mysql.com/doc/refman/5.7/en/temporary-files.html

You should move from MyISAM to InnoDB.

You can probably do the entire stored procedure in a single "multi-table UPDATE" statement. (Cursors are not efficient.)

But, perhaps most importantly, switch from FROM a,b to FROM a JOIN b ON ... at which point you will realize that you have a "cross join" because the ON condition is missing. This probably lead to a huge temp table. If each table were 10K rows long, the tmp table would have 100M rows!

Related Question