4

I've inherited a mid-migration project from another dev, and need to start the data migration from scratch. It seems like it has been run before, and the M2 tables for product, customer, and order data have been truncated.

I've tried data and delta modes with and without reset, dropping the m2_cl tables from M1, and even importing the correct product, customer, and order info into those tables, but I keep getting the same vague warning when I run the migration:

[WARNING]: Mismatch of entities in the document: sales_order_grid Source: 17857 Destination: 0

Any ideas?

Improve this question
1
  • Simeon Petrov. I followed your hints and the links you provided. Somehow I managed to migrate the data. But now when I run setup upgrade I have MULTIPLE errors for various Magento modules when I run setup:upgrade. All errors are similar (Unable to apply patch related to duplicate entry or table exists), like this : Unable to apply patch Magento\InventoryCatalog\Setup\Patch\Schema\CreateLegacyStockStatusView for module Magento_InventoryCatalog. Original exception message: SQLSTATE[42S01]: Base table or view already exists: 1050 Table 'inventory_stock_1' already exists, Setup:upgrade wants to ad
    – TheAgent
    Commented Mar 28, 2021 at 6:32

2 Answers 2

Reset to default
5

I am aware that this is an old question, but for others looking to do the same in the future I have compiled a few steps. As of current versions of Magento Data Migration Tool - June 2020 the issue is still not fixed.

If you want to run --reset, you may experience failure later on, especially with eav related failures, for instance eav_attribute_group table errors. NOTE: Any other attribute issues are similarly caused.

This eav_attribute_group issue that you mentioned as far as I remember only occurs when you try to run bin/magento migrate:data --reset again once a previous migration already migrated all the attributes. I seem to recall the migration tool confused with the IDs of the groups, so just restart from the beginning.

So some steps that may assist:

  1. Install Magento 2.3.x completely, setup modules etc, setup backend settings that you want. Once you have a working database before even migrating M1 data, make a backup of this M2 database state.... because you will most likely restart alot of times because of migration issues. So create a full m2_full_install_before_migration.sql backup.

  2. Create the same for Magento 1 database - until you are at the point where you can migrate without failures, you can restore this as well. NOTE: Delta migration is useless until you have successful migrate:data.

  3. DROP M2 migrated database and restore from SQL in step 1.

  4. VERY IMPORTANT - The most important step of all:
    Delete the var/migration* files in Magento 2 folder. These files are used by the tool it seems to keep track of where you stopped previously. So since the migration does not work 100% yet, just restart from the beginning. (this might also be why you have group issues). I always delete these files.

  5. So a complete DROP/RECREATE, RESTORE M2 DB, setup:upgrade, migrate data from M1 copy (locally) takes around an hour in my case because I have done it so many times.

  6. I would ignore migrate:delta for now if I were you, you will have to restart so many times. Get data working, make notes of issues that are specific to your instance, so that you could do things step by step.

Good luck!

Improve this answer
0
0

Here is my workflow with hard resetting the data migration few times...

Installing M2, making settings migration, installing some modules making a backup, making data migration, working on the site, clearing all customer, order and catalog tables in the database, making another data migration to be sure it is working and continue developing the store until the client likes the page, then making another final data migration and going live.

The trick is not using delta, only once settings and few data migrations. Clearing the tables before any sequential data migration is done with similar script:

USE magento_db;
SET FOREIGN_KEY_CHECKS = 0;

TRUNCATE TABLE amazon_customer;
TRUNCATE TABLE amazon_pending_authorization;
TRUNCATE TABLE amazon_pending_capture;
TRUNCATE TABLE amazon_pending_refund;
TRUNCATE TABLE cache;
TRUNCATE TABLE cache_tag;
TRUNCATE TABLE captcha_log;

