mediatribe.net -- Drupal et développement web

Notice: this post was last updated Il y a 3 years 37 weeks so it might be outdated. Please be cautious before implementing any of suggestions herein.

Drupal site hanging due to semaphore table

I recently had to debug a problem on a production site, so I cloned it to my local computer.

Now, the site was just hanging (loading forever).

Here's how I fixed the problem:

- I first eabled xdebug on my local site.
- Next I installed Webgrind.
- Now I visited the hanging page and add ?XDEBUG_PROFILE=1 to the end of the URL
- Webgrind informed me that lock_wait() was being called repeatedly. Unfortunately I could not find the exact trace and arguments with Webgrind.
- I installed Netbeans, a nice IDE which allows you to set breakpoints on your PHP code, so I set the breakpoint directly on the lock_wait() function (make sure the path to PHP on Netbeans is the same path as what is actually used by the site, in my case /Applications/MAMP/bin/php/php5.4.4/bin/php, not /usr/bin/php). This told me that the lock on "variable_init" was never being released, and that the semaphore table was involved.
- Next, I made sure all my MySQL queries were being logged and inspected the result. Sure enough, the system was repeatedly trying to delete the semaphore entry for variable_init.
- Next, a trip to the command line, drush sqlc, and the following command did the trick:

delete from semaphore where name='variable_init';

Now my site loads fine. I'm not sure if there are side effects to doing this, but in any case my local site is just for debugging purposes and I'll throw it away afterward.

Wow, you've put so much

Wow, you've put so much effort to find the problem. Also you solution looks good too!

I think I have a similar issue. Instead of using those softwares I've used Newrelic to understand what's going on.

My drupal 7 site goes down randomly for no apparent reason. Php-fpm & Mysqld using so much RAM and I even can't ssh into my server to restart the services.

When I check new relic, I've found the information below. What do you think? Can you help me?

Using delete from semaphore where name='variable_init'; could help me? Is there a down side of this solution?

SQL
DELETE FROM semaphore WHERE (name = :db_condition_placeholder_?) AND (value = :db_condition_placeholder_?) AND (expire <= :db_condition_placeholder_?)

Stack trace:
in PDOStatement::execute called at /home/prod/public_html/includes/database/database.inc (2171)
in DatabaseStatementBase::execute called at /home/prod/public_html/includes/database/database.inc (683)
in DatabaseConnection::query called at /home/prod/public_html/includes/database/query.inc (858)
in DeleteQuery::execute called at /home/prod/public_html/includes/lock.inc (181)
in lock_may_be_available called at /home/prod/public_html/includes/lock.inc (146)
in lock_acquire called at /home/prod/public_html/includes/bootstrap.inc (912)
in variable_initialize called at /home/prod/public_html/includes/bootstrap.inc (2478)
in _drupal_bootstrap_variables called at /home/prod/public_html/includes/bootstrap.inc (2242)
in drupal_bootstrap called at /home/prod/public_html/includes/bootstrap.inc (2376)
in _drupal_bootstrap_page_cache called at /home/prod/public_html/includes/bootstrap.inc (2234)
in drupal_bootstrap called at /home/prod/public_html/index.php (20)

it could be because of cron

it could be because of cron and crawl time

Thanks for this. Helped point

Thanks for this. Helped point me to the issue I was havving.

I was unable to to do anything with my semaphore table, however. To remediate it I did the following:

show open tables; // Look for table where in_use > 0 - probably semaphore
show processlist;
kill [process id]; // For any process that's acting on the locked table

I think you should consider

I think you should consider updating this post with references to the following issue on Drupal.org since there is a detailed discussion about a bug in innoDB. https://www.drupal.org/node/1898204#comment-8523869

Updating Semaphore table engine to Memory is the solution.
Thanks for the post.