Error migrating orders

This error interrupts the import of the orders after importing 600+ orders:

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: '0000-00-00 00:00:00' for column 'updated_at' at row 1. The SQL statement executed was: INSERT INTO `quote` (`store_id`, `updated_at`, `is_active`, `is_virtual`, `is_multi_shipping`, `items_count`, `items_qty`, `orig_order_id`, `store_to_base_rate`, `store_to_quote_rate`, `grand_total`, `base_grand_total`, `customer_group_id`, `customer_note_notify`, `customer_is_guest`, `trigger_recollect`, `is_persistent`, `created_at`, `customer_tax_class_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13, :yp14, :yp15, :yp16, :yp17, :yp18)

Tried this as you instructed:

Update `mage_sales_flat_quote` Set `updated_at` = NULL Where `updated_at` = '0000-00-00 00:00:00'; 

It did not help and it affected 0 rows in database. I checked that table and there are no 0000-00-00 00:00:00 values in that table in updated_at field. There is however a converted_at field that has this value, but I guess that is not the one meant here.

7 answers

Profile photo of Mall Staff 184060.00 $tone April 20, 2017
Public

Hi there,
So let’s open the php file at the path: pub/ub-tool/protected/controllers/Step7Controller.php
And find to the code lines:

if (empty($quote2->created_at)) {
$quote2->created_at = date("Y-m-d H:i:s");
}

and replace it by code lines:

if (empty($quote2->created_at)) {
$quote2->created_at = date("Y-m-d H:i:s");
}
if ($quote2->updated_at == '0000-00-00 00:00:00') {
$quote2->updated_at = null;
}

and then, you can continue with data migration in the step #7.
You should use CLI command to continue: php -f bin/ubdatamigration run --step=7;
And tell me know how it goes.
Regards,
Mall.

#1
Profile photo of krantila 660.00 $tone April 20, 2017
Public

Still the same error.
Status: fail
Message: CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value: ‘0000-00-00 00:00:00’ for column ‘updated_at’ at row 1. The SQL statement executed was: INSERT INTO `quote` (`store_id`, `is_active`, `is_virtual`, `is_multi_shipping`, `items_count`, `items_qty`, `orig_order_id`, `store_to_base_rate`, `store_to_quote_rate`, `grand_total`, `base_grand_total`, `customer_group_id`, `customer_note_notify`, `customer_is_guest`, `trigger_recollect`, `is_persistent`, `created_at`, `customer_tax_class_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13, :yp14, :yp15, :yp16, :yp17)

#2
Profile photo of Mall Staff 184060.00 $tone April 20, 2017
Public

Hi krantila,
So, let’s provide me the ssh credentials, admin credentials and web root folder path of your Magento2.
I will check further and reply you later.
Regards,
Mall.

#3
Profile photo of Mall Staff 184060.00 $tone April 20, 2017
Public

Hi there,
I have checked your working and see that issue related to current settings of your MySQL. You can view more about this settings at this post: https://dev.mysql.com/doc/refman/5.7/en/sql-mode.html#sqlmode_no_zero_date
To handle that case, i have changed the code lines:

if ($quote2->updated_at == '0000-00-00 00:00:00') {
$quote2->updated_at = null;
}

to the code lines:

if ($quote2->updated_at === '0000-00-00 00:00:00' || empty($quote2->updated_at)) {
$quote2->updated_at = date("Y-m-d H:i:s");
}

And now you can continue: http://i.prntscr.com/f3f091c9d9c842f5a56c3d18622e15d1.png
Regards,
Mall.

#5
Profile photo of Mall Staff 184060.00 $tone April 21, 2017
Public

Hi there,
Yes, let’s follow full steps in our guide in the Readme.html and contact me if you need further assistance.
Regards,
Mall.

#7

Please login or Register to Submit Answer

Written By

Comments