Running certain procedure in event fails (MySQL 5.1)

mismachinae · #1 (**permalink**) 02-26-11, 11:30

Hi everyone,

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:

Code:

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 `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.

it-iss.com · #2 (**permalink**) 02-27-11, 07:21

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.

mismachinae · #3 (**permalink**) 02-27-11, 10:31

It's true what you wrote, I've checked something like this:

Code:

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
select @output;

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.

mismachinae · #4 (**permalink**) 02-27-11, 12:45

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.

Thanks & Take care

Internet Services

Web Development

Digital Marketing

Consumer Tech