MySQL 8.0 on Gitlab CI/CD

I had a few problems getting a MySQL instance up and running in Gitlab CI/CD, so here are the errors I found and the steps I took.

Here’s the stanza from the .gitlab-ci.yml I ended up with:

gitab_mysql_service:
  stage: independent_unit_tests
  image: alpine:latest
  services:
  - name: mysql:8.0.20
    command: ["--default-authentication-plugin=mysql_native_password"]
  variables:
    MYSQL_DATABASE: avocado_test
    MYSQL_ROOT_PASSWORD: mysql
  artifacts:
      paths:
        - db/db_out.txt
      expire_in: 1 week
  script:
  - pwd
  - apk --no-cache add mysql-client
  - mysql --version
  - cd db/
  - mysql --default-auth=mysql_native_password -p"$MYSQL_ROOT_PASSWORD" -h mysql -D "${MYSQL_DATABASE}" -u root < demo.sql > db_out.txt
  - cat db_out.txt 

 

And these are the errors I came across as I was working on it. Bold is for commands in the .gitlab_ci.yml file, and italic is for the system response.

$ echo "SELECT 'OK';" | mysql --user=root --password="$MYSQL_ROOT_PASSWORD" --host=mysql "$MYSQL_DATABASE"

mysql: [Warning] Using a password on the command line interface can be insecure.<

ERROR 2005 (HY000): Unknown MySQL server host 'mysql' (22)

(I think at this point I hadn’t defined the variables properly in the .yml file.)

 

$ echo "SELECT 'OK';" | mysql --user=root --password="$MYSQL_ROOT_PASSWORD"

mysql: [Warning] Using a password on the command line interface can be insecure.

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

I assume the client tried to find a database running on the local host, since a network location wasn’t specified.

 

$ echo "SHOW tables;"| mysql -u root -p"$MYSQL_ROOT_PASSWORD" -h 127.0.0.1 "${MYSQL_DATABASE}"

ERROR 2002 (HY000): Can't connect to MySQL server on '127.0.0.1' (115)

Here I’d missed the fact that I’d suggested the host was two different things.

 

$ echo "SHOW tables;"| mysql -u root -p"$MYSQL_ROOT_PASSWORD" -h "${MYSQL_DATABASE}"

ERROR 2005 (HY000): Unknown MySQL server host 'avocado_test' (-2)

Now my host variable (MYSQL_DATABASE) didn’t match the system’s name for the MySQL service host.

 

$ echo "SHOW tables;"| mysql -u root -p"$MYSQL_ROOT_PASSWORD" -h mysql -D "${MYSQL_DATABASE}"

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so: No such file or directory

This one puzzled me for a while. Apparently MySQL 8.0 changed the default authentication mechanism, so if you want to log in from the command line you need to tell the database to accept that. I tried a few more ways of doing that before I discovered that I needed to do it in the services / command part of the job stanza (as I did in the end, seen above).

 

$ echo "SHOW tables;"| mysql -u root --default-auth=mysql_native_password -p"$MYSQL_ROOT_PASSWORD" -h mysql -D "${MYSQL_DATABASE}"

ERROR 1045 (28000): Plugin caching_sha2_password could not be loaded: Error loading shared library /usr/lib/mariadb/plugin/caching_sha2_password.so: No such file or directory

Telling the client how the authentication should work, won’t necessarily work. Should have guessed.

 

$ echo "SHOW tables;"| mysql --ssl-mode=DISABLED --default-auth=mysql_native_password -p"$MYSQL_ROOT_PASSWORD" -h mysql -D "${MYSQL_DATABASE}" -u root

mysql: unknown variable 'ssl-mode=DISABLED'

I thought this was worth trying, to see if I could get the client to try another way to authenticate, but I must have had at least the wrong syntax.

 

$ mysql --default-auth=mysql_native_password -p"$MYSQL_ROOT_PASSWORD" -h mysql -D "${MYSQL_DATABASE}" -u root < demo.sql > db_out.txt

/bin/sh: eval: line 104: can't open demo.sql: no such file

Files you haven’t created can’t be found. Surprising, I know. Nothing else would have worked either, but the file error took precedence over the authentication mode problem.

 

Leave a Reply

Your email address will not be published. Required fields are marked *

I accept that my given data and my IP address is sent to a server in the USA only for the purpose of spam prevention through the Akismet program.More information on Akismet and GDPR.

This site uses Akismet to reduce spam. Learn how your comment data is processed.