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 Cancel reply