List:General Discussion« Previous MessageNext Message »
From:Dan Burke Date:January 18 2006 7:35pm
Subject:RE: Procedure / Prepared statements error converting table
View as plain text  
 

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: SGreen@stripped [mailto:SGreen@stripped] 
Sent: Wednesday, January 18, 2006 2:16 PM
To: Burke, Dan
Cc: mysql@stripped
Subject: Re: Procedure / Prepared statements error converting table

 



"Burke, Dan" <dburke@stripped> 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


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