Trouble connecting (directly) to MySQL from Unity code

I am trying to connect to a MySQL database from my Unity code. This is the server code; there is no reason to go through PHP and I do not wish to go that route. This code will be running on Mac (during development) and Linux (for deployment).

I tried various .NET "Connector" libraries from https://dev.mysql.com/downloads, but most of them generate errors about missing references (regardless of whether I set the API compatibility level in the settings to .NET 4.x). I had to go back to a "v2.0" folder, part of the 6.9.8 package, before those errors went away and I was able to get the libraries to load at all.

But now, while I'm able to call the MySQL code, I can't get it to connect to my database. I was getting internal Unity errors until I added "encrypt=false" to my connection string; but now I get "Authentication method 'caching_sha2_password' not supported by any of the available plugins."

From my reading of the interwebs, this error should indicate that the DB user is configured to use the newer MySQL 8+ encryption. But it is not; the user is set to use "Legacy - 5.x" encryption on the password (this is all configured through Digital Ocean, where our DB is hosted).

It seems to me like I'm likely to have more trouble using such an old library, so the best solution would be to get the newer libraries working. But I don't know how to satisfy whatever they are missing. For example, in the latest plugin MySQL.Data.dll references K4os.Compression.LZ4.dll, which references System.Memory, which apparently is nowhere to be found.

Does anyone have experience accessing MySQL directly from Unity? Any tips or tricks to help me get past these errors?

Well, of course as soon as I posted, I found a solution. Following up for posterity.

The 6.10.8 connector package has a "v4.5.2" folder, and that MySql.Data.dll does not have any onerous dependencies. The other DLLs that come with it do, but it turns out you don't actually need any of those; MySQL.Data.dll is all you need. And this one, apparently, is modern enough to support the required handshake.

So, huzzah! My server code can now access its database. :)

3 Likes

Thanks for sharing, i went through the exact same struggle recently.

1 Like

Thanks a lot