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.
