Shopware SQL Trickkiste

Gültig ab Version:
5.2.0

Vorwort

Achtung: Es wird empfohlen diese Statements nur mit den nötigen Vorkenntnissen auszuführen. In jedem Fall sollte im Vorfeld ein vollständiges Backup der Datenbank angelegt werden um auf dieses zurückgreifen zu können.

Achtung! Die hier zur Verfügung gestellten Informationen wurden zum Zeitpunkt der Veröffentlichung dieses Artikels getestet, können sich zwischenzeitlich aber geändert haben. Die Durchführung dieses Tutorials geschieht auf eigene Gefahr! Lege Dir vor der Durchführung ein Backup an, sodass Du dieses im Zweifel zurückspielen kannst. Beachte, dass es sich hier um Anpassungen von Shopware handelt und der Inhalt dieses Tutorials daher nicht offiziell supportet wird!

In diesem Artikel findest Du einige nützliche SQL-Statements, die Dir bei bestimmten Fehlern oder Anforderungen helfen. Für die Verwaltung der Datenbank empfehlen wir als Administrationsoberfläche phpMyAdmin. Diese ermöglicht die graphische Darstellung der Datenbank und erleichtert die Ausführung der Befehle. Natürlich können die unten genannten Beispiele mit jedem anderen MySQL-Client durchgeführt werden.

Kundendaten

Doppelte Kundennummern filtern

Sofern Kundennummern im System doppelt vorhanden sind, kann es zu Fehlern bzw. inkonsistenten Datensätzen führen. Mit diesem Statement kannst Du ganz leicht die doppelten Kundennummern anzeigen lassen.

 
SELECT customernumber, COUNT( customernumber ) AS anzahl
FROM `s_user_billingaddress`
GROUP BY `customernumber`
HAVING (COUNT( customernumber ) >1
)
 

Artikel Daten

Doppelte Artikelnummern filtern

Über das Backend ist die Anlage von Artikeln mit bereits bestehender Artikelnummer nicht möglich. Schnittstellen oder Import-Skripte können diesen Mechanismus jedoch aushebeln, weshalb es in solchen Fällen zu Fehlern kommt. Mit diesem Statement lassen sich doppelt vorhandene Artikel schnell herausfinden:

 
SELECT ordernumber, COUNT( ordernumber ) AS anzahl
 
FROM `s_articles_details`
GROUP BY `ordernumber`
HAVING (COUNT( ordernumber ) >1
);
 

Doppelte SEO-URL's in der s_core_rewrite_urls filtern

Falls ein bestimmter SEO-Link in Deinem Shop bzw. Subshop nicht generiert werden kann, ist die Ursache oft ein bereits vorhandener Eintrag. Dieses Statement sortiert die Einträge und zeigt Dir mögliche Duplikate.

 
SELECT *
FROM `s_core_rewrite_urls`
WHERE main =1
GROUP BY `org_path` , `main` , `subshopID`
HAVING COUNT( * ) >1
 

Aktive Produkte filtern, welche im Frontend dennoch nicht angezeigt werden

In manchen Fällen (oft auch durch fehlerhafte Synchronisierung von der Warenwirtschaft) werden im Backend aktive Artikel im Frontend nicht ausgeben. Hintergrund hierfür kann sein, dass der Eintrag in der "s_articles" Tabelle die Spalte "active = 1" hat, jedoch der assoziierte Eintrag in der "s_articles_details" diese Spalte auf "active = 0" hat. Mit diesem Statement lassen sich solche Artikel anzeigen.

 
SELECT a.name, ad.ordernumber, a.active AS aktiv1, ad.active AS aktiv2
FROM `s_articles` AS a 
JOIN s_articles_details AS ad 
ON a.id = ad.articleID
WHERE a.active <> ad.active
 

Defekte Artikel ohne Eintrag in der s_article_details ausgeben lassen

