Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Useful Mysql Commands for Debugging Magento Reindex Locks

Sometimes you may have a run away reindex process that aborted due to a MySQL connection error of some sorts. It may be useful to use the following MySQL commands to aid in debugging.

Determining if a lock exists

Magento Enterprise labels the reindex lock via the constant REINDEX_FULL_LOCK in app/code/core/Enterprise/Index/Model/Observer.php

SELECT IS_FREE_LOCK('mydatabase_name.reindex_full')

Returns 1 is specified lock is free and can be acquired, 0 if it’s in use, NULL if an error occurs.

Determing the thread that is holding the lock

SELECT IS_USED_LOCK('mydatabase_name.reindex_full')

This function is useful if you need to know which thread is holding a certain lock. It returns the connection id of that thread, or NULL if the specified lock is free.

Releasing a lock

RELEASE_LOCK('mydatabase_name.reindex_full')

RELEASE_LOCK() should return 1. If it returns 0, that lock has been acquired by someone else, and can’t be release by you. If the specified lock does not currently exist, returns NULL. In both cases, your code is likely to be buggy.

@tegansnyder

This comment has been minimized.

Copy link
Owner Author

@tegansnyder tegansnyder commented Dec 3, 2014

In Magento Enterprise the lock can be determined in code like this:

<?php
require_once ("app/Mage.php");
umask(0);
Mage::app();

$resource   = Mage::getSingleton('enterprise_index/resource_lock_resource');
$connection = $resource->getConnection('enterprise_index_write', 'default_lock');
$helper = Mage::getResourceHelper('enterprise_index')->setWriteAdapter($connection);

$config = $connection->getConfig();
$lockName = $config['dbname'] . '.' . 'reindex_full';


if ($helper->isLocked($lockName)) {
    echo 'lock found: ' . $lockName;
} else {
    echo 'not locked';
}
@tegansnyder

This comment has been minimized.

Copy link
Owner Author

@tegansnyder tegansnyder commented Dec 3, 2014

You can also get really tricky and do a live log of the indexer as it performs transaction locks:

#!/bin/bash
while :
do

mysql -X -u myUsername --pMyPassword -h localhost -D us -e'SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE ID IN (SELECT trx_mysql_thread_id FROM information_schema.innodb_trx);'
    sleep 1
done

Note that will result in infinite loop but you can escape it when finished or make it output to a file.

@martinboy

This comment has been minimized.

Copy link

@martinboy martinboy commented Oct 25, 2016

If you find that some indexes are locked for a long time this is the evidence of the lost process which locked the index and probably some tables in database. If the process is lost then SELECT RELEASE_LOCK('mydatabase_name.reindex_full') is out of the control. You have to kill the process manually then all related locks will be released. Please follow my instructions bellow to release the index lock:

  • Detect which process ID is held the lock
SELECT IS_USED_LOCK('mydatabase_name.reindex_full')
  • Find the process ID in the list of the current processes
SHOW processlist;
  • Kill the process
KILL <PROCESS_ID>;
  • Check once again if the index is locked

You may also find that some tables are locked because of locked index. Check them too

SHOW OPEN TABLES FROM mydatabase_name WHERE In_use > 0;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.