Neu SQL in der Shop Datenbank legt den JTL Shop (5.1.5) lahm

[gnarx]

Da momentan sich der Support ( Ticket) noch nicht gemeldet hat, hier mal gefragt ob jemand das kennt:

auf Ihrem Server laufen erneut Datenbankabfragen endlos bis zur kompletten Überlastung und den damit einhergehenden Dienstausfällen des Servers.
Exemplarisch dafür ein Zugriff auf die Datenbank:

SELECT tseo.cSeo, ssMerkmal.kHersteller, ssMerkmal.cName, ssMerkmal.nSortNr, ssMerkmal.cBildPfad, COUNT(*) AS nAnzahl FROM (SELECT thersteller.kHersteller, thersteller.cName, thersteller.nSortNr, thersteller.cBildPfad, tartikel.kArtikel FROM tartikel #JOIN from JTL\Filter\States\BaseCharacteristic::getSQLJoin JOIN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert = 113 GROUP BY tartikelmerkmal.kArtikel ) AS tmerkmaljoin ON tmerkmaljoin.kArtikel = tartikel.kArtikel #join from JTL\Filter\Items\Characteristic::getSQLJoin JOIN tartikelmerkmal ON tartikel.kArtikel = tartikelmerkmal.kArtikel #JOIN from JTL\Filter\States\BaseManufacturer::getOptions JOIN thersteller ON tartikel.kHersteller = thersteller.kHersteller #product visiblity join from getBaseQuery LEFT JOIN tartikelsichtbarkeit ON tartikel.kArtikel = tartikelsichtbarkeit.kArtikel AND tartikelsichtbarkeit.kKundengruppe = 1 WHERE #condition from filter mf tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (6600)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 4 x AND tartikelsichtbarkeit.kArtikel IS NULL AND tartikel.kVaterArtikel = 0 AND (tartikel.cLagerBeachten != 'Y' OR tartikel.fLagerbestand > 0 OR (tartikel.cLagerVariation = 'Y' AND ( SELECT MAX(teigenschaftwert.fLagerbestand) FROM teigenschaft INNER JOIN teigenschaftwert ON teigenschaftwert.kEigenschaft = teigenschaft.kEigenschaft WHERE teigenschaft.kArtikel = tartikel.kArtikel ) > 0 ) OR tartikel.cLagerKleinerNull = 'Y') #default group by GROUP BY tartikel.kArtikel #limit sql ORDER BY tartikel.nSort, tartikel.cName) AS ssMerkmal LEFT JOIN tseo ON tseo.kKey = ssMerkmal.kHersteller AND tseo.cKey = 'kHersteller' AND tseo.kSprache = 1 GROUP BY ssMerkmal.kHersteller ORDER BY ssMerkmal.nSortNr, ssMerkmal.cName

 

[waagenwelt]

Hallo gnarx,

ja, kennen wir:

https://forum.jtl-software.de/threads/sql-fehler-im-logbuch.193564/#post-1035889

Viele Grüße
Stefan

 

[webksde]

Wir haben bei einem größeren Shop exakt dasselbe Problem (JTL-Shop4). Vermutlich seit Umstellung auf MariaDB 10.5.

Bei uns ist es folgender Query mit 2 Subselects:

SELECT thersteller.kHersteller, thersteller.cName, thersteller.cHomepage, thersteller.nSortNr,
thersteller.cBildpfad, therstellersprache.cMetaTitle, therstellersprache.cMetaKeywords,
therstellersprache.cMetaDescription, therstellersprache.cBeschreibung, tseo.cSeo
FROM thersteller
LEFT JOIN therstellersprache
ON therstellersprache.kHersteller = thersteller.kHersteller
AND therstellersprache.kSprache = 1
LEFT JOIN tseo
ON tseo.kKey = thersteller.kHersteller
AND tseo.cKey = 'kHersteller'
AND tseo.kSprache = 1
WHERE EXISTS (
SELECT 1
FROM tartikel
WHERE tartikel.kHersteller = thersteller.kHersteller

AND NOT EXISTS (
SELECT 1 FROM tartikelsichtbarkeit
WHERE tartikelsichtbarkeit.kArtikel = tartikel.kArtikel
AND tartikelsichtbarkeit.kKundengruppe = 1)
)
ORDER BY thersteller.nSortNr, thersteller.cName

Eine starke Verbesserung der Situation hat eine Umwandlung aller Tabellen zu InnoDB gebracht. Kernproblem & Ursache scheinen hier aber aus meiner Sicht entweder die Subselects zu sein und / oder der schlechte Umgang von MariaDB 10.5 mit Tabellen ohne Auto Increment Primary Key.

Mit MyISAM laufen die Tabellen in einen DEADLOCK (wg. Tabellen-Locking) und aus die Maus

Mit InnoDB hängen diese Abfragen trotzdem noch viel zu lange (ca 4s), aber immerhin löst sich der Lock schnell auf.


Nachfolgend das ANALYZE Ergebnis:

ANALYZE SELECT thersteller.kHersteller, thersteller.cName, thersteller.cHomepage, thersteller.nSortNr,
thersteller.cBildpfad, therstellersprache.cMetaTitle, therstellersprache.cMetaKeywords,
therstellersprache.cMetaDescription, therstellersprache.cBeschreibung, tseo.cSeo
FROM thersteller
LEFT JOIN therstellersprache
ON therstellersprache.kHersteller = thersteller.kHersteller
AND therstellersprache.kSprache = 1
LEFT JOIN tseo
ON tseo.kKey = thersteller.kHersteller
AND tseo.cKey = 'kHersteller'
AND tseo.kSprache = 1
WHERE EXISTS (
SELECT 1
FROM tartikel
WHERE tartikel.kHersteller = thersteller.kHersteller

AND NOT EXISTS (
SELECT 1 FROM tartikelsichtbarkeit
WHERE tartikelsichtbarkeit.kArtikel = tartikel.kArtikel
AND tartikelsichtbarkeit.kKundengruppe = 1)
)
ORDER BY thersteller.nSortNr, thersteller.cName;

1 PRIMARY thersteller ALL PRIMARY,kHersteller_cName NULL NULL NULL 394 394.00 100.00 100.00 Using filesort
1 PRIMARY <subquery2> eq_ref distinct_key distinct_key 4 func 1 0.47 100.00 100.00
1 PRIMARY therstellersprache eq_ref PRIMARY,kHersteller,kSprache PRIMARY 8 quadcom_db1.thersteller.kHersteller,const 1 0.98 100.00 100.00 Using where
1 PRIMARY tseo eq_ref cKey,cKey_kKey,kKey1,cKey1 cKey 263 const,quadcom_db1.thersteller.kHersteller,const 1 1.00 100.00 100.00 Using where; Using index
2 MATERIALIZED tartikel index kHersteller kHersteller 4 NULL 1088639 1139461.00 100.00 99.91 Using where; Using index
3 DEPENDENT SUBQUERY tartikelsichtbarkeit eq_ref PRIMARY PRIMARY 5 quadcom_db1.tartikel.kArtikel,const 1 0.00 100.00 100.00 Using index

Möglicherweise zusammenhängend mit:
- https://stackoverflow.com/questions/57854233/mysql-chooses-completely-wrong-index
- https://mariadb.com/kb/en/exists-to-in-optimization/
- https://stackoverflow.com/questions...-exists-subquery-from-mysql-5-7-to-mariadb-10
- https://dba.stackexchange.com/quest...ely-increased-after-upgrade-from-10-2-to-10-4

 

[gnarx]

Wie macht ihr das denn nun, habt ihr irgendwas außer immer die hängen gebliebenen zu killen?
Ich hab dem Support alles geschickt auch Zugänge und die Analyse von @webksde aber die melden sich nicht.

Wir sind jetzt seid der Woche vom Black Friday fast Shoplos und wegen anderer Fehler bis letzte Woche einstelllos bei ebay.
Wir haben sogar extra einen Management Server genommen der richtig Geld kostet weil wir dachten es liegt daran.

 

[togo]

Wie macht ihr das denn nun, habt ihr irgendwas außer immer die hängen gebliebenen zu killen?
Ich hab dem Support alles geschickt auch Zugänge und die Analyse von @webksde aber die melden sich nicht.

Wir sind jetzt seid der Woche vom Black Friday fast Shoplos und wegen anderer Fehler bis letzte Woche einstelllos bei ebay.
Wir haben sogar extra einen Management Server genommen der richtig Geld kostet weil wir dachten es liegt daran.

Wir sind der Kunde von webksde und ich habe gestern dazu ein Ticket angelegt. Habe soeben dort nochmal angerufen und diesen Foren-Beitrag verknüpfen lassen. Es wird nun mit höherer Prio bearbeitet.

 

[gnarx]

Wow super das du auch ein Ticket angelegt hast. Hier meine Ticketnummer: Ticket#2022121310001553
Komisch ist nur das das bei anderen Kunden von mir nicht ist.

 

[gnarx]

Hier noch der Tipp um den Shop ein bisserl am Leben zu erhalten:

1. Einloggen beim Provider und die Shop Datenbank per phpMyAdmin
2. Oben dann auf die Datenbank klicken (Server: local host)
3. Dort auf "Status" (oben in den Tabs)
4. Alle Prozesse beenden die mit "SELECT tseo.cSeo," beginnen. Unter Umständen auch die "ssMerkmal" enthalten, normal und die mit Max.
Oft kille ich alle Prozesse. Dann immer mal auf Aktualisieren klicken bist alle raus hast 2 (SHOW PROCESSLIST und Sleep) bleiben immer drinne und sind nicht zu beenden.
Dein Shop läuft dann wieder ein paar Stunden bis die Liste wieder voll ist bzw. Prozesse zu lange laufen.

 

[gnarx]

Grade Bescheid bekommen das der Vorgang an die Entwicklungsabteilung weiter geleitet wurde.
Ich habe das vor 7 Tagen gemeldet sorry JTL seht ihr nicht was sowas bewirken kann?

Wir reden, in einer sehr angespannten Zeit (Umsatzrückgänge), hier von 5 stelligen Umsatzverlusten und das bei gestiegen Preisen und höherem Mindestlohn.
Auf diese Art kann man Kunden verlieren ohne das die sich nach anderen Systemen umschauen, die brauchen dann keine Abwicklungssoftware mehr.

 

[gnarx]

Ist bei euch das Problem weg? Bei uns ja ohne Update aber leider ohne Benachrichtigung seitens JTL, Ticket wurde einfach geschlossen.

 

[BoMan]

Problem besteht hier weiterhin bei einem neu installierten Shop 5.1.5, 10.3.37-MariaDB-0ubuntu0.20.04.1 - Ubuntu 20.04
Andere Shopversionen scheinen nicht betroffen zu sein. Hinzuzufügen wäre eventuell noch, dass der Shop sehr viel mit Merkmalen arbeitet.

Beispielhafte Query:

SELECT tseo.cSeo, ssMerkmal.kHersteller, ssMerkmal.cName, ssMerkmal.nSortNr, ssMerkmal.cBildPfad, COUNT(*) AS nAnzahl FROM (SELECT thersteller.kHersteller, thersteller.cName, thersteller.nSortNr, thersteller.cBildPfad, tartikel.kArtikel FROM tartikel #JOIN from JTL\Filter\States\BaseCharacteristic::getSQLJoin JOIN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert = 631 GROUP BY tartikelmerkmal.kArtikel ) AS tmerkmaljoin ON tmerkmaljoin.kArtikel = tartikel.kArtikel #join from JTL\Filter\Items\Characteristic::getSQLJoin JOIN tartikelmerkmal ON tartikel.kArtikel = tartikelmerkmal.kArtikel #JOIN from JTL\Filter\States\BaseManufacturer::getOptions JOIN thersteller ON tartikel.kHersteller = thersteller.kHersteller #join from SORT by bestseller LEFT JOIN tbestseller ON tartikel.kArtikel = tbestseller.kArtikel #product visiblity join from getBaseQuery LEFT JOIN tartikelsichtbarkeit ON tartikel.kArtikel = tartikelsichtbarkeit.kArtikel AND tartikelsichtbarkeit.kKundengruppe = 1 WHERE tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (594)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 105 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (614)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 10,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (615)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 11,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (616)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 12,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (617)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 13,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (618)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 14,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (619)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 15,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (620)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 16,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (621)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 17,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (626)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 21,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (627)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 23,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (634)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 30,0 mm AND tartikelmerkmal.kArtikel IN (SELECT kArtikel FROM tartikelmerkmal WHERE kMerkmalWert IN (648)) #condition from JTL\Filter\Items\Characteristic::getSQLCondition 275 mm AND tartikelsichtbarkeit.kArtikel IS NULL AND tartikel.kVaterArtikel = 0 #default group by GROUP BY tartikel.kArtikel #limit sql ORDER BY tbestseller.fAnzahl DESC, tartikel.cName) AS ssMerkmal LEFT JOIN tseo ON tseo.kKey = ssMerkmal.kHersteller AND tseo.cKey = 'kHersteller' AND tseo.kSprache = 1 GROUP BY ssMerkmal.kHersteller ORDER BY ssMerkmal.nSortNr, ssMerkmal.cName

Temporärer Workaround ist es, die Prozesse dieses Users nach einer gewissen Zeit zu beenden:

#!/bin/bash
DBUSER=root
DBPASS=pass
CUSTOMERDBUSER=kundendatenbanknutzer
TIME=50

mysql -u $DBUSER -p$DBPASS -e "
SELECT id
FROM information_schema.processlist
WHERE user='$CUSTOMERDBUSER' and time>$TIME
" | while read id;
do
    if [[ "id" == "$id" ]]
    then
        continue
    fi
    echo "kill $id";
    mysql -u $DBUSER -p$DBPASS -e "kill $id";
done

Das ganze als cron alle paar Minuten.

Nicht schön aber funktional und lässt MariaDB "nur" noch 20 Kerne auslasten. Ich weiß noch nicht was die Query macht, deshalb Verwendung auf eigene Gefahr.

 

[gnarx]

Danke für den Tipp. Ich weiß auch nicht warum es jetzt auf einmal bei uns läuft ohne das was gemacht wurde.
Wir haben auch sehr viele Merkmale über 3 Millionen Einträge in der Shop Datenbank.

Shopversion 5.1.5 - Template-Version 1.1.2 - DBVersion v5.1.5 - PHP-Version 7.4.33 (Linux) - 5.5.5-10.5.16-MariaDB-1:10.5.16+maria-focal-lc

 

[gnarx]

Laut JTL haben die uns einen Fix im Shop eingespielt. Leider kann ich nicht mehr feststellen ob unser Shop schon vorher wieder lief.
Der Fix soll im neuen Shop 5.2 enthalten sein.

 

[301Moved]

Weiß jmd, wie der Fix aussieht? Dann würde ich einmal Bedarf anmelden wollen, da vermutlich ein System vom selben Fehler betroffen sein könnte

 

[gnarx]

Nein leider nicht. Kannst dich ja auf diesen Thread berufen, da gibt es beim Ticket erstellen nen Kasten für.

 

[301Moved]

Ja, Ticket ist bereits eröffnet. Aber dachte, wenn der Fix bekannt ist, braucht sich der Support vielleicht gar nicht erst die Arbeit machen

 

[bzng]

Ja, Ticket ist bereits eröffnet. Aber dachte, wenn der Fix bekannt ist, braucht sich der Support vielleicht gar nicht erst die Arbeit machen

Hey, hat sich bei dir inzw. was getan bzw. hast du einen Fix von JTL bekommen und/oder konntest du es eingrenzen?

 

[301Moved]

Ja, hatte Rückmeldung auf n Ticket bekommen, füge ich einfach mal ein. Letztlich war es Punkt 3 in dem Projekt:

1. Führen Sie bitte Serverseitig folgendes Befehl aus:
mysql> set global optimizer_search_depth = 0;

2. Bitte legen Sie folgendes Indizes an, in dem Sie folgende SQL Abfrage einmal über PphMyAdmin absetzen:
ALTER TABLE tartikelattribut ADD UNIQUE KEY idx_cName_kArtikel_uq(cName, kArtikel);

3. Bitte übernehmen Sie folgende Änderung als Hotfix:
https://gitlab.com/jtl-software/jtl-shop/core/-/commit/fdd069b41142c8543ff0cd5faabdfc07c35fa6bc