I am getting following error when I am trying to run one stored procedure on table which has 300000 rows in it. Table Type id MyIsAM.
ERROR 126 (HY000): Incorrect key file for table '/tmp/#sql_66b0_0.MYI'; try to repair it
How to debug this error? I mean I tried to increase tmp_table_Size parameter but, it does not affect.
Proc is as follows :-
CREATE definer=`myus...@`localhost` PROCEDURE `SP_GetLogs`(InputToDate Date, InputFromDate Date, InputFrom int, InputTo int)
DETERMINISTIC
BEGIN
Declare Count int;
Declare AvgVal float(10,2);
Drop Temporary Table
If Exists norep_Temp;
Create Temporary
Table norep_Temp
(
-- CountOfXML
int,
TaskName
Varchar(100),
MinFinalXML Varchar(10000),
MaxFinalXML Varchar(10000) -- ,
-- AvgOfXML
float(10,2)
);
If (InputToDate IS
NULL and InputFromDate IS NULL)
Then
SET @stmt =
Concat('Insert into norep_Temp(MinFinalXML)
Select distinct
FinalXML
From ClientLog
Where TaskName =
','"','Time required to complete task','"','
and FinalXML Between
1 and 10
Order By FinalXML ASC
LIMIT ',InputFrom,',',InputTo,';');
Select @stmt;
Prepare stmt1 From
@stmt;
Execute stmt1;
Deallocate Prepare
stmt1;
SET @stmt =
Concat('Insert into norep_Temp(MaxFinalXML)
Select Distinct
FinalXML
From ClientLog
Where TaskName =
','"','Time required to complete task','"','
Order By FinalXML
DESC LIMIT ',InputFrom,',',InputTo,';');
Prepare stmt1 From
@stmt;
Execute stmt1;
Deallocate prepare
stmt1;
Select AVG(FinalXML)
into AvgOfXML From ClientLog Where TaskName = 'Time required to complete
task';
Select Count(*) into
CountOfXML From ClientLog Where TaskName = 'Time required to complete
task';
-- Select Count(*) as
CountOfXML, MIN(MinFinalXML), MAX(MaxFinalXML), AvgOfXML From norep_Temp Order
By MinFinalXML, MaxFinalXML Limit 0,10;
Select
CountOfXML, MinFinalXML, MaxFinalXML, AvgOfXML From norep_Temp;
Drop Temporary Table If Exists Temp;
Create Temporary Table Temp
(
TaskName Varchar(100),
MinVal Varchar(100),
MaxVal Varchar(100) -- ,
);
If (InputToDate IS NULL and InputFromDate IS NULL)
Then
SET @stmt = Concat('Insert into Temp(MinVal)
Select distinct Val
>From MyLogs
Where TaskName = ','"','Time required to complete task','"','
and Val Between 1 and 10
Order By Val ASC LIMIT ',InputFrom,',',InputTo,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate Prepare stmt1;
SET @stmt = Concat('Insert into Temp(MaxVal)
Select Distinct Val
>From MyLogs
Where TaskName = ','"','Time required to complete task','"','
Order By Val DESC LIMIT ',InputFrom,',',InputTo,';');
Prepare stmt1 From @stmt;
Execute stmt1;
Deallocate prepare stmt1;
Select AVG(Val) into AvgVal From MyLogs Where TaskName = 'Time required to complete task';
Select Count(*) into Count From MyLogs Where TaskName = 'Time required to complete task';
Select Count, MinVal, MaxVal, AvgVal From Temp;
END;
--
Regards,
Manasi Save
