Fixed Stats in Admin not running

[ActorMike]

When I try to view my stats in the admin, I get a server error. Any ideas what could be causing this or how to track it down? I tried disabling 3rd party extensions and the problem persists-

Server Error
Mysqli prepare error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'grouping, stats_type, counter FROM xf_stats_daily WHERE stats_date BETWE' at line 1

1. Zend_Db_Statement_Mysqli->_prepare() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\Zend\Db\Statement.php at line 115
2. Zend_Db_Statement->__construct() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\Zend\Db\Adapter\Mysqli.php at line 381
3. Zend_Db_Adapter_Mysqli->prepare() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\Zend\Db\Adapter\Abstract.php at line 478
4. Zend_Db_Adapter_Abstract->query() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\Zend\Db\Adapter\Abstract.php at line 734
5. Zend_Db_Adapter_Abstract->fetchAll() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\XenForo\Model\Stats.php at line 59
6. XenForo_Model_Stats->getStatsData() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\XenForo\ControllerAdmin\Stats.php at line 58
7. XenForo_ControllerAdmin_Stats->getStatsData() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\XenForo\ControllerAdmin\Stats.php at line 112
8. XenForo_ControllerAdmin_Stats->actionDaily() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\XenForo\FrontController.php at line 369
9. XenForo_FrontController->dispatch() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\library\XenForo\FrontController.php at line 152
10. XenForo_FrontController->run() in D:\cuswebs\www1\forums.fordthunderbirdforum.com\admin.php at line 13

 

[ActorMike]

In case anyone gets this error, this is a compatibility problem with XenForo 1.5.18 and MySQL 8. They have a fix for the library/XenForo/Model/Stats.php file they gave me.

Look for 3 instances of the word grouping between lines 32 and 54 and change them to `grouping` with backticks around the word.

It will be added to the next release.

 

[Paul B]

Here's the diff if anyone needs it:

Index: library/XenForo/Model/Stats.php
===================================================================
--- library/XenForo/Model/Stats.php
+++ library/XenForo/Model/Stats.php
@@ -29,7 +29,7 @@
         if ($grouping == 'monthly')
         {
             $data = $db->fetchAll('
-                SELECT AVG(stats_date) AS grouping,
+                SELECT AVG(stats_date) AS `grouping`,
                     stats_type, SUM(counter) AS counter
                 FROM xf_stats_daily
                 WHERE stats_date BETWEEN ? AND ?
@@ -40,7 +40,7 @@
         else if ($grouping == 'weekly')
         {
             $data = $db->fetchAll('
-                SELECT AVG(stats_date) AS grouping,
+                SELECT AVG(stats_date) AS `grouping`,
                     stats_type, SUM(counter) AS counter
                 FROM xf_stats_daily
                 WHERE stats_date BETWEEN ? AND ?
@@ -51,7 +51,7 @@
         else
         {
             $data = $db->fetchAll('
-                SELECT stats_date AS grouping, stats_type, counter
+                SELECT stats_date AS `grouping`, stats_type, counter
                 FROM xf_stats_daily
                 WHERE stats_date BETWEEN ? AND ?
                     AND stats_type IN(' . $db->quote($statsTypes) . ')

 

[PaulB]

Can we (properly) tag this as a MySQL 8.0 readiness issue? It would help to start tracking those for both XF1 and XF2.