Bug #70975 ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler
Submitted: 21 Nov 2013 16:44 Modified: 23 Mar 2017 10:19
Reporter: Simon Mudd (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: Stored Routines Severity:S3 (Non-critical)
Version:5.5.23, 5.5.35, 5.6.16,5.7.17 OS:Any
Assigned to: CPU Architecture:Any
Tags: event_scheduler

[21 Nov 2013 16:44] Simon Mudd
Description:
I wanted to turn off the event_scheduler on a server but get this error:

ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start

Indeed mysql.event had been damaged. REPAIR TABLE mysql.event seemed to work fine.

> check table event;
+-------------+-------+----------+---------------------------------------------------+
| Table       | Op    | Msg_type | Msg_text                                          |
+-------------+-------+----------+---------------------------------------------------+
| mysql.event | check | warning  | Table is marked as crashed and last repair failed |
| mysql.event | check | warning  | Size of datafile is: 500       Should be: 484     |
| mysql.event | check | status   | OK                                                |
+-------------+-------+----------+---------------------------------------------------+
3 rows in set (0.06 sec)

> repair table event;
+-------------+--------+----------+----------+
| Table       | Op     | Msg_type | Msg_text |
+-------------+--------+----------+----------+
| mysql.event | repair | status   | OK       |
+-------------+--------+----------+----------+
1 row in set (0.01 sec)

Nevertheless I still get the error.  This doesn't help either.

> flush tables;
Query OK, 0 rows affected (0.12 sec)

Also did various other things:

> show global variables like 'event_scheduler';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| event_scheduler | DISABLED |
+-----------------+----------+
1 row in set (0.00 sec)

> set global event_scheduler = ON;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start
> set global event_scheduler = OFF;
ERROR 1577 (HY000): Cannot proceed because system tables used by Event Scheduler were found damaged at server start

How to repeat:
See above.

Suggested fix:
It seems unhelpful to _have_ to restart the server if there's a problem in this table, especially if we are able to repair or create it. Yet it seems that once we are in this mode we can't get out.

I know some changes were made to the event scheduler in 5.6 to make it more robust but am unaware as to whether this specific issue was addressed ( I have not seen it before ).

If possible make it a little more forgiving of errors and try to ensure we do not need to restart mysqld just to fix this.

I am not particularly interested in getting this fixed in 5.5 but if the issue could happen in 5.6 then I believe that should be dealt with.
[21 Nov 2013 16:49] Simon Mudd
Note: restarting mysql fixed the problem, but that is rather intrusive and leaves the server cold for a while.
[28 Nov 2013 19:18] Sveta Smirnova
Thank you for the report.

Verified as described using code analysis: flag check_system_tables_error set to TRUE in Events::init in case if table is corrupted and never resets back to FALSE.

I think one can hit other effect of this bug: wrong behavior in case if mysql.events get corrupted after Events::init was called.
[23 Mar 2017 9:51] Simon Mudd
See again today in 5.7.17.
[23 Mar 2017 10:19] Simon Mudd
So on 5.7.17 I see that I get this same error.
* check table currently shows the table as ok
* nevertheless mysqld refuses to use it because:

2017-03-23T10:13:39.267721Z 0 [ERROR] Event Scheduler: An error occurred when initializing system tables. Disabling the Event Scheduler.

* repair table (as described earlier) doesn't work either
* the error message doesn't explain what the problem is.

The files I see under $datadir/mysql are:

[root@somehost mysql]# ls -la | grep event
-rw-r----- 1 mysql mysql    10223 Mar 23 10:55 event.frm
-rw-r----- 1 mysql mysql     6632 Mar 23 11:13 event.MYD
-rw-r----- 1 mysql mysql     4096 Mar 23 11:13 event.MYI
[root@somehost mysql]#

Permissions look ok.

I'm going to steal the files from a working box (as these are MyISAM tables) and see if that resolves the issue.