How to Run PHP 5 Applications with MySQL 8.0 on CentOS 7

Despite the fact that PHP 5 has reached end-of-life, there are still legacy applications built on top of it that need to run in production or test environments. If you are installing PHP packages via operating system repository, there is still a chance you will end up with PHP 5 packages, e.g. CentOS 7 operating system. Having said that, there is always a way to make your legacy applications run with the newer database versions, and thus take advantage of new features.

In this blog post, we’ll walk you through how we can run PHP 5 applications with the latest version of MySQL 8.0 on CentOS 7 operating system. This blog is based on actual experience with an internal project that required PHP 5 application to be running alongside our new MySQL 8.0 in a new environment. Note that it would work best to run the latest version of PHP 7 alongside MySQL 8.0 to take advantage of all of the significant improvements introduced in the newer versions.

PHP and MySQL on CentOS 7

First of all, let’s see what files are being provided by php-mysql package:

$ cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
$ repoquery -q -l --plugins php-mysql
/etc/php.d/mysql.ini
/etc/php.d/mysqli.ini
/etc/php.d/pdo_mysql.ini
/usr/lib64/php/modules/mysql.so
/usr/lib64/php/modules/mysqli.so
/usr/lib64/php/modules/pdo_mysql.so

By default, if we installed the standard LAMP stack components come with CentOS 7, for example:

$ yum install -y httpd php php-mysql php-gd php-curl mod_ssl

You would get the following related packages installed:

$ rpm -qa | egrep 'php-mysql|mysql|maria'
php-mysql-5.4.16-46.el7.x86_64
mariadb-5.5.60-1.el7_5.x86_64
mariadb-libs-5.5.60-1.el7_5.x86_64
mariadb-server-5.5.60-1.el7_5.x86_64

The following MySQL-related modules will then be loaded into PHP:

$ php -m | grep mysql
mysql
mysqli
pdo_mysql

When looking at the API version reported by phpinfo() for MySQL-related clients, they are all matched to the MariaDB version that we have installed:

$ php -i | egrep -i 'client.*version'
Client API version => 5.5.60-MariaDB
Client API library version => 5.5.60-MariaDB
Client API header version => 5.5.60-MariaDB
Client API version => 5.5.60-MariaDB

At this point, we can conclude that the installed php-mysql module is built and compatible with MariaDB 5.5.60.

Installing MySQL 8.0

However, in this project, we are required to run on MySQL 8.0 so we chose Percona Server 8.0 to replace the default existing MariaDB installation we have on that server. To do that, we have to install Percona Repository and enable the Percona Server 8.0 repository:

$ yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
$ percona-release setup ps80
$ yum install percona-server-server

However, we got the following error after running the very last command:

--> Finished Dependency Resolution
Error: Package: 1:mariadb-5.5.60-1.el7_5.x86_64 (@base)
           Requires: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Removing: 1:mariadb-libs-5.5.60-1.el7_5.x86_64 (@anaconda)
               mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Obsoleted By: percona-server-shared-compat-8.0.15-6.1.el7.x86_64 (ps-80-release-x86_64)
               Not found
Error: Package: 1:mariadb-server-5.5.60-1.el7_5.x86_64 (@base)
           Requires: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Removing: 1:mariadb-libs-5.5.60-1.el7_5.x86_64 (@anaconda)
               mariadb-libs(x86-64) = 1:5.5.60-1.el7_5
           Obsoleted By: percona-server-shared-compat-8.0.15-6.1.el7.x86_64 (ps-80-release-x86_64)
               Not found
 You could try using --skip-broken to work around the problem
 You could try running: rpm -Va --nofiles --nodigest

The above simply means that the Percona Server shared compat package shall obsolete the mariadb-libs-5.5.60, which is required by the already installed mariadb-server packages. Since this is a plain new server, removing the existing MariaDB packages is not a big issue. Let’s remove them first and then try to install the Percona Server 8.0 once more:

