Test multiple MySQL versions on GitHub Actions

[pento]

As we do with PHP, it'd also be nice to test multiple versions of MySQL against our unit tests.

[pento]

30462.diff​ is a first pass of testing against the latest releases of MySQL 5.0, 5.1, 5.5 and 5.6. It shows some MySQL 5.0-specific unit test failures:

​https://travis-ci.org/pento/develop.wordpress/builds/41861516

[pento]

For exciting combinations, here's a test that consistently fails with PHP 5.2 and MySQL 5.1:

​https://travis-ci.org/pento/develop.wordpress/jobs/41917241

[pento]

Replying to pento:

For exciting combinations, here's a test that consistently fails with PHP 5.2 and MySQL 5.1:

​https://travis-ci.org/pento/develop.wordpress/jobs/41917241

My mistake, it's not consistent by PHP version. Seems it's an undefined sorting bug.

[boonebgorges]

In 30548:

Declare an explicit 'order' in test_get_comments_by_user().

This change fixes broken builds on older versions of MySQL. See #30462.

Assertions regarding indeterminate secondary ORDER BY clauses will go in
separate test methods. See #30478.

[pento]

Going by some recent testing, it looks like we should try and cache the MySQL builds, or host them ourselves - sometimes the download is really slow. I'm not sure if that's the MySQL CDN or the Travis network, though.

As suggested by @netweb, we should test against MariaDB, as well. For reference, here's an approximate version match:

MariaDB 5.1 = MySQL 5.1
MariaDB 5.2 = MySQL 5.1
MariaDB 5.3 = MySQL 5.1 + some of 5.5
MariaDB 5.5 = MySQL 5.5
MariaDB 10.0 = MySQL 5.5 + some of 5.6
MariaDB 10.1 (alpha) = MySQL 5.5 + most of 5.6 + some of 5.7

As of 10.0, MariaDB is starting to diverge from MySQL more heavily. It still aims to be compatible, but incompatibilities will inevitably be introduced.

I'm inclined to test against MariaDB 10.0 and 10.1, and also add MySQL 5.7 for alpha testing. It's tricky to drag MariaDB usage out of our MySQL stats, but I'd expect MariaDB 5.x to be lower than 10.0, which is already pretty small.

[netweb]

Replying to pento:

Going by some recent testing, it looks like we should try and cache the MySQL builds, or host them ourselves - sometimes the download is really slow. I'm not sure if that's the MySQL CDN or the Travis network, though.

Did some research on this yesterday, caching is only available for "paid accounts" but we can work around this with some custom build scripts and hosting these files ourselves.

This is beyond the scope of this ticket though and we should look at doing "something" with these databases as each DB is coming in at ~300mb per DB version!

As suggested by @netweb, we should test against MariaDB, as well. For reference, here's an approximate version match:

MariaDB 5.1 = MySQL 5.1
MariaDB 5.2 = MySQL 5.1
MariaDB 5.3 = MySQL 5.1 + some of 5.5
MariaDB 5.5 = MySQL 5.5
MariaDB 10.0 = MySQL 5.5 + some of 5.6
MariaDB 10.1 (alpha) = MySQL 5.5 + most of 5.6 + some of 5.7

As of 10.0, MariaDB is starting to diverge from MySQL more heavily. It still aims to be compatible, but incompatibilities will inevitably be introduced.

I'm inclined to test against MariaDB 10.0 and 10.1, and also add MySQL 5.7 for alpha testing. It's tricky to drag MariaDB usage out of our MySQL stats, but I'd expect MariaDB 5.x to be lower than 10.0, which is already pretty small.

Agreed, I've got two patches in a state of "work in progress" tweaking and adjusting the build matrix for performance!

What I have included thus far is:

• DB=MySQL5.0
• DB=MySQL5.1
• DB=MySQL5.5
• DB=MySQL5.6
• DB=MariaDB5.5
• DB=MariaDB10.0
• DB=MySQL5.7 (Allowed to fail)

With 6 versions of PHP we are currently testing against and the above 7 databases brings us to 42 tests, if we add MariaDB 5.1, 5.2 and 5.3 to this we we end up at 60 jobs / build (Or 80 if we include HHVM & HHVM Nightly). MySQL Sandbox does not support, nor work, with MariaDB 10.1alpha at this stage (​src).

