New libmysq with auth plugin caching_sha2_password ?

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 ?

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

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

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

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.

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.

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,

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.

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

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

@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.

Thank you aderrose, it worked!!

Glad to be of help :)

Oh my,

I notice that there is an error in what I posted as sollution, MarkDown has not displayed it correctly or I simply missed it when posting. As I was reinstalling my computer after a HD-krasj I felt like copy'paste my sollution here... Dang!

So no wonder some of you had problems. The second line granting privileges are missing asterixes, just has a dott.

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

Escaping the asterixes. we get the correct output:

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

Note that "grant all privileges on *.* to", asterix'es were missing. The grant all privileges command can be very precise if you only want the login to work on one table, or database.

Good examples on this page.

• How to create a new user and grant permissions in MySQL