Bug 252420 - 'caching_sha2_password' support missing in PHP port, limits MySQL 8 compatibility
Summary: 'caching_sha2_password' support missing in PHP port, limits MySQL 8 compatibi...
Status: In Progress
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: amd64 Any
: --- Affects Many People
Assignee: freebsd-ports-bugs (Nobody)
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-01-04 21:02 UTC by Sean McBride
Modified: 2021-02-24 11:31 UTC (History)
2 users (show)

See Also:


Attachments

Note You need to log in before you can comment on or make changes to this bug.
Description Sean McBride 2021-01-04 21:02:29 UTC
Repro:
- install vanilla FreeBSD 12.2-p2 on 64 bit Intel
- install PHP 7.4.13 from ports/packages
- install MySQL 8.0.22 from ports/packages
- install Apache 2.4.46
- set up basic website with PHP
- create typical phpinfo.php file that invokes phpinfo()
- in phpinfo output, look at the "loaded plugins" line of the "mysqlnd" section

Expected:
- 'caching_sha2_password' should be listed.

Actual:
- 'caching_sha2_password' is *not* listed

Notes:
- starting in MySQL 8, the default password scheme is "caching_sha2_password".
- PHP didn't support this at first, see: <https://bugs.php.net/bug.php?id=76243>
- that PHP bug is fixed by 7.4.13 (and even earlier).
- because it's missing, PHP can't talk to MySQL 8 by default, though a workaround is to use 'mysql_native_password'
- Ubuntu 20.04 doesn't have this problem
Comment 1 Torsten Zuehlsdorff freebsd_committer 2021-01-04 22:19:59 UTC
Some more notes:
- there is no patch removing the code
- the code has a "ifdef MYSQLND_HAVE_SSL"
- openssl is no default dependency

Maybe the solution will be to create a dependency to openssl and incorporate it into the build-process of the mysqlnd extension?
Comment 2 Sean McBride 2021-01-04 23:03:54 UTC
Torsten, that's a good hypothesis, and sounds like it could explain the issue!
Comment 3 Sean McBride 2021-01-05 22:07:33 UTC
BTW here's what Ubuntu 20.04 lists in the "loaded plugins" line of the "mysqlnd" section:

mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password,auth_plugin_caching_sha2_password,auth_plugin_sha256_password

vs FreeBSD's PHP 7.4.x:

mysqlnd,debug_trace,auth_plugin_mysql_native_password,auth_plugin_mysql_clear_password

So looks like there are 2 missing...
Comment 4 Markus Kohlmeyer 2021-02-14 14:05:45 UTC
MYSQLND needs to be unset in
lang/php80
databases/php80-mysqli
databases/php80-pdo_mysql


[root@devgate:~] # cat /var/db/ports/lang_php80/options
# This file is auto-generated by 'make config'.
# Options for php80-8.0.2
_OPTIONS_READ=php80-8.0.2
_FILE_COMPLETE_OPTIONS_LIST=CLI CGI FPM EMBED PHPDBG DEBUG DTRACE IPV6 MYSQLND LINKTHR ZTS
OPTIONS_FILE_SET+=CLI
OPTIONS_FILE_SET+=CGI
OPTIONS_FILE_SET+=FPM
OPTIONS_FILE_SET+=EMBED
OPTIONS_FILE_UNSET+=PHPDBG
OPTIONS_FILE_UNSET+=DEBUG
OPTIONS_FILE_UNSET+=DTRACE
OPTIONS_FILE_SET+=IPV6
OPTIONS_FILE_UNSET+=MYSQLND
OPTIONS_FILE_SET+=LINKTHR
OPTIONS_FILE_SET+=ZTS
[root@devgate:~] # cat /var/db/ports/databases_php80-mysqli/options
# This file is auto-generated by 'make config'.
# Options for php80-mysqli-8.0.2
_OPTIONS_READ=php80-mysqli-8.0.2
_FILE_COMPLETE_OPTIONS_LIST=MYSQLND
OPTIONS_FILE_UNSET+=MYSQLND
[root@devgate:~] # cat /var/db/ports/databases_php80-pdo_mysql/options
# This file is auto-generated by 'make config'.
# Options for php80-pdo_mysql-8.0.2
_OPTIONS_READ=php80-pdo_mysql-8.0.2
_FILE_COMPLETE_OPTIONS_LIST=MYSQLND
OPTIONS_FILE_UNSET+=MYSQLND
[root@devgate:~] #
Comment 5 Torsten Zuehlsdorff freebsd_committer 2021-02-19 22:35:41 UTC
Thank you for this suggestion Markus! 

