Page 1 of 1

SQL errors

Posted: 14 Sep 2016, 20:39
by bass
Hi seeing these errors after I updated server to php 7.. Any ideas what might be broken?

Thanks.

Code: Select all

[2016-09-14T21:18:05+02:00] 
    Occured on 14.09.2016 at 21:18:05 at process with ID #22239
    ---------------------------------
 -> ManiaLive\Database\QueryException with code 1055
    Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'eXpansion.r1.record_score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  - in /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Database/MySQLI/Connection.php on line 147
  - Stack: #0 /home/TMServer/eXpansion/vendor/ml-expansion/expansion/LocalRecords/LocalBase.php(1513): ManiaLive\Database\MySQLI\Connection->execute('INSERT INTO exp...')
           #1 /home/TMServer/eXpansion/vendor/ml-expansion/expansion/LocalRecords/LocalBase.php(509): ManiaLivePlugins\eXpansion\LocalRecords\LocalBase->updateRanks('erm4Vw69DiARmau...', 1, true)
           #2 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/DedicatedApi/Callback/Event.php(71): ManiaLivePlugins\eXpansion\LocalRecords\LocalBase->onEndMatch(Array, -1)
           #3 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Event/Dispatcher.php(132): ManiaLive\DedicatedApi\Callback\Event->fireDo(Object(ManiaLivePlugins\eXpansion\LocalRecords\LocalRecords))
           #4 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Application/Application.php(123): ManiaLive\Event\Dispatcher::dispatch(Object(ManiaLive\DedicatedApi\Callback\Event))
           #5 /home/TMServer/eXpansion/bootstrapper.php(79): ManiaLive\Application\Application->run()
           #6 {main}

[2016-09-14T21:30:13+02:00] 
    Occured on 14.09.2016 at 21:30:13 at process with ID #22239
    ---------------------------------
 -> ManiaLive\Database\QueryException with code 1055
    Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'eXpansion.r1.record_score' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by
  - in /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Database/MySQLI/Connection.php on line 147
  - Stack: #0 /home/TMServer/eXpansion/vendor/ml-expansion/expansion/LocalRecords/LocalBase.php(1513): ManiaLive\Database\MySQLI\Connection->execute('INSERT INTO exp...')
           #1 /home/TMServer/eXpansion/vendor/ml-expansion/expansion/LocalRecords/LocalBase.php(509): ManiaLivePlugins\eXpansion\LocalRecords\LocalBase->updateRanks('hWQFIMMlC6ZXCFA...', 1, true)
           #2 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/DedicatedApi/Callback/Event.php(71): ManiaLivePlugins\eXpansion\LocalRecords\LocalBase->onEndMatch(Array, -1)
           #3 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Event/Dispatcher.php(132): ManiaLive\DedicatedApi\Callback\Event->fireDo(Object(ManiaLivePlugins\eXpansion\LocalRecords\LocalRecords))
           #4 /home/TMServer/eXpansion/vendor/maniaplanet/manialive-lib/ManiaLive/Application/Application.php(123): ManiaLive\Event\Dispatcher::dispatch(Object(ManiaLive\DedicatedApi\Callback\Event))
           #5 /home/TMServer/eXpansion/bootstrapper.php(79): ManiaLive\Application\Application->run()
           #6 {main}

Re: SQL errors

Posted: 15 Sep 2016, 06:41
by oliverde8
For some reasons you have the only_full_group_by option enabled in your MYSQL; that should not be the case by default.

Are you running Linux(which distribution)? Or Windows (wamp, xampp) ?

Re: SQL errors

Posted: 15 Sep 2016, 12:55
by undef.de
It seem that the ONLY_FULL_GROUP_BY is now enabled by default:
As of MySQL 5.7.5, the default SQL mode includes ONLY_FULL_GROUP_BY.
http://dev.mysql.com/doc/refman/5.7/en/ ... l_group_by

Maybe this helps:

Code: Select all

SET GLOBAL sql_mode = '';
SET SESSION sql_mode = '';
But note http://stackoverflow.com/a/31058962

Re: SQL errors

Posted: 15 Sep 2016, 18:17
by oliverde8
Well, I started using DB with Oracle DB, which actually didn't allow to have this scenario. Until quite recently I never did queries in MYSQL without having all the SELECTS in the group by.
But so many applications are using group by this way, I started doing it as well :( .

I am going to fix this in eXpansion it shouldn't be to complicated I hope there isn't many of them.

I think the best way for now is to change the mysql/my.cnf

Add this to the end of the file
[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

Then restart,

Re: SQL errors

Posted: 16 Sep 2016, 00:41
by bass
Thx guys.. helpful as usual :thumbsup:

Re: SQL errors

Posted: 16 Sep 2016, 08:59
by undef.de
oliverde8 wrote:[mysqld]
sql_mode = "STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
Not fully visible because off no code block, here is the complete list:

Code: Select all

[mysqld]
sql_mode = "STRICT_TRANS_TABLES, NO_ZERO_IN_DATE, NO_ZERO_DATE, ERROR_FOR_DIVISION_BY_ZERO, NO_AUTO_CREATE_USER, NO_ENGINE_SUBSTITUTION"
8-)

Re: SQL errors

Posted: 18 Sep 2016, 10:38
by oliverde8
bass wrote:Thx guys.. helpful as usual :thumbsup:
Which version of mysql are you using? I can't reproduce the bug with mysql 5.7.14