Magento2 Reindex Issue - MySQL Configuration

Question

Magento2 Enterprise After we loaded in about half of our catalog, we started getting this error on re-index:

SQLSTATE[HY000]: General error: 1114 The table 'catalog_category_product_index_tmp' is full, query was: INSERT INTO catalog_category_product_index_tmp ...

we have

innodb_version..... 5.7.12 
innodb_data_file_path..... ibdata1:10M:autoextend 
tmp_table_size..... 2097152000 
max_tmp_tables..... 32

Our catalog size is sitting around 300k skus right now and we need to get it to about 1mill.

• hakre: Is that with EE 2.0 or EE 2.1 (or both)?
• GoatHater: It was 2.0 but we dropped dev on Magento2 for now. While this issue would likely be solved with throwing more hardware at it, there were other issues we could not resolve without a great deal of core re-coding.

Answer 1

The question is vague as it is about a configuration issue and for these the exact reason needs to be known which (often) requires insight into the concrete system. So bare with me as an answer can only be limited.

I personally could handle the error message by raising the default (no value configured, it was 16M effectively) to tmp_table_size = 64M / max_heap_table_size = 64M and it solved that problem.

That was Magento EE 2.1.0.

A co-worker had this problem as well, but raising to 64M, 128M, 256M, 512M etc. up to 2G did not solve the problem. Just saying. The only way the problem could be solved while re-indexing was to load a (new/other/different) db-dump which did not cause that issue. Would be interesting to learn what could cause this error as well. As it was a development version, we invested no further research on the issue then.

• GoatHater: We have run into many Database duplication errors. Lines getting repeated, lines getting deleted when they shouldn't be (this was pre 2.1) but I am willing to wager there was something that was being looped, or full texted more then it should have been. Sometimes things run smoothly, other times they don't. hence the seemingly random working config vs broken ones. Marking answer as correct since in current Mage versions, this is likely the bes the story is going to get.

Answer 2

Increase max_heap_table_size in MYSQL server configuration (usually in /etc/my.conf) works for me.

• GoatHater: max_heap_table_size 134217728 - does it really need more than that?
• Lan Nguyen: I really don't know how much is needed for your db, may be check with your hosting?
• hakre: Which values for what. And for which size of the catalog did that work for you? And the previous values that caused the error would be useful to know. I know I ask for much, but this would greatly improve the answer.

Answer 3

Are you only adding products through the Magento Admin Panel or programmatically somewhere?

Go to Admin Panel click -> System -> Cache Management Check all the boxes off make sure Action is set to ReindexData

Click Submit. Tell me if that works.

• GoatHater: We are only importing through the admin panel now, and the site is in development mode, no caching is on. This is more of a database configuration issue than magento itself.