Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2011
    Posts
    3

    Unanswered: Running certain procedure in event fails (MySQL 5.1)

    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.

  2. #2
    Join Date
    Sep 2009
    Location
    San Sebastian, Spain
    Posts
    880
    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.
    Ronan Cashell
    Certified Oracle DBA/Certified MySQL Expert (DBA & Cluster DBA)
    http://www.it-iss.com
    Follow me on Twitter

  3. #3
    Join Date
    Feb 2011
    Posts
    3
    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.

  4. #4
    Join Date
    Feb 2011
    Posts
    3
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •