[MDEV-28390] Resetting the password causes A LOT OF troube^ Created: 2022-04-22  Updated: 2022-06-01  Resolved: 2022-06-01

Status: Closed
Project: MariaDB Server
Component/s: Admin statements, Documentation
Affects Version/s: 10.8.2
Fix Version/s: N/A

Type: Bug Priority: Major
Reporter: Norman Koch Assignee: Ian Gilfillan
Resolution: Incomplete Votes: 0
Labels: None
Environment:

Debian



 Description   

I've used MariaDB for a long time and it is really great for a lot of use cases. But when a user forgets the password, it really gets annoying quickly.
There are tons of different tutorials on how to reset it, but none work.

I've tried several versions of these scripts:

#!/bin/bash
 
set -x
 
sudo killall -9 mysqld_safe mysqld mariadb mariadbd
 
sudo service mysqld stop
 
sudo mysqld_safe --skip-grant-tables &
 
sleep 5
 
echo 'FLUSH PRIVILEGES; ALTER USER "root"@"localhost" IDENTIFIED BY "mynewpassword"; FLUSH PRIVILEGES;' | mysql -uroot
 
sleep 5
 
sudo killall -9 mysqld_safe mysqld mariadb mariadbd
 
if [[ -e /var/lib/mysql/aria_log_control_OLD ]]; then
    sudo rm /var/lib/mysql/aria_log_control_OLD
fi
sudo mv /var/lib/mysql/aria_log_control /var/lib/mysql/aria_log_control_OLD
 
sudo service mysqld start

set -x
sudo service mysqld stop
sudo apt-get -y purge mariadb-server
sudo rm -rf /var/lib/mysql
sudo apt-get -y install --reinstall mariadb-server
sudo mkdir /var/lib/mysql/
sudo killall -9 mysqld mariadbd mysqld mysql mysqld_safe
sudo service mariadb start
sudo mysqld_safe --skip-grant-tables &                  
sudo mysql_install_db --user=mysql
sudo mysql_secure_installation

 

and a lot of more stuff.

But nothing works anymore. I cannot even re-initialize the /var/lib/mysql directory. This NEEDS to be a simple call. mariadb_reinit_mysql_data_dir and done. Empty installation, just like after a fresh format of the system. No more questions asked.

There has to be an official documentation that just works. Maybe even a mariadb_forgot_password program that does all of the heavy lifting.

I have several computers which I had to re-install the whole OS or abandon development on because I could not figure out how to properly reset the password. There isn't even a way of re-initializing the /var/lib/mysql directory that just works. All of them plainly suck.

This may very well be my own fault, of course, but the lack of official documentation (or at least the impossibility of finding it) on this problem makes it REALLY annoying. Every page that comes up at google does these things differently, but none work, and after trying each of them the problem progressively gets worse all the time.



 Comments   
Comment by Daniel Black [ 2022-04-22 ]

A reddit post using init_file, is very close the the MySQL documentation for password resets.

A data directory reload would be:

systemctl stop mariadb.service
sudo -u mysql rm -rf  /var/lib/mysql/*
sudo -u mysql mariadb-install-db
systemctl start mariadb.service

greenman Any suggestions for where in the kb to find/add/include/update?

Comment by Norman Koch [ 2022-04-22 ]

This is worthless. I cannot login with that.

mysql -uroot            
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

mysql_secure_installation    
 
 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 
In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.
 
Enter current password for root (enter for none): 
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
Enter current password for root (enter for none): 

Comment by Sergei Golubchik [ 2022-05-05 ]

Normally, if you install 10.8 you won't need a root password. use sudo mysql to login.

Comment by Ian Gilfillan [ 2022-05-05 ]

The page https://mariadb.com/kb/en/troubleshooting-connection-issues/ is meant to be useful for connection issues. Does it help solve the problem? I suspect the issue here is not being aware of Unix socket authentication as the default since 10.4.

Generated at Thu Feb 08 10:00:22 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.