SQL Error after Matomo was upgraded to v5.0
My Matomo installation was automatically upgraded to v5.0.0 and as a result a database upgrade was needed upon first access.
When I proceeded with the upgrade step, I got an Update error after several seconds:
Critical Error during the update process: /app/code/core/Updates/5.0.0-b1.php: Error trying to execute the migration 'ALTER TABLE `log_visit` ADD INDEX `index_idsite_idvisitor_time` (`idsite`, `idvisitor`, `visit_last_action_time` DESC);'. The error was: SQLSTATE: Syntax error or access violation: 1178 The storage engine for the table doesn't support descending indexes
Googling for the exact error didn't return anything useful, but by broadening my search I was able to narrow it down to a query incompatibility with the MyISAM engine, more specifically the "DESC" clause isn't supported.
So to solve the issue, I opened the MySQL cli and manually performed the query without the DESC modifier, like this:
ALTER TABLE `log_visit` ADD INDEX `index_idsite_idvisitor_time` (`idsite`, `idvisitor`, `visit_last_action_time`);
Then I restarted the upgrade procedure from the web interface and everything finished successfully.
I'm not sure if this is something that should be fixed in the Cloudron package, e.g. by upgrading to a newer MySQL version and/or switching to the InnoDB engine?
In any case, this information might be valuable for others as well.
Edit: fixed typo
@girish since I recently had another issue as well, I was just thinking that maybe all this is related to the fact that I initially migrated from an older Piwik installation.
With that, I mean that after I installed the Cloudron app about 2 years ago, I imported existing data from that older Piwik installation. This might explain why I'm faced with those issues during upgrades while others aren't