A sneak peek at this is here ​https://travis-ci.org/ntwb/wordpress/builds/41943470 (I'm not actually running PHPUnit tests here, these should be listed as failing and not passing BTW ;)

Will upload both patches later this arvo, though I am leaning towards some "selective" combination of PHP/DB testing to keep the number of jobs per build much lower than 80, possibly lower than 40 for example only test MariaDB 5.3 against a single PHP version and not all versions of PHP.

[pento]

Replying to netweb:

Did some research on this yesterday, caching is only available for "paid accounts" but we can work around this with some custom build scripts and hosting these files ourselves.

I noticed Travis allow custom apt-get repos - we could potentially build a repo with just our test versions of MySQL. That'd let us remove the perl/cpanm/MySQL Sandbox stuff, too.

This is beyond the scope of this ticket though and we should look at doing "something" with these databases as each DB is coming in at ~300mb per DB version!

We can look at stripping things out of the official builds, but I don't like our chances. I suspect our better option is going to be a self-hosted CI, so that all the transfers stay within our network.

With 6 versions of PHP we are currently testing against and the above 7 databases brings us to 42 tests, if we add MariaDB 5.1, 5.2 and 5.3 to this we we end up at 60 jobs / build (Or 80 if we include HHVM & HHVM Nightly). MySQL Sandbox does not support, nor work, with MariaDB 10.1alpha at this stage (​src).

I submitted a MySQL Sandbox feature request to add MariaDB 10.1 to the allowed versions list:

​https://bugs.launchpad.net/mysql-sandbox/+bug/1395965

I definitely wouldn't bother with MariaDB 5.1, 5.2 and 5.3.

A sneak peek at this is here ​https://travis-ci.org/ntwb/wordpress/builds/41943470 (I'm not actually running PHPUnit tests here, these should be listed as failing and not passing BTW ;)

That's looking super cool!

[nacin]

Once we switch over the GitHub repos and use the official wordpress/wordpress repo on Travis (later December, I expect), setting up a paid account is not a problem. We can also do that temporarily with a particular account now.

[netweb]

Replying to pento:

Replying to netweb:

Did some research on this yesterday, caching is only available for "paid accounts" but we can work around this with some custom build scripts and hosting these files ourselves.

I noticed Travis allow custom apt-get repos - we could potentially build a repo with just our test versions of MySQL. That'd let us remove the perl/cpanm/MySQL Sandbox stuff, too.

Due to download speeds locally here reminding me of dial up I ain't touching this :P But yes, a custom apt-get repo is one way, as is a single/multiple custom archives of our own hosted on s.wp.org (or wherever).

This is beyond the scope of this ticket though and we should look at doing "something" with these databases as each DB is coming in at ~300mb per DB version!

We can look at stripping things out of the official builds, but I don't like our chances. I suspect our better option is going to be a self-hosted CI, so that all the transfers stay within our network.

I'll post the reply I received from Travis-CI regarding pricing of said self-hosted versions to Slack via DM's (not sure that they want the pricing public)

With 6 versions of PHP we are currently testing against and the above 7 databases brings us to 42 tests, if we add MariaDB 5.1, 5.2 and 5.3 to this we we end up at 60 jobs / build (Or 80 if we include HHVM & HHVM Nightly). MySQL Sandbox does not support, nor work, with MariaDB 10.1alpha at this stage (​src).

I submitted a MySQL Sandbox feature request to add MariaDB 10.1 to the allowed versions list:

​https://bugs.launchpad.net/mysql-sandbox/+bug/1395965

I definitely wouldn't bother with MariaDB 5.1, 5.2 and 5.3.

Cool :)

A sneak peek at this is here ​https://travis-ci.org/ntwb/wordpress/builds/41943470 (I'm not actually running PHPUnit tests here, these should be listed as failing and not passing BTW ;)

That's looking super cool!

Thanks

Replying to nacin:

Once we switch over the GitHub repos and use the official wordpress/wordpress repo on Travis (later December, I expect), setting up a paid account is not a problem. We can also do that temporarily with a particular account now.

Cool +1

[netweb]

In 30462.2.diff alongside Travis-CI results: ​https://travis-ci.org/ntwb/wordpress/builds/42050606

• Bump the PHP version to 5.6 of the JavaScript test job WP_TRAVISCI=travis:js(In r28799 I chose to use the latest version of PHP, nothing special )

