Investigate issues with utf8mb4 and MariaDB

[kinglozzer]

https://docs.silverstripe.org/en/4/changelogs/4.7.0/#default-mysql-collation-updated. This appears to have caused problems with installations using MariaDB. See #9811 for more discussion.

The change was accidentally made opt-out for 4.7.0, it should be opt-in for 4.7.1 once silverstripe/recipe-core#64 is merged.

---

Stack trace from an affected server

SilverStripe\ORM\Connect\DatabaseException: Couldn't run query:

SELECT DISTINCT "STMessage"."ClassName", "STMessage"."LastEdited", "STMessage"."Created", "STMessage"."Text", "STMessage"."IsAddress", "STMessage"."Encrypted", "STMessage"."RequestRead", "STMessage"."ProvideRead", "STMessage"."RequesterID", "STMessage"."ProviderID", "STMessage"."MessageGroupID", "STMessage"."ID", 
			CASE WHEN "STMessage"."ClassName" IS NOT NULL THEN "STMessage"."ClassName"
			ELSE 'Firesphere\\StickerTrade\\Models\\Message' END AS "RecordClassName"

FROM "STMessage"

WHERE ("STMessage"."IsAddress" = ?)
 AND ("STMessage"."Created" < ?)

You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '."ClassName", "STMessage"."LastEdited", "STMessage"."Created", "STMessage"."Text' at line 1
#55 /ORM/Connect/DBConnector.php(64): SilverStripe\ORM\Connect\DBConnector::databaseError
#54 /ORM/Connect/MySQLiConnector.php(300): SilverStripe\ORM\Connect\MySQLiConnector::preparedQuery
#53 /ORM/Connect/Database.php(185): SilverStripe\ORM\Connect\Database::SilverStripe\ORM\Connect\{closure}
#52 /ORM/Connect/Database.php(258): SilverStripe\ORM\Connect\Database::benchmarkQuery
#51 /ORM/Connect/Database.php(183): SilverStripe\ORM\Connect\Database::preparedQuery
#50 /ORM/Connect/MySQLDatabase.php(402): SilverStripe\ORM\Connect\MySQLDatabase::preparedQuery
#49 /ORM/DB.php(445): SilverStripe\ORM\DB::prepared_query
#48 /ORM/Queries/SQLExpression.php(115): SilverStripe\ORM\Queries\SQLExpression::execute
#47 /ORM/DataList.php(743): SilverStripe\ORM\DataList::toArray
#46 /ORM/DataList.php(871): SilverStripe\ORM\DataList::getIterator
#45 /Tasks/ClearPrivateMessages.php(29): Firesphere\StickerTrade\Jobs\ClearPrivateMessages::run
#44 /Dev/TaskRunner.php(127): SilverStripe\Dev\TaskRunner::runTask
#43 /Control/RequestHandler.php(323): SilverStripe\Control\RequestHandler::handleAction
#42 /Control/Controller.php(286): SilverStripe\Control\Controller::handleAction
#41 /Control/RequestHandler.php(202): SilverStripe\Control\RequestHandler::handleRequest
#40 /Control/Controller.php(212): SilverStripe\Control\Controller::handleRequest
#39 /Control/RequestHandler.php(226): SilverStripe\Control\RequestHandler::handleRequest
#38 /Control/Controller.php(212): SilverStripe\Control\Controller::handleRequest
#37 /Control/Director.php(360): SilverStripe\Control\Director::SilverStripe\Control\{closure}
#36 /VersionedHTTPMiddleware.php(41): SilverStripe\Versioned\VersionedHTTPMiddleware::process
#35 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#34 /Control/Middleware/ConfirmationMiddleware.php(254): SilverStripe\Control\Middleware\ConfirmationMiddleware::process
#33 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#32 /Control/Middleware/ConfirmationMiddleware.php(254): SilverStripe\Control\Middleware\ConfirmationMiddleware::process
#31 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#30 /Security/PasswordExpirationMiddleware.php(84): SilverStripe\Security\PasswordExpirationMiddleware::process
#29 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#28 /Security/BasicAuthMiddleware.php(68): SilverStripe\Security\BasicAuthMiddleware::process
#27 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#26 /Security/AuthenticationMiddleware.php(61): SilverStripe\Security\AuthenticationMiddleware::process
#25 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#24 /Control/Middleware/CanonicalURLMiddleware.php(190): SilverStripe\Control\Middleware\CanonicalURLMiddleware::process
#23 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#22 /Control/Middleware/HTTPCacheControlMiddleware.php(42): SilverStripe\Control\Middleware\HTTPCacheControlMiddleware::process
#21 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#20 /Control/Middleware/ChangeDetectionMiddleware.php(28): SilverStripe\Control\Middleware\ChangeDetectionMiddleware::process
#19 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#18 /Control/Middleware/FlushMiddleware.php(27): SilverStripe\Control\Middleware\FlushMiddleware::process
#17 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#16 /Control/RequestProcessor.php(66): SilverStripe\Control\RequestProcessor::process
#15 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#14 /Control/Middleware/SessionMiddleware.php(20): SilverStripe\Control\Middleware\SessionMiddleware::process
#13 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#12 /Control/Middleware/AllowedHostsMiddleware.php(60): SilverStripe\Control\Middleware\AllowedHostsMiddleware::process
#11 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#10 /Control/Middleware/TrustedProxyMiddleware.php(176): SilverStripe\Control\Middleware\TrustedProxyMiddleware::process
#9 /Control/Middleware/HTTPMiddlewareAware.php(62): SilverStripe\Control\Director::SilverStripe\Control\Middleware\{closure}
#8 /Control/Middleware/HTTPMiddlewareAware.php(65): SilverStripe\Control\Director::callMiddleware
#7 /Control/Director.php(369): SilverStripe\Control\Director::handleRequest
#6 /Control/HTTPApplication.php(117): SilverStripe\Control\HTTPApplication::SilverStripe\Control\{closure}
#5 [internal](0): call_user_func
#4 /Control/HTTPApplication.php(136): SilverStripe\Control\HTTPApplication::SilverStripe\Control\{closure}
#3 /Control/Middleware/HTTPMiddlewareAware.php(65): SilverStripe\Control\HTTPApplication::callMiddleware
#2 /Control/HTTPApplication.php(137): SilverStripe\Control\HTTPApplication::execute
#1 /Control/HTTPApplication.php(116): SilverStripe\Control\HTTPApplication::handle
#0 /vendor/silverstripe/framework/cli-script.php(22): null

