# Mysql tuning plus catching ActiveRecord::StatementInvalid errors

**URL:** <https://www.ruby-forum.com/t/mysql-tuning-plus-catching-activerecord-statementinvalid-errors/149878>\
**Category:** Rails\
**Created:** [September 30, 2008, 7:52pm UTC](https://www.ruby-forum.com/t/mysql-tuning-plus-catching-activerecord-statementinvalid-errors/149878 "2008-09-30T19:52:56Z")\
**Posts on this page:** 2\
**Page:** 1

<div class="post-metadata">

**Author:** ![twaters](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a6a055/32.png) [@twaters](https://www.ruby-forum.com/u/twaters)\
**Post date:** [September 30, 2008, 7:52pm UTC](https://www.ruby-forum.com/t/mysql-tuning-plus-catching-activerecord-statementinvalid-errors/149878/1 "2008-09-30T19:52:56Z")

</div>

Recently our Rails app has been choking every once and a while on  
updates to the “users” table. The error normally appears like so:

A ActiveRecord::StatementInvalid occurred in  
activity\_responses#create:

Mysql::Error: Lock wait timeout exceeded; try restarting  
transaction: UPDATE `users` SET `created_at` = ‘2007-03-02 23:26:33’,  
`home_phone` = NULL, …

The users table currently has about 350,000 rows and consists of  
about 50 fields. The error only occurs on the users table yet there  
are 3 or 4 other tables with much more rows that work just fine  
(although the updating is not as often as the users table). I have  
googled and googled this error and haven’t come up with much. I have  
tweaked and retweaked mysql settings to no avail and also tried  
tweaking a few rails variables. I think it may have to do with certain  
queries that are not indexed properly, but I can’t seem to find  
anything that would point me to where or what to fix. Any ideas on  
where to go next to track down this issue?

Onto the 2nd part… While I hate that some users get this error, it is  
happening once or twice a day. Is there anyway to catch this error so  
I can send them a specific error message on the subject and let them  
know it is ok to try again in a minute or two? (Rather then the  
standard error message)

Thanks…  
-tim

---

<div class="post-metadata">

**Author:** ![twaters](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/t/a6a055/32.png) [@twaters](https://www.ruby-forum.com/u/twaters)\
**Post date:** [September 30, 2008, 8:22pm UTC](https://www.ruby-forum.com/t/mysql-tuning-plus-catching-activerecord-statementinvalid-errors/149878/2 "2008-09-30T20:22:52Z")

</div>

On Sep 30, 6:52 pm, Tim W [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> The users table currently has about 350,000 rows and consists of  
> about 50 fields. The error only occurs on the users table yet there  
> are 3 or 4 other tables with much more rows that work just fine  
> (although the updating is not as often as the users table). I have  
> googled and googled this error and haven’t come up with much. I have  
> tweaked and retweaked mysql settings to no avail and also tried  
> tweaking a few rails variables. I think it may have to do with certain  
> queries that are not indexed properly, but I can’t seem to find  
> anything that would point me to where or what to fix. Any ideas on  
> where to go next to track down this issue?

Basically that error means that one transaction was waiting to get a  
lock on some rows, waited a while and then gave up.  
InnoDB does row level locking, and locks index ranges, so a missing  
(or inefficient (ie not very precise)) index can cause it to lock far  
more rows than it needs to, making this error much more likely. There  
is also a mysql setting that controls how long it will wait before  
giving up. THere is also a plugin (deadlock\_retry) that will retry  
failed writes like this (for us at least it has not been a panacea).  
Looking at the source for this plugin should also give you some clues  
about how to deal with the second part of your problem.

Fred