MySQL 5.7 ONLY_FULL_GROUP_BY
nos1609 opened this issue · 5 comments
Have problems opening blog posts in admin panel
Whoops, looks like something went wrong.
3/3 DriverException in AbstractMySQLDriver.php line 115: An exception occurred while executing 'SELECT user_id, name, username FROM pk_blog_post INNER JOIN pk_system_user ON user_id = pk_system_user.id GROUP BY user_id, name':
SQLSTATE[42000]: Syntax error or access violation: 1055 'pagekit.pk_system_user.username' isn't in GROUP BY
in AbstractMySQLDriver.php line 115
at AbstractMySQLDriver->convertException('An exception occurred while executing 'SELECT user_id, name, username FROM pk_blog_post INNER JOIN pk_system_user ON user_id = pk_system_user.id GROUP BY user_id, name': SQLSTATE[42000]: Syntax error or access violation: 1055 'pagekit.pk_system_user.username' isn't in GROUP BY', object(PDOException)) in DBALException.php line 116
at DBALException::driverExceptionDuringQuery(object(Driver), object(PDOException), 'SELECT user_id, name, username FROM pk_blog_post INNER JOIN pk_system_user ON user_id = pk_system_user.id GROUP BY user_id, name', array()) in Connection.php line 836
at Connection->executeQuery('SELECT user_id, name, username FROM pk_blog_post INNER JOIN pk_system_user ON user_id = pk_system_user.id GROUP BY user_id, name', array(), array(), null) in Connection.php line 178
at Connection->executeQuery('SELECT user_id, name, username FROM @blog_post INNER JOIN @system_user ON user_id = @system_user.id GROUP BY user_id, name', array(), array()) in QueryBuilder.php line 670
at QueryBuilder->executeQuery() in QueryBuilder.php line 590
at QueryBuilder->execute()
at call_user_func_array(array(object(QueryBuilder), 'execute'), array()) in QueryBuilder.php line 181
at QueryBuilder->__call('execute', array()) in PostModelTrait.php line 29
at Post::getAuthors() in BlogController.php line 28
at BlogController->postAction(null, null)
at call_user_func_array(array(object(BlogController), 'postAction'), array(null, null)) in ControllerListener.php line 60
at ControllerListener->executeController(object(ControllerEvent), object(Request))
at call_user_func_array(array(object(ControllerListener), 'executeController'), array(object(ControllerEvent), object(Request))) in WrappedListener.php line 63
at WrappedListener->__invoke(object(ControllerEvent), object(Request))
at call_user_func_array(object(WrappedListener), array(object(ControllerEvent), object(Request))) in EventDispatcher.php line 123
at EventDispatcher->trigger(object(ControllerEvent), array(object(ControllerEvent), object(Request))) in TraceableEventDispatcher.php line 126
at TraceableEventDispatcher->trigger('controller', array(object(Request))) in HttpKernel.php line 148
at HttpKernel->handleController() in HttpKernel.php line 77
at HttpKernel->handle(object(Request)) in Application.php line 67
at Application->run() in app.php line 24
at require_once('/webroot/app/system/app.php') in index.php line 40
My config:
Pagekit 1.0.11
nginx 1.11.12
php 7.1.3
MariaDB 10.1.22
Unfortunately I can not recreate this issue. It would be great if you could change https://github.com/pagekit/extension-blog/blob/master/src/Model/PostModelTrait.php#L29 to return self::query()->select('user_id', 'name', 'username')->groupBy('user_id', 'name', 'username')->join('@system_user', 'user_id = @system_user.id')->execute()->fetchAll(); to see if it fixes the issue.
@MalteScharenberg Yes, it works. Thank You!
Can you also try if it works if you change it to return self::query()->select('user_id', 'name', 'username')->groupBy('@system_user.id')->join('@system_user', 'user_id = @system_user.id')->execute()->fetchAll();?
@MalteScharenberg No, this time it causes the same error like before
SQLSTATE[42000]: Syntax error or access violation: 1055 'pagekit.pk_blog_post.user_id' isn't in GROUP BY
Think there is something mandatory missing
Fixed in: 77856ae