DELIMITER issue in MySQL 5.6

Question

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

Answer 1

When creating a Stored Procedure, you do not need COMMIT;.

I have never used it for Stored Procedures.

No one should have to because Stored Procedures are stored in a MyISAM table (mysql.proc)

Please remove the COMMIT; before the END // and try again.

I would also change line to say

DELIMITER $$

and the end clause to

END $$

and try again

I thought you were working in the mysql client. If your problem is SQLFiddle, here is post from StackOverflow by its Author (Execute triggers stored procedures on SqlFiddle. Mysql)

Based on it, stop using DELIMITER altogether. Look for the fourth button under the query pane.

I just installed MySQL 5.6.22 for Windows on my laptop and ran your code as is. It Works !!!

The only culprit left would be phpmyadmin. It cannot handle delimiters against the latest version of MySQL, which is 5.6. You may need an upgrade of phpmyadmin.

Answer 2

In phpMyAdmin you cannot use DELIMITER in the SQL statement, because you have to define the delimiter on the page instead.

Remove both DELIMITER lines from the SQL statements and then, just below the TEXTAREA where you write your SQL, there is an input field that looks like this:

[ Delimiter ';']

Change the ';' to your new delimiter, ie. '//'.

This also means that ALL delimters are now changed from ; to //, so your very first line needs to change from:

Drop procedure if EXISTS rotatepjp;

to:

Drop procedure if EXISTS rotatepjp//