1 (edited by nickeo 2016-06-16 16:27:31)

Topic: MySQL connection not working after upgrading to PHP 5.6.22

Hello,

Thanks for your fine packages.

I have upgraded to PHP 5.6.22 successfully on Centos 6, but it didn't work on Centos 5.

Upgrade (using remi-php56 repo) completed, but I always got the error:

Database connection error (2): Could not connect to MySQL.

I couldn't find why it didn't work. I had to downgrade to the old 5.4.45-9 (from remi-php repo). After downgrade, things worked fine again (fortunately).

I did not downgrade MySQL but only php.

Can you please provide an explanation why this happened? MySQL and PHP versions should be compatible.

Can you please advise me on how to retry to upgrade safely?

I had installed the following packages:

Installed:
  php-mysqlnd.x86_64 0:5.6.22-1.el5.remi      

Dependency Installed:
  GeoIP-GeoLite-data.noarch 0:2015.12-1.el5             GeoIP-GeoLite-data-extra.noarch 0:2015.12-1.el5     gd-last.x86_64 0:2.2.1-2.el5.remi
  geoipupdate.x86_64 0:2.2.1-2.el5                      lcms2.x86_64 0:2.7-3.el5                            php-pecl-jsonc.x86_64 0:1.3.10-1.el5.remi.5.6    
  php-pecl-jsonc-devel.x86_64 0:1.3.10-1.el5.remi.5.6   php-pecl-zip.x86_64 0:1.13.2-1.el5.remi.5.6        

Updated:
  GeoIP.x86_64 0:1.6.5-2.el5              GraphicsMagick.x86_64 0:1.3.23-5.el5             GraphicsMagick-doc.x86_64 0:1.3.23-5.el5
  jasper-libs.x86_64 0:1.900.1-15.el5     lua.x86_64 0:5.1.4-4.el5                         mysql.x86_64 0:5.5.50-1.el5.remi
  mysql-libs.x86_64 0:5.5.50-1.el5.remi   mysql-server.x86_64 0:5.5.50-1.el5.remi          openssl.x86_64 0:0.9.8e-40.el5_11
  php.x86_64 0:5.6.22-1.el5.remi          php-bcmath.x86_64 0:5.6.22-1.el5.remi            php-cli.x86_64 0:5.6.22-1.el5.remi
  php-common.x86_64 0:5.6.22-1.el5.remi   php-devel.x86_64 0:5.6.22-1.el5.remi             php-gd.x86_64 0:5.6.22-1.el5.remi
  php-imap.x86_64 0:5.6.22-1.el5.remi     php-intl.x86_64 0:5.6.22-1.el5.remi              php-ldap.x86_64 0:5.6.22-1.el5.remi
  php-mbstring.x86_64 0:5.6.22-1.el5.remi php-mcrypt.x86_64 0:5.6.22-1.el5.remi            php-oci8.x86_64 0:5.6.22-1.el5.remi
  php-pdo.x86_64 0:5.6.22-1.el5.remi      php-pecl-memcache.x86_64 0:3.0.8-3.el5.remi.5.6  php-pecl-pthreads.x86_64 0:2.0.10-1.el5.remi.5.6
  php-pgsql.x86_64 0:5.6.22-1.el5.remi    php-process.x86_64 0:5.6.22-1.el5.remi           php-soap.x86_64 0:5.6.22-1.el5.remi
  php-xml.x86_64 0:5.6.22-1.el5.remi      php-xmlrpc.x86_64 0:5.6.22-1.el5.remi

Replaced:
  php-mysql.x86_64 0:5.4.45-8.el5.remi

Note: I also had problems with MySQL upgrade (on CentOS 5.11 only). I had to run mysql_upgrade to fix things, but this is a different story which does not affect the above issue.

Thanks in advance for any help.

Nick

Re: MySQL connection not working after upgrading to PHP 5.6.22

Since PHP 5.5 we use mysqlnd driver (in php-mysqlnd) instead of libmysqlclient (in php-mysql).

The only case (I'm aware of) where connection is not possible, is for very old database (pre 4.1) which use old password.

Check, if your mysql configuration contains the "old_password=1" option (/etc/my.cnf).
Check the size of the stored password, using

mysql -uroot -pyoursecretpassword mysql -e 'select User,length(Password) from user;'
+----------+------------------+
| User     | length(Password) |
+----------+------------------+
| root     |               41 |
| root     |               41 |
...

If some password are still old (size = 16), you can encounter the issue. In this case the message should be clear

# php -r 'mysqli_connect("localhost", "foo", "bar");'
PHP Warning:  mysqli_connect(): The server requested authentication method unknown to the client [mysql_old_password] in Command line code on line 1
PHP Warning:  mysqli_connect(): (HY000/2054): The server requested authentication method unknown to the client in Command line code on line 1

- fix mysql configuration if needed (old_password=0) and restart
- change the password
- check the size again


P.S.  please remind that EL-5 is nearly ~10 years old, and running modern software is not so easy. I recommend you consider a more modern version, which will be mandatory soon (EL-5 will be EOL in March 2017)

Laptop:  Fedora 38 + rpmfusion + remi (SCL only)
x86_64 builder: Fedora 39 + rpmfusion + remi-test
aarch64 builder: RHEL 9 with EPEL
Hosting Server: CentOS 8 Stream with EPEL, rpmfusion, remi

Re: MySQL connection not working after upgrading to PHP 5.6.22

Thank you very much for your advice.

It seems the case is exactly as you describe. We have old_password = 1 and all password sizes are 16.

It was a really tricky issue. Without your help I wouldn't be able to figure it out.

Would we have to change each and every password to move to new size (41)? Isn't there some way to change password size to 41 for all existing passwords (e.g. through a script)?

We are planning to gradually upgrade our CentOS 5 production infrastructure, but some things simply take time esp. when there is no manpower and time... sad

Re: MySQL connection not working after upgrading to PHP 5.6.22

> Would we have to change each and every password to move to new size (41)?

Yes (well, at least the ones used from PHP app)

> Isn't there some way to change password size to 41 for all existing passwords (e.g. through a script)?

No, as you need to know the password (and only the hash is stored).

Laptop:  Fedora 38 + rpmfusion + remi (SCL only)
x86_64 builder: Fedora 39 + rpmfusion + remi-test
aarch64 builder: RHEL 9 with EPEL
Hosting Server: CentOS 8 Stream with EPEL, rpmfusion, remi

Re: MySQL connection not working after upgrading to PHP 5.6.22

I confirm that after these changes in the database (conversion to new password hash length), upgrade to 5.6.22 was successful. Connection to MySQL went through without problems!

Thank you again Remi for your packages, your repos and your advice!

Case closed.

Re: MySQL connection not working after upgrading to PHP 5.6.22

smile

Laptop:  Fedora 38 + rpmfusion + remi (SCL only)
x86_64 builder: Fedora 39 + rpmfusion + remi-test
aarch64 builder: RHEL 9 with EPEL
Hosting Server: CentOS 8 Stream with EPEL, rpmfusion, remi