fuck that tank fuck everyone who plays it and fuck who ever thought it would be a good addition to the game. I hope everyone that plays it get pummeled by m44 heat and the person who thought this cancer tank would be a good idea gets fired.
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: 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