mysql host not resolving with docker container for Nodejs and express

Question

I'm trying to use NodeJS+Express, MySQL, phpmyadmin altogether. phpmyadmin itself resolving the address by port 8183 in my situation. But whenever I'm going to connect it through with mysql js package of NodeJS. it is not working and throwing error

Error: connect ECONNREFUSED 127.0.0.1:3306

Note: I tried with few variations as host anyone didn't work for me!

example: mysql, my_machine_ip, localhost, 0.0.0.0

nothing worked for me.

Here's my yml file.

version : '3'

services:
  phpmyadmin:
    image: phpmyadmin/phpmyadmin
    container_name: node_pa
    links:
      - mysql
    depends_on:
      - mysql
    environment:
      PMA_HOST: mysql
      PMA_PORT: 3306
      PMA_ARBITRARY: 1
    restart: always
    ports:
      - 8183:80
  node_backend:
    container_name: node_with_pa_msql
    build: ./backend_app
    # volumes: 
    #   - ./backend_app:/usr/src/app
    links:
      - mysql
    depends_on:
      - mysql
    restart: on-failure
    ports:
      - 3004:4006
  mysql:
    image: mysql:latest
    container_name: node_mysql
    # volumes:
    #   - backend_app/db_sample:/docker-entrypoint-initdb.d
    environment:
      MYSQL_USER: user
      MYSQL_PASSWORD: user
      MYSQL_ROOT_PASSWORD: root
      MYSQL_DATABASE: default_schema

And mysqlJS connection details

// environment variables
const PORT = process.env.PORT || 4006;
const HOST = process.env.HOST || 'localhost';

// // mysql credentials
const connection = mysql.createConnection({
    host: HOST,
    user: process.env.MYSQL_USER || 'root',
    password: process.env.MYSQL_PASSWORD || 'root',
    connectTimeout: 20000
});

So, phpmyadmin is working and i can access databases/create/delete/edit by that so this assure that mysql itself also working, But right now I'm unable to connect it with mysqlJS of my nodejs app.

here's the sample project with the problem if you would like to try on your machine project GitHub link with YAML file

Note: Check answer added a complete solution of this issue.

How do you start your docker? Or can you provide a snapshot of your docker desktop if possible — Seppe Mariën, Mar 05 '20 at 12:20
@SeppeMariën Here's the full code that i'm using to test. you can check and try https://github.com/MahbbRah/docker_mysql_node_phpmyadmin.git — Code Cooker, Mar 05 '20 at 12:24
yes it does, because if you don't forward your port then your nodejs app wont be able to connect to the server! — Seppe Mariën, Mar 05 '20 at 12:45

score 0 · Answer 1 · answered Mar 05 '20 at 12:32

0

I'm assuming the phpMyAdmin instance is working since you are actually passing the env var PMA_HOST and PMA_PORT correctly to the container.

You will need to do the same for the JS application, so your docker-compose config will look like the following:

  node_backend:
    container_name: node_with_pa_msql
    build: ./backend_app
    # volumes: 
    #   - ./backend_app:/usr/src/app
    environment:
      MYSQL_HOST: mysql
      MYSQL_PORT: 3306
    links:
      - mysql
    depends_on:
      - mysql
    restart: on-failure
    ports:
      - 3004:4006

Then in your JS code you need to get the connection information from your env variables:

// environment variables
const PORT = process.env.MYSQL_PORT || 4006;
const HOST = process.env.MYSQL_HOST || 'localhost';

// // mysql credentials
const connection = mysql.createConnection({
    host: HOST,
    port: PORT,
    user: process.env.MYSQL_USER || 'root',
    password: process.env.MYSQL_PASSWORD || 'root',
    connectTimeout: 20000
});

This works because when you link the mysql container, a hosts entry is added to your js container which makes the hostname mysql point to the (docker internal) ip address of the mysql container.

answered Mar 05 '20 at 12:32

JensV

3,997
2
19
43

What you said is fine. but this isn't the issue. I can pass it directly to my a environment variable. I believe it's something else. you can try this source. https://github.com/MahbbRah/docker_mysql_node_phpmyadmin.git – Code Cooker Mar 05 '20 at 12:40
@CodeCooker I mean it's wrongly trying to connect to `127.0.0.1:3306` where it should instead be the mysql container. Is phpMyAdmin working or not? – JensV Mar 05 '20 at 12:41
read my question properly please. I tried all the variation: example: mysql, my_machine_ip, localhost, 0.0.0.0 it was same for all. – Code Cooker Mar 05 '20 at 12:42
I think we need something like this, https://docs.docker.com/compose/startup-order/ but i'm not sure how to implement with this project. – Code Cooker Mar 05 '20 at 12:56
Alright, after setting the env vars (like in my example) a connection attempt was made to the sql server resulting in a `ER_HOST_NOT_PRIVILEGED` error. Meaning that a user still has to be created in the DB like `user@%` to allow connection from any IP address. (You can use the mysql-entrypoint file to achieve this or set it up manually once). Do you need further assistance than this? – JensV Mar 05 '20 at 13:02
Yes, It would be really nice if you help me on it more to get this work out. – Code Cooker Mar 05 '20 at 13:03
@CodeCooker this should help you out: https://stackoverflow.com/questions/47270505/er-host-not-privileged-docker-container-fails-to-connect-to-mariadb/47280522 – JensV Mar 05 '20 at 13:09
Check this screenshot. when I add wait for it, the error message has changed like below https://i.imgur.com/8LzBiph.png – Code Cooker Mar 05 '20 at 13:13
@CodeCooker try updating your mysql container or js library, that error is completely unrelated to this issue. If you need assistance with that open a new issue – JensV Mar 05 '20 at 13:19
Thanks for your assistance. solved this issue by looking at it. https://stackoverflow.com/questions/50093144/mysql-8-0-client-does-not-support-authentication-protocol-requested-by-server/53382070 – Code Cooker Mar 05 '20 at 13:33

score 0 · Accepted Answer · answered Mar 05 '20 at 13:48

Finally after a big mess, solved this issue!

With stated above YAML file. everything is almost OK, But the main problem is order of image.

That means I have total 3 image from them node is dependent to mysql. So first mysql must has to start so then you can get the connection of mysql and use it with your application (nodeJS). This is the reason why any host were not resolving with my implementation.

I followd this article: https://docs.docker.com/compose/startup-order/

and for implementation example this: https://dev.to/hugodias/wait-for-mongodb-to-start-on-docker-3h8b

You are done! now use your desired driver

Now

this is node (mysqlJS) package specific. the package by itself doesn't support latest mysql and if you use latest tagged mysql mysql:latest then you may face this issue.

Error: ER_NOT_SUPPORTED_AUTH_MODE: Client does not support authentication protocol requested by server; consider upgrading MySQL client docker mysql node

quick solution downgrade it to lower until it works. I'm not sure about minimum version but mysql:5.6 works!

If you want a quick starter with everything phpmyadmin,mysql,nodejs app then you can use this repo: Here

mysql host not resolving with docker container for Nodejs and express

2 Answers2