MySQLOO doesn't support caching_sha2_password / SSL with MySQL Server 8.0

[WinterPhoenix]

Upon attempting to connect with a user using the "caching_sha2_password" authentication plugin, I get this error:

Authentication plugin 'caching_sha2_password' cannot be loaded: /usr/local/mysql/lib/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory

Please note that if SSL is enabled (which is default), it won't even get that far, instead resulting in:

SSL connection error: unknown error number

I assume this has to do with still using 5.6 Headers/Libraries.

MySQLOO Version: 9.6.1
MySQL Server Version: 8.0.20
Operating System: Ubuntu 20.04 LTS

Please let me know if I can help debug this. Thanks!

[FredyH]

Yeah this is unfortunately a big problem that I don't know how to fix. Firstly, it seems like the MySQL 8 headers are incompatible with the 6 ones and require code changes for autoreconnect or similar things to work.
I would upgrade to the MySQL 8 headers, but there is no 32 bit windows version of libmysql anymore, so I would be unable to support that going forward, which I don't think is acceptable. However MySQL 8 headers are required for the new authentication method, so I really have no clue how to deal with this.

[WinterPhoenix]

Well... That is a difficult question to answer. Indeed, MySQL Server just straight up isn't offered in 64-bit anymore for Windows.

I'm not sure we can really avoid 64-bit, so my opinion is:

• MySQL has killed 32-bit for Windows upstream, so I'd say kill it in MySQLOO too
• Anybody that wants to continue to use 32-bit on Windows can continue to do so with old releases (or possibly a separate branch if you really wanted to), with the caveat that they don't get the new stuff. MySQL 8 does still work with 5.6 headers, just not the new stuff (SSL / caching_sha2_password)
• If they want the new stuff on Windows, they have the option of x86-64 branch. I've been using it on my prod servers for 1+ year now (although on Ubuntu), and it's been stable for me. Couldn't hurt to have more people testing it with different configurations

[viral32111]

I'd totally like a seperate branch using newer MySQL features n' stuff, even if it was only for x86-64. Just like WinterPhoenix said, anyone who's using x86 SRCDS should just use the old branch. Of course you'd still give the old branch security updates/support, but new feature requests should be directed at the new branch. (Also when the inevitable-but-always-postponed x86-64 update finally releases, then the old branch can be properly closed.)

[FredyH]

I probably have to agree, I will start working on the next version soon and I guess I will drop support for Windows 32 bit.
The next bigger problem is that libssl is just completely messed up (I just hate the C/C++ world, nothing ever works properly).
Ideally I'd link libssl dynamically and use the one provided by the OS (which also knows where the certificates are, etc.), but I have made some bad experiences with that and I just straight up don't think it works. This is made worse by there being differences between the versions and it seemingly matters which headers you use.
If anyone knows a solution for this or has dealt with this problem in the past, I'd really appreciate any input/help.

[WinterPhoenix]

Love you Fredy. I agree that linking it dynamically is probably the better approach... I'm not sure why it wouldn't work though.

[FredyH]

I think this might've actually been a problem in the past because most linux OS are now 64 bit and probably didn't have the 32 bit libssl installed. I'll toy around with it some and I'll try to make it work that way.

[WinterPhoenix]

Source on MySQL 5.X being faster than 8.X? I'm seeing quite a few benchmarks that are saying otherwise, and by a pretty significant margin.

[FredyH]

I have just published a beta build that should hopefully fix this, please report back and tell me if it works!

[ApertureDevelopment]

@FredyH Doesn't seem to work btw. I am unable to use mysqloo at all with the beta release.

Furthermore is there another approach then dropping 32bit for this? As you know every standard GMod server is using the 32bit branch, and would not be able to use the new MySQLoo then.

How I installed MySQLoo beta
First off, I am using Ubuntu 20.04

I put the libmysql.so in the folder with the srcds_run_x64 binary and the dll file into the garrysmod/lua/bin folder

lua_run require("mysqloo")
> require("mysqloo")...

[ERROR] lua_run:1: Couldn't load module library!
  1. require - [C]:-1
   2. unknown - lua_run:1

[ApertureDevelopment]

Also what would happen if you used the generic linux MySQL connector? That one seems to still have a 32bit version

[WinterPhoenix]

Try installing the library via APT and not worrying about the one included in the Beta download if that's not working:

