magento2: Magento's `setup:db:status` fails with MySQL 8.0.29

Preconditions and environment

• Magento 2.4.4
• MySQL 8.0.29 and higher — Server version: 8.0.29-0ubuntu0.20.04.3 (Ubuntu)

Steps to reproduce

1. Set up Magento 2.4.4 following the requirements (https://devdocs.magento.com/guides/v2.4/install-gde/system-requirements.html)
2. Execute setup:upgrade
3. Execute setup:db:status

Expected result

Magento should report that the Database is up-to-date

Actual result

1. bin/magento setup:db:status

Declarative Schema is not up to date Run ‘setup:upgrade’ to update your DB schema and data.

1. bin/magento setup:upgrade
2. bin/magento setup:db:status

Declarative Schema is not up to date Run ‘setup:upgrade’ to update your DB schema and data.

1. bin/magento setup:db-schema:upgrade
2. bin/magento setup:db:status

Declarative Schema is not up to date Run ‘setup:upgrade’ to update your DB schema and data.

Additional information

Issue is caused by charset comparison.

Beginning with MySQL 8.0.28, utf8mb3 is also displayed in place of utf8 in columns of Information Schema tables, and in the output of SQL SHOW statements.

Prior to MySQL 8.0.29, instances of utf8mb3 in statements were converted to utf8. In MySQL 8.0.30 and later, the reverse is true, so that in statements such as SHOW CREATE TABLE or SELECT CHARACTER_SET_NAME FROM INFORMATION_SCHEMA.COLUMNS or SELECT COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS, users see the character set or collation name prefixed with utf8mb3 or utf8mb3_.

The default table charset is defined in \Magento\Framework\Setup\Declaration\Schema\Dto\Factories\Table::create

Our internal workaround is a plugin which overrides default MySQL charset if no other provided:

<?php
declare(strict_types=1);

namespace SwiftOtter\MysqlCharsetFix\Plugin;

use Magento\Framework\Setup\Declaration\Schema\Dto\Factories\Table;

class OverrideDefaultCharsetPlugin
{
    private const DEFAULT_CHARSET = 'utf8mb3';

    /**
     * @param Table $subject
     * @param array $data
     * @return array
     */
    public function beforeCreate(Table $subject, array $data): array
    {
        if (!isset($data['charset'])) {
            $data['charset'] = self::DEFAULT_CHARSET;
        }

        return [$data];
    }
}

Another possible solution is to override \Magento\Framework\Setup\Declaration\Schema\Dto\Table::getDiffSensitiveParams parameters, excluding charset from the list. (Composer patch below)

diff --git a/Setup/Declaration/Schema/Dto/Table.php b/Setup/Declaration/Schema/Dto/Table.php
--- a/Setup/Declaration/Schema/Dto/Table.php
+++ b/Setup/Declaration/Schema/Dto/Table.php	(date 1655976014788)
@@ -343,7 +343,6 @@
             'resource' => $this->getResource(),
             'engine' => $this->getEngine(),
             'comment' => $this->getComment(),
-            'charset' => $this->getCharset(),
             'collation' => $this->getCollation()
         ];
     }

Huge thanks for helping with the investigation to @LeeSaferite, @navarr, and @so-max-u

Release note

No response

Triage and priority

• Severity: S0 - Affects critical data or functionality and leaves users without workaround.
• Severity: S1 - Affects critical data or functionality and forces users to employ a workaround.
• Severity: S2 - Affects non-critical data or functionality and forces users to employ a workaround.
• Severity: S3 - Affects non-critical data or functionality and does not force users to employ a workaround.
• Severity: S4 - Affects aesthetics, professional look and feel, “quality” or “usability”.