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

[anonymous-matomo-user]

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]

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]

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]

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]

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]

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.