MySQL Bugs: #81965: mysqladmin password fails silently if user plugin is not password auth

Bug #81965	mysqladmin password fails silently if user plugin is not password auth
Submitted:	22 Jun 2016 6:55	Modified:	9 Aug 2016 10:11
Reporter:	Philippe Florent	Email Updates:
Status:	Verified	Impact on me:	None
Category:	MySQL Server: Command-line Clients	Severity:	S2 (Serious)
Version:	5.7	OS:	Debian (8.5)
Assigned to:		CPU Architecture:	Any
Tags:	root password access denied headless install

Description:
as I install mysql community server I enter an empty password
it needs to be so cos I use SALT to push mysql server on debian boxes by remote configurations

but I cant find any way to set the password after that and log in as root

How to repeat:
then I try to set its password

sudo mysqladmin -u root -p password NEWPASSWORD

mysql -u root -p
Enter password: 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

I tried reseting the password but for version 5.7, there is no more Password field inthe user table, and all set/alter password commands, dont work due to using skip-grant-tables options

sudo service mysql stop  
sudo mysqld_safe --skip-grant-tables &

I tried
use mysql;update user set account_locked='N' where user='root';flush privileges;
update user set authentication_string=password('dfghdfgh') where User='root'

nothing works

I am not sure if it is a bug or a new "feature" of 5.7

Hi,

What is new in 5.7 is that it tries to be more secure by default, so if you enter an empty password during installation it will enable unix socket authentication for the root user.
What this means is simply that only the unix root user can log in as root, so:
sudo mysql -uroot
should work.

If you later wish to set a password, log in as above and change the password with
ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY 'yourpassword';

Setting the password by directly altering the mysql.user table with an UPDATE statement is discouraged in 5.7, and in this case would not work because the authentication method has been set to unix socket and not password.

Hope that helps!

As an extra note, the UPDATE statement might work if you also set the plugin field to 'mysql_native_password', but we recommend using ALTER USER instead.

However, mysqladmin not setting the password when socket auth is enabled sounds like a bug, and should be investigated further

socket or not, I cant login with root

so alternatively is there a way to setup a default password for a headless install of the community server ?

sudo debconf-set-selections <<EOT
mysql-community-server mysql-community-server/root-pass password mypass
mysql-community-server mysql-community-server/re-root-pass password mypass
EOT
then install as before.

ok I can login with root 

but how can I provide the debian package not to use any password or a default one ?

I use

sudo dpkg -i /home/eradmin/mysql57/mysql-community-server_5.7.12-1debian8_amd64.deb

Using the above debconf-set-selection command before running the dpkg command will let you set a default password, just replace mypass.

Ah, I may have misunderstood, you mean setting it not to use any password and no socket auth?

To set the server back to normal password authentication and empty password after installing with a blank password, you should be able to run:
sudo mysql -e "ALTER USER 'root'@'localhost' IDENTIFIED WITH 'mysql_native_password' BY '';"

If you installed with a password, use -uroot -p instead of sudo

ok I found it

sudo dpkg-preconfigure --frontend=noninteractive :-)

Were you able to get your setup working again?

??? 

well after the setup ran , the root password was invalidated
I had to login as root, then run the mysql client as root user, change the pass...
works so fine

the only issue (dont know if it is related) is that the SET PASSWORD/ALTER USER does not work on the command line 

still waiting someon to come back to me for this http://bugs.mysql.com/bug.php?id=82051&thanks=4

as a workaround Iset the password field in mysql.user table, but I have to figure the value first, a pain in the a$$

For the issue with setting the password with the command line: Can you reproduce with a completely plain password? e.g. just 'pass'
Might be a specific symbol that isn't passed correctly through

Original synopsis: mysql 5.7 : empty password during instals invalidates the root account

If MySQL is installed with an empty password on Debian/Ubuntu, the root user as UNIX socket authentication enabled for better default security.

If the user then tries to set a password using mysqladmin, the command will appear to succeed, but it won't be possible to log in with that password, since mysqladmin won't set the user plugin to mysql_native_password.

Posted by developer:
 
Same issue shows up when running mysql_secure_installation, as noted in https://bugs.launchpad.net/ubuntu/+source/mysql-5.7/+bug/1610574