Conversion to UTF8MB4 error

Support & Bugs
1

Hi!

We are currently running matomo on-premise v4.15.1 using AWS Aurora MySQL v2.x (mysql 5.7). This instance was upgraded from a previous v3.x so charset is still set to latin. We want to upgrade to the latest version, and also upgrading MySQL to 8.0. Also, taking the chance to convert database to UTF8MB4 as per the diagnostic check.

When running the “console core:convert-to-utf8mb4” command it has failed due to error below. It seems we have a constraint preventing it to upgrade. Looking at the table, I guess a long time ago login oidc plugin may have been deployed as the table is related to that. I see a similar topic, “matomo-4-0-4-problem-with-converting-tables-to-utf8mb4/39762”, but I’m a little bit confused about the solution proposed. It’s interesting because I have checked the reported table, matomo_loginoidc_provider, and it is empty (We are not using that plugin, maybe it was used/tested a long time ago). But as reported in the error the table has a constraint. When altering the matomo_user table it cannot continue.

In the plugin list “login oidc” is not installed (I can see it available in the marketplace).

Any idea how to properly fix this? Based on the solution mentioned in the previous post I was thinking about running the command that is failing manually. So I thought about:

  1. SET FOREIGN_KEY_CHECKS = 0;
  2. ALTER TABLE matomo_user CONVERT TO CHARACTER SET utf8mb4;
  3. SET FOREIGN_KEY_CHECKS = 1;
  4. Run console core:convert-to-utf8mb4 for the rest of the tables

Not sure if the constraint may be removed otherwise to prevent this issue in the future, or completely remove the plugin. My concern is that next time we have a similar task it may fail again due to this constraint.

Table:


+---------------------------+-------------+----------------------------------+-----------------------+------------------------+
| TABLE_NAME                | COLUMN_NAME | CONSTRAINT_NAME                  | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME |
+---------------------------+-------------+----------------------------------+-----------------------+------------------------+
| matomo_loginoidc_provider | user        | matomo_loginoidc_provider_ibfk_1 | matomo_user           | login                  |
+---------------------------+-------------+----------------------------------+-----------------------+------------------------+

Error:

Executing ALTER TABLE `matomo_user` CONVERT TO CHARACTER SET utf8mb4;... 
Enabling Matomo Tracking
ERROR [2024-10-09 07:57:10] 505462  Uncaught exception: /var/www/html/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 'piwik.matomo_loginoidc_provider' [Query: , CLI mode: 1]
                                                                                                                                                                                  
  [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 'piwik.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 'piwik.matomo_loginoidc_provider'

Thank you!!