Error in Product migration

Hi there,

MySQL server version for the right syntax to use near ‘AND entity_id = 1905 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_product_entity_int` `t` WHERE attribute_id = 167 AND value = AND entity_id = 1905 LIMIT 1

That issue occurred because your M1 database had some records which had bad data in the table catalog_product_entity_int. You can run the following SQL in M1 database to list those records at your end:

Select * From catalog_product_entity_int Where value IS NULL or value = '';

Then, you should fix the values of the value field on those records. The correct value should be ‘0’ for that case.
 
You can use the SQL below to mass change such records:

Update catalog_product_entity_int Set value = 0 Where value IS NULL or value = '';

And then, you can continue with the data migration in the step #5.

Regards,
Mall.

Hi there,
 
For more convenience for us to provide you technical support, please reply directly via this thread, instead of adding your new questions in the comment section under your original question. 

why i include configurable and bundle and etc will have error?

You can discard my reply #1 above.
 
That issue occurred because your M1 database had bad data in the table ‘catalog_product_super_attribute_pricing’. Some records in this table had the relationship with none-existing attribute options in the table ‘eav_attribute_options’ in your M1 database. You can run below SQL in your M1 database to list those records:

Select * From catalog_product_super_attribute_pricing Where value_index NOT IN (Select option_id From eav_attribute_option);

Then, you should delete all found records. Once done, you continue with the data migration in step #5 including configurable products as normal. It should not generate the issue as you mentioned above.

But when i reach customer migration, i am getting error too.

Please provide me more details about that issue. I will help to check further on that and get back to you then.

Regards,
Mall.
 
 

Hi there,

Data too long for column ‘suffix’ at row 1. The SQL statement executed was: UPDATE `customer_address_entity` SET `is_active`=:yp0, `entity_id`=:yp1, `parent_id`=:yp2, 

That issue occurred because your M1 database had bad data in the value of the ‘suffix’ attribute (customer address attribute). Max length allowed in M2 for the value of this field is 40 characters: http://prntscr.com/oag267

To resolve that case, you can do steps below:

+ Open the php file at: pub/ub-tool/protected/controllers/Step6Controller.php
and find the code lines:

if (isset($flagUpdateAddress2) && $flagUpdateAddress2) {
    $addressEntity2->update();
}

and replace it with:

if (isset($flagUpdateAddress2) && $flagUpdateAddress2) {
    if (strlen(trim($addressEntity2->suffix)) > 40) {
        $addressEntity2->suffix = substr(trim($addressEntity2->suffix), 0, 40);
    }
    $addressEntity2->update();
}

+ And then, you could continue with the data migration in the step #6. 

Regards,
Mall.
 

Hi there,

CDbCommand failed to execute the SQL statement: SQLSTATE[22001]: String data, right truncated: 1406 Data too long for column ‘suffix’ at row 1. The SQL statement executed was: UPDATE `customer_entity` SET `group_id`=:yp0, `store_id`=:yp1, `is_active`=:yp2, 

Please note that this refers to a similar issue of bad data but it happened with another data object (customer_entity).
 
To handle this case, you can do steps below:

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

if ($flagUpdateCustomer2) {
    $customer2->update();
}

and replace it with:

if ($flagUpdateCustomer2) {
    if (strlen(trim($customer2->suffix)) > 40) {
        $customer2->suffix = substr(trim($customer2->suffix), 0, 40);
    }
    $customer2->update();
}

+ Once done, you can continue with the data migration in step #6 using the CLI: 
php -f bin/ubdatamigration run --step=6

Regards,
Mall.

Hi there,

Incorrect integer value: ” for column ‘remote_ip_long’ at row 1. The SQL statement executed was: INSERT INTO `rating_option_vote` (`option_id`, `remote_ip_long`, `customer_id`, 

That issue indicated that your M1 database had some records which had bad data in the value of the ‘remote_ip_long’ field in the table ‘rating_option_vote’. The values on this field should be a integer value as per Magento’s requirement: http://prntscr.com/oay0we

To continue, you can follow steps below: 
+ Backup your current M1’s database
+ Run the following SQL in your M1’s database to reset the values of ‘remote_ip_long’ field in the table rating_option_vote:

Update rating_option_vote Set remote_ip_long = NULL;

+ And then, you can continue with the data migration in step #8 by running the CLI command:
php -f bin/ubdatamigration run --step=8;

Regards,
Mall.

Hi there,

Please provide me the phpMyAdmin credentials of your Magento1 database, I will help to check further and help you out.
 
PS. Please mark your reply private or simply switch this ticket to private mode, then it’s safe to share your site info here. Only you and our technical team can access. 

Regards,
Mall.

Hi there, 
So, please try to run the querry below in your M2 database: 

SELECT * FROM `rating_option_vote` ORDER BY `remote_ip` ASC;

Then capture the result of such query and send over to us. We will check and get back to you then. 
PS. If possible, please consider moving your instance to a staging server where we can help debug further. All our other users were proceeding migration on the staging servers as well. This will save time for both you and our team. 
Regards,
Ubertheme team

Hi there, 
 
Thanks for your quick follow up. 
 
It’s pity that from the new query’s result, we could still not figure out the bad data in the ‘rating_option_vote’ table. 
We need to detect the bad data in the value of the ‘remote_ip_long’ field though.  
 
Under this circumstance, please help to find a way to grant us access to your M1 database as noted in our reply #10 above: 

Please provide me the phpMyAdmin credentials of your Magento1 database, I will help to check further and help you out.

 
Thanks for your patience.
Regards,
Ubertheme team
 

Hi there,
 
While you figure it out to share us access to your M1 db, you can try this workaround first: 

+ Open the php at: pub/ub-tool/protected/controllers/Step8Controller.php
and find the code line (please note you will see two similar code lines, you need to replace both lines with the new code below): 

$model2->remote_ip_long = ip2long($model->remote_ip);

and replace it with:

$model2->remote_ip_long = (int)ip2long($model->remote_ip);

+ And then continue with the data migration in step #8: 
php -f bin/ubdatamigration run --step=8;

Regards,
Mall.

Hi there, 
Glad that the new tweak code helps. 
Please verify the migrated data at your end. If there’s anything else we can help, please let us know. 
Regards,
Ubertheme team