Unanswered: Running certain procedure in event fails (MySQL 5.1)
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
CREATE EVENT myevent2 ON SCHEDULE AT @endTime DO CALL `database`.`OnlyForTestProcedure`();
Both `SupportingProcedure` 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.
Are there any error messages? I would check the mysql error logs as this will most likely explain more about the issue.
Also I could see an issue with the use of the @variables. These are session variables but an event runs in the background. So passing these in to a function might not work. I have not tested this but I have not seen this in any examples.
It's true what you wrote, I've checked something like this:
set @input = 'sometext';
set @startTime = TIMESTAMP(NOW(), SEC_TO_TIME(10));
CREATE EVENT eventName ON SCHEDULE AT @startTime DO
CALL `databaseName`.`ProcedureInsertingTextIntoTableAndOutputtingIt`(@input, @output);
select SLEEP(11); #wait for event to trigger
Last line shows a value of 0 (zero), and the inserted text into table from ProcedureInsertingTextIntoTableAndOutputtingIt is NULL.
Don't really know how to bypass it, if I manage, I would post it here.
Problem with parameters that expires before execution may be bypassed by using PREPARE - EXECUTE sequence. However, this solution has some important limitation; as for MySQL 5.1 it is unable to prepare a statement that creates an event. For more information see MySQL documentation.
Since I use PHP I can concatenate proper SQL statement and call it via PHP. I have no idea how to do it in pure SQL, if I find it, I would post it.