Docker-Rails Can't Connect localhost mysql database

Question

I'm trying to have a docker container use my local mysql database. Using docker-compose up, I am able to get all the pieces running, however, the db exits with status code 1, and the rails application cannot connect to any database instance. I know that mysql is running on my system and that the service has been started. My docker-compose file:

version: '3'
services: 
    db:
        image: mysql
        restart: always
        ports:
            - "3306:3306"
        environment: 
            MYSQL_ROOT_PASSWORD: password
            MYSQL_DATABASE: db
            MYSQL_USER: root
            MYSQL_PASSWORD: password
        volumes: 
            - ./tmp/db:/var/lib/mysql
    web:
        build: .
        image: rails-devise:1.0.0
        command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
        volumes: 
            - ".:/baas-status"
        ports:
            - "3000:3000"
        depends_on: 
            - db

I know the containers are running because docker ps gives the following output:

CONTAINER ID        IMAGE                COMMAND                  CREATED             STATUS              PORTS                                         NAMES
1a49aba0cd68        rails-devise:1.0.0   "entrypoint.sh bash …"   23 minutes ago      Up 2 minutes        0.0.0.0:3000->3000/tcp                        status_web_1
38554baf5efc        mysql                "docker-entrypoint.s…"   23 minutes ago      Up 2 minutes        3306/tcp, 0.0.0.0:3002->3002/tcp, 33060/tcp   status_db_1

Even though I exposed port 3002 and want to use that as the port for the database, is it unable to connect because I'm on the wrong port? I see in the docker ps output that mysql is on port 3306.

My database.yml file:

base: &base
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>

default: &default
  <<: *base
  username: <%= ENV['DB_USERNAME'] %>
  password: <%= ENV['DB_PASSWORD'] %>
  host: <%= ENV['DB_HOST'] %>
  database: <%= ENV['DB_NAME'] %>

development:
  <<: *base
  username: root
  password: password
  host: db
  database: db

I have also tried editing the host for development to be host: db. That didn't change anything either. I tried running docker-compose run web rake db:create as well, and I get the following error:

This has been fixed with the current changes to the docker-compose file Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

At this point, I'm at a loss on where to proceed. Any information or help would be greatly appreciated. Thank you.

Edit:

I have changed the ports for the db and it seems to have fixed the connection issues. Now I'm running into an error that gives

web_1   | Mysql2::Error::ConnectionError (Plugin caching_sha2_password could not be loaded: /usr/lib/x86_64-linux-gnu/mariadb19/plugin/caching_sha2_password.so: cannot open shared object file: No such file or directory):

I'm going to do some research to figure out the meaning behind this error, any tips to move in the right direction would be greatly appreciated though!

Edit2:

I ended up connecting the db by reverting mysql back to 5.7.18 to avoid the sha authentication. The docker-compose file and settings that worked for me are as follows:

docker-compose.yml

version: '2'
services: 
    db:
        image: mysql:5.7.18
        restart: always
        ports:
            - 3306:3306
        environment: 
            - MYSQL_ROOT_PASSWORD=root
        volumes: 
            - db-data:/var/lib/mysql
    web:
        build: .
        image: rails-devise:1.0.0
        command: bash -c "rm -f tmp/pids/server.pid && bundle exec rails s -p 3000 -b '0.0.0.0'"
        volumes: 
            - ".:/baas-status"
        ports:
            - 3000:3000
        links:
            - db
        depends_on: 
            - db
volumes: 
    db-data:
        driver: local

The database.yml file remained the same with the exception of changing the password to root. This is the solution that worked for me.

Resource I ended up using!

score 2 · Accepted Answer · answered Jul 08 '20 at 01:43

Sounds like you figured out the connection issue. You'll want to connect on 3306, not 3002 based on the docker-compose file you posted.

I assume you also made sure to define these environment variables in your rails docker container as well. Is that correct?

  username: <%= ENV['DB_USERNAME'] %>
  password: <%= ENV['DB_PASSWORD'] %>
  host: <%= ENV['DB_HOST'] %>
  database: <%= ENV['DB_NAME'] %>

Since you do not define a tag of the mysql image to use in your docker-compose file, it will pull the latest image which is mysql ~8.x. MySQL 8 uses the caching_sha2_password authentication plugin by default (unless you specify otherwise). The Rails mysql2 driver version you're using likely does not support this authentication mechanism yet.

So, if you would like to revert to an older authentication plugin (like the one from mysql 5.7), you can use a command line flag in your mysql container like so:

services:
  ...
  db:
    image: mysql:8.0
    ...
    command: mysqld --default-authentication-plugin=mysql_native_password

More information can also be found on this SO answer.

Thank you! This information was super helpful. I ended up reverting back to 5.7 and reworking the docker-compose file a little bit. The mysqld command didn't work for mysql:8.0, sadly. Appreciate it! — Ciante, Jul 09 '20 at 09:52

Docker-Rails Can't Connect localhost mysql database

1 Answers1