Error in Product migration

CDbCommand failed to execute the SQL statement: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your 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
 
I tried 2 times both having error at the same percentage.
Please advise.

  1. Profile photo of ozhutozhut 160.00 $tone July 4, 2019
    If i only import simple products, ignore configurable, bundle and etc, den i can proceed the migration with no error. But when i reach customer migration, i am getting error too. Is there any limit on those records migrated? I have 4x,xxx customer records need to be migrated. Please help. Thanks.
  2. Profile photo of ozhutozhut 160.00 $tone July 4, 2019
    To add on, why i include configurable and bundle and etc will have error?

17 answers

Profile photo of Mall Staff 184060.00 $tone July 4, 2019
Public

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.

#1
Profile photo of Mall Staff 184060.00 $tone July 4, 2019
Public

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.
 
 

#2
Profile photo of ozhut 160.00 $tone July 4, 2019
Public

Customer migration error as following
 
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_address_entity` SET `is_active`=:yp0, `entity_id`=:yp1, `parent_id`=:yp2, `created_at`=:yp3, `updated_at`=:yp4, `country_id`=:yp5, `firstname`=:yp6, `lastname`=:yp7, `street`=:yp8, `telephone`=:yp9, `city`=:yp10, `suffix`=:yp11, `company`=:yp12, `region`=:yp13, `postcode`=:yp14 WHERE `customer_address_entity`.`entity_id`=’26744′

#3
Profile photo of Mall Staff 184060.00 $tone July 4, 2019
Public

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.
 

#4
Profile photo of ozhut 160.00 $tone July 5, 2019
Public

I am still getting error after add on the additional code. Please help.
 
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, `disable_auto_group_change`=:yp3, `failures_num`=:yp4, `entity_id`=:yp5, `website_id`=:yp6, `email`=:yp7, `created_at`=:yp8, `updated_at`=:yp9, `default_billing`=:yp10, `default_shipping`=:yp11, `created_in`=:yp12, `firstname`=:yp13, `lastname`=:yp14, `suffix`=:yp15, `password_hash`=:yp16 WHERE `customer_entity`.`entity_id`=’24155′

#5
Profile photo of Mall Staff 184060.00 $tone July 5, 2019
Public

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.

#6
Profile photo of ozhut 160.00 $tone July 5, 2019
Public

Hi, Customer migration works fine after adding in the piece of code. 
Now i am facing issue in step 8, this is the error.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 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`, `entity_pk_value`, `rating_id`, `percent`, `value`, `remote_ip`, `review_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8)

#7
Profile photo of Mall Staff 184060.00 $tone July 5, 2019
Public

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.

 

#8
Profile photo of ozhut 160.00 $tone July 8, 2019
Public

Hi, there is error is update remote_ip_long to null, the erros is column remote_ip_long cannot be null
I update the value into 0, but i am still getting the error as following, please help. Thanks.
CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 1366 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`, `entity_pk_value`, `rating_id`, `percent`, `value`, `remote_ip`, `review_id`) VALUES (:yp0, :yp1, :yp2, :yp3, :yp4, :yp5, :yp6, :yp7, :yp8)

#9
Profile photo of Mall Staff 184060.00 $tone July 8, 2019
Public

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.

#10
Profile photo of ubdev Staff 97690.00 $tone July 9, 2019
Public

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

#12
Profile photo of ubdev Staff 97690.00 $tone July 9, 2019
Public

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
 

#14
Profile photo of Mall Staff 184060.00 $tone July 9, 2019
Public

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.

#15
Profile photo of ozhut 160.00 $tone July 9, 2019
Public

The migration is complete with no error after amend the piece of code. Thanks for the help. 
Will look into the accuracy of the migrated data. Thanks.

#16
Profile photo of ubdev Staff 97690.00 $tone July 9, 2019
Public

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

#17

Please login or Register to Submit Answer

Written By

Comments