"Burke, Dan" <[EMAIL PROTECTED]> 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
