Piwik.php broken after 2.14.0 upgrade; always returns 400


#1

Since upgrading to 2.14, all tracking has stopped on my five-site setup. piwik.php is returning 400 with a useless payload, and the call results in no tracking.

Any incoming request, e.g.:

curl ‘http://{hostname}/piwik/piwik.php?action_name={page title}&idsite=2&rec=1&r=782288&h=8&m=44&s=25&url={escaped page URL}&_id=b98288daaf7749cd&_idts=1428762921&_idvc=5&_idn=0&_refts=0&_viewts=1436543065&send_image=0&cookie=1&res=750x1334’

yields this response:

HTTP/1.0 400 123
Piwik is a free/libre web analytics that lets you keep control of your data.

I haven’t the time to sort out a rollback right now, but I’ve forced piwik to serve up 404s until I have time to do so, in case there is background damage happening.


(Matthieu Aubry) #2

Hi there,

Can you enable tracker debug logging: Tracking HTTP API: API Reference - Matomo Analytics (formerly Piwik Analytics) - Developer Docs - v3
and then load this piwik.php page and see what it says?


#3

The problem is the location_browser_lang column being missing from piwik_log_visit. (Before you ask, the database user running Piwik has all privileges.)

I’ve seen other people with this issue on other threads, so I’ll go see how to resolve it. Why an upgrade would break things so hellishly is beyond me, though.