However, I can't reproduce your solution. Are you able to log into a MySQL 8 database using the new auth with it?
Comment 6 Markus Kohlmeyer 2021-02-20 00:30:18 UTC
According to PHP Bugtrcker all known related bugs should be fixed since 7.4.2 (Feb 2020):

https://bugs.php.net/bug.php?id=79011
https://bugs.php.net/bug.php?id=76243
https://bugs.php.net/bug.php?id=78981

So even mysqlnd should be able to use caching_sha2_password since 7.4.2.


I suggested using the native mysqllib as opposed to mysqlnd, as mysqllib comes directly from MySQL and does not have nor had the PHP bugs.
And when mysqllib fails, then every other MySQL-Client inclusive MySQLs own one fails and any related bug would be fixed asap by Oracle.
It's always better to use an original than an imitation...



To answer your question: No, currently i don't have a spare system to test this, but as i wrote above, Oracle would put such bugs on highest (very first) priority as it would effect every single installation out there, not only some crappy third-party client-implementations.



And yes, openssl should be the only (and mandatory) ssl-library to link against (both PHP and MySQL). openssl from base is enough.
Comment 7 Markus Kohlmeyer 2021-02-20 11:34:25 UTC
OK, i migrated one of my systems to use PHP8+MySQL8 (build from ports) with caching_sha2_password and disabled mysqlnd as i proposed earlier.
It works without any problems.

Migrating all my systems now ;)
Comment 8 Torsten Zuehlsdorff freebsd_committer 2021-02-21 00:52:58 UTC
Aloha Markus,

thank you for the test! I will try it also with a full installation. :)

If this works I would propose deactivating the MYSQLND option per default. When I introduced it, I wanted to deactivate it later. So this would be a good time for it. :D

Best,
Torsten
Comment 9 Markus Kohlmeyer 2021-02-21 01:40:01 UTC
But don't forget that it's *required* to link PHP and MySQL against OpenSSL.
Comment 10 Torsten Zuehlsdorff freebsd_committer 2021-02-21 19:28:47 UTC
> But don't forget that it's *required* to link PHP and MySQL against OpenSSL.

What does this mean exactly? That without the MYSQLND option OpenSSL becomes a Build/Run-Dependency?
Comment 11 Markus Kohlmeyer 2021-02-21 20:21:11 UTC
Yes, OpenSSL is a required Run-Dependency and also a required Build-Dependency, as MySQL requires a secure (SSL/TLS encrypted) connection to be used with (caching_)sha2_password and this requires OpenSSL.

https://dev.mysql.com/doc/refman/8.0/en/encrypted-connections.html
https://dev.mysql.com/doc/refman/8.0/en/caching-sha2-pluggable-authentication.html

MYSQLND also requires OpenSSL for the same reasons.


So, OpenSSL is a required Run/Build-Dependency anyway, at least for PHP >=7.4.2.



BTW: In this case OpenSSL means OpenSSL, not LibreSSL or any other SSLlib.
Comment 12 Torsten Zuehlsdorff freebsd_committer 2021-02-23 09:54:26 UTC
Mh... pulling in OpenSSL will break - out of experience - many systems. So it is a bad idea to pull it in on default. Especially since you really do not need it when you do not use MySQL. :D

So, best we can do is to provide an optional switch adding this dependency.
Comment 13 Markus Kohlmeyer 2021-02-23 10:38:50 UTC
I don't see any systems breaking, as PHP as of now has no run/build-dependency and you only need to run/build-depend on OpenSSL from base for PHP-Packages.
Isn't that exactly that, which nearly every other package in tree does?

And mysql_native_password might be removed in future releases of MySQL, so you have to run/build-depend on OpenSSL then anyway.


So, for packaging you have to run/build-depend on OpenSSL from base, and for ports-users like me you can depend on OpenSSL from base or ports and maybe on LibreSSL (at own risk for ports-users).
It's the same as MySQL does, and that also breaks no systems.


