New libmysq with auth plugin caching_sha2_password ?

TomasB posted 9 months ago in General

In newer version of MySQL (>=8.0.3) is used new authentication plugin. The libmysql.dll supplied with HeidiSQL doesn't support it and when I just copy new version 8.0.4 to Heidi's dir I'm getting error "Cannot find a usable libmysql". Can you change code to support new libmysql ?

ebyrob posted 9 months ago

Hi TomasB,

I just had a similar issue which I reported as HeidiSQL issue #163 on GitHub.

However, I got a different error message: Authentication plugin 'caching_sha2_password' cannot be loaded: The specified module could not be found.

So I can't be certain these are related. Just in case they are related, I thought I would cross-post here.

Thanks, ebyrob

cacofony posted 7 months ago

From some testing I have done, there does not seem to be a version of libmysql.dll for 32bit

edenilson_s posted 6 months ago

If your MySQL installation is >=8.0.3, i recommend locating your MySQL installation and copying the libmysql.dll file to your current HeidiSQL installation.

Example:

copy C:\Program Files\MySQL\MySQL Router 8.0\bin\libmysql.dll to C:\Program Files\HeidiSQL\libmysql.dll

You will find that the version of the libmysql.dll file is larger than the one distributed in the current installation of your HeidiSQL.

hug

steinhaug posted 6 months ago

I had same problems after installing new server with latest mysql and such.

Open up and modify my.ini file located most likely at C:\ProgramData\MySQL\MySQL Server 8.0 Look for "[mysqld]" and edit the line with "default_authentication_plugin" value to:

[mysqld]
default_authentication_plugin=mysql_native_password

Restart the MySQL Service

From the command line you can access MySQL and create the new user:

CREATE USER 'the_username'@'localhost' IDENTIFIED BY 'the_password';
GRANT ALL PRIVILEGES ON . TO 'the_username'@'localhost';

Now you can connect again, using the new user.

TSchm posted 5 months ago

I agree that this is working, BUT I think the correct way to handle this issue to fix this problem by using the updated library. IMO MySQL and its new standard gives the pace and HeidiSQL must adobt to it without such a workaround.

brutha_analog posted 5 months ago

Hi all, I'm having problems with the notorious, "cachingsha2..."

I tried Steinhaug's workaround in the .ini file, but no go. This may sound ignorant, but should we fill in the'the_username' with the root user?

I'm trying to get HeidSQL to work for a UDEMY course, but it aint going so hot...

Thanks for any help,

steinhaug posted 5 months ago

This may sound ignorant, but should we fill in the'the_username' with the root user?

Create a new user with a password, as your root user is already created and still requires the new library to connect. This new user you create will then have the old database connection, and when we get the updated library we can start using the root user as normal again.

brutha_analog posted 5 months ago

@steinhaug, looks like that worked. Now, to finish the class!

livvy posted 1 month ago

@brutha_analog I'm doing the same class and encountered the same problem. How did you finally resolve it? Thanks!

aderrose posted 1 month ago

@livvy Having spent a few hours today trying to get this working on a clean install VM (which is annoying because I have a development box and live server working just fine) I've cracked it.

  1. Install the latest nightly build from HeidiSQL.com, currently v9.5.0.5295.

  2. Copy libmysql.dll from MySQL to HeidiSQL (C:\Program Files\MySQL\MySQL Server 8.0\lib) currently v8.0.12.

  3. Delete libmariadb.dll from HeidiSQL as this seems to get in the way. If you download the official 9.5 release and upgrade to the nightly build in-app it doesn't get installed.

  4. Ensure you have Visual C++ 2013 Redistributable installed, (Google "latest visual c++ redistributable") currently v12.0.40660.0

  5. Enjoy HeidiSQL :)

This seems to be the cleanest way to do it and supports caching_sha2_password, I run one database in mysql_native_password mode but default to caching_sha2_password.

Hope this helps.

EggDude posted 4 weeks ago

Thank you aderrose, it worked!!

aderrose posted 4 weeks ago

Glad to be of help :)

Please login to leave a reply, or register at first.