Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

[Bug] "Host 'XXX.XXX.XXX.XXX' is not allowed to connect to this MariaDB server" on mariadb:11 / mariadb:latest #1648

Open
PapaBearDoes opened this issue Feb 22, 2025 · 7 comments
Open

[Bug] "Host 'XXX.XXX.XXX.XXX' is not allowed to connect to this MariaDB server" on mariadb:11 / mariadb:latest #1648

PapaBearDoes opened this issue Feb 22, 2025 · 7 comments

Comments

@PapaBearDoes
Copy link

RomM version
Latest Pull on Docker

Describe the bug
No matter what I try, the DB will not allow connections from the main app.

To Reproduce
Run Docker Install with the following:

### Common Variables ###
x-common: &common
  networks:
    - xyz
  restart: unless-stopped

### Common Environmental Variables ###
x-env: &env

### Healthchecks ###
x-health: &health
  interval: 5s
  timeout: 30s
  retries: 3
  start_period: 60s
  start_interval: 5s

services:
  romm-db:
    image: mariadb
    container_name: romm-db
    <<: *common
    environment:
      <<: *env
      MARIADB_DATABASE: ${DB}
      MARIADB_USER: ${DB_USER}
      MARIADB_PASSWORD: ${DB_PASSWORD}
      MARIADB_ROOT_PASSWORD: ${DB_ROOT_PASSWORD}
    volumes:
      - ${DOCKER_DIR}/romm/db:/var/lib/mysql
    healthcheck:
      <<: *health
      test: healthcheck.sh --connect --innodb_initialized

  romm:
    image: rommapp/romm:latest
    container_name: romm
    <<: *common
    environment:
      <<: *env
      DB_HOST: romm-db
      DB_NAME: ${DB}                       # Should match MARIADB_DATABASE in mariadb
      DB_USER: ${DB_USER}                           # Should match MARIADB_USER in mariadb
      DB_PASSWD: ${DB_PASSWORD}                     # Should match MARIADB_PASSWORD in mariadb
      ROMM_AUTH_SECRET_KEY: ${ROMM_AUTH_SECRET_KEY}   # Generate a key with `openssl rand -hex 32`
      IGDB_CLIENT_ID: ${IGDB_CLIENT_ID}               # Generate an ID and SECRET in IGDB
      IGDB_CLIENT_SECRET: ${IGDB_CLIENT_SECRET}       # https://api-docs.igdb.com/#account-creation
      MOBYGAMES_API_KEY: ${MOBYGAMES_API_KEY}         # https://www.mobygames.com/info/api/
      STEAMGRIDDB_API_KEY: ${STEAMGRIDDB_API_KEY}     # https://github.com/rommapp/romm/wiki/Generate-API-Keys#steamgriddb
    volumes:
      - ${DOCKER_DIR}/romm/resources:/romm/resources  # Resources fetched from IGDB (covers, screenshots, etc.)
      - ${DOCKER_DIR}/romm/redis:/redis-data          # Cached data for background tasks
      - ${DOCKER_DIR}/romm/assets:/romm/assets        # Uploaded saves, states, etc.
      - ${DOCKER_DIR}/romm/config:/romm/config        # Path where config.yml is stored
      - ${ROMM_LIBRARY}:/romm/library                 # Your game library. Check https://github.com/rommapp/romm?tab=readme-ov-file#folder-structure for more details.
    ports:
      - 3898:8080
    depends_on:
      romm-db:
        condition: service_healthy
    healthcheck:
      <<: *health
      test: curl -q http://localhost:8080 || exit 1

networks:
  xyz:
    driver: overlay
    external: true

Expected behavior
Should simply install

Screenshots
N/A

Desktop (please complete the following information):

  • OS: Debian 12

Additional context
Error being seen:

