M2 | CREAT USER statement giver syntax error when "WITH mysql_native_password" is used it it

Question

I am installing fresh Magento, after installing MariaDB serve, I am creating database user as:

mysql> CREATE USER 'magento'@'localhost' IDENTIFIED WITH mysql_native_password BY 'm0d1fyth15';

The problem is, this statement always shows syntax error message when I use "WITH mysql_native_password" but the same statment executes successfuly if I omit it and execute as:

mysql> CREATE USER 'magento'@'localhost' IDENTIFIED BY 'm0d1fyth15';

And if I want to install Magento with this user I get the error:

SQLSTATE[HY000] [1045] Access denied for user 'magento'@'localhost' (using password: YES)

Server version is: 10.3.34-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04

I have tried to alter the user created without "WITH mysql_native_password" as:

ALTER USER magento IDENTIFIED WITH mysql_native_password BY 'mypassword';

This statement also prompts the same syntax error message:

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 'mypassword'' at line 1ERROR 1064

Tutorial followed for installation is: https://www.rosehosting.com/blog/how-to-install-magento-2-4-3-on-ubuntu-20-04/

Answer 1

A quick check on the documentation page for mysql_native_password at https://mariadb.com/kb/en/authentication-plugin-mysql_native_password/ shows that the actual syntax is:

CREATE USER username@hostname IDENTIFIED VIA mysql_native_password USING 'PASSWORD123';

using VIA not WITH