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

Powered by GitHub Issue Mirror