mysql - create event in store procedure

Question

How to create event inside store procedure?

This is what I been trying to do, and it tells me that I have a syntax error, only it doesn't tells me where.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `update_leaderboard`()
BEGIN


CREATE EVENT update_leaderboard_event
    ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 minute
    DO call update_leaderboard();



END

Edit: According to my MySql(last line) it should be possible:

Syntax”. You can create an event as part of a stored routine, but an event cannot be created by another event.

Edit2 found the problem:

Error 1576: Recursion of EVENT DDL statements is forbidden when body is present

But no solution yet, how do I do it then?

Answer 1

You cant create inside procedure. You need to define it separately.

Just check Mysql Event Scheduler
detail blog: http://goo.gl/6Hzjvg

Answer 2

This can't be done. The closest thing is to use string-building to create a prepared statement inside the SP. Then save the prepared statement to a table "scheduled_queries". Use external scripting (bash/python etc) to build a simple "runner" to execute anything in the table "scheduled_queries".

set @statement = concat("DROP EVENT IF EXISTS ", invalid_event); 
insert into scheduled_queries(query_str) values (@statement);

Only worthwhile introducing a runner setup if making extensive use of create/alter/drop events.

Answer 3

"CREATE EVENT" and "ALTER EVENT" we created at special external application by direct "Request" to MySQL-engine DataBase.

Insert rows from the triggers or procedures to the special table "Need create_alter event" and special external application cyclically reading rows, then directly send requests about create/alter events to MySQL-engine DataBase