When the Migration Tool gets to step 4 and 29% completion it fails with the following error:
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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
Info
UB Data Migration Pro -- 3.0.2
Magento -- 2.1.2 Fresh Install
24 answers
Hi Daniel Gonzalez,
I need a closer look to detect the root of issue as you said. Let’s provide me the log file of our tool at the path:
`pub/ub-tool/protected/runtime/ub_data_migration.log`
Regards,
Mall.
2016/11/15 22:51:05 [info] [ub_data_migration] Start running step #2
2016/11/15 22:51:05 [info] [ub_data_migration] Migrated data in step #2 ok with 4 Websites
2016/11/15 22:51:05 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:51:05 [info] [ub_data_migration] (Continued) migrated data in step #2 ok with 4 Store Groups
2016/11/15 22:51:05 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:51:05 [info] [ub_data_migration] (Continued) migrated data in step #2 ok with 4 Stores
2016/11/15 22:51:05 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:51:05 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 22:51:05 [info] [ub_data_migration] ——————--
2016/11/15 22:51:05 [info] [ub_data_migration] Start running step #3
2016/11/15 22:51:05 [info] [ub_data_migration] Migrated data in step #3 ok with 2 Attribute Sets
2016/11/15 22:51:06 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:51:06 [info] [ub_data_migration] (Continued) migrated data in step #3 ok with 18 Attribute Groups
2016/11/15 22:51:06 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:51:06 [info] [ub_data_migration] (Continued) migrated data in step #3 ok with 79 Attributes
2016/11/15 22:51:06 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:51:06 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 22:51:06 [info] [ub_data_migration] ——————--
2016/11/15 22:51:06 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:51:33 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:51:33 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 22:51:33 [info] [ub_data_migration] ——————--
2016/11/15 22:51:33 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:51:33 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 22:51:33 [info] [ub_data_migration] ——————--
2016/11/15 22:51:33 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:51:51 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:51:52 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 22:51:52 [info] [ub_data_migration] ——————--
2016/11/15 22:51:52 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:51:52 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 22:51:52 [info] [ub_data_migration] ——————--
2016/11/15 22:51:52 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:52:00 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:53:01 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:53:02 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 22:53:02 [info] [ub_data_migration] ——————--
2016/11/15 22:53:02 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:53:02 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 22:53:02 [info] [ub_data_migration] ——————--
2016/11/15 22:53:02 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:53:10 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:54:10 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:54:13 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 22:57:16 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_category_entity_datetime` `t` WHERE entity_id = AND attribute_id = 61 AND store_id = 0 LIMIT 1
2016/11/15 22:58:30 [info] [ub_data_migration] Continue running step #2
2016/11/15 22:58:30 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 22:58:30 [info] [ub_data_migration] ——————--
2016/11/15 22:58:30 [info] [ub_data_migration] Continue running step #3
2016/11/15 22:58:30 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 22:58:30 [info] [ub_data_migration] ——————--
2016/11/15 22:58:30 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_category_entity_datetime` `t` WHERE entity_id = AND attribute_id = 61 AND store_id = 0 LIMIT 1
2016/11/15 22:58:30 [info] [ub_data_migration] Start running step #6
2016/11/15 22:58:30 [info] [ub_data_migration] Migrated data in step #6 ok with 2 Customer Groups.
2016/11/15 22:58:30 [info] [ub_data_migration] Continue running step #6
2016/11/15 22:58:30 [info] [ub_data_migration] (Continued) migrated data in step #6 ok with 34 Customers.
2016/11/15 22:58:30 [info] [ub_data_migration] Continue running step #6
2016/11/15 22:58:30 [info] [ub_data_migration] Step #6 was finished.
2016/11/15 22:58:30 [info] [ub_data_migration] ——————--
2016/11/15 22:59:11 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/15 23:41:39 [info] [ub_data_migration] Continue running step #2
2016/11/15 23:41:39 [info] [ub_data_migration] Step #2 was finished.
2016/11/15 23:41:39 [info] [ub_data_migration] ——————--
2016/11/15 23:41:39 [info] [ub_data_migration] Continue running step #3
2016/11/15 23:41:39 [info] [ub_data_migration] Step #3 was finished.
2016/11/15 23:41:39 [info] [ub_data_migration] ——————--
2016/11/15 23:41:39 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
2016/11/16 00:08:45 [info] [ub_data_migration] Continue running step #6
2016/11/16 00:08:45 [info] [ub_data_migration] Step #6 was finished.
2016/11/16 00:08:45 [info] [ub_data_migration] ——————--
2016/11/16 00:09:07 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1
Hi Daniel Gonzalez,
2016/11/15 22:58:30 [error] [ub_data_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 attribute_id = 61 AND store_id = 0 LIMIT 1’ at line 1. The SQL statement executed was: SELECT * FROM `catalog_category_entity_datetime` `t` WHERE entity_id = AND attribute_id = 61 AND store_id = 0 LIMIT 1
I see you stopped in the step #4 with that issue. That issue comes from you have at least one record with with bad data in the table `catalog_category_entity_datetime `
I need a closer look more in current database of your Magento 1. Let’s provide me the admin, ssh credentials of your site.
If you have phpmyadmin to control the database, let’ share me. I will check further and help you out.
Regards,
Mall.
At this time, we do not want to give credentials to these systems or mysql dump files because they contain sensitive information.
I don’t think that is the catalog_category_entity_datetime data is the issue. There are 2 issues with the query:
- The query is malformed: SELECT * FROM `catalog_category_entity_datetime` `t` WHERE entity_id = AND attribute_id = 61 AND store_id = 0 LIMIT 1; is not valid as entity_id does not have a value specified in the WHERE clause which is what is causing this to break.
- Additionally, the attribute associated with the id of 61 in the eav_attribute table is the product description attribute which should never be expected to be found in the catalog_category_entity_datetime table (and it is not found in this table). It should be in the catalog_product_entity_text table (and it is). I don’t know how or from where the attribute_id is being pulled but clearly there is an issue with this.
It looks like you are looking for a product attribute in a category attribute table. Can you tell me where this particular query is pulling it’s values from so that I may clean it up?
Hi Daniel Gonzalez,
- The query is malformed: SELECT * FROM `catalog_category_entity_datetime` `t` WHERE entity_id = AND attribute_id = 61 AND store_id = 0 LIMIT 1; is not valid as entity_id does not have a value specified in the WHERE clause which is what is causing this to break.
That issue related to code lines: 300 ->301 in the php file at path: /pub/ub-tool/protected/controllers/Step4Controller.php
and i don’t know why it missing the value of entity_id filed in that context. Please have a look on that.
- Additionally, the attribute associated with the id of 61 in the eav_attribute table is the product description attribute which should never be expected to be found in the catalog_category_entity_datetime table (and it is not found in this table). It should be in the catalog_product_entity_text table (and it is). I don’t know how or from where the attribute_id is being pulled but clearly there is an issue with this.
The attribute_id = 61 in that context is attribute in the Magento 2 database, it is not in the Magento 1 database as you think.
Hard for me to help you if you can’t share more your information in your working.
Let’s re-try with steps as below:
1 -- Reset all steps
2 -- Open your terminal go to your Magento 2 folder and run this command in your terminal: `rm -rf pub/ub-tool/protected/runtime/cache`
3 -- Restart with all steps with our tool.
And tell me know how it goes.
Regards,
Mall.
Well, I can answer why the entity_id is non-existent.
In pub/ub-tool/protected/controllers/Step4Controller.php on line 210, entity_id is set to null: $category2->entity_id = null; //we will not take old entity_id
Later on line 249, $category2->entity_id is passed into _migrateCatalogCategoryEAV(). It is in this method that, on line 300, the null $category2->entity_id is being used: $condition = “entity_id = {$entityId2} AND attribute_id = {$attributeId2} AND store_id = {$storeId2}”;
Because $entityId2 is equal to null, that query becomes: “entity_id = AND attribute_id = {some_id}”
Unless i’m missing something again, I would expect this sql query to fail.
In any case, what information/access do you need to debug this issue?
Hi Daniel Gonzalez,
In pub/ub-tool/protected/controllers/Step4Controller.php on line 210, entity_id is set to null: $category2->entity_id = null; //we will not take old entity_id
Yes, we have that proceed but after save a category, the entity_id will be auto fill after code line 220:
if (!$category2->save()) {
and in the line 249:
$this->_migrateCatalogCategoryEAV($category->entity_id, $category2->entity_id, $mappingStores);
the $category object will has a new value of entity_id.
I think the root issue because the $category2 object can not save data in that context and it missing the value of entity_id field.
Did you reset and restart data migration in all steps as my suggesting?
Regards,
Mall.
I completely reinstalled magento 2 with a clean database and ran the migration and ran into the same issue
Same issue if i comment that line out
Hi Daniel Gonzalez,
Same issue if i comment that line out
You should not comment out anything in our tool. What version of the MySQL in your working?
I can’t clone the issue as you said in my local. So, hard for me when you can’t share SSH or a backup of your Magento 1 database.
Let’s update me the latest content of the log file in our tool at
pub/ub-tool/protected/runtime/ub_data_migration.log
Regards,
Mall.
Hi Daniel,
I see your concern when sharing SSH credentials to our team.
However, we have helped many users successfully when they have issues during migration process. The root of issue for each case varies, and they all provide us SSH credentials with root permission.
Even for custom data migration projects we have completed, we have full access to our users’ server. One of recent case study for your reference (we helped migrate almost 400,000 customer entities -- https://www.ubertheme.com/magento2/case-study-winetasting-migrate-customer-entities-to-magento-2/
Hope you can consider again.
Anyway, for now please share the latest information in the log file of our tool (you will see it at: pub/ub-tool/protected/runtime/ub_data_migration.log)
Regards,
An Nguyen
Hi Daniel,
Just want add note that when you share your server credentials, kindly use private reply here. Or you can send email to info (at) ubertheme.com.
Thanks,
An Nguyen
Can your tool be made to work with enterprise?
Hi Daniel,
It’s pity that at this phase our migration tool works with CE version only. Database integrity is an essential requirement, so we can provide you workaround for issues associated with CE, while EE is beyond scope of our support now.
Thanks for your understanding.
Regards,
An Nguyen
What is the process for returns on products?
Hi Daniel,
Both you and us are in a hard situation now. We do have 7 Days Money Back Guarantee, yet your purchase was way beyond this period.
Further, our product detail page states clearly the Magento CE to avoid confusion for our users, including you.
Regards,
An Nguyen
Would you be willing to assist me in making it compatible with enterprise? We really need this extension to work and we spent a lot of money on it.
Please help us with this.
Hi Daniel,
The differences between Magento CE 1 and Magento CE 2 are huge, like we pointed our in this infographic -- https://www.ubertheme.com/magento2/infographic-magento-1-vs-magento-2-database-structure/
The case is similiar for Magento EE, so it could not be a quick fix to migrate from CE to EE.
With our current limited resource, I am afraid making our tool compatible with EE is not possible at present.
Thanks for your understanding.
Regards,
An Nguyen
I am sorry, but it is hard for me to see why you think you should keep $400 of mine when your product can do nothing for me and you are not willing to do anything to help me get it to work.
Hi Daniel,
It’s not that my team is not willing to help you, yet the EE is a complete different Magento instance, that we could not get our tool compatible at this phase. You know it took us almost 2 years now to reach the current version of UB Data Migration tool.
Further, there have been efforts from our users who tried to bypass our Refund policy, that’s why we stick to 7 Days Refund Guarantee for all our users. And the 7-Days period still allows to detect all issues that might have with our migration tool in actual usecases.
Anyway, I’d like to offer you one of our Magento 2 extension for free as a token of thanks for joining us this far. You can pick any item here: https://www.ubertheme.com/magento-extensions-2-x/ For your information, a new UB Instant Layered Navigation for Magento 2 has just been added.
I look forward to hearing from you on this exclusive offer.
Regards,
An Nguyen
