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: Closed FIXED
Alias: None
Product: Ports & Packages
Classification: Unclassified
Component: Individual Port(s) (show other bugs)
Version: Latest
Hardware: amd64 Any
: --- Affects Many People
Assignee: Torsten Zuehlsdorff
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2021-01-04 21:02 UTC by Sean McBride
Modified: 2021-04-15 21:36 UTC (History)
7 users (show)

See Also:


Attachments
mysql80 fix (3.58 KB, patch)
2021-03-29 21:37 UTC, Muhammad Moinur Rahman
no flags Details | Diff

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
Comment 20 Gabor 2021-03-02 11:10:14 UTC
Hi all, is there any progress with this?
Currently if anyone uses latest stable FreeBSD and installs latest stable mysql and php it just doesn't work.
I don't think this is acceptable in any way, these are not some marignal applications.
Comment 21 Jeffery Telford 2021-03-23 01:02:01 UTC
(In reply to Markus Kohlmeyer from comment #6)

If tried disabling the option to use the native driver in the php80-mysqli port and now php80-mysqli has a dependency on the mysql57-client which obviously conflicts with the mysql80-client so I can't even install it to test out your theory. There are no flavors for this port so I can't see how I can get it to work with the mysql80-client instead.
Comment 22 Jeffery Telford 2021-03-23 06:46:49 UTC
I even tried compiling PHP 8.0.3 source from PHP.Net and mysqlnd still doesn't show up with caching_sha2_password' support.
Comment 23 Torsten Zuehlsdorff freebsd_committer 2021-03-23 08:13:57 UTC
> If tried disabling the option to use the native driver in the php80-mysqli port and now php80-mysqli has a dependency on the mysql57-client which obviously conflicts with the mysql80-client so I can't even install it to test out your theory. 

Sure you can. But you need to define MySQL 8.0 as default version before doing so. This is why I stated, that the problem will persist when MySQL 8.0 becomes a default version of MySQL. Until than you need at least some deeper knowledge to get in run. And in this case its easy to go back to the old Auth-method.

But we are currently working on a patch to fix this. moin supports me at the moment - which means he writes and tests the patch to cover my lack of time. ;)
Comment 24 Jeffery Telford 2021-03-24 04:47:31 UTC
(In reply to Torsten Zuehlsdorff from comment #23)

I re-read all the posts and now I get the problem so I did a minor 3 line edit to the PHP80 Makefile and now MYSQLND is building with auth_plugin_caching_sha2_password & auth_plugin_sha256_password support and I can connect to MySQL 8.0 without disabling anything.

Look forward to a more professional long-term fix.
Comment 25 Torsten Zuehlsdorff freebsd_committer 2021-03-24 17:40:01 UTC
Happy to hear that :)

Can you share the 3 lines so other people also waiting will find a workaround here? 

