SQL Tips & Tricks

It is strongly advised that these statements should only be made with the necessary expertise.
The information provided here was tested at the time of publication of this article, but may have changed in the meantime. The execution of this tutorial is at your own risk

Before you start, make a backup so that you can restore it if you run into trouble. Please note that these are adjustments of shopware and therefore the content of this tutorial is not officially supported!

Catalogues

Delete all products

With the following query you can delete all created products and empty the product overview completely.


delete pv.*, pt2.*, pt.*, psk.*, pr.*, pp2.*, pp.*, po.*, pm.*, pcfs.*, pcst.*, pcsap.*, pcs2.*, pcs.*, pct.*, pc.*, p.* from product p
left join product_category pc on p.id = pc.product_id 
left join product_category_tree pct on p.id = pct.product_id 
left join product_configurator_setting pcs on p.id = pcs.product_id 
left join product_cross_selling pcs2 on p.id = pcs2.product_id 
left join product_cross_selling_assigned_products pcsap on p.id = pcsap.product_id 
left join product_cross_selling_translation pcst on pcs2.id = pcst.product_cross_selling_id 
left join product_custom_field_set pcfs on p.id = pcfs.product_id 
left join product_media pm on p.id = pm.product_id 
left join product_option po on p.id = po.product_id 
left join product_price pp on p.id = pp.product_id 
left join product_property pp2 on p.id = pp2.product_id 
left join product_review pr on p.id = pr.product_id 
left join product_search_keyword psk on p.id = psk.product_id 
left join product_tag pt on p.id = pt.product_id 
left join product_translation pt2 on p.id = pt2.product_id 
left join product_visibility pv on p.id = pv.product_id;

Delete all properties

With the following query you can remove all properties.


delete pgt.*, pgot.*, pgo.*, pg.* from property_group pg 
join property_group_option pgo on pg.id = pgo.property_group_id 
join property_group_option_translation pgot on pgo.id = pgot.property_group_option_id 
join property_group_translation pgt on pg.id = pgt.property_group_id;

Delete all customer data

You can apply the following SQL queries before you go live with your store. However, make sure that you never use them in your live store. Orders with receipts or receipts should never be deleted.

With the following query you can remove all customer data.


delete cwp.*, cw.*, ct.*, cr.*, ca.*, c.* from customer c 
left join customer_address ca on c.id = ca.customer_id 
left join customer_recovery cr on c.id = cr.customer_id 
left join customer_tag ct ON c.id = ct.customer_id 
left join customer_wishlist cw on c.id = cw.customer_id 
left join customer_wishlist_product cwp on cw.id = cwp.customer_wishlist_id;

Delete all orders

You can apply the following SQL queries before you go live with your store. However, make sure that you never use them in your live store. Orders with receipts or receipts should never be deleted.

With the following query you can delete all orders.


delete ot2.*, ot.*, oli.*, odp.*, od.*, oc.*, oa.*, o.* from `order` o 
left join  order_address oa on o.id = oa.order_id 
left join  order_customer oc on o.id = oc.order_id 
left join  order_delivery od on o.id = od.order_id 
left join  order_delivery_position odp on od.id = odp.order_delivery_id 
left join  order_line_item oli on o.id = oli.order_id 
left join  order_tag ot on o.id = ot.order_id 
left join  order_transaction ot2 on o.id = ot2.order_id;

System / Settings

Disable non-standard extensions

With the following queries you can deactivate the extensions that are not already present when installing Shopware. This is especially useful for debugging errors, as you can quickly eliminate the influence of extensions.

If you use your own theme that is integrated via a plug-in, you should make sure in advance that the theme is not assigned to a sales channel. Alternatively, you may encounter problems when calling the appropriate sales channels.

First we create a temporary backup table. The current status of the extensions is saved in this table.


CREATE TABLE plugin_tmp LIKE plugin;
INSERT INTO `plugin_tmp` SELECT * FROM `plugin`;

Then you can deactivate the extensions


UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);

The original state of the extensions can be restored later as follows


UPDATE plugin AS p JOIN plugin_tmp AS pt ON p.id = pt.id SET p.active = pt.active;

Finally, you can delete the temporary backup table


DROP TABLE plugin_tmp;

Customise database collation

By default Showpare uses "utf8mb4_unicode_ci" as collation for the tables.
However, it can happen that the collation for individual tables is not (no longer) set correctly.

This can cause errors when comparing strings of incompatible collations or when trying to select data from columns with different collations.
In the log files this can be recognised by the following entry (possibly slightly modified):

SQLSTATE[HY000]: General error: 1267 Illegal mix of collations (utf8mb4_unicode_ci,IMPLICIT) and (utf8_general_ci,COERCIBLE)

With the following query you can check if a wrong collation is used in a table.
To do this, insert the table and database name into the query at the marked positions.


SELECT table_schema, table_name, column_name, character_set_name, collation_name
 FROM information_schema.columns
 WHERE (collation_name = 'utf8mb4_unicode_ci' or collation_name = 'utf8_general_ci ') 
 and table_name = '' --Tabellenname einsetzen 
 and table_schema = '' --Datenbanknamen einsetzen
 ORDER BY table_schema, table_name,ordinal_position;

You can update the collation with one of the following two queries.

We recommend that you always make a full backup before making changes to the database.

Update of the entire database


ALTER DATABASE --Datenbanknamen einsetzen CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci

Update a single table


ALTER TABLE --Tabellenname einsetzen CONVERT TO CHARACTER SET utf8mb4 COLLATE 'utf8mb4_unicode_ci';

Restore default customer group

This restores the "default customer group" without a translation. The translations must be maintained manually in the admin.

Query to restore the default customer group:
 

INSERT INTO `customer_group` (`id`, `display_gross`, `registration_active`, `created_at`, `updated_at`) VALUES
(UNHEX('CFBD5018D38D41D8ADCA10D94FC8BDD6'), 1, 0, '2021-01-01 00:00:00.00', NULL);

Was this article helpful?