After successful Migration: SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '',

[andidhouse]

After successful migration (no errors) we have a problem accessing the products grid in the backend with the following error:

---

SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name '', query was: SELECT COUNT(DISTINCT main_table.attribute_id) FROM eav_attribute AS main_table
INNER JOIN eav_entity_type AS entity_type ON main_table.entity_type_id = entity_type.entity_type_id
INNER JOIN eav_entity_attribute ON main_table.attribute_id = eav_entity_attribute.attribute_id
INNER JOIN ``ASadditional_tableON main_table.attribute_id = additional_table.attribute_id WHERE (`entity_type_code`= 'catalog_product') AND (`additional_table`.`is_used_in_grid` = 1)

---

All other stuff should be ok and also the products are shown in the frontend.

[victor-v-rad]

Here is correct SQL

SELECT COUNT(DISTINCT main_table.attribute_id) FROM eav_attribute AS main_table
INNER JOIN eav_entity_type AS entity_type ON main_table.entity_type_id = entity_type.entity_type_id
INNER JOIN eav_entity_attribute ON main_table.attribute_id = eav_entity_attribute.attribute_id
INNER JOIN catalog_eav_attribute AS additional_table ON main_table.attribute_id = additional_table.attribute_id WHERE (entity_type_code= 'catalog_product') AND (additional_table.is_used_in_grid = 1)

As you can compare catalog_eav_attribute table missing in your query. This SQL is created in \Magento\Catalog\Model\ResourceModel\Product\Attribute\Collection

[victor-v-rad]

Try
php bin/magento indexer:reindex && php bin/magento cache:clean
and open the products grid again

[andidhouse]

Hi Victor,
many thanks for your feedback.

The table " catalog_eav_attribute" exists in the magento 2.0 database. We also cleaned cache and reindexed. Do you know how we can debug this?

Many thanks!

[andidhouse]

// We just deleted "catalog_eav_attribute" table for testing and the same error is shown again. //

Sry - another error is shown now so it must be an issue with this table right?

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'magento2.catalog_eav_attribute' doesn't exist,

[victor-v-rad]

