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:
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

[22 Jun 2016 6:55] Philippe Florent
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
[22 Jun 2016 17:21] Lars Tangvald

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!
[22 Jun 2016 17:25] Lars Tangvald
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.
[22 Jun 2016 17:30] Lars Tangvald
However, mysqladmin not setting the password when socket auth is enabled sounds like a bug, and should be investigated further
[23 Jun 2016 7:33] Philippe Florent
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 ?
[23 Jun 2016 7:46] Lars Tangvald
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
then install as before.
[23 Jun 2016 7:56] Philippe Florent
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
[23 Jun 2016 8:05] Lars Tangvald
Using the above debconf-set-selection command before running the dpkg command will let you set a default password, just replace mypass.
[23 Jun 2016 8:06] Lars Tangvald
Ah, I may have misunderstood, you mean setting it not to use any password and no socket auth?
[23 Jun 2016 8:15] Lars Tangvald
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
[23 Jun 2016 8:27] Philippe Florent
ok I found it

sudo dpkg-preconfigure --frontend=noninteractive :-)
[23 Jun 2016 10:03] Lars Tangvald
Were you able to get your setup working again?
[11 Jul 2016 9:32] Philippe Florent

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$$
[9 Aug 2016 9:38] Lars Tangvald
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
[9 Aug 2016 10:11] Lars Tangvald
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.
[12 Aug 2016 11:00] Lars Tangvald
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