Test multiple supported database type and version combinations 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.

[audrasjb]

As per today's bug scrub and since tomorrow is WP 5.9 feature freeze, I'm moving this ticket to Future release.

[jrf]

Related: #52496

This PR moves the actual testing aspect of the PHPUnit workflow to a callable one. This allows for two jobs with two unique strategy matrices specific to each database type, passing configuration details to the new callable workflow.

Trac ticket: https://core.trac.wordpress.org/ticket/30462

This option keeps everything to do with the PHPUnit workflow the same and just expands the strategy matrix. This is a cleaner diff, but does require a half dozen exclude statements to be added in order to avoid invalid combinations.

Trac ticket: https://core.trac.wordpress.org/ticket/30462.

[desrosj]

I had some great discussions with @l1nuxjedi and @johnbillion at WordCamp Europe 2023 around this. I've finally had some time to take a look at this following that, and here's a breakdown.

First, since I last commented above with versions:

• PHP 8.0, 8.1, 8.2 were officially released.
• WordPress has dropped support for PHP 5.6 (see [56141]).
• MySQL 5.6 reached EOL (see [52420], [54069]) and is no longer supported.
• Several versions of MariaDB are now marked as unmaintained (10.2 and 10.3). 10.4 will be supported through June 2024.

After chatting with @l1nuxjedi, these are the recommended ​versions of MariaDB that we should be worried about testing against:

• 10.4 (oldest stable release actively maintained)
• 10.6 (previous long-term release, maintained through July 2026)
• 10.11 (current long-ter stable release, maintained through February 2028)
• 11.0 (current stable release, maintained through June 2024)

Paring this down from all actively maintained MariaDB versions and eliminating PHP 5.6 from the equation cuts down the testing strategy matrix quite a bit.

I've explored a few options to tackle this, and have two PRs attached here to get feedback on.

Option 1 - ​PR-5015: This option keeps everything to do with the PHPUnit workflow the same and just expands the strategy matrix. This is a cleaner diff, but does require a half dozen exclude statements to be added in order to avoid invalid combinations.

Option 2 - ​PR-5016: This moves the actual testing aspect of the PHPUnit workflow to a callable one. This allows for two jobs with two unique strategy matrices specific to each database type, passing configuration details to the new callable workflow.

I'm leaning towards option 2. I quite like how option 2 is displayed in the GHA interface (each job is tucked in an expandable element for the respective PHP versions) however it's not displayed the same on the "checks" screen for a PR (the PHP version is repeated). I also don't really love requiring 6 exclude statements. It's possible that could grow in the future as more versions are added. I'm indifferent about having a new callable workflow.

These do result in a 5x increase in the number of jobs. However, I don't think it's a bad thing. I had considered having a smaller set of versions that we run on every commit, and a scheduled event that tested the full set of combinations. But after chatting with @johnbillion I don't think that's better. There's also room for improvement to make the tests run faster. They're currently creeping above 5 minutes per job, but it should be possible to cut that down.

Looking to see what others think!

[johnbillion]

I'm also leaning toward approach 2, I've had good results splitting the workflows in my plugins into reusable workflows. Another advantage it has is that if the tests start failing for one of the database types, it'll be presented more clearly on the job diagram on the Summary screen because the job lists are split by the two database types.

@desrosj Approach 1 PR is running 98 jobs but the approach 2 PR is running 101. The difference is in the PHP 7.4 jobs, approach 1 is running 2 memcached jobs but approach 2 is running 4. Plus Approach 2 is running a duplicate "MySQL 5.7" job.

[desrosj]

Replying to johnbillion:

@desrosj Approach 1 PR is running 98 jobs but the approach 2 PR is running 101. The difference is in the PHP 7.4 jobs, approach 1 is running 2 memcached jobs but approach 2 is running 4. Plus Approach 2 is running a duplicate "MySQL 5.7" job.

Ah, good catch. The duplicate is the job that reports the test results to the .org Host Test Results, it just was not noted within the job name. I've adjusted both workflows to reflect that within the name. Additionally, I don't think there's any need to run a second job specifically for reporting. That's something handled after all tests are completed and do not impact the environment at all. I've adjusted the strategies to allow report to be defined as true in one include statement to fix this.

For the second discrepancy, I added two jobs that tested memcached with MariaDB in the second approach, but did not copy that over to the first PR. I've gone and done that so both should now be identical with 100 jobs total.

[desrosj]

In 56439:

Build/Test Tools: Expand database testing to account for all supported versions and types.

This modifies the PHPUnit workflow to add MySQL version 8.0 to the strategy matrix. It also adds an additional strategy matrix to expand testing to include several supported MariaDB versions.

To prevent repeating code, the business logic of the PHPUnit testing has been moved to a new, callable workflow.

Because callable workflows target a specific branch, branches created in the future will continue to benefit from improvements made to the workflow in trunk without requiring backports. If a breaking change is required, older branches will need to be updated to target the commit SHA representing the final commit of the previous state.

Props johnbillion, pento, boonebgorges, netweb, nacin, desrosj.
Fixes #30462.

Closed in favor of #5016

Merged in https://core.trac.wordpress.org/changeset/56439.

[desrosj]

In 56440:

Build/Test Tools: Merge pre-commit changes missed in [56439].

Props l1nuxjedi.
See #30462.

[desrosj]

In 56443:

Build/Test Tools: Implicitly pass secrets to the called workflow.

This ensures the Hosting Test results API key is present for reporting results.

Follow up to [56439] and [56440].

See #30462.

[desrosj]

In 56464:

Build/Test Tools: Ensure database containers are prepared for commands.

By default, Docker waits for a container to be started before considering it “ready”. But this does not necessarily mean that it’s ready to receive commands.

This adds a check that ensures the database container is ready to receive commands before proceeding with running commands.

Follow up to [56439], [56440], [56443].

Props rmccue, desrosj.
See #30462, #58867.