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
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?
Torsten, that's a good hypothesis, and sounds like it could explain the issue!
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...
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:~] #
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?
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.
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 ;)
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
But don't forget that it's *required* to link PHP and MySQL against OpenSSL.
> 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?
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.
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.
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?
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.
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?
> 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.
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?
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.
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