Contrib.social

SQL Mode Compatibility Issue with Search API & Facets

Open on Drupal.org →
Created on 26 March 2024, 8 months ago
Updated 21 April 2024, 7 months ago

I recently (in drupal 10.2.4) upgraded Search API from version 1.30 to 1.31, which uses a combination of Search API and Facets (2.07) and a full text search field (exposed filter). After the upgrade, the text search functionality stopped working, and the Drupal log shows the following error related to an SQL syntax error:

Drupal\Core\Database\DatabaseExceptionWrapper while trying to create a temporary table: SQLSTATE[42000]: Syntax error or access violation: 1055 Expression #2 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'drupaldb.t.word' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by: CREATE TEMPORARY TABLE "db_temporary_66003d7d3c4b1" Engine=MEMORY SELECT DISTINCT "t"."item_id" AS "item_id" FROM (SELECT "t"."item_id" AS "item_id", CASE WHEN "t"."word" LIKE :like_w1 THEN 1 ELSE 0 END AS "w1" FROM "search_api_db_diergeneesmiddelen_index_text_1" "t" LEFT OUTER JOIN "search_api_db_diergeneesmiddelen_index" "t_2" ON t.item_id = t_2.item_id LEFT OUTER JOIN "search_api_db_diergeneesmiddelen_index_language_with_fallback" "t_3" ON t.item_id = t_3.item_id WHERE ("t"."word" LIKE :db_condition_placeholder_0 ESCAPE '\\') AND ("t"."field_name" IN (:db_condition_placeholder_1)) AND (("t_2"."status" = :db_condition_placeholder_2) AND ("t_2"."type" IN (:db_condition_placeholder_3)) AND ("t_3"."value" IN (:db_condition_placeholder_4, :db_condition_placeholder_5))) GROUP BY "t"."item_id") "t"; Array ( [:db_condition_placeholder_0] => %stromease% [:db_condition_placeholder_1] => title [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => diergeneesmiddel [:db_condition_placeholder_4] => nl [:db_condition_placeholder_5] => und [:like_w1] => %stromease% ) in Drupal\search_api_db\Plugin\search_api\backend\Database->getTemporaryResultsTable() (line 2902 of /data/sites/web/drupal/www/modules/contrib/search_api/modules/search_api_db/src/Plugin/search_api/backend/Database.php).

This seems to be a compatibility issue with the SQL mode ONLY_FULL_GROUP_BY, affecting how queries are handled. The error indicates that a column in the query is neither included in the GROUP BY clause nor wrapped in an aggregate function, which ONLY_FULL_GROUP_BY mode does not permit.

Reverting back to Search API version 1.30 eliminates the issue, indicating that the problem is introduced in version 1.31.

I found a related question in the Facets issue queue but believe this issue stems from the query structure in Search API, prompting me to post here.

Has anyone else encountered this problem, or does anyone have suggestions on resolving this issue?

🐛 Bug report
Status

Fixed

Version

1.0

Component

Database backend

Created by

🇧🇪Belgium yazzbe

Live updates comments and jobs are added and updated live.
Sign in to follow issues

Merge Requests

Comments & Activities

contrib.social Blog FAQ Discussions
Production build 0.71.5 2024