MySQL error #1227 and enabling the Event Scheduler: permissions, checks, and fixes

Why does SET GLOBAL event_scheduler = ON return MySQL error #1227?

When #1227 appears specifically on SET GLOBAL event_scheduler = ON, the current account normally lacks the server-level authority needed to change a global MySQL system variable. The command affects the MySQL server as a whole, not one database belonging to one customer.

An application account can therefore have wide privileges on its schema and still fail here. It might have SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER and even EVENT on shopdb.*. None of those automatically gives the account permission to reconfigure a global server variable.

Collect these values before changing anything:

SELECT VERSION(), @@version_comment;
SELECT CURRENT_USER(), USER();
SHOW GRANTS;
SHOW VARIABLES LIKE 'event_scheduler';

CURRENT_USER() shows the account MySQL uses for privilege checking. USER() shows how the client authenticated. On servers with several host-specific accounts such as 'appuser'@'localhost' and 'appuser'@'%', that distinction can explain why a grant appears correct on paper but the SQL request still gets rejected.

For a support request, six lines are much more useful than “MySQL Events do not work”:

Server: MySQL or MariaDB + version
Account: output of CURRENT_USER()
Scheduler: ON / OFF / DISABLED
Failing statement: exact SQL
Error: complete #1227 message
Grants: SHOW GRANTS

Is the MySQL Event Scheduler OFF, ON, or DISABLED?

Check the global scheduler before changing privileges. MySQL has three meaningful event_scheduler states, and OFF and DISABLED lead to completely different fixes.

SHOW VARIABLES LIKE 'event_scheduler';

or:

SELECT @@GLOBAL.event_scheduler;

If the value is already ON, stop trying to enable it. A #1227 from SET GLOBAL is irrelevant to the current execution problem because the server-wide switch is already on. Move to SHOW EVENTS, the event schedule and its execution context.

On a server you administer, SHOW PROCESSLIST can provide supporting evidence:

SHOW PROCESSLIST;

With sufficient visibility, MySQL can show an event_scheduler daemon thread waiting for the next activation. If the variable says ON but the server behaves as though no scheduler thread is active, the process list and server error log are more informative than repeating SET GLOBAL.

Which privilege is missing: EVENT, SYSTEM_VARIABLES_ADMIN, or SUPER?

GRANT EVENT succeeds, CREATE EVENT starts working, but SET GLOBAL event_scheduler = ON still returns #1227. That behaviour is expected: managing event objects and changing the server-wide scheduler variable are different privilege checks.

What does the EVENT privilege allow?

The EVENT privilege allows an account to create, alter, drop and display Event Scheduler events within the scope where the privilege was granted. For one database, an administrator might use:

GRANT EVENT ON appdb.* TO 'appuser'@'localhost';

Verify the actual account afterwards:

SHOW GRANTS FOR 'appuser'@'localhost';

Or, while logged in as the application account:

SHOW GRANTS;

If CREATE EVENT failed because EVENT was missing, this grant can solve that failure. EVENT will not fix SET GLOBAL.

What does MySQL require for SET GLOBAL event_scheduler?

Current MySQL uses the dynamic SYSTEM_VARIABLES_ADMIN privilege for setting global system variables. The older, broader SUPER privilege also covers many server-administration operations in MySQL releases where it remains available, but it should not be treated as the default answer for an application account.

This sequence is therefore possible:

GRANT EVENT ON appdb.* TO 'appuser'@'localhost';

-- Connected as appuser:
CREATE EVENT ...;
-- Works

SET GLOBAL event_scheduler = ON;
-- ERROR 1227

EVENT and SET GLOBAL are checked at different privilege levels.

Why is MariaDB different?

Do not assume that a MySQL privilege name applies unchanged to MariaDB. MariaDB documentation uses SUPER for changing event_scheduler, so identify the product before applying a grant recipe:

SELECT VERSION(), @@version_comment;

If it is MariaDB, use the MariaDB privilege model for that release. If it is MySQL, use the MySQL model.

Can you enable the Event Scheduler from phpMyAdmin or shared hosting?

Seeing an “Events” tab in phpMyAdmin does not mean the account controls the server-wide scheduler. phpMyAdmin sends SQL using the privileges of the connected MySQL account; the interface does not bypass the privilege boundary.

On shared hosting, two checks answer most of the question:

SHOW VARIABLES LIKE 'event_scheduler';
SHOW GRANTS;

This is where users often lose time: event_scheduler already says ON, but they continue trying SET GLOBAL event_scheduler = ON and focus on the resulting #1227. In that case the denied SET GLOBAL request does not explain why the individual event is failing. The global switch was already correct.

