Possibly we should be updating to MEDIUMINT in this case

Not sure how we will handle schema changes but I suggest to fix it definitely in MySql Schema for new installs. For existing installs not sure if we add an FAQ how to fix it or an update? > 32k actions in one visit are not usual I reckon.

Hi,

I did INT because I was a bit in a hurry. So MEDIUMINT could be fine. They ware smallint, and that is not big enough for us.
I have to add that this was an upgrade from the latest 2.X stable version. So there was already data there.

32K actions is for us some kind of normal. We measure requests on our system, and we have customers that do a request every 2 second from the same IP. And that 24/7 a day. Those are automated systems. And because of the 2 second time between every hit, Piwik will see this as one visit of 'unlimited' time? If that is the case, you could say that we have a 'special' case and that this is something we have to know and could be explained in a FAQ.

Kind regards
Joshua

have the same issue as well (on a 2.x install)

@hans-d this should not be the same issue as this column should not exist in Piwik 2.x. Can you create a new issue with your error message?

Fixed in #11086 for new installations. If you experience this issue in your existing install, please make the two db fields MEDIUMINT manually.

@theyosh thanks for the report. since you have a huge installation of Piwik, it would be great if you could help us the beta/ RC versions and help us prevent such issues 👍

@mattab I have now a test version running. Not sure if I understand your fixes correct, but beta 3.0.1b1 does not do any MySQL updates. So this change to mediumint is not in the update of 3.0.1?

@theyosh we merged only this change: #11101 which should prevent the error by keep the value to 32765

The schema change in #11086 will be applied in Piwik 4 (since we try not to make schema changes within a major version)

Wonder if we could one day find a better solution. Now the problem is that if users do need a higher limit, they cannot simply alter the DB because it would be limited in the code. One day ideally the platform would read the fields definition and limit it to highest possible value or so automatically. This can be also good when changing the field to a lower int to save storage etc. It is a rather edge case right now since we do not want to update the DB field but be cool if users could one day just change the fields definition and it would work. Now any prio probably though

Just installed fresh 3.0.4.
Getting same issue. I though, it should be fixed already by PR #11101.
But apparently it is not.
Issue happened while running import_logs.py script.
Should this issue be reopened? Should I file new one?

Here is my nginx error.log entry:

2017/07/06 20:24:58 [error] 1202#1202: *11919 FastCGI sent in stderr: "PHP message: Error in Piwik (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'visit_total_interactions' at row 1 In query: UPDATE piwik_log_visit SET idvisitor = ?, visit_last_action_time = ?, visit_exit_idaction_url = ?, visit_total_actions = visit_total_actions + 1 , visit_total_interactions = visit_total_interactions + 1 , visit_total_time = ? WHERE idsite = ? AND idvisit = ? Parameters: array ( 0 => 'M▒D0E▒/', 1 => '2016-06-08 00:49:07', 2 => 15890, 3 => 17278, 4 => 2, 5 => 80480, )" while reading response header from upstream, client: ::1, server: _, request: "POST //piwik.php HTTP/1.1", upstream: "fastcgi://unix:/run/php/php7.0-fpm.sock:", host: "localhost:5000"

This issue is still present in matomo 3.11.0:

[Wed Aug 21 15:48:20.769726 2019] [php7:notice] [pid 15320] [client x.x.x.x:43062] Error in Matomo (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'interaction_position' at row 1 In query: INSERT INTO piwik_log_link_visit_action (idvisit, idsite, idvisitor, idaction_url, idaction_url_ref, idaction_name_ref, server_time, idpageview, interaction_position, time_spent_ref_action, custom_var_k1, custom_var_v1) VALUES (?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '22832045', 1 => 26, 2 => '\xb3w\x18\x80\xb4=$\xf9', 3 => '31834007', 4 => '31823502', 5 => '31805091', 6 => '2019-08-21 04:22:21', 7 => '', 8 => 71040, 9 => 1, 10 => 'HTTP-code', 11 => '200', )

The fix seems to be incomplete as interaction_position defaults to getCurrentInteractionPosition here: plugins/Actions/Columns/InteractionPosition.php

I've set a cap to that and managed to get over this issue:
`
public static function getCurrentInteractionPosition($request)
{
$position = $request->getMetadata('Actions', 'visit_total_interactions');

    return (int) min($position, 65530);
}

`
Similar to what's been done with getNextInteractionPosition

I think the best fix would be changing the column type to MEDIUMINT, but this should work until we manage to do the change, piwik_log_link_visit_action table on our setup is over 110Gb.

This change is already scheduled for Matomo 4 👍 #11086