Travis: how to run tests against different MySQL backends?

Question

How to run tests against different MySQL backends, using one .travis.yml file?

Something like here, using matrix->include. For instance:

• Run tests against MySQL 5.5
• Run tests against MySQL 5.6
• Run tests against MySQL 5.7
• Run tests against MySQL 8.0

What i currently found is:

• use MySQL 5.7 in Travis: https://gist.github.com/gocanto/46b8d39576f612f9c6b8b4ffb3efac98
• general information about how to setup a database on Travis: https://docs.travis-ci.com/user/database-setup/

Latter mentions

You can also install MySQL 5.7 on sudo-enabled Ubuntu Trusty.

But does not mention how to use 5.6 and 5.7, for instance.

Possible workarounds (if there is no "Travis" way to solve it):

1. Approach: Create different Docker setups, each with a certain MySQL version and test my script manually. But this would bloat my development environment very heavily and is quite time consuming.

2. Install required MySQL server from sources/via apt, depending on the related environment variable (e.g. MYSQL_VERSION == 5.5). Using certain configuration to access it.

Thanks for your feedback!

Answer 1

I took the example that @k00ni linked to in the PyMySQL initializedb.sh script and boiled it down to something a bit simpler, since my tests don't require TLS or a custom user. Here's the Sqitch linux-mysql script:

#!/bin/bash

if [ $MYSQL = 'system' ]; then
    exit
fi

# Derived from https://github.com/PyMySQL/PyMySQL/blob/master/.travis/initializedb.sh
set -e
sudo service mysql stop
docker pull ${MYSQL}
RUN_MYSQL="docker run -it --name=mysqld -d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306"
export MYSQL_URI=db:mysql://root@127.0.0.1/information_schema

if [ $MYSQL == 'mysql:8.0' ]; then
    ${RUN_MYSQL} ${MYSQL} --default-authentication-plugin=mysql_native_password
else
    ${RUN_MYSQL} ${MYSQL}
fi

It exports MYSQL_URI so the tests no how to connect; modify as appropriate for your own tests.

This script doesn't bother to wait for MySQL to finish starting, because the Sqitch tests take a minute or two do do a bunch of other stuff before running the tests that connect to the database. If your project needs to wait for MySQL to start, append this to the end of the script:

while ! docker exec mysqld mysqladmin ping --host localhost --silent &> /dev/null ; do
    echo "Waiting for database connection..."
    sleep 2
done

Either way, here's how the the `.travis.yml creates a build stage to run multiple versions of MySQL:

jobs:
  include:
    # https://hub.docker.com/_/mysql
    # https://hub.docker.com/_/mariadb
    - &mysql
      stage: 🐬 MySQL
      if: branch = master
      services: docker
      env: MYSQL=mysql:8.0
      before_install:
        - source dev/linux-mysql
        - source dev/linux-prereqs
      script:
        - LIVE_MYSQL_REQUIRED=1 prove -lr --directives --comments t/mysql.t
    - <<: *mysql
      env: MYSQL=mysql:5.7
    - <<: *mysql
      env: MYSQL=mysql:5.6
    - <<: *mysql
      env: MYSQL=mysql:5.5
    - <<: *mysql
      env: MYSQL=mariadb:10.4
    - <<: *mysql
      env: MYSQL=mariadb:10.3
    - <<: *mysql
      env: MYSQL=mariadb:10.2
    - <<: *mysql
      env: MYSQL=mariadb:10.1
    - <<: *mysql
      env: MYSQL=mariadb:10.0
    - <<: *mysql
      env: MYSQL=mariadb:5.5

See this build for how it ends up (along with lots of other database tests).

Answer 2

I will answer my own question, but am open for other approaches.

Solution

The following solution is based on the .travis.yml from the PyMySQL project: https://github.com/PyMySQL/PyMySQL/blob/master/.travis.yml

You need a combination of a script and the .travis.yml configuration.

Script

If you want to reuse the following code, use the following path in your project: .travis/install-and-init-db.sh

The following script installs the database in the required version:

# debug
set -x
# verbose
set -v

if [ ! -z "${DB}" ]; then
    # disable existing database server in case of accidential connection
    sudo service mysql stop

    docker pull ${DB}
    docker run -it --name=mysqld -d -e MYSQL_ALLOW_EMPTY_PASSWORD=yes -p 3306:3306 ${DB}
    sleep 10

    mysql() {
        docker exec mysqld mysql "${@}"
    }
    while :
    do
        sleep 5
        mysql -e 'select version()'
        if [ $? = 0 ]; then
            break
        fi
        echo "server logs"
        docker logs --tail 5 mysqld
    done

    mysql -e 'select VERSION()'

    if [ $DB == 'mysql:8.0' ]; then
        WITH_PLUGIN='with mysql_native_password'
        mysql -e 'SET GLOBAL local_infile=on'
        docker cp mysqld:/var/lib/mysql/public_key.pem "${HOME}"
        docker cp mysqld:/var/lib/mysql/ca.pem "${HOME}"
        docker cp mysqld:/var/lib/mysql/server-cert.pem "${HOME}"
        docker cp mysqld:/var/lib/mysql/client-key.pem "${HOME}"
        docker cp mysqld:/var/lib/mysql/client-cert.pem "${HOME}"
    else
        WITH_PLUGIN=''
    fi

    mysql -uroot -e 'create database testdb DEFAULT CHARACTER SET utf8mb4'
else
    cat ~/.my.cnf

    mysql -e 'select VERSION()'
    mysql -e 'create database testdb DEFAULT CHARACTER SET utf8 DEFAULT COLLATE utf8_general_ci;'
fi

.travis.yml

You have to use the matrix->include block. For PHP see the following code. In the mentioned PyMySQL you will find a way for Python.

sudo: required

language: php

services:
  - docker

matrix:
    fast_finish: true
    include:
        #
        # Define versions of MySQL and MariaDB to test against.
        #
        # mysql 5.5
        - php: 5.6
          env: DB=mysql:5.5
        - php: 7.0
          env: DB=mysql:5.5
        - php: 7.1
          env: DB=mysql:5.5
        - php: 7.2
          env: DB=mysql:5.5
        # mysql 5.6
        - php: 5.6
          env: DB=mysql:5.6
        # ...
        # mariadb 10.0
        - php: 5.6
          env: DB=mariadb:10.0
        - php: 7.0
          env: DB=mariadb:10.0
        - php: 7.1
          env: DB=mariadb:10.0
        - php: 7.2
          env: DB=mariadb:10.0
        # ...

before_script:
    #
    # install and init database (see matrix => include => env)
    #
    - ./.travis/install-and-init-db.sh

    #
    # setup and run tests
    #
    # Install composer packages, will also trigger dump-autoload
    - travis_retry composer install --no-interaction

script:
    - vendor/bin/phpunit --coverage-clover gen/coverage/clover.xml

Create and use connection

Use the following parameter to create a connection to the database server:

$dbConfig = array(
    'db_name' => 'testdb',
    'db_user' => 'root',
    'db_pwd'  => '',
    'db_host' => '127.0.0.1',
);