sudo apt-get install libmysqlclient-dev

And the reality of 32-bit is that it's being dropped pretty much everywhere possible. It's unrealistic to try and keep supporting it when not even MySQL is, and with GMod moving in the same direction.

[viral32111]

And the reality of 32-bit is that it's being dropped pretty much everywhere possible. It's unrealistic to try and keep supporting it when not even MySQL is, and with GMod moving in the same direction.

Sadly GMod's x64 development is likely going to slow to a halt as Willox left Facepunch a month or so ago (maybe a bit longer), and afaik he was the one mostly working on x64 (so who knows, it may never properly release now :c).

However, you're still very much right (and I totally agree) in you saying that it's unrealistic to support it, like you said it's being dropped everywhere else and without this module moving to x64 we wouldn't be able to get the newer MySQL 8 features and such, same applies to other modules that rely on thirdparty libraries. Also, support for users running x86 platforms or software is slowly dying out too, which makes debugging and researching issues for the maintainers even more troublesome.

Furthermore is there another approach then dropping 32bit for this? As you know every standard GMod server is using the 32bit branch, and would not be able to use the new MySQLoo then.

What's preventing GMod servers from just updating to the x86-64 branch? If the server is hosted on shared hosting, I'm sure if the owner contacts the hosting provider they'll update the server to the x86-64 branch. If that's for some reason totally not possible for them, then they'll just have to use an older version of the module where x86 was provided, and they'd simply have to accept the fact that they can only use older versions/features of MySQL server and alike.

Doesn't seem to work btw. I am unable to use mysqloo at all with the beta release.

My comment from #68 might help, that person was also using the beta release and getting a Couldn't load module library! error, turns out the mysqlclient lib in the beta release was suffixed incorrectly.

[ApertureDevelopment]

What's preventing GMod servers from just updating to the x86-64 branch?

There are a few hosters that don't support changing the main branch, and also some self hostable webpanels require complicated re-configuration to change how the GMod server is installed. The truth is since it is still the default branch of GMod there should at least be some kind of support for this, its like dropping support for Spacebuild 3 in favor of Spacebuild 4, which is unfinished and unstable. I can't speak for facepunch, but there is propably a good reason why the 64bit branch isnt the default branch already.

I know that 32 bit is dying out, which is a good thing ( I hate 32 bit limitation ), Windows dropped it, Ubuntu dropped it, a ton of other distros dropped it ( and Mac is moving backwards with arm64 ) etc. But as long as GMod didn't drop the 32 bit support, and even suggests it over the 64bit branch, I'd say it should still be supported.

My comment from #68 might help, that person was also using the beta release and getting a Couldn't load module library! error, turns out the mysqlclient lib in the beta release was suffixed incorrectly.

Ah, that makes sense. I will have to update the installation manual of MSync sometime in the near future. It still explains how to install MySQLoo the old way XD

[Kefta]

there is propably a good reason why the 64bit branch isnt the default branch already.

A few outstanding bugs is all that's preventing x64 from being merged into the main branch, it is very stable enough to use for servers. If your host doesn't allow you to change branches, submit a support ticket or change hosts - that's a ridiculous restriction that modules shouldn't have to abide to, though the real restriction here is MySQL's lack of 32-bit headers.

[ApertureDevelopment]

We are talking about this connector, right? https://dev.mysql.com/downloads/connector/cpp/

And yes, abandoning a standard before a new standard has been established is a bad idea, and keeping in mind facepunches slow development process, it propably will be another 3-5 years before x64 will be merged into master. And as for the reason why some hosts don't allow the changing of the default branch, they most likely planned in the required ressources to run the server at the capability you pay for, and switching to a beta branch ruins those calculations.

I am not saying to not drop 32bit support, but I think its a bit early to do that before facepunch does

[Kefta]

another 3-5 years before x64 will be merged into master.

Where did you come up with this figure?

[ApertureDevelopment]

"and keeping in mind facepunches slow development process"

How long did it take them to abandon Awesomium for a more recent browser that supports a few more CSS properties? Awesomium was already outdated when I started with GMod. And the same goes for the 64bit branch, it took years for them to actually start going with 64bit, despite the direction to abandon 32bit being clearly visible the past 10 years.

[XeroxDev]

