QueueController code incompatible with only_full_group_by SQL mode
Closed this issue · 4 comments
It appears that the admin backend at /admin/queue is incompatilbe with the only_full_group_by SQL mode. Opening that page in version 4.3.0 of this plugin resulted in this error being generated:
Error: [PDOException] SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'okbvtfr_cri.QueueProcesses.id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by (/home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php:38)
The line that triggers this contains:
$servers = $this->QueueProcesses->find()->distinct(['server'])->find('list', ['keyField' => 'server', 'valueField' => 'server'])->toArray();
But that seems to only generate this query, which doesn't mention QueueProcesses.id:
SELECT QueueProcesses.server AS `QueueProcesses__server` FROM queue_processes QueueProcesses GROUP BY server
So I'm confused about how the error is being generated and how to correct it. (other than by disabling only_full_group_by mode)
Full stack trace:
#0 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Database/Statement/MysqlStatement.php(38): PDOStatement->execute(NULL)
#1 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Database/Connection.php(333): Cake\Database\Statement\MysqlStatement->execute()
#2 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Core/Retry/CommandRetry.php(67): Cake\Database\Connection->Cake\Database\{closure}()
#3 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Database/Connection.php(336): Cake\Core\Retry\CommandRetry->run(Object(Closure))
#4 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Database/Query.php(218): Cake\Database\Connection->run(Object(Cake\ORM\Query))
#5 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/ORM/Query.php(1141): Cake\Database\Query->execute()
#6 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(310): Cake\ORM\Query->_execute()
#7 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/ORM/Query.php(1088): Cake\ORM\Query->_all()
#8 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Datasource/QueryTrait.php(327): Cake\ORM\Query->all()
#9 /home/okbvtfr/public_html/cri/vendor/dereuromark/cakephp-queue/src/Controller/Admin/QueueController.php(46): Cake\ORM\Query->toArray()
#10 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Controller/Controller.php(609): Queue\Controller\Admin\QueueController->index()
#11 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/ActionDispatcher.php(120): Cake\Controller\Controller->invokeAction()
#12 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/ActionDispatcher.php(94): Cake\Http\ActionDispatcher->_invoke(Object(Queue\Controller\Admin\QueueController))
#13 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/BaseApplication.php(234): Cake\Http\ActionDispatcher->dispatch(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#14 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Runner.php(65): Cake\Http\BaseApplication->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response), Object(Cake\Http\Runner))
#15 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Routing/Middleware/RoutingMiddleware.php(162): Cake\Http\Runner->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#16 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Runner.php(65): Cake\Routing\Middleware\RoutingMiddleware->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response), Object(Cake\Http\Runner))
#17 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Routing/Middleware/AssetMiddleware.php(88): Cake\Http\Runner->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#18 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Runner.php(65): Cake\Routing\Middleware\AssetMiddleware->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response), Object(Cake\Http\Runner))
#19 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Error/Middleware/ErrorHandlerMiddleware.php(96): Cake\Http\Runner->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#20 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Runner.php(65): Cake\Error\Middleware\ErrorHandlerMiddleware->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response), Object(Cake\Http\Runner))
#21 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Runner.php(51): Cake\Http\Runner->__invoke(Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#22 /home/okbvtfr/public_html/cri/vendor/cakephp/cakephp/src/Http/Server.php(97): Cake\Http\Runner->run(Object(Cake\Http\MiddlewareQueue), Object(Cake\Http\ServerRequest), Object(Cake\Http\Response))
#23 /home/okbvtfr/public_html/cri/webroot/index.php(42): Cake\Http\Server->run()
#24 {main}
Are you able to PR a fix?
I'd love to, but I'm stuck on the error complaining about how QueueProcesses.id is selected when it doesn't appear to actually be selected. When I run ->sql() on that query, I get SELECT QueueProcesses.server AS QueueProcesses__server FROM queue_processes QueueProcesses GROUP BY server. I was hoping you or other contributors may be able to help out.
Trying to step through execution with a debugger to find the cause has been unsuccessful for me so far.
We might have to rewrite the query.
Does anyone have an idea for a fix here?