📋

Description

This error occurs in MySQL when defining a scheduled event, indicating a logical inconsistency between the event's `STARTS` and `ENDS` timestamps. Specifically, it means the `ENDS` time is set to a value that is chronologically before the `STARTS` time, or one of the timestamps is malformed and cannot be properly interpreted.

💬

Error Message

ENDS is either invalid or before STARTS

🔍

Known Causes

4 known causes

⚠️

ENDS Time Before STARTS Time

The most common reason, where the specified end time for the event occurs chronologically before its start time, which is logically impossible.

⚠️

Invalid Date/Time Format

The `STARTS` or `ENDS` timestamps provided are not in a recognized MySQL date and time format, leading the server to interpret them incorrectly.

⚠️

Manual Calculation Error

When calculating `STARTS` and `ENDS` times manually or programmatically, an error in logic results in `ENDS` being set to a value earlier than `STARTS`.

⚠️

Time Zone Discrepancies

Implicit or explicit differences in time zones between `STARTS` and `ENDS` values can lead to an apparent 'before' condition, especially with server-side time interpretation.

🛠️

Solutions

3 solutions available

1. Correcting Conflicting Event Schedule Times easy

Adjusts the STARTS and ENDS times of an event to ensure ENDS is after STARTS.

1

Identify the problematic event. You can list all events using `SHOW EVENTS`.

SHOW EVENTS;

2

Examine the `STARTS` and `ENDS` columns for the event that is causing the error. If the `ENDS` time is before or the same as the `STARTS` time, it needs to be corrected.

SELECT EVENT_NAME, STARTS, ENDS FROM INFORMATION_SCHEMA.EVENTS WHERE EVENT_NAME = 'your_event_name_here';

3

Modify the event's schedule to ensure `ENDS` is chronologically after `STARTS`. Replace 'your_event_name_here', the old `STARTS` value, and the new, corrected `ENDS` value.

ALTER EVENT your_event_name_here ON SCHEDULE EVERY 1 DAY STARTS '2023-10-27 08:00:00' ENDS '2023-10-27 09:00:00';

4

Verify the change by showing events again.

SHOW EVENTS;

2. Recreating Event with Valid Schedule medium

Drops and recreates the event with corrected `STARTS` and `ENDS` times.

1

Get the definition of the problematic event. This will help you recreate it accurately.

SHOW CREATE EVENT your_event_name_here;

2

Drop the existing event.

DROP EVENT your_event_name_here;

3

Create the event again, ensuring the `STARTS` and `ENDS` times are valid and `ENDS` is after `STARTS`. Adjust the `STARTS` and `ENDS` values as needed.

CREATE EVENT your_event_name_here
ON SCHEDULE EVERY 1 DAY
STARTS '2023-10-27 08:00:00'
ENDS '2023-10-27 09:00:00'
DO
  -- Your event logic here
  BEGIN
    -- Example: INSERT INTO some_table (col1) VALUES ('some_value');
  END;

3. Adjusting the Event Scheduler's Time Zone advanced

Ensures the server's time zone is correctly configured to avoid date/time interpretation issues.

1

Check the current time zone setting of your MySQL server.

SHOW VARIABLES LIKE 'time_zone';

2

If the `time_zone` is 'SYSTEM', it uses the operating system's time zone. Verify the OS time zone is set correctly.

# On Linux/macOS:
ls -l /etc/localtime
# On Windows:
Get-TimeZone

3

If the time zone is incorrect or needs to be explicitly set for MySQL, you can set it globally (requires SUPER privilege) or for the current session.

-- For current session:
SET time_zone = '+00:00'; -- Example for UTC

-- Globally (requires SUPER privilege, needs to be in my.cnf/my.ini for persistence):
SET GLOBAL time_zone = '+00:00';

4

After setting the time zone, you might need to restart the MySQL server for global changes to take full effect. Then, re-evaluate and potentially correct the event schedules if the time zone change impacted them.

# Example for systemd-based Linux:
sudo systemctl restart mysql

# Example for older SysVinit:
sudo service mysql restart

🔗

Related Errors

5 related errors

MySQL Error 1543: Invalid Event Schedule Times