Thank you! :)
Torsten
Comment 26 Jeffery Telford 2021-03-24 23:06:23 UTC
(In reply to Torsten Zuehlsdorff from comment #25)

I edited the PHP80 Makefile and changed this:

.if ${PORT_OPTIONS:MMYSQLND}
CONFIGURE_ARGS+=--enable-mysqlnd
.endif

to this (taken from the php80-openssl Makefile):

.if ${PORT_OPTIONS:MMYSQLND}
CONFIGURE_ARGS+=--enable-mysqlnd \
                --with-openssl
CONFIGURE_ENV+= OPENSSL_CFLAGS="-I${OPENSSLINC}" \
                OPENSSL_LIBS="-L${OPENSSLLIB} -lssl -lcrypto"
.endif

And don't enable the OPENSSL extension as I guess it's now statically linked (due to the --disable-all earlier in the Makefile) or else you'll get this error message when you do a phpinfo():

'Warning: Module "openssl" is already loaded in Unknown on line 0'

Once I did this when I do a phpinfo() I see two additional plugins under MYSQLND called auth_plugin_caching_sha2_password and auth_plugin_sha256_password and I connect to MySQL 8.0 without changing anything back to mysql_native.
Comment 27 Jeffery Telford 2021-03-26 01:24:39 UTC
It seems a lot of PHP apps have php-openssl listed as a dependency. So they will go ahead and install the extension then you'll get that warning message that openssl is loading twice.

If the warning message annoys you just edit the file /usr/local/etc/php/ext-20-openssl.ini and comment out the line so it reads:

#extension=openssl.so

Another alternative (and probably better solution) might be to build PHP80 and remove the --disable-all option. I think that way openssl will build the extension and not link it statically and PHP will still build with the sha2 and sha256 plugins (I'm testing this now).
Comment 28 Jeffery Telford 2021-03-26 02:14:45 UTC
Well no matter what I try I just cannot get OpenSSL to compile as an extension and yet still have 'caching_sha2_password' support.

I took a look at Linux and I noticed that it's never had a openssl.so PHP extension and I honestly have no idea how the port maintainer plans on providing 'caching_sha2_password' support and yet still maintain an OpenSSL extension. Maybe it's time to just statically link OpenSSL (along with the LibXML that's being statically linked today) and drop the OpenSSL extension.
Comment 29 Torsten Zuehlsdorff freebsd_committer 2021-03-26 13:06:37 UTC
With removing --disable-all you will enable a bunch of modules and increase the size of the standard installation. While this is no issue for many installations, it is if you think on a scaled cloud environment. We have also users there, which are just happy about every storage, memory and CPU saved by our method.

Also if you do a static linking of OpenSSL you will get a number of problems. Most SSL implementations do not work well when they are installed in parallel. We have many LibreSSL user for example.

But because of these issues, I do not rush to a solution like it is requested over again. I do not want to break anything ;)
Comment 30 Sean McBride 2021-03-26 15:44:52 UTC
>I do not want to break anything ;)

I totally understand, and I hope a solution that satisfies (almost) everyone can be found.

But let's also not forget that something is already broken here.  MySQL is very popular, and not working with its default password scheme is a kind of 'broken' too.

>With removing --disable-all you will enable a bunch of modules and increase the size of the standard installation.

Perhaps some numerical quantification would help measure the tradeoffs here.  i.e. How much bigger? 1%? 10%? 100%?
Comment 31 Torsten Zuehlsdorff freebsd_committer 2021-03-26 16:00:52 UTC
> But let's also not forget that something is already broken here.  MySQL is very popular, and not working with its default password scheme is a kind of 'broken' too.

Yes, that is right, but it is not that bad. ;) MySQL works just fine right now. If you choose to pick MySQL 8.0 however, it is "broken". The other "but" here: it is easily fixable by changing the config of MySQL.

So yes, it is kind of broken and kind of usable at the same moment. It is unsatisfying but not trivial to fix. Especially not with different PHP and MySQL versions at the same time. Not to mention the really hard stuff around SSL.

> Perhaps some numerical quantification would help measure the tradeoffs here.  i.e. How much bigger? 1%? 10%? 100%?

I can give you one, but this would be false by default. I get way fewer mails/issues/complaints than there are PHP+MySQL installations out there. :D

Over the last years, I was constantly surprised by what issues came up and how PHP+MySQL is used. But it is nothing I can judge while being fair. For example, I can name several contacts naming some specific config, which sound rare - but all of them have a hosting business which hundreds or thousands of clients.
Comment 32 Sean McBride 2021-03-26 16:27:46 UTC
>MySQL works just fine right now. If you choose to pick MySQL 8.0 however, it is "broken".

Those two sentences are something of a contradiction. :) Do you mean vs picking some older version of MySQL?  It's not like 8.x is some new beta version, it's 3 years old now.

>The other "but" here: it is easily fixable by changing the config of MySQL.

Easy only if you know how. Before I created this ticket, I couldn't find anything anywhere that didn't say the issues of MySQL8 + PHP were all fixed in newer PHP.  Of course most blogs/tutorials/docs out there are for linux, not FreeBSD.