I agree with @ApertureDevelopment. Dropping the 32 bit support is currently a bad idea. You should at least wait until everything has normalized and then you can start thinking about it. It would currently cause problems for many people.

[Kefta]

You can always build a previous version of mysqloo that supports x86 - old builds are available here: https://github.com/FredyH/MySQLOO/tags

32-bit support has already been dropped from MySQL so I don't see the reason why the module should continue supporting it, especially if it means sacrificing the ability to incorporate new features. 32-bit users can continue to use the builds that were working fine before.

[ApertureDevelopment]

32bit support has dropped from the Database server not the Libaries, client and so on. The reason for that is that it doesn't make much sense to host a high scaleable database with the restrictions of 32bit. What do makes sense thought is to still allow 32bit clients to use MySQL, which they do allow.

[FredyH]

There are 32 bit libraries for Linux, however MySQL does not offer any 32 bit builds for Windows. So while I could theoretically add a 32 bit Linux build I am unsure it is worth the hassle having to support 2 different architectures and always having all libraries up to date.

[ApertureDevelopment]

On the website I sent in you can also get the libaries for Windows 32bit
https://dev.mysql.com/downloads/connector/cpp/

[FredyH]

MySQLOO uses the C connector, not the C++ one. The C++ one is available, however it is vastly different (also worse) than the C one.

[ApertureDevelopment]

You mean this one?

[FredyH]

You can see right in that image, that this is the 6.1 client library (which btw I am using up until the latest version).
The 6.1 client library IS NOT compatible with MySQL 8 features like the new password authentication and ssl.

[ApertureDevelopment]

What about the MariaDB C connector? Since MariaDB is a fork of mysql, and works almost the same ( just a few key differences on the server side ) It should also be able to connect to MySQL. I have been using MySQLoo with a MariaDB server for a long time already, so why shouldnt it work the other way around?

[FredyH]

I have already tried switching to the MariaDB connector before and it caused loads of issues. I am also not sure if the latest MariaDB connector is even compatible with MySQL 8.

If it is it might be worth considering, but ideally I'd like to drop 32 bit support because it's a lot of work supporting both 32 and 64 bit version for both windows and linux especially when it comes to actually building the library for all of these configurations.

[ApertureDevelopment]

The only issue I see here is that people that develop products utilizing MySQLoo ( Me with MSync for example ) will propably get a lot of reports from users because they can't use MySQL on their standard server. While there is the possibility to tell those that reported to switch to the 64bit branch, most will propably just give up instantly and not even report errors and the users that reported it will say it's too much work for them

[FredyH]

It's been a very long time, but I just added a new beta release that uses the latest MariaDB Client including statically linked caching sha2 authentication.
You can download/test the version here
Please report back if it works or causes any issues.

[WinterPhoenix]

I have, finally, gotten a chance to look at this.

Since 9.7.0-beta-2, I get this when attempting to connect to a MySQL 8 server with require_secure_transport enabled:

Connections using insecure transport are prohibited while --require_secure_transport=ON

To be clear, SSL works on 9.7.0-beta-1, but not after that. Are we supposed to be providing our own cacerts.pem or something?

[FredyH]

If you use SSL you need to provide the certificates (assuming you are using self signed ones), you can do so using db:setSSLSettings(key, cert, ca, capath, cipher) (all parameters are optional), see here.

Edit: It might be due to some plugin not being installed. I will try and install all plugins and then see if that makes it work.
Edit2: It seems that even the official mariadb client has this issue, while mysql does not. This is not great and I do not have a solution at the moment.
Edit3: I think I found the difference. Mysql and MariaDB have different networks they consider "secure" and a certificate is not required. If I specify a different IP, I cannot connect using either, so I suppose in those cases you will still have to get specify your own certificates.

[FredyH]

This is now fixed via 9,7.0

[ApertureDevelopment]

Thank you <3

[WinterPhoenix]

Hey Fredy, giving 9.7.1 a shot; somehow I'm getting a segfault if I specify SSL_CA or SSL_CAPATH. Not sure what's going on here, but the MySQL docs are pretty unhelpful in telling you what's required and in what format (no examples for ciphers, etc), or how relative vs. absolute paths are handled.

Here's the debug log:
debug.log

This is on Ubuntu 20.04 LTS with MySQL Server 8.0.27, btw. If you want the crash dump, I can send it to you on Steam, just lemme know.

Thanks!