# SQL Fehler bei Ausführung Refresh Search Index

**URL:** <https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777>\
**Category:** Shopware 5\
**Created:** [9. August 2024 um 08:18 UTC](https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777 "2024-08-09T08:18:23Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![vismagine](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/vismagine/32/24302_2.png) [@vismagine](https://forum.shopware.com/u/vismagine)\
**Post date:** [9. August 2024 um 08:18 UTC](https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777/1 "2024-08-09T08:18:23Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![sacrofano](https://avatars.discourse-cdn.com/v4/letter/s/65b543/32.png) [@sacrofano](https://forum.shopware.com/u/sacrofano)\
**Post date:** [9. August 2024 um 08:41 UTC](https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777/2 "2024-08-09T08:41:17Z")

</div>

Das geht in die Richtung [sql - I have a message: ERROR 1241 (21000): Operand should contain 1 column(s) Mysql - Stack Overflow](https://stackoverflow.com/questions/62706065/i-have-a-message-error-1241-21000-operand-should-contain-1-columns-mysql)  
aber ohne ein vollständiges und leserliches SQL sehe ich da nichts.

---

<div class="post-metadata">

**Author:** ![vismagine](https://dub1.discourse-cdn.com/flex013/user_avatar/forum.shopware.com/vismagine/32/24302_2.png) [@vismagine](https://forum.shopware.com/u/vismagine)\
**Post date:** [9. August 2024 um 10:01 UTC](https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777/3 "2024-08-09T10:01:39Z")

</div>

> [@sacrofano](#):
>
> leserliches SQL

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

---

<div class="post-metadata">

**Author:** ![sacrofano](https://avatars.discourse-cdn.com/v4/letter/s/65b543/32.png) [@sacrofano](https://forum.shopware.com/u/sacrofano)\
**Post date:** [9. August 2024 um 10:33 UTC](https://forum.shopware.com/t/sql-fehler-bei-ausfuehrung-refresh-search-index/104777/4 "2024-08-09T10:33:19Z")

</div>

> [@vismagine](#):
>
> _COUNT(_) \* 0,9

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

So funktioniert das SQL:

```auto
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)

```