Magento2: The table 'catalog_category_product_index_tmp' is full

[paales]

Preconditions

1. Magento 2.1
2. MySQL 5.6.22
3. Set tmp_table_size and max_heap_table_size to 64M
4. 30k products have 50 store views under one website.

Steps to reproduce

1. Run php bin/magento indexer:reindex catalog_category_product

Expected result

1. Reindexer runs

Actual result

SQLSTATE[HY000]: General error: 1114 The table 'catalog_category_product_index_tmp' is full, query was: INSERT INTO `catalog_category_product_index_tmp` (`category_id`, `product_id`, `position`, `is_parent`, `store_id`, `visibility`) SELECT `cc`.`entity_id` AS `category_id`, `ccp`.`product_id`, ccp.position + 10000 AS `position`, 0 AS `is_parent`, 51 AS `store_id`, IFNULL(cpvs.value, cpvd.value) AS `visibility` FROM `catalog_category_entity` AS `cc`
 INNER JOIN `temp_catalog_category_tree_index_07e03972` AS `cc2` ON cc2.parent_id = cc.entity_id AND cc.entity_id NOT IN (1)
 INNER JOIN `catalog_category_product` AS `ccp` ON ccp.category_id = cc2.child_id
 INNER JOIN `catalog_product_entity` AS `cpe` ON ccp.product_id = cpe.entity_id
 INNER JOIN `catalog_product_website` AS `cpw` ON cpw.product_id = ccp.product_id
 INNER JOIN `catalog_product_entity_int` AS `cpsd` ON cpsd.entity_id = cpe.entity_id AND cpsd.store_id = 0 AND cpsd.attribute_id = 94
 LEFT JOIN `catalog_product_entity_int` AS `cpss` ON cpss.entity_id = cpe.entity_id AND cpss.attribute_id = cpsd.attribute_id AND cpss.store_id = 51
 INNER JOIN `catalog_product_entity_int` AS `cpvd` ON cpvd.entity_id = cpe. entity_id AND cpvd.store_id = 0 AND cpvd.attribute_id = 96
 LEFT JOIN `catalog_product_entity_int` AS `cpvs` ON cpvs.entity_id = cpe.entity_id AND cpvs.attribute_id = cpvd.attribute_id AND cpvs.store_id = 51
 INNER JOIN `catalog_category_entity_int` AS `ccad` ON ccad.entity_id = cc.entity_id AND ccad.store_id = 0 AND ccad.attribute_id = 51
 LEFT JOIN `catalog_category_entity_int` AS `ccas` ON ccas.entity_id = cc.entity_id AND ccas.attribute_id = ccad.attribute_id AND ccas.store_id = 51 WHERE (cpw.website_id = '1') AND (IFNULL(cpss.value, cpsd.value) = 1) AND (IFNULL(cpvs.value, cpvd.value) IN (2, 3, 4)) AND (IFNULL(ccas.value, ccad.value) = 1) AND (`cc`.`entity_id` >= 1001) AND (`cc`.`entity_id` < 1501) ON DUPLICATE KEY UPDATE `category_id` = VALUES(`category_id`), `product_id` = VALUES(`product_id`), `position` = VALUES(`position`), `is_parent` = VALUES(`is_parent`), `store_id` = VALUES(`store_id`), `visibility` = VALUES(`visibility`)

[paales]

I think the 64M is a respectable amount, but currently contacting my hosting provider to increase the tmp_table_size and max_heap_table_size to see if that solves the issue.

[paales]

Testing locally it was indeed solved by upping the limit to 512MB.

[PingusPepan]

If you increase the size on fly and do not restart mysql server, truncate the table manually so it applies the new size for that table.

[paales]

I've changed the storage engine to InnoDB for now, which seems to solve the issue (might be a performance hit).

[paales]

Also present in Magento 2.1.1

[PascalBrouwers]

In my opinion: try not to have all the logic in 1 mysql query, but try to have some logic in PHP that divides it into multiple queries. This way mysql doesn't blow up ;)

[southerncomputer]

Same problem with 700K products - have to alter table engine=innodb for that index process to complete. I suspect using MEMORY for the table is not always optimal in large catalog setups and should we really need those _tmp tables to default to type=memory ! It would seem just as slow as using a large mysql memory footprint backed by SSD storage as a better approach!

[rip057]

