@itcrowdsource opened this Issue on December 12th 2018

After upgrading to the latest Matomo version 3.7.0 I'm unable to import data anymore. The import script throws the following error message: "Error when connecting to Matomo: HTTP Error 500: Internal Server Error" while importing data.

python /.../.../import_logs.py --log-format-name=shoutcast --url=http://x.x.x.x --idsite=1 /.../.../sc_w3c.log
0 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
Parsing log /.../.../sc_w3c.log...
857 lines parsed, 0 lines recorded, 0 records/sec (avg), 0 records/sec (current)
1066 lines parsed, 200 lines recorded, 99 records/sec (avg), 200 records/sec (current)
1278 lines parsed, 400 lines recorded, 133 records/sec (avg), 200 records/sec (current)
1488 lines parsed, 600 lines recorded, 149 records/sec (avg), 200 records/sec (current)
1488 lines parsed, 600 lines recorded, 119 records/sec (avg), 0 records/sec (current)
1699 lines parsed, 800 lines recorded, 133 records/sec (avg), 200 records/sec (current)
1915 lines parsed, 1000 lines recorded, 142 records/sec (avg), 200 records/sec (current)
1915 lines parsed, 1000 lines recorded, 124 records/sec (avg), 0 records/sec (current)
2132 lines parsed, 1200 lines recorded, 133 records/sec (avg), 200 records/sec (current)
2346 lines parsed, 1400 lines recorded, 139 records/sec (avg), 200 records/sec (current)
2346 lines parsed, 1400 lines recorded, 127 records/sec (avg), 0 records/sec (current)
2559 lines parsed, 1600 lines recorded, 133 records/sec (avg), 200 records/sec (current)
2775 lines parsed, 1800 lines recorded, 138 records/sec (avg), 200 records/sec (current)
2986 lines parsed, 2000 lines recorded, 142 records/sec (avg), 200 records/sec (current)
2986 lines parsed, 2000 lines recorded, 133 records/sec (avg), 0 records/sec (current)
3195 lines parsed, 2200 lines recorded, 137 records/sec (avg), 200 records/sec (current)
3195 lines parsed, 2200 lines recorded, 129 records/sec (avg), 0 records/sec (current)
3405 lines parsed, 2400 lines recorded, 133 records/sec (avg), 200 records/sec (current)
3611 lines parsed, 2600 lines recorded, 136 records/sec (avg), 200 records/sec (current)
3816 lines parsed, 2800 lines recorded, 139 records/sec (avg), 200 records/sec (current)
3816 lines parsed, 2800 lines recorded, 133 records/sec (avg), 0 records/sec (current)
4033 lines parsed, 3000 lines recorded, 136 records/sec (avg), 200 records/sec (current)
4033 lines parsed, 3000 lines recorded, 130 records/sec (avg), 0 records/sec (current)
4244 lines parsed, 3200 lines recorded, 133 records/sec (avg), 200 records/sec (current)
4460 lines parsed, 3400 lines recorded, 135 records/sec (avg), 200 records/sec (current)
4460 lines parsed, 3400 lines recorded, 130 records/sec (avg), 0 records/sec (current)
4668 lines parsed, 3600 lines recorded, 133 records/sec (avg), 200 records/sec (current)
4875 lines parsed, 3800 lines recorded, 135 records/sec (avg), 200 records/sec (current)
5083 lines parsed, 4000 lines recorded, 137 records/sec (avg), 200 records/sec (current)
5289 lines parsed, 4200 lines recorded, 139 records/sec (avg), 200 records/sec (current)
5289 lines parsed, 4200 lines recorded, 135 records/sec (avg), 0 records/sec (current)
5495 lines parsed, 4400 lines recorded, 137 records/sec (avg), 200 records/sec (current)
5703 lines parsed, 4600 lines recorded, 139 records/sec (avg), 200 records/sec (current)
5703 lines parsed, 4600 lines recorded, 135 records/sec (avg), 0 records/sec (current)
5906 lines parsed, 4800 lines recorded, 136 records/sec (avg), 200 records/sec (current)
6110 lines parsed, 5000 lines recorded, 138 records/sec (avg), 200 records/sec (current)
6313 lines parsed, 5200 lines recorded, 140 records/sec (avg), 200 records/sec (current)
2018-12-12 07:11:16,968: [INFO] Error when connecting to Matomo: HTTP Error 500: Internal Server Error
2018-12-12 07:11:16,969: [INFO] Retrying request, attempt number 2
6313 lines parsed, 5200 lines recorded, 136 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 133 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 129 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 126 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 123 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 120 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 118 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 115 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 112 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 110 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 108 records/sec (avg), 0 records/sec (current)
2018-12-12 07:11:27,933: [INFO] Error when connecting to Matomo: HTTP Error 500: Internal Server Error
2018-12-12 07:11:27,934: [INFO] Retrying request, attempt number 3
6313 lines parsed, 5200 lines recorded, 106 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 103 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 101 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 99 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 98 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 96 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 94 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 92 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 91 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 89 records/sec (avg), 0 records/sec (current)
6313 lines parsed, 5200 lines recorded, 88 records/sec (avg), 0 records/sec (current)
2018-12-12 07:11:38,843: [INFO] Error when connecting to Matomo: HTTP Error 500: Internal Server Error
2018-12-12 07:11:38,843: [INFO] Max number of attempts reached, server is unreachable!
Fatal error: HTTP Error 500 Internal Server Error, response: {"status":"error","tracked":134,"invalid":0,"invalid_indices":[]}
You can restart the import of "/.../.../sc_w3c.log" from the point it failed by specifying --skip=5495 on the command line.

