Josheli
  • Home
  • Blog
    • Knob
    • Running
    • Soccer
    • Technology
  • About
Knob , Technology

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

by dv January 29, 2016 1 Comment

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:

mentoring-response-production

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:

mentoring-response-development

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.

Related Content:

  • Dockerizing PHP apps and deploying to AWS Fargate Part 1 by Dv February 6, 2019 For the day job, I maintain four custom PHP apps in addition to several Drupal sites. Up until a couples…
  • Continuous Deployment: Drupal 8, Composer, Github,… by Dv April 17, 2018 UPDATE: Watch a tech talk I recently gave at work about this process. I've been doing some pretty cool work…
  • MariaDB or MySQL Root Password Struggles on Debian/Ubuntu by Dv March 14, 2018 Had a difficult time authenticating to a newly installed MariaDB 10.1 instance on Debian 9 (Stretch) recently, wasting a couple…
  • Single Sign-on in Caddy Server Using only the… by Dv February 24, 2021 As mentioned, I've started self-hosting a lot of services, and in front of them all I have a reverse proxy…
  • A Blog Too Far by Dv October 10, 2006 Blogging is funny. At least for me, what ends up on the page is never what I originally intended to…
It's only fair to share...Share on facebook
Facebook
Share on twitter
Twitter
Share on email
Email
  • Previous Block Website Visitors by Country using GeoIP on Nginx9 years ago
  • Next Automated Testing with Codeception9 years ago

comments title

  1. scooter says:
    September 20, 2017 at 10:24 am

    Thanks, I keep coming back to this.

Leave a Reply

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

Popular Posts

  • Josheli, What Happened? (55,158)
  • Stupidly Simple, Static, Startpage for Self-hosted Services (30,259)
  • Running a Plex Media Server on an Old Laptop (26,115)
  • Simple Google Photos: A WordPress Plugin (23,000)
  • Three Saturdays (22,605)

Random Read

Poopourri
Time to poop out another post. I'm playing baseball for the 10th year in a…

Read More

Google Photo
Google Photo
Google Photo
Google Photo

Social Things

  • Family Vance
  • Texas Longhorns News

RSS From Familyvance

2025 Josheli. Donna Theme powered by WordPress