[mysqld]
innodb_data_file_path=ibdata1:10M:autoextend
tmp_table_size=50M
max_heap_table_size=50M

in /etc/my.cnf and a restart of mariadb fixed this for me.

[paales]

@rip057 In my case 512M wasn't enough, so upping the memory limit wont solve this issue.

[nikoelgatito]

We are experiencing a similar issue with table catalog_product_index_price_tmp. The logic is the same, the store has nearly 100k products and 9 store views. In the case of the price index, customer groups also impacts the rows count. Raising max_heap_table_size does not seem to be a sustainable solution as this can lead to mysql unnecessarily over-consuming ram.

@PascalBrouwers I agree that indexing in query batches would be a solution. A config added to specify the batch size would be welcomed as it would allow server specific settings for adjusting performances.

[koenner01]

Any news on this.. ?

[magento-engcom-team]

@paales, thank you for your report.
The issue is already fixed in develop branch, 2.2.0

[rugandroll]

i am still having this problem in magento 2.2.0.
magento claim they fixed it on 2.2.0, but it is not.
i tried this:
tmp_table_size=2048M
max_heap_table_size=2048M
i have not got any error yet, but the reindex is taking for ever, it is still not finished after 12 hours.
any advise please

[franckgarnier21]

@magento-engcom-team Can I have the commit reference of this fix ?

[oltseb]

@magento-engcom-team , I can confirm that issue is still present.
I am using PHP 7.1, Magento 2.2.1.
./magento indexer:reindex catalog_product_price for 300k+ products is taking forever. At the same moment, all other reindex processes are completed perfectly. When trying to verbose the process I am receiving the following error, while trying to handle chunk with the latest ID = 295211:

SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_cfg_opt_agr_temp' is full, query was: INSERT INTOcatalog_product_index_price_cfg_opt_agr_tempSELECTsub.parent_id,sub.entity_id,sub.customer_group_id,sub.website_id,sub.price,sub.tier_priceFROM (SELECTe.entity_id,cg.customer_group_id,cw.website_id, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AStax_class_id, IFNULL((ta_price.value), 0) ASorig_price, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) ASprice, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) ASmin_price, IFNULL((IF(IF(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_from_date.value_id, -1) > 0, tas_special_from_date.value, tad_special_from_date.value)) <= DATE(cwd.website_date), 1, 0)) > 0 AND IF(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value) IS NULL, 1, IF(DATE(IF(IFNULL(tas_special_to_date.value_id, -1) > 0, tas_special_to_date.value, tad_special_to_date.value)) >= DATE(cwd.website_date), 1, 0)) > 0 AND ta_special_price.value < ta_price.value, ta_special_price.value, ta_price.value)), 0) ASmax_price, tp.min_price AStier_price, tp.min_price ASbase_tier,le.entity_idASparent_idFROMcatalog_product_entityASe CROSS JOINcustomer_groupAScg CROSS JOINstore_websiteAScw INNER JOINcatalog_product_index_websiteAScwdON cw.website_id = cwd.website_id INNER JOINstore_groupAScsgON csg.website_id = cw.website_id AND cw.default_group_id = csg.group_id INNER JOINstoreAScsON csg.default_store_id = cs.store_id AND cs.store_id != 0 INNER JOINcatalog_product_websiteASpwON pw.product_id = e.entity_id AND pw.website_id = cw.website_id LEFT JOINcatalog_product_index_tier_priceAStpON tp.entity_id = e.entity_id AND tp.website_id = cw.website_id AND tp.customer_group_id = cg.customer_group_id INNER JOINcatalog_product_entity_intAStad_statusON tad_status.entity_id = e.entity_id AND tad_status.attribute_id = 96 AND tad_status.store_id = 0 LEFT JOINcatalog_product_entity_intAStas_statusON tas_status.entity_id = e.entity_id AND tas_status.attribute_id = 96 AND tas_status.store_id = cs.store_id LEFT JOINcatalog_product_entity_intAStad_tax_class_idON tad_tax_class_id.entity_id = e.entity_id AND tad_tax_class_id.attribute_id = 121 AND tad_tax_class_id.store_id = 0 LEFT JOINcatalog_product_entity_intAStas_tax_class_idON tas_tax_class_id.entity_id = e.entity_id AND tas_tax_class_id.attribute_id = 121 AND tas_tax_class_id.store_id = cs.store_id LEFT JOINcatalog_product_entity_decimalASta_priceON ta_price.entity_id = e.entity_id AND ta_price.attribute_id = 75 AND ta_price.store_id = 0 LEFT JOINcatalog_product_entity_decimalASta_special_priceON ta_special_price.entity_id = e.entity_id AND ta_special_price.attribute_id = 76 AND ta_special_price.store_id = 0 LEFT JOINcatalog_product_entity_datetimeAStad_special_from_dateON tad_special_from_date.entity_id = e.entity_id AND tad_special_from_date.attribute_id = 77 AND tad_special_from_date.store_id = 0 LEFT JOINcatalog_product_entity_datetimeAStas_special_from_dateON tas_special_from_date.entity_id = e.entity_id AND tas_special_from_date.attribute_id = 77 AND tas_special_from_date.store_id = cs.store_id LEFT JOINcatalog_product_entity_datetimeAStad_special_to_dateON tad_special_to_date.entity_id = e.entity_id AND tad_special_to_date.attribute_id = 78 AND tad_special_to_date.store_id = 0 LEFT JOINcatalog_product_entity_datetimeAStas_special_to_dateON tas_special_to_date.entity_id = e.entity_id AND tas_special_to_date.attribute_id = 78 AND tas_special_to_date.store_id = cs.store_id INNER JOINcatalog_product_super_linkASlON l.product_id = e.entity_id INNER JOINcatalog_product_entityASleON le.entity_id = l.parent_id WHERE (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1)) ASsubON DUPLICATE KEY UPDATEprice= VALUES(price),tier_price= VALUES(tier_price)

