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.

But why are they different? I just said all MySql database columns were returned as strings. Well, it looks like some things in PHP have changed in the past decade without me noticing. Since PHP version 5.3, if your PHP installation was compiled with the mysqlnd driver, the following is true: “mysqlnd returns native data types when using Server-side Prepared Statements, for example an INT column is returned as an integer variable not as a string.”

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.

Development Configuration of PDO
Development Configuration of PDO
Production Configuration of PDO
Production Configuration of PDO

So, the fix for me is to disable mysqlnd locally in favor of libmysql, or get the server guys to install mysqlnd on production … but what’s that? “Choosing a library is a compile time decision.”

Aw geez. Sys admins aint gonna do that, so I guess I’ll be recompiling my PHP.

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.

As a second side note, here’s a useful discussion on getting it all to work, where “it all” is mysqlnd, PDO, typecasts, etc.

It's only fair to share...Share on FacebookTweet about this on TwitterEmail this to someone

Leave a Reply

Your email address will not be published. Required fields are marked *