3

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.

0

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!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.