Cannot reset MariaDB root password via panel interface

elzdave

Hello, I successfully compiled MariaDB 10.6.7 in my Aapanel installation on Debian (Raspberry Pi OS). Everything works fine, until I tried to change my root database password using panel Databases->Root Password interface. The panel says "Successfully modified root password!" but I cannot login to MariaDB root account using the newly-resetted password:

mysqli::real_connect(): (HY000/1698): Access denied for user 'root'@'localhost'

I tried using CLI interface to force reset password (sudo bt 7), it has an error:

ERROR 1356 (HY000) at line 1: View 'mysql.user' references invalid table(s) or column(s) or function(s) or definer/invoker of view lack rights to use them

What should I do? Any help would be appreciated.

elzdave

Solved.

So starting in MariaDB 10.4 onwards, the mysql.user is not a table anymore, thus requiring a new command to reset the password. Fortunately, the command is very simple:

SET PASSWORD FOR `root`@`localhost` = PASSWORD("new_password");
FLUSH PRIVILEGES;

With root is the root user, and new_password is the new password.

Maybe we can do a little bit automatic, we can wrap those expressions in one single command:

sudo mysql -u root -e 'SET PASSWORD FOR `root`@`localhost` = PASSWORD("new_password"); FLUSH PRIVILEGES;'

Maybe this can helps so many users with confusion on how to reset password MariaDB 10.4 or newer.

Reference:
Stackoverflow discussion
MariaDB SET PASSWORD

CC: @aaPanel_Kern

aaP_kotakomputer.ceo

elzdave Thanks for sharing, I just know that it is not a Table anymore, but a View.

elzdave

aaP_kotakomputer.ceo You're welcome. Me just discovered it too 😄