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 ;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
