1

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.

Andy
  • 1,815
  • 2
  • 22
  • 49
  • 1
    When it gets stuck, try running a `SHOW PROCESSLIST;` query to see if maybe one of the event-triggered queries is blocking. – cdhowie Jul 30 '12 at 21:50
  • When it got stuck I did SHOW PROCESSLIST; There were no queries running. – Andy May 21 '13 at 18:10

2 Answers2

4

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.

Justin Swanhart
  • 1,826
  • 13
  • 15
  • I'm using InnoDB. I don't have START TRANSACTION and COMMIT in any of my event queries so I'll work on adding that. Is it necessary to use GET_LOCK on inserts and updates as well as selects? – Andy May 16 '13 at 18:51
  • Place the get_lock() before the first statement in your event and the release lock after the last statement. put the work in an IF in between so only the event that actually got the lock does the work. – Justin Swanhart May 17 '13 at 03:05
0

I ended up taking my iOS games offline because of this problem recently. I couldn't figure out how to implement the answer by Justin Swanhart. If anyone is interested I can make my php/mysql code available to see if you can fix this problem. Just let me know. andy.triboletti@gmail.com

Andy
  • 1,815
  • 2
  • 22
  • 49