3

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.

Improve this question
2
  • Is that with EE 2.0 or EE 2.1 (or both)?
    – hakre
    Commented Jul 21, 2016 at 13:28
  • 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.
    – GoatHater
    Commented Jul 21, 2016 at 14:40

3 Answers 3

Reset to default
3

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.

Improve this answer
1
  • 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.
    – GoatHater
    Commented Jul 22, 2016 at 13:02
1

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

Improve this answer
3
  • max_heap_table_size 134217728 - does it really need more than that?
    – GoatHater
    Commented Jun 21, 2016 at 14:15
  • I really don't know how much is needed for your db, may be check with your hosting?
    – Lan Nguyen
    Commented Jun 22, 2016 at 1:45
  • 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.
    – hakre
    Commented Jul 21, 2016 at 13:25
-2

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.

Improve this answer
1
  • 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.
    – GoatHater
    Commented Jun 6, 2016 at 14:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.