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.
Hi there, this table is a temporary table with only one column and has on purpose no primary key as it would be only causing an overhead. I know you mentioned you can’t change it but be good to double check this with DigitalOcean directly or search for changing parameters/variables if there is any chance to change it? Eg on AWS MySQL Aurora which is managed as well you can still change parameters/variables.
I’ve already searched for a solution to change this setting, but can’t find one. I’m waiting for a reply by DigitalOcean and will post the answer here.
okay, I’ve found a solution. But this require a Code change in Matomo and I’m not sure if this is correct or if there is a better place for this command
I’ve changed the LogAggregator.php (added line is highlighted):
This works generally fine for websites with low traffic (only a few persons per day) but I receive an error for a lot of other websites, looks like this:
