PSA: Please verify PHP extension "mysqli"

To ensure that CiviCRM continues to work with standard, contemporary PHP hosting platforms, a future version may make a subtle change in hosting requirements. We expect this to be mostly seamless; however, we're looking for administrators responsible for sites running a recent CiviCRM (e.g. v4.7+) to spend a few minutes to help ensure a smooth transition.

Q: I'm not too fluent in geek-speak. What should I do?

Forward this to whoever manages your server. If you work with a CiviCRM partner, they may be thinking of you already.

Q: I am fluent in geek-speak. What should I do?

Determine if your web server has the PHP extension mysqli installed. Any of these will tell you:

• Use the server's command-line:
  • Run "php -r 'echo extension_loaded("mysqli") ? "loaded\n" : "missing\n";'".
  • It should display "loaded".
• Use the API Explorer:
  • Login to CiviCRM as administrator.
  • Navigate to "Support => Developer => API Explorer"
  • Choose entity "System" and action "get". Hit "Execute".
  • Search for "mysqli". It should appear under "values => php => extensions".

We expect that most systems will already have mysqli. If your system doesn't, then:

• First, try to enable it.
• Second, report back. How was PHP installed? How did you add mysqli? (You can comment on this blog or on JIRA; if you can't get access, email totten at civicrm dot org and I'll pass along the info).

Q: What's this all about?

PHP 5.0 provided three alternative "extensions" for sending queries to MySQL -- mysql, mysqli, and pdo. CiviCRM has been using the original mysql extension; however, mysql was deprecated in PHP 5.5 (2013) and removed in PHP 7.0 (2015) and Ubuntu 16.04 (2016). On contemporary hosting infrastructure, one must use one of the other drivers (mysqli or pdo). This requires a change in CiviCRM.

Switching from mysql to mysqli should generally be a small matter with little impact on administrators. After all, CiviCRM uses a DB abstraction which supports mysqli. mysqli is part of PHP 5.x and 7.x, and mysqli is used by many other popular PHP applications (such as Joomla). Looking at a few popular PHP packages (from Ubuntu, Debian, CentOS, MAMP), the mysqli extension is readily available. In fact, if you already run CiviCRM using a standard PHP package from Ubuntu, Debian, CentOS, or MAMP, then you should be good to go.

However, CiviCRM is an open product which runs in many different environments, and it's a wild world out there. There's no way to guarantee that all web-hosts have mysqli. For example, if your web-host used the source code for PHP to compile a custom version, then they could have omitted mysqli.

Q: Who is most likely to be affected?

It's hard to say -- our standard telemetry doesn't provide enough detail to properly estimate impacts. Based on informal surveying and discussion of servers running CiviCRM, I've found a single live site which was missing mysqli, leading to an estimate of 0%-5% of sites needing to install mysqli.

In the one case found so far, the affected site appears to run a power-user Linux distribution (which encourages more low-level library optimization). Intuitively, one would expect a bit more risk on bespoke, highly customized hosting platforms (e.g. custom binaries; ports; portage). On the plus side, administrators who use bespoke binaries should have the skills/experience to configure them, and mysqli has been a widely available build option for many years.

Q: Why are you talking about switching to mysqli? Couldn't we add it as an alternative option?

In the long term, supporting both does not make sense. Supporting both requires more complex code, more abstraction, more QA work/infrastructure, more documentation, more questions on StackExchange, etc. In 2017, we don't want users and administrators debating the question, "Which driver should I use?" It's not a high-value topic.

Never-the-less, even though this issue is a pretty small change in the application, it will become increasingly painful as adoption of Ubuntu 16.04 LTS and PHP 7.0 continues to grow. (PHP 7 provides some big improvements that many administrators want.) It would be overkill to take on the overhead of a major upgrade-cycle just to support PHP 7. Making a planned switch is the simplest path -- and for the overwhelming majority of systems, we expect the transition to be seamless.

Q: When will changes be made?

A: Maybe a couple weeks... maybe a few months. CiviCRM uses an open, peer-review process for code changes with regular monthly releases. After a change passes through peer-review, there's a testing period, and then it'll ship the following month. At time of writing, patches have been proposed for CiviCRM 4.7.10 (August 2016), but they have not yet been accepted in review. We're more likely to see them in September or October 2016.

Q: Great, this is an excuse to upgrade all our software right away!

A: That's good enthusiasm, but there's no need to over-do it. Most people shouldn't need any change at all. Even if you do need a change, it should be a pretty small one. mysqli has been a common part of PHP since PHP 5.0.

Under any approach, the main CiviCRM requirements will remain unchanged: PHP 5.3+ (5.5 recommended); MySQL 5.1+; etc.

Q: Why mysqli? Why not pdo?

A: Happenstance. Civi uses a DB abstraction layer (PEAR DB) which already has built-in support for mysqli... but lacks support for pdo. For our purposes, mysqli and pdo provide the same benefits -- but pdo would take more time/energy. That's not a judgment on the quality of either project - just a cost/benefit evaluation for this specific code at time of writing.

Q: Ah, there's another problem you haven't foreseen!

A: That's possible. If you've read this blog and still feel there are unaddressed issues, please follow-up on CRM-17789.

But please bear in mind... regardless of what the final patch looks like... the simplest, quickest, most effective, most paranoid, preventative action you can take is to ensure that mysqli is installed.