SQLSTATE[22007]: Invalid datetime format

On step 7 I am getting the error SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value on sales_order_status_history.
How can I fix this?

11 answers

Profile photo of Mall Staff 184060.00 $tone May 21, 2021
Public

Hi there,

On step 7 I am getting the error SQLSTATE[22007]: Invalid datetime format: 1292 Incorrect datetime value on sales_order_status_history.

Can you provide me further details of the error message you got in step #7?

Also, please contact the system admin of your M2 server to get further help to change the timezone setting in MySQL of your M2 server (Please enable the UTC timezone). Once done, you continue with data migration in step #7 as normal. 
 
Regards,
Mall.

#1
Profile photo of coi8zuy11168 170.00 $tone May 21, 2021
Public

Enabling UTC timezone worked.
Now I am getting SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: ‘\xD0’ for column quote_address
 

#2
Profile photo of Mall Staff 184060.00 $tone May 21, 2021
Public

Hi there,

1366 Incorrect string value: ‘\xD0’ for column quote_address

Please provide me further details of that error message to see the column in the table ‘sals_flat_quote_address’ which caused that error message. I will provide you a workaround to solve that issue then.

Regards,
Mall.

#3
Profile photo of coi8zuy11168 170.00 $tone May 21, 2021
Public

CDbCommand failed to execute the SQL statement: SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: ‘\xD0’ for column `devsite`.`quote_address`.`region` at row 1. The SQL statement executed was: INSERT INTO `quote_address` (`quote_id`, `save_in_address_book`, `same_as_billing`, `collect_shipping_rates`, `weight`, `subtotal`, `base_subtotal`, `subtotal_with_discount`, `base_subtotal_with_discount`, `tax_amount`, `base_tax_amount`, `shipping_amount`, `base_shipping_amount`, `discount_amount`, `base_discount_amount`, `grand_total`, `base_grand_total`, `free_shipping`, `address_id`, `created_at`, `updated_at`, `address_type`, `region`, `postcode`, `country_id`, `shipping_tax_amount`, `base_shipping_tax_amount`, `applied_taxes`, `shipping_discount_amount`, `base_shipping_discount_amount`, `subtotal_incl_tax`, `discount_tax_compensation_amount`, `base_discount_tax_compensation_amount`, `shipping_discount_tax_compensation_amount`, `shipping_incl_tax`, `base_shipping_incl_tax`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8, :yp9, :yp10, :yp11, :yp12, :yp13, :yp14, :yp15, :yp16, :yp17, :yp18, :yp19, :yp20, :yp21, :yp22, :yp23, :yp24, :yp25, :yp26, :yp27, :yp28, :yp29, :yp30, :yp31, :yp32, :yp33, :yp34, :yp35)

#4
Profile photo of Mall Staff 184060.00 $tone May 24, 2021
Public

Hi there,
 
I am sorry for replying to you this late as our team has just been back from our weekend. 

1366 Incorrect string value: ‘\xD0’ for column `devsite`.`quote_address`.`region` at row 1.

To solve that issue, you can try these steps:

1. Run the following SQL query in your M1 DB:

Update sales_flat_quote_address set region = convert(binary convert(region using latin1) using utf8);

2. And then, you continue with data migration in step #7 by running the CLI command:

php -f bin/ubdatamigration run --step=7

Regards,
Mall.

#5
Profile photo of coi8zuy11168 170.00 $tone May 25, 2021
Public

I added code for the quote section to identify the address_id : echo “ID:”.$model->address_id;

I found this text wasn’t accepted in the region. I am guessing this is a length issue:
Московская область, город Дзержинский, улица Лесная, дом 17 квартира 65

Also telephone numbers that have a + sign at the beginning aren’t accepted.

#7
Profile photo of Mall Staff 184060.00 $tone May 25, 2021
Public

Hi there,

I found this text wasn’t accepted in the region. I am guessing this is a length issue:
Московская область, город Дзержинский, улица Лесная, дом 17 квартира 65

No, that occurred because that string value had some Invalid utf8 characters. And the steps suggested in my reply #5 helped to solve that issue.

Also telephone numbers that have a + sign at the beginning aren’t accepted.

You can try these steps:

1. Run the following SQL query in your M1 DB:

Update sales_flat_quote_address set telephone = convert(binary convert(telephone using latin1) using utf8);

2. And then, you continue with data migration in step #7 by running the CLI command:

php -f bin/ubdatamigration run --step=7

Regards,
Mall.

#8
Profile photo of coi8zuy11168 170.00 $tone May 25, 2021
Public

I also notice a bug in your code on line 2431:
elseif (in_array($key, array(‘postcode’, ‘telephone’, ‘fax’)) AND strlen($val) > 20) {
Telephone and Fax are 255 character length in Magento2 database

#9
Profile photo of coi8zuy11168 170.00 $tone May 25, 2021
Public

None of the updates were able to fix the character issues. The only way I could resolve the issue was to change the telephone, city and region tables to varbinary on the M2 side and then switch them back to varchar after migration.

#10
Profile photo of Mall Staff 184060.00 $tone May 25, 2021
Public

Hi there,

elseif (in_array($key, array(‘postcode’, ‘telephone’, ‘fax’)) AND strlen($val) > 20) {
Telephone and Fax are 255 character length in Magento2 database

Yes, those are the changes in that data table from the recent versions of Magento. We will consider updating these changes in the next release of our migration tool.

Please try the steps below at your end:

1. Open the PHP file at: pub/ub-tool/protected/controllers/Step7Controller.php
and find the code lines:

if (in_array($key, array('region', 'city')) AND strlen($val) > 40) {
$val = substr($val, 0, 40);
} elseif (in_array($key, array('postcode', 'telephone', 'fax')) AND strlen($val) > 20) {
$val = substr($val, 0, 20);
}

and replace it with:

if (in_array($key, array('postcode')) AND strlen($val) > 20) {
$val = substr($val, 0, 20);
}

2. And find the code lines:

if (in_array($key, array('region', 'shipping_method', 'city')) AND strlen($val) > 40) {
$val = substr($val, 0, 40);
} elseif (in_array($key, array('firstname', 'lastname', 'postcode', 'telephone', 'fax')) AND strlen($val) > 20) {
$val = substr($val, 0, 20);
}

and replace it with:

if (in_array($key, array('shipping_method')) AND strlen($val) > 120) {
$val = substr($val, 0, 120);
} elseif (in_array($key, array('postcode')) AND strlen($val) > 20) {
$val = substr($val, 0, 20);
}

3. And then, you can continue with data migration in step #7.

Regards,
Mall.

#11

Please login or Register to Submit Answer

Written By

Comments