Jump to content
Never

The 0 Recent WN8 Bug

Recommended Posts

Hello,

So a few days ago we ran into a big problem. Players from the NA server were no longer getting their WN8 values. These issues sometimes happen to one or two players every few days, and it fixes itself after the next stats update, but this time it was affecting everyone from NA, and it didn't fix itself with time.

To debug the issue, I put in a piece of code that displays any errors from the database queries. If anything was going wrong during insertion or recovery of stuff from the database, that would show. And it did, in the table that holds the records of a player's individual tanks:

'SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2147483647' for key 'PRIMARY''

So the programmers among us probably know that number. For everyone else, that's the maximum value that a signed 32 bit INTEGER variable (used to store whole numbers) can hold. This PRIMARY key in particular was the ID, a unique identifier for all records added to the database. When I first created WoTLabs, I set the ID up as was normal: Signed 32 bit integer, with AUTO_INCREMENT, a column attribute that keeps track of the latest ID added, and automatically increases it for the next record insertion.

So things were going normal, records were being added every day by the hundreds of thousands, and this ID value kept going up and up and up. The way this table works is simple. When you request your statistics, the API returns a list of tanks. For every tank in that list, the system adds a record to the table with the relevant information. It does this every time it updates. To put it into perspective, I took a look at how many individual tank records my account has: Over 400,000.

Then, a few days ago, the ID value finally hit the magic number. Since it can't hold a bigger number, the AUTO_INCREMENT couldn't raise it any farther, so it kept trying to add new records with the same ID. Problem is that, being a PRIMARY key, the ID has to be unique.

Now the reason this affects the stats: The system is not set up as an all or nothing deal. It first updates your overall statistics to the most recent ones. Then it inserts a record into the account_stats table, which holds your historical data for your entire account. Then it adds your individual tanks. If the process fails in the individual tanks part, it already did the other two. As far as the system is aware, your account is updated normally. When you look up your stats, the system looks for the corresponding individual tank information. In this case, it can't find it because it was never added, resulting in stats at 0.

So how was it fixed?

I had a few options, but most of them required a LOT of time. Since the table is so big (over 2 billion records), anything I do that would cause it to rebuild (nearly everything) would take days.

First option would be to change the ID from a signed 32 bit integer to an unsigned 32 bit integer, which would give me 2 billion more records or so.
Second option would be to change it to a BIGIT, which would give me 6 billion more records or so.
Third option wold be to completely remove the ID column, since it was pretty much useless.
Fourth option would be to reset the AUTO_INCREMENT value to 1, since because of a very old issue, the first records had an ID of over 100 million, so this would give me 100 million more records or so.
Fifth option would be to create a new table for the individual tank records, without an auto incrementing ID to prevent this problem from happening again, and use the old one for historical queries only.

I first tried the second option. After 20 hours, I noticed that the operation was causing the database size to increase, and it was going to take up all the space I had, so I cancelled the operation and went back to the drawing board.

Next I considered the third option, but it would run into the same problem that the second option did. Same for the first and fourth options.

So the only option left was the fifth option. I already had a table that held detailed information for a player's tanks. Basically the same as the old table, but it included all of a tank's statistics and the records were updated instead of a new one being added at every update.

So I adapted that table, added some things that I needed to add, changed some things I needed to change, and the table was ready to go. Next, I updated the code to include some logic that decides which table it should check for the individual tank statistics and there we go.

Remember kids, when planning your database, consider the fact that your website might explode in popularity and you might have more records in your database than you would have in your wildest dreams, so account for that :P

Link to post
Share on other sites

bloody hell, that sounds like a massive clusterfuck lol.

but hey, a problem created by your site being too popular is probably a good problem to have, right?

Link to post
Share on other sites

This post is a cleverly disguised humble brag :party:

 

On a seperate note, I have 4000 wn8 from today despite being clubbed by arty 5 matches in a row....is this what unihood feels like?

Link to post
Share on other sites

Thanks for the postmortem. It was an interesting read. I hadn't put much that into those drawbacks of a auto-increment integer primary key.

Link to post
Share on other sites

Nice, now we can play internet tonkz until the heatdeath of the universe and don't have to worry about silly integer sizes. A properly engineered solution, I approve.

Link to post
Share on other sites

Nice write up, and I know a fair amount of SQL, but I'm not all that familiar with the limitations of MySQL.  I would think that renaming the table, creating a new table with same structure but with bigint, then creating a view with the name of the same name as the original table that does a union all would solve the problem immediately.  All code would reference the view and work with the unioned tables just fine -- depending on if mysql supports updatable views or not, you may need to write a before trigger to manually handle it, and you could then kick off a process to slowly move the records from the old table to new in the background in which case you can then just drop the view and original table.

Not sure why you have such a big hole in your records unless you are doing DELETE/INSERT (which generates a new identity) instead of doing an UPDATE (which would remain the same).  DELETE/INSERT can cause you to hit that limit fairly quickly.

Also I suspect that the database size growth during your attempt at #2 was only going to be temporary is it needed to keep a complete copy of both the old table and the table you were inserting into until the operation completed.  Then it would have released the space used by the original table, but if you don't have room for both, it could be a problem unless you moved the records in batches at a time.

Just my $.02.

SQL (assuming your table is called tanks):

RENAME TABLE Tanks TO TanksOld; --Rename

CREATE TABLE TanksNew SELECT * FROM TanksOld; -- Create TanksNew

ALTER TABLE TanksNew ALTER COLUMN ID BIGINT UNSIGNED; -- Make TanksNew ID bigger

CREATE VIEW Tanks AS SELECT * FROM TanksOld UNION ALL SELECT * FROM TanksNew; -- Create view, however it would be better if you listed all columns instead of *

At this point your database is back up and running in 2-3 seconds. and you can move the records from TanksOld to TanksNew as you want to in the background.

 

** A quick google fu tells me that MySQL doesn't support updatable views that contain UNION ALL (yuck), and doesn't yet support INSTEAD OF triggers (double yuck), so you'd need to change your code to pull from the Tanks view, only insert into TanksNew, and when deleting via ID, delete from both tables.  Which sounds awfully close to what you landed up actually doing at the end anyhow.

 

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...