@tchwpkgorg opened this Issue on June 24th 2018

I'm getting plenty of

Numeric value out of range: 1264 Out of range value for column 'time_on_page'

with Matomo 3.5.1 running on Ubuntu 18.04 (PHP 7.2.5, Apache 2.4.29, MySQL server 5.7.22):

[Sat Jun 23 06:30:16.612963 2018] [proxy_fcgi:error] [pid 5247:tid 139715784689408] [client 180.149.6.46:55182] AH01071: Got error 'PHP message: Error in Matomo (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_on_page' at row 1 In query: INSERT INTO matomo_log_hsr (idvisit,idsite,idhsrview,idpageview,server_time,time_on_page,viewport_w_px,viewport_h_px,scroll_y_max_relative,fold_y_relative) VALUES(?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE time_on_page = if(? > time_on_page, ?, time_on_page), scroll_y_max_relative = if(? > scroll_y_max_relative, ?, scroll_y_max_relative), idloghsr = LAST_INSERT_ID(idloghsr) Parameters: array ( 0 => '154208', 1 => 1, 2 => 'GF7ioI', 3 => 'MQfXwD', 4 => '2018-06-23 06:30:16', 5 => '9003864', 6 => '1366', 7 => '654', 8 => 947, 9 => 947, 10 => '9003864', 11 => '9003864', 12 => '947', 13 => '947', )\n', referer: https://login.example.com/v2/index.php?page=timeline_report

[Sat Jun 23 17:26:28.290580 2018] [proxy_fcgi:error] [pid 5247:tid 139715793090304] [client 49.32.87.103:19435] AH01071: Got error 'PHP message: Error in Matomo (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_on_page' at row 1 In query: INSERT INTO matomo_log_hsr (idvisit,idsite,idhsrview,idpageview,server_time,time_on_page,viewport_w_px,viewport_h_px,scroll_y_max_relative,fold_y_relative) VALUES(?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE time_on_page = if(? > time_on_page, ?, time_on_page), scroll_y_max_relative = if(? > scroll_y_max_relative, ?, scroll_y_max_relative), idloghsr = LAST_INSERT_ID(idloghsr) Parameters: array ( 0 => '157825', 1 => 1, 2 => 'RmzfdS', 3 => '', 4 => '2018-06-23 17:26:28', 5 => '43831193', 6 => '360', 7 => '616', 8 => 450, 9 => 257, 10 => '43831193', 11 => '43831193', 12 => '450', 13 => '450', )\n', referer: https://login.example.com/v2/index.php?page=timesheet

[Sat Jun 23 19:10:13.293229 2018] [proxy_fcgi:error] [pid 5247:tid 139715809892096] [client 80.7.252.73:57806] AH01071: Got error 'PHP message: Error in Matomo (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_on_page' at row 1 In query: INSERT INTO matomo_log_hsr (idvisit,idsite,idhsrview,idpageview,server_time,time_on_page,viewport_w_px,viewport_h_px,scroll_y_max_relative,fold_y_relative) VALUES(?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE time_on_page = if(? > time_on_page, ?, time_on_page), scroll_y_max_relative = if(? > scroll_y_max_relative, ?, scroll_y_max_relative), idloghsr = LAST_INSERT_ID(idloghsr) Parameters: array ( 0 => '158256', 1 => 1, 2 => 'E6zYWq', 3 => '3BYqoj', 4 => '2018-06-23 19:10:13', 5 => '100978536', 6 => '1536', 7 => '759', 8 => 925, 9 => 627, 10 => '100978536', 11 => '100978536', 12 => '925', 13 => '925', )\n', referer: https://valentineweddingsuk.example.com/v2/index.php?page=billing

[Sat Jun 23 19:13:09.938400 2018] [proxy_fcgi:error] [pid 5247:tid 139715809892096] [client 2.124.220.97:64486] AH01071: Got error 'PHP message: Error in Matomo (tracker): Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_on_page' at row 1 In query: INSERT INTO matomo_log_hsr (idvisit,idsite,idhsrview,idpageview,server_time,time_on_page,viewport_w_px,viewport_h_px,scroll_y_max_relative,fold_y_relative) VALUES(?,?,?,?,?,?,?,?,?,?) ON DUPLICATE KEY UPDATE time_on_page = if(? > time_on_page, ?, time_on_page), scroll_y_max_relative = if(? > scroll_y_max_relative, ?, scroll_y_max_relative), idloghsr = LAST_INSERT_ID(idloghsr) Parameters: array ( 0 => '158268', 1 => 1, 2 => 'Yseta4', 3 => 'VNHfYX', 4 => '2018-06-23 19:13:09', 5 => '91655791', 6 => '1536', 7 => '732', 8 => 505, 9 => 505, 10 => '91655791', 11 => '91655791', 12 => '505', 13 => '505', )\n', referer: https://acfconsultants.example.com/v2/index.php?page=timeline_report

@sgiehl commented on June 24th 2018 Member

This seems to be a problem with the Heatmap & Session Recording plugin.
ping @tsteur

@tsteur commented on June 25th 2018 Member

Can you execute the following MySQL query in your database?

ALTER TABLE matomo_log_hsr CHANGE `time_on_page` `time_on_page` BIGINT(11) NOT NULL;

This should fix the issue. For now I would not add an unsigned as the current column does not have an unsigned which might be just an accident but we will need to double check again.

@tchwpkgorg commented on July 8th 2018

The error stopped showing up a couple of days ago. I didn't run the above query. Did the plugin auto-update with some fix?

@tsteur commented on July 8th 2018 Member

There is no "auto-update" in Matomo, you always need to actively click on "update". Also there was no change that could affect this.

@tsteur commented on October 4th 2018 Member

See comment in https://github.com/matomo-org/matomo/issues/13098#issuecomment-399827303 on how to fix this. Matomo 4.0 will bring an update for this. For new installs it was fixed.

This Issue was closed on October 4th 2018
Powered by GitHub Issue Mirror