📋

Description

This error occurs in MariaDB when attempting to create or alter an event without providing a valid datetime expression. It specifically indicates the absence of a required `AT` clause for one-time events or a `STARTS` clause for recurring events, which are essential for defining when the event should execute.

💬

Error Message

No datetime expression provided

🔍

Known Causes

3 known causes

⚠️

Omitted AT Clause

When defining a one-time event using `CREATE EVENT`, the required `AT` clause specifying the exact execution datetime is completely missing from the statement.

⚠️

Missing STARTS Clause

For recurring events defined with `EVERY` in `CREATE EVENT` or `ALTER EVENT` statements, the `STARTS` clause, which sets the initial execution time, is not included.

⚠️

Invalid Datetime Function Usage

An attempt was made to use a datetime function (e.g., `NOW()`, `CURRENT_TIMESTAMP`) incorrectly or with missing arguments, leading to no valid datetime expression being generated.

🛠️

Solutions

3 solutions available

1. Provide a Valid Datetime for Event Creation easy

Ensure the `CREATE EVENT` statement includes a valid `AT` clause with a datetime expression.

1

When creating a MariaDB event, the `AT` clause is mandatory and must be followed by a valid datetime expression. This expression can be a fixed timestamp, a function that returns a timestamp, or a schedule with a starting point.

CREATE EVENT my_scheduled_task
ON SCHEDULE AT '2023-10-27 10:00:00'
DO
  -- your SQL statement here

2

Alternatively, you can use a recurring schedule. For instance, to run an event every day at 2 AM:

CREATE EVENT daily_cleanup
ON SCHEDULE EVERY 1 DAY
STARTS CURRENT_TIMESTAMP + INTERVAL 1 HOUR -- Example: start 1 hour from now
DO
  -- your SQL statement here

3

If you are using a stored procedure or a script to create events dynamically, ensure that the variable or the result of the function used for the `AT` clause is a valid datetime string or a datetime literal.

SET @event_time = NOW() + INTERVAL 5 MINUTE;
CREATE EVENT delayed_task
ON SCHEDULE AT @event_time
DO
  -- your SQL statement here

2. Correct Event Scheduler Syntax medium

Review and correct the syntax of your `CREATE EVENT` statement, specifically the `ON SCHEDULE` clause.

1

The `ON SCHEDULE` clause is where the datetime expression is specified. Common valid syntaxes include:

ON SCHEDULE AT 'YYYY-MM-DD HH:MM:SS'
ON SCHEDULE EVERY interval [unit] [STARTS datetime_expression] [ENDS datetime_expression]
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL value unit

2

Ensure there are no typos or missing keywords. For example, `ON SCHEDULE AT CURRENT_TIMESTAMP` is valid, but `ON SCHEDULE CURRENT_TIMESTAMP` would be incorrect.

Incorrect:
CREATE EVENT test_event
ON SCHEDULE CURRENT_TIMESTAMP + 5 MINUTE
DO
  SELECT 1;

Correct:
CREATE EVENT test_event
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 5 MINUTE
DO
  SELECT 1;

3

If you are using variables, make sure they are properly defined and contain a value that MariaDB can interpret as a datetime.

SET @start_time = '2023-10-27 14:30:00';
CREATE EVENT specific_time_event
ON SCHEDULE AT @start_time
DO
  -- your SQL statement here

3. Verify Event Scheduler Status and Configuration medium

Confirm that the MariaDB Event Scheduler is enabled and configured correctly.

1

Check the status of the Event Scheduler. It should be `ON`.

SHOW VARIABLES LIKE 'event_scheduler';

2

If the Event Scheduler is `OFF`, enable it. This can be done temporarily for the current session or permanently by modifying the MariaDB configuration file.

SET GLOBAL event_scheduler = ON;

3

To enable it permanently, edit your MariaDB configuration file (e.g., `my.cnf` or `my.ini`). Add or modify the following line under the `[mysqld]` section:

[mysqld]
event_scheduler = ON

4

After modifying the configuration file, restart the MariaDB service for the changes to take effect.

# For systemd-based systems (e.g., Ubuntu 15.04+, CentOS 7+)
sudo systemctl restart mariadb

# For init.d-based systems (e.g., older Ubuntu, Debian, CentOS)
sudo service mariadb restart

🔗

Related Errors

5 related errors

MariaDB Error 1546: Missing Event Scheduler Datetime