Game Dev Blog #2 - Events of Oct 24th

If you were playing Farm RPG around 11:40pm game time on October 24th you might have noticed some issues with getting items and, in general, the entire game feeling wonky.  I'm writing this blog today to go into more detail on what happened that night.

The night of Oct 24th

First off, I was asleep.  Poor timing, but I am usually in bed by 11:15pm most nights.  I say a prayer nightly that all will be well on Farm RPG while I sleep, but in this case... that didn't happen.  The items table "crashed" and any player getting an item they hadn't had before didn't actually get that item.  It affected every part of the game from farming to the wishing well.  Makes sense, since the entire game centers around items and item collecting.

I awoke to about 250 support tickets.  Looking back I'm surprised it wasn't more like 1,000+, but the staff did a great job of damage control during the night hours to ensure people that the issue was known, but not a whole could be done until I woke up.   It made for quite a morning too.  I quickly shut the game off about 7am until I could figure out what had happened.  I thought the database had just crashed as that happens sometimes.  The items table isn't that big with only 3.3 million rows in it.  I've worked with tables with over 100 million rows in the past without issues like this.  So what happened?

What went wrong

OK, so this is technical, but I'll do my best.  The items table contains a row for every unique item per player.  So if you have 100 different items, there's 100 rows.  Each row contains the amount of that item you have.  It's pretty simple really with good indexes (indexes help queries run fast).  There's a numeric ID for each row that just goes up by 1 each time a new row is added.  It's a habit of mine to use keys like this (called a surrogate key).  I took a look at the table first thing and noticed the ID had stopped at 2,147,483,647.

Going deeper here, the ID column is of type (int) in MySQL.  Oh, also it wasn't set to unsigned, so this means the maximum value of the column is 2,147,483,647... so the table had run out of new IDs and thus could no longer accept new rows.  This really simple thing caused a big big mess though.  Stepping back, why was it that high?  2.1 billon when there's only 3 million rows in the table?  The answer here is why I never anticipated this could happen.  I assumed the items table could grow for a very long time, probably the life of the game and this wouldn't ever happen... but it did.

Let's dig deeper

Farm RPG is growing pretty fast.  I've been working on it 6 months and early on I wanted to make sure the items table could be added and updated quickly.  I have a function in the game that adds items to the table.  Let me also mention the items table is the InnoDB engine in MySQL which is very fast.  Originally, the "Give items" function would check the items table to see if the user already had a row for the item.  After some trial and error and testing, I found that I could set a unique key on the table and just do a "INSERT ON DUPLICATE KEY UPDATE" query and take care of the items table update in 1 single query.  It was way faster to do it this way.

If you are still with me, here's why the ID ended up hitting 2.1 billion so much faster than the number of actual rows in the table.   By default with InnoDB, the ID value will "lose" the value if the INSERT fails.  So every time a player was getting an item they already have, the INSERT would fail, but it would fall over to the UPDATE part of the query and go on successfully.  The ID value could skip by 1 every time this happened.  Take that times 8,000 people playing daily and hundreds of thousands of items moving around the game.  It's no wonder the ID reached 2.1 billion as quickly as it did.  This was an educational experience for me as I didn't know that about InnoDB defaults.

The fix

I had a couple options to fix this going forward.  I wanted to get the fix in place as quickly as possible and get the game back online.  At that point, it had been offline for 4-5 hours but was in a broken state for almost 12 hours.   

• Option 1 was to change the InnoDB auto-increment behavior and rebuild the items table with new IDs starting at 1.  
• Option 2 was to change the ID type to (bigint) unsigned and let the IDs start over and just keep going.  The max value in this case is 18,446,744,073,709,551,615

I went with Option 2.  I've got a daily backup of the game database that happens just before midnight every night (and since the event I've got a backup running every 3 hours now).  By lunch-time on October 25th I had the chance to talk this over with staff and we all agreed it would be best to start from the backup, lose the overnight progress and fix the database ID with Option 2.  It didn't take long to do all of that and have the game running again.  However, it was a lose-lose situation because no matter what I did, progress from about 11:40pm to 7am was very much lost for anybody who played then.

So that's what happened....

The rest of the week is history.  I had a lot of support tickets and unhappy players, but I think I got everybody fixed ultimately.  Since then, we've been ok and I'll continue to do my best to prevent that sort of thing from happening ever again.  If you were affected, I am sorry once more and hope you find this post helpful in understanding what went wrong.  Now, back to farming!