0

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

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.

| improve this answer | |
  • Worked for me. Thanks Ryan. – Bimal Sep 17 '19 at 6:35
  • Sorry for the late reply. Worked perfectly. Thank You! – anon Sep 23 '19 at 13:37
0

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.