Question 63684 in PhpMyAdmin Question posted in
The official documentation can be found here.

Shopware import Database runs on "GENERATED ALWAYS AS" error – Phpmyadmin

So i exported a shopware database using phpmyadmin with MySQL and get an "GENERATED ALWAYS AS" error on the order table when importing to a new database.
After a short time I get this error and can’t find any solution to this.
I even deleted all orders to be sure there is no malformed order in my table.
This is what phpmyadmin returns:

SQL-Befehl:

CREATE TABLE `order` ( `id` binary(16) NOT NULL, `version_id` binary(16) NOT NULL, `state_id` binary(16) NOT NULL, `auto_increment` bigint(20) UNSIGNED NOT NULL, `order_number` varchar(64) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `currency_id` binary(16) NOT NULL, `language_id` binary(16) NOT NULL, `currency_factor` double DEFAULT NULL, `sales_channel_id` binary(16) NOT NULL, `billing_address_id` binary(16) NOT NULL, `billing_address_version_id` binary(16) NOT NULL, `price` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`price`)), `order_date_time` datetime(3) NOT NULL, `order_date` date GENERATED ALWAYS AS (cast(`order_date_time` as date)) STORED, `amount_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,'$.totalPrice'))) VIRTUAL, `amount_net` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,'$.netPrice'))) VIRTUAL, `position_price` double GENERATED ALWAYS AS (json_unquote(json_extract(`price`,'$.positionPrice'))) VIRTUAL, `tax_status` varchar(255) COLLATE utf8mb4_unicode_ci GENERATED ALWAYS AS (json_unquote(json_extract(`price`,'$.taxStatus'))) VIRTUAL, `shipping_costs` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin NOT NULL CHECK (json_valid(`shipping_costs`)), `shipping_total` double GENERATED ALWAYS AS (json_unquote(json_extract(`shipping_costs`,'$.totalPrice'))) VIRTUAL, `deep_link_code` varchar(32) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `custom_fields` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`custom_fields`)), `affiliate_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `campaign_code` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL, `customer_comment` longtext COLLATE utf8mb4_unicode_ci DEFAULT NULL, `created_at` datetime(3) NOT NULL, `updated_at` datetime(3) DEFAULT NULL, `item_rounding` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`item_rounding`)), `total_rounding` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`total_rounding`)), `rule_ids` longtext CHARACTER SET utf8mb4 COLLATE utf8mb4_bin DEFAULT NULL CHECK (json_valid(`rule_ids`)), `created_by_id` binary(16) DEFAULT NULL, `updated_by_id` binary(16) DEFAULT NULL )

MySQL meldet: Dokumentation

#1064 - Fehler in der SQL-Syntax. Bitte die korrekte Syntax im Handbuch nachschlagen bei 'GENERATED ALWAYS AS (json_unquote(json_extract(`price`,'$.taxStatus'))) VIRTU...' in Zeile 19
Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    Using this line of code via SSH on my dump, made it possible to Import it afterwards:

    sed -i 's/COLLATE.*GENERATED ALWAYS/GENERATED ALWAYS/g' dump.sql

  2. 0

    You can look into https://sw-cli.fos.gg/commands/project/#shopware-cli-project-dump-database and use that for the dump creation. It’s a replacement for mysqldump and considers this all issues.

    Otherwise, you need the same mysqldump/mysql cli binary as the server. Importing mostly works only in the same database type (mysql/mariadb)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search