MySQL Bugs: #28507: procedure bugs

Bug #28507	procedure bugs
Submitted:	18 May 2007 4:17	Modified:	18 Jun 2007 8:01
Reporter:	huang chuanwen	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.0.15	OS:	Linux
Assigned to:		CPU Architecture:	Any
Tags:	PROCEDURE

Description:
may be a bug of stored procedure

How to repeat:
CREATE PROCEDURE `updateClass`()
begin
        DECLARE done INT DEFAULT 0;
        declare cid,cnt int;
        declare cn varchar(255);
        DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
        DROP TABLE IF EXISTS wiki.memClass2;
        CREATE TABLE wiki.memClass2 (
          `ClassId` int(11) NOT NULL,
          `ClassName` varchar(255) NOT NULL,
          `count` int(11) NOT NULL,
          PRIMARY KEY  (`ClassId`),
          KEY `mccn` (`ClassName`(10))
        ) ENGINE=InnoDB DEFAULT CHARSET=gbk;

        drop table if exists wiki.memClass;
        alter table wiki.memClass2 rename wiki.memClass;
end

source this procedure to proc

use this php to call the procedure;

<?php
define('ROOT_PATH','../../');
define('WRITE_PAGE',1);
require_once(ROOT_PATH.'common-php/config.php');
require_once(ROOT_PATH.'common-php/global.php');
$num=0;
while(1){
    echo $num++;
$site_db->query("use proc");
$site_db->query("call updateClass()");
sleep(2);
}
$site_db->close();
?>

when the this php procedure is running, when the $num=6,call unpdateClass will echo  a Bad SQL Query: call updateClass()<br /><b>Can't create table
 './wiki/memClass2.frm' (errno: 139)

Suggested fix:
yes

show create table wiki.memClass
CREATE TABLE `memClass` (
  `ClassId` int(11) NOT NULL,
  `ClassName` varchar(8160) NOT NULL,
  `count` int(11) NOT NULL,
  PRIMARY KEY  (`ClassId`),
  KEY `mccn` (`ClassName`(320))
) ENGINE=InnoDB DEFAULT CHARSET=gbk

KEY `mccn` (`ClassName`(10)) is changed to KEY `mccn` (`ClassName`(320))!!

Thank you for the report.

But version 5.0.15 is quite old.

Please try with current 5.0.41 version and say us result.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".