Converting tables to use utf8mb4 with using LoginOIDC plugin lead to problem

[DeamonMV]

Hello

We have updated our matomo 3.x installation to new version 4.0.4 - looks like it’s all going well, but update tables to use utf8mb4 have problems.

this is looks like this

Starting the database upgrade process now. This may take a while, so please be patient.

Executing ALTER TABLE `matomo_user` CONVERT TO CHARACTER SET utf8mb4;... ERROR [2020-12-09 08:58:12] 1099  Uncaught exception: /var/www/matomo/libs/Zend/Db/Adapter/Pdo/Abstract.php(280): SQLSTATE[HY000]: General error: 1833 Cannot change column 'login': used in a foreign key constraint 'matomo_loginoidc_provider_ibfk_1' of table 'matomo-db.matomo_loginoidc_provider'
SQLSTATE[HY000]: General error: 1833 Cannot change column 'login': used in a foreign key constraint 'matomo_loginoidc_provider_ibfk_1' of table 'matomo-db.matomo_loginoidc_provider'

 [Zend_Db_Adapter_Exception]
  SQLSTATE[HY000]: General error: 1833 Cannot change column 'login': used in a foreign key constraint 'matomo_loginoidc_provider_ibfk_1' of table 'matomo-db.matomo_loginoidc_provider'

 [PDOException]
  SQLSTATE[HY000]: General error: 1833 Cannot change column 'login': used in a foreign key constraint 'matomo_loginoidc_provider_ibfk_1' of table 'matomo-db.matomo_loginoidc_provider'

As I understand this because of LoginOIDC adds a foreign key constraint to the database that disallows the changes needed for the conversion.

My origin post on matomo forum.

Can someone help to resolve it. Thank you.

[markwooff]

Also ran into this one when attempting to run the /var/www/html/console core:convert-to-utf8mb4 script. Going to see if there is a way to get around the FK constraint.

[markwooff]

This is probably not the best way to get around the foreign key constraint but you can add SET FOREIGN_KEY_CHECKS = 0; to the DB upgrade queries and then set it back to SET FOREIGN_KEY_CHECKS = 1; after the DB upgrade is completed. Just tested this out using the following code snippet in the /var/www/html/plugins/CoreUpdater/Commands/ConvertToUtf8mb4.php file:

Line 87:

            $output->writeln("\n" . Piwik::translate('CoreUpdater_ConsoleStartingDbUpgrade'));
            Db::get()->exec("SET FOREIGN_KEY_CHECKS = 0;");

            foreach ($queries as $query) {
                $output->write("\n" . 'Executing ' . $query . '... ');
                Db::get()->exec($query);
                $output->write(' done.');
            }

            Db::get()->exec("SET FOREIGN_KEY_CHECKS = 1;");

[DeamonMV]

@markwooff Thank you for your workaround.

Update went well.

[dlecan]

@markwooff Thank you for your workaround.

The update went well for me too.

[dominik-th]

Should be fixed in the next version: https://github.com/dominik-th/matomo-plugin-LoginOIDC/commit/ed137deb617287532e9d5f84b8c20e6f226101cf https://developer.matomo.org/api-reference/events#dbgettablesinstalled