Remote Database Setup

Its pretty clear that the installation does not know where the MySQL is or its not installed

Before anything make sure you have everything setup as per #Post 6 if your install is a remote db based one if its a regular install, please follow instructions on EPRNext wiki

Hey @saidsl, already used these commands but still getting error.

@Saditi
Unless you install mysql or do a complete install you are not going to get much further. As I mentioned, your MySQL DB is not installed. Be it locally or remotely.

So please check that you have the right setup before progressing further

Hello @saidsl,

Changes in instance server:
common_site_config.json

Changes in database server:
Mysql changes :

This is my my.cnf file :

Error while creating new site:

I can’t connect to the mariadb on other server:

ERROR 2002 (HY000): Can't connect to MySQL server on '10.10.128.209' (115)
  • Should I open any port on the ERPNext server or Mariadb server?
  • Is there any difference between connect using public or private IP?
  • In the db server should I install bench too? Or is it able only MariaDB?
  • Do I need to connect to certain database (of a site) or can it connect to mysql?

Thank you for any help.

Setup the remote DB to accept remote connections :

in /etc/mysql/my.cnf or /etc/my.cnf

comment out:
bind-address=0.0.0.0

bench mariadb

mysql -uuser -ppass

GRANT ALL PRIVILEGES ON . TO user@% IDENTIFIED BY ‘pass’ WITH GRANT OPTION;

only for specific user and database (example):

GRANT ALL PRIVILEGES ON [Database_Name].* TO *[User_Name]*@% IDENTIFIED BY ‘[Password]’ WITH GRANT OPTION;

Restart remote MySQL

sudo service mysql restart

Hi @Ernesto,
Sorry for not responding for too long.

There is no bind-address in my.cnf
but there is in /etc/my.cnf.d/mariadb-server.cnf and it’s already commented out.

It connects to a database [_xxxxxx].
Should it be to the root as it is for multi tenants.

Get a syntax error.

Hi Said, Do you have a clue about using the Azure Remote Database for Mariadb ?

Apparently the above steps won’t work with that (works perfectly with the rest).

Hi @Shinzuco - Unfortunately, I have not worked with Azure Remote DB. The setup outlined was meant for Linux based server setups.

However, It should not be any different to other setups in that, if you can access the DB externally than you would be able to connect to it.

You might want to look at the privileges/permissions on your Azure DB and whether external connections are permitted. I know some require tokens etc.

Regards - Said

It’s fairly easy to set up , the problem with Azure Remote DB is it accepts username in a “username@hostname” format and this conflicts with frappe’s code which uses “username” to create new db’s manage access etc.

@Shinzuco
I had also faced the same issue when I was trying to use RDS MariaDB and I resolved using just change the username in RDS MariaDB.

Yeah, it works for amazon since the username expected is in “username” format. Azure requires it to be sent in username@hostname format in the connection string and this cannot be changed from azure’s side afaik. We might need to customize database.py

If I am hosted by Erpnext.com can I Remote Database?

Thank you for your help!

I have App server as follows 192.168.1.26 and Mariadb DB server as follows 192.168.1.25 separately installed. Below is the App Server config..

Above contains common_site_config.json
Ive added through bench set-mariadb-host 192.168.1.25


this contains site_config.json on the app server.

For the DB server config pls refer to below :


this instance contains DB server 192.168.1.25 having mariadb. and config as above.

when i try to take the site backup by following command:
App_site_backup

Its giving the below error as
Backup failed for site demo1.in. Database or site_config.json may be corrupted.

For the Database to access it from App server given permission required.
But somehow its creating Database in DB server but couldn’t able to take the backup.
Please help me on this..Thanks in Advance

You may have missed --no-mariadb-socket while creating new site. If you use that then it’ll create db user with wildcard host.

Refer this to fix it https://github.com/frappe/frappe_docker/blob/main/docs/troubleshoot.md#fixing-mariadb-issues-after-rebuilding-the-container

Tried, but the issue still persists…Please suggest.
Thanks in Advance

drop the user with ip as host, just keep the user with wildcard host.

I’m struggling to implement a remote database server.

When I run:

bench new-site test_04.site --verbose --db-host db1 --db-name frappe_04 --mariadb-user-host-login-scope='%'

I get this error:

                   ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1044, "Access denied for user 'root'@'%' to database 'frappe_04'")

The remote database server is:

hostname = db1

ip = 10.1.22.40

Frappe server IP = 10.1.22.142

I think the database server is setup correctly:

commented out bind address, and set up root@%.

I can connect to db1 from frappe server like:

mysql -h 10.1.22.40 -uroot -p
or
mysql -h db1 -uroot -p

After logged in, I run:

MariaDB [(none)]> show grants;
+--------------------------------------------------------------------------------------------------------------+
| Grants for root@%                                                                                            |
+--------------------------------------------------------------------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO `root`@`%` IDENTIFIED BY PASSWORD '*FEC0B5A39...' |
+--------------------------------------------------------------------------------------------------------------+
1 row in set (0.001 sec)

I don’t understand the error:

pymysql.err.OperationalError: (1044, "Access denied for user 'root'@'%' to database 'frappe_04'")
MariaDB [mysql]> select user,host from user where user like "root";
+------+-----------+
| User | Host      |
+------+-----------+
| root | %         |
| root | localhost |
+------+-----------+

Is root@localhost causing a conflict? They do have unique passwords.

When I run the create site command, the remote database and user are created, but the database is empty.

MariaDB [mysql]> use frappe_04;
Database changed
MariaDB [frappe_04]> show tables;
Empty set (0.001 sec)

MariaDB [frappe_04]> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [mysql]> show grants for frappe_04@'%';
+----------------------------------------------------------------------------------------------------------+
| Grants for frappe_04@%                                                                                   |
+----------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO `frappe_04`@`%` IDENTIFIED BY PASSWORD '*E7AD2E0B45EC27DF715F168526459E3D06E9DAC2' |
+----------------------------------------------------------------------------------------------------------+

I try:

bench new-site test_05.site --verbose --db-host db1 --db-name frappe_05 --mariadb-user-host-login-scope='%' --db-root-username root@'%'

but get:

(1045, "Access denied for user 'root@%'@'10.1.22.142' (using password: YES)")

Is this a remote database server issue or frappe config issue?

I see the problem.

I ran:

CREATE USER 'root'@'%' IDENTIFIED BY 'mysupersecretpassword';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%'

But I should’ve ran:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '[ROOT-SQL-PASSWORD]' WITH GRANT OPTION;

On the remote database server

Quick comment - do not know if relevant - since our mariadb container is a shared resource between erpnext and wordpress (say) - we created separate dbusers for each application and grants / privileges restricted to specific databases. Did not read your post in full - but “grant all privileges on *.* (all databases) to user root @% - may not be optimal from a security risk perspective. My two cents…