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".

Portions of this website are copyright © 2001, 2002 The PHP Group

Page generated in 0.040 sec. using MySQL 8.4.7-u6-cloud

Timestamp references displayed by the system are UTC.
Content reproduced on this site is the property of the respective copyright holders.
It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.