Hello UberTheme,
on the previous week we bought your “UB Data Migration Pro” extension and now are migrating the database (we are on the 7-th step).
All previous steps except the 5-th seem to be completed well. During the product migration we encountered with the next error.
After all product had been migration and product links were started we received:
[Processing][run] in step #5: …………………………………………………………………………………………………………………………………………..
Status: fail
Message: 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 MariaDB server version for the right syntax to use near ‘AND entity_id = 3534 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_product_entity_int` `t` WHERE attribute_id = 258 AND value = AND entity_id = 3534 LIMIT 1
but the attribute with id = 258 is not even a product attribute, but customer’s one.
Please advise how can we fix this and finish the products migration.
Best regards,
Ivan.
-
Peter ⋅60.00 $tone June 23, 2020
Also the table catalog_product_relation is empty, so there is no bundle association.
Peter ⋅60.00 $tone
June 23, 2020
9 answers
Hi Ivan,
on the previous week we bought your “UB Data Migration Pro” extension and now are migrating the database (we are on the 7-th step).
So what is the version of our module and what is the version of M2 in your instance now?
Message: 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 MariaDB server version for the right syntax to use near ‘AND entity_id = 3534 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_product_entity_int` `t` WHERE attribute_id = 258 AND value = AND entity_id = 3534 LIMIT 1
That issue occurred because your M1 database had at least one bad data record in the ‘catalog_product_super_attribute_pricing’ table. These data records had the relation with a none-existing attribute option in the ‘eav_attribute_option’ table.
To solve that issue, you can try with these steps:
+ Run the following SQL query in your M1 database to delete the bad data records I mentioned:
DELETE FROM catalog_product_super_attribute_pricing WHERE value_index IS NULL OR value_index = '' OR value_index NOT IN (SELECT option_id FROM eav_attribute_option);
+ Once done, you can continue with the data migration in step #5 by running the CLI command:
php -f bin/ubdatamigration run --step=5;
Regards,
Mall.
Hello Mall,
thanks a lot for your help!
I applied this fix and now step 5 was able to finish successfully.
catalog_product_relation table is also filled.
I will check the products on the backend and let you know
Best regards,
Ivan.
Hi Ivan,
We’re glad that it helps.
In case you have any additional questions, feel free to get back to us here.
Regards,
Ubertheme team
Hello UberTheme,
We have a very urgent request.
After the migration we discovered that the migrated Configurable Products have no prices (please see attached screen),
while we had it on the old site.
The sql query select * from catalog_product_entity_decimal where attribute_id = 77 and entity_id = 4863;
(77 is attribute_id of the price attribute, 4863 is the id of one of our configurables)
returns nothing.
Could you please advise.
Best regards,
Ivan.
Hi Ivan,
After the migration we discovered that the migrated Configurable Products have no prices (please see attached screen),
while we had it on the old site.
The sql query select * from catalog_product_entity_decimal where attribute_id = 77 and entity_id = 4863;
(77 is attribute_id of the price attribute, 4863 is the id of one of our configurables)
returns nothing.
Yes, that is right. It refers to the working rule of Magento 2 on the pricing of the Configurable products.
This is a difference compared with Magento 1. You can learn more about the Configurable products in M2 via this post.
PS. Since our team is off during the weekend, our apology for a certain delay for response if you reach out to us during this time.
Regards,
Mall.
Hello guys,
regarding the previous thing with the lack of prices for configurable products, it’s a very strange decision cause it breaks the data integrity.
Because I just expected all the data from M1 to be copied to M2 as close as possible regardless of the new logic in M2 and then in M2 I could decide on my own what to do with that prices. So, I needed to write additional scripts for the lacking prices.
For now, I encountered with another problem.
When I try to save a product -- whether in admin or by my script,
it says “The stock item was unable to be saved. Please try again.”
If I do it by the script, the error appears for the stores, except storeId = 0.
The full error is
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘))’ at
line 2, query was: SELECT `main_table`.*, `cp_table`.`type_id` FROM `cataloginventory_stock_item` AS `main_table`
INNER JOIN `catalog_product_entity` AS `cp_table` ON main_table.product_id = cp_table.entity_id WHERE (`main_table`.`website_id` = ‘0’) AND (`main_table`.`product_id` IN())
I see that there was already such complain previously, in https://www.ubertheme.com/question/the-stock-item-was-unable-to-be-saved/, but it didn’t help me.
Please advice.
Regards,
Ivan.
Hi there,
Because I just expected all the data from M1 to be copied to M2 as close as possible regardless of the new logic in M2 and then in M2 I could decide on my own what to do with that prices. So, I needed to write additional scripts for the lacking prices.
You shouldn’t do that. As I explained in reply #5. There isn’t price assign for main product in configurable product. Price will be calculates depends on associated children simple products.
I see that there was already such complain previously, in https://www.ubertheme.com/question/the-stock-item-was-unable-to-be-saved/, but it didn’t help me.
Please make sure you did steps suggesting in reply #11 in that post?
Regards,
Mall.
We are seeing the exact same issue as #5 when trying to save certain products, the following error is returned:
SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ‘))’ at line 2, query was: SELECT `main_table`.*, `cp_table`.`type_id` FROM `cataloginventory_stock_item` AS `main_table` INNER JOIN `catalog_product_entity` AS `cp_table` ON main_table.product_id = cp_table.entity_id WHERE (`main_table`.`website_id` = ‘0’) AND (`main_table`.`product_id` IN())
One thing I notice here, is that it is an associated simple product of a configurable product and it seems the price of the simple product has not been transferred over either.
Hi discountdisplays,
Please submit a new separate ticket and provide me more details about your instance:
- URL and Admin credentials of your M2 instance
- SSH credentials of your M2 instance
- Let me know the path to the M2 folder
- And provide me detailed information about the issue you mentioned
I will help to check further and get back to you then.
PS. Please make sure your mark your new ticket private, then only you and our technical team can access it.
Regards,
Mall.
