GitLab CI - MySQL - Access denied for user 'debian-sys-maint'@'172.17.0.3' (using password: YES)

Hello all, currently trying to troubleshoot my gitlab-ci setup for a Ruby on Rails project that uses a MySQL -v 5.7 database. Running into the below error:

Mysql2::Error::ConnectionError: Access denied for user 'debian-sys-maint'@'172.17.0.3' (using password: YES)

I store the username and password as environment variables in the GitLab CI / CD Variables section.

  • DB_USERNAME
  • DB_PASSWORD

I then connect to the DB and access those variables:

echo "SELECT 'OK';" | mysql --user="$DB_USERNAME" --password="$DB_PASSWORD" --host="$DB_HOST" "$MYSQL_DATABASE"

After that I run:

bundle exec rake db:create db:migrate db:seed

That’s when the error from above occurs and fails to create the DB. Of note, I’m able to connect to the standup db locally with the same user and password stored in the GitLab CI / CD environment variables.

.gitlab-ci.yml

image: 'registry.gitlab.com/lisasnapit/onboarding-app:latest'

stages:
  - build
  - mysql
  - rubocop

variables:
  LC_ALL: C.UTF-8
  LANG: en_US.UTF-8
  LANGUAGE: en_US.UTF-8

.default-cache: &default-cache
  cache:
    untracked: true
    key: suna-cache
    paths:
      - node_modules/
      - vendor/
      - public/

build:
  <<: *default-cache
  stage: build
  script:
    - ruby -v
    - node -v
    - yarn --version
    - which ruby
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
    - yarn install

mysql:
  <<: *default-cache
  stage: mysql
  variables:
    MYSQL_DATABASE: standup
    MYSQL_ROOT_PASSWORD: ''
    MYSQL_ALLOW_EMPTY_PASSWORD: 'yes'
    DB_USERNAME: $DB_USERNAME
    DB_PASSWORD: $DB_PASSWORD
    DB_HOST: 'mysql'
  services:
    - mysql:5.7
  before_script:
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
    - cp config/database.gitlab config/database.yml
  script:
    - echo "SELECT 'OK';" | mysql --user="$DB_USERNAME" --password="$DB_PASSWORD" --host="$DB_HOST" "$MYSQL_DATABASE"
    - sleep 20
    - bundle exec rake db:create db:migrate db:seed
    - bundle exec rails assets:precompile

rubocop:
  <<: *default-cache
  stage: rubocop
  before_script:
    - gem install bundler -v 1.17.3 --no-document
    - bundle install --path=vendor
  script:
    - bundle exec rubocop

config/database.yml

default: &default
  adapter: mysql2
  encoding: utf8
  pool: <%= ENV['RAILS_MAX_THREADS'] || 5 %>
  timeout: 5000
  database: <%= ENV['DB'] %>
  username: <%= ENV['DB_USERNAME'] || 'root' %>
  password: <%= ENV['DB_PASSWORD'] %>
  host: <%= ENV['DB_HOST'] %>

development:
  <<: *default
  database: <%= ENV['DB'] || 'standup' %>

# Warning: The database defined as "test" will be erased and
# re-generated from your development database when you run "rake".
# Do not set this db to the same as development or production.
test:
  <<: *default
  database: standup_test

production:
  <<: *default
  username: <%= ENV['DB_USERNAME'] %>

Hi,

maybe an origin problem with different IPs for the client. Can you show the database access for this specific user?

select host, user, password from mysql.user;

Cheers,
Michael

Output after logging in and changing to mysql DB and standup DB to check both.

mysql -u debian-sys-maint -p

password input

mysql db

use mysql;

The below command doesn’t work for me however since I use MySQL 5.7, and the password field is replaced with authentication_string.

mysql> select host, user, password from mysql.user;
ERROR 1054 (42S22): Unknown column 'password' in 'field list'

standup db

use standup;

Hi,

hmmmm, it could be that the debian-sys-maint is just for local access. AFAIK this user only exists to recover a locked out root user, and other local tasks.

If you need a user which is allowed to create a database next to the root user, create a new one which may access from an ip range. I don’t suggest to use a wildcard with %, this opens up a security problem.

Something like 'maint'@'172.17.0.0/255.255.0.0' as subnet should be sufficient.

Cheers,
Michael

1 Like

Created a user maint with access to an ip range of 172.17.0.0/255.255.0.0 and utilized the same password stored in the environment variable on GitLab.

Seems to be the same output as before with the debian-sys-maint user.

When I test with just use the root user, it seems I’m able to get passed that, but upon the bundle exec rake db:create db:migrate commands, it comes back with the following error:

Mysql2::Error::ConnectionError: Can't connect to local MySQL server through socket '/run/mysqld/mysqld.sock' (2)

At the start of this jobs output, it initially says the concurrent-0-mysql-0 service runner probably didn't start properly and the ...wait-for-service timed out.

It proceeds to initialize however shortly after and mysqld listens for connections via socket, /var/run/mysqld/mysqld.sock.

Since I’m using the docker executor on my gitlab-runner I had registered for this project since the shared runners were giving me trouble, it was recommended to use the host mysql for the mysql:5.7 service.
https://docs.gitlab.com/ee/ci/docker/using_docker_images.html#how-services-are-linked-to-the-job


Update:

Upon removing the saved variables within GitLab CI / CD settings and the Variables section, which is DB_USERNAME and DB_PASSWORD, I am able to run the rake db:* commands and the job succeeds. This is of course using just the root user with no password.

I’m not sure why that works, but a user created that has access to the 172.17.0.0/255.255.0.0 subnet does not.

Hi,

maybe the subnets don’t really match in this regard, and we have to go the % route (access from anywhere). With using root you also grant too many permissions, I’d recommend avoiding this - same as debian-sys-maint.

Cheers,
Michael