$ yum remove mariadb mariadb-libs
...
Resolving Dependencies
--> Running transaction check
---> Package mariadb-libs.x86_64 1:5.5.60-1.el7_5 will be erased
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18()(64bit) for package: php-mysql-5.4.16-46.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: perl-DBD-MySQL-4.023-6.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: 2:postfix-2.10.1-7.el7.x86_64
--> Processing Dependency: libmysqlclient.so.18(libmysqlclient_18)(64bit) for package: php-mysql-5.4.16-46.el7.x86_64
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.60-1.el7_5 for package: 1:mariadb-5.5.60-1.el7_5.x86_64
---> Package mariadb-server.x86_64 1:5.5.60-1.el7_5 will be erased
--> Running transaction check
---> Package mariadb.x86_64 1:5.5.60-1.el7_5 will be erased
---> Package perl-DBD-MySQL.x86_64 0:4.023-6.el7 will be erased
---> Package php-mysql.x86_64 0:5.4.16-46.el7 will be erased
---> Package postfix.x86_64 2:2.10.1-7.el7 will be erased

Removing mariadb-libs will also remove other packages that depend on this from the system. Our primary concern is the php-mysql packages which will be removed because of the dependency on libmysqlclient.so.18 provided by mariadb-libs. We will fix that later.

After that, we should be able to install Percona Server 8.0 without error:

$ yum install percona-server-server

At this point, here are MySQL related packages that we have in the server:

$ rpm -qa | egrep 'php-mysql|mysql|maria|percona'
percona-server-client-8.0.15-6.1.el7.x86_64
percona-server-shared-8.0.15-6.1.el7.x86_64
percona-server-server-8.0.15-6.1.el7.x86_64
percona-release-1.0-11.noarch
percona-server-shared-compat-8.0.15-6.1.el7.x86_64

Notice that we don’t have php-mysql packages that provide modules to connect our PHP application with our freshly installed Percona Server 8.0 server. We can confirm this by checking the loaded PHP module. You should get empty output with the following command:

$ php -m | grep mysql

Let’s install it again:

$ yum install php-mysql
$ systemctl restart httpd

Now we do have them and are loaded into PHP:

$ php -m | grep mysql
mysql
mysqli
pdo_mysql

And we can also confirm that by looking at the PHP info via command line:

$ php -i | egrep -i 'client.*version'
Client API version => 5.6.28-76.1
Client API library version => 5.6.28-76.1
Client API header version => 5.5.60-MariaDB
Client API version => 5.6.28-76.1

Notice the difference on the Client API library version and the API header version. We will see the after affect of that later during the test.

Let’s start our MySQL 8.0 server to test out our PHP5 application. Since we had MariaDB use the datadir in /var/lib/mysql, we have to wipe it out first, re-initialize the datadir, assign proper ownership and start it up:

$ rm -Rf /var/lib/mysql
$ mysqld --initialize
$ chown -Rf mysql:mysql /var/lib/mysql
$ systemctl start mysql

Grab the temporary MySQL root password generated by Percona Server from the MySQL error log file:

$ grep root /var/log/mysqld.log
2019-07-22T06:54:39.250241Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 1wAXsGrISh-D

Use it to login during the first time login of user root@localhost. We have to change the temporary password to something else before we can perform any further action on the server:

$ mysql -uroot -p

mysql> ALTER USER root@localhost IDENTIFIED BY 'myP455w0rD##';

Then, proceed to create our database resources required by our application:

mysql> CREATE SCHEMA testdb;
mysql> CREATE USER testuser@localhost IDENTIFIED BY 'password';
mysql> GRANT ALL PRIVILEGES ON testdb.* TO testuser@localhost;

Once done, import the existing data from backup into the database, or create your database objects manually. Our database is now ready to be used by our application.

Errors and Warnings

In our application, we had a simple test file to make sure the application is able to connect via socket, or in other words, localhost on port 3306 to eliminate all database connections via network. Immediately, we would get the version mismatch warning:

