Unable to connect to database, caching_sha2_password workaround

Databases
1

Hey all,

So I’ve done a little digging and it seems that this forum post hints at me needing a XOJO LICENSE to be able to develop an application with a MySQL Community Server database. So am I misunderstanding something or can I only use SQLITE databases to build my applications until purchasing a license?

I am utterly unable to connect to my local instance of MySQL through the IDE even when I drop some code into a button, it will never connect.

2

You can develop without a license.

For MySQL please make sure the MySQL plugin is in the plugins folder.

Next to build the application, please buy a license.

3

Verified plugin is present: C:\Program Files\Xojo\Xojo 2018r4\Plugins\MySQLCommunityPlugin

After Further research it looks like it might be a plugin issue with MySQL.

Error message “Error connecting to MySQL: Authentication plugin ‘caching_sha2_password’ cannot be loaded: The specified module could not be found”

Which, for posterity (I’m not a DBA):

looks to be the new method of authenticating to MySQL server 8 and connect to your databases. When you set up mysql community server and choose the Use Strong Password Encryption for Authentication option it enables a new plugin caching_sha2_password . any users subsequently created while in this mode will try to use this plugin to authenticate and access a schema.

My solution in this case was to disable the strong password encryption via the reconfigure option in Mysql Installer, restart my machine then drop into a mysql shell and issue the following commands

\connect root@localhost
CREATE USER ‘xojo’@‘localhost’ IDENTIFIED WITH mysql_native_password BY ‘password’;

went back into my app and put those credentials in and it connected!

if you disable and restart your MySQL server the WITH mysql_native_password might be over kill as since the strong encryption is disabled, MySQL should create users that now naturally use the mysql_native_password plugin and not the caching_sha2_password plugin. It seems as though the zojo plugin doesn’t support this new method of authenticating, I could be mistaken.

PS: Actually, if you open MYSQL workbench and go to Server > users and priviliges you can confirm this problem by checking the Authentication type, which will have the name of the plugin being used to authenticate the user.