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
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.
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.
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