Maria DB docker Access denied for user 'root'@'localhost'

I'm using a MariaDB docker image and keep getting the Warning:

[Warning] Access denied for user 'root'@'localhost' (using password: NO)

This is my docker file used to create the image:

FROM mariadb:10.6.4
ENV MYSQL_ROOT_PASSWORD pw
ENV MARIADB_DATABASE db1
ENV MARIADB_USER user1
ENV MARIADB_PASSWORD user1pw

I start the container in docker-compose :

mariadb:
    restart: always
    image: mariadb_image
    container_name: mariadb_container
    build: topcat_mariadb/.
    ports:
      - 2306:3306

and I access the container from another container using:

mysql+pymysql://user1:user1pw@mariadb_container:3306/db1

Most of the solutions google found was running some kind of SQL statement, but as I'm running a docker container, I need an ephemeral solution. Anyone know how to fix this in docker?

-----EDIT-----

I had forgotten to add the health check section to the dockercompose file:

mariadb:
    restart: always
    image: mariadb_image
    container_name: mariadb_container
    build: topcat_mariadb/.
    ports:
      - 2306:3306  
    healthcheck:
      test: ["CMD-SHELL", 'mysqladmin ping']
      interval: 10s
      timeout: 2s
      retries: 10

Solution

Update

Based on the update to your question, you're trying to run the mysqladmin ping command inside the container. mysqladmin is attempting to connect as the root user, but authenticating to your database server requires a password.

You can provide a password to mysqladmin by:

Using the -p command line option
Using the MYSQL_PWD environment variable
Creating a credentials file

If we move the root password out of your image, and instead set it at runtime, we can write your docker-compose.yml file like this:

version: "3"

services:
  mariadb:
    restart: always
    image: mariadb_image
    container_name: mariadb_container
    build: topcat_mariadb/.
    environment:
      - "MYSQL_ROOT_PASSWORD=$MYSQL_ROOT_PASSWORD"
      - "MYSQL_PWD=$MYSQL_ROOT_PASSWORD"
    healthcheck:
      test: ["CMD-SHELL", 'mysqladmin ping']
      interval: 10s
      timeout: 2s
      retries: 10

And then in our .env file we can set:

MYSQL_ROOT_PASSWORD=pw1

Now after the container starts up we see that the container is healthy:

$ docker ps
CONTAINER ID   IMAGE            COMMAND                  CREATED          STATUS                    PORTS      NAMES
c1c9c9f787e6   mariadb_image    "docker-entrypoint.s…"   28 seconds ago   Up 27 seconds (healthy)   3306/tcp   mariadb_container

As a side note, it's not clear from this example why you're bothering to build a custom image: it's better to set the environment variables at runtime, rather than creating an image with "baked-in" credentials.

Previous answer

I can't reproduce your problem when using the mysql client or Python code. Given the following docker-compose.yml:

version: "3"

services:
  mariadb:
    restart: always
    image: mariadb_image
    container_name: mariadb_container
    build: topcat_mariadb/.

  shell:
    image: mariadb:10.6.4
    command: sleep inf

(The directory topcat_mariadb contains the Dockerfile from your question.)

If I exec into the shell container:

docker-compose exec shell bash

And run mysql like this:

mysql -h mariadb_container -u user1 -p db1

It works just fine:

root@4fad8e8435df:/# mysql -h mariadb_container -u user1 -p db1
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 6
Server version: 10.6.4-MariaDB-1:10.6.4+maria~focal 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 [db1]>

It looks like you may be using sqlalchemy. If I add a Python container to the mix:

version: "3"

services:
  mariadb:
    restart: always
    image: mariadb_image
    container_name: mariadb_container
    build: topcat_mariadb/.

  shell:
    image: mariadb:10.6.4
    command: sleep inf

  python:
    image: python:3.9
    command: sleep inf

And then run the following Python code in the python container:

>>> import sqlalchemy
>>> e = sqlalchemy.engine.create_engine('mysql+pymysql://user1:user1pw@mariadb_container:3306/db1')
>>> res = e.execute('select 1')
>>> res.fetchall()
[(1,)]

It also seems to work without a problem.