The Daily WTF
Follow
CodeSOD: One Last ID
Chris's company faced an unexpected surge in data transfer costs when their web application, hosted on Cloud Provider B, interacted with a MySQL database on Cloud Provider A. They anticipated only gigabytes of data transfer monthly. However, they were experiencing terabytes, exceeding their budget significantly. Investigation revealed that every insert operation was retrieving an enormous amount of data. The root cause was a malformed SQL query: SELECT last_insert_id() FROM some_table_name. This query, instead of simply fetching the last inserted ID, was instructed to return that ID for every single row in the specified table. Since the table contained millions of rows, this resulted in millions of repetitions of the last inserted ID being sent back with each insert. Consequently, each insert operation generated a substantial data transfer of approximately 30MB. Implementing the technical fix was straightforward. However, coordinating the change with the web development company and their hosting environment, including testing and deployment, took six weeks. A portion of this time was spent convincing the web developers that the issue was indeed occurring and was caused by their code.