Durch Schnittstellen oder Plugins können Artikel defekt in der Datenbank angelegt sein. Ein Indiz hierfür ist der fehlende Eintrag in der "s_articles_details". Mit diesem Statement lassen sich solche Artikel anzeigen. Diese sollten umgehend neu angelegt werden.

 
SELECT *
FROM s_articles a
LEFT JOIN
s_articles_details ad
ON ad.articleID = a.id
WHERE ad.id IS NULL
 

Kaputte Varianten / Varianten-Leichen entfernen

Mit diesem Statement lassen sich nicht (mehr) zugeordnete Varianten Optionen aufzeigen. Diese können für Dateninkonsistenzen und fehlende Darstellungen im Frontend sorgen.

 
DELETE d, at
-- SELECT d.*
FROM s_articles_details d
JOIN s_articles a
ON d.articleID = a.id
AND a.configurator_set_id IS NOT NULL
JOIN s_articles_attributes at
ON d.id = at.articledetailsID
LEFT JOIN s_article_configurator_option_relations cr
ON cr.article_id = d.id
WHERE cr.id IS NULL
AND d.articleID = a.id;
 
DELETE r
-- SELECT r.*
FROM `s_article_configurator_option_relations` r
LEFT  JOIN s_articles_details d ON d.id = r.article_id
WHERE d.id IS NULL;
 

Varianten Vorauswahl setzen

Mit diesem Statement werden Variantenartikel, die keine Vorauswahl haben, automatisch mit der ersten Varianten ausgewählt. Ohne Vorauswahl ist keine Anzeige des Variantenartikels im Frontend möglich.

 
UPDATE s_articles a
LEFT JOIN s_articles_details d
ON d.id = a.main_detail_id
SET a.main_detail_id = (
  SELECT id FROM s_articles_details WHERE articleID = a.id LIMIT 1
)
WHERE d.id IS NULL;
UPDATE s_articles a, s_articles_details d
SET d.kind = 1
WHERE d.id = a.main_detail_id;
 

Fehlende Kategorie Zuweisungen in der s_article_categories_ro beheben

Sofern diese Zuweisung fehlt, werden Artikel im Frontend als "nicht verfügbar" angezeigt - mit diesem Statement wird die besagte Tabelle für alle Artikel neu befüllt. Gerade bei vielen Artikeln ist dies performanter als den Kategorie-Baum neu aufzubauen.

 
INSERT IGNORE INTO `s_articles_categories_ro` (`articleID`,
`categoryID`, `parentCategoryID`)
 
SELECT ac.articleID, c2.id AS categoryID, c.id AS parentCategoryID
FROM s_articles_categories ac
 
JOIN s_categories c
ON c.id = ac.categoryID
 
JOIN s_categories c2
ON c2.id = c.id
OR c.path LIKE CONCAT('%|', c2.id, '|%')
 
ORDER BY  ac.articleID, c.id, c2.id
 

Vorschaubilder für Vorschlagssuche korrigieren

Hiermit können fehlende Vorschaubilder in verschiedenen Bereichen (Artikelansicht im Backend, Vorschlagsuche, etc.) korrigiert werden. Dies ist in manchen Fällen nach einem Update von Shopware 4 auf Shopware 5 notwendig.

 
UPDATE s_articles_img i, s_media m
SET i.media_id = m.id
WHERE m.path = CONCAT('media/image/', i.img, '.', i.extension);
 

Bestelldatensätze

Doppelte Bestellnummern auslesen

Doppelte Bestellnummern in Shopware führen zu Fehlern im Backend und sollten daher nicht vorhanden sein. Durch Plugins oder Schnittstellen kann es jedoch zu einem solchen Verhalten kommen. Mit diesem Statement kannst Du Dir doppelte Bestellnummern ausgeben lassen.

 
SELECT ordernumber, COUNT(ordernumber) AS anzahl
FROM s_order
GROUP BY ordernumber
HAVING ( COUNT(ordernumber) > 1 )
 