In short: I currently don't understand why a dependency on OpenSSL from base should break systems? It doesn't for hundreds of other packages/ports, what is the different in PHP here?
Comment 14 Torsten Zuehlsdorff freebsd_committer 2021-02-23 10:57:24 UTC
Relying on base OpenSSL will break:
* All system with LibreSSL and other SSL Implementations (there are quite a lot)
* Will break PHP when the required OpenSSL version is newer than the one from base (which happens already multiple times)
* Will break when user prefer to use OpenSSL from ports instead from base
[I already tried this with 7.4 and it flooded my mailbox with complaints :D]

Making this a default will also increase system-requirements and import a new security vector (aka more issues).

While PHP+MySQL is a very common combination it's not the only one. There are so many setups that use other databases or none at all. I for example haven't installed MySQL for more than 10 years and work with big databases on a daily routine.

So it's not a simple "just add the dependency and move on". And because of the complex and different usage scenarios, the comparison to many other ports does not work out.
Comment 15 Markus Kohlmeyer 2021-02-23 16:16:07 UTC
OK, so you want to render the MySQL-Extensions from FreeBSDs PHP completly useless by making them incompatible with any defaultinstallation of MySQL >=8?

Remember that caching_sha2_password (which implies OpenSSL) is the standard in MySQL >=8 and without OpenSSL in PHP you can't even connect to standard MySQL-Servers >=8.
Is that realy what you want? How many installations would this affect?



How are other packages which require [Open|Libre]SSL resolving this issue?
Comment 16 Torsten Zuehlsdorff freebsd_committer 2021-02-23 21:41:58 UTC
> OK, so you want to render the MySQL-Extensions from FreeBSDs PHP completly useless by making them incompatible with any defaultinstallation of MySQL >=8?

First: I never stated such a thing. Please re-read my comments.

Second: Following your proposal would render PHP - not only the MySQL - Extensions - completely useless in many scenarios. Do you think it is better to break the full PHP instead of a small number of extensions?

Third: These only cause problems with MySQL 8, which still is not the default version. And the issues can be fixed on MySQL side within any big effort.

Forth: There are other options than breaking PHP in one or more scenarios. And plenty of time to keep care of them.
Comment 17 Markus Kohlmeyer 2021-02-24 00:39:26 UTC
OK, you are the maintainer, so then just do what you think is the right thing.


I tried my best to get this bug fixed in the IMHO correct way, but as i can't see any of the problems that you might see, i'm out for now.


I promise that in a few weeks/months, when there are many broken PHP+MySQL8 systems out there, but at least when MySQL<8 is EoL (which isn't far away), we will have this problem again. Looking forward how you'll fix it then.


Sorry that my try to help waistet that much time.


BTW: You already pull in OpenSSL with at least these PHP-Extensions:
     curl, ftp, imap, oopenssl, snmp
What is the difference to the mysqli/pdo_mysql Extensions?
Comment 18 Torsten Zuehlsdorff freebsd_committer 2021-02-24 00:57:30 UTC
I am sorry that you have the feeling that you wasted time while trying to fix the issue. It is quite the opposite! It brought us a huge step in the right direction! :)

The difference with the other extensions is, that they do not require lang/php(74|80) itself to pull in the dependency. And except for OpenSSL extension, you can use LibreSSL with all of them. Yes, the OPENSSL flags allow you to use something else (as i have learned in the last try :D)

You mentioned that it is not enough to compile the mysql-extension with OpenSSL. If this would be the case, it would be quite easy. We could just do it the way the other extensions do.

But now it looks like we will need another non-default option that will pull in the dependency. And maybe a flavor for the port with the option enabled. Something like that can be a solution.
Comment 19 Markus Kohlmeyer 2021-02-24 11:31:16 UTC
OK, possibly we both misunderstood eaachother a bit, sorry for that.


This gives me ldd on my system now:

[root@devgate:~] # ldd /usr/local/bin/php
/usr/local/bin/php:
        libcrypt.so.5 => /lib/libcrypt.so.5 (0x80075b000)
        libargon2.so.0 => /usr/local/lib/libargon2.so.0 (0x80077c000)
        libutil.so.9 => /lib/libutil.so.9 (0x80078a000)
        libm.so.5 => /lib/libm.so.5 (0x8007a2000)
        libthr.so.3 => /lib/libthr.so.3 (0x8007d4000)
        libxml2.so.2 => /usr/local/lib/libxml2.so.2 (0x800801000)
        libc.so.7 => /lib/libc.so.7 (0x80099a000)
        libz.so.6 => /lib/libz.so.6 (0x800d86000)
        liblzma.so.5 => /usr/lib/liblzma.so.5 (0x800da2000)
        libmd.so.6 => /lib/libmd.so.6 (0x800dce000)
