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.. ?