Defect #11678

MySQL locks and Redmine collapsing

Added by Petr Pospisil over 5 years ago. Updated over 2 years ago.

Status:ClosedStart date:
Priority:NormalDue date:
Assignee:-% Done:

0%

Category:Database
Target version:-
Resolution:Duplicate Affected version:2.0.3

Description

Hello

Our heavily used Redmine often dies during update or create a new issue:

ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT `issues`.* FROM `issues` WHERE `issues`.`root_id` IS NULL ORDER BY `rgt` desc LIMIT 1 FOR UPDATE

Ruby 1.8.7, MySql 5.5.4, Redmine 2.0.3, OS FreeBSD, WebServer is Passenger. Every 5 minutes is running rake redmine:email:receive_pop3.

No errors in production.log.

It is imposible to simulate this behaviour, but it occurs if more than 5-10 peoples are working. We had no problems on older Redmine < 2.0.

It's frustrating...

innodb.txt Magnifier (5.61 KB) Petr Pospisil, 2012-08-22 17:24


Related issues

Duplicates Redmine - Defect #6579: Tree hierachy being currupted on multiple submissions of ... Closed 2010-10-05

History

#1 Updated by Etienne Massip over 5 years ago

  • Category set to Database

Is there a way you could give more information about the existing blocking transaction when the problem occurs?

I'm not a MySQL expert, maybe http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html#innodb-standard-monitor could be of some help (for InnoDB).

#2 Updated by Petr Pospisil over 5 years ago

I should you provide more information, but i dont know which... I think the hint is in upgrade Redmine from 1.9.x to 2.0.3. Others systems are same.

#3 Updated by Petr Pospisil over 5 years ago

deleting issue: ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT id FROM `issues` WHERE (`lft` >= 1) FOR UPDATE

#4 Updated by Etienne Massip over 5 years ago

Petr Pospisil wrote:

I should you provide more information, but i dont know which...

Told you, blocking transactions. Because the SQL you've been writing down here is the blocked one, not the blocking one.

Try to execute a SHOW ENGINE INNODB STATUS\G as soon as the issue occurs again?

#6 Updated by Petr Pospisil over 5 years ago

SHOW ENGINE INNODB STATUS attached

Added to database.yml:
pool: 20
wait_timeout: 30

#7 Updated by Etienne Massip over 5 years ago

Petr Pospisil wrote:

SHOW ENGINE INNODB STATUS attached

Not sure but you might have to create a innodb_lock_monitor table teporarily to get lock information by issuing the SHOW ENGINE INNODB STATUS command.

As I said, I'm not a MySQL expert.

#8 Updated by Etienne Massip over 5 years ago

You can identify long running requests:

BTW, by default there should not be such "FOR UPDATE" part at the end of statement unless specified in code and there's no such thing.
Could that be a tuned parameter or a plugin behavior?

#9 Updated by Petr Pospisil over 5 years ago

Nobody uses FOR UPDATE statement. But the problem seems to be solved. We bought better server and we changed ruby to 1.9.3... :o)

#10 Updated by Etienne Massip over 5 years ago

Petr Pospisil wrote:

Nobody uses FOR UPDATE statement. But the problem seems to be solved. We bought better server and we changed ruby to 1.9.3... :o)

Did you change adapter from mysql to mysql2 too?

#11 Updated by Petr Pospisil about 5 years ago

Of course. The original adapter mysql is not compatible with ruby 1.9.3.
There are other benefits - Rails 3.x.y is much faster with new Ruby. E.g. /issues - up to 4 times!
(And FreeBSD were replaced by Debian)

#12 Updated by Etienne Massip about 5 years ago

Petr Pospisil wrote:

Of course. The original adapter mysql is not compatible with ruby 1.9.3.

And do you still have FOR UPDATE clause appended to the end of the SELECT statement?

#13 Updated by Petr Pospisil about 5 years ago

Nop. As I wrote earlier. After upgrading HW and migrating to mysql2 and ruby 193 the locks not occured yet.

#14 Updated by @ go2null over 2 years ago

related to Defect #8143 Problem to update subtask.

I am also having the same issue - it is caused with large parent-child hierarchies and the consequent cascading updates of status, priority and other checks.

Environment:
  Redmine version                2.6.0.stable
  Ruby version                   1.9.3-p484 (2013-11-22) [x86_64-linux]
  Rails version                  3.2.19
  Environment                    production
  Database adapter               Mysql2

#15 Updated by Toshi MARUYAMA over 2 years ago

  • Status changed from New to Closed
  • Resolution set to Duplicate

This is fixed by #6579 on 3.0.0.
But there are new problems of MySQL 5.6 and 5.7 (#17460, #19344).

#16 Updated by Toshi MARUYAMA over 2 years ago

  • Duplicates Defect #6579: Tree hierachy being currupted on multiple submissions of an issue added

Also available in: Atom PDF