Travis: how to run tests against different MySQL backends?

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):

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.
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!

Solution

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',
);