Silverstripe-framework: version 4.x has a broken test on MySQL 5.5

[dnsl48]

Affected Version

SilverStripe Framework 4.x-dev

Description

We have a failing test with MySQL 5.5.
Particularly, we use a database feature set transaction READ ONLY, which has been introduced in MySQL 5.6.

The particular test is ORM::TransactionTest::testReadOnlyTransaction:

$ ./vendor/bin/phpunit --testsuite core --filter TransactionTest::testReadOnlyTransaction
PHPUnit 5.7.27 by Sebastian Bergmann and contributors.

E                                                                   1 / 1 (100%)

Time: 3.48 seconds, Memory: 201.50MB

There was 1 error:

1) SilverStripe\ORM\Tests\TransactionTest::testReadOnlyTransaction
SilverStripe\ORM\Connect\DatabaseException: Couldn't run query:

SET TRANSACTION READ ONLY

Unknown system variable 'TRANSACTION'

/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/DBConnector.php:64
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/MySQLiConnector.php:181
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/Database.php:143
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/Database.php:228
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/Database.php:145
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/MySQLDatabase.php:380
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/MySQLTransactionManager.php:38
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/NestedTransactionManager.php:51
/mnt/bench/im4t/vendor/silverstripe/framework/src/ORM/Connect/MySQLDatabase.php:333
/mnt/bench/im4t/vendor/silverstripe/framework/tests/php/ORM/TransactionTest.php:171
/mnt/bench/im4t/vendor/phpunit/phpunit/phpunit:52

ERRORS!
Tests: 1, Assertions: 0, Errors: 1.

[chillu]

Travis supports MySQL 5.5, 5.6 and 5.7 through different Ubuntu images. MySQL 5.5 goes EOL Dec 2018 (https://en.wikipedia.org/wiki/MySQL#Release_history), so I think we should stop claiming support for it. Unlike the PHP situation where there's a large reliance on unsupported versions across the ecosystem (see discussion at https://github.com/silverstripe/silverstripe-framework/issues/8556), I don't see that amount of use in MySQL 5.5. MySQL 5.5 was released 8 years ago, MySQL 5.6 was released 5 years ago. I think we're OK. Is anyone aware of widespread use of MySQL 5.5?

Given that we run MariaDB in SSP, and it's becoming an increasingly popular choice in the wider ecosystem, we should probably start running builds against that as well. I've started a discussion at https://github.com/silverstripe/silverstripe-framework/issues/8655.

[chillu]

I couldn't find any semver-like statements about MySQL, and they're both deprecating and removing server variables and removing features like YEAR(2) in minor releases like 5.7. That being said, I doubt that a SilverStripe application would have trouble upgrading from MySQL 5.5 to 5.7 or beyond, due to the ORM and database abstraction. Even some more esoteric use cases I've seen in the wild (like using MySQL geospatial fields) appear to be unaffected by those changes.

[dnsl48]

This same issue also affects MariaDB 5.5 and lower.

[sminnee]

I believe that this relates to my work in #8448 a couple of months back; this test may have been originally relegated to one of the specific database backends?

The bug wasn't introduced by my change - rather, startTransaction("READ ONLY") wasn't previously being tested.

It's an edge-case feature and my suggestion would be to skip the test on MySQL 5.5. This doesn't mean we should run the 5.5 test on travis; so this may wait until some community member is running 5.5 themselves and complains. But at least now, this ticket logs some context! :-P

[sminnee]

FYI I've removed the failing test in #8503 because it also breaks on SQlite and it's testing a deprecated feature that wasn't tested in 4.3.

Updated build matrix in https://travis-ci.org/silverstripe/silverstripe-framework/builds/464675917 to clarify database version numbers.

[dnsl48]

We don't support MySQL 5.5 anymore and we don't consider supporting MariaDB 5.5, so I'm gonna close this one.