laravel docker: SQLSTATE[HY000] [1130] Host '172.22.0.4' is not allowed to connect to this-CodePudding

I want to be able, locally, to connect from my app container to my db container using root, without any password, from any ip.

in my docker-compose.yml I defined a db serice as

    db:
        image: mariadb
        container_name: docker-mariadb
        restart: unless-stopped
        tty: true
        environment:
            MYSQL_DATABASE: docker-db
            MYSQL_ROOT_HOST: "%"
            MYSQL_ALLOW_EMPTY_PASSWORD: yes
        volumes:
            - ./db/:/var/lib/mysql/
        networks:
             - docker-network

laravel .env file

DB_CONNECTION=mysql
DB_HOST=db
DB_PORT=3306
DB_DATABASE=docker-db
DB_USERNAME=root
DB_PASSWORD=

I am getting, at first Laravel query, the error

SQLSTATE[HY000] [1130] Host '172.22.0.4' is not allowed to connect to this MariaDB server

So base networking is working, but mysql is refusing connection to my root user

Edit 1 - Users on db container

I accessed mariadb container, and obtained user list

MariaDB [(none)]> SELECT user, host FROM mysql.user;
 ------------- -------------- 
| User        | Host         |
 ------------- -------------- 
| root        | 127.0.0.1    |
| root        | ::1          |
| root        | a1a831ff7300 |
| mariadb.sys | localhost    |
| root        | localhost    |
 ------------- -------------- 
5 rows in set (0.002 sec)

Question

What can i do to fix it?

Notes: I don't want to manually change host or add another root@% user manually. Also I need a way to do these things using docker-compose to be able to distribute docker-compose and all related files to coworkers to spin up a full working environment without having to do things manually

CodePudding user response：

I solved adding a db init sql

In db container definition I added

    volumes:
        - ./db/init:/docker-entrypoint-initdb.d

I created the init folder under db folder under same folder where is located docker-compose.yml

- docker-composer.yml
- db
   - init 
      - 01-create-root-any-host.sql

Added this in the 01-create-root-any-host.sql

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

Now my app container can login into mysql server, which in its mariadb separated container, using root user without any password

CodePudding user response：

Just checked on my virtual machine. No need for db init script

After adding to docker-compose.yaml:

MYSQL_ROOT_HOST: "%"

you should do a docker-compose down and delete the volume ./db/:

rm -rf ./db/

and do a docker-compose up -d again - my results:

 ------------- ----------- 
| User        | Host      |
 ------------- ----------- 
| mybb        | %         |
| root        | %         |
| mariadb.sys | localhost |
| root        | localhost |
 ------------- -----------