Magento 2 catalog indexing error, products missing in category pages

In this article we will explore a fairly common problem that can be a bit difficult to identify. Here is the error we will be taking a look at:

SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_temp' is full at /var/www/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

How it affects Magento 2 store

Usually this problem is noticed when some products are missing in the category pages or seem to disappear from the catalog lists on your store. Once you notice the issue and you set the indexing to Update On Save - after saving the product in admin it shows up again. But after a certain period it disappears on its own. This may affect many products or just some depending on various factors.

If you run the indexing manually through bin/magento indexer:reindex then the products do show up. However after a while they disappear again.

How to verify that this particular error is causing the error

Since the products disappear on their own we know that we need to look for an automated background process. The first thing you should do is browse the var/log/exception.log and look for a periodic error that contains a long SQL statement and ends similarly to this:

 LEFT JOIN `catalog_product_entity_datetime` AS `tas_special_to_date` ON tas_special_to_date.entity_id = e.entity_id AND tas_special_to_date.attribute_id = 78 AND tas_special_to_date.store_id = cwd.default_store_id WHERE (IF(IFNULL(tas_status.value_id, -1) > 0, tas_status.value, tad_status.value) = 1) AND (e.type_id = 'simple') AND (e.entity_id BETWEEN 12802 AND 23410) at /var/www/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:235, PDOException(code: HY000): SQLSTATE[HY000]: General error: 1114 The table 'catalog_product_index_price_temp' is full at /var/www/magento2/vendor/magento/zendframework1/library/Zend/Db/Statement/Pdo.php:228)"} []

The important bit here to focus on is this:

The table 'catalog_product_index_price_temp' is full

This problem is interesting because it occurs only under certain circumstances and can have various causes. Nevertheless if you do see this error then you are likely affected by this.

Solution

Once you have determined that you are actually affected by this problem then the next step is to understand why this happens. Since there can be multiple reasons we will focus only on the most common ones:

• Make sure you have enough disk space on the serer. In case you find that there is no disk space you need to free up some space.

df -h

• While the first case is very common it is not very tricky and can be easily noticed without any debugging. The second and more interesting case is that you may have low values allowed for database files. The system tablespace can have one or more data files. By default, a single system tablespace data file, named ibdata1, is created in the data directory. The size and number of system tablespace data files is defined by the innodb_data_file_path startup option. You may want to check your database configuration(/etc/mysql/mysql.conf.d/mysqld.cnf) and adjust the following:

innodb_data_file_path = ibdata1:10M:autoextend:max:512M

• If the above two fixes did not resolve your problem there is a third option you can try. The variable max_heap_table_size sets the maximum size to which user-created MEMORY tables are permitted to grow. This variable is also used in conjunction with tmp_table_size to limit the size of internal in-memory table. Take a look at the current values and attempt to increase them. For example, in the same database configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf) you need to add the following lines.

tmp_table_size=2G
max_heap_table_size=2G

In most cases after trying all 3 of these you should be able to resolve the issues and products should no longer randomly disappear.