Bench: new installation & database issues

RM13 · July 23, 2024, 5:20pm

Howdy guys,

We’ve been recently exploring ERPNext as an option for our next CRM/ERP software.
We dwelled a bit on the docker version just to see how it went, and we’re now setting up a new environment that matches our production setups.
So the target setup is the frappe framework installed using bench, and the database server is a separate instance.

Right now we have performed the following tasks:

Server running EL9
User for frappe
Python 3.11
Redis
NGINX
Supervisor
Fail2ban
NodeJS
Yarn
wkhtmltopdf
MariaDB-client
bench installed successfully: version 15

File                                                        Size
frappe/dist/js/
├─ bootstrap-4-web.bundle.FOZOVELL.js                       1.73 Kb
├─ controls.bundle.V232G3G2.js                              1234.62 Kb
├─ data_import_tools.bundle.GNL3BWOK.js                     126.56 Kb
├─ desk.bundle.57YFDHES.js                                  1345.79 Kb
├─ dialog.bundle.JUOABYOR.js                                57.42 Kb
├─ form.bundle.4O2LCAVM.js                                  167.85 Kb
├─ frappe-web.bundle.MTRV47AB.js                            829.37 Kb
├─ libs.bundle.TIV7ZGVY.js                                  556.25 Kb
├─ list.bundle.HO5SB67H.js                                  195.70 Kb
├─ logtypes.bundle.EKN7LWKW.js                              0.73 Kb
├─ onboarding_tours.bundle.RAUR6X4Z.js                      7.60 Kb
├─ report.bundle.GAFYCPKW.js                                197.43 Kb
├─ sentry.bundle.IPS6PK2M.js                                69.50 Kb
├─ telemetry.bundle.LKEZCADB.js                             2.59 Kb
├─ user_profile_controller.bundle.ZJ6AYZ5P.js               11.96 Kb
├─ video_player.bundle.DUYYLSFO.js                          120.67 Kb
├─ web_form.bundle.W5PVXWCW.js                              1426.57 Kb
├─ form_builder.bundle.XMZ2QWXC.js                          798.60 Kb
├─ form_builder.bundle.TCJ3OUGI.css                         23.19 Kb
├─ print_format_builder.bundle.SVKIATEY.js                  685.24 Kb
├─ print_format_builder.bundle.IT2A52GY.css                 5.54 Kb
├─ workflow_builder.bundle.MKQN2B5X.js                      351.72 Kb
├─ workflow_builder.bundle.TJDUANWC.css                     11.02 Kb
├─ build_events.bundle.AYWS3JOI.js                          105.71 Kb
├─ build_events.bundle.DWLH2NLQ.css                         1.29 Kb
├─ file_uploader.bundle.YSAX2FGX.js                         201.83 Kb
├─ file_uploader.bundle.MVFAD5HI.css                        6.53 Kb
└─ kanban_board.bundle.AS3FI5GX.js                          578.14 Kb

frappe/dist/css/
├─ desk.bundle.F2GOIPRZ.css                                 590.98 Kb
├─ email.bundle.PXNGSBSE.css                                5.97 Kb
├─ login.bundle.GOIMITRI.css                                32.29 Kb
├─ print.bundle.3772FXPE.css                                203.33 Kb
├─ print_format.bundle.MPBQEH5L.css                         186.11 Kb
├─ report.bundle.A7THXGQU.css                               5.33 Kb
├─ web_form.bundle.747R5M2P.css                             14.69 Kb
└─ website.bundle.TS5QVEYG.css                              443.56 Kb

frappe/dist/css-rtl/
├─ desk.bundle.W52GUF6B.css                                 591.49 Kb
├─ email.bundle.SFAK6D7D.css                                5.98 Kb
├─ login.bundle.AJKKLW6Q.css                                32.29 Kb
├─ print.bundle.XOJKR3MK.css                                203.49 Kb
├─ print_format.bundle.F6VCB3OE.css                         186.24 Kb
├─ report.bundle.NOD3ATKH.css                               5.33 Kb
├─ web_form.bundle.L2OCOJIU.css                             14.68 Kb
└─ website.bundle.M5GEY225.css                              443.72 Kb

 DONE  Total Build Time: 13.102s

 WARN  Cannot connect to redis_cache to update assets_json
 WARN  Cannot connect to redis_cache to update assets_json
 WARN  Cannot connect to redis_cache to update assets_json