Affected server software versions

Kernel Version | Linux vps278100 4.15.0-128-generic #131-Ubuntu SMP Wed Dec 9 06:57:35 UTC 2020 x86_64
Name | Linux
Version | 4.15.0-128-generic(#131-Ubuntu SMP Wed Dec 9 06:57:35 UTC 2020)
Name | php
Version | 7.4.13
Database | mariadb | Ver 15.1 Distrib 10.1.47-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

Related code snippets

class Clear extends BuildTask {
    public function run($HTTPRequest)
    {
        $date = date('Y-m-d 00:00:00', strtotime('-3 days'));
        /** @var DataList|Message[] $msgs */
        $msgs = Message::get()->filter(['IsAddress' => true, 'Created:LessThan' => $date]);
    }
}

    private static $table_name = 'STMessage';

    private static $db = [
        'IsAddress'   => 'Boolean',
    ];

    private static $indexes = [
        'Created'    => true,
        'LastEdited' => true,
        'Encrypted'  => true,
    ];

[emteknetnz]

@Cheddam's comment on the previous issue:

We've determined that due to the collation adjustment being introduced in a separate config file, it is currently being installed in projects during upgrades, making this an opt-out change as of Recipe 4.7.0. I've raised two PRs to address this:

• A fix to make the collation change opt-in in Recipe 4.7.1 and later: silverstripe/recipe-core#64
• A clarification in the 4.7.0 upgrade guide with instructions on how to opt out for the time being: #9821

[Cheddam]

Two separate problems were raised in the original issue:

Collation change is opt-out in 4.7.0

This will be resolved in 4.7.1, and projects using older MariaDB / MySQL versions will need to opt out manually in the meantime (and on new projects going forward.) The release notes document the most common issue that will be encountered by running the new collation against old DB versions - index size errors.

Queries break in specific circumstances

This is the challenging and as-yet-unsolved one. When running a CronTask operation, an issue was encountered where the DB would throw syntax errors on standard ORM operations. (See the stack trace in the top comment.) This syntax error suggests that the sql_mode setting is not being correctly sent to the DB, which Silverstripe relies on to enable double quotes to be used in place of backticks in queries (via the ANSI / ANSI_QUOTES mode.)

The only way we've managed to reproduce this failure is when the sql_mode configuration in the MySQLDatabase adapter is unset, which can occur when upgrading to 4.7.0 without a full flush, but should not be an issue after flushing. The original reporter has stated that they've flushed multiple times and that this appears not to be the issue.

A similar case has been documented. However, that project was running 4.4.6, and not using the utfmb4 collation in the first place. Strangely, their situation was resolved by changing their collation to latin1_swedish_ci.

At this point we've run out of leads, so we're looking for additional information from the community. If you've faced this issue, can you please provide the following information:

• The full error and stack trace
• silverstripe/recipe-core or silverstripe/framework version
• Where you encountered the issue (CLI command or web request)
• Code sample (related DataObject and the code that the error was thrown in)
• The output of var_dump(Config::inst()->get(MySQLDatabase::class, 'sql_mode'); above the breaking line
• Whether changing the collation settings fixes the issue, and whether changing back reintroduces it

Thanks!

[Firesphere]

On MariaDB 10.x, indexes and uniques break, because MB4 takes up more bits, so 255 varchar's can't be proper indexes anymore (this is partially a bug in MariaDB not using the long indexes properly, I think)

[kinglozzer]

No further reports of any issues after upgrading, closing