I’ll never forget this number: 4294967295 (0xffffffff)
Do you guys know what the hell that number is? Theoretically, we can think that; you should know If you’re reading this content. Last month we had a moment when we remembered these limits again.
Have any of you ever exceeded an unsigned integer’s maximum value? While constructing small, medium-sized, and perhaps large projects, we might forget these constraints. But please do not forget that it also has a cost. This was a fantastic time for all of us. Check out what happened.
Please see this content as a reminder to check your relational database table fields before your data becomes enormous.
What happened?
The day was Thursday, close to 18:30. Everything was OK; the scary releases had already been made, they were functioning well, and we were prepared for a long weekend. (Our workweek consists of four days. Go to job openings)
We’d receive an alert notification about a service that keeps transactions. (The most important one is because of directly affects the money flow.) We’ve already forgotten the weekend and started to check what’s wrong. Then we saw the log:
SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '4294967295' for key 'PRIMARY'...The primary key exceeded the data type’s maximum value. The problem was evident, and the solution appeared equally so, yet… We need to see a strong BUT at this stage.
The table size is the main issue in these types of scenarios. The 100TB table cannot be immediately edited like a table with 500 records.
The reason for the issue
Last year, the team changed the table fields, but sadly, this occurred again. So what caused this?
New customers contributed to an increase in data speed. And when compared to the previous year, the old clients made 4–5 times as many transactions this year. This was the primary cause. The planned rising speed was invalid.
Different reasons come about our infrastructure. Using another data structure may be the solution, but this is another story… This case is mainly about MySql.
The solution we implemented quickly.
What would you be able to do if you were in charge of this service? What approach would you take?
Ours involved altering the primary key data type to a significant limit and creating an empty table with the same structure.
The problem was solved, but still, we had to check all databases and all projects that might have potential issues like this. For this reason, I’ve prepared a command script that helped us to review these fields.
A Laravel command helps you to find risky database fields.
To help us, I created a command tool that makes it easier to locate those dangerous columns in the MySQL databases and the tables inside the databases.
Here’s the preview of the Laravel command. You can see the full of it by clicking here.
You can join me in the comments. You can expose your ideas. How would you handle these kinds of cases? Let’s find out some more options.
Thanks for your time and reading.
