SQL Fehler bei Ausführung Refresh Search Index

Unerwartet meldet Shopware 5 seit Tagen folgenden SQL Fehler bei der Ausführung des Refresh Search Index mit „sw:refresh:search:index“ in der CLI. Ist Euch das bekannt? Habt Ihr einen Hinweis oder Lösungsvorschlag? Danke im Voraus.

Die exakte Fehlermeldung ist:
SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

Log

[2024-08-09 09:00:11 CEST] Creating the search index. This may take a while depending on the shop size.
[2024-08-09 09:00:39 CEST] In DBALException.php line 185:
An exception occurred while executing

‚SELECT STRAIGHT_JOIN si.keywordID, si.fieldID, sk.keyword, (SELECT tableId FROM s_search_fields WHERE s_search_fields.id = si.fieldID) AS tableId, COUNT() AS count_self, (SELECT COUNT() * 0,9 FROM s_articles) AS cnt_s_articles, (SELECT COUNT() * 0,9 FROM s_articles_categories) AS cnt_s_articles_categories, (SELECT COUNT() * 0,9 FROM s_articles_supplier) AS cnt_s_articles_supplier, (SELECT COUNT() * 0,9 FROM s_articles_details) AS cnt_s_articles_details, (SELECT COUNT() * 0,9 FROM s_articles_translations) AS cnt_s_articles_translations, (SELECT COUNT(*) * 0,9 FROM s_articles_attributes) AS cnt_s_articles_attributes FROM s_search_index si INNER JOIN s_search_keywords sk ON si.keywordID=sk.id LEFT JOIN s_articles_categories t2 ON si.elementID = t2.categoryID AND si.fieldID IN (1, 2) LEFT JOIN s_articles t3 ON si.elementID = t3.supplierID AND si.fieldID IN (6) LEFT JOIN s_articles_details t4 ON si.elementID = t4.id AND si.fieldID IN (5) GROUP BY si.keywordID, si.fieldID HAVING (tableId = 1 AND count_self > cnt_s_articles) OR (tableId = 2 AND count_self > cnt_s_articles_categories) OR (tableId = 3 AND count_self > cnt_s_articles_supplier) OR (tableId = 4 AND count_self > cnt_s_articles_details) OR (tableId = 5 AND count_self > cnt_s_articles_translations) OR (tableId = 6 AND count_self > cnt_s_articles_attributes)‘

[2024-08-09 09:00:39 CEST] SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)
[2024-08-09 09:00:39 CEST] sw:refresh:search:index [–clear-table]
[2024-08-09 09:00:39 CEST] Exit-Code: 1

Das geht in die Richtung sql - I have a message: ERROR 1241 (21000): Operand should contain 1 column(s) Mysql - Stack Overflow
aber ohne ein vollständiges und leserliches SQL sehe ich da nichts.

1 Like

@sacrofano danke für Feedback und berechtigtem Hinweis. Ich habe das SQL jetzt leserlicher gemacht.

Hier müsste count(*) * 0.9 stehen.

So funktioniert das SQL:

SELECT STRAIGHT_JOIN si.keywordID,
 si.fieldID,
 sk.keyword, 
(SELECT tableId FROM s_search_fields WHERE s_search_fields.id = si.fieldID) AS tableId, 
COUNT(*) AS count_self, 
(SELECT COUNT(*) * 0.9 FROM s_articles) AS cnt_s_articles, 
(SELECT COUNT(*) * 0.9 FROM s_articles_categories) AS cnt_s_articles_categories,
(SELECT COUNT(*) * 0.9 FROM s_articles_supplier) AS cnt_s_articles_supplier, 
(SELECT COUNT(*) * 0.9 FROM s_articles_details) AS cnt_s_articles_details, 
(SELECT COUNT(*) * 0.9 FROM s_articles_translations) AS cnt_s_articles_translations, 
(SELECT COUNT(*) * 0.9 FROM s_articles_attributes) AS cnt_s_articles_attributes 
FROM s_search_index si INNER JOIN s_search_keywords sk ON si.keywordID=sk.id 
LEFT JOIN s_articles_categories t2 ON si.elementID = t2.categoryID AND si.fieldID IN (1, 2) 
LEFT JOIN s_articles t3 ON si.elementID = t3.supplierID AND si.fieldID IN (6) 
LEFT JOIN s_articles_details t4 ON si.elementID = t4.id AND si.fieldID IN (5) 
GROUP BY si.keywordID, si.fieldID 
HAVING (tableId = 1 AND count_self > cnt_s_articles) OR (tableId = 2 AND count_self > cnt_s_articles_categories) OR (tableId = 3 AND count_self > cnt_s_articles_supplier) OR (tableId = 4 AND count_self > cnt_s_articles_details) OR (tableId = 5 AND count_self > cnt_s_articles_translations) OR (tableId = 6 AND count_self > cnt_s_articles_attributes)

1 Like