MariaDB – Fix ‘SHOW EVENTS’ Error Code 1545

mariadbmysql-eventrecovery

I have a MariaDB 5.5.56 install (The default CentOS 7 package).

The VM was running on a host that had some problems reaching its storage. This resulted in the OS remounting some filesystems read-only.

The database is a replication slave and was rebuilt by importing a backup from the master. (After a reboot and fsck, which got the root fs and others back to read-write) (There did not appear to be major data corruption, but replication was failing due to duplicate entries, so restoring seemed safer)

mysqldump failed with mysqldump: Couldn't execute 'show events': Failed to open mysql.event (1545).

Events are not currently in use, but are included in the back, in case they are used in the future.

I have tried running REPAIR TABLE mysql.event, however SHOW EVENTS still fails with Error Code: 1545. Failed to open mysql.event.

SELECT * from mysql.event works as on the master and returns no rows.

On the master (unaffected by the storage issue and running an older, MySQL, version, SHOW EVENTS works as expected and returns no results)

After restarting MariaDB, SHOW TABLES now fails with Error Code: 1577. Cannot proceed because system tables used by Event Scheduler were found damaged at server start. SELECT * from mysql.event still works, returning 0 rows.

mysqlcheck --all-databases --check-upgrade -p returns "OK" for all tables, including mysql.event.

Best Answer

Firstly, get the mysql.event table working. (Consider it working if SELECT * from mysql.event works). Try these in this order (check after each step)

  • REPAIR TABLE mysql.event
  • mysqlcheck mysql -p # use the repair options if it finds issues
  • myisamchk /var/lib/mysql/mysql/event.MYI # Path is installation and configuration dependant. Repair options needed when it finds issues.
  • DROPping and recreating mysql.event (get the statement with SHOW CREATE TABLE mysql.event; on a working version of the same MariaDB / MySQL version, or from this answer)

Restart the database and check is there is still and error. (After a SELECT works)

With mysql.event working (If a SELECT on it works) and SHOW EVENTS still failing:

  • mysql_upgrade -p --force # This seems to be the only way to get rid of the Error Code: 1577 message, at least with a different version's mysql.event CREATE statement to recreate it
  • Restart MySQL / MariaDB

SHOW EVENTS should now work. Depending on how bad the issue was in the first place, and which of the first steps was necessary to fix it, some or all of your events might be missing.

If you had any events defined, check that they are present and restore them from a backup (or an export from the master, if relevant).

Related Question