• Build Matrix PHP 5.2, 5.3, 5.4, 5.5 & 5.6 against MySQL 5.0, 5.1, 5.5, 5.6 and MariaDB 5.5 & 10.0 for a total of 30 PHPUnit jobs per build

• Allowed to fail PHP 5.6/MySQL 5.7, HHVM/MySQL 5.6 and HHVM-Nightly/MySQL 5.6 for a total of 3 PHPUnit jobs per build

• Run npm update -g in before_script to update global NPM packages, primarily to force NPM to update to the latest revision and avoid NodeJS module imagemin warning. (Travis-CI needs to update their dependencies)

• Total time of the Travis-CI build is 60-70 mins at time of writing with 4 VM's per build running concurrently

[netweb]

30462.3.diff​ builds on 30462.2.diff​ with a specific build job for utf8mb4, see #21212 ;)
Example build job: ​https://travis-ci.org/ntwb/wordpress/jobs/44395543 :P

[netweb]

Travis CI now supports cron jobs for scheduling jobs, this could be a once a day build rather than per commit for testing a large matrix of database versions without being overly time consuming for the existing build matrix. This build matrix could then include alternate locale collations and character sets.

To perform a said once daily Travis CI build matrix we'd use a separate GitHub repo specifically for this purpose with a custom .travis.yml defining this build matrix.

See ​https://docs.travis-ci.com/user/cron-jobs/

[netweb]

Just leaving these notes somewhere related, they came about on the way to r37555, but will be handy for this ticket in creating a large set of DB tests...

Travis CI 


Both these HHVM jobs work correctly

This first (basic) uses HHVM 3.6.x (No longer supported) on Ubuntu Precise with MySQL 5.5
  - php: hhvm
    env: WP_TRAVISCI=travis:phpunit MATRIX_OS="precise" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="HHVM 3.6.6~precise" MATRIX_SQL="MySQL 5.5"

This second job uses the current HHVM 3.13.x branch on Ubuntu Trusty with MySQL 5.6 (group:edge probably can be removed)
  - php: hhvm
    env: WP_TRAVISCI=travis:phpunit MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="edge" MATRIX_OS_PHP="HHVM 3.13.1~trusty" MATRIX_SQL="MySQL 5.6"
    group: edge
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6

This batch of jobs works but should be added to the Travis MySQL ticket:
  - php: 7
    env: MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MySQL 5.6"
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6
  - php: 7
    env: MATRIX_OS="trusty" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MariaDB 10.1"
    addons:
      mariadb: 10.1
  - php: 7
    env: MATRIX_OS="trusty" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MariaDB 10.0"
    addons:
      mariadb: 10.0
  - php: 7
    env: MATRIX_OS="trusty" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MariaDB 5.5"
    addons:
      mariadb: 5.5

