Hi @yellowdog, if you upgraded to MariaDb 11.5 before the fix, it is possible that some tables were created with a different collation. You should be able to manually fix the tables by following the steps in the FAQ at https://matomo.org/faq/troubleshooting/how-do-i-resolve-the-error-illegal-mix-of-collations-for-operation/, have you tried that? Based on the error, it also looks like your default collation should be set to utf8mb4_general_ci in the config, not the one MariaDb 11.5 comes with as the default.
1 Like
Thanks for the info, Michal; I’ll get that done and report back.
I updated Matomo to the latest version and I altered the table’s collations by following the steps mentioned in the faq: https://matomo.org/faq/troubleshooting/how-do-i-resolve-the-error-illegal-mix-of-collations-for-operation/
As you can see here, all tables have the collation `utf8mb4_general_ci`
MariaDB [(none)]> SELECT TABLE_NAME, TABLE_COLLATION FROM information_schema.TABLES WHERE TABLE_SCHEMA = 'matomo';
+---------------------------------------+--------------------+
| TABLE_NAME | TABLE_COLLATION |
+---------------------------------------+--------------------+
| matomo_changes | utf8mb4_general_ci |
| matomo_archive_numeric_2015_01 | utf8mb4_general_ci |
| matomo_archive_blob_2021_03 | utf8mb4_general_ci |
| matomo_archive_blob_2021_10 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_07 | utf8mb4_general_ci |
| matomo_archive_blob_2016_01 | utf8mb4_general_ci |
| matomo_archive_blob_2023_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2014_01 | utf8mb4_general_ci |
| matomo_archive_blob_2017_12 | utf8mb4_general_ci |
| matomo_logger_message | utf8mb4_general_ci |
| matomo_archive_numeric_2020_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_07 | utf8mb4_general_ci |
| matomo_archive_blob_2024_05 | utf8mb4_general_ci |
| matomo_archive_blob_2023_11 | utf8mb4_general_ci |
| matomo_archive_blob_2024_06 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_08 | utf8mb4_general_ci |
| matomo_archive_blob_2020_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_10 | utf8mb4_general_ci |
| matomo_archive_blob_2021_02 | utf8mb4_general_ci |
| matomo_archive_blob_2024_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_10 | utf8mb4_general_ci |
| matomo_archive_blob_2019_07 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_05 | utf8mb4_general_ci |
| matomo_archive_blob_2019_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_10 | utf8mb4_general_ci |
| matomo_archive_blob_2018_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_09 | utf8mb4_general_ci |
| matomo_goal | utf8mb4_general_ci |
| matomo_archive_blob_2023_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_07 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_03 | utf8mb4_general_ci |
| matomo_archive_blob_2018_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_06 | utf8mb4_general_ci |
| matomo_archive_blob_2022_10 | utf8mb4_general_ci |
| matomo_report_subscriptions | utf8mb4_general_ci |
| matomo_archive_blob_2008_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_06 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_08 | utf8mb4_general_ci |
| matomo_archive_blob_2022_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_03 | utf8mb4_general_ci |
| matomo_archive_blob_2017_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_07 | utf8mb4_general_ci |
| matomo_archive_blob_2019_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_09 | utf8mb4_general_ci |
| matomo_site_setting | utf8mb4_general_ci |
| matomo_archive_blob_2022_08 | utf8mb4_general_ci |
| matomo_archive_blob_2011_01 | utf8mb4_general_ci |
| matomo_option | utf8mb4_general_ci |
| matomo_report | utf8mb4_general_ci |
| matomo_archive_numeric_2024_10 | utf8mb4_general_ci |
| matomo_archive_numeric_2013_01 | utf8mb4_general_ci |
| matomo_user | utf8mb4_general_ci |
| matomo_archive_numeric_2022_12 | utf8mb4_general_ci |
| matomo_archive_blob_2018_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2011_01 | utf8mb4_general_ci |
| matomo_segment | utf8mb4_general_ci |
| matomo_archive_blob_2020_03 | utf8mb4_general_ci |
| matomo_archive_blob_2021_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_06 | utf8mb4_general_ci |
| matomo_archive_blob_2023_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_11 | utf8mb4_general_ci |
| matomo_archive_blob_2022_03 | utf8mb4_general_ci |
| matomo_archive_blob_2024_10 | utf8mb4_general_ci |
| matomo_archive_blob_2018_01 | utf8mb4_general_ci |
| matomo_tracking_failure | utf8mb4_general_ci |
| matomo_archive_blob_2020_02 | utf8mb4_general_ci |
| matomo_archive_blob_2023_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_02 | utf8mb4_general_ci |
| matomo_archive_blob_2024_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_09 | utf8mb4_general_ci |
| matomo_archive_blob_2024_07 | utf8mb4_general_ci |
| matomo_archive_blob_2020_06 | utf8mb4_general_ci |
| matomo_archive_blob_2022_07 | utf8mb4_general_ci |
| matomo_sequence | utf8mb4_general_ci |
| matomo_archive_blob_2017_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_12 | utf8mb4_general_ci |
| matomo_archive_blob_2018_11 | utf8mb4_general_ci |
| matomo_archive_blob_2020_05 | utf8mb4_general_ci |
| matomo_archive_blob_2022_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_11 | utf8mb4_general_ci |
| matomo_archive_blob_2014_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_08 | utf8mb4_general_ci |
| matomo_session | utf8mb4_general_ci |
| matomo_archive_blob_2024_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_01 | utf8mb4_general_ci |
| matomo_archive_blob_2017_10 | utf8mb4_general_ci |
| matomo_archive_blob_2019_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_10 | utf8mb4_general_ci |
| matomo_access | utf8mb4_general_ci |
| matomo_archive_blob_2023_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_10 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_01 | utf8mb4_general_ci |
| matomo_archive_blob_2019_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_05 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_07 | utf8mb4_general_ci |
| matomo_log_profiling | utf8mb4_general_ci |
| matomo_archive_numeric_2018_06 | utf8mb4_general_ci |
| matomo_archive_blob_2020_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_09 | utf8mb4_general_ci |
| matomo_archive_blob_2019_06 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_03 | utf8mb4_general_ci |
| matomo_archive_blob_2020_11 | utf8mb4_general_ci |
| matomo_user_language | utf8mb4_general_ci |
| matomo_archive_numeric_2019_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_06 | utf8mb4_general_ci |
| matomo_plugin_setting | utf8mb4_general_ci |
| matomo_archive_blob_2021_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_09 | utf8mb4_general_ci |
| matomo_archive_blob_2019_10 | utf8mb4_general_ci |
| matomo_custom_dimensions | utf8mb4_general_ci |
| matomo_archive_numeric_2021_07 | utf8mb4_general_ci |
| matomo_archive_blob_2019_03 | utf8mb4_general_ci |
| matomo_archive_blob_2022_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2017_08 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2016_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_12 | utf8mb4_general_ci |
| matomo_log_visit | utf8mb4_general_ci |
| matomo_archive_numeric_2022_06 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_04 | utf8mb4_general_ci |
| matomo_archive_blob_2018_04 | utf8mb4_general_ci |
| matomo_archive_numeric_2010_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_07 | utf8mb4_general_ci |
| matomo_archive_blob_2023_06 | utf8mb4_general_ci |
| matomo_archive_blob_2020_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_12 | utf8mb4_general_ci |
| matomo_archive_blob_2021_07 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_02 | utf8mb4_general_ci |
| matomo_archive_blob_2021_06 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_03 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2008_12 | utf8mb4_general_ci |
| matomo_archive_blob_2024_04 | utf8mb4_general_ci |
| matomo_archive_blob_2023_07 | utf8mb4_general_ci |
| matomo_archive_blob_2023_12 | utf8mb4_general_ci |
| matomo_archive_blob_2023_08 | utf8mb4_general_ci |
| matomo_log_conversion_item | utf8mb4_general_ci |
| matomo_user_token_auth | utf8mb4_general_ci |
| matomo_archive_blob_2017_08 | utf8mb4_general_ci |
| matomo_archive_blob_2019_09 | utf8mb4_general_ci |
| matomo_archive_blob_2018_12 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_10 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_04 | utf8mb4_general_ci |
| matomo_archive_blob_2019_08 | utf8mb4_general_ci |
| matomo_archive_blob_2022_06 | utf8mb4_general_ci |
| matomo_archive_invalidations | utf8mb4_general_ci |
| matomo_archive_numeric_2024_09 | utf8mb4_general_ci |
| matomo_archive_blob_2021_09 | utf8mb4_general_ci |
| matomo_archive_blob_2018_10 | utf8mb4_general_ci |
| matomo_site | utf8mb4_general_ci |
| matomo_twofactor_recovery_code | utf8mb4_general_ci |
| matomo_archive_numeric_2022_08 | utf8mb4_general_ci |
| matomo_archive_blob_2015_01 | utf8mb4_general_ci |
| matomo_archive_blob_2024_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_01 | utf8mb4_general_ci |
| matomo_locks | utf8mb4_general_ci |
| matomo_archive_blob_2021_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2009_01 | utf8mb4_general_ci |
| matomo_archive_blob_2020_07 | utf8mb4_general_ci |
| matomo_archive_blob_2018_05 | utf8mb4_general_ci |
| matomo_archive_blob_2021_04 | utf8mb4_general_ci |
| matomo_archive_blob_2022_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2024_01 | utf8mb4_general_ci |
| matomo_archive_blob_2019_02 | utf8mb4_general_ci |
| matomo_archive_numeric_2022_11 | utf8mb4_general_ci |
| matomo_archive_numeric_2021_11 | utf8mb4_general_ci |
| matomo_archive_blob_2019_04 | utf8mb4_general_ci |
| matomo_archive_blob_2009_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_05 | utf8mb4_general_ci |
| matomo_archive_blob_2018_07 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_07 | utf8mb4_general_ci |
| matomo_log_action | utf8mb4_general_ci |
| matomo_archive_blob_2020_12 | utf8mb4_general_ci |
| matomo_archive_blob_2017_07 | utf8mb4_general_ci |
| matomo_archive_blob_2022_05 | utf8mb4_general_ci |
| matomo_log_link_visit_action | utf8mb4_general_ci |
| matomo_archive_numeric_2012_01 | utf8mb4_general_ci |
| matomo_site_url | utf8mb4_general_ci |
| matomo_archive_blob_2022_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2019_04 | utf8mb4_general_ci |
| matomo_archive_blob_2013_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_06 | utf8mb4_general_ci |
| matomo_archive_blob_2023_10 | utf8mb4_general_ci |
| matomo_archive_blob_2023_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2020_03 | utf8mb4_general_ci |
| matomo_user_dashboard | utf8mb4_general_ci |
| matomo_archive_numeric_2020_11 | utf8mb4_general_ci |
| matomo_brute_force_log | utf8mb4_general_ci |
| matomo_privacy_logdata_anonymizations | utf8mb4_general_ci |
| matomo_archive_blob_2020_10 | utf8mb4_general_ci |
| matomo_archive_blob_2021_05 | utf8mb4_general_ci |
| matomo_archive_blob_2020_09 | utf8mb4_general_ci |
| matomo_archive_numeric_2018_10 | utf8mb4_general_ci |
| matomo_archive_blob_2010_01 | utf8mb4_general_ci |
| matomo_archive_numeric_2023_08 | utf8mb4_general_ci |
| matomo_archive_blob_2018_06 | utf8mb4_general_ci |
| matomo_archive_blob_2022_02 | utf8mb4_general_ci |
| matomo_archive_blob_2017_01 | utf8mb4_general_ci |
| matomo_archive_blob_2012_01 | utf8mb4_general_ci |
| matomo_archive_blob_2018_09 | utf8mb4_general_ci |
| matomo_archive_blob_2021_01 | utf8mb4_general_ci |
| matomo_log_conversion | utf8mb4_general_ci |
| matomo_archive_blob_2024_08 | utf8mb4_general_ci |
+---------------------------------------+--------------------+
227 rows in set (0.174 sec)
Nevertheless I still have the same errors in my dashboard:
In the system-check I see the warning:
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_uca1400_ai_ci
The default collation for your configured charset is:utf8mb4_uca1400_ai_ci
Which is interesting, because I configured the collation in my config.ini.php:
; <?php exit; ?> DO NOT REMOVE THIS LINE
; file automatically generated or modified by Matomo; you can manually override the default values in global.ini.php by redefining them in this file.
[database]
host = "xxx.xxx.xx.xx"
username = "matomo"
password = "xxxxxxxxxxxxxx"
dbname = "matomo"
tables_prefix = "matomo_"
charset = "utf8mb4"
collation = "utf8mb4_general_ci"
schema = Mariadb
[General]
force_ssl = 1
assume_secure_protocol = 1
...
Any idea what the problem might be?
Maybe @innocraft have some clues on this topic?
Nevermind, I was in the wrong config. Everything seems to work now.