This is a submission for DEV’s Summer Bug Smash: Smash Stories powered by Sentry.
Around 2016, I was working as a PHP developer at Racing Post in London. The Post is best known for horse-racing news, form and results, but behind the public-facing products sat a large collection of systems for processing all kinds of racing and greyhound data. The application I worked on used that data to produce periodic reports.
This was the PHP 5 era. The language had already powered serious web applications for years, although many companies also carried the history that accumulated around those applications. Ours depended on Sybase, an enterprise relational database that was once a common sight in large organizations.
PHP did not communicate with Sybase through a neat modern package installed with Composer, it used a native extension written in C. Production ran on Linux, while our local development environments ran in Vagrant virtual machines. At the same time, the company was migrating its source code from Subversion to Git. We had several generations of technology living beside one another, and most days they cooperated. Then production began to fail.
When queries returned nothing
The failures appeared random: a request would reach the application, a database query would run, and the result would be empty. There was no useful error to explain why. The same query could work before the incident and work again later. Restarting PHP restored normal service, but only temporarily.
That last detail was both helpful and misleading. A restart clearing the problem suggested stale process state, a damaged resource or a connection that had outlived its usefulness. It did not tell us which layer owned the problem. From the application, all we could see was a valid-looking call returning no rows.
We began with the code we knew. Reviewed the paths that built and executed the queries and added logging around them. We checked the input data, compared successful and unsuccessful requests, inspected the database schema, and ran the SQL independently. Nothing explained why a query that should return records would occasionally behave as though the records did not exist.
We searched Google and Stack Overflow for anyone reporting the same combination of PHP, Sybase and intermittent empty results. Stack Overflow was the developer companion at the time. If a library behaved strangely, there was a fair chance that somebody had already described the exact failure and received a precise answer from a maintainer eight years earlier.
This time, the familiar search results led nowhere. We found related errors and connection problems, but nothing that matched our symptoms closely enough to trust. The application continued to look correct, and every new log line told us the same incomplete story.
Meeting below the abstraction
Eventually, the investigation reached the boundary most application developers prefer not to cross. Our PHP code asked for rows and received an empty result, but PHP was not doing the database work itself. The native extension sat between the application and Sybase, translating function calls, managing native resources, and turning the database client’s responses into PHP values.
Because the application appeared to behave correctly, we needed to see what happened inside that translation layer. I had studied C at university and had always liked it. That gave me enough familiarity to follow pointers, structs, and return codes, but it did not make me a C expert. Native extension development was certainly not part of my job description. Fortunately, the company had someone who was an expert, so I walked over and asked for help.
Our pairing had an unusual balance. I understood the PHP application, its query flow and the behaviour visible to its callers. He understood the C extension, memory management and the native database API. Each of us could follow only part of the journey on our own. We had to explain our respective layer to one another while tracing a single operation across both. It was an absolute nightmare, although the guy’s patience and likable demeanor made it bearable.
We followed the lifecycle of a query from PHP into C. We examined how the extension acquired a connection, issued a command, and interpreted the status returned by the native client library. We then traced how each native outcome became a PHP result. At that boundary, several distinct events could become indistinguishable to the application unless the extension handled every return code carefully.
A better error, and the same outage
Our first theory was that the driver swallowed a native error. Somewhere below PHP, the database client appeared to be reporting a failure that the extension converted into an empty result. That would account for the application seeing no rows and no exception.
We changed the extension so that the native failure surfaced properly. This was a genuine improvement. An operational failure should never masquerade as a successful query with an empty result set. Of course, application code treats those outcomes differently: an empty result may be valid business data, while a failed query needs reporting, retry logic, or intervention.
The new diagnostics gave us a clearer view, and we deployed the change expecting that we had found the bug. Production failed again.
We had corrected a misleading symptom. The underlying condition still occurred, and the improved error handling merely described it more honestly. That was something, although it did not feel much like progress while the system was still breaking.
The clearer signal sent us back through the connection lifecycle. Instead of concentrating only on query execution, we looked at how the extension obtained the connection on which the query ran. It maintained a pool so that it could reuse database connections. Opening a database connection has a cost, so keeping connections available is a sensible optimisation. It also introduces an important assumption: a connection returned to the pool remains usable when another request takes it out.
It turned out our pool made that assumption too freely, which directed our attention to the state of connections while they waited to be reused.
The dead connection in the pool
On occasion, a pooled connection died while it was idle. The database server, the network or some other part of the surrounding infrastructure could close it, while the local process still held a resource that looked like a connection. Later, the pool handed that resource to the application. The extension attempted to use it, the native call failed, and the original error handling presented that failure as an empty result.
Restarting PHP worked because it destroyed the pool along with the processes that owned it. New processes established fresh connections, so the application recovered until another pooled connection became stale. The apparently magical restart had been resetting the faulty state all along.
The durable (if somewhat ugly) fix was to validate a pooled connection before reusing it. When the pool selected an existing connection, the extension performed a liveness check. A healthy connection could proceed to the query. A dead one was discarded and replaced rather than being returned to the caller.
In simplified pseudocode, the change was conceptually small:
conn = pool.acquire();
if (!alive(conn)) {
conn = reconnect();
}
Enter fullscreen mode Exit fullscreen mode
The production impact was much larger than the simple patch: queries stopped inheriting dead connections, the intermittent failures disappeared, and restarting PHP ceased to be part of the recovery procedure. We also kept the improved error propagation from our first attempt. It had not resolved the incident, but it made the extension more truthful and would make future failures easier to diagnose. People were rejoicing, and the application was finally stable.
And then, not long afterward, the company migrated away from Sybase. After we had spent days following a bug through PHP and C, the stack that produced it disappeared. That timing was slightly painful, but it did not make the work wasted. The lesson survived the technology: when the visible code cannot explain a production failure, follow the operation through every abstraction it depends on. Sometimes the shortest route to fixing an application begins several layers beneath it.
답글 남기기