paolo@bimodesign.com | +34 608 61 64 10

DB Relational

        

MySQL - schedule stored procedure + debug

This MySQL stored procedure checks and change the state of a record from 1 to 2 or 2 to 3 depending of some conditions. It will be schedule every minute and it calls a debug stored procedure. Before to create a procedure, I'll check the global event_scheduler variable

--
-- show variables like 'event_scheduler';
-- 
SET GLOBAL event_scheduler=ON;

The I'll schedule the event called change_state that will call the stored procedure change_state_contest.

CREATE EVENT IF NOT EXISTS change_state
    ON SCHEDULE
      EVERY 1 MINUTE
    COMMENT 'Change state'
    DO
      CALL change_state_contest();
--
-- To check if the creation will return ok
-- 
-- show events;
--
-- To drop
--
-- drop event change_state;

Then it's the mainly stored procedure where I call a debug stored procedure called debug_msg, to list the debug message. You can notice the enable variable that will be the first parameter to activate or not the debug.

DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `change_state_contest`()
BEGIN
    DECLARE constate INT;
    DECLARE condateconfiguration TIMESTAMP;
    DECLARE condatepause TIMESTAMP;

    SET @enabled = TRUE;

    call debug_msg(@enabled, "Start");

    SELECT cont_state, 
           cont_state_date_configuration, 
           cont_state_date_pause
      INTO constate,
           condateconfiguration,
           condatepause
      FROM contest;

    call debug_msg(@enabled, (select concat_ws('',"constate:  ", constate)));
    call debug_msg(@enabled, (select concat_ws('',"cont_state_date_configuration:  ", condateconfiguration)));
    call debug_msg(@enabled, (select concat_ws('',"cont_state_date_pause:  ", condatepause)));
    call debug_msg(@enabled, (select concat_ws('',"NOW:  ", NOW())));

    CASE  constate
       WHEN 1 THEN 
            call debug_msg(@enabled,"case 1");

            IF (condateconfiguration IS NOT NULL) THEN
		call debug_msg(@enabled,"Fecha Configuration diferente de NULL - Hago Update");
		

                IF (NOW() > condateconfiguration) THEN
                    call debug_msg(@enabled,"Fecha configuration minor de la acutal - UPDATE State a 2");

                    UPDATE contest SET
                        cont_state = 2,
                        cont_state_date_configuration = NULL;
                END IF;
            END IF;
       WHEN 2 THEN 
             call debug_msg(@enabled,"case 2");

             IF (condatepause IS NOT NULL) THEN 
                call debug_msg(@enabled,"Fecha pause diferente de NULL - Hago Update");
                
                IF (NOW() > condatepause)  THEN
                    call debug_msg(@enabled,"Fecha pause minor de la acutal - UPDATE State a 3");
                    UPDATE contest SET
                        cont_state = 3,
                        cont_state_date_pause = NULL;       
                END IF;
             END IF;
    END CASE;

    call debug_msg(@enabled, "End");
END

This the debug stored procedure where you can notice the first input variable, that identified the enable or not the debug.

-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: procedure para el debug
-- --------------------------------------------------------------------------------
DELIMITER $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `debug_msg`(enabled INTEGER, msg VARCHAR(255))
BEGIN
  IF enabled THEN BEGIN
    select concat("** ", msg) AS '** DEBUG:';
  END; END IF;
END

Finally, those are some resources that I used to solve the problem, cause It's impossible to remember all the details.

MySQL concept of storage procedure and user-defined variables.
http://www.mysqltutorial.org/mysql-stored-procedure-tutorial.aspx http://code.tutsplus.com/articles/an-introduction-to-stored-procedures-in-mysql-5--net-17843 http://stackoverflow.com/questions/1009954/mysql-variable-vs-variable-whats-the-difference/1010042#1010042

Difference between function and procedure
http://stackoverflow.com/questions/3744209/mysql-stored-procedure-vs-function-which-would-i-use-when/13510102#13510102
MySQL event scheduler
http://stackoverflow.com/questions/3070277/mysql-event-scheduler-on-a-specific-time-everyday
http://dev.mysql.com/doc/refman/5.1/en/create-event.html

Cronjob or MySQL event? And why?
http://stackoverflow.com/questions/14805742/cronjob-or-mysql-event​ http://sforsuresh.in/mysql-event-scheduler-an-alternative-to-cronjob/

How debug in MySQL?
http://stackoverflow.com/questions/273437/how-do-you-debug-mysql-stored-procedures/15687954#15687954