Creating a scheduled event in MySQL

Question

I have a table names offer inside a MySQL database named fouras :

mysql> desc offer;
+------------------------+---------------+------+-----+---------+----------------+
| Field                  | Type          | Null | Key | Default | Extra          |
+------------------------+---------------+------+-----+---------+----------------+
| id                     | bigint(20)    | NO   | PRI | NULL    | auto_increment |
| description            | varchar(5000) | NO   |     | NULL    |                |
| end_date               | date          | NO   |     | NULL    |                |
| is_accepted            | bit(1)        | NO   |     | NULL    |                |
| is_active              | bit(1)        | NO   |     | NULL    |                |
| is_draft               | bit(1)        | NO   |     | NULL    |                |
| is_processed           | bit(1)        | NO   |     | NULL    |                |
| is_removed             | bit(1)        | NO   |     | NULL    |                |
| max_reservation_number | int(11)       | NO   |     | NULL    |                |
| promotion_first_param  | varchar(255)  | YES  |     | NULL    |                |
| promotion_product      | varchar(255)  | YES  |     | NULL    |                |
| promotion_second_param | varchar(255)  | YES  |     | NULL    |                |
| promotion_type         | varchar(255)  | NO   |     | NULL    |                |
| publish_date           | date          | YES  |     | NULL    |                |
| remove_time_stamp      | bigint(20)    | YES  |     | NULL    |                |
| start_date             | date          | NO   |     | NULL    |                |
| title                  | varchar(255)  | NO   |     | NULL    |                |
| views_number           | int(11)       | YES  |     | NULL    |                |
| local_business         | bigint(20)    | YES  | MUL | NULL    |                |
+------------------------+---------------+------+-----+---------+----------------+
19 rows in set (0.00 sec)

Now, i want to periodically check if an offer has expired (end_date > today), for that i'm trying to use a MySQL scheduled event :

CREATE EVENT IF NOT EXISTS check_expired_offers
    ON SCHEDULE EVERY 10 MINUTE
    DO
        BEGIN
            DECLARE id INT;
            DECLARE end_date DATE;
            DECLARE offer_cursor CURSOR FOR SELECT id, end_date FROM fouras.offer;

            OPEN offer_sursor;
            offer_loop: LOOP
                FETCH offer_cursor into id, end_date;
                IF end_date < NOW() THEN 
                    UPDATE fouras.offer set is_active = false;  
                END IF;

            END LOOP
END;

But MySQL throws an error when i try to add this event:

Error Code: 1064. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 5    0,127 sec

did you change the delimiter? if not, then the `;` in `declare id int` is going to termiante the entire `create` call, not just that one single line — Marc B, Mar 23 '16 at 19:51
i removed the **;** and most of the errors disappeared but still one error near **DECLARE end_date DATE** — jemlifathi, Mar 23 '16 at 20:11
you still need `;` to delimit the individual statements. just change the main delimiter before you start the create call, e.g. `DELIMITER $$ CREATE blah blahblah END; DELIMITER ;` — Marc B, Mar 23 '16 at 20:15
@MarcB here is the editor : https://drive.google.com/file/d/0B2S5amMpeE6QYWNDSkhNcWhZNUk/view?usp=sharing — jemlifathi, Mar 23 '16 at 20:19

score 0 · Accepted Answer · answered Mar 23 '16 at 20:34

0

The error was about the delimiter, this tutorial helped me a lot

answered Mar 23 '16 at 20:34

jemlifathi

1,482
5
22
32

score 0 · Answer 2 · edited Dec 21 '19 at 06:24

==========================

Found delimiter issue.

==========================

Here's the modified event:

DELIMITER $$
CREATE EVENT IF NOT EXISTS check_expired_offers
    ON SCHEDULE EVERY 10 MINUTE
    DO
        BEGIN
            DECLARE finished INTEGER DEFAULT 0;
            DECLARE id INT;
            DECLARE end_date DATE;
            DECLARE offer_cursor CURSOR FOR SELECT id, end_date FROM fouras.offer;
                        DECLARE CONTINUE HANDLER FOR NOT FOUND SET finished = 1;

            OPEN offer_cursor;
            offer_loop: 
                        LOOP
                FETCH offer_cursor into id, end_date;
                IF finished = 1 THEN 
                                    LEAVE offer_loop;
                                END IF;
                                IF end_date < NOW() THEN 
                    UPDATE fouras.offer set is_active = false;  
                END IF;

            END LOOP ;
END $$
DELIMITER ;

Learn delimiters in MySQL

Note: I've also used a variable there named finished.

Where finished is a variable to indicate that the cursor has reached the end of the result set. Notice that the handler declaration must appear after variable and cursor declaration inside the stored programs.

The following diagram illustrates how MySQL cursor works.

Creating a scheduled event in MySQL

2 Answers2

Found delimiter issue.