MySQL for v22 Part 2

[john w]

After getting a connection to the MySQL db. I checked to make sure data was being returned (wizard->RunQuery). I see the data returned.

I think set up a select into variables query and it fails. Its say to update the MySQL client, it cannot connect. I have made sure the Client and Version numbers all match both on the local machine and the server,

Anyone have a suggestion as what may be happening?

I am using InDe v 22r14, MySQL v8.0.29.0 both locally and on the server. All MySQL Connectors are the same version.

The debug info is in the pic below

John W

[ljwilson]

Looks like you need to make some authentication changes at the server level.

This is a good thread on the subject:

https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server

...jack

[john w]

Thanks for the information. At some point I tried that without success. I will step through it again just to make sure I did not miss something. If it works out I will make a post.

[john w]

I stepped through my MySQL server and no luck. It is still a mystery :(

[john w]

It seems like the connections test okay and everything works until it is compiled and the app tries to connect to the database. I have tried setting up a DSN which works and testing the connection reports okay.

However, when I compile and run the app it fails. The error messages are not very descriptive. The DSN connection does give me this error, which is different. Since there is only one table in the test app, there is not anything that should be ambiguous.

The error messages are not very helpful, but perhaps they mean something to the support people.

[john w]

After some trial and error, Jack was finally able to help me get this resolved. The solution was not total. I am using Maria instead of MySQL. I am okay with that.

He helped me track down some .ini files, make port corrections and deal with some services.

It appears at this point, I will be able to move forward with this piece using Maria. As I said I am okay with that.

As an aside, I do think InDe needs to do some work with the MySQL side of things. It does not need deal with the new version very well. If there a process to use the documentation could explain it better.

Anyway thanks to Jack and him giving me some his time I am moving again on the new application.

[ljwilson]

I was finally able to reproduce the problem John was seeing in my local test environment. As of InDe 22.0 r15, the .Net MySql.Data.dll connector used to communicate with MySQL .net is 6.3.6.0 (from 2018).

This version doesn't know about the newer caching_sha2_password default authentication method. I ran into a similar problem on the PostgreSQL side which InDe fixed in 21.5.

The latest version of MySql.Data.dll is 8.0.29.0 (from 2022). Maybe InDe could update their shipping versions?

In the meantime, there are workarounds. What John did was switch to MariaDB. But if that isn't an option...

When you do a default install of MySQL 8.0 server, it will automatically install this newer caching_sha2_password mechanism, which will not work with the current InDe drivers. The simplest fix is when you first install MySQL Server, for Authentication Method select "Use Legacy Authentication Method (Retain MySQL 5.x Compatibility)".

If you don't do this on the first install, you can go back and modify the existing installation, but any accounts you have already created will still be using the newer caching_sha2_password method, so it appears that all is still broken. To fix that for each account you use from InDe or its apps to login to the MySQL Server, you must run some SQL to change the account to use the older authentication method. This is a good post on the subject:

https://chrisshennan.com/blog/fixing-authentication-plugin-cachingsha2password-cannot-be-loaded-errors

John and I did the first part, but failed to do this second part, which is why we ended-up using MariaDB instead.

So in the above post under "Resolution", be sure and do BOTH Items 1) and 2).

And then restart the MySQL service.

Note: the file you need to change is my.ini which on Windows with a default install is in this location:

Code: Select all

C:\ProgramData\MySQL\MySQL Server 8.0\my.ini

Of course, make a backup of this file before doing any changes!

...jack