If the scheduler is OFF and the account gets #1227 on SET GLOBAL, there is usually nothing useful to gain from trying broader SQL grants from the same restricted account. Send the provider a reproducible request instead:

SHOW VARIABLES LIKE 'event_scheduler';
-- Result: OFF

SHOW GRANTS;
-- Output attached

SET GLOBAL event_scheduler = ON;
-- ERROR 1227 ...

Application requirement:
CREATE EVENT in database appdb

If the hosting service intentionally does not support MySQL Events, cron or an application scheduler can be an alternative only when the application itself supports moving that scheduled work out of MySQL. It is not a transparent replacement for every existing event.

How do you enable event_scheduler on a VPS and keep it enabled after restart?

On a VPS you administer, treat runtime state and startup state separately. SET GLOBAL can make the scheduler work immediately, while the next MySQL restart can expose that the persistent configuration was never set or was placed in a file the server does not read.

How to enable the scheduler immediately

First confirm that the variable is OFF, not DISABLED:

SHOW VARIABLES LIKE 'event_scheduler';

If it is OFF and you are connected with sufficient administrative privileges:

SET GLOBAL event_scheduler = ON;

Verify it:

SHOW VARIABLES LIKE 'event_scheduler';

The expected value is ON. No restart is needed for this runtime change.

How to make the setting persistent

For a server that should start with the Event Scheduler enabled, configure the server startup value. A traditional option-file entry is:

[mysqld]
event_scheduler=ON

Do not assume that /etc/my.cnf, /etc/mysql/my.cnf or another familiar path is automatically the active file on this installation. MySQL can read several option files and included files, and later settings can override earlier ones.

For Oracle MySQL, one practical way to see which default option-file paths the installed mysqld binary checks is:

mysqld --verbose --help

The beginning of the output lists option files in the order MySQL looks for them. This does not prove that every listed file exists, but it tells you where the binary expects configuration. Check included configuration directories as well if the main file contains !include or !includedir directives.

Next identify the service you are actually running. Depending on the package, it may be named mysql, mysqld or mariadb:

systemctl status mysql
systemctl status mysqld
systemctl status mariadb

Restart the service that exists on the server, reconnect, and verify:

SHOW VARIABLES LIKE 'event_scheduler';

Only the post-restart check proves that the persistent setting took effect.

Why is event_scheduler OFF again after a restart?

SET GLOBAL event_scheduler = ON works, the events start running, MySQL restarts, and the variable is OFF again. That symptom points away from privileges and toward startup configuration.

Check the problem in this order:

1. Confirm that the intended MySQL or MariaDB service actually restarted.
2. Run SHOW VARIABLES LIKE 'event_scheduler'; after reconnecting to the same instance.
3. Check which option files the server binary reads.
4. Inspect include directories for another event_scheduler setting that overrides yours.
5. Check the service startup configuration for command-line options that override option files.
6. Review the service status and MySQL error log if the restart did not complete cleanly.

Do not keep adding the same line to different my.cnf files. Find the configuration chain the running server actually uses.

MySQL releases that support persisted system variables also provide SET PERSIST for persistable variables. If you use that mechanism, remember that MySQL stores persisted values in mysqld-auto.cnf; do not edit that JSON file manually. Version and privilege requirements still apply.

What if event_scheduler is DISABLED?

If SHOW VARIABLES returns DISABLED, runtime grants will not turn the scheduler on. Look for a startup setting such as:

event_scheduler=DISABLED

or a command-line equivalent:

--event-scheduler=DISABLED

Correct the startup configuration, restart MySQL and check the variable again.

Why can a MySQL event exist but never run?

SHOW EVENTS lists cleanup_event as enabled, event_scheduler is ON, but yesterday's rows are still in the table. The event exists. That still does not prove its SQL completed successfully.

Start with the object itself:

SHOW EVENTS FROM appdb;
SHOW CREATE EVENT appdb.cleanup_event;

Then collect its execution metadata:

SELECT
    EVENT_SCHEMA,
    EVENT_NAME,
    STATUS,
    EVENT_TYPE,
    EXECUTE_AT,
    INTERVAL_VALUE,
    INTERVAL_FIELD,
    STARTS,
    ENDS,
    LAST_EXECUTED,
    DEFINER
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'appdb'
  AND EVENT_NAME = 'cleanup_event';

Read that output in order: status, schedule, time zone, execution metadata, definer, then the event body. Do not jump straight to recreating the event.

How do you check the event schedule and MySQL time zone?

If an event seems to run “at the wrong time,” compare its stored schedule with the MySQL session time zone that was in effect when the schedule was defined. MySQL interprets times in an ON SCHEDULE clause using the current session time_zone when the event is created or altered.

Start with:

