List:General Discussion« Previous MessageNext Message »
From:Dan Burke Date:January 18 2006 7:05pm
Subject:Procedure / Prepared statements error converting table
View as plain text  
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 ;

Thread
Procedure / Prepared statements error converting tableDan Burke18 Jan
  • Re: Procedure / Prepared statements error converting tableSGreen18 Jan
    • Help in joining three tablesImran18 Jan
      • Re: Help in joining three tablesSGreen18 Jan
      • Re: Help in joining three tablesgerald_clark18 Jan
      • Re: Help in joining three tablesRhino18 Jan
        • Re: Help in joining three tablesSGreen18 Jan
          • Re: Help in joining three tablesRhino18 Jan
  • Re: Procedure / Prepared statements error converting tablePeter Brawley18 Jan
RE: Procedure / Prepared statements error converting tableDan Burke18 Jan