Upgraded to 2.14.0 / Unknown column, Undefined index, etc


#1

Automatically upgraded to 2.14.0. No error reported during app and database upgrade.

New Dashboard shows SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.location_browser_lang’ in ‘field list’" in Keywords, Visits Over Time, Search Engines, Referrer Websites, Operating System versions widgets, and “There is no data for this report” in Visitor Map widget. The browser and OS icons shows white squares next to a working flag under Visitors in Real-time widget. The SEO Rakings widget is the only one not showing any apparent error.

If set to 1 day, Visitors > Overview shows many lines of [i]“WARNING: /[path]/htdocs/piwik/plugins/UserLanguage/Visitor.php(29): Notice - Undefined index: location_browser_lang - Piwik 2.14.0…”[i], also “config_browser_version” and “config_os”.

All under one long red text error of [i]“ERROR: Failed to get data from API: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.location_browser_lang’ in ‘field list’ #0 /[path]/piwik/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #1 /[path]/piwik/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 /[path]/piwik/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’\n\t\t\tSELECT\n\t\t\t\t…’, Array)…[snip]”

If set to longer periods, Visitors > Overview shows “Oops… there was a problem during the request. Maybe the server had a temporary issue, or maybe you requested a report with too much data. Please try again. If this error occurs repeatedly please contact your Piwik administrator for assistance.”

Visitors > Visitor Log shows working list other than blank OS and browser icons. Hovering over those shows unknown OS and browser but browser engines are still identified.

Virtually all other pages show "SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.location_browser_lang’ in ‘field list’ " errors.

I don’t think Piwik is tracking any view visits.

Did not manually backup database prior to upgrade.


#2

I have a very big problem too since this update !!

Look at this : 301 Moved Permanently


(Matthieu Aubry) #3

Hi temp150701

can you try disable and re-enable the UserLanguage plugin?


#4

I have the same problem here, I’ve tried disabling/re-enabling UserLanguage plugin without success…

New version of piwik should really be much more tested, that’s not the first time an upgrade fail for me while I just use the stock version without any customization or using advanced features, that’s really annoying.


#5

Update from 2.8 to 2.14 report some errors with the console core:archive.

First ‘log_visit.location_browser_lang’ was missing, have done an ALTER, and now…
Missing : 'log_visit.config_os’
And then : ‘log_visit.config_browser_name’

Where does those fields came from ?
I tried to reactive the plugin DeviceDetection but it does nothing on table update…


(Matthieu Aubry) #6

Hi there,

I created a bug report here: Column not found: 1054 Unknown column 'log_visit.location_browser_lang' in 'field list'" · Issue #8304 · matomo-org/piwik · GitHub

You can find a workaround to fix your PIwik here: 301 Moved Permanently


#7

After disabling UserLanguage plugin, various widgets shows
"SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.config_os’ in ‘field list’ ".
After reactivating the plugin, various widgets returns to showing
"SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.location_browser_lang’ in ‘field list’ ".


#8

Thank you. I note that the bug reports refers to the “location_browser_lang” problem specifically. As I mentioned in my original post other similar erros include at least “config_browser_version” and “config_os”. Should seperate bug reports be filed for each?


#9

Great problems here. Cannot see any data after upgrading:

ERROR: Failed to get data from API: SQLSTATE[42S22]: Column not found: 1054 Unknown column ‘log_visit.location_browser_lang’ in ‘field list’ #0 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/libs/Zend/Db/Statement.php(300): Zend_Db_Statement_Pdo->_execute(Array) #1 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/libs/Zend/Db/Adapter/Abstract.php(479): Zend_Db_Statement->execute(Array) #2 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/libs/Zend/Db/Adapter/Pdo/Abstract.php(238): Zend_Db_Adapter_Abstract->query(’???SELECT???..’, Array) #3 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Db/Adapter/Pdo/Mysql.php(233): Zend_Db_Adapter_Pdo_Abstract->query(’???SELECT???..’, Array) #4 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/DataAccess/LogAggregator.php(324): Piwik\Db\Adapter\Pdo\Mysql->query(’???SELECT???..’, Array) #5 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/UserLanguage/Archiver.php(65): Piwik\DataAccess\LogAggregator->queryVisitsByDimension(Array) #6 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/UserLanguage/Archiver.php(38): Piwik\Plugins\UserLanguage\Archiver->aggregateByLanguage() #7 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/ArchiveProcessor/PluginsArchiver.php(108): Piwik\Plugins\UserLanguage\Archiver->aggregateDayReport() #8 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/ArchiveProcessor/Loader.php(122): Piwik\ArchiveProcessor\PluginsArchiver->callAggregateAllPlugins(‘1’, ‘0’) #9 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/ArchiveProcessor/Loader.php(72): Piwik\ArchiveProcessor\Loader->prepareAllPluginsArchive(false, false) #10 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Archive.php(894): Piwik\ArchiveProcessor\Loader->prepareArchive(‘VisitsSummary’) #11 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Archive.php(708): Piwik\Archive->prepareArchive(Array, Object(Piwik\Site), Object(Piwik\Period\Day)) #12 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Archive.php(660): Piwik\Archive->cacheArchiveIdsAfterLaunching(Array, Array) #13 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Archive.php(598): Piwik\Archive->getArchiveIds(Array) #14 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Archive.php(324): Piwik\Archive->get(Array, ‘numeric’) #15 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/VisitsSummary/API.php(35): Piwik\Archive->getDataTableFromNumeric(Array) #16 [internal function]: Piwik\Plugins\VisitsSummary\API->get(‘1’, ‘day’, ‘2015-06-10,2015…’, false, ‘nb_uniq_visitor…’) #17 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/API/Proxy.php(211): call_user_func_array(Array, Array) #18 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/API/API.php(405): Piwik\API\Proxy->call(’\Piwik\Plugins…’, ‘get’, Array) #19 [internal function]: Piwik\Plugins\API\API->get(‘1’, ‘day’, ‘2015-06-10,2015…’, false, false) #20 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/API/Proxy.php(211): call_user_func_array(Array, Array) #21 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Plugin/Visualization.php(259): Piwik\API\Proxy->call(’\Piwik\Plugins…’, ‘get’, Array) #22 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Plugin/Visualization.php(180): Piwik\Plugin\Visualization->loadDataTableFromAPI() #23 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Plugin/ViewDataTable.php(414): Piwik\Plugin\Visualization->buildView() #24 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/Plugin/Controller.php(248): Piwik\Plugin\ViewDataTable->render() #25 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/VisitsSummary/Controller.php(126): Piwik\Plugin\Controller->renderView(Object(Piwik\Plugins\CoreVisualizations\Visualizations\JqplotGraph\Evolution)) #26 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/plugins/VisitsSummary/Controller.php(42): Piwik\Plugins\VisitsSummary\Controller->getEvolutionGraph(Array, Array, ‘getIndexGraph’) #27 [internal function]: Piwik\Plugins\VisitsSummary\Controller->index() #28 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/FrontController.php(494): call_user_func_array(Array, Array) #29 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/FrontController.php(94): Piwik\FrontController->doDispatch(NULL, NULL, NULL) #30 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/core/dispatch.php(34): Piwik\FrontController->dispatch() #31 /home/hmestrum/domains/hansmestrum.nl/public_html/statistics/index.php(27): require_once(’/home/hmestrum/…’) #32 {main}
×


#10

This did the trick for me: 301 Moved Permanently Solved!


#11

[ul]
[li]Applied workaround. 301 Moved Permanently
[/li][li]Deactivated/reactivated userLanguage plugin.
[/li][li]No more obvious database error.
[/li][li]Visitors > Visitor Log error remains. Log shows working list but blank OS and browser icons. Hovering over those shows unknown OS and browser but browser engines are still identified.
[/li][li]Still not tracking new visits. Last visitor entry just before upgrade. New test visits not showing.
[/li][/ul]


(Matthieu Aubry) #12

@temp150701

Do you see any error in your server error log?

To troubleshoot issue with tracker further, enable debugging: Tracking HTTP API: API Reference - Matomo Analytics (formerly Piwik Analytics) - Developer Docs - v3

then visit your website with Browser Developer Tools, and open the piwik.php request -> do you see an error message?


#13

@matt

Before I enable debugging as advised, which log do you want me to check? The apache log? Piwik web app log? (If latter, where?)

To “open the piwik.php request”, do I just visit the address “mydoamin.com - This website is for sale! - mydoamin Resources and Information.” from my browser with Developer Tools enabled, and report back with any messages from the Developer Tools?


(Matthieu Aubry) #14
  1. Webserver error log

  2. open Developer tools and open the piwik.php?.. request (it will have many parameters)


#15

Apologies. It appears to have been tracking visitors since applying the workaround. Perhaps the host’s caching web servers cannot deliver results in real-time.

For entries before the update to 2.14, Visitors > Visitor Log shows blank OS and browser icons, which when hovered over show unknown OS and browser but browser engines are still identified.

New visitors entries since the applying the workaround show full set of icons.


#16

sound like an old bug

read: 301 Moved Permanently

in our case a phpmyadmin fix, where “stat” needs to be replaced by your DB table prefix

1 Go to your MySQL database (for example with phpmyadmin)
2. Click the SQL tab or SQL command button
3. Copy paste the code below in the text field
4. Click start/run/execute


ALTER TABLE stat_log_visit ADD COLUMN location_browser_lang VARCHAR(20) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_os char(3) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_browser_name varchar(10) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_browser_version varchar(20) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_resolution varchar(9) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_pdf tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_flash tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_director tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_quicktime tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_realplayer tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_windowsmedia tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_gears tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_silverlight tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_java tinyint(1) NOT NULL; 
ALTER TABLE stat_log_visit ADD COLUMN config_cookie tinyint(1) NOT NULL;