Try googling for things like 'FAMP tutorial' and see if you can find any where you won't get stuck because of this issue. :(
Comment 33 Jeffery Telford 2021-03-26 19:14:19 UTC
According to Freshports there are only 2 packages that depend on LibreSSL so I'm not sure what is going to break if you build PHP with OpenSSL.  This resistance to build PHP so it supports MySQL 8.0 out of the box is just another reason why FreeBSD will continue be a niche product and Linux will continue to dominant. MySQL obviously made a decision to support these newer more secure protocols and FreeBSD shouldn't require you to disable them in order to function. 

It's also frustrating that I cannot install multiple versions of PHP on the same server. I've done this for years on Linux using PHP-FPM and all I need to do is to repoint the webserver to a different socket and I'm using a different version of PHP. It's a great feature for PHP developers as they can easily test their PHP apps on different versions.
Comment 34 Christos Chatzaras 2021-03-26 19:16:32 UTC
(In reply to Jeffery Telford from comment #33)

You can use jails to run multiple php versions. I already do it for 5.6, 7.1, 7.2, 7.3 and 7.4.
Comment 35 Jeffery Telford 2021-03-26 19:28:34 UTC
(In reply to Sean McBride from comment #30)

When I compare my php-fpm to the version that comes from FreeBSD package it's about 200K bigger or ~3.5%.
Comment 36 Jeffery Telford 2021-03-26 19:29:41 UTC
(In reply to Christos Chatzaras from comment #34)

Yea that's what I ended up doing.
Comment 37 Chris Hutchinson 2021-03-26 21:20:31 UTC
(In reply to Jeffery Telford from comment #33)
> According to Freshports there are only 2 packages that depend on LibreSSL
> so I'm not sure what is going to break if you build PHP with OpenSSL.
Indeed. But there are well more than that that _permit_ the use of other
than OpenSSL. And leave us not forget that only 2 ports does not determine
the total number of installs/uses. :-)

> This resistance to build PHP so it supports MySQL 8.0 out of the box is
> just another reason why FreeBSD will continue be a niche product and
> Linux will continue to dominant.
This has zero to do with your understanding/evaluation except in
your mind. :-)
* * BUT * *
I feel your frustration/anxiety. As a Maintainer of some 160 ports.
I'm well familiar with the hurdles && pitfalls encountered along
the way. :-)

> MySQL obviously made a decision to support these newer more secure
> protocols and FreeBSD shouldn't require you to disable them in order
> to function. 
> 
> It's also frustrating that I cannot install multiple versions of PHP
> on the same server.
You only have PHP to thank for that. :-) Waaay back in the day, I
managed to cobble a lib for Apache that permitted 2 different versions
of PHP to co-exist. But then that niche product FreeBSD came out with
this thing called a jail(8), and I never looked back. I think Linux
dominated that with chroot(8). But I found jail(8) to be far superior.

> I've done this for years on Linux using PHP-FPM and
> all I need to do is to repoint the webserver to a different socket and
> I'm using a different version of PHP. It's a great feature for PHP
> developers as they can easily test their PHP apps on different versions.
See jail(8) above. ;-)

Look. I *do* get your frustration, and because I *love* puzzles. I decided
to chime in, and either solve this puzzle, or further complicate it. ;-)
Seriously. Tho this thread is quite long. I *think* I have all the pieces
together. Wouldn't it be enough to create a separate option against
mysql-8 that provided a conditional to require (some)SSL.lib? Does MySQL8
really *require* OPENssl to accomplish your needs. Or just SSL? It does
appear that your password extension/option does. If that's the case.
Then just ticking the sub_option (your password stuff) could slurp in
OpenSSL, and provide a "conflicts" resolution. It's all just an
IF THEN ELSE dance.

You have anything proposed? I don't see any attachments to this pr(1).

Chris out...
Comment 38 Jeffery Telford 2021-03-26 22:14:46 UTC
(In reply to Chris Hutchinson from comment #37)

According to this page on my MySQL site:

https://dev.mysql.com/doc/apis-php/en/apis-php-mysqlnd.install.html

you need to compile PHP with OpenSSL in order to get the MySQL Native Driver to have Sha256 (and caching_sha2_password) support. 

It's not enough to compile OpenSSL as an extension or else it seems the native driver is not compiled with openssl hence no 'caching_sha2_password' support.
Comment 39 Torsten Zuehlsdorff freebsd_committer 2021-03-26 22:41:16 UTC
> Those two sentences are something of a contradiction. :) Do you mean vs picking some older version of MySQL?  It's not like 8.x is some new beta version, it's 3 years old now.

Nearly. I mean "VS" picking the current default version, which is pulled in automatically. To get around this you can build your ports at your own or you do a standalone installation of MySQL 8. The first requires some knowledge. 

> According to Freshports there are only 2 packages that depend on LibreSSL so I'm not sure what is going to break if you build PHP with OpenSSL.  

You are reading it one-sided. There are plenty of people out there, who define LibreSSL in the make.conf as default SSL implementation. This can't be tracked by Freshports but is supported by the Ports-Tree.

> This resistance to build PHP so it supports MySQL 8.0 out of the box is just another reason why FreeBSD will continue be a niche product and Linux will continue to dominant. 

This gets political. So I cut it short:
* I do not know any Linux-Distribution which supports all Major PHP versions in parallel. Without hacking the Repo, using a 3 party repo or compiling them at your own
* Same for MySQL
* Or MariaDB
* Even PostgreSQL - which is superior to MySQL at so many points... :D

You say I am resistant to build PHP so it supports MySQL 8.0? I stated multiple times here, that we are working on a patch.

I stated multiple times, that we try not to break anything. You think going right to MySQL 8.0 and ignoring all the current users will make us a bigger niche? 

