We're running Matomo in an environment with a rather high number of trackings. Recently we lost some until we noticed that log_action.idaction reached 2^31. In the log we saw that errror:
Error in Matomo (tracker): Error query: Mysqli statement execute error : Out of range value for column 'idaction' at row 1 In query: INSERT INTO matomo_log_action (name, hash, type, url_prefix) VALUES (?,CRC32(?),?,?)
We altered all relevant tables and switched to BIGINT by hand. But from my point of view it would be better if Matomo wouldn't require such a "hack" and use BIGINT as default.
Do you still consider it better to save storage and force users to modify Matomo's tables. Or wouldn't be better to revert https://github.com/matomo-org/matomo/pull/10569 and use BIGINT as default?
Matomo can handle that number of actions without the need to change the database schema manually.
Changes to the database schema need to be done on high-traffic sites.
Use Matomo until log_action.id reaches 2^31
Hi @stefan-work, thanks for sharing this experience, I'm sorry to hear that this limitation caused you problems and I'm glad you managed to work around it.
Database limitation are something we're currently evaluating and this sort of feedback is a valuable input to that process . :+1:
The reason for not having idaction a BIGINT was explained here https://github.com/matomo-org/matomo/issues/3288#issuecomment-249717454
Not sure if this is still something that is relevant. ping @tsteur @mattab
BIGINT keys will be required to support distributed databases, especially if using random keys. Any future support for single schema would also depend on having a large enough key space. We could have a different schema in that case, but it would be better for data migration and interoperability to standardize on BIGINT keys unless there is still a good reason not to.
I don't think preserved disc space for keys should be that much of an issue nowadays, but just wanted to get a confirmation from @tsteur or @mattab as it seems we made the decision on purpose not to use bigint there in the past...
Also changing that columns means a potential big database migration, which we imho wanted to avoid for Matomo 5. If we decide to do that nevertheless, we could also consider merging #17466 to Matomo 5, as it was moved to Matomo 6, due to the required database migration.