SELECT
    NOW() AS mysql_now,
    @@global.time_zone AS global_time_zone,
    @@session.time_zone AS session_time_zone;

Then compare that with:

SELECT
    EVENT_NAME,
    EXECUTE_AT,
    STARTS,
    ENDS,
    TIME_ZONE
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'appdb'
  AND EVENT_NAME = 'cleanup_event';

A common misdiagnosis is to see LAST_EXECUTED=NULL and assume the scheduler failed when the first scheduled time has not arrived according to the event's time zone. Check the clock before checking privileges again.

This also matters during migrations. A dump can recreate an event with a schedule that is technically valid but interpreted differently from what the administrator expects after moving between environments.

What does LAST_EXECUTED actually prove?

LAST_EXECUTED is useful, but it should not be treated as a universal “event succeeded” flag. Read it together with the event schedule and the server error log.

MySQL writes Event Scheduler executions that terminate with an error or warning to the server error log. Search around the time the event should have run and look for the event name, the definer account and the actual SQL error. A privilege failure usually gives you something much more useful than “event did not run,” for example a denied INSERT or DELETE operation tied to the event.

If you have no access to the server log, isolate the SQL body and test the harmless part manually with the same account assumptions. Do not run a destructive production DELETE just to prove a privilege error.

Can the DEFINER create an event but fail to execute its body?

Yes. EVENT allows the event object to be created, but the SQL inside the event runs using the event's definer privilege context. A user can therefore create an event whose body later attempts an operation the definer cannot perform.

For example, the event may be valid:

CREATE EVENT appdb.cleanup_event
ON SCHEDULE EVERY 1 HOUR
DO
  DELETE FROM appdb.temp_rows
  WHERE created_at < NOW() - INTERVAL 7 DAY;

but the definer may have EVENT and SELECT without DELETE. The DDL can succeed while the scheduled action fails later. This is exactly the kind of case where the server error log matters.

Check:

SHOW CREATE EVENT appdb.cleanup_event;

Then identify the DEFINER and verify that the account has the privileges required by every statement in the event body.

There is a less obvious MySQL behaviour here: revoking the EVENT privilege, renaming the account or even dropping the account that originally created an event does not automatically remove the event definition. Do not assume that user-account cleanup also cleaned up scheduled objects. Inspect information_schema.EVENTS after migrations and account changes.

Can recurring MySQL events overlap?

A recurring event creates another problem if one run lasts longer than its interval. An event scheduled every minute can still be running when the next minute arrives, and MySQL may execute overlapping instances.

The simple diagnostic rule is:

worst-case execution time > schedule interval
= overlap is possible

If concurrent runs would corrupt data or duplicate work, do not assume Event Scheduler serializes them. Use a deliberate locking strategy such as an application lock, GET_LOCK(), row locking or another mechanism appropriate to the task.

Before adding locking, measure or observe the actual event runtime. A lock is not a substitute for fixing an event body that has suddenly become slow because a table grew or an index disappeared.

Why does importing a dump with CREATE EVENT or DEFINER fail with error #1227?

A typical migration gets through CREATE TABLE and thousands of INSERT statements, then phpMyAdmin stops on CREATE DEFINER=... EVENT. Do not re-import the whole dump yet. First isolate the statement that actually failed.

Search the SQL dump for:

CREATE EVENT
DEFINER=

The destination account has no EVENT privilege

A dump that contains events needs the destination account to satisfy the privileges required by those CREATE EVENT statements. Tables can import perfectly while event creation fails.

Check:

SHOW GRANTS;

If EVENT is missing and you administer the server, it can be granted at schema scope:

GRANT EVENT ON appdb.* TO 'appuser'@'localhost';

Retry the failed CREATE EVENT statement separately. If that works, you have proved the failure was event-specific rather than a general dump incompatibility.

The dump contains a DEFINER from another server

A migrated event often carries the account from the source server:

CREATE DEFINER=`old_user`@`localhost`
EVENT `cleanup_event`
ON SCHEDULE EVERY 1 DAY
DO
  DELETE FROM appdb.temp_rows
  WHERE created_at < NOW() - INTERVAL 7 DAY;

On the destination system, three different situations need to be separated:

Before editing the dump, collect:

SELECT VERSION(), @@version_comment;
SELECT CURRENT_USER(), USER();
SHOW GRANTS;

Then decide which account should execute the event on the destination server. Do not remove every DEFINER clause with a blind search-and-replace. Views, routines, triggers and events all use security contexts; changing those contexts can change what the imported objects are allowed to do.

Newer MySQL releases also have specific dynamic privileges for setting arbitrary definers and for creating orphaned stored objects. This is another reason to identify the server version before following an old “grant SUPER and retry” migration recipe.

How do you verify that a MySQL event actually executed?