Alternativ besteht auch die Möglichkeit die Bestellungen in eine temporäre Order Tabelle zu kopieren und dann die doppelten Bestellnummern in der eigentlichen Tabelle zu kennzeichnen.

 
CREATE  TABLE  `s_order_tmp` (  `id` int( 11  )  NOT  NULL  AUTO_INCREMENT ,
 `ordernumber` varchar( 30  )  COLLATE utf8_unicode_ci  DEFAULT NULL ,
 `userID` int( 11  )  DEFAULT NULL ,
 `invoice_amount` double NOT  NULL DEFAULT  '0',
 `invoice_amount_net` double NOT  NULL ,
 `invoice_shipping` double NOT  NULL DEFAULT  '0',
 `invoice_shipping_net` double NOT  NULL ,
 `ordertime` datetime  DEFAULT NULL ,
 `status` int( 11  )  NOT  NULL DEFAULT  '0',
 `cleared` int( 11  )  NOT  NULL DEFAULT  '0',
 `paymentID` int( 11  )  NOT  NULL DEFAULT  '0',
 `transactionID` varchar( 255  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `comment` mediumtext COLLATE utf8_unicode_ci NOT  NULL ,
 `customercomment` mediumtext COLLATE utf8_unicode_ci NOT  NULL ,
 `internalcomment` mediumtext COLLATE utf8_unicode_ci NOT  NULL ,
 `net` int( 1  )  NOT  NULL ,
 `taxfree` int( 11  )  NOT  NULL ,
 `partnerID` varchar( 255  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `temporaryID` varchar( 255  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `referer` mediumtext COLLATE utf8_unicode_ci NOT  NULL ,
 `cleareddate` datetime  DEFAULT NULL ,
 `trackingcode` varchar( 255  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `language` varchar( 10  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `dispatchID` int( 11  )  NOT  NULL ,
 `currency` varchar( 5  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `currencyFactor` double NOT  NULL ,
 `subshopID` int( 11  )  NOT  NULL ,
 `remote_addr` varchar( 255  )  COLLATE utf8_unicode_ci NOT  NULL ,
 `deviceType` varchar( 50  )  COLLATE utf8_unicode_ci  DEFAULT NULL ,
 PRIMARY  KEY (  `id`  ) ,
 KEY  `partnerID` (  `partnerID`  ) ,
 KEY  `userID` (  `userID`  ) ,
 KEY  `ordertime` (  `ordertime`  ) ,
 KEY  `cleared` (  `cleared`  ) ,
 KEY  `status` (  `status`  ) ,
 KEY  `paymentID` (  `paymentID`  ) ,
 KEY  `temporaryID` (  `temporaryID`  ) ,
 KEY  `ordernumber` (  `ordernumber`  ) ,
 KEY  `transactionID` (  `transactionID`  )  ) ENGINE  = InnoDB  DEFAULT CHARSET  = utf8 COLLATE  = utf8_unicode_ci;
SET SQL_MODE='NO_AUTO_VALUE_ON_ZERO';
INSERT INTO `s_order_tmp` SELECT * FROM `s_order`;
UPDATE s_order SET ordernumber = CONCAT(ordernumber , '-doppelt') WHERE id IN (SELECT max(id) FROM s_order_tmp GROUP BY ordernumber HAVING ( COUNT(ordernumber) > 1 )) AND ordernumber <> 0;
DROP TABLE `s_order_tmp`;
 

In diesem Fall wird an die doppelte Bestellnummer ein "-doppelt" angehangen.

Beachte hierbei, dass es sich bei Bestellungen mit der Bestellnummer "0" um Abbruchwarenkörbe handelt und diese Nummer mehrfach vergeben sein kann.

Bestellungen und Kunden aus Sicherung migrieren

Es kann vorkommen, dass durch ein Problem oder den Einsatz eines Test-Systems einige Bestellungen nicht in der Live-Datenbank vorhanden sind, die IDs der Bestellungen jedoch bereits durch andere neue Bestellungen wieder belegt sind. Über das Backend von Shopware ist es aktuell nicht möglich Bestellungen und Kunden aus einer Sicherung zu übernehmen, dieses Query sollte hier abhilfe schaffen. Bitte beachte dabei, dass Dein System ggfs. noch weitere Spalten, Tabellen oder Anforderungen enthält, welche du ergänzen musst. Auch müssen Zahlungen hier ggfs. manuell abgewickelt werden. Anschließend sollte noch das Query für doppelte Bestellnummern ausgeführt werden, damit die Bestellnummern nach der Migration im Backend überprüft werden können. DBOLDNAME ersetzt du einfach mit dem Namen der Datenbank aus der du die Datensätze migrieren willst und die IdOfFirstCustomerToMigrate und NumberOfFirstOrderToMigrate durch die jeweilie ID bzw. Bestellnummer des ersten zu migrierenden Datensatzes.

 
ALTER TABLE s_user ADD idold INT(11) NULL AFTER customernumber;
INSERT INTO s_user  (idold, password, encoder, email, active, accountmode, cONfirmatiONkey, paymentID, firstlogin, lastlogin, sessiONID, newsletter, validatiON, affiliate, customergroup, paymentpreset, LANGUAGE, subshopID, referer, pricegroupID, internalcomment, failedlogins, lockeduntil, default_billing_address_id, default_shipping_address_id, title, salutatiON, firstname, lastname, birthday, customernumber) SELECT * FROM DBOLDNAME.s_user WHERE id >= IdOfFirstCustomerToMigrate;
INSERT INTO s_user_attributes (userID) SELECT id FROM s_user WHERE id NOT IN (SELECT userid FROM s_user_attributes);
INSERT INTO s_user_addresses (user_id, company, department, salutatiON, title, firstname, lastname, street, zipcode, city, country_id, state_id, ustid, phone, additional_address_line1, additional_address_line2) SELECT u.id, oldad.company, oldad.department, oldad.salutatiON, oldad.title, oldad.firstname, oldad.lastname, oldad.street, oldad.zipcode, oldad.city, oldad.country_id, oldad.state_id, oldad.ustid, oldad.phone, oldad.additional_address_line1, oldad.additional_address_line2 FROM DBOLDNAME.s_user_addresses AS oldad JOIN s_user AS u ON u.idold = oldad.user_id WHERE u.idold >=1;
INSERT INTO s_user_addresses_attributes (address_id) SELECT id FROM s_user_addresses WHERE id NOT IN (SELECT address_id FROM s_user_addresses_attributes);
INSERT INTO `s_user_billingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `phone`, `countryID`, `stateID`, `ustid`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title  FROM DBOLDNAME.s_user_billingaddress AS oldbil JOIN s_user AS u ON u.idold = oldbil.userid WHERE u.idold >=1;
INSERT INTO s_user_billingaddress_attributes (billingID) SELECT id FROM s_user_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO `s_user_shippingaddress` (`userID`, `company`, `department`, `salutatiON`, `firstname`, `lastname`, `street`, `zipcode`, `city`, `countryID`, `stateID`, `additional_address_line1`, `additional_address_line2`, `title`) SELECT u.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title  FROM DBOLDNAME.s_user_shippingaddress AS oldship JOIN s_user AS u ON u.idold = oldship.userid WHERE u.idold >=1;
INSERT INTO s_user_shippingaddress_attributes (shippingID) SELECT id FROM s_user_shippingaddress WHERE id NOT IN (SELECT shippingID FROM s_user_shippingaddress_attributes);
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_billing_address_id = ua.id WHERE u.idold >=1;
UPDATE s_user AS u JOIN s_user_addresses AS ua ON u.id = ua.user_ID SET u.default_shipping_address_id = ua.id WHERE u.idold >=1;
ALTER TABLE s_order ADD idold INT(11) NULL AFTER deviceType;
ALTER TABLE s_order_details ADD didold INT(11) NULL AFTER pack_unit;
INSERT INTO s_order (idold, ordernumber, userID, invoice_amount, invoice_amount_net, invoice_shipping, invoice_shipping_net, ordertime, STATUS, cleared, paymentID, transactiONID, comment, customercomment, internalcomment, net, taxfree, partnerID, temporaryID, referer, cleareddate, trackingcode, LANGUAGE, dispatchID, currency, currencyFactor, subshopID, remote_addr, deviceType) SELECT o.id, o.ordernumber, u.id, o.invoice_amount, o.invoice_amount_net, o.invoice_shipping, o.invoice_shipping_net, o.ordertime, o.STATUS, o.cleared, o.paymentID, o.transactiONID, o.comment, o.customercomment, o.internalcomment, o.net, o.taxfree, o.partnerID, o.temporaryID, o.referer, o.cleareddate, o.trackingcode, o.LANGUAGE, o.dispatchID, o.currency, o.currencyFactor, o.subshopID, o.remote_addr, o.deviceType FROM DBOLDNAME.s_order AS o JOIN s_user AS u ON u.idold = o.userID WHERE o.ordernumber >= NumberOfFirstOrderToMigrate;
INSERT INTO s_order_attributes (orderID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6) 
SELECT o.id, oold.attribute1, oold.attribute2, oold.attribute3, oold.attribute4, oold.attribute5, oold.attribute6 FROM s_order AS o JOIN DBOLDNAME.s_order_attributes AS oold ON o.idold = oold.id WHERE o.id NOT IN (SELECT orderID FROM s_order_attributes);
INSERT INTO s_order_details (didold, orderID, ordernumber, articleID, articleordernumber, price, quantity, name, STATUS, shipped, shippedgroup, releasedate, modus, esdarticle, taxID, tax_rate, cONfig, ean, unit, pack_unit)
SELECT posold.id, o.id, posold.ordernumber, posold.articleID, posold.articleordernumber, posold.price, posold.quantity, posold.name, posold.STATUS, posold.shipped, posold.shippedgroup, posold.releasedate, posold.modus, posold.esdarticle, posold.taxID, posold.tax_rate, posold.cONfig, posold.ean, posold.unit, posold.pack_unit FROM s_order AS o JOIN DBOLDNAME.s_order_details AS posold ON o.idold = posold.orderID WHERE o.idold >= 1;
INSERT INTO s_order_details_attributes (detailID, attribute1, attribute2, attribute3, attribute4, attribute5, attribute6)
SELECT d.id, dold.attribute1, dold.attribute2, dold.attribute3, dold.attribute4, dold.attribute5, dold.attribute6 FROM DBOLDNAME.s_order_details_attributes AS dold JOIN s_order_details AS d ON d.didold = dold.id WHERE d.id NOT IN (SELECT detailID FROM s_order_details_attributes);
INSERT INTO s_order_billingaddress (userID, orderID, company, department, salutatiON, customernumber, firstname, lastname, street, zipcode, city, phone, countryID, stateID, ustid, additional_address_line1, additional_address_line2, title) SELECT u.id, o.id, oldbil.company, oldbil.department, oldbil.salutatiON, oldbil.customernumber, oldbil.firstname, oldbil.lastname, oldbil.street, oldbil.zipcode, oldbil.city, oldbil.phone, oldbil.countryid, oldbil.stateid, oldbil.ustid, oldbil.additional_address_line1, oldbil.additional_address_line2, oldbil.title FROM DBOLDNAME.s_order_billingaddress AS oldbil JOIN s_order AS o ON o.idold = oldbil.orderID JOIN s_user AS u ON u.idold = oldbil.userID WHERE o.idold >=1;
INSERT INTO s_order_billingaddress_attributes (billingID) SELECT id FROM s_order_billingaddress WHERE id NOT IN (SELECT billingID FROM s_user_billingaddress_attributes);
INSERT INTO s_order_shippingaddress (userID, orderID, company, department, salutatiON, firstname, lastname, street, zipcode, city, countryID, stateID, additional_address_line1, additional_address_line2, title) 
SELECT u.id, o.id, oldship.company, oldship.department, oldship.salutatiON, oldship.firstname, oldship.lastname, oldship.street, oldship.zipcode, oldship.city, oldship.countryid, oldship.stateid, oldship.additional_address_line1, oldship.additional_address_line2, oldship.title FROM DBOLDNAME.s_order_shippingaddress AS oldship JOIN s_order AS o ON o.idold = oldship.orderID JOIN s_user AS u ON u.idold = oldship.userID WHERE o.idold >=1;
INSERT INTO s_order_shippingaddress_attributes (shippingid) SELECT id FROM s_order_shippingaddress WHERE id NOT IN (SELECT shippingid FROM s_order_shippingaddress_attributes);
ALTER TABLE `s_user` DROP `idold`;
ALTER TABLE `s_order` DROP `idold`;
ALTER TABLE `s_order_details` DROP `didold`;
 

System / Einstellungen

Nicht standardmäßig vorhandene Plugins deaktivieren

Dieses Statement sorgt dafür, dass alle Plugins (über den Plugin Manager hochgeladen, aus dem Community Store installiert) deaktiviert werden. Dies ist besonders für das Debuggen von Fehlern sehr hilfreich, da Du so schnell den Einfluss von Plugins ausschließen kannst.

 
CREATE TABLE s_core_plugins_tmp AS SELECT * FROM s_core_plugins;
INSERT INTO `s_core_plugins_tmp` SELECT * FROM `s_core_plugins`;
 

Nun setzt Du mit diesem Statement alle Plugins auf inaktiv.

 
UPDATE `s_core_plugins` SET `active`= 0 WHERE `source` = 'Community' AND author <> 'shopware AG';
UPDATE `s_core_plugins` SET `active`= 0 WHERE `source` = 'Local' AND author <> 'shopware AG';
UPDATE `s_core_plugins` SET `active`= 0 WHERE `source` = '' AND author <> 'shopware AG';
 
 

Für die Widerherstellung der alten Plugins kopierst Du die Daten aus der temporären Tabelle in die reguläre und löschst im Anschluss die nicht mehr benötigte Tabelle.

 
UPDATE s_core_plugins AS p JOIN s_core_plugins_tmp AS pt ON p.id = pt.id SET p.active = pt.active;
DROP TABLE s_core_plugins_tmp;
 

Datenbank zurücksetzen bzw. Testdaten löschen

Weitere Informationen zum Löschen der Testdaten bzw. Zurücksetzen der Datenbank findest Du in dieser Dokumentation.

Weitere interessante Artikel:

Tutorial: Zahlungsstatus von Bestellungen im Frontend anzeigen

{{Achtung|'''Achtung!''' Die hier zur Verfügung gestellten Informationen wurden zum Zeitpunkt der Veröffentlichung dieses Artikels getestet, können sich zwischenzeitlich aber geändert haben. '''Die Durchführung dieses Tutorials geschieht auf...

weiterlesen

Die Shopware Datenbank

Einleitung In diesem Artikel findest Du eine Erläuterung zur Datenbank-Struktur in Shopware sowie weiterführende Artikel zu den einzelnen Bereichen inkl. Erklärung der genauen Spalten. Die Shopware-Datenbank kannst Du entweder über Dein...

weiterlesen

Storytelling mit Shopware 5

Grundgedanke Das Wichtigste direkt vorweg: Storytelling - also eine Geschichte erzählen - funktioniert hauptsächlich über Bilder. Also Fotos, Videos oder auch Illustrationen. Natürlich kann man, wie jeder Roman zeigt, große Emotionen und...

weiterlesen
€ 1,00

Preise inkl. gesetzlicher
MwSt. + Versandkosten*