Docker + Rails 6 + MySQL : Mysql2::Error::ConnectionError: Access denied for 'user'@'%' to database

huangapple go评论71阅读模式
英文:

Docker + Rails 6 + MySQL : Mysql2::Error::ConnectionError: Access denied for 'user'@'%' to database

问题

以下是您要翻译的内容:

Context : Rails 6 app hosted in Docker, using docker-compose

When the Rails container starts, I get :
Mysql2::Error::ConnectionError: Access denied for user 'user'@'%' to database 'db_production'
Even after MySQL is fully initalized

My docker-compose.yml :

services:

  app_production:
    image: app_production
    build:
      context: .
      dockerfile: ./dockerfiles/production.dockerfile
    container_name: app_production
    volumes:
      - ./public:/app_production/public
      - mysql_socket:/var/run/mysqld
    command: bash -c "sleep 5 && bundle exec rails db:create db:migrate && bundle exec puma -C config/puma.rb"
    restart: always
    environment:
      RAILS_ENV: production
      SECRET_KEY_BASE: ${PRODUCTION_SECRET_KEY_BASE}
      APP_DATABASE_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
      PORT: 5000
    ports:
      - "5000:5000"
    depends_on:
      - database_production
    links:
      - database_production


  database_production:
    image: mysql:8.0.20
    command: mysqld --default-authentication-plugin=mysql_native_password
    restart: always
    container_name: database_production
    environment:
      MYSQL_USER: user
      MYSQL_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
      MYSQL_RANDOM_ROOT_PASSWORD: "yes"
    volumes:
      - dbdata_production:/var/lib/mysql
      - mysql_socket:/var/run/mysqld
    ports:
      - "3307:3306"


volumes:
 dbdata_production:
 mysql_socket:

My Dockerfile :

FROM ruby:2.6.1

RUN echo "deb http://archive.debian.org/debian stretch main" > /etc/apt/sources.list
RUN curl https://deb.nodesource.com/setup_12.x | bash
RUN curl https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install -y nodejs yarn
RUN apt-get update && apt-get install -y gconf-service libasound2 libatk1.0-0 libc6 libcairo2 libcups2 libdbus-1-3 libexpat1 libfontconfig1 libgcc1 libgconf-2-4 libgdk-pixbuf2.0-0 libglib2.0-0 libgtk-3-0 libnspr4 libpango-1.0-0 libpangocairo-1.0-0 libstdc++6 libx11-6 libx11-xcb1 libxcb1 libxcomposite1 libxcursor1 libxdamage1 libxext6 libxfixes3 libxi6 libxrandr2 libxrender1 libxss1 libxtst6 ca-certificates fonts-liberation libappindicator1 libnss3 lsb-release xdg-utils wget
RUN apt-get install -y default-mysql-client

WORKDIR /app_production

RUN export RAILS_ENV=production
RUN gem install bundler:2.2.31

COPY Gemfile Gemfile.lock ./
COPY package.json ./
COPY vendor/gems/ vendor/gems/

RUN bundle install

COPY . ./

RUN yarn install --check-files

EXPOSE 5000

CMD ["RAILS_ENV=production", "bundle", "exec", "puma", "-C", "config/puma.rb"]

database.yml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:

production:
  <<: *default
  host: database_production
  port: 3306
  database: db_production
  username: user
  password: "some_password"
  socket: "/var/run/mysqld/mysqld.sock"

Notes :

  • This configuration is working fine on another server on Ubuntu 22.04
  • I'm now trying to make it works on this server on Ubuntu 20.04
  • I had to add RUN apt-get install -y default-mysql-client to the Dockerfile in order to have MySQL working in the Rails container, otherwise it was not found, but on my other server I did not have to do this
  • I also had to create a shared volume for mysql_socket:/var/run/mysqld otherwise socket was not found
  • I am able to log to MySQL with CLI on the Rails container, using the same credentials
  • If I put a phpMyAdmin service in docker-compose, I am also able to login to MySQL using the same credentials
  • If I try to connect to MySQL via container's rails console, it works
  • If I remove bundle exec rails db:create db:migrate from rails container command, rails is launched

So I don't understand much what exactly is happening, since I can successfully connect to MySQL through container's CLI and PhpMyAdmin.
How come Rails can't connect to it ? There are no detailed logs.

It fails at the step rake db:create and when trying to run it manually via rails console I got the same error. Very stange.

Any clue ?

英文:

Context : Rails 6 app hosted in Docker, using docker-compose

When the Rails container starts, I get :
Mysql2::Error::ConnectionError: Access denied for user 'user'@'%' to database 'db_production'
Even after MySQL is fully initalized

My docker-compose.yml :

services:

  app_production:
    image: app_production
    build:
      context: .
      dockerfile: ./dockerfiles/production.dockerfile
    container_name: app_production
    volumes:
      - ./public:/app_production/public
      - mysql_socket:/var/run/mysqld
    command: bash -c "sleep 5 && bundle exec rails db:create db:migrate && bundle exec puma -C config/puma.rb"
    restart: always
    environment:
      RAILS_ENV: production
      SECRET_KEY_BASE: ${PRODUCTION_SECRET_KEY_BASE}
      APP_DATABASE_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
      PORT: 5000
    ports:
      - "5000:5000"
    depends_on:
      - database_production
    links:
      - database_production


  database_production:
    image: mysql:8.0.20
    command: mysqld --default-authentication-plugin=mysql_native_password
    restart: always
    container_name: database_production
    environment:
      MYSQL_USER: user
      MYSQL_PASSWORD: ${PRODUCTION_MYSQL_PASSWORD}
      MYSQL_RANDOM_ROOT_PASSWORD: "yes"
    volumes:
      - dbdata_production:/var/lib/mysql
      - mysql_socket:/var/run/mysqld
    ports:
      - "3307:3306"


