search

dev-labs-bg / sportify

Football match prediction game with simple rules, built on Symfony
GNU Affero General Public License v3.0
33 stars 13 forks source link

Exception editing matches #20

Closed gonzad26 closed 6 years ago

gonzad26 commented 6 years ago

When i try to edit matches, it throws an sql exception I have some teams loaded with the API but no matches yet.

Exception:

` [1] Doctrine\DBAL\Exception\DriverException: An exception occurred while executing 'SELECT DISTINCT m0_.id AS id0, m0.datetime AS datetime1, m0.home_goals AS home_goals2, m0.away_goals AS away_goals3, m0.notification_sent AS notification_sent4, m0.home_team_id AS home_team_id5, m0.away_team_id AS away_team_id6, m0.tournament_id AS tournament_id7 FROM matches m0 INNER JOIN teams t1 ON m0.home_teamid = t1.id WHERE m0_.tournamentid = ? AND (m0.datetime >= ? AND m0.datetime <= ?) ORDER BY m0.datetime ASC, t1_.name ASC' with params [1, "2018-05-16", "2018-05-31"]:

SQLSTATE[HY000]: General error: 3065 Expression #2 of ORDER BY clause is not in SELECT list, references column 'prodewnpprodetest.t1.name' which is not in SELECT list; this is incompatible with DISTINCT at n/a in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/AbstractMySQLDriver.php line 115

at Doctrine\DBAL\Driver\AbstractMySQLDriver->convertException('An exception occurred while executing 'SELECT DISTINCT m0_.id AS id_0, m0_.datetime AS datetime_1, m0_.home_goals AS home_goals_2, m0_.away_goals AS away_goals_3, m0_.notification_sent AS notification_sent_4, m0_.home_team_id AS home_team_id_5, m0_.away_team_id AS away_team_id_6, m0_.tournament_id AS tournament_id_7 FROM matches m0_ INNER JOIN teams t1_ ON m0_.home_team_id = t1_.id WHERE m0_.tournament_id = ? AND (m0_.datetime >= ? AND m0_.datetime <= ?) ORDER BY m0_.datetime ASC, t1_.name ASC' with params [1, "2018-05-16", "2018-05-31"]:

SQLSTATE[HY000]: General error: 3065 Expression #2 of ORDER BY clause is not in SELECT list, references column 'prodewnpprodetest.t1.name' which is not in SELECT list; this is incompatible with DISTINCT', object(PDOException)) in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/DBALException.php line 116

at Doctrine\DBAL\DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT DISTINCT m0_.id AS id_0, m0_.datetime AS datetime_1, m0_.home_goals AS home_goals_2, m0_.away_goals AS away_goals_3, m0_.notification_sent AS notification_sent_4, m0_.home_team_id AS home_team_id_5, m0_.away_team_id AS away_team_id_6, m0_.tournament_id AS tournament_id_7 FROM matches m0_ INNER JOIN teams t1_ ON m0_.home_team_id = t1_.id WHERE m0_.tournament_id = ? AND (m0_.datetime >= ? AND m0_.datetime <= ?) ORDER BY m0_.datetime ASC, t1_.name ASC', array('1', '2018-05-16', '2018-05-31'))
    in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 836

at Doctrine\DBAL\Connection->executeQuery('SELECT DISTINCT m0_.id AS id_0, m0_.datetime AS datetime_1, m0_.home_goals AS home_goals_2, m0_.away_goals AS away_goals_3, m0_.notification_sent AS notification_sent_4, m0_.home_team_id AS home_team_id_5, m0_.away_team_id AS away_team_id_6, m0_.tournament_id AS tournament_id_7 FROM matches m0_ INNER JOIN teams t1_ ON m0_.home_team_id = t1_.id WHERE m0_.tournament_id = ? AND (m0_.datetime >= ? AND m0_.datetime <= ?) ORDER BY m0_.datetime ASC, t1_.name ASC', array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'), null)
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'))
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 321

at Doctrine\ORM\Query->_doExecute()
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 969

at Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 924

at Doctrine\ORM\AbstractQuery->execute(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 727

at Doctrine\ORM\AbstractQuery->getResult()
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Entity/MatchRepository.php line 208

at Devlabs\SportifyBundle\Entity\MatchRepository->getAllByTournamentAndTimeRange(object(Tournament), '2018-05-16', '2018-05-31')
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Controller/AdminController.php line 474

at Devlabs\SportifyBundle\Controller\AdminController->matchesAction(object(Request), 'empty', 'empty', 'empty')
    in  line 

at call_user_func_array(array(object(AdminController), 'matchesAction'), array(object(Request), 'empty', 'empty', 'empty'))
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 153

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 68

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 169

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /home/prodewnp/public_html/sportify/web/app_dev.php line 33

[2] Doctrine\DBAL\Driver\PDOException: SQLSTATE[HY000]: General error: 3065 Expression #2 of ORDER BY clause is not in SELECT list, references column 'prodewnpprodetest.t1.name' which is not in SELECT list; this is incompatible with DISTINCT at n/a in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php line 93

at Doctrine\DBAL\Driver\PDOStatement->execute()
    in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 828

at Doctrine\DBAL\Connection->executeQuery('SELECT DISTINCT m0_.id AS id_0, m0_.datetime AS datetime_1, m0_.home_goals AS home_goals_2, m0_.away_goals AS away_goals_3, m0_.notification_sent AS notification_sent_4, m0_.home_team_id AS home_team_id_5, m0_.away_team_id AS away_team_id_6, m0_.tournament_id AS tournament_id_7 FROM matches m0_ INNER JOIN teams t1_ ON m0_.home_team_id = t1_.id WHERE m0_.tournament_id = ? AND (m0_.datetime >= ? AND m0_.datetime <= ?) ORDER BY m0_.datetime ASC, t1_.name ASC', array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'), null)
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'))
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 321

at Doctrine\ORM\Query->_doExecute()
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 969

at Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 924

at Doctrine\ORM\AbstractQuery->execute(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 727

at Doctrine\ORM\AbstractQuery->getResult()
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Entity/MatchRepository.php line 208

at Devlabs\SportifyBundle\Entity\MatchRepository->getAllByTournamentAndTimeRange(object(Tournament), '2018-05-16', '2018-05-31')
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Controller/AdminController.php line 474

at Devlabs\SportifyBundle\Controller\AdminController->matchesAction(object(Request), 'empty', 'empty', 'empty')
    in  line 

at call_user_func_array(array(object(AdminController), 'matchesAction'), array(object(Request), 'empty', 'empty', 'empty'))
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 153

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 68

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 169

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /home/prodewnp/public_html/sportify/web/app_dev.php line 33

[3] PDOException: SQLSTATE[HY000]: General error: 3065 Expression #2 of ORDER BY clause is not in SELECT list, references column 'prodewnpprodetest.t1.name' which is not in SELECT list; this is incompatible with DISTINCT at n/a in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php line 91

at PDOStatement->execute(null)
    in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOStatement.php line 91

at Doctrine\DBAL\Driver\PDOStatement->execute()
    in /home/prodewnp/public_html/sportify/vendor/doctrine/dbal/lib/Doctrine/DBAL/Connection.php line 828

at Doctrine\DBAL\Connection->executeQuery('SELECT DISTINCT m0_.id AS id_0, m0_.datetime AS datetime_1, m0_.home_goals AS home_goals_2, m0_.away_goals AS away_goals_3, m0_.notification_sent AS notification_sent_4, m0_.home_team_id AS home_team_id_5, m0_.away_team_id AS away_team_id_6, m0_.tournament_id AS tournament_id_7 FROM matches m0_ INNER JOIN teams t1_ ON m0_.home_team_id = t1_.id WHERE m0_.tournament_id = ? AND (m0_.datetime >= ? AND m0_.datetime <= ?) ORDER BY m0_.datetime ASC, t1_.name ASC', array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'), null)
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query/Exec/SingleSelectExecutor.php line 50

at Doctrine\ORM\Query\Exec\SingleSelectExecutor->execute(object(Connection), array('1', '2018-05-16', '2018-05-31'), array('integer', '2', '2'))
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/Query.php line 321

at Doctrine\ORM\Query->_doExecute()
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 969

at Doctrine\ORM\AbstractQuery->executeIgnoreQueryCache(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 924

at Doctrine\ORM\AbstractQuery->execute(null, '1')
    in /home/prodewnp/public_html/sportify/vendor/doctrine/orm/lib/Doctrine/ORM/AbstractQuery.php line 727

at Doctrine\ORM\AbstractQuery->getResult()
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Entity/MatchRepository.php line 208

at Devlabs\SportifyBundle\Entity\MatchRepository->getAllByTournamentAndTimeRange(object(Tournament), '2018-05-16', '2018-05-31')
    in /home/prodewnp/public_html/sportify/src/Devlabs/SportifyBundle/Controller/AdminController.php line 474

at Devlabs\SportifyBundle\Controller\AdminController->matchesAction(object(Request), 'empty', 'empty', 'empty')
    in  line 

at call_user_func_array(array(object(AdminController), 'matchesAction'), array(object(Request), 'empty', 'empty', 'empty'))
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 153

at Symfony\Component\HttpKernel\HttpKernel->handleRaw(object(Request), '1')
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/HttpKernel.php line 68

at Symfony\Component\HttpKernel\HttpKernel->handle(object(Request), '1', true)
    in /home/prodewnp/public_html/sportify/vendor/symfony/symfony/src/Symfony/Component/HttpKernel/Kernel.php line 169

at Symfony\Component\HttpKernel\Kernel->handle(object(Request))
    in /home/prodewnp/public_html/sportify/web/app_dev.php line 33`
cmihaylov commented 6 years ago

Thanks for catching this and bringing it up. There was some unneeded usage of SELECT DISTINCT in MatchRepository which triggers an exception specific for MySQL 5.7 and later. I guess we've tested this on older versions only. Now it's removed and issue is solved. Pull and try again.

gonzad26 commented 6 years ago

Thanks for the fix! It's an excelent idea for a game. It was developed very well and the code is very understandable.