INFO:     [init][2025-02-22 07:55:11] Starting up, please wait...
INFO:     [init][2025-02-22 07:55:11] starting valkey-server
14:C 22 Feb 2025 07:55:11.960 # WARNING Memory overcommit must be enabled! Without it, a background save or replication may fail under low memory condition. To fix this issue add 'vm.overcommit_memory = 1' to /etc/sysctl.conf and then reboot or run the command 'sysctl vm.overcommit_memory=1' for this to take effect.
14:C 22 Feb 2025 07:55:11.960 * oO0OoO0OoO0Oo Valkey is starting oO0OoO0OoO0Oo
14:C 22 Feb 2025 07:55:11.960 * Valkey version=7.2.8, bits=64, commit=00000000, modified=0, pid=14, just started
14:C 22 Feb 2025 07:55:11.960 * Configuration loaded
14:M 22 Feb 2025 07:55:11.960 * monotonic clock: POSIX clock_gettime
14:M 22 Feb 2025 07:55:11.960 * Running mode=standalone, port=6379.
14:M 22 Feb 2025 07:55:11.960 * Server initialized
14:M 22 Feb 2025 07:55:11.961 * Ready to accept connections tcp
INFO:     [RomM][redis_handler][2025-02-22 07:55:14] Connecting to sync redis in /src/.venv/bin/alembic...
INFO:     [RomM][redis_handler][2025-02-22 07:55:14] Redis sync connection established in /src/.venv/bin/alembic!
INFO:     [RomM][redis_handler][2025-02-22 07:55:14] Connecting to async redis in /src/.venv/bin/alembic...
INFO:     [RomM][redis_handler][2025-02-22 07:55:14] Redis async connection established in /src/.venv/bin/alembic!
Traceback (most recent call last):
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3302, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
    with util.safe_reraise():
         ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
         ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 621, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
mariadb.OperationalError: Host '10.0.1.169' is not allowed to connect to this MariaDB server

The above exception was the direct cause of the following exception:

