I've moved our Matomo installation to DigitalOcean Managed Database Cluster MySQL 8. Since I've moved it there some metrics are not accessible and I see the following error:
Mysqli statement execute error : Unable to create or change a table without a primary key, when the system variable 'sql_require_primary_key' is set. Add a primary key to the table or unset this variable to avoid this message. Note that tables without a primary key can cause performance problems in row-based replication, so please consult your DBA before changing this setting.
And I can see this error in the cluster logs:
[Server] Attempt to create or modify table without primary key: xyz_matomo.matomo_logtmpsegmentceXXXXXXXXXXXXXXXXXXXX
I think I can't change the sql_require_primary_key because the Database is managed and I do not have access to the conf.
But how can this problem fixed elsewhere or is Matomo generally not compatible with DigitalOcean's Managed Database Cluster (MySQL 8).
I've also found this in the known issues list:
MySQL databases containing tables without a primary key and which contain more than 5000 rows may experience replication issues. To prevent this, DigitalOcean now requires you to add a primary key for each new table you create in any managed MySQL database created after 8 April 2020. We strongly recommend that you also [add primary keys][/docs/databases/mysql/how-to/create-primary-keys/] in existing databases to avoid replication issues.