[MDEV-14450] Root password lost on restart; can't log in afterward. Created: 2017-11-20  Updated: 2017-11-21  Resolved: 2017-11-21

Status: Closed
Project: MariaDB Server
Component/s: Authentication and Privilege System
Affects Version/s: None
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Marcus Hoffman Assignee: Unassigned
Resolution: Not a Bug Votes: 0
Labels: None


 Description   

This is reproducible on AltLinux and Ubuntu 16.04.

MariaDB version:
mysqld Ver 10.1.27-MariaDB-alt1.M80P.1 for Linux on x86_64 ((ALT))

Steps:

1) Install MariaDB as normal (apt-get install mariadb, or use aptitude)
2) Set root password with /bin/mysqld_secure_installation, including a flush privileges
3) Modify /etc/my.cnf.d/server.conf with the following:

  • disable skip-networking
  • enable bind-address=0.0.0.0
  • change server-id
    4) Validate root user can log in (mysql -u root -p), success
    5) Restart mysqld using systemctl restart mysqld
    6) Try to log in as root user (mysql -u root -p), authentication failure, both with and without passwords.
    7) Running with --no-grant-tables yields the following output (attempting to set the password to qwerty) 

    [root@hostname]# mysqld_safe --skip-grant-tables &
    		 
    [1] 39760
    		 
    [root@hostname]# [1]+  Done                    mysqld_safe --skip-grant-tables
    		 
     
    		 
    [root@hostname]# ps auxwwww | grep mysqld
    		 
    root     39786  0.0  0.0   8604  2628 pts/1    S    21:40   0:00 /bin/sh /usr/sbin/mysqld_wrapper --skip-grant-tables
    		 
    mysql    39791  7.2  1.7 719176 139904 pts/1   SNl  21:40   0:00 /usr/sbin/mysqld --skip-grant-tables
    		 
    root     39819  0.0  0.0   8256   860 pts/1    S+   21:40   0:00 grep mysqld
    		 
    [root@hostname]# mysqladmin -u root password qwerty
    		 
    mysqladmin:
    		 
    You cannot use 'password' command as mysqld runs
    		 
     with grant tables disabled (was started with --skip-grant-tables).
    		 
    Use: "mysqladmin flush-privileges password '*'" instead
    		 
    [root@hostname]# mysqladmin flush-privileges password 'qwerty'
    		 
    mysqladmin: unable to change password; error: 'You are using MariaDB as an anonymous user and anonymous users are not allowed to modify user settings'
    		 
    [root@hostname]#

So far as I can tell, this version of MariaDB literally will lose the root password in a way that requires reinstallation. Unless there's something obvious here that I'm missing?

 Comments   
Comment by Elena Stepanova [ 2017-11-20 ]

get.marcus.h,

For the proper password resetting procedure, please check various instructions and manuals. For example, this one, since you're on Ubuntu:
https://help.ubuntu.com/community/MysqlPasswordReset

For what happens with the password to begin with – I don't know how AltLinux builds and packages MariaDB, but Debian (and Ubuntu, I believe) in their packages configure local root with unix_socket plugin authentication. It means that the password for the local root is ignored, but you can only login if you are a system root, e.g. under sudo.

To check if it's the case, try to connect as sudo mysql -uroot, and to confirm further, run select user, host, plugin from mysql.user where user = 'root'.

If the user is configured to be authenticated via unix_socket, just changing the password won't help, you also need to unset unix_socket plugin in mysql.user table. However, keep in mind that if the installation is indeed configured this way, then probably startup/shutdown/monitoring scripts rely on it, and they might stop working if you change it.

Comment by Marcus Hoffman [ 2017-11-21 ]

This was a total error on my part- part of our bootstrapping left some errata that made MySQL wipe the user table. Please close the ticket!

Generated at Thu Feb 08 08:13:39 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.