So: how am I supposed to do this?
Comment 40 Jeffery Telford 2021-03-27 00:36:35 UTC
(In reply to Torsten Zuehlsdorff from comment #39)

Every Linux (at least Debian based that I've used) can run multiple versions of PHP-FPM on one server.

https://www.digitalocean.com/community/tutorials/how-to-run-multiple-php-versions-on-one-server-using-apache-and-php-fpm-on-ubuntu-18-04#:~:text=Similarly%2C%20PHP%2DFPM%20uses%20a,all%20at%20the%20same%20time.
Comment 41 Torsten Zuehlsdorff freebsd_committer 2021-03-27 00:55:41 UTC
I am happy that I have deleted a long reply which covers so many issues with Linux. :D 

> Every Linux (at least Debian based that I've used) can run multiple versions of PHP-FPM on one server.

Yes, this is one method of Linux and has so many flaws. Like for example dependencies on a third-party binary repo.

Also, your statement is simple just false. First, you can run multiple versions of PHP on one server. Second, you can do this (in another context). And in both scenarios, you do not need to rely on third-party software or any other hack of your package system.

And yes: I do know the PHP of many Linux distros very well. I was in an unsettling situation to write patches for them. And you can trust me: there is some real resistance. REAL. Not the kind of "we try our best not to break something, but this will take a while". More of "NOOOOOO, GO AWAY". 

And this does not even start with the common flaws so many of them share. If you want to be legally safe you can't use most of them in the EU, because of the GDPR (aka DSGVO). It simply forbids using outdated software when it will become an issue. Most distros will ship EOL (PHP) versions for years. They sometimes will take weeks until they publish a security patch. And of course, they keep known bugs for around 5 years (aka the EOL of the LTS-distro). Being hit by a PHP bug which is fixed in the next version - no problem, just wait some years and do a major update of your Linux distro, and the issue is fixed :P

See Jeffery, I know you are frustrated about the situation. As you can see in the thread you are not alone! We are in here together. Please also have a look at my side: I need to find a fix that will make you happy and prevent every other user from filling a new PR. FreeBSD may be a (big) niche, but the quality is very important for us. 

I am working together with moin@ to get this issue fixed as fast and best as possible. We will cover your scenario and we will find a solution that enables you to use MySQL 8 together with PHP on FreeBSD! :)
Comment 42 Sean McBride 2021-03-27 01:12:11 UTC
Things are getting rather offtopic now...

>I am working together with moin@ to get this issue fixed as fast and best as possible

Thanks for your work on this Torsten!  When there's something to test, I can spin up a VM...
Comment 43 Muhammad Moinur Rahman freebsd_committer 2021-03-29 21:37:43 UTC
Created attachment 223692 [details]
mysql80 fix

This patch adds an OPTION for adding 'caching_sha2_password' (Default Password mechanism in MySQL >= 8.0) in php80. If the OPTION MYSQL80 is selected it will build openssl module with base PHP including mysqlnd. This will also prevent php80-openssl module from being installed in the future. php80-mysqli and php80-pdo_mysql will use mysqlnd.

portmaster --force-config databases/php80-mysqli

Select MYSQL80 in php80 OPTIONS menu.
Comment 44 commit-hook freebsd_committer 2021-04-06 10:15:27 UTC
A commit in branch main references this bug:

URL: https://cgit.FreeBSD.org/ports/commit/?id=91fdbed776033fd4b210135429a171ab5fed549b

commit 91fdbed776033fd4b210135429a171ab5fed549b
Author:     Muhammad Moinur Rahman <bofh@FreeBSD.org>
AuthorDate: 2021-04-06 10:06:56 +0000
Commit:     Muhammad Moinur Rahman <bofh@FreeBSD.org>
CommitDate: 2021-04-06 10:14:41 +0000

    lang/php80: Adding MySQL 8.0 'caching_sha2_password' support

    - Pet portlint
    - Add non-default OPTION MYSQL80

    This patch adds an OPTION for adding 'caching_sha2_password' (Default
    Password mechanism in MySQL >= 8.0) in php80. If the OPTION MYSQL80 is
    selected it will build openssl module with base PHP including mysqlnd.
    This will also prevent php80-openssl module from being installed in the
    future. php80-mysqli and php80-pdo_mysql will use mysqlnd.

    PR:             252420
    Reported by:    sean@rogue-research.com
    Approved by:    tz
    Sponsored by:   Bounce Experts
    Differential Revision:  https://reviews.freebsd.org/D29491

 lang/php80/Makefile     | 29 +++++++++++++++++++++++++----
 lang/php80/Makefile.ext | 10 ++++++++++
 2 files changed, 35 insertions(+), 4 deletions(-)
Comment 45 Jeffery Telford 2021-04-08 02:46:24 UTC
(In reply to Muhammad Moinur Rahman from comment #43)

What happens to apps like phpMyAdmin that have php-openssl listed as a dependency if the patch prevents it from being installed?
Comment 46 Sean McBride 2021-04-15 16:47:28 UTC
I've finally managed to partly test this.

At first I could not find this `MYSQL80` option, even after `svn up` on my poudriere server.  Eventually I determined that ports have switched to git, and there's apparently no mirroring to svn.

Then I turned on `MYSQL80` for several ports but `php80-openssl` failed to build. Re-reading the messages here, I think I understand now that I shouldn't even be using `php80-openssl` at all.  So I removed it and set the option for `php80` and `mod_php80` only and everything seems to build now, both in 12.2 and 13.0 poudriere jails.

But I have yet to actually try *using* this yet...
Comment 47 Torsten Zuehlsdorff freebsd_committer 2021-04-15 21:36:23 UTC
You are on the right track Sean! :)

When you have enabled this option the php80-openssl is statically compiled into PHP. So you are no longer allowed to install it as module. Removing the module is fine in this scenario. :)