• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Liutauras Vilda
  • Bear Bibeault
  • Jeanne Boyarsky
  • Tim Cooke
Sheriffs:
  • Knute Snortum
  • Junilu Lacar
  • Devaka Cooray
Saloon Keepers:
  • Ganesh Patekar
  • Tim Moores
  • Carey Brown
  • Stephan van Hulst
  • salvin francis
Bartenders:
  • Ron McLeod
  • Frits Walraven
  • Pete Letkeman

Authentication String of Root User on MySQL  RSS feed

 
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have spent a lot of time trying to figure this out and have finally reached quite close to the solution as pointed out in this article:

https://www.percona.com/blog/2016/03/16/change-user-password-in-mysql-5-7-with-plugin-auth_socket/

Especially this line:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test';

But, when I run this on my command line I get the following:

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'test'; ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'localhost'

Further down below in this article there is a comment by someone:

If the commande “ALTER USER ‘root’@’localhost’ IDENTIFIED WITH mysql_native_password BY ‘test’;” doesn’t work you can try :

UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’; FLUSH PRIVILEGES;

When I try this I get the following:

mysql> UPDATE mysql.user SET authentication_string = PASSWORD(‘test’), plugin = ‘mysql_native_password’ WHERE User = ‘root’ AND Host = ‘localhost’; ERROR 1054 (42S22): Unknown column '‘root’' in 'where clause'

I can see the problem that my root user has no password set. That is why I cannot login as root and I have combed through all the threads here discussing that without any solution.

Here are some warnings that were helpful to me to find the above resources.

Root user has no authentication_string set

https://gyazo.com/fef2073150114ae487f6a0b3b6cf3f96

Any help is much appreciated.

Thanks.
 
Sheriff
Posts: 21503
96
Chrome Eclipse IDE Java Spring Ubuntu VI Editor Windows
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Make sure you use ' for strings, not anything other like or . (Note: these last two are rendered differently, but I mean the characters around root.)
 
Tajeshwar Khara
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

Rob Spoor wrote:Make sure you use ' for strings, not anything other like or . (Note: these last two are rendered differently, but I mean the characters around root.)



Thanks this worked But, I am still having issues with my overall problem of setting a password (or authentication string) for the root user.

Thanks.
 
Bartender
Posts: 19814
93
Android Eclipse IDE Linux
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You should be able to to this directly with an update mysql.user set password=password('newpassword') where user = 'root' and host = 'localhost'.

But the root user is a little different, so you might want to check the MySQL docs for "gotcha"s. I'm pretty sure that there are explicit instructions on setting the root password, since that's one of the first things you're cautioned to do after installing MySQL server.

Alas, too often, I'm going the other direction, where I'm resetting the root password. For that you have to start the server in unsecured mode.
 
It is sorta covered in the JavaRanch Style Guide.
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!