https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292012-08-22T08:52:02ZRedmineRedmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404602012-08-22T08:52:02ZEtienne Massip
<ul><li><strong>Category</strong> set to <i>Database</i></li></ul><p>Is there a way you could give more information about the existing blocking transaction when the problem occurs?</p>
<p>I'm not a MySQL expert, maybe <a class="external" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html#innodb-standard-monitor">http://dev.mysql.com/doc/refman/5.6/en/innodb-monitors.html#innodb-standard-monitor</a> could be of some help (for InnoDB).</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404612012-08-22T10:06:51ZPetr Pospisil
<ul></ul><p>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.</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404622012-08-22T10:24:38ZPetr Pospisil
<ul></ul><p>deleting issue: ActiveRecord::StatementInvalid: Mysql::Error: Lock wait timeout exceeded; try restarting transaction: SELECT id FROM `issues` WHERE (`lft` >= 1) FOR UPDATE</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404652012-08-22T12:08:21ZEtienne Massip
<ul></ul><p>Petr Pospisil wrote:</p>
<blockquote>
<p>I should you provide more information, but i dont know which...</p>
</blockquote>
<p>Told you, blocking transactions. Because the SQL you've been writing down here is the blocked one, not the blocking one.</p>
<p>Try to execute a <code>SHOW ENGINE INNODB STATUS\G</code> as soon as the issue occurs again?</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404682012-08-22T12:28:42ZToshi MARUYAMA
<ul></ul><p>Try increase max pool size.<br /><a class="external" href="http://stackoverflow.com/questions/3966215/how-to-increase-max-pool-size-in-activerecord">http://stackoverflow.com/questions/3966215/how-to-increase-max-pool-size-in-activerecord</a></p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404772012-08-22T15:24:12ZPetr Pospisil
<ul><li><strong>File</strong> <a href="/attachments/8120">innodb.txt</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/8120/innodb.txt">innodb.txt</a> added</li></ul><p>SHOW ENGINE INNODB STATUS attached</p>
<p>Added to database.yml:<br /> pool: 20<br /> wait_timeout: 30</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=404792012-08-22T15:51:41ZEtienne Massip
<ul></ul><p>Petr Pospisil wrote:</p>
<blockquote>
<p>SHOW ENGINE INNODB STATUS attached</p>
</blockquote>
<p>Not sure but you might have to create a <code>innodb_lock_monitor</code> table teporarily to get lock information by issuing the <code>SHOW ENGINE INNODB STATUS</code> command.</p>
<p>As I said, I'm not a MySQL expert.</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=405992012-08-28T13:33:17ZEtienne Massip
<ul></ul><p>You can identify long running requests:</p>
<ul>
<li>enable request logging and use <a href="https://github.com/wvanbergen/request-log-analyzer" class="external">request-log-analyzer</a></li>
<li>set a value to <a href="http://guides.rubyonrails.org/configuring.html#configuring-active-record" class="external"><code>config.active_record.auto_explain_threshold_in_seconds</code></a> parameter in your <code>config/environment/production.rb</code> so that they automatically get an explain plan in log</li>
</ul>
<p>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.<br />Could that be a tuned parameter or a plugin behavior?</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=409842012-09-18T10:37:26ZPetr Pospisil
<ul></ul><p>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)</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=409952012-09-18T14:48:35ZEtienne Massip
<ul></ul><p>Petr Pospisil wrote:</p>
<blockquote>
<p>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)</p>
</blockquote>
<p>Did you change adapter from <code>mysql</code> to <code>mysql2</code> too?</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=410202012-09-19T08:18:07ZPetr Pospisil
<ul></ul><p>Of course. The original adapter mysql is not compatible with ruby 1.9.3.<br />There are other benefits - Rails 3.x.y is much faster with new Ruby. E.g. /issues - up to 4 times!<br />(And FreeBSD were replaced by Debian)</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=410212012-09-19T08:30:53ZEtienne Massip
<ul></ul><p>Petr Pospisil wrote:</p>
<blockquote>
<p>Of course. The original adapter mysql is not compatible with ruby 1.9.3.</p>
</blockquote>
<p>And do you still have <code>FOR UPDATE</code> clause appended to the end of the <code>SELECT</code> statement?</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=410282012-09-19T12:12:43ZPetr Pospisil
<ul></ul><p>Nop. As I wrote earlier. After upgrading HW and migrating to mysql2 and ruby 193 the locks not occured yet.</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=638172015-05-21T17:43:54Z@ go2null
<ul></ul><p>related to Defect <a class="issue tracker-1 status-5 priority-6 priority-highest closed" title="Defect: Problem to update subtask (Closed)" href="https://www.redmine.org/issues/8143">#8143</a> <em>Problem to update subtask</em>.</p>
<p>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.</p>
<pre>
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
</pre> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=643132015-06-20T08:22:57ZToshi MARUYAMA
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Resolution</strong> set to <i>Duplicate</i></li></ul><p>This is fixed by <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: Tree hierachy being currupted on multiple submissions of an issue (Closed)" href="https://www.redmine.org/issues/6579">#6579</a> on <a class="version" href="https://www.redmine.org/versions/80">3.0.0</a>.<br />But there are new problems of MySQL 5.6 and 5.7 (<a class="issue tracker-2 status-5 priority-4 priority-default closed" title="Feature: MySQL 5.7 support (Closed)" href="https://www.redmine.org/issues/17460">#17460</a>, <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency : always fails (Closed)" href="https://www.redmine.org/issues/19344">#19344</a>).</p> Redmine - Defect #11678: MySQL locks and Redmine collapsinghttps://www.redmine.org/issues/11678?journal_id=643142015-06-20T08:23:19ZToshi MARUYAMA
<ul><li><strong>Is duplicate of</strong> <i><a class="issue tracker-1 status-5 priority-4 priority-default closed" href="/issues/6579">Defect #6579</a>: Tree hierachy being currupted on multiple submissions of an issue</i> added</li></ul>