ERROR 126 (HY000): Incorrect key file for table

--=_6ks601tq6o74
Content-Type: text/plain;
charset=UTF-8
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Hi All,

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=`myuser`@`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


--=_6ks601tq6o74
Content-Type: multipart/related;
boundary="=_6ruv35ks1bsw";
start="6rw2bzlq38xs@mail.artificialmachines.com"
Content-Transfer-Encoding: 7bit

This message is in MIME format.

--=_6ruv35ks1bsw
Content-Type: text/html;
charset=UTF-8
Content-Description: HTML Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-ID: 6rw2bzlq38xs@mail.artificialmachines.com

Hi All,

 

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=`myuser`@`localhost` PROCEDURE `SP_GetLogs`(InputToDate Date,
InputFromDate Date, InputFrom int, InputTo int)

    DETERMINISTIC

BEGIN

Re: ERROR 126 (HY000): Incorrect key file for table

This kind of error usually means you ran out of disk space on your
tmp_dir drive.

Walter

On Mon, May 31, 2010 at 13:30, Manasi Save
wrote:
> Hi All,
>
> 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'; t=
ry
> to repair it
>
> How to debug this error? I mean I tried to increase tmp_table_Size parame=
ter
> but, it does not affect.
>
> Proc is as follows :-
>
>
> CREATE DEFINER=3D`myuser`@`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 =3D Concat('Insert into norep_Temp(MinFinalXML)
> Select distinct FinalXML
> From ClientLog
> Where TaskName =3D ','"','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 =3D Concat('Insert into norep_Temp(MaxFinalXML)
> Select Distinct FinalXML
> From ClientLog
> Where TaskName =3D ','"','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 =3D 'Tim=
e
> required to complete task';
> Select Count(*) into CountOfXML From ClientLog Where TaskName =3D '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 no=
rep_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 =3D Concat('Insert into Temp(MinVal)
> Select distinct Val
> From MyLogs
> Where TaskName =3D ','"','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 =3D Concat('Insert into Temp(MaxVal)
> Select Distinct Val
> From MyLogs
> Where TaskName =3D ','"','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 =3D 'Time required=
to
> complete task';
>
> Select Count(*) into Count From MyLogs Where TaskName =3D 'Time required =
to
> complete task';
>
>  Select Count, MinVal, MaxVal, AvgVal  From Temp;
>
> END;
>
> --
> Regards,
> Manasi Save
>
>
>
>

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: ERROR 126 (HY000): Incorrect key file for table

Dear Walter,
 
Thanks for quick response.
 
But When I try to remove my tmp files related to mysql. I am not
allowed to remove them. How should I make more space in that case.
 
and We have allocated 2GB space for /tmp dir. and current size of
folder is showing as 60M. Still I am getting this error.
 
Any specific reason this could happen. Any other possible scenario.
 
Any input will be a great help. 
 
Thanks in advance.

--
Regards,
Manasi Save

On Mon, 31 May 2010 14:29:16 0200, "Walter Heck - OlinData.com" wrote:
This kind of error usually means you ran out of disk space on your
> tmp_dir drive.
>
> Walter
>
> On Mon, May 31, 2010 at 13:30, Manasi Save
> wrote:
> > Hi All,
> >
> > 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=3D`myuser`@`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 =3D Concat('Insert into norep_Temp(MinFinalXML)
> > Select distinct FinalXML
> > From ClientLog
> > Where TaskName =3D ','"','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 =3D Concat('Insert into norep_Temp(MaxFinalXML)
> > Select Distinct FinalXML
> > From ClientLog
> > Where TaskName =3D ','"','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 =3D =
'Time
> > required to complete task';
> > Select Count(*) into CountOfXML From ClientLog Where TaskName =3D 'Ti=
me
> > 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  Fro=
m
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 =3D Concat('Insert into Temp(MinVal)
> > Select distinct Val
> > From MyLogs
> > Where TaskName =3D ','"','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 =3D Concat('Insert into Temp(MaxVal)
> > Select Distinct Val
> > From MyLogs
> > Where TaskName =3D ','"','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 =3D 'Time
required to
> > complete task';
> >
> > Select Count(*) into Count From MyLogs Where TaskName =3D 'Time requi=
red to
> > complete task';
> >
> >  Select Count, MinVal, MaxVal, AvgVal  From Temp;
> >
> > END;
> >
> > --
> > Regards,
> > Manasi Save
> >
> >
> >
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
http://lists.mysql.com/mysql?unsub=3Dmanasi.save@artificialm achines.com
>
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg