Description:
I am having trouble restoring my stored procedures backed up with Administrator 1.1.9 on MySQL InnoDB 5.0.22.
How to repeat:
-- heres a sample of stored procs that I backed up using Administrator
--
-- Procedure `goolabri`.`InsertEvent`
--
DROP PROCEDURE IF EXISTS `InsertEvent`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertEvent`(xIDMaster INT, xIDUser INT, day1_ INT, day2_ INT, hour1_ INT, hour2_ INT)
BEGIN
DECLARE zNewID INT;
INSERT INTO `sale-events`(idguest, idemployee, datemade, arrive, depart)
VALUES(xIDMaster, xIDUser, CURDATE(), ADDDATE(ADDDATE(CURDATE(), INTERVAL day1_ DAY), INTERVAL hour1_ HOUR), ADDDATE(ADDDATE(CURDATE(), INTERVAL day2_ DAY), INTERVAL hour2_ HOUR));
SELECT LAST_INSERT_ID() INTO zNewID;
INSERT INTO `sale-glist`(idguest, idevent) VALUES(xIDMaster, zNewID);
SELECT zNewID;
END $$
DELIMITER ;
--
-- Procedure `goolabri`.`InsertGuest`
--
DROP PROCEDURE IF EXISTS `InsertGuest`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGuest`(IN xIDMaster INT, IN xIDEvent INT)
BEGIN
DECLARE zNewID INT;
INSERT INTO `sale-guests` (idref, idpostcode, firstname, lastname, ccdate) VALUES(xIDMaster, 555, LPAD((SELECT COUNT(idguest) +1 FROM `sale-glist` WHERE idevent = xIDEvent), 2, "0"), 'Guest', ADDDATE(CURDATE(), INTERVAL 1 YEAR));
SELECT LAST_INSERT_ID() INTO zNewID;
INSERT INTO `sale-glist`(idguest, idevent) VALUES(zNewID, xIDEvent);
SELECT zNewID;
END $$
DELIMITER ;
--
-- Procedure `goolabri`.`InsertMaster`
--
DROP PROCEDURE IF EXISTS `InsertMaster`;
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertMaster`()
BEGIN
DECLARE zNewID INT;
DECLARE zFirstName INT;
SELECT (SELECT count(id) +1 from `sale-guests` where id = idref) INTO zFirstName;
-- replace 4 queries
INSERT INTO `sale-guests` (idpostcode, firstname, lastname, dateadded, ccdate) VALUES('233', LPAD(zFirstName, 2, "0"), 'Master ', CURDATE(), ADDDATE(CURDATE(), INTERVAL 1 YEAR));
SELECT LAST_INSERT_ID() INTO zNewID;
UPDATE `sale-guests` SET idref = zNewID where id = zNewID;
SELECT zNewID;
END $$
Suggested fix:
1. Remove Definer crap from create proc line
2. Replace the semi colon on the Drop statement line with $$
Description: I am having trouble restoring my stored procedures backed up with Administrator 1.1.9 on MySQL InnoDB 5.0.22. How to repeat: -- heres a sample of stored procs that I backed up using Administrator -- -- Procedure `goolabri`.`InsertEvent` -- DROP PROCEDURE IF EXISTS `InsertEvent`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertEvent`(xIDMaster INT, xIDUser INT, day1_ INT, day2_ INT, hour1_ INT, hour2_ INT) BEGIN DECLARE zNewID INT; INSERT INTO `sale-events`(idguest, idemployee, datemade, arrive, depart) VALUES(xIDMaster, xIDUser, CURDATE(), ADDDATE(ADDDATE(CURDATE(), INTERVAL day1_ DAY), INTERVAL hour1_ HOUR), ADDDATE(ADDDATE(CURDATE(), INTERVAL day2_ DAY), INTERVAL hour2_ HOUR)); SELECT LAST_INSERT_ID() INTO zNewID; INSERT INTO `sale-glist`(idguest, idevent) VALUES(xIDMaster, zNewID); SELECT zNewID; END $$ DELIMITER ; -- -- Procedure `goolabri`.`InsertGuest` -- DROP PROCEDURE IF EXISTS `InsertGuest`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGuest`(IN xIDMaster INT, IN xIDEvent INT) BEGIN DECLARE zNewID INT; INSERT INTO `sale-guests` (idref, idpostcode, firstname, lastname, ccdate) VALUES(xIDMaster, 555, LPAD((SELECT COUNT(idguest) +1 FROM `sale-glist` WHERE idevent = xIDEvent), 2, "0"), 'Guest', ADDDATE(CURDATE(), INTERVAL 1 YEAR)); SELECT LAST_INSERT_ID() INTO zNewID; INSERT INTO `sale-glist`(idguest, idevent) VALUES(zNewID, xIDEvent); SELECT zNewID; END $$ DELIMITER ; -- -- Procedure `goolabri`.`InsertMaster` -- DROP PROCEDURE IF EXISTS `InsertMaster`; DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertMaster`() BEGIN DECLARE zNewID INT; DECLARE zFirstName INT; SELECT (SELECT count(id) +1 from `sale-guests` where id = idref) INTO zFirstName; -- replace 4 queries INSERT INTO `sale-guests` (idpostcode, firstname, lastname, dateadded, ccdate) VALUES('233', LPAD(zFirstName, 2, "0"), 'Master ', CURDATE(), ADDDATE(CURDATE(), INTERVAL 1 YEAR)); SELECT LAST_INSERT_ID() INTO zNewID; UPDATE `sale-guests` SET idref = zNewID where id = zNewID; SELECT zNewID; END $$ Suggested fix: 1. Remove Definer crap from create proc line 2. Replace the semi colon on the Drop statement line with $$