SQL errors

[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}

[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) ?

[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

[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,

[bass]

Thx guys.. helpful as usual

[undef.de]

[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"

[oliverde8]

Thx guys.. helpful as usual

Which version of mysql are you using? I can't reproduce the bug with mysql 5.7.14