The problem
A scheduled script collected brands' product details from XML feeds, processed them and loaded them into a MySQL database. Then new products stopped appearing.
Nothing looked wrong. The script kept running without errors, and experienced developers checked the code and logs without finding a cause.
The cause
The clue was in the data, not the code. The values in the table's primary key column had reached 2,147,483,647, the highest number a standard MySQL INT column can hold. That's about 2.1 billion.
Once an ID column reaches its limit, the table can't accept new rows. The fault wasn't in the code, which is why reading the code didn't help.
The fix
The fix was to:
- Create a new table with a BIGINT primary key, which allows far larger numbers.
- Copy the existing data across.
- Swap the new table in for the original.
Imports resumed straight away and the project stayed on schedule.
How to avoid it
- Check how close your large tables are. Compare the highest ID in each busy table with the limit for its column type. Tables fed by automated imports can grow faster than anyone expects.
- Watch the result, not just the job. A job that finishes is not the same as a job that worked. Alert on what matters, such as no new rows arriving when they should.
- Choose BIGINT early for tables that will grow quickly. It's a small decision at the start and a bigger job later.
- Test the swap first. Changing a large table takes planning, so rehearse on a copy and take a backup.
When it's worth a second look
If a process looks healthy but its output has stopped changing, look at the data itself. Patterns in the values can reveal what logs don't.
This article draws on work by Syed Quadri, SMART5's director, while working at another organisation. It wasn't a SMART5 client project, so the organisation isn't named.