TRUNCATE TABLE catalog_category_entity;
TRUNCATE TABLE catalog_category_entity_datetime;
TRUNCATE TABLE catalog_category_entity_decimal;
TRUNCATE TABLE catalog_category_entity_int;
TRUNCATE TABLE catalog_category_entity_text;
TRUNCATE TABLE catalog_category_entity_varchar;
TRUNCATE TABLE catalog_category_flat_store_3;
TRUNCATE TABLE catalog_category_flat_store_4;
TRUNCATE TABLE catalog_category_product;
TRUNCATE TABLE catalog_category_product_index;
TRUNCATE TABLE catalog_category_product_index_replica;
TRUNCATE TABLE catalog_category_product_index_store1;
TRUNCATE TABLE catalog_category_product_index_store1_replica;
TRUNCATE TABLE catalog_category_product_index_store4;
TRUNCATE TABLE catalog_category_product_index_store4_replica;
TRUNCATE TABLE catalog_category_product_index_store3;
TRUNCATE TABLE catalog_category_product_index_store3_replica;
TRUNCATE TABLE catalog_category_product_index_tmp;
TRUNCATE TABLE catalog_compare_item;
TRUNCATE TABLE catalog_product_bundle_option;
TRUNCATE TABLE catalog_product_bundle_option_value;
TRUNCATE TABLE catalog_product_bundle_price_index;
TRUNCATE TABLE catalog_product_bundle_selection;
TRUNCATE TABLE catalog_product_bundle_selection_price;
TRUNCATE TABLE catalog_product_bundle_stock_index;
TRUNCATE TABLE catalog_product_entity;
TRUNCATE TABLE catalog_product_entity_datetime;
TRUNCATE TABLE catalog_product_entity_decimal;
TRUNCATE TABLE catalog_product_entity_gallery;
TRUNCATE TABLE catalog_product_entity_int;
TRUNCATE TABLE catalog_product_entity_media_gallery;
TRUNCATE TABLE catalog_product_entity_media_gallery_value;
TRUNCATE TABLE catalog_product_entity_media_gallery_value_to_entity;
TRUNCATE TABLE catalog_product_entity_media_gallery_value_video;
TRUNCATE TABLE catalog_product_entity_text;
TRUNCATE TABLE catalog_product_entity_tier_price;
TRUNCATE TABLE catalog_product_entity_varchar;
TRUNCATE TABLE catalog_product_flat_3;
TRUNCATE TABLE catalog_product_flat_4;
TRUNCATE TABLE catalog_product_frontend_action;
TRUNCATE TABLE catalog_product_index_eav;
TRUNCATE TABLE catalog_product_index_eav_decimal;
TRUNCATE TABLE catalog_product_index_eav_decimal_idx;
TRUNCATE TABLE catalog_product_index_eav_decimal_replica;
TRUNCATE TABLE catalog_product_index_eav_decimal_tmp;
TRUNCATE TABLE catalog_product_index_eav_idx;
TRUNCATE TABLE catalog_product_index_eav_replica;
TRUNCATE TABLE catalog_product_index_eav_tmp;
TRUNCATE TABLE catalog_product_index_price;
TRUNCATE TABLE catalog_product_index_price_bundle_idx;
TRUNCATE TABLE catalog_product_index_price_bundle_opt_idx;
TRUNCATE TABLE catalog_product_index_price_bundle_opt_tmp;
TRUNCATE TABLE catalog_product_index_price_bundle_sel_idx;
TRUNCATE TABLE catalog_product_index_price_bundle_sel_tmp;
TRUNCATE TABLE catalog_product_index_price_bundle_tmp;
TRUNCATE TABLE catalog_product_index_price_cfg_opt_agr_idx;
TRUNCATE TABLE catalog_product_index_price_cfg_opt_agr_tmp;
TRUNCATE TABLE catalog_product_index_price_cfg_opt_idx;
TRUNCATE TABLE catalog_product_index_price_cfg_opt_tmp;
TRUNCATE TABLE catalog_product_index_price_downlod_idx;
TRUNCATE TABLE catalog_product_index_price_downlod_tmp;
TRUNCATE TABLE catalog_product_index_price_final_idx;
TRUNCATE TABLE catalog_product_index_price_final_tmp;
TRUNCATE TABLE catalog_product_index_price_idx;
TRUNCATE TABLE catalog_product_index_price_opt_agr_idx;
TRUNCATE TABLE catalog_product_index_price_opt_agr_tmp;
TRUNCATE TABLE catalog_product_index_price_opt_idx;
TRUNCATE TABLE catalog_product_index_price_opt_tmp;
TRUNCATE TABLE catalog_product_index_price_replica;
TRUNCATE TABLE catalog_product_index_price_tmp;
TRUNCATE TABLE catalog_product_index_tier_price;
TRUNCATE TABLE catalog_product_index_website;
TRUNCATE TABLE catalog_product_link;
TRUNCATE TABLE catalog_product_link_attribute;
TRUNCATE TABLE catalog_product_link_attribute_decimal;
TRUNCATE TABLE catalog_product_link_attribute_int;
TRUNCATE TABLE catalog_product_link_attribute_varchar;
TRUNCATE TABLE catalog_product_link_type;
TRUNCATE TABLE catalog_product_option;
TRUNCATE TABLE catalog_product_option_price;
TRUNCATE TABLE catalog_product_option_title;
TRUNCATE TABLE catalog_product_option_type_price;
TRUNCATE TABLE catalog_product_option_type_title;
TRUNCATE TABLE catalog_product_option_type_value;
TRUNCATE TABLE catalog_product_relation;
TRUNCATE TABLE catalog_product_super_attribute;
TRUNCATE TABLE catalog_product_super_attribute_label;
TRUNCATE TABLE catalog_product_super_link;
TRUNCATE TABLE catalog_product_website;
TRUNCATE TABLE catalog_url_rewrite_product_category;
TRUNCATE TABLE cataloginventory_stock;
TRUNCATE TABLE cataloginventory_stock_item;
TRUNCATE TABLE cataloginventory_stock_status;
TRUNCATE TABLE cataloginventory_stock_status_idx;
TRUNCATE TABLE cataloginventory_stock_status_replica;
TRUNCATE TABLE cataloginventory_stock_status_tmp;
TRUNCATE TABLE catalogsearch_fulltext_scope1;
TRUNCATE TABLE catalogsearch_recommendations;

