Fixed SQL Error

larsf

Cadet
After i change the htaccess i get this error:
Code:
Server Error
 
Mysqli prepare error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause
 
    Zend_Db_Statement_Mysqli->_prepare() in Zend/Db/Statement.php at line 115
    Zend_Db_Statement->__construct() in Zend/Db/Adapter/Mysqli.php at line 381
    Zend_Db_Adapter_Mysqli->prepare() in Zend/Db/Adapter/Abstract.php at line 478
    Zend_Db_Adapter_Abstract->query() in Zend/Db/Adapter/Abstract.php at line 734
    Zend_Db_Adapter_Abstract->fetchAll() in Arcade/Model/Game.php at line 37
    Arcade_Model_Game->buildPlayCount() in Arcade/ControllerPublic/ArcadeUgly.php at line 132
    Arcade_ControllerPublic_ArcadeUgly->actionCoreBurn() in XenForo/FrontController.php at line 310
    XenForo_FrontController->dispatch() in XenForo/FrontController.php at line 132
    XenForo_FrontController->run() in /httpdocs/index.php at line 13
 
Larsf,

In researching this error, it has typically been found to be a compatibility issue with another mod. Have you tried it on a clean, test board, with no mods?
 
I dont install any other Mods.
I have make i complete new installation of the forum with the newest version of the arcade and i get the same error.
 
Larsf,

Have you tried completely removing it (including any rogue tables) and reinstalling to see if this cures the problem? The thing that concerns me is this is not an error that anyone else is seeing and I doubt it is related to your PHP/SQL version.

You would think if it was an illegal query, it would affect all of us, regardless of the version.
 
Note to Kevin...

Looking over the code...and not a 'real' sql guru...let me pose this question.

In Library/Arcade/Model/Session.php, lines 43-48

SELECT session.*
FROM xf_arcade_session AS session
WHERE session.game_id = ?
AND session.valid = 1
AND (" . implode(' OR ', $whereConditions) . ")
ORDER BY session.score $scoreDirection, session.time_finish DESC

Should there be a GROUP BY clause? (ref)
 
I have deinstall the arcde, the database tables was automatic deleted. I cant found any other tables for the arcade.
I delete all files from the arcade and upload the newest version and install it. The Problem is the same.
I install a complete fresh XenForo and then i install the arcade but i get the same error.
 
Looking over the code...and not a 'real' sql guru...
Well, that makes two of us. :LOL:

... Should there be a GROUP BY clause? (ref)
We are thinking along the same lines. :coffee: Before I modify the query I was first trying to resolve why it is working now for everybody else except for Larsf. With the 0.0.6 release out the door last night I figured this weekend I would step back a bit and just look into the query issue before diving back into other code.
 
Before I modify the query I was first trying to resolve why it is working now for everybody else except for Larsf.
Not for everybody... :smiley:
Same error here. Occurs when the game ends and data is transmitting.

Fresh install (XF Arcade 0.0.7) on test board.
PHP: 5.2.6-1+lenny9
MySQL: 5.0.51a (hm, same as by larsf)

Server Error Log:
Code:
Error Info
Zend_Db_Statement_Mysqli_Exception: Mysqli prepare error: Mixing of GROUP columns (MIN(),MAX(),COUNT(),...) with no GROUP columns is illegal if there is no GROUP BY clause - library/Zend/Db/Statement/Mysqli.php:77
Generated By: XFR, Yesterday at 9:58 PM
 
Stack Trace
#0 /***/test/library/Zend/Db/Statement.php(115): Zend_Db_Statement_Mysqli->_prepare('????SELECT game...')
#1 /***/test/library/Zend/Db/Adapter/Mysqli.php(381): Zend_Db_Statement->__construct(Object(Zend_Db_Adapter_Mysqli), '????SELECT game...')
#2 /***/test/library/Zend/Db/Adapter/Abstract.php(478): Zend_Db_Adapter_Mysqli->prepare('????SELECT game...')
#3 /***/test/library/Zend/Db/Adapter/Abstract.php(734): Zend_Db_Adapter_Abstract->query('????SELECT game...', Array)
#4 /***/test/library/Arcade/Model/Game.php(63): Zend_Db_Adapter_Abstract->fetchAll('????SELECT game...')
#5 /***/test/library/Arcade/ControllerPublic/ArcadeUgly.php(132): Arcade_Model_Game->buildPlayCount(6)
#6 /***/test/library/XenForo/FrontController.php(310): Arcade_ControllerPublic_ArcadeUgly->actionCoreBurn()
#7 /***/test/library/XenForo/FrontController.php(132): XenForo_FrontController->dispatch(Object(XenForo_RouteMatch))
#8 /***/test/index.php(13): XenForo_FrontController->run()
#9 {main}
 
Request State
array(3) {
  ["url"] => string(72) "***.ru/arcade/?sessdo=burn&id=1&microone=1327701526.79"
  ["_GET"] => array(3) {
    ["sessdo"] => string(4) "burn"
    ["id"] => string(1) "1"
    ["microone"] => string(13) "1327701526.79"
  }
  ["_POST"] => array(0) {
  }
 
You can see there's a GROUP BY missing in the /Model/Game.php function "buildPlayCount", in the first query.

You could probably make it work by changing that query to:

PHP:
        $results = $db->fetchAll("
            SELECT game_id, COUNT(*) AS play_count
            FROM `xf_arcade_session`
            WHERE valid = 1
            " . ($gameId > 0?(" AND game_id = $gameId"):'') . "
            GROUP BY game_id
        ");
 
Unfortunately I don't have MySQL 5.0.x running anywhere so I can't reproduce it. :( However, the change by Trekkan is the proper fix and will be included in the 0.0.8 release. :smiley: No explanation as to why it is/was only affecting some versions of MySQL.
 
Back
Top