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

[18 May 2007 4:17] huang chuanwen
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
[18 May 2007 4:24] huang chuanwen
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))!!
[18 May 2007 8:01] Sveta Smirnova
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.
[18 Jun 2007 23:00] Bugs System
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".