CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE.,

Question

My Magento 2 site can't be indexed anymore.

not sure what created the problem, when I run php bin/magento indexer:reindex, It shows following error

userxx@host [~/public_html/magento2]# php bin/magento indexer:reindex Design Config Grid index has been rebuilt successfully in 00:00:00 Customer Grid index has been rebuilt successfully in 00:00:00 Category Products indexer process unknown error: SQLSTATE[HY000]: General error: 4047 CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE., query was: CREATE TEMPORARY TABLE IF NOT EXISTS shrp_catalog_category_product_index_store1_tmp LIKE shrp_catalog_category_product_index_store1 Product Categories indexer process unknown error: SQLSTATE[HY000]: General error: 4047 CREATE TEMPORARY TABLE is not allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE., query was: CREATE TEMPORARY TABLE IF NOT EXISTS shrp_catalog_category_product_index_store1_tmp LIKE shrp_catalog_category_product_index_store1 Catalog Rule Product index has been rebuilt successfully in 00:00:00 Product EAV index has been rebuilt successfully in 00:00:00 Stock index has been rebuilt successfully in 00:00:00 Inventory index has been rebuilt successfully in 00:00:00 Catalog Product Rule index has been rebuilt successfully in 00:00:00 Product Price index has been rebuilt successfully in 00:00:00 Catalog Search index has been rebuilt successfully in 00:00:00 userxx@host [~/public_html/magento2]#

I'm using Magento ver 2.3.2

I googled it but couldn't find any useful info on this error

Answer 1

This is due to the row format of the shrp_catalog_category_product_index_store1 table being COMPRESSED (https://dev.mysql.com/doc/refman/5.6/en/innodb-row-format.html). You can change it with the query "ALTER TABLE shrp_catalog_category_product_index_store1 row_format=DYNAMIC;" and the indexing should work.

Answer 2

As Ryan mentioned above; the sql statement below can solve your issue:

ALTER TABLE shrp_catalog_category_product_index_store1 row_format=DYNAMIC

Please note that shrp_ refers to table prefix in database. You might be using a different table prefix in your database. So, modify and execute the above sql query accordingly.

Also, there might be more than 1 table so this query needs to be executed for them.

The tables might be numbered like: shrp_catalog_category_product_index_store2, shrp_catalog_category_product_index_store3, shrp_catalog_category_product_index_store4 etc.

In this case you need to execute query like this:

ALTER TABLE shrp_catalog_category_product_index_store2 row_format=DYNAMIC
ALTER TABLE shrp_catalog_category_product_index_store3 row_format=DYNAMIC
ALTER TABLE shrp_catalog_category_product_index_store4 row_format=DYNAMIC

Please check your database and execute the above query for all iterations of: catalog_category_product_index_store