$ php -e test_mysql.php
PHP Warning:  mysqli::mysqli(): Headers and client library minor version mismatch. Headers:50560 Library:50628 in /root/test_mysql.php on line 9

At the same time, you would also encounter the authentication error with php-mysql module:

$ php -e test_mysql.php
PHP Warning:  mysqli::mysqli(): (HY000/2059): Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/lib64/mysql/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory in /root/test_mysql.php on line 9

Or, if you were running with MySQL native driver library (php-mysqlnd), you would get the following error:

$ php -e test_mysql.php
PHP Warning:  mysqli::mysqli(): The server requested authentication method unknown to the client [caching_sha2_password] in /root/test_mysql.php on line 9

Plus, there would be also another issue you would see regarding charset:

PHP Warning:  mysqli::mysqli(): Server sent charset (255) unknown to the client. Please, report to the developers in /root/test_mysql.php on line 9

Solutions and Workarounds

Authentication plugin

Neither php-mysqlnd nor php-mysql library for PHP5 supports the new authentication method for MySQL 8.0. Starting from MySQL 8.0.4 authentication method has been changed to ‘caching_sha2_password’, which offers a more secure password hashing if compare to ‘mysql_native_password’ which default in the previous versions.

To allow backward compatibility on our MySQL 8.0. Inside MySQL configuration file, add the following line under [mysqld] section:

default-authentication-plugin=mysql_native_password

Restart MySQL server and you should be good. If the database user has been created before the above changes e.g, via backup and restore, re-create the user by using DROP USER and CREATE USER statements. MySQL will follow the new default authentication plugin when creating a new user.

Minor version mismatch

With php-mysql package, if we check the library version installed, we would notice the difference:

$ php -i | egrep -i 'client.*version'
Client API version => 5.6.28-76.1
Client API library version => 5.6.28-76.1
Client API header version => 5.5.60-MariaDB
Client API version => 5.6.28-76.1

The PHP library is compiled with MariaDB 5.5.60 libmysqlclient, while the client API version is on version 5.6.28, provided by percona-server-shared-compat package. Despite the warning, you can still get a correct response from the server.

To suppress this warning on library version mismatch, use php-mysqlnd package, which does not depend on MySQL Client Server library (libmysqlclient). This is the recommended way, as stated in MySQL documentation.

To replace php-mysql library with php-mysqlnd, simply run:

$ yum remove php-mysql
$ yum install php-mysqlnd
$ systemctl restart httpd

If replacing php-mysql is not an option, the last resort is to compile PHP with MySQL 8.0 Client Server library (libmysqlclient) manually and copy the compiled library files into /usr/lib64/php/modules/ directory, replacing the old mysqli.so, mysql.so and pdo_mysql.so. This is a bit of a hassle with small chance of success rate, mostly due to deprecated dependencies of header files in the current MySQL version. Knowledge of programming is required to work around that.

Incompatible Charset

Starting from MySQL 8.0.1, MySQL has changed the default character set from latin1 to utf8mb4. The utf8mb4 character set is useful because nowadays the database has to store not only language characters but also symbols, newly introduced emojis, and so on. Charset utf8mb4 is UTF-8 encoding of the Unicode character set using one to four bytes per character, if compared to the standard utf8 (a.k.a utf8mb3) which using one to three bytes per character.

Many legacy applications were not built on top of utf8mb4 character set. So it would be good if we change the character setting for MySQL server to something understandable by our legacy PHP driver. Add the following two lines into MySQL configuration under [mysqld] section:

collation-server = utf8_unicode_ci
character-set-server = utf8

Optionally, you can also add the following lines into MySQL configuration file to streamline all client access to use utf8:

[client]
default-character-set=utf8

[mysql]
default-character-set=utf8

Don’t forget to restart the MySQL server for the changes to take effect. At this point, our application should be getting along with MySQL 8.0.

That’s it for now. Do share any feedback with us in the comments section if you have any other issues moving legacy applications to MySQL 8.0.