Issue with MariaDB on creating site

Hello guys
I am building a custom image using Podman. The building process is successful but when I try to create sites it shows an error saying connection refused from Mariadb. But, the site will be created by checking on the sites directory it is visible.

What I am trying to achieve is to install erpnext on site1.mydomain and helpdesk on site2.mydomain. Therefore, I need to create two sites with dns_multitenant on. I tried following the following guideline: frappe_docker/docs/troubleshoot.md at main · frappe/frappe_docker · GitHub. But that didn’t help.
Here are some screenshots from different bench commands:

bench$ bench new-site erp --mariadb-root-password 123 --admin-password 123123
Output

Traceback (most recent call last):
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 649, in connect
    sock = socket.create_connection(
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/usr/local/lib/python3.11/socket.py", line 851, in create_connection
    raise exceptions[0]
  File "/usr/local/lib/python3.11/socket.py", line 836, in create_connection
    sock.connect(sa)
ConnectionRefusedError: [Errno 111] Connection refused

During handling of the above exception, another exception occurred:

Traceback (most recent call last):
  File "<frozen runpy>", line 198, in _run_module_as_main
  File "<frozen runpy>", line 88, in _run_code
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 114, in <module>
    main()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/utils/bench_helper.py", line 20, in main
    click.Group(commands=commands)(prog_name="bench")
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/click/core.py", line 1157, in __call__
    return self.main(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/click/core.py", line 1078, in main
    rv = self.invoke(ctx)
         ^^^^^^^^^^^^^^^^
  File "/home/frappe/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 "/home/frappe/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 "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/click/core.py", line 1434, in invoke
    return ctx.invoke(self.callback, **ctx.params)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/click/core.py", line 783, in invoke
    return __callback(*args, **kwargs)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/commands/site.py", line 99, in new_site
    _new_site(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 90, in _new_site
    install_db(
  File "/home/frappe/frappe-bench/apps/frappe/frappe/installer.py", line 170, in install_db
    setup_database(force, verbose, mariadb_user_host_login_scope)
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/__init__.py", line 21, in setup_database
    return frappe.database.mariadb.setup_db.setup_database(force, verbose, mariadb_user_host_login_scope)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/setup_db.py", line 32, in setup_database
    if force or (db_name not in dbman.get_database_list()):
                                ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/db_manager.py", line 50, in get_database_list
    return self.db.sql("SHOW DATABASES", pluck=True)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 206, in sql
    self.connect()
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/database.py", line 111, in connect
    self._conn: "MariadbConnection" | "PostgresConnection" = self.get_connection()
                                                             ^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 107, in get_connection
    conn = self._get_connection()
           ^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 113, in _get_connection
    return self.create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/apps/frappe/frappe/database/mariadb/database.py", line 116, in create_connection
    return pymysql.connect(**self.get_connection_settings())
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 361, in __init__
    self.connect()
  File "/home/frappe/frappe-bench/env/lib/python3.11/site-packages/pymysql/connections.py", line 716, in connect
    raise exc
pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '127.0.0.1' ([Errno 111] Connection refused)")

The network connection seems fine

frappe@37e1f9695067:~/frappe-bench$ mysql -uroot -p123 -hdb
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 82
Server version: 10.6.20-MariaDB-ubu2004 mariadb.org binary distribution

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)]> 


Checking for duplicate entry, I got the following result but couldn’t find a duplicate entry

MariaDB [(none)]> SELECT User, Host FROM mysql.user;
+-------------+-----------+
| User        | Host      |
+-------------+-----------+
| root        | %         |
| healthcheck | 127.0.0.1 |
| healthcheck | ::1       |
| healthcheck | localhost |
| mariadb.sys | localhost |
| root        | localhost |
+-------------+-----------+
6 rows in set (0.001 sec)

Screenshot from podman-desktop

I tried rebuilding the custom image multiple times but this is how it goes, Can someone help me, please? Thanks

What is the mariadb container logs showing ? It seems that it’s not reachable.

It is ready to accept connections

2024-11-24 06:36:50+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.20+maria~ubu2004 started.
2024-11-24 06:36:51+00:00 [Note] [Entrypoint]: Switching to dedicated user 'mysql'
2024-11-24 06:36:51+00:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:10.6.20+maria~ubu2004 started.
2024-11-24 06:36:51+00:00 [Note] [Entrypoint]: Initializing database files
2024-11-24 06:36:53+00:00 [Note] [Entrypoint]: Database files initialized
2024-11-24 06:36:53+00:00 [Note] [Entrypoint]: Starting temporary server
2024-11-24 06:36:53+00:00 [Note] [Entrypoint]: Waiting for server startup
�2024-11-24  6:36:53 0 [Note] Starting MariaDB 10.6.20-MariaDB-ubu2004 source revision f00711bba2cd383825d0be1867f7d7d7f641c9e4 server_uid ujW9mPf1CAf70z447TwEiyVPwPc= as process 101
2024-11-24  6:36:53 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-11-24  6:36:53 0 [Note] InnoDB: Number of pools: 1
2024-11-24  6:36:53 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-11-24  6:36:53 0 [Note] InnoDB: Using Linux native AIO
2024-11-24  6:36:53 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2024-11-24  6:36:53 0 [Note] InnoDB: Completed initialization of buffer pool
2024-11-24  6:36:53 0 [Note] InnoDB: 128 rollback segments are active.
2024-11-24  6:36:53 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-11-24  6:36:53 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-11-24  6:36:53 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-11-24  6:36:53 0 [Note] InnoDB: 10.6.20 started; log sequence number 42486; transaction id 14
2024-11-24  6:36:53 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-11-24  6:36:53 0 [Note] mariadbd: ready for connections.
Version: '10.6.20-MariaDB-ubu2004'  socket: '/run/mysqld/mysqld.sock'  port: 0  mariadb.org binary distribution
2024-11-24 06:36:54+00:00 [Note] [Entrypoint]: Temporary server started.
2024-11-24  6:36:54 5 [Warning] Access denied for user 'root'@'localhost' (using password: YES)
2024-11-24 06:36:55+00:00 [Note] [Entrypoint]: Securing system users (equivalent to running mysql_secure_installation)

2024-11-24 06:36:55+00:00 [Note] [Entrypoint]: Stopping temporary server
2024-11-24  6:36:55 0 [Note] mariadbd (initiated by: unknown): Normal shutdown
2024-11-24  6:36:55 0 [Note] InnoDB: FTS optimize thread exiting.
2024-11-24  6:36:55 0 [Note] InnoDB: Starting shutdown...
2024-11-24  6:36:55 0 [Note] InnoDB: Dumping buffer pool(s) to /var/lib/mysql/ib_buffer_pool
2024-11-24  6:36:55 0 [Note] InnoDB: Buffer pool(s) dump completed at 241124  6:36:55
2024-11-24  6:36:55 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2024-11-24  6:36:55 0 [Note] InnoDB: Shutdown completed; log sequence number 42514; transaction id 15
2024-11-24  6:36:55 0 [Note] mariadbd: Shutdown complete

2024-11-24 06:36:55+00:00 [Note] [Entrypoint]: Temporary server stopped

2024-11-24 06:36:55+00:00 [Note] [Entrypoint]: MariaDB init process done. Ready for start up.

�2024-11-24  6:36:55 0 [Note] Starting MariaDB 10.6.20-MariaDB-ubu2004 source revision f00711bba2cd383825d0be1867f7d7d7f641c9e4 server_uid ujW9mPf1CAf70z447TwEiyVPwPc= as process 1
2024-11-24  6:36:55 0 [Note] InnoDB: Compressed tables use zlib 1.2.11
2024-11-24  6:36:55 0 [Note] InnoDB: Number of pools: 1
2024-11-24  6:36:55 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2024-11-24  6:36:55 0 [Note] InnoDB: Using Linux native AIO
2024-11-24  6:36:55 0 [Note] InnoDB: Initializing buffer pool, total size = 134217728, chunk size = 134217728
2024-11-24  6:36:55 0 [Note] InnoDB: Completed initialization of buffer pool
2024-11-24  6:36:55 0 [Note] InnoDB: 128 rollback segments are active.
2024-11-24  6:36:55 0 [Note] InnoDB: Creating shared tablespace for temporary tables
2024-11-24  6:36:55 0 [Note] InnoDB: Setting file './ibtmp1' size to 12 MB. Physically writing the file full; Please wait ...
2024-11-24  6:36:55 0 [Note] InnoDB: File './ibtmp1' size is now 12 MB.
2024-11-24  6:36:55 0 [Note] InnoDB: 10.6.20 started; log sequence number 42514; transaction id 14
2024-11-24  6:36:55 0 [Note] Plugin 'FEEDBACK' is disabled.
2024-11-24  6:36:55 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2024-11-24  6:36:55 0 [Note] InnoDB: Buffer pool(s) load completed at 241124  6:36:55
2024-11-24  6:36:55 0 [Warning] You need to use --log-bin to make --expire-logs-days or --binlog-expire-logs-seconds work.
2024-11-24  6:36:55 0 [Note] Server socket created on IP: '0.0.0.0'.
2024-11-24  6:36:55 0 [Note] Server socket created on IP: '::'.
2024-11-24  6:36:55 0 [Note] mariadbd: ready for connections.
Version: '10.6.20-MariaDB-ubu2004'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

set db_host in common_site_config.json. Run the configure job/task.

1 Like

mysql -uroot -p123 -hdb

you need to set host to db not 127.0.0.1. Do not change anything in MariaDB configuration.

127.0.0.1 is to connect from your machine to container. db is to connect between containers. Also make sure that db container is in the same network as frappe.

1 Like

thank you @revant_one and @aldoblack for replying,

set db_host in common_site_config.json. Run the configure job/task.
Do I need to set/change this setting always? or the Configurator container should do this?

This is the content of common_site_config.json
image
So do I need to append db_host to db?

Your configure task failed, env vars were not picked properly.

all hosts should point to appropriate ip/hostname for service.

It’s mentioned here frappe_docker/docs/single-compose-setup.md at main · frappe/frappe_docker · GitHub

2 Likes

Hello @revant_one thank you for clarifying the process for me. As you suggested I think podman is not reading from .env. Checking on issues on github podman-compose repo, I found out that there is an active bug on the problem I am facing, Passing of environment variables does not match behaviour of docker-compose · Issue #491 · containers/podman-compose · GitHub

So far, I have used podman to build the custom image, but when creating docker-compose.yml, I used docker-compose instead. After this process I removed the docker-compose/docker package from my server since it gets precedence while executing podman-compose --project-name frappe -f ./docker-compose.yml up -d

The result

After running podman-compose --project-name frappe -f ./docker-compose.yml up -dstill some of the containers were not running. I stopped the started containers and restarted the whole container using podman-desktop. It worked this time, and the configurator container did configured db and redis related parameters in common_site_config.json , this is what it looks like now.
image

  • I have created sites and installed apps and it worked fine.
    To make sure things work fine even when the server restarts I stopped all containers and restarted again, and this time it shows an internal server error, checking on logs from backend container, I saw this
pymysql.err.OperationalError: (1045, "Access denied for user '_e8f7cc3a7b2ec51f'@'10.89.0.31' (using password: YES)")

I really couldn’t understand why this was not working after restarting. Here is an output from SQL query.

MariaDB [(none)]>  SELECT User, Host FROM mysql.user;
+-------------------+------------+
| User              | Host       |
+-------------------+------------+
| root              | %          |
| _d5fe0c22606823ee | 10.89.0.18 |
| _e8f7cc3a7b2ec51f | 10.89.0.18 |
| healthcheck       | 127.0.0.1  |
| healthcheck       | ::1        |
| healthcheck       | localhost  |
| mariadb.sys       | localhost  |
| root              | localhost  |
+-------------------+------------+
8 rows in set (0.002 sec)

I wish to contribute to the custom-apps.md guideline with podman setup, but I don’t make it work 100% yet.

Follow troubleshoot.

Hey, this issue is caused by the ip addresses linked to the user. For some reason, they change anytime you restart erpnext but stay the same in the database. here is a solution to this issue:

you need to rename the user to removed the linked host ip and ensure it can access the database from all host.

This problem is directly related to the podman compose not reading the .env file, but even if that works fine, there are multiple cases, which makes custom app deployment even harder.
For now, renaming the host did make it work as @Thevoice suggested. Thanks