Skip to content

End of life planning for MySQL server 5.0 - 5.6

Overview

This issue aims to consolidate the range of supported versions of MySQL.

At time of writing, all versions of MySQL from 5.5 and below are have reached their "end of life" (i.e. Oracle's "Extended Support Period" with security updates has ended). MySQL 5.6 will be EOL in Feb 2021. Decent summary: https://dba.stackexchange.com/questions/234261/mysql-5-7-community-server-end-of-life-support

Initial Status (March 2020)

Regarding the declared requirements:

  • In CRM/Utils/Check, there appears to be no specific assertion about the MySQL server version.
  • In CRM/Upgrade, there appears to be no specific assertion about the MySQL server version.
  • In Civi/Install and install/, there does appear to be an assertion that MySQL is at least 5.1.
  • NOTE: There are some assertions about the MySQL client version, but that's a separate matter.
  • In the automated test systems, we run tests against MySQL 5.5 (min), 5.7 (dfl/max), and 8.0 (edge).
  • In the Sysadmin Guide: Requirements, it says: "Your MySQL version should be 5.7.5 or greater or MariaDB 10.0.2 or greater. CiviCRM does still work on older versions but there is some functionality missing, notably around ensuring various database actions don't compete for the same resources."

Regarding actual usage: stats.civicrm.org reports the 10 most popular DBs, but it doesn't report on the less used ones. So I dug into the data for a list of sites that have pinged back within the past year to summarize their current/latest DB version. (Queries: https://gist.github.com/totten/cc22772673579d878601b728f446518a)

This shows overall percentages:

+-------+----------+----------+
| MySQL | site_cnt | site_pct |
+-------+----------+----------+
| 5.0   |        5 |   0.0362 |
| 5.1   |      141 |   1.0214 |
| 5.5   |     1666 |  12.0690 |
| 5.6   |     1856 |  13.4454 |
| 5.7   |     4509 |  32.6644 |
| 8.0   |        7 |   0.0507 |
| 10.0  |      807 |   5.8461 |
| 10.1  |     2333 |  16.9009 |
| 10.2  |     1334 |   9.6639 |
| 10.3  |      908 |   6.5778 |
| 10.4  |      238 |   1.7241 |
+-------+----------+----------+

(Note: To simplify the results, I've made no distinction between MySQL / MariaDB / Percona. Within the 5.x series, the numbers should be broadly comparable. Outside 5.x, they diverge more, but you can easily discern MySQL 8.x and MariaDB 10.x.)

Of course, sites with old versions of MySQL often have old versions of Civi. Folks on Civi 3.x probably don't care too much how Civi 5.x handles the MySQL deprecation/EOL process, but folks on Civi 5.x may. Let's break down the usage further.

+------+-------+----------+----------+
| Civi | MySQL | site_cnt | site_pct |
+------+-------+----------+----------+
| 2.x  | 5.5   |        1 |   0.0072 |
| 3.x  | 5.0   |        2 |   0.0145 |
| 3.x  | 5.1   |        2 |   0.0145 |
| 3.x  | 5.5   |       22 |   0.1594 |
| 4.x  | 5.0   |        3 |   0.0217 |
| 4.x  | 5.1   |      111 |   0.8041 |
| 4.x  | 5.5   |      651 |   4.7160 |
| 5.x  | 5.1   |       28 |   0.2028 |
| 5.x  | 5.5   |      992 |   7.1863 |
+------+-------+----------+----------+

Additional notes:

  • I don't want to overload with another chart, but FYI those Civi 5.x users are actually quite spread around (5.0 through 5.22+.)
  • The last Ubuntu with MySQL 5.5 as standard was released in 2014. See Distrowatch summary for more details.

Proposal

Over the next few months, incrementally phase out support for versions of MySQL 5.5 and earlier. We can move more aggressively with MySQL 5.0-5.4 because they've been out of circulation longer and have far fewer users.

  • Circa Civi 5.24 (Apr 2020) or 5.25 (May 2020): Deprecate MySQL 5.1 - 5.5.
    • The status-check, installer, and upgrader should all warn if a site uses MySQL 5.5 or lower. Recommend upgrades to MySQL 5.7.
    • Note: ~7% of current sites run Civi 5.x with an affected MySQL release.
  • Circa Civi 5.26 (Jun 2020): Remove MySQL 5.1 - 5.4.
    • The installer and upgrader should no longer allow deployments on MySQL 5.1 - 5.4.
    • Note: ~0.2% of current sites run Civi 5.x with an affected MySQL release.
    • Note: The current ESR is 5.21. We might aim for the next ESR (circa 5.27) to be in this bucket - being the last to support MySQL 5.5.
  • Circa Civi 5.28 (Aug 2020) or 5.29 (Sep 2020): Remove MySQL 5.5.
    • The installer and upgrader should no longer allow deployments on MySQL 5.5.
    • Note: ~7% of current sites run Civi 5.x with an affected MySQL release. This would work out to ~5 months notice.
    • Update automated testing so that the lowest tested version is MySQL 5.6.

That basically gets us caught-up so that the minimum matches the oldest supported version of MySQL.

Looking forward, upstream is marking MySQL 5.6 EOL in Feb 2021. Additional measures to prep for that could be:

  • Circa Civi 5.26 (Jun 2020): Deprecate MySQL 5.6.
    • The status-check, installer, and upgrader should all warn if a site uses MySQL 5.6 or lower. Recommend upgrades to MySQL 5.7.
    • Note: At time of writing, ~13% of current sites run Civi 5.x with MySQL 5.6.
  • Circa Civi 5.34 (Feb 2021): Remove MySQL 5.6.
    • The installer and upgrader should no longer allow deployments on MySQL 5.6.
    • Note: At time of writing, ~13% of current sites run Civi 5.x with MySQL 5.6. This would work out to ~7 months notice.
    • Update automated testing so that the lowest tested version is MySQL 5.7.

Comments

I'm not quite certain about the growing divergence between MySQL and MariaDB. My hope is that it doesn't matter much while the minimum is 5.6 and lower, but it could be more of an issue when the minimums get to 5.7 and beyond. At that point, we'll probably have to revise the test matrix to run 3 or 4 different DBMS variants.

Edited by totten
To upload designs, you'll need to enable LFS and have an admin enable hashed storage. More information