Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

plenty of Error query: SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_on_page' at row 1 #13098

Closed
tchwpkgorg opened this issue Jun 24, 2018 · 5 comments
Labels
not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Milestone

Comments

@tchwpkgorg
Copy link

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
Copy link
Member

sgiehl commented Jun 24, 2018

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

@sgiehl sgiehl added the not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org. label Jun 24, 2018
@tsteur
Copy link
Member

tsteur commented Jun 25, 2018

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
Copy link
Author

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
Copy link
Member

tsteur commented Jul 8, 2018

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

@mattab mattab added this to the 3.7.0 milestone Oct 3, 2018
@tsteur
Copy link
Member

tsteur commented Oct 4, 2018

See comment in #13098 (comment) on how to fix this. Matomo 4.0 will bring an update for this. For new installs it was fixed.

@tsteur tsteur closed this as completed Oct 4, 2018
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
not-in-changelog For issues or pull requests that should not be included in our release changelog on matomo.org.
Projects
None yet
Development

No branches or pull requests

4 participants