Magento 2.3 – Fix CREATE TEMPORARY TABLE Not Allowed with ROW_FORMAT=COMPRESSED or KEY_BLOCK_SIZE Error

magento2magento2.3reindex

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]#

enter image description here

I'm using Magento ver 2.3.2

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

Best Answer

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.

Related Topic