Can you please reference the commit, which is responsible for fixing the issue?

[sibhis]

I am facing similar issue while placing order
SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_cfg_opt_agr_tmp' is full, query was: INSERT INTO catalog_product_index_price_cfg_opt_agr_tmp SELECT sub.parent_id, sub.entity_id, sub.customer_group_id, sub.website_id, sub.price, sub.tier_price FROM (SELECT e.entity_id, cg.customer_group_id, cw.website_id, IF(IFNULL(tas_tax_class_id.value_id, -1) > 0, tas_tax_class_id.value, tad_tax_class_id.value) AS tax_class_id, IFNULL((ta_price.valu…atetimeAStas_special_to_dateON tas_special_to_date.entity_id = e.entity_id AND tas_special_to_date.attribute_id = 80 AND tas_special_to_date.store_id = cs.store_id INNER JOINya_catalog_product_super_linkASlON l.product_id = e.entity_id INNER JOINya_catalog_product_entityASleON le.entity_id = l.parent_id WHERE (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value)=1)) ASsubON DUPLICATE KEY UPDATEprice= VALUES(price),tier_price= VALUES(tier_price`)

We face this issue after upgradation of magento to 2.1.10 from 2.1.5

[southerncomputer]

tmp_table_size = 4G
max_heap_table_size = 4G

seems to help in my.cnf

or remove : $table->setOption('type', 'memory'); from
magento2/lib/internal/Magento/Framework/Search/Adapter/Mysql/TemporaryStorage.php

to disable use of memory type tmp tables.

I'd do that and recommend using tmpfs for /tmp and setting my.cnf temp to use /tmp if you have enough ram!

tmpfs 60350944 176 60350768 1% /tmp

[sibhis]

@magento-engcom-team Please refer the commit for this...

[rip057]

this isnt necessarily a magento issue. other than the method that they use to go about indexing the various tables for later use. an error of
SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_cfg_opt_agr_temp' is full, query was: INSERT
is a mysql error referring to a temporary table being over filled. basically you allotted so much space for temporary storage for various data crunching, and that space has filled before whatever stored procedure or statement has completed. Just like when you have totally filled up a hard drive, the only way to get more data onto that volume is to increase its size. this for me was done by increasing some of the values in the my.cnf like has been mentioned, but you shouldnt have a problem if you have autoextend enabled on your innodb directive.
innodb_data_file_path=ibdata1:10M:autoextend
for good information about all the directives you may have in your my.cnf or my.cnf.d files you can see:
https://dev.mysql.com/doc/refman/5.7/en/mysqld-option-tables.html you may also want to monitor your disk capacity while the reindexing is going on. you can do this easily in a shell with the simple command
watch df
one of the causes of this running out of temporary space is when the temporary space is directed to a folder and it fills up the volume that folder is in.

