PSA: Please verify PHP extension "mysqli"

2016-07-19 18:17
Written by
totten - member of the CiviCRM community - view blog guidelines

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.


Anonymous (not verified)
2016-07-21 - 09:56

we're using Amazon Linux, and found mysqli to be already installed.

PHP was installed with the following command:

yum install mysql55-server php56-mysqlnd php56-opcache httpd24 httpd24-tools mod24_ssl php56-fpm

Hope this helps.

I've set up three instances of LAMP / WordPress / CiviCRM on the AWS infrastructure.  Checked each of them using the php command line, above.  In each instance, found mysqli installed, as expected.  PHP installation per default apg-get install LAMP^ -- which has worked just fine for me.    Best wishes!

Anonymous (not verified)
2016-07-22 - 15:15

We have several servers of varying ages, varying OSs, set up in different ways, and have varying versions of PHP installed (although all are at least PHP 5.5x currently). mysqli is installed on all of them. Hope this helps.

Have run the command-line check on approx. 40 web servers, mostly running Debian (various versions, one or two using dotdeb packages) plus a CentOS/cPanel/WHM. All report mysqli as present. On the cPanel server, when EasyApache was built, mysqli had to be explicitly selected.

Anonymous (not verified)
2016-08-26 - 05:11

I have several sites with PHP 7.0.7  - Nginx on Amazon Linux. The only way so far that I have gotten phpMyAdmin 4.6.2 working is to install mysqli, then remove mysql and install mysqlnd. Without mysql CiviCrm install fails. If anyone has a work-around I would like to know. CiviCrm definitely needs to move to mysqli post haste.

Anonymous (not verified)
2016-09-07 - 22:50

It would be necessary to be careful about installing PHP extension mysqli and make sure that you have done server configuration correctly.