Order Timestamp are incorrect after data migration

Hi 
I have the same issue and I have the latest version of the software. I exactly follow the same step but I get an error 

Step 3: Re-update the logging data of our migration tool by running the below SQL commands:

+ Run this SQL in your M1 database to get the minimum value of the sales order’s created time which need to be migrated:

SELECT MIN(created_at) FROM `sales_flat_order`;

After running that SQL, you will see the needed string date time value, then use that value to replace the data time string in bold in the following SQL:

UPDATE `ub_migrate_map_step_7` SET `created_time` = '2000-01-01 00:00:00', `offset` = 0 WHERE `entity_name` = 'sales_flat_order';

and then, run that SQL in your M2 database.

Step 4: Once done, open your terminal and go to your M2 folder and run this CLI command to run delta update for the sales order data in step #7:

php -f bin/ubdatamigration run --step=7 --mode=update --timezone='America/New_York';

Finally, clean M2 cache and check the issue on migrated sales order’s time you mentioned again.

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘48251’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_payment_transaction` (`order_id`, `payment_id`, `is_closed`, `transaction_id`, `txn_id`, `txn_type`, `created_at`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6)

Then run this step again and re run the command again

That issue occurred because your M2 site has sales order tax data added in M2 while you checked the ‘KEEP ORIGINAL IDs’ settings in step #7 of our migration tool (The Keep Original IDs requires your M2 to be a fresh instance).
 
To solve that issue, you need to delete the sale tax data records which was added in M2 as follows:

+ Run the below SQL in your M2 database:

Delete From sales_payment_transaction Where transaction_id = 48251;

+ Then, you can continue with data migration in step #7 as normal. 
I have 267976 number of order and running the update for last 8 hrs and suddenly this error popups us and now I check the order which are there in magento 2 and magento 1 but the time is not fixed.
What should I need to do to fix this?

14 answers

Profile photo of Mall Staff 141850.00 $tone June 11, 2020
Public

Hi Argha,

What should I need to do to fix this?

Please let me know what is the timezone you enabled in your M1 site. 

Regards,
Mall. 

#1
Profile photo of Mall Staff 141850.00 $tone June 11, 2020
Public

Hi Argha,

America/New_York

Please make sure that this is the timezone you enabled in your M1 site. Then, please try with these steps once again:

+ Run the following SQL query in your M2 database:

UPDATE `ub_migrate_map_step_7` SET `created_time` = '2000-01-01 00:00:00' WHERE `entity_name` = 'sales_flat_order';

+ Once done, you run the delta migration in step with the ‘update’ mode using this command:

php -f bin/ubdatamigration run --step=7 --mode=update --timezone='America/New_York';

Finally, clean M2 cache and check the issue on the migrated sales order’s time you mentioned again.

Regards,
Mall.

#3
Profile photo of Argha Bhattacharya 140.00 $tone June 11, 2020
Public

Hi,
 
Yes I follow the same step what you mentioned above. But the error comes in 

CDbCommand failed to execute the SQL statement: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry ‘48251’ for key ‘PRIMARY’. The SQL statement executed was: INSERT INTO `sales_payment_transaction` (`order_id`, `payment_id`, `is_closed`, `transaction_id`, `txn_id`, `txn_type`, `created_at`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6)

Then I delete that particular ID then re run again popups with another ID. So Finally what I did I truncate the entire table sales_payment_transaction. 

Again re run now it is running don’t know what would happened. Waiting for the result.

#4
Profile photo of Mall Staff 141850.00 $tone June 11, 2020
Public

Hi Argha,

Again re run now it is running don’t know what would happened. Waiting for the result.

Yes, please wait until it finishes. And let me know how it goes then.

Regards,
Mall.

#5
Profile photo of Argha Bhattacharya 140.00 $tone June 11, 2020
Public

Hi,
Everything is successfully completed but unfortunately, the time doesn’t change. Also new order with delta migration it stopped migration. I am totally frustrated just because I am dealing with a huge data set. It’s killing my time at one run 8 hrs 10hrs.

Now I am trying to reset the step 7 and will try to migrate the orders again. Now the most painful part of reset is it is taking a lot of time. Deleting the table content one by one with your script.

You should have been solved this issue by resetting the step at once I mean by truncating the tables related to the step.

Now what is the solution I mean is there any way to reset the step quickly? 

And after resetting the step what should I need to do to get the proper time of the order.

My server time is below

Local time: Thu 2020-06-11 15:10:18 EDT
                  Universal time: Thu 2020-06-11 19:10:18 UTC
                        RTC time: Thu 2020-06-11 19:10:19
                       Time zone: America/New_York (EDT, -0400)
       System clock synchronized: yes
systemd-timesyncd.service active: yes
                 RTC in local TZ: no

My Magento time is link -- https://prnt.sc/sy3me9

Eastern Standard Time (America/New_York)

Also, the PHP.ini set to America/New_York.

Now what step should I need to do to get the proper time?

Additional information: My version is the latest one. Purchased the plugin just a few days back.

Magento Version : Magento ver. 2.3.5-p1

#6
Profile photo of Argha Bhattacharya 140.00 $tone June 11, 2020
Public

Additional information For M1 Site:
Server time : 
timedatectl
      Local time: Thu 2020-06-11 15:16:15 EDT
  Universal time: Thu 2020-06-11 19:16:15 UTC
        Timezone: America/New_York (EDT, -0400)
     NTP enabled: yes
NTP synchronized: yes
RTC in local TZ: no
      DST active: yes
Last DST change: DST began at
                  Sun 2020-03-08 01:59:59 EST
                  Sun 2020-03-08 03:00:00 EDT
Next DST change: DST ends (the clock jumps one hour backwards) at
                  Sun 2020-11-01 01:59:59 EDT
                  Sun 2020-11-01 01:00:00 EST

Php.ini time not set to anything.

Magento time: https://prnt.sc/sy3qoi

Eastern Standard Time (America/New_York)

#7
Profile photo of Argha Bhattacharya 140.00 $tone June 12, 2020
Public

Sorry, I am working for a US client and I can’t provide you any of the information as per the NDA. What I can do is we can get on a call and I will share my screen with your technical person.

#9
Profile photo of ubdev Staff 63620.00 $tone June 12, 2020
Public

Hi Argha, 
 
It’s hard to provide you assistance without taking a closer look at your instance to debug the root cause of the issue. 
 
Actually, we did test the workaround suggested to you in our local instance and the issue was solved accordingly.
 
Please make sure your M1 was using exactly the time zome: Eastern Standard Time (America/New_York) all the time.
 
Under your circumstance, please try once again on a fresh M2 instance. We know this takes you extra time, yet this is the best option since we do not have access to debug in your instance. 
 
Please note, when you re-migrating to the Step #7, please make sure you run the following specific CLI command:

php -f bin/ubdatamigration run --step=7 --timezone='America/New_York';

(Do not run the migration in GUI or using the regular CLI command: php -f bin/ubdatamigration run --step=7 in this step 7)
 
We hope that helps.
Regards,
Ubertheme team

#10
Profile photo of Argha Bhattacharya 140.00 $tone June 12, 2020
Public

Hi,
 
I seriously don’t understand what is happening. Now I reset step 7 and try to migrate using 

php -f bin/ubdatamigration run --step=7 --timezone='America/New_York';

And now this time if a order at M1 is Oct 13, 2015 8:00:37 AM and the same order at M2 is Oct 13, 2015 4:00:37 PM

#11
Profile photo of Mall Staff 141850.00 $tone June 14, 2020
Public

Hi Argha,

And now this time if a order at M1 is Oct 13, 2015 8:00:37 AM and the same order at M2 is Oct 13, 2015 4:00:37 PM

Did you see that time values in the backend of your M1 and M2 instances? Did you set the timezone of the Admin user to ‘America/New_York’?

For your information, Magento 2 stores the date time values as UTC time in the database.

Regards,
Mall.

#12
Profile photo of Argha Bhattacharya 140.00 $tone June 14, 2020
Public

Hi,
 
Both the Magento Admin set to America/New_York. See the screenshots attached below.

M1 Admin time zone — https://prnt.sc/szfdu0

M2 Admin time zone — https://prnt.sc/szfelk

“For your information, Magento 2 stores the date time values as UTC time in the database.”

My basic goal is to transfer the orders from magento 1 to magento 2 with the M1 order date and time.

Additional Information.
Please see the above mention information.

I setup Magento freshly and again setup the Ub DataMigration and started the Migration again. Now I am at step 5 Migrating the product. Then it comes to customer and order. Now what step should I need to follow to get the proper time as magento1 has.

I am using the same RDS server for MySQL and the database timezone is the same. My M1 and M2 is using the same RDS server so the timezone Is the same.

and both the EC2 server M1 and M2 timezone are exactly same as I mentioned above.

and both the admin are set to America/New_York.

So what step we should follow to get the timezone? 

I am seriously confused.
 

#13
Profile photo of Mall Staff 141850.00 $tone June 15, 2020
Public

Hi Argha,

M2 Admin time zone — https://prnt.sc/szfelk

In M2, please update the setting of the locale options as this screenshot: https://prnt.sc/szrqf8

I setup Magento freshly and again setup the Ub DataMigration and started the Migration again. Now I am at step 5 Migrating the product. Then it comes to customer and order. Now what step should I need to follow to get the proper time as magento1 has.

Please continue with the data migration instep #6 as normal.

In step #7, after you finish the settings in this step via GUI, please make sure you run data migration in step #7 using the CLI command:

php -f bin/ubdatamigration run --step=7 --timezone='America/New_York';

Regards,
Mall.

#14

Please login or Register to Submit Answer

Written By

Comments