Matomo: Numeric value out of range: 1264 Out of range value for column 'visit_total_interactions'

[pengshuai2010]

This is similar to the issue #9565 .
I'm using version 3.04, and I get the following error when importing logs with import_logs.py.
2017/05/19 22:01:37 [error] 7#7: *25796 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 = ?, user_id = ?, 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 => '���\\����', 1 => '54.85.240.164', 2 => '2017-05-15 03:41:37', 3 => 1662, 4 => 63036, 5 => 1, 6 => 4, )" while reading response header from upstream, client: 10.110.52.171, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://172.18.0.3:9000", host: "speng.mm-corp.net:5002"
This can be fixed by changing https://github.com/piwik/piwik/blob/3.x-dev/plugins/Actions/Columns/VisitTotalInteractions.php#L20 to protected $columnType = 'INT(11) UNSIGNED DEFAULT 0';
Another field visit_total_searches may also need to be changed.

[mattab]

This will be fixed in Piwik 4 by https://github.com/piwik/piwik/pull/11086

[SlavikCA]

@pengshuai2010 I fixed this issue for myself in 3.0.4 by running this SQL

ALTER TABLE `piwik_log_visit` CHANGE `visit_total_interactions` `visit_total_interactions` MEDIUMINT(5) UNSIGNED NULL DEFAULT '0';

https://dev.mysql.com/doc/refman/5.7/en/integer-types.html

[kaplun]

Just stumbled over this in version 3.5.0. Thanks @SlavikCA for the fix!