mysql 同步停止工作_MySQL事件暂时停止工作

I have some games where users health and other attributes are updated ever couple of minutes using MySQL events. I ran into a problem where eventually the events are no longer being run, the SQL in the event doesn't get executed.

I wasn't sure how else to fix it, so I tried restarting MySQL and that fixes it for awhile. I setup MySQL to restart every night in cron, but that's not a very good solution. Sometimes MySQL fails to restart and hangs.

Edit: All of the tables in my databases that use the events are InnoDB.

解决方案

It could be that you have events that are not completing and holding many locks. Eventually additional jobs will "stack up" each trying to acquire locks but appearing to do no work. This can be especially true if you are using MyISAM tables as they have table level, not row level locking.

Consider configuring pt-stalk (part of the Percona Toolkit) to capture regular snapshots of 'show processlist' and other important details. Then you can track down when things "stop working" and work backwords to when the problem started.

To prevent jobs from "stacking up" use the GET_LOCK function:

SELECT GET_LOCK('THIS_IS_A_NAMED_LOCK', 0) INTO @got_lock;

IF @got_lock = 1 THEN

select 'do something here';

SELECT RELEASE_LOCK('THIS_IS_A_NAMED_LOCK') INTO @discard;

END IF;

If you are using InnoDB, make sure that you issue START TRANSACTION and COMMIT commands in your event to ensure that you are not creating long running transactions.