This batch of jobs also work and should be added to an a daily Travis CI database job (along with Jorbin's 78 character set/collation jobs)
  - php: 7
    env: DB=utf8mb4:utf8mb4_unicode_ci MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MySQL 5.6"
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6
  - php: 7
    env: DB=utf8mb4:utf8mb4_unicode_ci MATRIX_OS="trusty" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MariaDB 10.1"
    addons:
      mariadb: 10.1
  - php: 7
    env: DB=utf8mb4:utf8mb4_unicode_520_ci MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MySQL 5.6"
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6
  - php: 7
    env: DB=utf8mb4:utf8mb4_unicode_520_ci MATRIX_OS="trusty" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MariaDB 10.1"
    addons:
      mariadb: 10.1
- |
  if [[ "$DB" == "utf8mb4:utf8mb4_unicode_ci" ]]; then
    sed -i "s/utf8/utf8mb4/" wp-tests-config.php
    sed -i "s/'DB_COLLATE', ''/'DB_COLLATE', 'utf8mb4_unicode_ci'/" wp-tests-config.php
    grep "DB_C" wp-tests-config.php
  fi
- |
  if [[ "$DB" == "utf8mb4:utf8mb4_unicode_520_ci" ]]; then
    sed -i "s/utf8/utf8mb4/" wp-tests-config.php
    sed -i "s/'DB_COLLATE', ''/'DB_COLLATE', 'utf8mb4_unicode_520_ci'/" wp-tests-config.php
    grep "DB_C" wp-tests-config.php
  fi

  
MySQL 5.6 jobs to be also added to the MySQL Travis ticket:

  - php: 5.6
    env: MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 5.6" MATRIX_SQL="MySQL 5.6"
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6
  - php: 5.2
    env: MATRIX_OS="precise" MATRIX_SUDO="inherited" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 5.2" MATRIX_SQL="MySQL 5.5"
    sudo: false
  - php: 7
    env: MATRIX_OS="trusty" MATRIX_SUDO="required" MATRIX_OS_GROUP="standard" MATRIX_OS_PHP="PHP 7.0" MATRIX_SQL="MySQL 5.6"
    addons:
      apt:
        packages:
        - mysql-server-5.6
        - mysql-client-core-5.6
        - mysql-client-5.6




Proposed Matrix for DB crazyness

sudo: required
dist: trusty
language: php

php:
  - 5.2
  - 5.6
  - 7.0
  - hhvm

env:
  matrix:
    - MySQL 5.5
    - MySQL 5.6
    - MariaDB 5.5
    - MariaDB 10.0
    - MariaDB 10.1
cache:
  apt: true

[netweb]

Travis CI has just released a new feature "build stages" ​https://blog.travis-ci.com/2017-05-11-introducing-build-stages

Adding a ​warm up the cache build job with each of the MySQL/MariaDB versions would be a great way to utilise this

[netweb]

Replying to nacin:

Once we switch over the GitHub repos and use the official wordpress/wordpress repo on Travis (later December, I expect), setting up a paid account is not a problem. We can also do that temporarily with a particular account now.

Via ​https://make.wordpress.org/core/2017/05/18/increased-concurrent-jobs-on-travis-ci-builds/

"​https://travis-ci.org/WordPress now has 15 concurrent jobs instead of the default 5 available to all open-source projects."

[pento]

It looks like we can do cron jobs from the main .travis.yml now - by checking for the TRAVIS_EVENT_TYPE environment variable being set to cron.

It's unfortunate that Travis still doesn't (and will probably never) support includes in the .travis.yml file, because this is going to make ours ludicrous.

[netweb]

Replying to pento:

It looks like we can do cron jobs from the main .travis.yml now - by checking for the TRAVIS_EVENT_TYPE environment variable being set to cron.

Yup

It's unfortunate that Travis still doesn't (and will probably never) support includes in the .travis.yml file, because this is going to make ours ludicrous.

Yup

I've some initial thoughts on a few ways to rebuild the matrix, I'll think on that some more...

[netweb]

Related:

• #42811 Run the PHP 5.2 Travis job with the MySQL Extension
• #42812 Use MySQLi when available by default

We should also include a few MySQL/MySQLi CI job combinations testing the above ticket scenarios

[desrosj]

With the introduction of the local Docker environment (and the current move to GitHub Actions), this should be much simpler.

I have opened #51744 to add the ability to change database software. After that change is made, adding test jobs for various versions of MariaDB should not be a problem.

We'll need to come up with the combinations that we'd like to test against. Rough first pass based on a ​test workflow run I set up (though these combinations may not be ideal or indicate the actual support):

• MySQL 5.6
• MySQL 5.7
• MySQL 8.0
• MariaDB 10.1 (PHP 5.6.20 only)
• MariaDB 10.2-10.5 (PHP 5.6.20-8.0)

I'd also like to get a page similar to the ​PHPUnit and ​PHP version support set up for MySQL/MariaDB support in the handbook once we figure out the support matrix.

[desrosj]

In 49836:

Build/Test Tools: Support the use of MariaDB in the local Docker environment.

In addition to MySQL, WordPress also supports MariaDB for databases. This makes changes to the local Docker environment to allow MariaDB to be used instead, if desired.

The LOCAL_DB_TYPE environment variable will now be used to determine which type of database container to use. This will default to mysql, but will also accept mariadb.

The LOCAL_MYSQL environment variable (which previously held the version of MySQL to use) has been replaced with the new, more generic LOCAL_DB_VERSION environment variable.

This change will make it possible to perform automated testing on a wider array of supported database types and versions.

Props davidbaumwald.
Fixes #51744. See #30462.

[Presskopp]

related #41490 (waiting on this ticket)

[desrosj]

I'd like to prioritize this for 5.9.