CODEWITHSUNDEEP
mysqlmariadb
Blaine Lafreniere
Blaine Lafreniere

Reputation: 3600

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password; -- fails with syntax error

Trying to enable regular password-based auth according to the below page: https://mariadb.com/kb/en/library/authentication-plugin-unix-socket/

The page suggests the following code:

ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
SET PASSWORD = PASSWORD('foo');

but on my machine it fails with a syntax error:

MariaDB [(none)]> ALTER USER root@localhost IDENTIFIED VIA mysql_native_password;
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 VIA mysql_native_password' at line 1
MariaDB [(none)]> SET PASSWORD = PASSWORD('foo');
Query OK, 0 rows affected, 1 warning (0.00 sec)

Upvotes: 12

Views: 33207

Answers (3)

diogob003
diogob003

Reputation: 176

Try

ALTER USER root@localhost IDENTIFIED BY PASSWORD '************';

Worked with me. I'm using mariaDB: 10.4.20-MariaDB

Upvotes: 1

mwieczorek
mwieczorek

Reputation: 2252

If you're running MariaDB < 10.2, the ALTER USER command will not work, as stated above.

To change the authentication method, use:

UPDATE mysql.user SET plugin = 'mysql_native_password' WHERE user = 'root';

Upvotes: 13

danblack
danblack

Reputation: 14691

ALTER USER "MariaDB starting with 10.2.0"

So 10.1 is too early. Also mysqld --version or SELECT VERSION() to show the version. mysql --version only shows the client version.

Upvotes: 4

Related Questions