Log_link_visit_action.idlink_va int field limit reached

Hello all.

We recently become to a “dead end” as our log_link_visit_action idlink_va int field has reached its maximum capacity of 4294967295, as available in MySQL field types maximum lenght: MySQL :: MySQL 5.6 Reference Manual :: 11.2.1 Integer Types (Exact Value) - INTEGER, INT, SMALLINT, TINYINT, MEDIUMINT, BIGINT

The error as shown in console:

Error in Piwik (tracker): Error query: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘4294967295’ for key ‘PRIMARY’ In query: INSERT INTO log_link_visit_action (idvisit, idsite, idvisitor, idaction_url, idaction_url_ref, idaction_name_ref, server_time, time_spent_ref_action, idaction_name) VALUES (?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => ‘836293874’, 1 => 5, 2 => ‘\t\xb72\x86:’ . “\0” . ‘\xec\xe9’, 3 => 0, 4 => ‘0’, 5 => ‘17861139’, 6 => ‘2016-01-20 17:24:15’, 7 => 14, 8 => ‘11807543’, )

We are in the proccess of changing this field type to bigint unsigned and, in a near future, change too the log_visit idvisit from int to bigint unsigned too as these are the most used tables.

I think this must be a clear transparent change, I’m I right? Do you have any suggestion on similar int fields to change to bigint unsigned?

It must be in a near future release change?

Thank you,

Pedro Estevão

1 Like

We are planning to change field types in Piwik 3.0
See Integer overflow: how to track more than 2 billion actions with PiwiK? · Issue #3288 · matomo-org/piwik · GitHub

I’ve search for it but haven’t found, sorry.

Glad that this should be solved on a future release! :sweat_smile:

Have followed all that is said in the github ticket namely the change in createTempTableForStoringUsedActions function. Is anything more that we should be aware of?

Thanks, Pedro

@pestevao could you post your feedback on the issue if you have anything to add? thanks!

Ok @matthieu I will if see or suggest anything more.
Right now with the feedback included in the issue is everything working as smooth as before. :slightly_smiling:

Hi, just want to add a ‘me too’ to this bug. Not able to import stats. My Fix for now:

alter table piwik_log_link_visit_action change idlink_va idlink_va bigint unsigned not null auto_increment;

This is fixed in Piwik 3