Bug #3338 Stored procedure executes correctly only first time after creation

• Submitted: 30 Mar 2004 16:15
• Modified: 20 May 2004 9:17
• Reporter: Fredrik Hedberg
• Status: Duplicate
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 5.0.0-alpha-standard
• OS: Linux (Debian 'testing' GNU/Linux 2.6.3)
• Assigned to: Assigned Account
• CPU Architecture: Any

[30 Mar 2004 16:15] Fredrik Hedberg

Description:
Stored procedure executes correctly only first time after creation.

How to repeat:
Stored procedure

CREATE PROCEDURE getBackendId (location VARCHAR(255),parent BIGINT,OUT backendId VARCHAR(255))
		BEGIN
     		SELECT objectId FROM backendStructure WHERE 
     			(objectName = SUBSTRING(location,2,LOCATE('/',location,2)-2)) AND 
     			(objectParent = parent) INTO backendId;
		END

Data

DROP TABLE IF EXISTS backendStructure;
CREATE TABLE backendStructure (
  objectId bigint(20) NOT NULL auto_increment,
  objectName varchar(255) NOT NULL default '',
  objectParent bigint(20) NOT NULL default '0',
  objectType smallint(3) NOT NULL default '0',
  PRIMARY KEY  (objectId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
 
--
-- Dumping data for table `backendStructure`
--
 
 
/*!40000 ALTER TABLE backendStructure DISABLE KEYS */;
LOCK TABLES backendStructure WRITE;
INSERT INTO backendStructure VALUES (1,'home',0,0),(2,'opt',0,0),(3,'usr',0,0),(4,'etc',0,0);
UNLOCK TABLES;
/*!40000 ALTER TABLE backendStructure ENABLE KEYS */;
 
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;

Called by:

CALL getBackendId('/home',0,@backendId);
SELECT @backendId;

Should return 1;

Suggested fix:
?

[20 May 2004 9:17] Oleksandr Byelkin

Thank you for bugreport. This bug have same cause as Bug #2266. Patch from 
that bug fix this problem. Please, use that bugrecord to track progress of 
bugfixing. 
 
Here is result which I get on your test case on server fixed for bug Bug 
#2266: 
 
+ CREATE PROCEDURE getBackendId (location VARCHAR(255),parent BIGINT,OUT 
backendId 
+ VARCHAR(255)) 
+ BEGIN 
+ SELECT objectId FROM backendStructure WHERE 
+ (objectName = SUBSTRING(location,2,LOCATE('/',location,2)-2)) AND 
+ (objectParent = parent) INTO backendId; 
+ END| 
+ DROP TABLE IF EXISTS backendStructure| 
+ Warnings: 
+ Note  1051    Unknown table 'backendStructure' 
+ CREATE TABLE backendStructure ( 
+ objectId bigint(20) NOT NULL auto_increment, 
+ objectName varchar(255) NOT NULL default '', 
+ objectParent bigint(20) NOT NULL default '0', 
+ objectType smallint(3) NOT NULL default '0', 
+ PRIMARY KEY  (objectId) 
+ ) ENGINE=InnoDB DEFAULT CHARSET=latin1| 
+ INSERT INTO backendStructure VALUES 
+ (1,'home',0,0),(2,'opt',0,0),(3,'usr',0,0),(4,'etc',0,0)| 
+ CALL getBackendId('/home/',0,@backendId)| 
+ SELECT @backendId| 
+ @backendId 
+ 1 
+ CALL getBackendId('/home/',0,@backendId)| 
+ SELECT @backendId| 
+ @backendId 
+ 1 
+ CALL getBackendId('/home',0,@backendId)| 
+ Warnings: 
+ Warning       1322    No data to FETCH 
+ SELECT @backendId| 
+ @backendId 
+ NULL 
+ CALL getBackendId('/home',0,@backendId)| 
+ Warnings: 
+ Warning       1322    No data to FETCH 
+ SELECT @backendId| 
+ @backendId 
+ NULL