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
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.
Enabling UTC timezone worked.
Now I am getting SQLSTATE[22007]: Invalid datetime format: 1366 Incorrect string value: ‘\xD0’ for column quote_address
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.
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)
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.
I tried updating the column and now I get this error:
Error in query (1300): Invalid utf8 character string: ‘\xFCrich’
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.
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.
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
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.
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.