Dan,
>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:
|CREATE TABLE|, |DELETE|, |DO|, |INSERT|, |REPLACE|, |SELECT|, |SET|,
|UPDATE|, and most |SHOW| statements. Other statements are not yet
supported."
(http://dev.mysql.com/doc/refman/5.0/en/sqlps.html)
PB
-----
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
> 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 ;
>
>
>
Attachment: [text/html]
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.375 / Virus Database: 267.14.20/233 - Release Date: 1/18/2006