TRUNCATE TABLE customer_address_entity;
TRUNCATE TABLE customer_address_entity_datetime;
TRUNCATE TABLE customer_address_entity_decimal;
TRUNCATE TABLE customer_address_entity_int;
TRUNCATE TABLE customer_address_entity_text;
TRUNCATE TABLE customer_address_entity_varchar;
TRUNCATE TABLE customer_entity;
TRUNCATE TABLE customer_entity_datetime;
TRUNCATE TABLE customer_entity_decimal;
TRUNCATE TABLE customer_entity_int;
TRUNCATE TABLE customer_entity_text;
TRUNCATE TABLE customer_entity_varchar;
TRUNCATE TABLE customer_grid_flat;
TRUNCATE TABLE customer_log;
TRUNCATE TABLE customer_visitor;

TRUNCATE TABLE downloadable_link;
TRUNCATE TABLE downloadable_link_price;
TRUNCATE TABLE downloadable_link_purchased;
TRUNCATE TABLE downloadable_link_purchased_item;
TRUNCATE TABLE downloadable_link_title;
TRUNCATE TABLE downloadable_sample;
TRUNCATE TABLE downloadable_sample_title;

TRUNCATE TABLE email_abandoned_cart;
TRUNCATE TABLE email_automation;
TRUNCATE TABLE email_campaign;
TRUNCATE TABLE email_catalog;
TRUNCATE TABLE email_contact;
TRUNCATE TABLE email_contact_consent;
TRUNCATE TABLE email_failed_auth;
TRUNCATE TABLE email_importer;
TRUNCATE TABLE email_order;
TRUNCATE TABLE email_review;
TRUNCATE TABLE email_rules;
TRUNCATE TABLE email_template;
TRUNCATE TABLE email_wishlist;
TRUNCATE TABLE gift_message;
TRUNCATE TABLE googleoptimizer_code;
TRUNCATE TABLE import_history;
TRUNCATE TABLE importexport_importdata;
TRUNCATE TABLE indexer_state;
TRUNCATE TABLE integration;
TRUNCATE TABLE layout_link;
TRUNCATE TABLE layout_update;

TRUNCATE TABLE magento_acknowledged_bulk;
TRUNCATE TABLE magento_bulk;
TRUNCATE TABLE magento_operation;

TRUNCATE TABLE mageplaza_smtp_log;

