PHP, JSON, Strings, Integers, Mysqlnd and Matching Environments

Here’s another instance of being wary when your development environment does not exactly match your production environment.

In PHP, for the longest time, when you fetched records from a MySql database, every column, no matter the column type, would be returned as a string. Primary key “id” of 42, type integer? String. Column “pi” of value 3.14159, type decimal? String. This kind of sucked, but it was consistent, and if you knew PHP, you just took it for granted and worked with it.

Now recently at work I built an API, which no one but me uses, and it’s rather naive in that it basically just returns database records encoded as JSON. Here’s a portion of a response from our production server:

Notice the types of “id” and “active”? Strings, since I’m just spitting back database columns (“count” is an integer because that’s not a database column, but a computed variable). Stupid, but it works.

Anyway, like a good boy, I wrote some acceptance tests for this API, but I designed and wrote the tests against my local development box, and only ever ran them on the development box. Hmm ok, tests written, tests work, all is good. But wait, let’s take a look at the response for that same API call on the development box:

Uh oh. Notice “id” and “active” are now integers. Me too, today, when I decided to run my tests on the, uh, test server, and of course the result was … Big Red Fail. I had written my tests expecting integers in those fields, not noticing my development responses were different than the production response.

Mysqlnd is the default, recommended driver by MySql and highly recommended by PHP, but for some reason our production server doesn’t use it (of course not). I never noticed. I did notice the production servers are stuck on PHP 5.4 (came out in 2012), so on my local box I had installed PHP 5.4 using homebrew trying to match environments. But of course my local environment installed and uses myslqnd. Ugh.

As a side note, you can use the JSON_NUMERIC_CHECK flag to silently cast between types when serializing PHP variables as JSON, but that’s gonna blindly, magically convert everything (phone numbers? geographic co-ordinates? invoice numbers?), and maybe, probably, you don’t want that. I tried it. I don’t want that.