Getting syntax error when creating MySQL scheduled event

Other Questions MySQL
1

I’m trying to set up an automated task in MySQL that runs every hour, but I keep running into a syntax problem. The error message says there’s something wrong with my SQL syntax.

Here’s the error I’m getting:

#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 'EVENT daily_cleanup_task ON SCHEDULE EVERY 1 HOUR STARTS CURRENT_TIMESTAMP' at line 1

This is the code I’m using:

DELIMITER //
CREATE EVENT daily_cleanup_task
    ON SCHEDULE EVERY 1 HOUR
    STARTS CURRENT_TIMESTAMP
    DO
        CALL process_maintenance_routine();
    END//
DELIMITER ;

The stored procedure process_maintenance_routine() definitely exists and works fine when I call it manually. What could be causing this syntax error?

Update: Just realized my MySQL version is 5.0.91, and it looks like events need version 5.1 or higher. That explains the issue!

2

Had the exact same issue a few years back with an older MySQL setup. Version compatibility is definitely what’s causing this. Since you know what’s wrong, I’d check if you can upgrade MySQL first. If that’s not possible, try writing a simple Python or PHP script that connects to your database and runs the stored procedure. Then just schedule it with your system’s task scheduler. This way you get better error handling and logging than basic cron jobs, and you can tweak the scheduling without messing with database config.

3

totally agree, since ur using 5.0, events won’t work at all. u gotta upgrade to 5.1 or use cron jobs instead. it’s not too hard to set up cron tho!

4

MySQL 5.0 is ancient at this point lol. If you can’t upgrade, try a bash script with crontab. Something like */60 * * * * mysql -u user -ppassword dbname -e "CALL process_maintenance_routine();" works fine for basic maintenance tasks.

5

MySQL 5.0 doesn’t support the EVENT scheduler, which is the reason for the syntax error you’re encountering. This feature was introduced in MySQL 5.1, so while your code is technically correct, it won’t function in your current version. If upgrading isn’t possible for you, consider alternative methods such as setting up a cron job to execute a MySQL script hourly or using a shell script that invokes your stored procedure. For the cron job, you could use a command like mysql -u username -p database_name -e "CALL process_maintenance_routine();", ensuring you manage authentication securely.