MySQL 8.0 & PHP on RedHat, CentOS and Fedora
https://ift.tt/2EGGjbo
As you could read in this previous post, PHP 7.4 is now completely supporting MySQL 8.0 and the new default authentication plugin.
I wanted to make a summary table providing and overview of all PHP versions and how they supported MySQL 8.0 and the different authentication plugins.
As I am a RPM based distribution user, I’m using the famous repository of remi since a lot of years, and I use it then also to install PHP 7.4.0 and 7.4.1…
I created a new user to test to connect with PHP and then… I was surprised to see that I could not connect to MySQL using caching_sha2_password. Of course I tried to see if my credentials were correct using the MySQL client… and I could connect… Then I tried again my PHP script and new surprise, I could connect !?!
I could connect because the password was cached. If I run FLUSH PRIVILEGES, then the PHP script could not connect anymore.
The error was:
Trying with caching_sha2_password.... PHP Warning: mysqli::__construct(): (HY000/1045): Access denied for user 'fred_secure'@'mysql-dc1-2' (using password: YES)
I discussed this with my colleagues. They tried ith the same PHP version and they could not reproduce my error… but they were using Ubuntu.
What’s wrong ?
So I decided to compile from scratch PHP 7.4 on my CentOS 8 box… and… it worked as expected !
After a lot of debugging, testing many openSSL versions and compilation more than 10 times PHP… I was able to find the difference and compile a rpm based on Remi‘s spec file.
The problem was in mysqli.so.
I don’t explain yet why this is a problem, and I already reported this to my colleagues, but the difference between Ubuntu packages and my compiled from scratch version and the one installed from Remi’s repo, is the absence of value for mysqli.default_socket:
mysqli.default_socket => no value => no value
So, I’ve rebuild Remi’s package removing --with-mysql-sock=%{mysql_sock} \ and it worked !
I will now wait for feedback from the developers to understand the reason and see if this is a bug. However, if you want already to use PHP 7.4.1 and MySQL 8.0 on any RedHat based distribution, you will need to have a new php74-php-mysqlnd package.
You can download this one for el8 (RedHat, Oracle Linux and CentOS):
The package is built in way that you don’t need to update all PHP 7.4 packages, but only the mysqlnd one, like this:
rpm -Uvh php74-php-mysqlnd-7.4.1-2.el8.remi.x86_64.rpm
Verifying… ################# [100%]
Preparing… ################# [100%]
Updating / installing…
1:php74-php-mysqlnd-7.4.1-2.el8.rem################# [ 50%]
Cleaning up / removing…
2:php74-php-mysqlnd-7.4.1-1.el8.rem################# [100%]
I hope this can help you if you faced some authentication issue with PHP 7.4 and MySQL 8.0.
technology
via Planet MySQL https://ift.tt/2iO8Ob8
December 22, 2019 at 07:17PM