0

At the moment we have an issue with our Magento 1.7.0.2 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?

Thanks

3

The fact those tables aren't INNODB already is alarming. I assume you've copy pasted the contents of /etc/my.cnf from a guide and not verified the contents or values. Then on restart of MySQL, the INNODB engine has failed to start and all table types have defaulted to MyISAM.

Easiest way to tell is to look through your MySQL error log and look for "innodb".

First fix MySQL, then after, install a demo store with the same version as your live store and use the DB repair tool to fix your broken tables, keys and indexes.

  • Thanks for the answer. Actually the Magento instance is on a cPanel environment so we don't have access to mySQL (just through phpmyadmin). Do you think it is safe to temporary stop the website, move the database to a local environment, fix it and move it back again? As I said, I was able to ALTER the tables on my local environment. – jpruizs Aug 15 '14 at 1:15
  • 1
    No, that won't work at all given your issue is almost certainly that your server is not capable of using InnoDB. If you reimport into a broken DB server, the same thing will happen again. Fix your DB server. – Ben Lessani - Sonassi Aug 15 '14 at 22:54
  • 1
    I'm reasonably sure that Plesk allows configuration of MySQL at that level, somewhere buried in the website settings. However, if you really do not have access to it, tell your hoster to fix it for you. If they will or can not, change to a different hoster. Being able to run InnoDB is an essential requirement for running a Magento store. – Melvyn Aug 27 '14 at 18:49

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.