Help with --mariadb-root-username option to authenticate to MariaDB for new site creation

Install / Update
1

Hello,

As most distributions now ship with unix_socket authentication for MariaDB, I have been trying to use pass login credentials to the installer with --mariadb-root-username and --mariadb-root-password without success.

I have created a MariaDB user ‘dbadmin’ with root privileges, however, when using the following command to create a new site:

bench new-site test.site --db-name test --mariadb-root-username dbadmin --mariadb-root-password mydbpassword

I get the following error :

File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 166, in sql self.connect() File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 123, in connect self.use(self.user) File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 128, in use self._conn.select_db(db_name) File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 448, in select_db self._read_ok_packet() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 396, in _read_ok_packet pkt = self._read_packet() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet packet.check_error() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.InternalError: (1049, u"Unknown database 'dbadmin'")

It appears the installer is trying to access a database with the name set by --mariadb-root-username.

This post appears to offer a solution : https://discuss.frappe.io/t/new-site-creation-with-another-user-than-mariadbs-root/23939/4 - is this a viable permanent solution that could be merged to the repo?

I could switch MariaDB back to password authentication for the root user, but this is discouraged as it may break system scripts. ( See bottom of this page )

Ideally I would like to be able to create / update / delete sites with the --mariadb-root-username option, using a non-root user.

Am I missing something here? Has anyone been able to get this to work?

Many thanks in advance.
Marty

2

Have you granted previledges to this user dbadmin for the db . test ?

something like this …

mysql> GRANT ALL PRIVILEGES ON test.* TO 'dbadmin'@'localhost';
3

Thanks for the response @joshiparthin

The dbadmin user has all privileges for all databases:

show grants for 'dbadmin'@'localhost';

GRANT ALL PRIVILEGES ON *.* TO 'dbadmin'@'localhost' IDENTIFIED BY PASSWORD 'mydbadminpassword' WITH GRANT OPTION

Effectively the user dbadmin is a root user, just with a different name.

4

Just for completeness, I have tried explicitly granting the dbadmin user privileges to the test.* database, but the same error persists:

File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 166, in sql self.connect() File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 123, in connect self.use(self.user) File "/opt/frappe/frappe-bench/apps/frappe/frappe/database.py", line 128, in use self._conn.select_db(db_name) File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 448, in select_db self._read_ok_packet() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 396, in _read_ok_packet pkt = self._read_packet() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/connections.py", line 683, in _read_packet packet.check_error() File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/protocol.py", line 220, in check_error err.raise_mysql_exception(self._data) File "/opt/frappe/frappe-bench/env/local/lib/python2.7/site-packages/pymysql/err.py", line 109, in raise_mysql_exception raise errorclass(errno, errval) pymysql.err.InternalError: (1049, u"Unknown database 'dbadmin'")

5

Hi @Marty,

We haven’t been able to find a solution that could be merged for this issue yet.
Here is the detail of the Github issue with different solutions:
https://github.com/frappe/frappe/issues/3354

The easiest solution may be to create a database named like your root user manually first. Then you should be able to use your root user to create additional databases.

3 Likes
6

Hi @chdecultot,

Many thanks for your reply - your work-around has worked as suggested.
Much appreciated.

7

yeah his trick worked for me. But I really wonder why things have to be tricky for installation of such a splendid product.

Here is my simplistic scenario. I am installing ERPNext 14 in Oracle Virtual box based ubuntu 22.04 vm. I have created a user ERPNext while installing ubuntu in VM. I am following this link to install ERPNext. While installing there is a step to create a Frappe Bench User. I created a user called frappe.

Now all done and set, when I try to run the command
bench new-site x2.llc.in,

I get following error
pymysql.err.OperationalError: (1049, "Unknown database 'frappe'").

So, after reading your comment, I created a database frappe in MySql using phpmyadmin and then ran command

bench new-site x2.llc.in --db-name x2 --db-password password --db-root-username frappe --db-root-password password --admin-password password

And that created the database x2 in MySql and populated it with respective tables and master data. Also note that, dont create the actual database (in my case x1) in advance in MySql or else, it will give error like Exception(f"Database {db_name} already exists").

Is it so difficult to have installation as simple as installing Wordpress or Joomla.

8

this should fix it: fix: site creation using non-root users by ankush · Pull Request #19014 · frappe/frappe · GitHub