Project

General

Profile

Actions

Defect #11678

closed

MySQL locks and Redmine collapsing

Defect #11678: MySQL locks and Redmine collapsing

Added by Petr Pospisil about 14 years ago. Updated over 11 years ago.

Status:
Closed
Priority:
Normal
Assignee:
-
Category:
Database
Target version:
-
Resolution:
Duplicate
Affected version:

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...


Files

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

Related issues 1 (0 open — 1 closed)

Is duplicate of Redmine - Defect #6579: Tree hierachy being currupted on multiple submissions of an issueClosedJean-Philippe LangActions

Updated by Etienne Massip about 14 years ago Actions #1

  • 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).

Updated by Petr Pospisil about 14 years ago Actions #2

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.

Updated by Petr Pospisil about 14 years ago Actions #3

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

Updated by Etienne Massip about 14 years ago Actions #4

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?

Updated by Petr Pospisil about 14 years ago Actions #6

SHOW ENGINE INNODB STATUS attached

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

Updated by Etienne Massip about 14 years ago Actions #7

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.

Updated by Etienne Massip about 14 years ago Actions #8

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?

Updated by Petr Pospisil about 14 years ago Actions #9

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)

Updated by Etienne Massip about 14 years ago Actions #10

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?

Updated by Petr Pospisil about 14 years ago Actions #11

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)

Updated by Etienne Massip about 14 years ago Actions #12

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?

Updated by Petr Pospisil about 14 years ago Actions #13

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

Updated by @ go2null over 11 years ago Actions #14

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

Updated by Toshi MARUYAMA over 11 years ago Actions #15

  • 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).

Updated by Toshi MARUYAMA over 11 years ago Actions #16

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

Also available in: PDF Atom