>I tried using prepared statements to make a procedure that
>converts every table in a schema from ARCHIVE to MyISAM...
"The following SQL statements can be used in prepared statements:
SHOW statements. Other statements
are not yet supported."
Burke, Dan wrote:
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
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;
create procedure CONVERT_TABLES()
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
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
FETCH cur1 INTO t;
IF NOT done THEN
set @x = CONCAT('alter table ', t, ' engine =
prepare z from @x;
deallocate prepare z;
UNTIL done END REPEAT;