Debug enabled - Input parameters:
array (
‘action_name’ => ‘{site title}’,
‘idsite’ => ‘2’,
‘rec’ => ‘1’,
‘r’ => ‘184222’,
‘h’ => ‘19’,
‘m’ => ‘4’,
‘s’ => ‘22’,
‘url’ => ‘{site URL}’,
’_id’ => ‘62ed5b0670af4c1d’,
’_idts’ => ‘1434910182’,
’_idvc’ => ‘2’,
’_idn’ => ‘0’,
’_refts’ => ‘0’,
’_viewts’ => ‘1436579998’,
‘send_image’ => ‘0’,
‘pdf’ => ‘0’,
‘qt’ => ‘1’,
‘realp’ => ‘0’,
‘wma’ => ‘0’,
‘dir’ => ‘0’,
‘fla’ => ‘1’,
‘java’ => ‘1’,
‘gears’ => ‘0’,
‘ag’ => ‘0’,
‘cookie’ => ‘1’,
‘res’ => ‘1366x768’,
‘gt_ms’ => ‘226’,
)
Current datetime: 2015-07-11 02:22:19
General tracker cache was re-created.
DoNotTrack header not found
Website 2 tracker cache was re-created.
Visitor IP was not anonymized: ###.94.116.211
(this is not a Site Search request)
Excluding parameters “gclid,fb_xd_fragment,fb_comment_id,phpsessid,jsessionid,sessionid,aspsessionid,doing_wp_cron,pk_cpn,pk_campaign,piwik_campaign,utm_campaign,utm_source,utm_medium,pk_kwd,pk_keyword,piwik_kwd,utm_term” from URL
Action is a PAGE_URL,
Action name = {site title},
Action URL = {site URL}
Matching visitors with: visitorId=62ed5b0670af4c1d OR configId=6b5224733b8f122d
Exception: Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘location_browser_lang’ in 'field list’
In query: SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_days_since_first, visitor_days_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, location_ip, location_browser_lang, visit_total_time, visit_total_actions, visit_total_searches , custom_var_k1, custom_var_v1, custom_var_k2, custom_var_v2, custom_var_k3, custom_var_v3, custom_var_k4, custom_var_v4, custom_var_k5, custom_var_v5 FROM piwik_log_visit WHERE visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ? AND idvisitor = ?
ORDER BY visit_last_action_time DESC
LIMIT 1
Parameters: array (
0 => ‘2015-07-11 01:52:19’,
1 => ‘2015-07-11 02:52:19’,
2 => 2,
3 => ‘bí[p¯L’,
)

Piwik › Error
Piwik
free/libre analytics platform
<div class="header">
	<h1>An error occurred</h1>
</div>

<div class="content"><p>Error query: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'location_browser_lang' in 'field list'
                            In query: SELECT visit_last_action_time, visit_first_action_time, idvisitor, idvisit, user_id, visit_exit_idaction_url, visit_exit_idaction_name, visitor_returning, visitor_days_since_first, visitor_days_since_order, visitor_count_visits, visit_goal_buyer, location_country, location_region, location_city, location_latitude, location_longitude, referer_name, referer_keyword, referer_type, location_ip, location_browser_lang, visit_total_time, visit_total_actions, visit_total_searches , custom_var_k1, custom_var_v1, custom_var_k2, custom_var_v2, custom_var_k3, custom_var_v3, custom_var_k4, custom_var_v4, custom_var_k5, custom_var_v5  FROM piwik_log_visit WHERE visit_last_action_time >= ? AND visit_last_action_time <= ? AND idsite = ? AND idvisitor = ?
            ORDER BY visit_last_action_time DESC
            LIMIT 1
                            Parameters: array (

0 => ‘2015-07-11 01:52:19’,
1 => ‘2015-07-11 02:52:19’,
2 => 2,
3 => ‘bí[p¯L’,
)

Backtrace:
#0 /home/public/piwik/core/Tracker/Db/Pdo/Mysql.php(166): Piwik\Tracker\Db\Pdo\Mysql->query(‘SELECT visit_la…’, Array)
#1 /home/public/piwik/core/Tracker/Model.php(375): Piwik\Tracker\Db\Pdo\Mysql->fetch(‘SELECT visit_la…’, Array)
#2 /home/public/piwik/core/Tracker/Model.php(357): Piwik\Tracker\Model->fetchVisitor(‘SELECT visit_la…’, ‘FROM piwik_log_…’, ‘visit_last_acti…’, Array)
#3 /home/public/piwik/core/Tracker/Model.php(340): Piwik\Tracker\Model->findVisitorByVisitorId(‘b\xED[\x06p\xAFL\x1D’, ‘SELECT visit_la…’, ‘FROM piwik_log_…’, ‘visit_last_acti…’, Array)
#4 /home/public/piwik/core/Tracker/Visitor.php(68): Piwik\Tracker\Model->findVisitor(2, ‘kR$s;\x8F\x12-’, ‘b\xED[\x06p\xAFL\x1D’, Array, 5, false, true, ‘2015-07-11 01:5…’, ‘2015-07-11 02:5…’)
#5 /home/public/piwik/core/Tracker/Visit.php(167): Piwik\Tracker\Visitor->recognize()
#6 /home/public/piwik/core/Tracker.php(139): Piwik\Tracker\Visit->handle()
#7 /home/public/piwik/core/Tracker/Handler.php(49): Piwik\Tracker->trackRequest(Object(Piwik\Tracker\Request))
#8 /home/public/piwik/core/Tracker.php(121): Piwik\Tracker\Handler->process(Object(Piwik\Tracker), Object(Piwik\Tracker\RequestSet))
#9 /home/public/piwik/core/Tracker.php(98): Piwik\Tracker->track(Object(Piwik\Tracker\Handler), Object(Piwik\Tracker\RequestSet))
#10 /home/public/piwik/piwik.php(67): Piwik\Tracker->main(Object(Piwik\Tracker\Handler), Object(Piwik\Tracker\RequestSet))
#11 {main}
<div class="footer">
    <a href="index.php">&laquo; Back to Piwik</a>
</div>
Empty request => Piwik page Piwik is a free/libre web analytics that lets you keep control of your data.End of the page. array ( ) Time elapsed: 0.321s

#4

Found the workaround:

http://forum.piwik.org/read.php?2,127838,page=1#msg-127877

Only the location_browser_lang column was missing for me.

ALTER TABLE stat_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL;