Search Knowledge Base Articles

DataBase Cronjob

1. Enable the Event Scheduler

You can enable it temporarily or permanently.

  • Temporarily (until MySQL restarts):

SET GLOBAL event_scheduler = ON;

  • Check if it’s enabled:

SHOW VARIABLES LIKE 'event_scheduler';

  • Permanently (edit MySQL config file):

Add this line to your my.cnf (Linux) or my.ini (Windows) file under [mysqld] section:

event_scheduler=ON

Then restart MySQL server.

2. Disable the Event Scheduler

  • Temporarily:

SET GLOBAL event_scheduler = OFF;

  • Permanently: Remove or set event_scheduler=OFF in your MySQL config file and restart the server.

3. Create, Show, and Drop Events

  • Create an event (example: event runs every day):

CREATE EVENT my_event ON SCHEDULE EVERY 1 DAY DO -- Your SQL statement here, e.g.: UPDATE my_table SET col = 'value' WHERE id = 1;

  • Show events:

SHOW EVENTS;

  • Drop (delete) an event:

DROP EVENT my_event;

MySQL Event Scheduler for Last Day of Month Reset

DELIMITER $$ CREATE EVENT reset_actual_lead_count ON SCHEDULE EVERY 1 DAY STARTS '2025-09-06 00:00:00' -- Adjust start time as needed DO BEGIN -- Check if today is the last day of the month IF LAST_DAY(CURDATE()) = CURDATE() THEN -- Reset actual_lead_count to 0 for all staffs UPDATE et_staff SET actual_lead_count = 0; END IF; END $$ DELIMITER ;

Explanation:

  • LAST_DAY(CURDATE()) returns the last day of the current month.

  • We compare it with CURDATE() (today).

  • If they are equal, it's the last day of the month.

  • Then actual_lead_count is reset to zero for all staffs.

Important:

  • Make sure the MySQL event scheduler is enabled (see previous message).

  • Adjust the STARTS time for when you want this event to first run (e.g., at midnight)

Step 1: Enable Event Scheduler in MySQL on cPanel

Important: The MySQL event scheduler setting is usually controlled at the server level, so you might not be able to enable or disable it directly through cPanel unless your hosting provider allows it.

  • Check if Event Scheduler is enabled:

  1. Log in to cPanel.

  2. Open phpMyAdmin.

  3. Select your database.

  4. Go to the SQL tab and run:

    SHOW VARIABLES LIKE 'event_scheduler';

If it says OFF, then the scheduler is disabled.

  • Enable it temporarily (if your user permissions allow):

    Run:

    SET GLOBAL event_scheduler = ON;

If you get a permission error, then you will need to contact your hosting provider to enable it.

Step 2: Manage Events in phpMyAdmin (within cPanel)

  • To view existing events:

    1. In phpMyAdmin, click your database.

    2. Click on the Events tab (usually next to Tables, Views, etc.).

      • If you don’t see the Events tab, your user permissions or MySQL version might not support events.

    3. You will see a list of scheduled events.

  • To delete (drop) an event:

    1. In the Events tab, select the event you want to remove.

    2. Click Drop to delete it.

  • To create or edit an event:

    1. You can use the SQL tab and write an event creation query, for example:

      CREATE EVENT my_event ON SCHEDULE EVERY 1 DAY DO -- your SQL here;

Did you find this article useful?

Related Articles