0

Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9

I have installed a LAMP stack on my Debian 9 machine and can not log in with the root user. From my searching, it seems that it's because the install commonly creates a root user without a password, causing it to default to unix_socket, which foils logging in with that user via terminal.

MariaDB [(none)]> SELECT user,authentication_string,plugin,host FROM mysql.user;
+------------+-------------------------------------------+-------------+-----------+ 
| user       | authentication_string                     | plugin      | host      |
+------------+-------------------------------------------+-------------+-----------+ 
| root       | *3624F23A73825C0D79BF3591774631513FE76C24 | unix_socket | localhost | | phpmyadmin |                                           |             | localhost |
+------------+-------------------------------------------+-------------+-----------+ 
2 rows in set (0.00 sec)

I found an article explaining how to resolve this issue by shutting down mysql, starting it with sudo mysqld_safe --skip-grant-tables & so I can log in and then running:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass';

then stopping and starting the daemon. My problem is that no matter how I format this line, I get:

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 'user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'mypass'' at line 1

I've tried tons of different formatting with backtics, case change and other iterations of the command found on the web but everything fails with the same result.

Could someone tell me how I go about changing this successfully?

  • What MySQL version are you using? – mustaccio Dec 5 '19 at 18:57
  • What version of MariaDB ??? – RolandoMySQLDBA Dec 5 '19 at 18:58
  • Sorry, version is: Server version: 10.1.41-MariaDB-0+deb9u1 Debian 9.9 – Schwim Dandy Dec 5 '19 at 19:28
0

As the manual says:

The ALTER USER statement was introduced in MariaDB 10.2.0.

Since you're at 10.1, you'll need to reset the password using the old, dangerous method:

update mysql.user 
 set authentication_string=password('mypass'),  
 plugin='mysql_native_password' 
where user='root'; 

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.