[root@devgate:~] # ldd /usr/local/lib/php/20200930-zts/mysqli.so
/usr/local/lib/php/20200930-zts/mysqli.so:
        libmysqlclient.so.21 => /usr/local/lib/mysql/libmysqlclient.so.21 (0x801c00000)
        libz.so.6 => /lib/libz.so.6 (0x8014b2000)
        libzstd.so.1 => /usr/local/lib/libzstd.so.1 (0x8014ce000)
        libm.so.5 => /lib/libm.so.5 (0x8015c4000)
        librt.so.1 => /usr/lib/librt.so.1 (0x8015f6000)
        libexecinfo.so.1 => /usr/lib/libexecinfo.so.1 (0x8022b2000)
        libunwind.so.8 => /usr/local/lib/libunwind.so.8 (0x8022b8000)
        libssl.so.11 => /usr/local/lib/libssl.so.11 (0x8022d2000)
        libcrypto.so.11 => /usr/local/lib/libcrypto.so.11 (0x802368000)
        libthr.so.3 => /lib/libthr.so.3 (0x80265b000)
        libc.so.7 => /lib/libc.so.7 (0x80106f000)
        libc++.so.1 => /usr/lib/libc++.so.1 (0x802688000)
        libcxxrt.so.1 => /lib/libcxxrt.so.1 (0x802756000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x802778000)
        libelf.so.2 => /lib/libelf.so.2 (0x802792000)
        liblzma.so.5 => /usr/lib/liblzma.so.5 (0x8027ae000)
        libmd.so.6 => /lib/libmd.so.6 (0x8027da000)
[root@devgate:~] # ldd /usr/local/lib/php/20200930-zts/pdo_mysql.so
/usr/local/lib/php/20200930-zts/pdo_mysql.so:
        libmysqlclient.so.21 => /usr/local/lib/mysql/libmysqlclient.so.21 (0x801c00000)
        libz.so.6 => /lib/libz.so.6 (0x801498000)
        libzstd.so.1 => /usr/local/lib/libzstd.so.1 (0x8014b4000)
        libm.so.5 => /lib/libm.so.5 (0x8015aa000)
        librt.so.1 => /usr/lib/librt.so.1 (0x8015dc000)
        libexecinfo.so.1 => /usr/lib/libexecinfo.so.1 (0x8015e5000)
        libunwind.so.8 => /usr/local/lib/libunwind.so.8 (0x8022b2000)
        libssl.so.11 => /usr/local/lib/libssl.so.11 (0x8022cc000)
        libcrypto.so.11 => /usr/local/lib/libcrypto.so.11 (0x802362000)
        libthr.so.3 => /lib/libthr.so.3 (0x802655000)
        libc.so.7 => /lib/libc.so.7 (0x80106f000)
        libc++.so.1 => /usr/lib/libc++.so.1 (0x802682000)
        libcxxrt.so.1 => /lib/libcxxrt.so.1 (0x802750000)
        libgcc_s.so.1 => /lib/libgcc_s.so.1 (0x802772000)
        libelf.so.2 => /lib/libelf.so.2 (0x80278c000)
        liblzma.so.5 => /usr/lib/liblzma.so.5 (0x8027a8000)
        libmd.so.6 => /lib/libmd.so.6 (0x8027d4000)
[root@devgate:~] #


So, if i understand it correct, it means that only the mysqli/pdo_mysql extensions need the OpenSSL and not lang/php[74|80] itself.

As you said, that would not be problem, so i suggest to add OpenSSL as a run/build-dependency to only the two extensions, but not php itself.


I'm unsure if that also helps for MYSQLND, that should be tested by someone else, but for libmysql it works.
Maybe MYSQLND should be switched to non-default by now and libmysql from databases/mysql[57|80]-client should be another run/build-dependency for the mysqli/pdo_mysql extensions instead.


HTH