[PascalBrouwers]

@rip057 this IS a magento issue. Or rather a flawed programming design of putting all logic in 1 huge query. You will notice these issues when you have stores with 300k+ products in them with multiple store views.

[rip057]

That is the nature of database indexes, you do one HUGE calculation now to
stop yourself from doing a certain calculation over and over again. You do
know the reason that DB tables have indexes? For faster searching and
inter table correlation. Each product can have up to as many entities as
are defined, which by default is at least a hundred could be on the order
of hundreds, and when you add a couple of zeros to the end of that 300K
figure... Well it's a really big calculation. I've given you suggestions.

On Dec 4, 2017 9:17 AM, "PascalBrouwers" [e-mail] wrote:

@rip057 https://github.com/rip057 this IS a magento issue. Or rather a
flawed programming design of putting all logic in 1 huge query. You will
notice these issues when you have stores with 300k+ products in them with
multiple store views.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/6415#issuecomment-348992082,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AJ0Vctkz-sUlNUwgKodnvtJAfJNw0eWxks5s9Az7gaJpZM4Jxbgm
.

[hostep]

@magento-engcom-team: can you reference the commit(s) which solve this issue in 2.2.x ?

We run against this problem on Magento 2.1.12 with a big shop, although it's a different table then referenced above, namely: catalog_product_index_eav_tmp

I can find some commits referencing something related:

• MAGETWO-66904: General error: The table 'catalog_category_product_index_tmp' is full
• MAGETWO-81007: Table is full errors on same modify profile

Any chance these will fix this issue? And backporting those to 2.1 won't cause new problems?
Extra information here from the Magento team would be appreciated.

Thanks!

[giacmir]

Had the same error today on Magento 2.2.2. In my case the table was catalog_product_index_price_cfg_opt_agr_temp

[Knight017]

We are still facing this issue in Magento 2.2.3 in catalog_category_product and catalog_product_category reindexing.

[rip057]

Did you read the thread? You need to increase the max temp table setting in
my.cnf

See

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmp_table_size

On Wed, Jul 18, 2018, 1:45 AM Rajesh Harwani, [e-mail]
wrote:

We are still facing this issue in Magento 2.2.3 in
catalog_category_product and catalog_product_category reindexing.

—
You are receiving this because you were mentioned.
Reply to this email directly, view it on GitHub
https://github.com/magento/magento2/issues/6415#issuecomment-405826879,
or mute the thread
https://github.com/notifications/unsubscribe-auth/AJ0VciPkzveNZvGv0FLZPU-P6_jzwPuoks5uHtmSgaJpZM4Jxbgm
.

[ericvhileman]

The actual limit is determined from whichever of the values of tmp_table_size and max_heap_table_size is smaller. If an in-memory temporary table exceeds the limit, MySQL automatically converts it to an on-disk temporary table.

[mlaurense]

It is also worth noting that if the above doesn't seem to help, to check the innodb_temp_data_file_path:
innodb_temp_data_file_path = ibtmp1:12M:autoextend:max:5G
I had added 'max:5GB' in the past, and that wasn't enough... Increasing it to 16GB did the trick for me

[grahamslam]

I had this issue and upon further investigation it appeared the cause of my error was due to running out of hard disk space on my VPS. I had set up a duplicate site on my server for testing and apparently the last product my client added filled up the disk space. So I had no room On-Disk for mysql to use a temporary table. Just something else to look at.

[dmanners]

I will add this here in-case it helps anyone. I was having this issue not with a full reindex but when reindexing only 536 products from a specific category.

When diving into this problem I noticed that the redinexall ends up calling Magento\Catalog\Model\Indexer\Product\Eav\Action\Full:execute which does the batching but when specifing just a set of product ids it ended up in Magento\Catalog\Model\Indexer\Product\Eav\AbstractAction::reindex which does not batch the ids given in.

What makes things a bit more confusing is that the full index method claims to take in ids but then ignores them when processing.

public function execute($ids = null)

You can see the three different methods for reindexing all, multiple ids and a single id at https://github.com/magento/magento2/blob/2.3-develop/app/code/Magento/Catalog/Model/Indexer/Product/Eav.php#L53