Oh, I thought I had rolled all the changes back, I guess I missed that
line before posting. I had tried to see if it was because the tables
were ARCHIVE tables to being with, so I tried in another DB to convert a
bunch of tables the other way and got the same error. Either way, I'm
getting an error :-(
Correct procedure I'm trying to get working:
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
= 'ARCHIVE';
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 ;
________________________________
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Wednesday, January 18, 2006 2:16 PM
To: Burke, Dan
Cc: [email protected]
Subject: Re: Procedure / Prepared statements error converting table
"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)
>
>
>
> information_schema.TABLES where TABLE_SCHEMA = 'ARCH_TABLES' and
ENGINE
> = 'MyISAM';
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
