Problem/Motivation
I get an SQL error with white screen when adding the relevant content block to the page.
The website encountered an unexpected error. Please try again later.
Drupal\Core\Database\DatabaseExceptionWrapper: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of ORDER BY clause is not in GROUP BY clause and contains nonaggregated column 'drupal10.n.created' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: SELECT "n"."nid" AS "nid", COUNT(*) AS "cnt" FROM "node_field_data" "n" INNER JOIN "taxonomy_index" "ti" ON n.nid = ti.nid WHERE ("n"."status" = :db_condition_placeholder_0) AND ("n"."type" IN (:db_condition_placeholder_1)) AND ("ti"."tid" IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6)) AND ("n"."nid" NOT IN (:db_condition_placeholder_7)) GROUP BY "n"."nid" ORDER BY "cnt" DESC, "n"."created" DESC, "n"."nid" DESC LIMIT 10 OFFSET 0; Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => post [:db_condition_placeholder_2] => 14 [:db_condition_placeholder_3] => 17 [:db_condition_placeholder_4] => 686 [:db_condition_placeholder_5] => 711 [:db_condition_placeholder_6] => 730 [:db_condition_placeholder_7] => 50 ) in Drupal\relevant_content\RelevantContentService->findRelevantContent() (line 125 of modules/contrib/relevant_content/src/RelevantContentService.php).
Drupal\Core\Database\StatementWrapper->execute(Array, Array) (Line: 765)
Drupal\Core\Database\Connection->query('SELECT "n"."nid" AS "nid", COUNT(*) AS "cnt"
FROM
{node_field_data} "n"
INNER JOIN {taxonomy_index} "ti" ON n.nid = ti.nid
WHERE ("n"."status" = :db_condition_placeholder_0) AND ("n"."type" IN (:db_condition_placeholder_1)) AND ("ti"."tid" IN (:db_condition_placeholder_2, :db_condition_placeholder_3, :db_condition_placeholder_4, :db_condition_placeholder_5, :db_condition_placeholder_6)) AND ("n"."nid" NOT IN (:db_condition_placeholder_7))
GROUP BY "n"."nid"
ORDER BY "cnt" DESC, "n"."created" DESC, "n"."nid" DESC
LIMIT 10 OFFSET 0', Array, Array) (Line: 525)
Drupal\Core\Database\Query\Select->execute() (Line: 125)
Drupal\relevant_content\RelevantContentService->findRelevantContent(Array, Array, Array, 10) (Line: 68)
Drupal\relevant_content\Plugin\Block\RelevantContentBlock->build() (Line: 171)
Drupal\block\BlockViewBuilder::preRender(Array)
call_user_func_array(Array, Array) (Line: 101)
Drupal\Core\Render\Renderer->doTrustedCallback(Array, Array, 'Render #pre_render callbacks must be methods of a class that implements \Drupal\Core\Security\TrustedCallbackInterface or be an anonymous function. The callback was %s. See https://www.drupal.org/node/2966725', 'exception', 'Drupal\Core\Render\Element\RenderCallbackInterface') (Line: 788)
Drupal\Core\Render\Renderer->doCallback('#pre_render', Array, Array) (Line: 374)
Drupal\Core\Render\Renderer->doRender(Array) (Line: 446)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 477)
Drupal\Core\Template\TwigExtension->escapeFilter(Object, Array, 'html', NULL, 1) (Line: 88)
__TwigTemplate_158714afbd97a8420c25ab18db8aef73->doDisplay(Array, Array) (Line: 394)
Twig\Template->displayWithErrorHandling(Array, Array) (Line: 367)
Twig\Template->display(Array) (Line: 379)
Twig\Template->render(Array, Array) (Line: 40)
Twig\TemplateWrapper->render(Array) (Line: 53)
twig_render_template('themes/custom/aaas_scidip/templates/layout/page.html.twig', Array) (Line: 372)
Drupal\Core\Theme\ThemeManager->render('page', Array) (Line: 433)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 477)
Drupal\Core\Template\TwigExtension->escapeFilter(Object, Array, 'html', NULL, 1) (Line: 80)
__TwigTemplate_739750fbae51c7503865e2ec95599a07->doDisplay(Array, Array) (Line: 394)
Twig\Template->displayWithErrorHandling(Array, Array) (Line: 367)
Twig\Template->display(Array) (Line: 379)
Twig\Template->render(Array, Array) (Line: 40)
Twig\TemplateWrapper->render(Array) (Line: 53)
twig_render_template('core/themes/stable9/templates/layout/html.html.twig', Array) (Line: 372)
Drupal\Core\Theme\ThemeManager->render('html', Array) (Line: 433)
Drupal\Core\Render\Renderer->doRender(Array, ) (Line: 204)
Drupal\Core\Render\Renderer->render(Array) (Line: 158)
Drupal\Core\Render\MainContent\HtmlRenderer->Drupal\Core\Render\MainContent\{closure}() (Line: 580)
Drupal\Core\Render\Renderer->executeInRenderContext(Object, Object) (Line: 159)
Drupal\Core\Render\MainContent\HtmlRenderer->renderResponse(Array, Object, Object) (Line: 90)
Drupal\Core\EventSubscriber\MainContentViewSubscriber->onViewRenderArray(Object, 'kernel.view', Object)
call_user_func(Array, Object, 'kernel.view', Object) (Line: 111)
Drupal\Component\EventDispatcher\ContainerAwareEventDispatcher->dispatch(Object, 'kernel.view') (Line: 168)
Symfony\Component\HttpKernel\HttpKernel->handleRaw(Object, 1) (Line: 74)
Symfony\Component\HttpKernel\HttpKernel->handle(Object, 1, 1) (Line: 58)
Drupal\Core\StackMiddleware\Session->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\KernelPreHandle->handle(Object, 1, 1) (Line: 106)
Drupal\page_cache\StackMiddleware\PageCache->pass(Object, 1, 1) (Line: 85)
Drupal\page_cache\StackMiddleware\PageCache->handle(Object, 1, 1) (Line: 48)
Drupal\Core\StackMiddleware\ReverseProxyMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\NegotiationMiddleware->handle(Object, 1, 1) (Line: 51)
Drupal\Core\StackMiddleware\StackedHttpKernel->handle(Object, 1, 1) (Line: 686)
Drupal\Core\DrupalKernel->handle(Object) (Line: 19)
Steps to reproduce
- Create a Relevant Content Preset entity:
- Enabled Content Types: Post
- Enabled Vocabularies: Topics
- Place the block in page region, restrict display to node type 'Post'
- Visit a node type 'Post', and i get this white screen with the aforementioned SQL error
Proposed resolution
Would probably need to add aggregation on the column.
Note that when I try the SQL INIT command in the db connection settings as found in this
issue thread
🐛
GROUP BY clause and contains nonaggregated column and incompatible with sql_mode=only_full_group_by
Closed: works as designed
, the error goes away. However, this is changing the default SQL mode which may have undesirable side effects.
'init_commands' => [
'sql_mode' => "SET sql_mode = 'REAL_AS_FLOAT,PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,STRICT_TRANS_TABLES,STRICT_ALL_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO'"
],