just updated to Piwik version 3.0 and again there are fields in the database that are to small. Please consider using INT for the fields:
We get the following errors during importing logfiles
2016/12/21 10:34:45 [error] 13701<a href='/13701'>#13701</a>: *1111 FastCGI sent in stderr: "PHP message: Error in Piwik (tracker): Error query: SQLSTATE: 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, bandwidth, idaction_name, custom_float, custom_var_k1, custom_var_v1) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?) Parameters: array ( 0 => '82761048', 1 => 2, 2 => 'njŨ ��e', 3 => '27820096', 4 => '27820096', 5 => '0', 6 => '2016-12-20 09:04:23', 7 => '', 8 => 65536, 9 => 0, 10 => 553, 11 => 0, 12 => '20', 13 => 'HTTP-code', 14 => '200', )" while reading response header from upstream, client: 18.104.22.168, server: stats.streamzilla.xlcdn.com, request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.piwik.sock:", host: "stats.streamzilla.xlcdn.com" 2
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.
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.
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 :+1:
@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?
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
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<a href='/1202'>#1202</a>: *11919 FastCGI sent in stderr: "PHP message: Error in Piwik (tracker): Error query: SQLSTATE: 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: 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 👍 https://github.com/matomo-org/matomo/pull/11086