mysql - Mysql 中的事件调度程序未运行

Question

我在 MySQL 中编写了事件调度程序。执行后，这个事件根本不起作用。所写的任务没有发生。以下是我的事件调度程序，分隔符$$

CREATE  EVENT `Untravelled_Deduction` ON SCHEDULE EVERY 1 DAY STARTS '2016-04-01 06:42:00' ON COMPLETION NOT PRESERVE ENABLE DO BEGIN
     DECLARE UserId INT;
     DECLARE v_finished INT DEFAULT 0;
     DECLARE GetDate DATE DEFAULT DATE(NOW());

     /*get each user who's account is activated and not swiped for the given date*/
     DECLARE UnTravelled CURSOR FOR 
        SELECT DISTINCT U.user_id 
        FROM `um.user` U
    INNER JOIN `um.user_ps.pass` UP ON UP.user_id=U.user_id
    INNER JOIN `ps.pass` P ON P.pass_id=UP.pass_id AND P.status_id=4
    INNER JOIN `um.user_trs.tariff` UT ON UT.user_id = U.user_id
    WHERE U.is_verified_email=1 AND U.is_active=1

    AND UT.user_tariff_id = (
                 /*check user available_journeys journeys is available or not*/
                  SELECT MAX(UT2.user_tariff_id) FROM `um.user_trs.tariff` UT2 WHERE UT2.user_id = UT.user_id
                  AND UT2.available_journeys>0 AND UT2.current_balance>0 AND UT2.end_date>=GetDate
                );


     DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished=1;

     /*Match th date with holiday list*/
     SET @HolidayCount=(SELECT COUNT(*) FROM `holiday_list` WHERE DATE(holiday_date)=GetDate);

     /*Match date for saturday and sunday*/
     IF DAYOFWEEK(GetDate)!=1 AND DAYOFWEEK(GetDate)!=7 AND @HolidayCount=0 THEN
       OPEN UnTravelled;
       get_userid:LOOP
       FETCH UnTravelled INTO UserId;
       IF v_finished=1 THEN
        LEAVE get_userid;
        END IF;

         /*Find user is registered for two way or one way, if @UserRouteCount=2 i.e. two way, @UserRouteCount=1 i.e. one way, */    
         SET @UserRouteCount = (SELECT COUNT(*) FROM `um.user_rm.route` WHERE user_id = UserId);
         /*Get user swipe count to check how many times he swiped for the day. if only one and he is one way then deduct only once*/
         SET @UserSwipeCount = (SELECT COUNT(*) FROM `ts.swipe_information` WHERE user_id = UserId AND DATE(swipe_in_time)=GetDate);
         /*if user is two way and swiped only once for the day then deduct only once*/
         IF (@UserRouteCount=2 AND @UserSwipeCount=1) THEN
             SET @RouteCount=1;
         ELSE
             SET @RouteCount=@UserRouteCount;
         END IF;
         SET @i=1;

         /*Get ticket details on this date for the user*/

         SET @TicketCont= (SELECT COUNT(ticket_id) FROM `ts.ticket` WHERE DATE(`issued_on`)=GetDate AND user_id=UserId);
         SET @IsInsert=0;
         /*Check if any ticket is issued for the user on this date. if not he not travelled and go ahead to deduct*/
         IF (@TicketCont=0) THEN
            SET @IsInsert=1;
         END IF;
         /*check if ticket issued once, if he is two way user then decuct once*/
         IF (@TicketCont=1 AND @UserRouteCount=2) THEN
             SET @IsInsert=1;
         END IF;

          WHILE @i <= @RouteCount DO
            IF (@IsInsert=1) THEN
          /*Generate ticket if not exist for given date*/
          /*get user current tariff plan*/
          SET @UserTariffId = (SELECT user_tariff_id FROM `um.user_trs.tariff` WHERE user_id =UserId AND expired_on >= GetDate AND available_journeys > 0 ORDER BY user_tariff_id LIMIT 1);
          IF(@UserTariffId IS NOT NULL)
          THEN
                SET @PerJourneyCost = (SELECT per_journey_cost FROM `um.user_trs.tariff` WHERE user_tariff_id=@UserTariffId );
            SET @TariffCurrentBalance = (SELECT current_balance FROM `um.user_trs.tariff` WHERE user_tariff_id=@UserTariffId );

            INSERT INTO `ts.ticket`(user_id,ticket_type_id,ticket_number,issued_on,
                amount_charged,user_tariff_id,opening_balance,is_untravelled) VALUES
                (UserId,1,'',UTC_TIMESTAMP(),@PerJourneyCost,@UserTariffId, @TariffCurrentBalance,1);

                IF @PerJourneyCost IS NOT NULL THEN
            /*Update user current tariff balance and number of journeys*/
              UPDATE `um.user_trs.tariff` SET current_balance=(current_balance-@PerJourneyCost),
              available_journeys =(available_journeys-1) WHERE user_tariff_id = @UserTariffId;

                END IF;

          END IF;

        END IF; 
        SET @i=@i+1; 
         END WHILE;

         /*Update user balance details and update Updated date in User table*/
          UPDATE `um.user` SET updated_on=UTC_TIMESTAMP() WHERE user_id = UserId;
       END LOOP get_userid;
       CLOSE UnTravelled;
     END IF;
    END$$

DELIMITER ;

无法纠正该问题，请提出任何改进建议。

问候桑吉塔

Answer 1

创建事件还不够。

确保调度程序确实已启用:

MariaDB [(none)]> show global variables like 'event_scheduler';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| event_scheduler | OFF   |
+-----------------+-------+
1 row in set (0.00 sec)

如果没有，请尝试在运行时激活它:

SET GLOBAL event_scheduler = ON;

或

SET @@global.event_scheduler = ON;

或者在/etc/my.conf 中(或者您的配置所在的任何位置)，然后重新启动服务器:

event_scheduler=ON

官方文档:

Event Scheduler Configuration

Event scheduler options