volumes:
 dbdata_production:
 mysql_socket:

My Dockerfile :

FROM ruby:2.6.1

RUN echo "deb http://archive.debian.org/debian stretch main" > /etc/apt/sources.list
RUN curl https://deb.nodesource.com/setup_12.x | bash
RUN curl https://dl.yarnpkg.com/debian/pubkey.gpg | apt-key add -
RUN echo "deb https://dl.yarnpkg.com/debian/ stable main" | tee /etc/apt/sources.list.d/yarn.list
RUN apt-get update && apt-get install -y nodejs yarn
RUN apt-get update && apt-get install -y gconf-service libasound2 libatk1.0-0 libc6 libcairo2 libcups2 libdbus-1-3 libexpat1 libfontconfig1 libgcc1 libgconf-2-4 libgdk-pixbuf2.0-0 libglib2.0-0 libgtk-3-0 libnspr4 libpango-1.0-0 libpangocairo-1.0-0 libstdc++6 libx11-6 libx11-xcb1 libxcb1 libxcomposite1 libxcursor1 libxdamage1 libxext6 libxfixes3 libxi6 libxrandr2 libxrender1 libxss1 libxtst6 ca-certificates fonts-liberation libappindicator1 libnss3 lsb-release xdg-utils wget
RUN apt-get install -y default-mysql-client

WORKDIR /app_production

RUN export RAILS_ENV=production
RUN gem install bundler:2.2.31

COPY Gemfile Gemfile.lock ./
COPY package.json ./
COPY vendor/gems/ vendor/gems/

RUN bundle install

COPY . ./

RUN yarn install --check-files

EXPOSE 5000

CMD ["RAILS_ENV=production", "bundle", "exec", "puma", "-C", "config/puma.rb"]

database.yml

default: &default
  adapter: mysql2
  encoding: utf8mb4
  pool: <%= ENV.fetch("RAILS_MAX_THREADS") { 5 } %>
  username: root
  password:

production:
  <<: *default
  host: database_production
  port: 3306
  database: db_production
  username: user
  password: "some_password"
  socket: "/var/run/mysqld/mysqld.sock"

Notes :

  • This configuration is working fine on another server on Ubuntu 22.04
  • I'm now trying to make it works on this server on Ubuntu 20.04
  • I had to add RUN apt-get install -y default-mysql-client to the Dockerfile in order to have MySQL working in the Rails container, otherwise it was not found, but on my other server I did not have to do this
  • I also had to create a shared volume for mysql_socket:/var/run/mysqld otherwise socket was not found
  • I am able to log to MySQL with CLI on the Rails container, using the same credentials
  • If I put a phpMyAdmin service in docker-compose, I am also able to login to MySQL using the same credentials
  • If I try to connect to MySQL via container's rails console, it works
  • If I remove bundle exec rails db:create db:migrate from rails container command, rails is launched

So I don't understand much what exactly is happening, since I can successfully connect to MySQL through container's CLI and PhpMyAdmin.
How come Rails can't connect to it ? There are no detailed logs.

It fails at the step rake db:create and when trying to run it manually via rails console I got the same error. Very stange.

Any clue ?

答案1

得分: 1

找到解决方法:
这是与MySQL用户权限相关的问题。
必须以root用户连接到MySQL并运行以下命令:

GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';
英文:

Found the solution:
It was a privileges issue with the MySQL user.

Had to connect to MySQL as root and run the following command :

GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';

答案2

得分: 1

在Docker文件中设置环境变量MYSQL_USER只是创建了用户,但没有授予任何权限。为了解决这个问题,你可以创建一个包含以下内容的文件:

GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';

然后将其挂载到/docker-entrypoint-initdb.d文件夹中。这样,当首次运行mysql时,它将执行该文件并授予适当的权限。这种方式将在容器/卷被删除时保留权限,而不是手动授予权限。

来自mysql - Official Image | Docker的摘录

初始化一个全新的实例

当首次启动容器时,将创建一个具有指定名称的新数据库,并使用提供的配置变量进行初始化。此外,它将执行在/docker-entrypoint-initdb.d中找到的具有扩展名.sh、.sql和.sql.gz的文件。文件将按字母顺序执行。你可以通过将SQL转储挂载到该目录并提供包含贡献数据的自定义镜像来轻松填充你的mysql服务。默认情况下,SQL文件将导入到由MYSQL_DATABASE变量指定的数据库中。

英文:

Setting the environment variable MYSQL_USER in the docker file just creates the user, but grants no privileges to it. To solve the problem you can create a file with the following content

GRANT ALL PRIVILEGES ON db_production.* TO 'user'@'%';

and mount it in the /docker-entrypoint-initdb.d folder. That way when mysql is run for the first time it will execute the file and grant the appropiate privileges. This way will endure any container/volume deletion as opposed to manually granting the privileges.

Excerpt from mysql - Official Image | Docker

> Initializing a fresh instance
>
> When a container is started for the first time, a new database with the specified name will be created and initialized with the provided configuration variables. Furthermore, it will execute files with extensions .sh, .sql and .sql.gz that are found in /docker-entrypoint-initdb.d. Files will be executed in alphabetical order. You can easily populate your mysql services by mounting a SQL dump into that directory and provide custom images with contributed data. SQL files will be imported by default to the database specified by the MYSQL_DATABASE variable.

huangapple
  • 本文由 发表于 2023年6月1日 15:07:46
  • 转载请务必保留本文链接:https://go.coder-hub.com/76379443.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定