I have a replication setup running which gets stuck on a master log sequence of deleting rows from the heatmaps table “log_hsr_event”. I’m not 100% sure if that’s the only table that is affected. This table has no PRIMARY key. I deleted a heatmap that leads to a lot of deletes in this (and probably other heatmap) table(s).
With Binlog-Format ROW or (probably also) MIXED, deletes from a table without PRIMARY key leads to excessive table scans on every deletion, so deleting a single row takes more than 10 minutes which stalls the replication (Seconds_Behind_Master: 866279).
Would it be possible to introduce one for the tables without a primary key?
Hi there. While new installs have a primary key there automatically, if you installed an older version of heatmaps the primary key would be missing and would be automatically installed with the Matomo 4 update.
This is because we don’t include major SQL changes in minor updates. To fix this, you could add this manually like this
ALTER TABLE log_hsr_event add column `idhsrevent` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY
Hi Thomas! Thanks for the info & that makes total sense not to introduce major SQL schema changes, at least when not absolutely necessary.
I’ll have to reset replication after the change for testing, but I’m quite sure that this was the root cause. I’ll report back as soon as replication is up&running again.