Traceback (most recent call last):
  File "/src/.venv/bin/alembic", line 8, in <module>
    sys.exit(main())
             ^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/alembic/config.py", line 641, in main
    CommandLine(prog=prog).main(argv=argv)
  File "/src/.venv/lib/python3.12/site-packages/alembic/config.py", line 631, in main
    self.run_cmd(cfg, options)
  File "/src/.venv/lib/python3.12/site-packages/alembic/config.py", line 608, in run_cmd
    fn(
  File "/src/.venv/lib/python3.12/site-packages/alembic/command.py", line 403, in upgrade
    script.run_env()
  File "/src/.venv/lib/python3.12/site-packages/alembic/script/base.py", line 583, in run_env
    util.load_python_file(self.dir, "env.py")
  File "/src/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 95, in load_python_file
    module = load_module_py(module_id, path)
             ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/alembic/util/pyfiles.py", line 113, in load_module_py
    spec.loader.exec_module(module)  # type: ignore
    ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "<frozen importlib._bootstrap_external>", line 999, in exec_module
  File "<frozen importlib._bootstrap>", line 488, in _call_with_frames_removed
  File "/backend/alembic/env.py", line 97, in <module>
    run_migrations_online()
  File "/backend/alembic/env.py", line 81, in run_migrations_online
    with engine.connect() as connection:
         ^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3278, in connect
    return self._connection_cls(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 148, in __init__
    Connection._handle_dbapi_exception_noconnection(
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 2442, in _handle_dbapi_exception_noconnection
    raise sqlalchemy_exception.with_traceback(exc_info[2]) from e
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 146, in __init__
    self._dbapi_connection = engine.raw_connection()
                             ^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/base.py", line 3302, in raw_connection
    return self.pool.connect()
           ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 449, in connect
    return _ConnectionFairy._checkout(self)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 1263, in _checkout
    fairy = _ConnectionRecord.checkout(pool)
            ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 712, in checkout
    rec = pool._do_get()
          ^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 179, in _do_get
    with util.safe_reraise():
         ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/impl.py", line 177, in _do_get
    return self._create_connection()
           ^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 390, in _create_connection
    return _ConnectionRecord(self)
           ^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 674, in __init__
    self.__connect()
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 900, in __connect
    with util.safe_reraise():
         ^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/util/langhelpers.py", line 146, in __exit__
    raise exc_value.with_traceback(exc_tb)
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/pool/base.py", line 896, in __connect
    self.dbapi_connection = connection = pool._invoke_creator(self)
                                         ^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/create.py", line 643, in connect
    return dialect.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/sqlalchemy/engine/default.py", line 621, in connect
    return self.loaded_dbapi.connect(*cargs, **cparams)
           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/mariadb/__init__.py", line 146, in connect
    connection = connectionclass(*args, **kwargs)
                 ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
  File "/src/.venv/lib/python3.12/site-packages/mariadb/connections.py", line 85, in __init__
    super().__init__(*args, **kwargs)
sqlalchemy.exc.OperationalError: (mariadb.OperationalError) Host '10.0.1.169' is not allowed to connect to this MariaDB server
(Background on this error at: https://sqlalche.me/e/20/e3q8)
ERROR:    [init][2025-02-22 07:55:14] Something went horribly wrong with our database
INFO:     [init][2025-02-22 07:55:14] stopping valkey-server
14:signal-handler (1740239714) Received SIGTERM scheduling shutdown...
14:M 22 Feb 2025 07:55:14.968 * User requested shutdown...
14:M 22 Feb 2025 07:55:14.968 * Saving the final RDB snapshot before exiting.
14:M 22 Feb 2025 07:55:15.610 * DB saved on disk
14:M 22 Feb 2025 07:55:15.610 # Valkey is now ready to exit, bye bye...

The key issue being (I think):

mariadb.OperationalError: Host '10.0.1.169' is not allowed to connect to this MariaDB server

The IP changes as it is the container for "romm" that is trying to connect, and it's IP will change each time we docker compose down/up it.

I have changed users via the USER: XXX:XXX docker compose tag, that did not help. Permissions on file system read like they are correct for my setup. Specifically, MARIADB can read/write to the volume.

I'm usually pretty good about troubleshooting these on my own, but I'm at a loss at this point.

I did check to see if there were issues like mine previous, and there are 2 that are similar that were closed, but the fixes for them did not fix my issue here.

Thoughts?
@PapaBearDoes
Copy link
Author

The database itself initializes and looks like it's working as normal:

2025-02-22 07:47:04-08:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.7.2+maria~ubu2404 started.
2025-02-22 07:47:05-08:00 [Note] [Entrypoint]: Initializing database files
2025-02-22  7:47:06 0 [Warning] mariadbd: io_uring_queue_init() failed with errno 95
2025-02-22  7:47:06 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2025-02-22 07:48:27-08:00 [Note] [Entrypoint]: Entrypoint script for MariaDB Server 1:11.7.2+maria~ubu2404 started.
2025-02-22 07:48:28-08:00 [Note] [Entrypoint]: MariaDB upgrade information missing, assuming required
2025-02-22 07:48:28-08:00 [Note] [Entrypoint]: MariaDB upgrade (mariadb-upgrade or creating healthcheck users) required, but skipped due to $MARIADB_AUTO_UPGRADE setting
2025-02-22  7:48:28 0 [Note] Starting MariaDB 11.7.2-MariaDB-ubu2404 source revision 80067a69feaeb5df30abb1bfaf7d4e713ccbf027 server_uid rPCRIervqmJ/B29PV/AkQ5upnEc= as process 1
2025-02-22  7:48:28 0 [Note] mariadbd: Aria engine: starting recovery
recovered pages: 0% 10% 21% 33% 44% 54% 67% 80% 90% 100% (0.2 seconds); tables to flush: 5 4 3 2 1 0 (2.8 seconds);
2025-02-22  7:48:32 0 [Note] mariadbd: Aria engine: recovery done
2025-02-22  7:48:32 0 [Note] InnoDB: Compressed tables use zlib 1.3
2025-02-22  7:48:32 0 [Note] InnoDB: Number of transaction pools: 1
2025-02-22  7:48:32 0 [Note] InnoDB: Using crc32 + pclmulqdq instructions
2025-02-22  7:48:32 0 [Note] mariadbd: O_TMPFILE is not supported on /tmp (disabling future attempts)
2025-02-22  7:48:32 0 [Warning] mariadbd: io_uring_queue_init() failed with errno 95
2025-02-22  7:48:32 0 [Warning] InnoDB: liburing disabled: falling back to innodb_use_native_aio=OFF
2025-02-22  7:48:32 0 [Note] InnoDB: Initializing buffer pool, total size = 128.000MiB, chunk size = 2.000MiB
2025-02-22  7:48:32 0 [Note] InnoDB: Completed initialization of buffer pool
2025-02-22  7:48:32 0 [Note] InnoDB: Buffered log writes (block size=512 bytes)
2025-02-22  7:48:32 0 [Note] InnoDB: Starting crash recovery from checkpoint LSN=18006
2025-02-22  7:48:32 0 [Note] InnoDB: End of log at LSN=47463
2025-02-22  7:48:32 0 [Note] InnoDB: To recover: 163 pages
2025-02-22  7:48:32 0 [Note] InnoDB: Opened 3 undo tablespaces
2025-02-22  7:48:32 0 [Note] InnoDB: 128 rollback segments in 3 undo tablespaces are active.
2025-02-22  7:48:32 0 [Note] InnoDB: Removed temporary tablespace data file: "./ibtmp1"
2025-02-22  7:48:32 0 [Note] InnoDB: Setting file './ibtmp1' size to 12.000MiB. Physically writing the file full; Please wait ...
2025-02-22  7:48:32 0 [Note] InnoDB: File './ibtmp1' size is now 12.000MiB.
2025-02-22  7:48:32 0 [Note] InnoDB: log sequence number 47463; transaction id 14
2025-02-22  7:48:32 0 [Note] InnoDB: Loading buffer pool(s) from /var/lib/mysql/ib_buffer_pool
2025-02-22  7:48:32 0 [Note] InnoDB: Cannot open '/var/lib/mysql/ib_buffer_pool' for reading: No such file or directory
2025-02-22  7:48:32 0 [Note] Plugin 'FEEDBACK' is disabled.
2025-02-22  7:48:32 0 [Note] Plugin 'wsrep-provider' is disabled.
2025-02-22  7:48:32 0 [Note] Recovering after a crash using tc.log
2025-02-22  7:48:32 0 [Note] Starting table crash recovery...
2025-02-22  7:48:32 0 [Note] Crash table recovery finished.
2025-02-22  7:48:33 0 [Note] DDL_LOG: Crash recovery executed 1 entries
2025-02-22  7:48:34 0 [Note] Server socket created on IP: '0.0.0.0'.
2025-02-22  7:48:34 0 [Note] Server socket created on IP: '::'.
2025-02-22  7:48:37 0 [Note] mariadbd: Event Scheduler: Loaded 0 events
2025-02-22  7:48:37 0 [Note] mariadbd: ready for connections.
Version: '11.7.2-MariaDB-ubu2404'  socket: '/run/mysqld/mysqld.sock'  port: 3306  mariadb.org binary distribution

@adamantike
Copy link
Contributor

I think this is related to using the Overlay network driver. Do you have other database containers running correctly in that same network? Do you need to use an overlay-based network?

Related StackOverflow question: https://stackoverflow.com/questions/69822573/how-can-i-connect-from-project-to-mysql-container-in-docker-swarm

@leugiMG
Copy link

leugiMG commented Feb 23, 2025 •
edited
Loading

I am currently having the same issue.
I have other containers running on my server (all of my *arrs).

I'm using the docker compose example that is provided in the wiki. I noticed if I log into the mariadb container I'm unable to login to the db with the credentials that are specified in my environment.

Image

@PapaBearDoes
Copy link
Author

I think this is related to using the Overlay network driver. Do you have other database containers running correctly in that same network? Do you need to use an overlay-based network?

Related StackOverflow question: https://stackoverflow.com/questions/69822573/how-can-i-connect-from-project-to-mysql-container-in-docker-swarm

None of the containers share a container name, and as long as the DB is on the same "server" I've not had a problem. These aren't being deployed on the swarm, but as server-local containers that happen to use the swarm overlay network for DNS resolution.

It's how I keep my *arrs behind a VPN and the non-*arrs on the non-VPN'd servers while still being able to use a single reverse proxy instance to run it all.

I do have other mariadb instances running with different container names without issue.

It seems more that as the next comment mentioned, being unable to login with the specified credentials. I wonder if I can resolve to another working mariadb instance and see if that works or not. Will report back shortly.

@PapaBearDoes
Copy link
Author

PapaBearDoes commented Feb 23, 2025 •
edited
Loading

I can confirm at this point being unable to login via CLI in the mariadb container:

ERROR 1045 (28000): Access denied for user 'romm'@'localhost' (using password: YES)

When I downgrade to mariadb:10 almost everything works. This issue is specifically with maridb:11 (mariadb:11, mariadb:latest, mariadb, etc).

@PapaBearDoes
Copy link
Author

OK, so downgrading to MariaDB:10 worked with adjustments. I needed to adjust my healthcheck settings to

healthcheck:
  timeout: 5m

and add the ENV variable:

      MARIADB_AUTO_UPGRADE: true

to my mariadb container to enable the healthcheck script, and it's now installing ROMM normally.

I'm not sure what is causing the issue with not being able to login with MariaDB:11 (latest, etc), but that's the cause I believe.

@PapaBearDoes PapaBearDoes changed the title [Bug] Host 'XXX.XXX.XXX.XXX' is not allowed to connect to this MariaDB server [Bug] "Host 'XXX.XXX.XXX.XXX' is not allowed to connect to this MariaDB server" on mariadb:11 / mariadb:latest Feb 23, 2025
@gabriel-andreescu
Copy link

Using the root user worked for me with mariadb 11, any custom user I try to assign specifically for the romm db binded to the romm host or '%' doesn't work.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
None yet
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
4 participants
@gabriel-andreescu @adamantike @PapaBearDoes @leugiMG