Magento Got Stuck on Data Reindexing

Someone reached out to me asking to check what’s wrong with their site. Said it wasn’t working.

I logged into the hosting panel and saw the server was up. Checked the open ports with nmap - all the needed ports were open. The homepage wouldn’t load. Or rather, it loaded for a very long time and never finished, without any error messages. Meanwhile the magento admin panel was accessible.

I log in and see this picture:

I realize the Category Products re-indexing locked up the database.

With the help of stackoverflow.com and some choice language, I managed to bring the site back to our world.

First, I stop nginx and php-fpm.

Next, I connect to the database, which runs on an amazon instance I have no access to except through the mysql client. I enter:

SHOW ENGINE INNODB STATUS \G;

And I see a pile, or rather an endless stream of messages about various processes running or waiting to run. I find a section that looks like this:

-TRANSACTION 11357017, ACTIVE 6768 sec
MySQL thread id 5201363, OS thread handle 0x7f2982e91700, query id 882213399 xxxIPxxx xx.xx.xxx.x user cleaning up

And I start killing every process I can get my hands on with the KILL command:

KILL **5201363**;

After each KILL I check whether the picture changed, using:

SHOW ENGINE INNODB STATUS \G;

At some point it finally changed. I breathed a sigh of relief, went back to the server, and from there went into the /var/www/magento_home/shell folder (yours might be different) and ran a full re-index with the following command:

php indexer.php reindexall

In this case the script runs via the cli version of php, and you can break it off at any moment.

Luckily for me it all finished successfully, and after starting nginx and php-fpm I saw the following picture in the admin panel (Index management):

The site was accessible again after that.