Drop procedure if EXISTS rotatepjp;
DELIMITER //
CREATE PROCEDURE rotatepjp()
BEGIN
DECLARE u_id, settingval, pjp_userid, pjp_beatid, u_frequency INT;
DECLARE u_pjpstartdate, pjp_fordate, tilldate, newdate DATE;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED ;
SELECT value INTO settingval FROM settings where name = 'autorotatepjp';
IF settingval > 0 THEN
BEGIN
DECLARE done INT DEFAULT FALSE;
DECLARE cur1 CURSOR FOR SELECT id, pjpstartdate, frequency FROM users;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
#open cursor
OPEN cur1;
#starts the loop
the_loop: LOOP
#get the values of each column into our variables
FETCH cur1 INTO u_id, u_pjpstartdate, u_frequency;
IF done THEN
LEAVE the_loop;
END IF;
#Do some post processing
IF u_frequency > 0 THEN
SET tilldate = DATE_ADD( u_pjpstartdate, INTERVAL u_frequency DAY );
IF tilldate = CURDATE() THEN
BEGIN
DECLARE done1 INT DEFAULT FALSE;
DECLARE cur2 CURSOR FOR SELECT user_id, beat_id, fordate FROM pjps where user_id = u_id and fordate >= u_pjpstartdate and fordate < tilldate;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done1 = TRUE;
OPEN cur2;
the_loop1: LOOP
#get the values of each column into our variables
FETCH cur2 INTO pjp_userid, pjp_beatid, pjp_fordate;
IF done1 THEN
LEAVE the_loop1;
END IF;
SET newdate = DATE_ADD( pjp_fordate, INTERVAL u_frequency DAY );
INSERT INTO pjps (user_id, beat_id, fordate, created, modified )
VALUES ( pjp_userid, pjp_beatid, newdate, NOW(), NOW() );
END LOOP the_loop1;
UPDATE users set pjpstartdate = CURDATE(), modified = NOW() where id = u_id;
END;
END IF;
END IF;
END LOOP the_loop;
END;
END IF;
COMMIT ;
END //
DELIMITER ;
this code is working in innodb_version 5.5.34 but not working innodb_version 5.6.21.......any clue, where I am doing mistake???
error - #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DELIMITER' at line 1