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:
Here is a screen shot of the table structure of the destination table of the stored procedure I'm trying to call:
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.