Getting error during migrate sales data

I am  getting  error  when migrate sales data step 7 in pro version 

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xE2\x80’ for column ‘telephone’ at row 1 

Thank you 
Mona Bishnoi

3 answers

Profile photo of Mall Staff 184060.00 $tone June 23, 2020
Public

Hi Mona,

CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 Incorrect string value: ‘\xE2\x80’ for column ‘telephone’ at row 1 

Please try with these steps:

+ Run the following SQL query in your M1 database:

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

+ Once done, you continue with the data migration in step #7 using this command:
php -f bin/ubdatamigration run --step=7;

Regards,
Mall.

#1
Profile photo of mtcmedia 220.00 $tone November 7, 2020
Public

instead i would reccomend do to the following on the magento 1 database ( general— see below for a more detailed step ) .

update sales_flat_quote_address set telephone  = replace(replace ( telephone,unhex("E280AD"),''),unhex("E280AC"),'');

Changing the encoding will not sort anything out or if you change in mysql my.cnf the datails.. that will also not sort anything out.
 
replacing the unhexed data from the error would be the correct way to sort it out. and if you want to see more details about how it looks ( perform the following modifications so that you get the row where the error is: )
in

pub/ub-tool/yii-1.1.22/db/schema/CDbCommandBuilder.php

at the function

createInsertCommand

update

$sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')';

to look something like
 

       echo $sql="INSERT INTO {$table->rawName} (".implode(', ',$fields).') VALUES ('.implode(', ',$placeholders).')';
echo "\n\n\n"; print_r($data);
echo "\n\n\n";

then run the step 7 again to get the error and view the data
and then you can do the following command in SQL:

SELECT  telephone,hex(telephone) from sales_flat_quote_address where quote_id =  [Quote id generated by the error]

then execute the update

update sales_flat_quote_address set telephone  = replace(replace ( telephone,unhex("E280AD"),''),unhex("E280AC"),'') where quote_id= [QUOTE ID FROM ABOVE ERROR / QUERY]

then run the step 7 again and see the miracle happen
 
Oh. And remember to revert the

pub/ub-tool/yii-1.1.22/db/schema/CDbCommandBuilder.php 

back to it’s original state.

#2
Profile photo of ubdev Staff 98550.00 $tone November 9, 2020
Public

Hi mtcmedia,
 
The issue associated with the workaround you shared referred to 1 bad data case in the sales data section in M1. Since the bad data issue varies depending on each instance, it would be best to review on a case-by-case basis.
 
If you find the workaround works for you, you can apply though.
 
Regards,
Ubertheme team

#3

Please login or Register to Submit Answer

Written By

Comments