TRUNCATE TABLE newsletter_problem;
TRUNCATE TABLE newsletter_queue;
TRUNCATE TABLE newsletter_queue_link;
TRUNCATE TABLE newsletter_queue_store_link;
TRUNCATE TABLE newsletter_subscriber;
TRUNCATE TABLE newsletter_template;
TRUNCATE TABLE password_reset_request_event;
TRUNCATE TABLE paypal_billing_agreement;
TRUNCATE TABLE paypal_billing_agreement_order;
TRUNCATE TABLE paypal_cert;
TRUNCATE TABLE paypal_payment_transaction;
TRUNCATE TABLE paypal_settlement_report;
TRUNCATE TABLE paypal_settlement_report_row;
TRUNCATE TABLE persistent_session;
TRUNCATE TABLE product_alert_price;
TRUNCATE TABLE product_alert_stock;
TRUNCATE TABLE quote;
TRUNCATE TABLE quote_address;
TRUNCATE TABLE quote_address_item;
TRUNCATE TABLE quote_id_mask;
TRUNCATE TABLE quote_item;
TRUNCATE TABLE quote_item_option;
TRUNCATE TABLE quote_payment;
TRUNCATE TABLE quote_shipping_rate;
TRUNCATE TABLE rating_option_vote;
TRUNCATE TABLE rating_option_vote_aggregated;
TRUNCATE TABLE rating_store;
TRUNCATE TABLE rating_title;
TRUNCATE TABLE release_notification_viewer_log;
TRUNCATE TABLE report_compared_product_index;
TRUNCATE TABLE report_event;
TRUNCATE TABLE report_event_types;
TRUNCATE TABLE report_viewed_product_aggregated_daily;
TRUNCATE TABLE report_viewed_product_aggregated_monthly;
TRUNCATE TABLE report_viewed_product_aggregated_yearly;
TRUNCATE TABLE report_viewed_product_index;
TRUNCATE TABLE reporting_counts;
TRUNCATE TABLE reporting_module_status;
TRUNCATE TABLE reporting_orders;
TRUNCATE TABLE reporting_system_updates;
TRUNCATE TABLE reporting_users;
TRUNCATE TABLE review;
TRUNCATE TABLE review_detail;
TRUNCATE TABLE review_entity_summary;
TRUNCATE TABLE review_store;

TRUNCATE TABLE sales_bestsellers_aggregated_daily;
TRUNCATE TABLE sales_bestsellers_aggregated_monthly;
TRUNCATE TABLE sales_bestsellers_aggregated_yearly;
TRUNCATE TABLE sales_creditmemo;
TRUNCATE TABLE sales_creditmemo_comment;
TRUNCATE TABLE sales_creditmemo_grid;
TRUNCATE TABLE sales_creditmemo_item;
TRUNCATE TABLE sales_invoice;
TRUNCATE TABLE sales_invoice_comment;
TRUNCATE TABLE sales_invoice_grid;
TRUNCATE TABLE sales_invoice_item;
TRUNCATE TABLE sales_invoiced_aggregated;
TRUNCATE TABLE sales_invoiced_aggregated_order;
TRUNCATE TABLE sales_order;
TRUNCATE TABLE sales_order_address;
TRUNCATE TABLE sales_order_aggregated_created;
TRUNCATE TABLE sales_order_aggregated_updated;
TRUNCATE TABLE sales_order_grid;
TRUNCATE TABLE sales_order_item;
TRUNCATE TABLE sales_order_payment;
TRUNCATE TABLE sales_order_status;
TRUNCATE TABLE sales_order_status_history;
TRUNCATE TABLE sales_order_status_label;
TRUNCATE TABLE sales_order_status_state;
TRUNCATE TABLE sales_order_tax;
TRUNCATE TABLE sales_order_tax_item;
TRUNCATE TABLE sales_payment_transaction;
TRUNCATE TABLE sales_refunded_aggregated;
TRUNCATE TABLE sales_refunded_aggregated_order;
TRUNCATE TABLE sales_sequence_meta;
TRUNCATE TABLE sales_sequence_profile;
TRUNCATE TABLE sales_shipment;
TRUNCATE TABLE sales_shipment_comment;
TRUNCATE TABLE sales_shipment_grid;
TRUNCATE TABLE sales_shipment_item;
TRUNCATE TABLE sales_shipment_track;
TRUNCATE TABLE sales_shipping_aggregated;
TRUNCATE TABLE sales_shipping_aggregated_order;

TRUNCATE TABLE salesrule;
TRUNCATE TABLE salesrule_coupon;
TRUNCATE TABLE salesrule_coupon_aggregated;
TRUNCATE TABLE salesrule_coupon_aggregated_order;
TRUNCATE TABLE salesrule_coupon_aggregated_updated;
TRUNCATE TABLE salesrule_coupon_usage;
TRUNCATE TABLE salesrule_customer;
TRUNCATE TABLE salesrule_customer_group;
TRUNCATE TABLE salesrule_label;
TRUNCATE TABLE salesrule_product_attribute;
TRUNCATE TABLE salesrule_website;
TRUNCATE TABLE search_query;
TRUNCATE TABLE search_synonyms;
TRUNCATE TABLE sendfriend_log;
TRUNCATE TABLE session;
TRUNCATE TABLE shipping_tablerate;
TRUNCATE TABLE signifyd_case;
TRUNCATE TABLE sitemap;
TRUNCATE TABLE translation;
TRUNCATE TABLE ui_bookmark;
TRUNCATE TABLE url_rewrite;
TRUNCATE TABLE variable;
TRUNCATE TABLE variable_value;
TRUNCATE TABLE vault_payment_token;
TRUNCATE TABLE vault_payment_token_order_payment_link;
TRUNCATE TABLE weee_tax;
TRUNCATE TABLE wishlist;
TRUNCATE TABLE wishlist_item;
TRUNCATE TABLE wishlist_item_option;