I also checked my NGINX error.log which shows the following errors:

2018/12/12 06:30:44 [error] 1210<a href='/1210'>#1210</a>: *37066 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
2018/12/12 06:30:55 [error] 1210<a href='/1210'>#1210</a>: *37068 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
2018/12/12 06:31:06 [error] 1210<a href='/1210'>#1210</a>: *37070 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
2018/12/12 07:00:43 [error] 1210<a href='/1210'>#1210</a>: *37134 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
2018/12/12 07:00:54 [error] 1210<a href='/1210'>#1210</a>: *37136 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
2018/12/12 07:01:04 [error] 1210<a href='/1210'>#1210</a>: *37138 FastCGI sent in stderr: "PHP message: Error in Matomo (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 => '▒▒XS▒▒7',   1 => '2018-12-06 20:37:36',   2 => 2,   3 => 1,   4 => 1,   5 => '2905145', )" while reading response header from upstream, client: x.x.x.x, server: , request: "POST /piwik.php HTTP/1.1", upstream: "fastcgi://unix:/var/run/php/php7.0-fpm.sock:", host: "x.x.x.x"
@itcrowdsource commented on December 12th 2018

I managed to solve the issue by executing the following command on the SQL database:

ALTER TABLE `piwik_log_visit` CHANGE `visit_total_interactions` `visit_total_interactions` MEDIUMINT(5) UNSIGNED NULL DEFAULT '0';
@fdellwing commented on December 12th 2018 Contributor

Duplicate of #11086

@Findus23 commented on December 12th 2018 Member

See also https://github.com/matomo-org/matomo/issues/11058
This is a breaking change as it may take a long time to migrate, so it will be done with Matomo 4

@itcrowdsource commented on December 12th 2018

@fdellwing @Findus23 After running the SQL update command I have no data anymore in the visitor map section. The import script seems to work flawless, also I can't discover any PHP/NGINX related errors. But something broke the visitor map. I deleted the current site and recreated a new one but that didn't help. Any ideas what this might be?

@fdellwing commented on December 12th 2018 Contributor

Are your visitors all from one country? There was a change to the visitor map with 3.7.0 that make it look like there are no data, see #13691

@itcrowdsource commented on December 12th 2018

No our visitors are from all around the world. The visitor map says There is no data for this report I tried to toggle between country/city with no result.

This Issue was closed on December 12th 2018
Powered by GitHub Issue Mirror