Running certain procedure in event fails

I want to call procedure at certain time, so I instatinate an event that will do it (call procedure) for me. Code looks like this:

CALL MainProcedure(@firstOutParam, @secondOutParam);

SELECT @moveId, @endTime; #select just for check if parameters are ok

CREATE EVENT myevent1 ON SCHEDULE AT @endTime DO CALL 
`database`.`SupportingProcedure`(@firstOutParam);

CREATE EVENT myevent2 ON SCHEDULE AT @endTime DO CALL `database`.`OnlyForTestProcedure`();

Both SupportingProcedu开发者_开发技巧re and OnlyForTestProcedure when called not from event work properly, also, myevent2 calls .OnlyForTestProcedure.

The only thing that doesn't work is myevent1. It appears on show events, it then disappears but it doesn't work (SupportingProcedure either isn't called or doesn't work..).

Do you see anything I do wrong? What should I do to make it work? Any help appreciated, I have no clue what else to check.

Have you enabled the event scheduler ?

You need an entry in my.cnf under the [mysqld] section saying:

event_scheduler=on

If the event somehow fails, mysql has poor support for diagnosing it, but you'll see some log entries in the mysql log file (which is usually located in the mysql data directory, e.g. /var/lib/mysql or under the system logs /var/log)

The documentation statest there's no way to pass an argument to and from a procedure invoked by an event, so your first event that pass in @firstOutParam will probably not work. To get around that you'll have to build the SQL dynamically as text and execute it.

SET @sql = CONCAT("CREATE EVENT myevent1 ON SCHEDULE AT ", @endTime, " DO CALL 
`database`.`SupportingProcedure`(",@firstOutParam,")");
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

As I mentioned earlier CREATE EVENT within procedure is not possible, it probably shuts the topic, but read on.

1) If you use MySQL with PHP and need single event in database, you can instantinate one via mysqli query method.

2) If you need to keep track of certain changes in tables you may can create an event that is some sort of daemon. Just set it to:

CREATE EVENT EventDaemon ON SCHEDULE EVERY 1 SECOND STARTS NOW() DO CALL procedure();

where procedure executes its body only if specific requirements occurs (that would make you CREATE EVENT in original situation). Can't really tell if it's efficient, but it works fine for me.

I hope I've made myself clear.