Loading...
 
Skip to main content

tikiwiki/stats does not work, and displays "An error occurred while performing the request."

  • Tracker item actions
Trackers
Items
Status
Open
Subject
tikiwiki/stats does not work, and displays "An error occurred while performing the request."
Version
16.x
Category
  • Error
Resolution status
New
Submitted by
DNeukomm
Lastmod by
DNeukomm
Rating
(0)
Description

tikiwiki/stats does not work, and displays "An error occurred while performing the request."
However, I would expect, that it displays the statistics.
I use tikiwiki 16.2, mysqld is 5.7.17-0ubuntu0.16.04.1

It seems that the error happens in function site_stats() from lib/stats/statslib.php.
I uncommented every call to functions, in tiki-stats.php, to find the position with the error.

It seems that some sql statements are wrong, and only work with some relax settings.
mysql 5.7.17 uses sql_mode=only_full_group_by .

I took the select-Statement from line 235 and inserted it into mysql.
select SUM(`pageviews`) AS views, `day` AS unixtime FROM `tiki_pageviews` GROUP by FROM_UNIXTIME(`day`, '%Y-No value assignedd') ORDER by views DESC LIMIT 1;
ERROR 1055 (42000): Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'tikiwikitestdb.tiki_pageviews.day' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

We group by date. However there are many seconds matching the same day.
So SQL does not know which unixtime to return.

select SUM(`pageviews`) AS views, ANY_VALUE(`day`) AS unixtime FROM `tiki_pageviews` GROUP by FROM_UNIXTIME(`day`, '%Y-No value assignedd') ORDER by views DESC LIMIT 1;

Solution

The solution is to tell sql that it should accept any_value().

select SUM(`pageviews`) AS views, ANY_VALUE(`day`) AS unixtime FROM `tiki_pageviews` GROUP by FROM_UNIXTIME(`day`, '%Y-No value assignedd') ORDER by views DESC LIMIT 1;

Copy to clipboard
statslib.php:234 to 260
			$max = $this->fetchAll(
				"SELECT SUM(`pageviews`) AS views, ANY_VALUE(`day`) AS unixtime" .
				" FROM `tiki_pageviews`" .
				" GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d')" .
				" ORDER BY views DESC" .
				" LIMIT 1"
			);
			$maxvar = $max[0]['views'];

			//get min pageview number
			$min = $this->fetchAll(
				"SELECT SUM(`pageviews`) AS views, ANY_VALUE(`day`) AS unixtime" .
				" FROM `tiki_pageviews`" .
				" GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d')" .
				" ORDER BY views ASC" .
				" LIMIT 1"
			);
			$minvar = $min[0]['views'];

			//pull all dates with max or min because there may be more than one for each
			$views = $this->fetchAll(
				"SELECT SUM(`pageviews`) AS views, FROM_UNIXTIME(`day`, '%Y-%m-%d') AS date, ANY_VALUE(`day`) AS unixtime" .
				" FROM `tiki_pageviews`" .
				" GROUP BY FROM_UNIXTIME(`day`, '%Y-%m-%d')" .
				" HAVING views = '$maxvar' OR views = '$minvar'" .
				" ORDER BY date ASC"
			);
Importance
1 low
Easy to solve?
9
Priority
9
Demonstrate Bug on Tiki 19+
Demonstrate Bug (older Tiki versions)
Ticket ID
6303
Created
Monday 20 March, 2017 16:51:59 UTC
by DNeukomm
LastModif
Tuesday 21 March, 2017 15:39:08 UTC


Show PHP error messages