At the moment we have an issue with our Magento 22.214.171.124 database. It seems that some duplicated records were added to the database and now we can't reindex the Category Flat Data without making the website unresponsive. Every time we try to reindex the following error appears:
SQLSTATE[42S02]: Base table or view not found: 1146 Table 'db.catalog_category_flat_store_1' doesn't exist
Right now we have to restore the catalog_category_flat_store_1 table manually to keep the website online. But very soon we'll need to reindex this table again.
I restored the database on a development environment and did the same process with the same outcome. However, this time, after reindexing the catalog flat data, I ran this two commands:
ALTER TABLE catalog_category_entity ENGINE=INNODB; ALTER TABLE core_store ENGINE=INNODB;
Then I ran the indexer and everything worked fine! So I think that's the solution.
I want to run those two commands on my production database, but every time I try to do that the following error appears:
#1025 - Error on rename of './db/#sql-7fbd_5895f0' to './db/catalog_category_entity' (errno: 121)
Is there anything I can do to alter those tables and restore the database to a better state?