Hi everyone!
I recently upgraded our dev Matomo instance from v4.15.1 to v5.3.2. I had some issues while upgrading but found comments on the forums about how to solve them. The problem is that I’m now finding a warning in the database abilities section in the platform check. The warning message I see is:
You database connection is configured without an explicit collation. Please update
[database] collation = ''in the “config/config.ini.php” file with the collation to be used, to ensure all database features work as expected.
Your currently used connection collation is:
utf8mb4_0900_ai_ci
The default collation for your configured charset is:utf8mb4_0900_ai_ci
I guess I need to add in the config file at the database section a line with collation = ‘utf8mb4_0900_ai_ci’. I found as well this topic in the docs about fixing the collation error in the UI (I don’t see that in dev, maybe in prod…) https://matomo.org/faq/troubleshooting/how-do-i-resolve-the-error-illegal-mix-of-collations-for-operation/.
My question is, which collate should I use? Should I convert the tables not having that collate? If I understood the post properly only archive tables should be changed.
SELECT count(), TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = ‘piwik’ AND TABLE_NAME LIKE ‘matomo_archive%’ GROUP BY TABLE_COLLATION;
±---------±-------------------+
| count() | TABLE_COLLATION |
±---------±-------------------+
| 53 | utf8mb4_0900_ai_ci |
| 58 | utf8mb4_general_ci |
±---------±-------------------+
This is a confusing topic, especially as this should be handled by the app itself, not the users.
Thank you very much!!