CODEWITHSUNDEEP
mysql
DelphiNewbie
DelphiNewbie

Reputation: 57

cannot change user to use mysql_native_password, operation failed

Having managed to get Delphi to connect to MySQL (see this question), I now find that it can't log in.

Looking at this answer, it seems that as Delphi is a 32-bit program, I can't use the new caching_sha2_password, and have to use the legacy mysql_native_password.

According to other answers there (and elsewhere on this site), all I need to do is...

alter user 'myusername' IDENTIFIED WITH mysql_native_password BY 'mypassword';

However, when I try this, I get the rather unhelpful message "Operation ALTER USER failed for 'myusername'@'%'"

I tried this with 'myusername'@'localhost', 'myusername'@'%' and 'myusername'@'127.0.0.1', but had the same problem with all of them.

The problem is that it doesn't tell me why it failed. I'm doing this as root, so I wouldn't expect it to be a permissions issue.

Anyone any ideas? Thanks

Upvotes: 1

Views: 353

Answers (1)

Avrohom Yisroel
Avrohom Yisroel

Reputation: 9460

I have a solution for you, but it depends on your intended usage of MySQL.

If you only want to use it with Delphi, and so are happy to live without the newer connection protocol, then you can just tell MySQL to use the old one, ie mysql_native_password.

Not sure if you can do this once it's installed, but if you uninstall and reinstall, then at one stage in the installation, you'll be asked which authentication type you want to use. Choose the legacy one...

enter image description here

The downside of this is that you won't be able to use the caching_sha2_password authentication in other scenarios (ie not from Delphi). If that's acceptable, then this is the way to go.

However, this will still leave you with a 64-bit version of libmysql.dll. If you go to the MySQL download page for version 5.1, then you can download that version as a zip file. The lib folder in there includes the DLL, as well as a correspondingly named .lib file. I don't know if you need to, but I copied both of these into C:\Program Files\MySQL\MySQL Server 8.0\lib (renaming the existing ones first!), and restarted Delphi, and all was well.

Hope that helps.

Upvotes: 1

Related Questions