Creating user in MySQL with “mysql_native_password” spitting syntax error

Problem

I was trying to create a MySQL user with this command

CREATE USER 'user@localhost' IDENTIFIED WITH mysql_native_password BY 'fTNg3gHQdSmDsJp';

Although there is no syntax error in the command, I still got an error.

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 'BY 'fTNg3FQdSmDsJp'' at line 1

Solution

Turn out the problem arise because I am using MariaDB.

For MariaDB, I have to use USING PASSWORD() for the password

CREATE USER 'user'@'localhost' IDENTIFIED WITH mysql_native_password USING PASSWORD('fTNg3FQdSmDsJp');