INSERT INTO `catalog_category_entity` VALUES (1,3,0,NOW(),NOW(),1,0,0,1), (2,3,1,NOW(),NOW(),'1/2',1,1,0);

INSERT INTO `catalog_category_entity_varchar` VALUES (1,45,0,1, 'Root Catalog'), (2,45,0,2, 'Default Category'), (3,52,0,2, 'PRODUCTS');

INSERT INTO `cataloginventory_stock` VALUES (1,0, 'Default');

INSERT INTO `report_event_types` VALUES (1, 'catalog_product_view', 0), (2, 'sendfriend_product', 0), (3, 'catalog_product_compare_add_product', 0), (4, 'checkout_cart_add_product', 0), (5, ' wishlist_add_product ', 0), (6,' wishlist_share ', 0);

INSERT INTO `sales_order_status` VALUES ('canceled', 'Canceled'), ('closed', 'Closed'), ('complete', 'Complete'), ('fraud', 'Suspected Fraud'), ('holded ',' On Hold '), (' payment_review ',' Payment Review '), (' paypal_canceled_reversal ',' PayPal Canceled Reversal '), (' paypal_reversed ',' PayPal Reversed '), (' pending ',' Pending ' ), ('pending_payment', 'Pending Payment'), ('pending_paypal', 'Pending PayPal'), ('processing', 'Processing');

INSERT INTO `sales_order_status_state` VALUES ('canceled', 'canceled', 1,1), ('closed', 'closed', 1,1), ('complete', 'complete', 1,1), (' fraud ',' payment_review ', 0,1), (' fraud ',' processing ', 0,1), (' holded ',' holded ', 1,1), (' payment_review ',' payment_review ', 1 , 1), ('pending', 'new', 1,1), ('pending_payment', 'pending_payment', 1,0), ('processing', 'processing', 1,1);

INSERT INTO `sales_sequence_meta` VALUES (1, 'order', 0, 'sequence_order_0'), (2, 'invoice', 0, 'sequence_invoice_0'), (3, 'creditmemo', 0, 'sequence_creditmemo_0'), (4 , 'shipment', 0, 'sequence_shipment_0'), (5, 'order', 1, 'sequence_order_1'), (6, 'invoice', 1, 'sequence_invoice_1'), (7, 'creditmemo', 1, ' sequence_creditmemo_1 '), (8,' shipment ', 1,' sequence_shipment_1 ');

INSERT INTO `sales_sequence_profile` VALUES (1,1, NULL, NULL, 1,1,4294967295,4294966295,1), (2,2, NULL, NULL, 1,1,4294967295,4294966295,1), (3,3, NULL, NULL, 1,1,4294967295,4294966295,1), (4,4, NULL, NULL, 1,1,4294967295,4294966295,1), (5,5, NULL, NULL, 1,1,4294967295,4294966295,1), (6,6, NULL, NULL, 1,1,4294967295,4294966295,1), (7,7, NULL, NULL, 1,1,4294967295,4294966295,1), (8,8, NULL, NULL, 1,1,4294967295,4294966295,1);

DROP TABLE sequence_creditmemo_3;
DROP TABLE sequence_creditmemo_4;
DROP TABLE sequence_invoice_3;
DROP TABLE sequence_invoice_4;
DROP TABLE sequence_order_3;
DROP TABLE sequence_order_4;
DROP TABLE sequence_shipment_3;
DROP TABLE sequence_shipment_4;

SET FOREIGN_KEY_CHECKS = 1;

As said before - Delete the var/migration* after each data migration.

To keep CMS content and the attributes created after the first data migration, I comment the EAV Step in config.xml and ignore cms_* in map.xml

The original idea comes from https://www.czettner.com/2018/09/13/magento-2-data-migration.html and is extended by https://www.laberbla.de/2019/07/magento-2-und-das-data-migration-tool/

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.