List:General Discussion« Previous MessageNext Message »
From:Moon's Father Date:February 1 2008 9:24am
Subject:Alter statement can not be processed correctly in prepare statment
View as plain text  
Here is my procedure body:
DELIMITER $$

DROP PROCEDURE IF EXISTS `db3307`.`sp_alter_engine`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_alter_engine`(
 IN f_db_name varchar(255),IN f_tb_name varchar(255),
 IN f_engine_name varchar(255))
BEGIN
  -- Get the total number of tables.
  declare cnt1 int default 0;
  declare i int;
  set i = 0;
  if char_length(f_tb_name) = 0 then
    select count(1) from information_schema.tables where table_schema =
f_db_name and `engine` != f_engine_name into cnt1;
    while i < cnt1
    do
      set @stmt = concat('select @tbname:=table_name from
information_schema.tables where table_schema=''',f_db_name,''' and `engine`
!= ''',f_engine_name,''' order by table_name desc limit ',i,',1');
      prepare s1 from @stmt;
      execute s1;
      deallocate prepare s1;
      set @stmt = '';
      set @stmt = concat('alter table ',f_db_name,'.',@tbname,'
engine=',f_engine_name);
      prepare s1 from @stmt;
      execute s1;
      deallocate prepare s1;
      set @stmt = '';
      set i = i + 1;
    end while;
  else
    set @stmt = concat('alter table ',f_db_name,'.',f_tb_name,'
engine=',f_engine_name);
    prepare s1 from @stmt;
    execute s1;
    deallocate prepare s1;
    set @stmt = '';
    set @stmt = concat('select ''',f_db_name,'.',f_tb_name,' was changed
successfully!'' as result');
    prepare s1 from @stmt;
    execute s1;
    drop prepare s1;
    set @stmt = '';
  end if;
END$$

DELIMITER ;


All my test is as follows:

mysql> select `engine` from tables where table_schema='db3307' and `engine`
!= 'db3307' order by table_name desc;
+--------+
| engine |
+--------+
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
| MyISAM |
+--------+
37 rows in set (0.00 sec)

mysql> call sp_alter_engine('db3307','','innodb');
ERROR 1305 (42000): PROCEDURE information_schema.sp_alter_engine does not
exist
mysql> use db3307;call sp_alter_engine('db3307','','innodb');
Database changed
+-----------------------+
| @tbname:=table_name   |
+-----------------------+
| cs_site_sensitive_key |
+-----------------------+
1 row in set (0.00 sec)

...
+---------------------+
| @tbname:=table_name |
+---------------------+
| cs_char2letter      |
+---------------------+
1 row in set (0.00 sec)

Empty set (0.16 sec)

Empty set (0.17 sec)

Empty set (0.17 sec)

Empty set (0.18 sec)

Empty set (0.18 sec)

Empty set (0.19 sec)

Empty set (0.19 sec)

Empty set (0.20 sec)

Empty set (0.20 sec)

Empty set (0.21 sec)

Empty set (0.21 sec)

Empty set (0.22 sec)

Empty set (0.23 sec)

Empty set (0.23 sec)

Empty set (0.24 sec)

Empty set (0.24 sec)

Empty set (0.25 sec)

Empty set (0.25 sec)

Query OK, 0 rows affected (0.26 sec)
mysql> select `engine` from information_schema.tables where
table_schema='db3307' and `engine` != 'db3307' order by table_name desc;
+--------+
| engine |
+--------+
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
| MyISAM |
| InnoDB |
+--------+
37 rows in set (0.00 sec)

mysql>


So my question is why some table can not be changed?
Any appreciated will be thanks.
-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

Thread
Alter statement can not be processed correctly in prepare statmentMoon's Father1 Feb