Can't change blank root password for MariaDB
 

Hi,

I have a new install of OpenSUSE and I'm a newbie in Linux.

It comes with a pre-installed MariaDB, which doesn't have a root password. So I can login with 'root' and no password.

I tried to set it with:
SET PASSWORD FOR 'root' = PASSWORD('password');

But get:
ERROR 1133 (28000): Can't find any matching row in user table.

When searching online, there are many possible issues and the solutions I've tried doesn't work.

Anybody who has any clues?

https://linuxize.com/post/how-to-res...root-password/
If this does not help please explain what did you try exactly and what's happened.

Hi Pan64,

Thanks for your feedback.

------
Even though I can login without a password at the root, I also took the first steps;

linux-jfw1:~ # mysql --version
mysql Ver 15.1 Distrib 10.2.31-MariaDB, for Linux (x86_64) using EditLine wrapper
linux-jfw1:~ # systemctl stop mysql
linux-jfw1:~ # mysqld_safe --skip-grant-tables &
[1] 7213
linux-jfw1:~ # 200429 04:48:25 mysqld_safe Logging to '/var/log/mysql/mysqld.log'.
200429 04:48:25 mysqld_safe A mysqld process already exists

------

The first option didn't work out because of the --skip-grant-tables option:

FGS@linux-jfw1:~> mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 8
Server version: 10.2.31-MariaDB SUSE package

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> ALTER USER 'root'@'localhost' IDENTIFIED BY 'something'
-> ;
ERROR 1290 (HY000): The MariaDB server is running with the --skip-grant-tables option so it cannot execute this statement
MariaDB [(none)]> Ctrl-C -- exit!
Aborted
FGS@linux-jfw1:~>


------
The second option I tried and accidentially overwrote 6 passwords??? Although there are basically no tables in the database, are there any applications using the database in standard OpenSUSE? Afterwards it denied access with the added password, but accepted no password.

FGS@linux-jfw1:~> mysql -u root
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 10
Server version: 10.2.31-MariaDB SUSE package

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('something')
-> ;
Query OK, 6 rows affected (0.00 sec)
Rows matched: 6 Changed: 6 Warnings: 0

MariaDB [(none)]> WHERE User = 'root' AND Host = 'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE User = 'root' AND Host = 'localhost'' at line 1
MariaDB [(none)]> WHERE user = 'root' AND Host = 'localhost';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'WHERE user = 'root' AND Host = 'localhost'' at line 1
MariaDB [(none)]> UPDATE mysql.user SET authentication_string = PASSWORD('something')
-> WHERE user = 'root' and Host = 'localhost';
Query OK, 0 rows affected (0.01 sec)
Rows matched: 1 Changed: 0 Warnings: 0

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> exit
Bye
FGS@linux-jfw1:~> mysql -u root -p
Enter password:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
FGS@linux-jfw1:~> mysql -u root -p
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 12
Server version: 10.2.31-MariaDB SUSE package

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>



I'm mostly used with Windows and graphical userinterfaces, so bear with me :)-

would be nice to use code tags.
Yes it looks like you have set the password and not only for root, but for every user.
So how to go further?