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

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.erpnext.com/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

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

something like this …

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

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.

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'")

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:

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

Hi @chdecultot,

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