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

Jump to bottom

Numeric value out of range: 1264 Out of range value for column 'time_spent_ref_action' #3561

Closed
anonymous-matomo-user opened this issue Nov 21, 2012 · 5 comments
Closed

Numeric value out of range: 1264 Out of range value for column 'time_spent_ref_action' #3561

anonymous-matomo-user opened this issue Nov 21, 2012 · 5 comments
Assignees
@mattab
Labels
Bug For errors / faults / flaws / inconsistencies etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. worksforme The issue cannot be reproduced and things work as intended.
Milestone
1.11

Comments

@anonymous-matomo-user
Copy link

I upgraded to Piwik 1.9.2 (now to 1.9.3b5) from 1.9.1 and I'm getting couple of SQL errors from import_logs.py (but probably it has nothing to with the upgrade):

SQLSTATE[22003]: Numeric value out of range: 1264 Out of range value for column 'time_spent_ref_action' at row 1
                                In query: INSERT INTO piwik_log_link_visit_action (idvisit, idsite, idvisitor, server_time, idaction_url, idaction_url_ref, idaction_name_ref, time_spent_ref_action) VALUES (?,?,?,?,?,?,?,?)
                                Parameters: array (
  0 => '2424670',
  1 => 595,
  2 => 'zHH!"',
  3 => '2012-11-20 07:24:49',
  4 => '596044',
  5 => '989',
  6 => '0',
  7 => -47,
)

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

In query: INSERT INTO piwik_log_link_visit_action (idvisit, idsite, idvisitor, server_time, idaction_url, idaction_name, idaction_url_ref, idaction_name_ref, time_spent_ref_action) VALUES (?,?,?,?,?,?,?,?,?)

Parameters: array (\n  0 => '2430964',\n  1 => 604,\n  2 => '[~#Vf\rX\u0014',\n  3 => '2012-11-20 00:02:56',\n  4 => '26937',\n  5 => 0,\n  6 => '26791',\n  7 => '0',\n  8 => -1,\n)
@mattab
Copy link
Member

mattab commented Nov 22, 2012

Thanks for the report, I guess the reason is that your log file is in unsorted order (has older lines later after newer lines for a given visitor) but still of course piwik should not fail and we could eg. do a abs(time)

@anonymous-matomo-user
Copy link
Author

Yeah, I'm using logresolvemerge.pl from AWStats and I think that should sort the file properly? But import_logs.py fails pretty hard if one record in hits array fails to record.

@anonymous-matomo-user
Copy link
Author

server_time idaction_url idaction_url_ref time/ref Timefrom last
1/22/2013 05:49:08 278688 15642018 122 122 0
1/22/2013 06:03:28 278688 278688 860 860 0
1/22/2013 06:03:45 278695 278688 17 17 0
1/22/2013 06:04:14 15832442 278695 29 29 0
1/22/2013 06:06:20 15534057 15832442 126 126 0
1/22/2013 06:07:32 278695 278688 173 72 101
1/22/2013 06:07:58 15534255 278688 116 26 90
1/22/2013 06:09:16 15834719 15802188 128 78 50
1/22/2013 06:10:50 15708881 15834719 94 94 0
1/22/2013 06:12:30 278688 15824415 156 100 56
1/22/2013 06:13:55 15797646 278688 85 85 0
1/22/2013 06:18:38 278688 15797646 283 283 0
1/22/2013 06:19:29 15846367 278688 51 51 0
1/22/2013 06:25:25 15811737 15846367 356 356 0

This is some column from database. As you can see there is difference between time_spent_ref_action and difference time between server_time

We could focus on 101 - 90 - 50 - 56
In log file of fail we have:
[3] => 2013-01-22 06:04:39
[4] => 278688
[5] => 278687
[8] => -101

[3] => 2013-01-22 06:06:02
[4] => 278688
[5] => 278687
[8] => -90

[3] => 2013-01-22 06:07:08
[4] => 15802188
[5] => 15802187
[8] => -50

[3] => 2013-01-22 06:09:54
[4] => 15824415
[5] => 15824414
[8] => -56

I guest that record the visit at
at 06:06:20 before 06:04:39
at 06:07:58 before 06:07:08
....
Currently I have to add abs to prevent lose pageview. I will watch this bug and investigate more.

@mattab
Copy link
Member

mattab commented Feb 4, 2013

please submit a log file that replicaes the issue, ideally with 5-10 lines. If you can reproduce we can fix it (but i suspect time sort was not done)

@anonymous-matomo-user
Copy link
Author

This log file is very big and we don't keep this raw log right now. I'm sure that log order in this file is correct as we're using scribe and unix timestamp to record the log. I will check and test again with smaller log file. The problem could be from import_logs.py and piwik.php communication.

@anonymous-matomo-user anonymous-matomo-user added this to the 1.11 - Piwik 1.11 milestone Jul 8, 2014
This issue was closed.
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@mattab mattab

Labels
Bug For errors / faults / flaws / inconsistencies etc. Major Indicates the severity or impact or benefit of an issue is much higher than normal but not critical. worksforme The issue cannot be reproduced and things work as intended.
Projects
None yet
Milestone
1.11
Development

No branches or pull requests
2 participants
@mattab @anonymous-matomo-user