It is strongly advised that these statements should only be executed with the necessary expertise.
The information provided here was tested at the time of publication of this article - it 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!
With the following query you can delete all created products and empty the product overview completely:
DELETE FROM product;With the following query you can remove all properties:
DELETE FROM property_group;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 FROM customer;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.
TRUNCATE `order`;
TRUNCATE `order_address`;
TRUNCATE `order_customer`;
TRUNCATE `order_delivery`;
TRUNCATE `order_delivery_position`;
TRUNCATE `order_line_item`;
TRUNCATE `order_tag`;
TRUNCATE `order_transaction`;After a migration, inheritance is disabled in all variant products. To enable inheritance, only the respective property must have NULL as a value in the database.
With the following query all properties and additionally also the inheritance for the properties are activated:
UPDATE product SET
active = NULL,
tax_id = NULL,
product_manufacturer_id = NULL,
delivery_time_id = NULL,
deliveryTime = NULL,
product_media_id = NULL,
manufacturer_number = NULL,
ean = NULL,
restock_time = NULL,
is_closeout = NULL,
purchase_steps = NULL,
max_purchase = NULL,
min_purchase = NULL,
purchase_unit = NULL,
reference_unit = NULL,
shipping_free = NULL,
mark_as_topseller = NULL,
weight = NULL,
width = NULL,
height = NULL,
length = NULL,
release_date = NULL,
tag_ids = NULL,
tags = NULL
WHERE parent_id IS NOT NULL;
DELETE FROM product_translation WHERE product_id IN (SELECT id FROM product WHERE parent_id IS NOT NULL);
DELETE FROM product_tag WHERE product_id IN (SELECT id FROM product WHERE parent_id IS NOT NULL);
DELETE FROM product_media WHERE product_id IN (SELECT id FROM product WHERE parent_id IS NOT NULL);
DELETE FROM product_visibility WHERE product_id IN (SELECT id FROM product WHERE parent_id IS NOT NULL);
DELETE FROM product_property WHERE product_id IN (SELECT id FROM product WHERE parent_id IS NOT NULL);
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`;From version 6.5, extensions and Themes are also entered as an app and must therefore be deactivated separately. A temporary backup of the app table should therefore also be created.
CREATE TABLE app_tmp LIKE app;
INSERT INTO `app_tmp` SELECT * FROM `app`;Then you can deactivate the extensions
UPDATE `plugin` SET `active`= 0 WHERE (author <> 'shopware AG' AND author <> 'Shopware') OR (author IS NULL);You can deactivate the apps using the following command
UPDATE `app` SET `active` = 0;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;To restore the apps to their original state, use the following command
UPDATE app AS a JOIN app_tmp AS at ON a.id = at.id SET a.active = at.active;Finally, you can delete the temporary backup tables
DROP TABLE plugin_tmp;
DROP TABLE app_tmp;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';
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);