0

Wordpress and MariaDB are on different hosts. I'm connected to MariaDB via it's CLI command interface. I am following the DigtalOcean guide.

Here are the DB and OS info:

  • 10.3.32-MariaDB-0ubuntu0.20.04.1
  • Ubuntu 20.04

Here is the command I'm using to create the user:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4' 
  IDENTIFIED WITH mysql_native_password BY 'PasswordAlphaNumeric';

And here is the error message:

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

Where:

  • user_name_for_wordpress: Username with upper and lower case
  • 1.2.3.4: WordPress server IP address
  • PasswordAlphaNumeric: The password

What am I doing wrong?

2
  • May be check the CREATE USER syntax.
    – mustaccio
    Commented Dec 22, 2021 at 20:24
  • Yes, I tried this syntax (that as @dbdemon mentioned) even though my version is older. And it did work! Thanks! Commented Dec 23, 2021 at 15:57

1 Answer 1

0

Small syntax issue, but this requires MariaDB 10.4.0 or later:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4' 
  IDENTIFIED WITH mysql_native_password 
  USING PASSWORD('PasswordAlphaNumeric');

The solution for you is to simply not specify the authentication plugin as it will default to mysql_native_password anyway. So:

CREATE USER 'user_name_for_wordpress'@'1.2.3.4'
  IDENTIFIED BY 'PasswordAlphaNumeric';

According to the MariaDB Knowledgebase article about authentication plugins:

By default, when you create a user account without specifying an authentication plugin, MariaDB uses the mysql_native_password plugin.

5
  • I had already found documentation about the version issue, so I didn't even try. But after your answer, even though I'm still 10.3.32, I tried. It did work! Commented Dec 23, 2021 at 15:55
  • @GilbertoMartins Maybe your MariaDB server version is newer? Maybe 10.3.32 is the version of your MariaDB client, and that the server version is newer? To test, you can run the command status - it will show the client version on the first line and then the server version further down.
    – dbdemon
    Commented Dec 23, 2021 at 16:30
  • thank you again because you taught me this "status" command. never had heard of it before. Excellent!! The first line says 'mysql Ver 15.1 Distrib 10.3.32-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2', while the version line says 'Server version: 10.3.32-MariaDB-0ubuntu0.20.04.1 Ubuntu 20.04' Which version is the right one? Commented Dec 23, 2021 at 17:37
  • @GilbertoMartins It looks like you really do have version 10.3.32 for both client and server. So, it remains a mystery to me how you were able to use the first CREATE USER option in my answer which only works on 10.4+ according to the documentation and my own testing. Maybe Ubuntu or its upstream (Debian) have back-ported the feature from 10.4? That would surprise me, but I can't think of another explanation.
    – dbdemon
    Commented Dec 24, 2021 at 12:59
  • Likewise, I definitely do not think such "back-port" could take place. But as crazy as it may seem, it just worked really fine! Commented Dec 25, 2021 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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