event_scheduler=ON and STATUS=ENABLED are configuration checks. They do not prove that the scheduled SQL produced the result you expected. The best verification is an observable, harmless change.

Check LAST_EXECUTED and event metadata

Start with:

SELECT
    EVENT_NAME,
    STATUS,
    LAST_EXECUTED,
    STARTS,
    ENDS,
    TIME_ZONE,
    DEFINER
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'appdb';

If LAST_EXECUTED is NULL, compare it with STARTS, EXECUTE_AT and the event time zone. If the event should already have run, move to the MySQL error log and definer privileges rather than assuming the global scheduler is off.

For MySQL server access, Event Scheduler failures and warnings are written to the error log. Search the time window in which the event should have executed. The useful part is not merely the phrase “Event Scheduler”; it is the SQL error attached to the event, such as a denied table operation.

Use a harmless test event

When you control the database and need to prove scheduling end to end, create a temporary event that writes one timestamp. Do not test the scheduler with a production cleanup query.

First confirm the scheduler and current grants:

SHOW VARIABLES LIKE 'event_scheduler';
SHOW GRANTS;

Create a small test table:

CREATE TABLE appdb.event_scheduler_test (
    id INT UNSIGNED NOT NULL AUTO_INCREMENT,
    executed_at DATETIME NOT NULL,
    PRIMARY KEY (id)
);

Create a one-time event shortly in the future:

CREATE EVENT appdb.event_scheduler_test_run
ON SCHEDULE AT CURRENT_TIMESTAMP + INTERVAL 1 MINUTE
ON COMPLETION PRESERVE
DO
  INSERT INTO appdb.event_scheduler_test (executed_at)
  VALUES (CURRENT_TIMESTAMP);

Confirm the definition:

SHOW CREATE EVENT appdb.event_scheduler_test_run;

After the scheduled time:

SELECT * FROM appdb.event_scheduler_test;

If a row appears, you have an observable end-to-end result: the global scheduler ran, the event reached its scheduled time, its definer could execute the INSERT, and the SQL changed the database.

Check the metadata too:

SELECT
    EVENT_NAME,
    STATUS,
    LAST_EXECUTED,
    DEFINER
FROM information_schema.EVENTS
WHERE EVENT_SCHEMA = 'appdb'
  AND EVENT_NAME = 'event_scheduler_test_run';

Then clean up:

DROP EVENT IF EXISTS appdb.event_scheduler_test_run;
DROP TABLE IF EXISTS appdb.event_scheduler_test;

What should you check if error #1227 remains after granting EVENT?

If GRANT EVENT did not remove #1227, do not add broader privileges at random. Identify the statement that still fails and follow that branch.

Retest the statement that originally failed. If the original failure was SET GLOBAL, successfully creating an event does not prove that the server-level privilege issue is fixed. If the original failure was an explicit DEFINER, seeing event_scheduler=ON does not prove the import issue is fixed.

Once the SQL statement succeeds, verify the next layer too. A successful CREATE EVENT proves only that the event definition was accepted. It does not prove that its scheduled action has executed successfully.

Which fix should you use on shared hosting, a managed server, or your own VPS?

On shared hosting, #1227 can simply mark the boundary of your database account. On your own VPS, the same error can mean you are using the wrong administrative account or changing the wrong startup configuration. The environment decides who can actually apply the fix.

Shared hosting

If event_scheduler is ON, do not ask for server-level privileges just to run SET GLOBAL. Check whether your database account has EVENT and whether the individual event executes. If the scheduler is OFF, the provider controls the next step.

Managed VPS or managed database server

Do not ask only for “MySQL Events to be fixed.” Give the administrator the failed statement and the four values that locate the problem:

SELECT VERSION(), @@version_comment;
SELECT CURRENT_USER(), USER();
SHOW VARIABLES LIKE 'event_scheduler';
SHOW GRANTS;

That is enough to distinguish a restricted database account from a global scheduler configuration problem in most first-pass diagnostics.

Your own VPS with root or DBA access

Use the full chain: identify MySQL or MariaDB, check ON/OFF/DISABLED, enable the scheduler with an administrative account if necessary, make the intended state persistent, check the event's DEFINER, and verify a harmless scheduled action. After the next database restart, check event_scheduler again.

Database migration or SQL dump import

If tables imported but CREATE EVENT did not, treat the stored object as a separate migration problem. Check EVENT, explicit DEFINER, destination account rules and the server version. Test the failed event definition by itself before repeating a large import.

The clean diagnostic path is not “grant more privileges until the error disappears.” It is to identify which operation failed, fix the privilege or configuration layer responsible for that operation, and then verify the next layer. With MySQL Events, creation, global scheduling and execution are separate checks. Treat them that way.