Done in 13.93s.
Compiling translations for frappe
SUCCESS: Bench bench initialized

On the database server we created an user:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'erpnext-server-ip' WITH GRANT OPTION;

Added

"db_host": "server-hostname",
"db_port": 3306,

When I try to add a site:

bench new-site new.site --db-name erp_next1 --install-app erpnext --verbose
MySQL root password: 
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
    main()
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/commands/site.py", line 68, in new_site
    _new_site(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 87, in _new_site
    install_db(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 160, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/opt/frappe/bench/apps/frappe/frappe/database/__init__.py", line 21, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 31, in setup_database
    if force or (db_name not in dbman.get_database_list()):
                                ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/db_manager.py", line 50, in get_database_list
    return self.db.sql("SHOW DATABASES", pluck=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 211, in sql
    self.connect()
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 117, in connect
    self._conn: "MariadbConnection" | "PostgresConnection" = self.get_connection()
                                                             ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 107, in get_connection
    conn = self._get_connection()
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 113, in _get_connection
    return self.create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 116, in create_connection
    return pymysql.connect(**self.get_connection_settings())
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 361, in __init__
    self.connect()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 669, in connect
    self._request_authentication()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 957, in _request_authentication
    auth_packet = self._read_packet()
                  ^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'server-ip' (using password: YES)")

However, from the same host I have NO ISSUES at all connecting to the database server:

mysql -h db-server -u root -p
mysql: Deprecated program name. It will be removed in a future release, use '/usr/bin/mariadb' instead
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 168
Server version: 10.6.18-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| ...                |
| information_schema |
| ...                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
21 rows in set (0.001 sec)

MariaDB [(none)]> show session status like 'ssl_cipher';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| Ssl_cipher    | TLS_AES_256_GCM_SHA384 |
+---------------+------------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> ^DBye

I am simply unable to add sites. The DB part always fails.
There are no SELinux events.

Suggestions are appreciated, thank you.

Adeel_Siddiqui · July 23, 2024, 6:07pm

Are you using docker compose for it? Are both instances on the same network? Are you able to connect to the database from inside the docker instance’s shell?

RM13 · July 23, 2024, 6:19pm

I am sorry, maybe I didn’t express myself correctly.

We tried docker just to see how ERPNext was.
We are not using docker nor docker compose now.

We are using the frappe framework via bench, without any docker in the mix. Thank you.

Adeel_Siddiqui · July 23, 2024, 6:42pm

Try to just create site first and then install app later on separately
Is ‘root’ user allowed to connect from the ERPNext server’s IP address? You can check the host permissions by running the following command on the MariaDB server:

SELECT * FROM mysql.user WHERE user = 'root';

RM13 · July 23, 2024, 7:33pm

Hi @Adeel_Siddiqui

Thank you for your feedback. Regarding your second point, on my initial post in the end you can see a connection to the database server. The connection is initiated from the ERPNext server, to the database server, using the root user and root password used in bench.

Regarding the first point, I tested adding a site only, without an app, as suggested.
Output:

bench new-site erpnext.domain.net --db-name erp_next --mariadb-root-password 'password' --verbose
Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
    main()
  File "/opt/frappe/bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1688, in invoke
    return _process_result(sub_ctx.command.invoke(sub_ctx))
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/commands/site.py", line 68, in new_site
    _new_site(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 87, in _new_site
    install_db(
  File "/opt/frappe/bench/apps/frappe/frappe/installer.py", line 160, in install_db
    setup_database(force, source_sql, verbose, no_mariadb_socket)
  File "/opt/frappe/bench/apps/frappe/frappe/database/__init__.py", line 21, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 31, in setup_database
    if force or (db_name not in dbman.get_database_list()):
                                ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/db_manager.py", line 50, in get_database_list
    return self.db.sql("SHOW DATABASES", pluck=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 211, in sql
    self.connect()
  File "/opt/frappe/bench/apps/frappe/frappe/database/database.py", line 117, in connect
    self._conn: "MariadbConnection" | "PostgresConnection" = self.get_connection()
                                                             ^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 107, in get_connection
    conn = self._get_connection()
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 113, in _get_connection
    return self.create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/apps/frappe/frappe/database/mariadb/database.py", line 116, in create_connection
    return pymysql.connect(**self.get_connection_settings())
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 361, in __init__
    self.connect()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 669, in connect
    self._request_authentication()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 957, in _request_authentication
    auth_packet = self._read_packet()
                  ^^^^^^^^^^^^^^^^^^^
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 775, in _read_packet
    packet.raise_for_error()
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/protocol.py", line 219, in raise_for_error
    err.raise_mysql_exception(self._data)
  File "/opt/frappe/bench/env/lib/python3.11/site-packages/pymysql/err.py", line 150, in raise_mysql_exception
    raise errorclass(errno, errval)
pymysql.err.OperationalError: (1045, "Access denied for user 'root'@'10.0.0.1' (using password: YES)")

From the same server:

mariadb -h db-server.internal.domain.net -uroot -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 615
Server version: 10.6.18-MariaDB-log MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select user();
+------------------+
| user()           |
+------------------+
| root@10.0.0.1 |
+------------------+
1 row in set (0.001 sec)

MariaDB [(none)]> ^DBye
[frappe@erpnext bench]$

avc · July 23, 2024, 10:29pm

Hi @RM13:

Try creating your site this way:
bench new-site yoursite.localhost --no-mariadb-socket

Hope this helps.

RM13 · July 24, 2024, 9:09am

Hi @avc thank you for your suggestion.

I tried creating a site using --no-mariadb-socket but the error is the exact same as before. I won’t paste the output because it is literally the same.

However, I made a simple shell script to test my connection to the server.
This is the script:

#!/usr/bin/env sh

MARIADB_USER="root"
MARIADB_PSWD="db-root-password"
MARIADB_SERV="db-server-host"

execute_query() {
	local query=$1
	mariadb -h "$MARIADB_SERV" -u "$MARIADB_USER" -p"$MARIADB_PSWD" -e "$query"
}

# Function to list user info
display_user() {
	execute_query "SELECT user()"
}

display_user

And the output of running the script:

[frappe@erpnext bench]$ ./database-connect.sh 
+------------------+
| user()           |
+------------------+
| root@10.0.0.1 |
+------------------+

I was wondering if someone could throw a similar script but in python?

Is there any incompatibility between special characters on passwords that could be conflicting the python connection using bench?

RM13 · July 24, 2024, 11:55am

New update

Got this python script to do exactly the same as the previous shell script:

cat database-connect.py

import mariadb

# MariaDB login details
MARIADB_USER = 'root'
MARIADB_PSWD = 'root-password'
MARIADB_SERV = 'db-server.internal.net'

def execute_query(query):
    conn = mariadb.connect(
        user=MARIADB_USER,
        password=MARIADB_PSWD,
        host=MARIADB_SERV,
        port=3306
    )
    cursor = conn.cursor()
    cursor.execute(query)
    result = cursor.fetchall()
    cursor.close()
    conn.close()
    return result

# Function to list user info
def display_user():
    query = "SELECT user()"
    result = execute_query(query)
    print(result)

display_user()

And the output is:

python3.11 database-connect.py 
Traceback (most recent call last):
  File "/home/myuser/database-connect.py", line 29, in <module>
    display_user()
  File "/home/myuser/database-connect.py", line 26, in display_user
    result = execute_query(query)
             ^^^^^^^^^^^^^^^^^^^^
  File "/home/myuser/database-connect.py", line 9, in execute_query
    conn = mariadb.connect(
           ^^^^^^^^^^^^^^^^
  File "/home/myuser/.local/lib/python3.11/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/myuser/.local/lib/python3.11/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
mariadb.OperationalError: Access denied for user 'root'@'10.0.0.1' (using password: YES)

So I am believing the problem is not directly related to the frappe framework or ERPNext, but definitely there’s some issue with the underlying python connector.
I’ll keep digging and update as I go.

RM13 · July 29, 2024, 2:03pm

If anyone comes around this, don’t use the mariadb-connector.

tested using mysql connector and importing mysql.connector in python script, authenticates successfully.
Frappe PyMySQL still failing