Duplicate entry during Step 7 Delta Migration

Hello,
I’ve run several successful migration tests, and was hoping to perform the live delta migration today. On Step 7, I’m encountering the following…

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘132’ for key ‘PRIMARY’

I presume this is related to keeping original IDs and/or test data. Any recommendations on how to continue would be appreciated!
Thanks!

7 answers

Profile photo of Mall Staff 144590.00 $tone July 30, 2020
Public

Hi there,

I presume this is related to keeping original IDs and/or test data. Any recommendations on how to continue would be appreciated!

Yes, that is right. You can try with these steps:

+ Run the following SQL queries in your M2 database to clean all sample/testing data in the sales data section in M2:

Delete From salesrule Where rule_id NOT IN (Select m2_id From ub_migrate_map_step_7 where entity_name = 'salesrule');
Delete From quote Where entity_id NOT IN (Select m2_id From ub_migrate_map_step_7_quote where entity_name = 'sales_flat_quote' OR entity_name = 'sales_flat_quote_active');
Delete From quote_item Where item_id NOT IN (Select m2_id From ub_migrate_map_step_7_quote_item where entity_name = 'sales_flat_quote_item');
Delete From quote_address Where address_id NOT IN (Select m2_id From ub_migrate_map_step_7_quote_address where entity_name = 'sales_flat_quote_address');
Delete From sales_order Where entity_id NOT IN (Select m2_id From ub_migrate_map_step_7_order where entity_name = 'sales_flat_order');
Delete From sales_order_item Where item_id NOT IN (Select m2_id From ub_migrate_map_step_7_order_item where entity_name = 'sales_flat_order_item');
Delete From sales_order_address Where entity_id NOT IN (Select m2_id From ub_migrate_map_step_7_order_address where entity_name = 'sales_flat_order_address');
Delete From sales_invoice Where entity_id NOT IN (Select m2_id From ub_migrate_map_step_7_invoice where entity_name = 'sales_flat_invoice');
Delete From sales_invoice_item Where entity_id NOT IN (Select m2_id From ub_migrate_map_step_7_invoice_item where entity_name = 'sales_flat_invoice_item');

+ Once done, you can continue with the data migration in step #7.
Regards,
Mall.
 

#1
Profile photo of newpro 90.00 $tone July 30, 2020
Public

Hi Mall,
Thanks for the fast reply, as usual. I’ve run the suggested queries, flushed caches, updated DB settings in Step 1, but still encounter the same error.
I am completely fine with deleting all Customers, Sales, and Other data from M2 and performing those migration steps again. I assume truncating order tables (A), customer tables (B), and review tables (C) might work for this?

I’m open to any other suggestions.
 
A)

TRUNCATE TABLE `gift_message`;
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 `reporting_orders`;
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_invoiced_aggregated`;
TRUNCATE TABLE `sales_invoiced_aggregated_order`;
TRUNCATE TABLE `sales_invoice_comment`;
TRUNCATE TABLE `sales_invoice_grid`;
TRUNCATE TABLE `sales_invoice_item`;
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_history`;
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_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 `tax_order_aggregated_created`;
TRUNCATE TABLE `tax_order_aggregated_updated`;

 
B)

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_log`;
TRUNCATE TABLE `customer_visitor`;
TRUNCATE TABLE `persistent_session`;
TRUNCATE TABLE `wishlist`;
TRUNCATE TABLE `wishlist_item`;
TRUNCATE TABLE `wishlist_item_option`;

 
C)

TRUNCATE TABLE `review`;
TRUNCATE TABLE `review_detail`;
TRUNCATE TABLE `review_entity_summary`;
TRUNCATE TABLE `review_store`;

 
 
 

#2
Profile photo of Mall Staff 144590.00 $tone July 30, 2020
Public

Hi there,

I am completely fine with deleting all Customers, Sales, and Other data from M2 and performing those migration steps again. I assume truncating order tables (A), customer tables (B), and review tables (C) might work for this?

That’s an option, however it’s not recommended. Besides, if you followed that option, you have to reset step #7 of our migration tool first. Otherwise, it will remove all data records being migrated by our migration tool.

You should follow steps as suggested in my reply #1 instead. And then run Step #7 using the CLI command:
php -f bin/ubdatamigration run --step=7

And provide me the details of errors message if you get any.

Regards,
Mall.

#3
Profile photo of newpro 90.00 $tone July 30, 2020
Public

Hi Mall,
I understand, and appreciate your answer. Below is CLI output.

[Processing][run] in step #7: …………………………………………..
Status: fail
Message: CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘132’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_order_tax` (`tax_id`, `order_id`, `code`, `title`, `percent`, `amount`, `priority`, `position`, `base_amount`, `process`, `base_real_amount`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10)

Thanks again.

#4
Profile photo of Mall Staff 144590.00 $tone July 30, 2020
Public

Hi there,

1062 Duplicate entry ‘132’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_order_tax` (`tax_id`, `order_id`, `code`, `title`, `percent`, `amount`, `priority`, `position`, `base_amount`, `process`, `base_real_amount`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10)

Let’s run following SQL in your M2 database:

Delete From sales_order_tax Where tax_id = 132;

+ Once done you continue with data migration in step #7:
php -f bin/ubdatamigration run --step=7
and tell me know how it goes.
Regards,
Mall.

#5
Profile photo of newpro 90.00 $tone July 30, 2020
Public

Hi Mall,
Your reply arrived faster than I could find this post and try it out. Great work. I needed to repeat it for a 2nd tax_id, but it then completed successfully.
I’m off to proceed with the other deltas and start testing.
Thank you very much. 

#6
Profile photo of ubdev Staff 65720.00 $tone July 30, 2020
Public

Hi Taber Landwerlen, 
We’re glad that it helped. Please continue with the process at your end. In case you come across any issue, please let us know here, we will work closely with you then. 
PS. Our team has just replied to your email as well, we hope you can help us with a few quick survey questions there. 
Regards,
Ubertheme team

#7

Please login or Register to Submit Answer

Written By

Comments