@theyosh opened this Issue on December 21st 2016

Hi,

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:

piwik_log_visit.visit_total_interactions
piwik_log_link_visit_action.interaction_position

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[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, 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: 82.150.152.12, 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

@tsteur commented on December 21st 2016 Owner

Possibly we should be updating to MEDIUMINT in this case

@tsteur commented on December 21st 2016 Owner

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.

@theyosh commented on December 21st 2016

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

@hans-d commented on December 24th 2016

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

@mattab commented on December 26th 2016 Owner

@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?

@mattab commented on December 26th 2016 Owner

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:

@theyosh commented on December 28th 2016

@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?

@mattab commented on December 30th 2016 Owner

@theyosh we merged only this change: https://github.com/piwik/piwik/issues/11101 which should prevent the error by keep the value to 32765

The schema change in https://github.com/piwik/piwik/pull/11086 will be applied in Piwik 4 (since we try not to make schema changes within a major version)

@tsteur commented on December 31st 2016 Owner

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

@SlavikCA commented on July 7th 2017

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<a href='/1202'>#1202</a>: *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 was closed on December 26th 2016
Powered by GitHub Issue Mirror