I had a interesting problem from Yahoo! mail team that they were not able to start the 5.1 server at all on couple of their replication setup boxes. They tried 5.1.20 , 21 and 22 and all are behaving in the same way.

After digging into the box, the mysql error log has the following info that it was failed to open event table.

1
2
3
4
5
6
7
8
9

071113 10:09:28 [ERROR] Event Scheduler: Failed to open table mysql.event
071113 10:09:28 [ERROR] Event Scheduler: Error while loading from disk.
071113 10:09:28 [Note] Event Scheduler: Purging the queue. 0 events
071113 10:09:28 [ERROR] Aborting
 
Creating new mysql user for ymon and ysar...
071113 10:09:28  InnoDB: Starting shutdown...
071113 10:09:30  InnoDB: Shutdown completed; log sequence number 0 46410
071113 10:09:30 [Note] /mysql/libexec64/mysqld: Shutdown complete

And this happened only on slave server and master server seemed to run fine; and after fighting a while figured out that this happens only when the my.cnf had read_only variable specified as all the slaves are acting as read_only servers.

And searching through MySQL Bug System, found that this is a existing bug in recent MySQL 5.1 version and already fixed and will be available from 5.1.23 on wards. To get around, I suggested the following alternative ways till we have the patched 5.1.22 or new 5.1.23 version.

  • Add EVENT_SCHEDULER=DISABLED in the my.cnf under [mysqld] section along with READ_ONLY option enabled; which will disable the event scheduler and thus will not open any event system files. 
    1
2
3
4
5
6
7
8

    [vanugant@pkgbuild7:t5 ~]$ grep event /mysql/etc/my.cnf
event_scheduler=disabled
[vanugant@pkgbuild7:t5 ~]$ mysql -e "show variables like 'event%'"
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| event_scheduler | DISABLED |
+-----------------+----------+
  • Start the server as non-read only (without read_only in my.cnf) and once the server is up; set the global read_only flag using 
    1
2
3
4
5
6
7
8
9
10

    mysql> set global read_only=1;
Query OK, 0 rows affected (0.00 sec)
 
mysql> show variables like 'READ_ONLY';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| read_only     | ON    |
+---------------+-------+
1 row in set (0.01 sec)
  • Another way is to completely remove the event.* files from the mysql system data directory; even though this is not recommended.