Ok. Please show one record (with #4 where field entity_type_code=catalog_product) from the table eav_entity_type of Magento 1 and Magento 2 (before migration).
I beliave the record has wrong value in field additional_attribute_table.

[andidhouse]

Hi Victor,

many thanks for your effort here at first!

The data is as followed:

Source 1.7.0.2 Database: / table eav_entity_type / row entity_type_code=catalog_product:
"catalog/eav_attribute"

Clean magento 2.0 Database (before migration): / table eav_entity_type / row entity_type_code=catalog_product:
"catalog_eav_attribute"

magento 2.0 Database (after migration): / table eav_entity_type / row entity_type_code=catalog_product:
NO ENTRY HERE

If we put "catalog_eav_attribute" in this entry of the migrated magento 2.0 Database the backend product grid is shown right.
But if we click on a product (see screen attached) the product details are not shown. If we however access a product over "update attributes" the attributes are shown (but look somehow not right as for example some dropdowns show no value (screen2).

Another question: Can we exclude some product attributes on the migration process which we do not need any more?

Thanks again!

[victor-v-rad]

Can we exclude some product attributes on the migration process which we do not need any more?

add these attributes to etc/ce-to-ce/eav-attribute-groups.xml.dist

magento 2.0 Database (after migration): / table eav_entity_type / row entity_type_code=catalog_product:
NO ENTRY HERE

This is strange. Should be catalog_eav_attribute. Check other records of field additional_attribute_table. Some of them should not be empty also

[andidhouse]

Migrated magento 2.0 Database shows 5 entries. The other ones are 0.

[victor-v-rad]

I meant magento 2.0 Database after migration. If after migration for entity_type_code=catalog_product we got empty value of field additional_attribute_table then other records should be empty also

[andidhouse]

It is magento 2.0 Database after migration. The other entries not shown have empty value in the additional_attribute_table.

[victor-v-rad]

So the only row with entity_type_code=catalog_product has empty additional_attribute_table after migration?

[andidhouse]

pls find attached screen of the full table (after migration of course):

Is it normal to have some table backups in the database after migration?

[victor-v-rad]

Is it normal to have some table backups in the database after migration?

No it is not. Thank you for pointing this out. According internal issue (MAGETWO-47928) was created

[andidhouse]

Ok Victor - many thanks!
Are you a magento 2 member? What is now going to happen?
I am asking because we are a little bit in a hurry to get this working as we plan to run with 2.0 end of this month? Is it possible to hire you as a freelancer? ;-)

[andidhouse]

We just tried to repair the 1.7.0.2 database with the official database repair tool. No errors - no effect.

it would just be great to have a feedback on this (also if it takes time it is important for us to know when we can go on) as it is blocking our hole process and timeline.

[andidhouse]

Ok this is blocking our complete process now 👎

Is there any option how we can fix this or how we can exclude ANY of custom attributes for testing?

I think it has to do something with eav attributes as all other functions are running and if we try to export products there is just a empty space (screen).

For us it is just important to know if we get any help here as we have a strict timeline and otherwise considering to user shopify enterprise for a solution. Also we can´t find any information regarding this on the internet.

Many thanks!

[andidhouse]

sytem log shows:

[2016-01-19 18:06:02] main.CRITICAL: Invalid template file: 'Magento_Catalog::js/components.phtml' in module: '' block's name: 'catalog_category_page_head_components' [] []

[2016-01-19 18:14:17] main.CRITICAL: Class media_image does not exist [] []
[2016-01-19 18:14:18] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []
[2016-01-19 18:14:18] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []

[victor-v-rad]

I assume you use extensions that created its own data in your DB and Migration Tool cannot transfer it correctly.

[andidhouse]

hmm yes i think so but how can we now debug it? no errors shown and no migration error log lets us here without a possibility to fix this at all 👎

[andidhouse]

We are still struggling with this. We also compared the eav tables to a clean magento 1.7.0.2 version and they are exactly the same. We also deleted all extensions tables from database before migration - still the same problem.

Isn´t there any way to debug this? Error logs show nothing and we really do not now how to go on on this topic...

[victor-v-rad]

Hi @andidhouse

I have just migrated 1.7.0.2 (without any extensions) to Magento 2.0.2 and everything is ok Product/order/customer grids show its data in Admin Panel of Magento 2.
There are many causes that could lead to the issue you have. For example:

1. You may used develop branch of Magento 2 which is not stable. Make sure your version of Data Migration Tool is the same as version Magento 2
2. You may had created some Products/Customers/Orders etc. in Magento 2 before migration. Your Magento 2 DB should be in state as right after its installation

I can provide meaningful help only if I have exactly the environment you have. The code and DB. Please get in touch with me if you still have the issue "SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name "

[andidhouse]

Hi Victor,
many thanks for your answer.

We also migrated data with a clean 1.7.0.2 install to magento 2.0.2 - no problem here.
The problem in my mind is that maybe some extensions installed in the 1.7.0.2 system lead to these errors. We also fixed the the error that we can´t access the backend product grid due to a wrong value in the magento 2 database set. But we have no solution for the product issue. We can´t access any product in the admin of magento 2 as shown before.

Is there a option in the data migration tool to NOT copy any extension database or additional attributes from the old 1.7 database.

I mean the approach of the tool is maybe not suited for many shops to migrate all data from the old database. For example we have a payment plugin in our old shop - this is and will not be available for magento 2 but the attributes at the customer backend site show the values of the payment extension in the magento 2 shop.

As i read the problems here on other post most are related to extension data and attributes which are migrated by default from the 1.7 database and then will cause different errors.

I think an approach would be better to:

• Just copy all necessary attributes form m2 at first place (no extension data, no additional attributes etc)
• then in a second step include the attributes you would like to keep

The problem in my mind is that the data migration tool migrates many stuff which is not needed in magento 2 and causes different errors (like some extensions attributes which are not used at all as the extensions are not available for m2 or have a complete other database design).

Also i see no way to debug our problem here.

If you want i can provide a in detail description of all versions and ignored fields etc. but i think the easier way would be (also for the other users here) to just have a look at for example our 1.7 database and debug whats going wrong here and then write a documentation for all users how to fix problems with installed extensions or changed database in the old magento 1.x system.

Many thanks!

[victor-v-rad]

We also fixed the the error that we can´t access the backend product grid due to a wrong value in the magento 2 database set.

Can you provide information about how you fixed this issue and what was the cause? There are many users experience such problem "SQLSTATE[42000]: Syntax error or access violation: 1103 Incorrect table name" Thus we could improve the Migration Tool in the way to prevent this.

[andidhouse]

Hi Victor,

shure i can share this. Just give me a view days because we are busy at the moment.

Also we fixed the admin grind in the backend of magento2 the problem remains that we can´t access any product in the backend (they are just half shown like shown in the screen on top).

Is there any way we can provide you with our 1.7.0.2 database and you have a look - i think many problems here are related to "extensions or modules" installed at the 1.x system and i think it would be best for the community just to have a case how you can debug such a problem like ours because there is no error.log and so no possibility to fix this from our side without huge effort.

Any chance?

[andidhouse]

Hi,

i can confirm that this fix fixes the admin product grid problem:
#66

The problem that we can´t access any product in the backend is still there:

• The product just gets rendered half way see screen

The magento migration log shows no errors.
The magento log after trying to view a product in backend shows these errors:

[2016-03-01 12:40:15] main.CRITICAL: Class media_image does not exist [] []
[2016-03-01 12:40:15] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []
[2016-03-01 12:40:16] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []
[2016-03-01 12:40:33] main.CRITICAL: Invalid template file: 'Magento_Catalog::js/components.phtml' in module: '' block's name: 'catalog_category_page_head_components' [] []
[2016-03-01 12:40:37] main.CRITICAL: Invalid template file: 'Magento_Catalog::js/components.phtml' in module: '' block's name: 'catalog_category_page_head_components' [] []

[2016-03-01 12:41:27] main.CRITICAL: Class media_image does not exist [] []
[2016-03-01 12:41:27] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []
[2016-03-01 12:41:27] main.CRITICAL: Block product_tabs throws exception and cannot be rendered. [] []

[andidhouse]

no support here?

[jonazu]

Hi there

I have the exact same problem and I suspect it has something to do with the entity_type_id. I don't completely understand what the data migration tool is doing exactly, but in sevaral places the entity_type_id is referenced. For example in \src\Migration\Step\Eav\Data.php in function migrateAttributes() where it is used for some mapping reasons.

BUT: my entity_type_ids in the table eav_entity_type are used for completely different entity_type_codes than what's in default installations of M1 or M2. So using this attribute for mapping reasons will not work, I think.

I'm in the process of figuring our on how to aligning my entity_type_ids to default Magento. I'll post updates if I know more.

Regards
Jonas

[jonazu]

Finally, I got it to work! It is as I thought: The default entity_type_ids shouldn't be different from default M2 installation. Otherwise the mapping functions in the migration tool won't work.

That's what I did:

1. I carefully compared entity_type_id and entity_type_code of my M1 eav_entity_type table and the M2 table.
2. Then I figured out all the tables where entity_type_id is a column. (Fortunately there's an SQL for that). Sometimes there is a foreign key with update cascade. Sometimes there is nothing.
3. Create the update SQL to switch the entity_type_ids manually in eav_entity_type and other tables where no foreign key updates the id automatically. See here: https://docs.google.com/spreadsheets/d/1ByasBh_kQMC89pemDRv4c1eEX82cmtssrIv4aYBJdsM/edit?usp=sharing
4. Run migration
5. Edit product!

I'm sure there are bugs and I need to test every function very carefully now. Right now, I get a 404 error after saving... but at least the data get's saved.

In the end, this "solution" is really not what I prefer but I just don't know how to solve this issue otherwise.

[andidhouse]

Hi jonazu,

many thanks! We also still have no solid solution for this problem nor magento self is going to investigate here. We skyped with an magento internal a while ago and the feedback just was "on normal 1.x installations there is no issue". Hmmmm we are still looking for a solid solution here - maybe a magento internal can give feedback here? victor-v-rad?

It would be great jonazu if you could update this post as soon as you know more.

[jonazu]

I think Magento just doesn't care as long as some important (paying) customer has the same problem as we do...

Btw: The error after saving a product is gone. Some strange configuration/cache problem.

So far everything's working well. We'll go live with the new shop end of May so there will be in depth testing coming in May. I keep you posted.

[ilol]

Hey @jonazu and @andidhouse Internal have heard you. I am working with developers to prepare answers.

[andidhouse]

Many thanks @ilol and @jonazu ! Hope we find a solution here soon as this is really our most blocker for setup 3 shops.

[victor-v-rad]

Magento just doesn't care

It is only matter of allocated resources by the company for supporting of these issues. If you feel it is not enough raise this question to the management of the company to involve more staff for supporting.

[jonazu]

I apologize for my harsh comment. I'm sure all of you work hard to make Magento 2 a great product.

In the end, I was able to solve the problem, I think. And I hope soon you'll spend som time fixing it in the migration tool for others as well.

[andidhouse]

hi @ilol
is there any news on this problem? 2 weeks already passed and this is still keeping us away from former migration steps and blocks our timeline.

thank you!

[andidhouse]

Hi @ilol
it has been a while since internals have had a look at this problem (over a month). Can we expect any solution for this from the magento side?

Many thanks!

André

[ilol]

We are still working with MAGETWO-51381. Can`t commit to ETA unfortunately

[phronesis]

@jonazu 's solution seems to be the way to go. My case is worse because for some reason, the tables that should cascade on update do not have foreign key constraints. I however think the order of the updates in that document matter. Especially updating to 5 where entity_type_id = 4 and then updating to 32 where entity_type_id = 5. (Your ids may be different. Fortunately for me, it was exactly the same with @jonazu ).
I think it should be in this order: updating to 32 where entity_type_id = 5 then updating to 5 where entity_type_id = 4. Stilling implementing this though, will update as to the status.

[victor-v-rad]

I expect the issue with eav_entity_type.entity_type_id will be resolved in upcoming release

[andidhouse]

@victor-v-rad

Many thanks for the info.
Do you have any information when version data migration tool for 2.1 is released?
Really hope this is fixed there so fingers crossed.

Thanks!