MySql: create procedure error

Question

I'm trying to create a stored procedure, either in command line or MySql Workbench editor.

In Mysql WOrkbench I have no message at all, no success no error, but the procedure is not created. In command line mysql --host=localhost ... < procedure-script.sql I get the following error:

ERROR 1064 (42000) at line 4: 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 28

Line 28 is actually empty but I guess it refers to the final DELIMITER ; in line 31. Here is the complete code:

DELIMITER $$;
-- drop procedure if exists updPages$$

CREATE PROCEDURE updPages()
BEGIN
DECLARE _post_id INTEGER;
DECLARE cur_pages CURSOR FOR 
select ID 
from wp_posts 
where post_type='page' and ID not in (select post_id from wp_postmeta); 

OPEN cur_pages;

Reading_Pages: LOOP
FETCH cur_pages INTO _post_id;
IF done THEN
LEAVE Reading_Pages;
END IF;

insert into wp_postmeta(post_id, meta_key, meta_value)
values (_post_id, '_wp_page_template', 'default'),
(_post_id, 'upside-show-page-title', '1'),
(_post_id, 'upside-show-breadcrumb', '1'),
(_post_id, 'upside-page-show-document-search', '0'),
(_post_id, 'upside-show-page-title-middle', 'false'),
(_post_id, '_vc_post_settings', 'a:1:{s:10:"vc_grid_id";a:0:{}}');
END LOOP;

CLOSE cur_pages;
END$$
DELIMITER ;

(1) Is there any error? (2) Do you know how to show compilation errors in Mysql Workbench? also, (3) can I use tab inside scripts when running them from command line? (I had to strip them out as I had errors)

remove semicolon in the first line DELIMITER $$;

rocks
– rocks

2016-11-15 15:01:39 +00:00
Commented Nov 15, 2016 at 15:01 — rocks
– rocks, Commented Nov 15, 2016 at 15:01

rocks · Accepted Answer · 2016-11-15 15:08:56Z

2

Try this by removing semicolon from delimiter

DELIMITER $$
drop procedure if exists updPages $$
create procedure ..
..
END $$
DELIMITER ;

answered Nov 15, 2016 at 15:08

rocks

1902 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

MySql: create procedure error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related