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.
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'] %>
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.
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.
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.
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.
this was not an easy one to debug for us, we just had a ....$F.... in the randomly-generated database password and gitlab was trying to replace that with a variable. essentially altering the password, and this caused the same issue
actually, that’s the shell where the command is invoked. Since some of the examples use double quotes, all non-escaped character sequences will be interpreted as such. Even if $ characters in passwords might be recommended, they cause more troubles in different input methods. I’d suggest avoiding them, especially with different access levels and operating systems.
The following should work with escaping $ with \$: