CODEWITHSUNDEEP
mysqldatabaseeventsscheduled-tasksmysql-event
Gnanendra
Gnanendra

Reputation: 519

A MySQL Event should run once every month at the 1st day's 00:00:00 (Midnight)

I have Win XP os and XAMPP installed in my machine.

I need to run my event at 00:00:00(Midnight) of the 1st day of every month. Means 1st of every month. (e.g. jan 1st, Feb1 1st, March 1st, ... ).

And, I also need to call the stored procedure in the same event. And I want achieve all this using Event/Job only not from front end.

Please spend few minutes for my query.

Upvotes: 7

Views: 24346

Answers (3)

Super Kai - Kazuya Ito
Super Kai - Kazuya Ito

Reputation: 1

For example, you create test table as shown below:

CREATE TABLE test (
  num int
);

Then, you insert the row whose num is 1 as shown below:

INSERT INTO test (num) VALUES (1);

Now, you create plus_one event which starts adding 1 to num every one month since 2023-11-01 00:00:00 as shown below. *If 2023-11-01 00:00:00 has already passed, plus_one event starts adding 1 to num every one month since 2023-12-01 00:00:00 after you create plus_one event. *My answer and the doc explain events in MySQL:

DELIMITER $$

CREATE EVENT plus_one
ON SCHEDULE EVERY 1 MONTH
STARTS '2023-11-01 00:00:00'
DO
BEGIN
UPDATE test SET num = num + 1;
END$$

DELIMITER ;

Then, you can check plus_one event adds 1 to num every one month as shown below:

mysql> SELECT * FROM test;
+------+
| num  |
+------+
|  2   |
+------+

Upvotes: 0

Devart
Devart

Reputation: 122032

The MySQL event syntax is very simple -

DELIMITER $$
CREATE EVENT event1
ON SCHEDULE EVERY '1' MONTH
STARTS '2011-05-01 00:00:00'
DO 
BEGIN
 -- your code
END$$

DELIMITER ;

Event will start working on '2011-05-01' at '00:00:00' (datetime must be in a future).

More information - Using the Event Scheduler

Do not forget to enable global event scheduling thread -

SET GLOBAL event_scheduler = 1;

Upvotes: 22

lyle
lyle

Reputation: 837

Assuming your mysql database lives on a web server, you can set up a cron job that runs at the first of every month. Your server probably provides some web interface to do this.

If you can't start the cron job from the server itself, there are free web services where you can set up cron jobs that call scripts on your server at given times.

The script called by the cron job can be a simple php script that runs the query.

$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_query(' /* insert query here */ ');
mysql_close($link);

You may want to include some error checking and either send yourself an email about success / failure or leave some messages in a log file. That way you can monitor whether the script runs at all and / or successfully.

Upvotes: 0

Related Questions