Mysql Event Scheduler using local variable

Question

I am trying to create a report schedule in mysql, but the query depends on the local variable fdate. How can I have mysql load the fdate variable and then run the query in the 2nd event? Thanks

CREATE EVENT fdate
  ON SCHEDULE
  EVERY 1 DAY
starts '2018-03-26 07:30:00'
Do
 set @fdate = 2018031; #EOM prior month

create event report_1 on SCHEDULE
   EVERY 1 day
 starts '2018-03-26 07:31:00'
DO
 <Here is the Query depending up fdate>

https://stackoverflow.com/questions/14523816/load-data-infile-variable-into-infile-error — Graciano, Apr 01 '18 at 15:48

score 1 · Accepted Answer · answered Apr 01 '18 at 16:48

I think each event executes in a new session, and user-defined variables have session scope. So you can't really use those variables in events and have their value survive from one event execution to another.

Here's a test to show it:

mysql> create table log ( i int );

mysql> create event e on schedule every 1 minute do
    -> insert into log set i = @a := coalesce(@a+1, 1);

If the @a variable retains its value from one event execution to the next, we'll get a series of incrementing values in my log table.

A few minutes later, we see that we don't get incrementing values. Each execution starts the counter over at 1.

mysql> select * from log;
+------+
| i    |
+------+
|    1 |
|    1 |
|    1 |
+------+

But you can DECLARE a local variable, and use it in a compound statement body for an event.

CREATE EVENT fdate
  ON SCHEDULE
  EVERY 1 DAY
  STARTS '2018-03-26 07:30:00'
DO BEGIN
  DECLARE fdate INT;
  SET fdate = DATE_FORMAT(LAST_DAY(CURDATE() - INTERVAL 1 MONTH), '%Y%m%d');
  # following query uses fdate 
END

Thanks this is what I was looking for. Just did not know how to go about adding in multiple statements within the Do begin clause. — user60887, Apr 01 '18 at 18:11
Read about it here: https://dev.mysql.com/doc/refman/5.7/en/begin-end.html — Bill Karwin, Apr 01 '18 at 18:52

Mysql Event Scheduler using local variable

1 Answers1

Linked