Magento re-index, cannot create table

Question

I'm trying to re-index the category flat data, but I am always met with the same error:

There was a problem with reindexing process. Error Message: SQLSTATE[HY000]: General error: 1005 Can't create table 'xxx.catalog_category_flat_store_6' (errno: 121)

The table doesn't exist, there is a 1 and a 7. Not sure if that makes a difference?

After running the query manually through phpMyAdmin, I am met with the MySQL error 121. I've checked around and this would suggest the names of the foreign keys trying to be created already exist. I've listed all foreign keys in the DB right now, and they don't exist at all.

I've also tried running SHOW ENGINE INNODB STATUS on the DB for more information, but we don't have the rights to view that apparently.

Does the magento mysql user have "CREATE TABLE" permissions? — Janus Tøndering, Feb 28 '13 at 13:38
Yep, it's created others in the past just fine (such as product flat tables) — BenOfTheNorth, Feb 28 '13 at 13:40
Perhaps this could help: http://stackoverflow.com/questions/8070336/error-code-1005-sql-state-hy000-cant-create-table-errno-150 — Janus Tøndering, Feb 28 '13 at 13:51
Thanks @JanusTøndering, unfortunately I don't have the rights for that. I've emailed my server admin though to get them increased. — BenOfTheNorth, Feb 28 '13 at 14:34
I have solved this issue and described here [here][1] [1]: http://stackoverflow.com/a/25476080/532564 — phpWebStudio, Aug 24 '14 at 20:40

score 1 · Accepted Answer · answered Mar 14 '13 at 16:20

After getting the priv's updated so we could run SHOW INNODB STATUS, we discovered that we already had an existing index that was attempting to be duplicated with this new table. This stemmed from us backing up an older version of the table that was trying to be created. Deleting that copy of the table enabled Magento to re-index properly and solved our problem.

score 0 · Answer 2 · answered Feb 28 '13 at 13:50

Try logging the sql commands and debug what its trying to do by executing them manually. On the index process, normally there is a command that clears a table, and another to recreate it.

Edit /magentoRoot/lib/Varien/Db/Adapter/Pdo/Mysql.php and change $_debug to true and note the $_debugFile location (should be var/debug/pdo_mysql.log)

Its best to edit the file in vi, have a browser open to reindex JUST the category data, save the file in vi :w! and then run the indexer then change the debug back to false.

Then go read the log. It may help.

Thanks! It didnt show any more error information, however it did provide the query. I tried running it manually, and it gave me a mysql error of 121 — BenOfTheNorth, Feb 28 '13 at 13:57

Magento re-index, cannot create table

2 Answers2