List:General Discussion« Previous MessageNext Message »
From:SGreen Date:January 18 2006 7:15pm
Subject:Re: Procedure / Prepared statements error converting table
View as plain text  
"Burke, Dan" <dburke@stripped> wrote on 01/18/2006 02:05:24 PM:

> 
> I tried using prepared statements to make a procedure that converts
> every table in a schema from ARCHIVE to MyISAM (there's about three
> dozen archive tables here).  But for some odd reason it will give an
> error after doing the first table, and abort.  The really odd thing is
> that the table it gives the error on does successfully get converted.
> 
> Any thoughts?  Is there a better way to do this?  Or am I just doing
> something stupid?
> 
> This is the error directly below... and the full procedure below that.
> 
> 
> ERROR 7 (HY000): Error on rename of './CANN_ARCH/#sql-27b4_275.ARN' to
> './CANN_ARCH/WEBLOG_2004.ARN' (Errcode: 2)
> 
> 
> 
> drop procedure if exists CONVERT_TABLES;
> delimiter //
> create procedure CONVERT_TABLES()
> begin
>         DECLARE t char(255);
>         DECLARE done INT DEFAULT 0;
>         DECLARE cur1 CURSOR for SELECT TABLE_NAME from
> information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and ENGINE
> = 'MyISAM';
>         DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
> 
>         OPEN cur1;
> 
>         REPEAT
>                 FETCH cur1 INTO t;
>                 IF NOT done THEN
>                         set @x = CONCAT('alter table ', t, ' engine =
> myisam');
>                         prepare z from @x;
>                         execute z;
>                         deallocate prepare z;
> 
>                 END IF;
> 
>         UNTIL done END REPEAT;
> 
>         CLOSE cur1;
> 
> END; //
> delimiter ;
> 
> 

Maybe you described it backwards?  According to your cursor definition, 
you are looking for tables of type MyISAM. Perhaps your cursor should be 
looking for ENGINE='Archive' ?

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Thread
Procedure / Prepared statements error converting tableDan Burke18 Jan
  • Re: Procedure / Prepared statements error converting tableSGreen18 Jan
    • Help in joining three tablesImran18 Jan
      • Re: Help in joining three tablesSGreen18 Jan
      • Re: Help in joining three tablesgerald_clark18 Jan
      • Re: Help in joining three tablesRhino18 Jan
        • Re: Help in joining three tablesSGreen18 Jan
          • Re: Help in joining three tablesRhino18 Jan
  • Re: Procedure / Prepared statements error converting tablePeter Brawley18 Jan
RE: Procedure / Prepared statements error converting tableDan Burke18 Jan