https://www.redmine.org/https://www.redmine.org/favicon.ico?16793021292014-08-03T11:56:47ZRedmineRedmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=578502014-08-03T11:56:47ZGo MAEDA
<ul></ul><p>This is caused by a change of MySQL 5.7.3-m13. Please see the following URL for details.</p>
<p><a href="http://stackoverflow.com/questions/21075515/creating-tables-and-problems-with-primary-key-in-rails" class="external">mysql - Creating tables and problems with primary key in Rails - Stack Overflow</a></p>
<p>The <a href="https://github.com/rails/rails/commit/26cea8fabe828e7b0535275044cb8316fff1c590" class="external">workaround</a> is included in Rails 4.1. But current Redmine is based on Rails 3.2, so it seems that we have to rely on <a href="https://github.com/rails/rails/pull/13247#issuecomment-32425844" class="external">monkey patch</a> for now.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=578572014-08-04T06:18:08ZEtienne Massip
<ul><li><strong>Target version</strong> set to <i>Candidate for next minor release</i></li></ul><p>Don't know what to do with this one, guess the patch can't be avoided?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=595592014-11-03T08:35:44ZGo MAEDA
<ul><li><strong>Target version</strong> deleted (<del><i>Candidate for next minor release</i></del>)</li></ul><p>Probably this issue will be resolved in Redmine 3.0.0 because it is based on Rails 4.1.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=595752014-11-03T20:41:12ZEnderson Maia
<ul></ul><p>Same problem here using <code>redmine-2.6.0</code>.</p>
<pre>
# mysqld --version
mysqld Ver 5.7.5-m15 for linux-glibc2.5 on x86_64 (MySQL Community Server (GPL))
# bundle exec rails --version
Rails 3.2.19
# ruby --version
ruby 2.1.2p95 (2014-05-08 revision 45877) [x86_64-linux-gnu]
</pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=595762014-11-03T20:47:16ZEnderson Maia
<ul></ul><p>If your're gonna wait for Rails 4 in Redmine 3, maybe an update to the docs to inform it's not compatible with this specific version of MySQL.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=597152014-11-11T09:15:45ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Target version</strong> set to <i>3.0.0</i></li></ul><p>Note about this incompatibillity added to <a class="wiki-page" href="https://www.redmine.org/projects/redmine/wiki/RedmineInstall">RedmineInstall</a>.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=615262015-02-15T09:12:49ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Tracker</strong> changed from <i>Defect</i> to <i>Feature</i></li><li><strong>Subject</strong> changed from <i>Redmine 2.5.2 incompatible with mysql-5.7.3-m13</i> to <i>MySQL 5.7 support</i></li></ul><p>There are still some issues with mysql 5.7 and Rails 4.2:</p>
<ol>
<li>it does not pass the issue concurrency test (dead locks), although the <a href="http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html" class="external">5.7 changelog</a> does not mention any changes to the lock mechanism</li>
<li>timestamps rouding issues after reload that trigger failures in <code>AccountTest#test_user_with_must_change_passwd_should_be_able_to_change_its_password</code>. Here is an example that shows a timestamp returning a different value after reload:</li>
</ol>
<pre>
irb(main):044:0> u=User.first
irb(main):045:0> u.created_on = "2015-02-15 09:38:59.767393"
=> "2015-02-15 09:38:59.767393"
irb(main):046:0> u.save
=> true
irb(main):047:0> u.created_on
=> Sun, 15 Feb 2015 09:38:59 UTC +00:00
irb(main):048:0> u.reload
irb(main):049:0> u.created_on
=> Sun, 15 Feb 2015 09:39:00 UTC +00:00
</pre>
<p>A workaround was committed in <a class="changeset" title="Workaround for timestamps rounding issues with Rails4.2 and mysql5.7 that may kill user session a..." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/14011">r14011</a> for 2.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=615932015-02-18T12:15:53ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>Target version</strong> changed from <i>3.0.0</i> to <i>Candidate for next major release</i></li></ul> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=621542015-03-11T12:02:01ZToshi MARUYAMA
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<ol>
<li>it does not pass the issue concurrency test (dead locks), although the <a href="http://dev.mysql.com/doc/refman/5.7/en/mysql-nutshell.html" class="external">5.7 changelog</a> does not mention</li>
</ol>
</blockquote>
<p><a class="external" href="http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html">http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html</a></p>
<blockquote>
<p>InnoDB uses a new, faster algorithm to detect deadlocks.</p>
</blockquote> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=621572015-03-11T12:04:56ZToshi MARUYAMA
<ul><li><strong>Related to</strong> <i><a class="issue tracker-1 status-5 priority-4 priority-default closed" href="/issues/19344">Defect #19344</a>: MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency : always fails</i> added</li></ul> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=621592015-03-11T12:07:27ZToshi MARUYAMA
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<ol>
<li>it does not pass the issue concurrency test (dead locks)</li>
</ol>
</blockquote>
<p><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> says MySQL 5.6 too.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=622932015-03-16T20:20:16ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>Toshi MARUYAMA wrote:</p>
<blockquote>
<p><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> says MySQL 5.6 too.</p>
</blockquote>
<p>Indeed, the CI server runs MySQL 5.1.</p>
<p>I had a deeper look at the deadlocks issue and it seems to work when doing <code>SELECT * ... FROM UPDATE</code> instead of <code>SELECT id ... FOR UPDATE</code>. Here is a patch for current trunk tested with mysql5.7, the concurrency test passes for me. Could you give it a try?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=622942015-03-16T20:20:44ZJean-Philippe Langjp_lang@yahoo.fr
<ul><li><strong>File</strong> <a href="/attachments/13330">mysql5.7_deadlocks_fix.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/13330/mysql5.7_deadlocks_fix.patch">mysql5.7_deadlocks_fix.patch</a> added</li></ul> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623312015-03-17T12:45:06ZToshi MARUYAMA
<ul></ul><p>On my CentOS7 mariadb-5.5.41-2.el7_0.x86_64:</p>
<p>clean <a class="changeset" title="add MariaDB 5.5 environment to travis (#17460, #19344, #19395)" href="https://www.redmine.org/projects/redmine/repository/svn/revisions/14128">r14128</a>:<br /><pre>
$ ruby test/unit/issue_nested_set_concurrency_test.rb
Run options: --seed 12276
# Running:
F.
Finished in 19.053029s, 0.1050 runs/s, 0.4199 assertions/s.
1) Failure:
IssueNestedSetConcurrencyTest#test_concurrency [test/unit/issue_nested_set_concurrency_test.rb:45]:
Expected "Mysql2::Error: Deadlock found when trying to get lock;
try restarting transaction:
SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (SELECT root_id FROM issues WHERE id IN (319,316)))
ORDER BY `issues`.`id` ASC FOR UPDATE" to be nil.
2 runs, 8 assertions, 1 failures, 0 errors, 0 skips
</pre></p>
<p><a class="changeset" title="add MariaDB 5.5 environment to travis (#17460, #19344, #19395)" href="https://www.redmine.org/projects/redmine/repository/svn/revisions/14128">r14128</a> with note-13 patch:<br /><pre>
$ ruby test/unit/issue_nested_set_concurrency_test.rb
Run options: --seed 50424
# Running:
F.
Finished in 5.455071s, 0.3666 runs/s, 1.2832 assertions/s.
1) Failure:
IssueNestedSetConcurrencyTest#test_concurrency [test/unit/issue_nested_set_concurrency_test.rb:45]:
Expected "Mysql2::Error: Deadlock found when trying to get lock;
try restarting transaction:
SELECT `issues`.* FROM `issues` WHERE `issues`.`root_id` = 432
ORDER BY `issues`.`id` ASC FOR UPDATE" to be nil.
2 runs, 7 assertions, 1 failures, 0 errors, 0 skips
</pre></p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623362015-03-17T16:37:46ZToshi MARUYAMA
<ul></ul><p>This change passes test half times, but fails half times on my MariaDB 5.5.<br /><pre><code class="diff syntaxhl"><span class="gh">diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gd">--- a/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gi">+++ b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="p">@@ -158,7 +158,8 @@</span> module Redmine
self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
else
sets_to_lock = [id, parent_id].compact
<span class="gd">- self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
</span><span class="gi">+ root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a
+ self.class.where(:root_id => root_ids).lock.ids
</span> end
end
</code></pre></p>
<pre>
$ ruby test/unit/issue_nested_set_concurrency_test.rb
Run options: --seed 63128
# Running:
..
Finished in 25.875842s, 0.0773 runs/s, 0.3865 assertions/s.
2 runs, 10 assertions, 0 failures, 0 errors, 0 skips
</pre>
<pre>
$ ruby test/unit/issue_nested_set_concurrency_test.rb
Run options: --seed 40861
# Running:
FF
Finished in 6.222392s, 0.3214 runs/s, 0.6428 assertions/s.
1) Failure:
IssueNestedSetConcurrencyTest#test_concurrency [test/unit/issue_nested_set_concurrency_test.rb:45]:
Expected "Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
UPDATE `issues` SET lft = CASE WHEN lft > 9 THEN lft - 2 ELSE lft END, rgt = CASE WHEN rgt > 9 THEN rgt - 2 ELSE rgt END WHERE `issues`.`root_id` = 4249 AND (lft > 9 OR rgt > 9)" to be nil.
2) Failure:
IssueNestedSetConcurrencyTest#test_concurrent_subtasks_creation [test/unit/issue_nested_set_concurrency_test.rb:61]:
Expected "Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
UPDATE `issues` SET lft = CASE WHEN lft >= 18 THEN lft + 2 ELSE lft END, rgt = CASE WHEN rgt >= 18 THEN rgt + 2 ELSE rgt END WHERE `issues`.`root_id` = 4263 AND (lft >= 18 OR rgt >= 18)" to be nil.
2 runs, 4 assertions, 2 failures, 0 errors, 0 skips
</pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623372015-03-17T17:53:07ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>Toshi, your patch does not do what it's supposed to.</p>
<p>You may want to write:</p>
<pre><code>root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id)</code></pre>
<p>instead of:</p>
<pre><code>root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a</code></pre>
<p>which returns records without their ids. The lock after that does nothing:</p>
<pre><code>SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` IS NULL FOR UPDATE</code></pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623382015-03-17T18:07:33ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>I've isolated the log for a thread that triggers a dead lock. It ends with:</p>
<pre>
[52206168] BEGIN
[52206168] SELECT `issues`.* FROM `issues` WHERE `issues`.`id` = 781 LIMIT 1
[52206168] SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` = 778 ORDER BY `issues`.`id` ASC FOR UPDATE
[52206168] ROLLBACK
[52206168] ERROR: Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction:
SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` = 778 ORDER BY `issues`.`id` ASC FOR UPDATE
</pre>
<p>As we can see, the thread starts a transaction, has no lock yet and gets a dead lock error on the first lock. Anyone knows what would explain that?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623402015-03-17T19:00:50ZToshi MARUYAMA
<ul></ul><p>Jean-Philippe Lang wrote:</p>
<blockquote>
<p>Toshi, your patch does not do what it's supposed to.</p>
<p>You may want to write:</p>
<p>root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id)</p>
<p>instead of:</p>
<p>root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a</p>
<p>which returns records without their ids. The lock after that does nothing:</p>
<p>SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` IS NULL FOR UPDATE</p>
</blockquote>
<p>This changes fails 3/4 times.</p>
<pre><code class="diff syntaxhl"><span class="gh">diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gd">--- a/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gi">+++ b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="p">@@ -158,7 +158,8 @@</span> module Redmine
self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
else
sets_to_lock = [id, parent_id].compact
<span class="gd">- self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
</span><span class="gi">+ root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id).compact.uniq
+ self.class.where(:root_id => root_ids).lock.ids
</span> end
end
</code></pre>
<pre>
$ ruby test/unit/issue_nested_set_concurrency_test.rb
Run options: --seed 1553
# Running:
F.
Finished in 20.314407s, 0.0985 runs/s, 0.4430 assertions/s.
1) Failure:
IssueNestedSetConcurrencyTest#test_concurrency [test/unit/issue_nested_set_concurrency_test.rb:45]:
Expected "Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction: SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` = 7049 FOR UPDATE" to be nil.
2 runs, 9 assertions, 1 failures, 0 errors, 0 skips
</pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623482015-03-18T04:10:34ZToshi MARUYAMA
<ul></ul><p>"SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;" reduces failure times on my MariaDB 5.5.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623492015-03-18T05:29:28ZToshi MARUYAMA
<ul></ul><p>This is code from <a class="source" href="https://www.redmine.org/projects/redmine/repository/svn/entry/tags/2.6.3/lib/plugins/awesome_nested_set/lib/awesome_nested_set/model/transactable.rb">source:tags/2.6.3/lib/plugins/awesome_nested_set/lib/awesome_nested_set/model/transactable.rb</a> .</p>
<pre><code class="diff syntaxhl"><span class="gh">diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gd">--- a/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gi">+++ b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="p">@@ -148,7 +148,29 @@</span> module Redmine
new_record? || !is_or_is_ancestor_of?(issue)
end
+ def in_tenacious_transaction(&block)
<span class="gi">+ retry_count = 0
+ begin
+ transaction(&block)
+ rescue ActiveRecord::StatementInvalid => error
+ raise unless error.message =~ /Deadlock found when trying to get lock/
+ raise unless retry_count < 10
+ retry_count += 1
+ logger.info "Deadlock detected on retry #{retry_count}, restarting transaction"
+ sleep(rand(retry_count)*0.1) # Aloha protocol
+ retry
+ end
+ end
+
</span> def lock_nested_set
<span class="gi">+ if self.class.connection.adapter_name =~ /mysql/i
+ in_tenacious_transaction { lock_nested_set_in_tenacious_transaction }
+ else
+ lock_nested_set_in_tenacious_transaction
+ end
+ end
+
+ def lock_nested_set_in_tenacious_transaction
</span> if self.class.connection.adapter_name =~ /sqlserver/i
lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)"
# Custom lock for SQLServer
</code></pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623502015-03-18T06:07:50ZToshi MARUYAMA
<ul></ul><p>Note-20 is wrong because it uses nested transaction and parent transaction does not use lock.<br />This is fix.</p>
<pre><code class="diff syntaxhl"><span class="gh">diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gd">--- a/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="gi">+++ b/lib/redmine/nested_set/issue_nested_set.rb
</span><span class="p">@@ -148,7 +148,29 @@</span> module Redmine
new_record? || !is_or_is_ancestor_of?(issue)
end
+ def get_lock_mysql(&block)
<span class="gi">+ retry_count = 0
+ begin
+ yield
+ rescue ActiveRecord::StatementInvalid => error
+ raise unless error.message =~ /Deadlock found when trying to get lock/
+ raise unless retry_count < 10
+ retry_count += 1
+ logger.info "Deadlock detected on retry #{retry_count}, restarting transaction"
+ sleep(rand(retry_count)*0.1) # Aloha protocol
+ retry
+ end
+ end
+
</span> def lock_nested_set
<span class="gi">+ if self.class.connection.adapter_name =~ /mysql/i
+ get_lock_mysql { get_lock }
+ else
+ get_lock
+ end
+ end
+
+ def get_lock
</span> if self.class.connection.adapter_name =~ /sqlserver/i
lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)"
# Custom lock for SQLServer
</code></pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623562015-03-18T07:23:55ZJean-Philippe Langjp_lang@yahoo.fr
<ul></ul><p>Toshi, the idea of the implementation of nested sets in 3.0.0 is to start the transaction by locking all the rows that might be updated or used to compute shifts in the transaction, in order by prevent dead locks and inconsistencies. I won't commit that workaround until I figure out why it doesn't work as I expect (note-17) in recent versions of MySQL.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=623792015-03-19T04:00:19ZToshi MARUYAMA
<ul></ul><p>I think MySQL uses <strong>Gap Locks</strong>, so we cannot avoid deadlock.<br /><a class="external" href="http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html#idm140169015854080">http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html#idm140169015854080</a></p>
<p>I tried <strong>READ COMMITTED</strong> on MariaDB 5.5, but deadlock raised. I don't know the reason.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=764552017-02-01T17:28:52ZDave Martin
<ul></ul><p>Do current versions of Redmine still not support MySQL 5.7?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=773962017-03-21T15:38:51ZToshi MARUYAMA
<ul></ul><p><a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: #lock_nested_set very slow on mysql with thousands of subtasks (Closed)" href="https://www.redmine.org/issues/23318#note-18">#23318#note-18</a> patch reduces test failure times from about 100% to 50% on my CentOS7 mariadb-5.5.52-1.el7.x86_64.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=781412017-04-21T16:11:28ZStephane Evr
<ul></ul><p>Latest versions of Ubuntu server only provide the 5.7 package, version 5.5 is really difficult to install on it: <a class="external" href="https://askubuntu.com/questions/763240/is-it-possible-to-install-mysql-5-5-or-5-6-on-ubuntu-16-04">https://askubuntu.com/questions/763240/is-it-possible-to-install-mysql-5-5-or-5-6-on-ubuntu-16-04</a></p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=781552017-04-23T13:45:31ZStephane Evr
<ul></ul><p>Here are some logs from MySQL 5.7:</p>
<pre>
mysql> select * FROM INNODB_LOCKS \G;
*************************** 1. row ***************************
lock_id: 56163:265:12:54
lock_trx_id: 56163
lock_mode: X
lock_type: RECORD
lock_table: `redmine_test`.`issues`
lock_index: index_issues_on_root_id_and_lft_and_rgt
lock_space: 265
lock_page: 12
lock_rec: 54
lock_data: 653, 1, 20, 653
*************************** 2. row ***************************
lock_id: 56159:265:12:54
lock_trx_id: 56159
lock_mode: X
lock_type: RECORD
lock_table: `redmine_test`.`issues`
lock_index: index_issues_on_root_id_and_lft_and_rgt
lock_space: 265
lock_page: 12
lock_rec: 54
lock_data: 653, 1, 20, 653
2 rows in set, 1 warning (0.00 sec)
mysql> SHOW ENGINE INNODB STATUS \G;
------------------------
LATEST DETECTED DEADLOCK
------------------------
2017-04-23 14:38:12 0x7f308c273700
*** (1) TRANSACTION:
TRANSACTION 56161, ACTIVE 0 sec starting index read
mysql tables in use 2, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 9, OS thread handle 139846486136576, query id 1081 localhost root Sending data
SELECT `issues`.`id` FROM `issues` WHERE (root_id IN (SELECT root_id FROM issues WHERE id IN (658,655))) ORDER BY `issues`.`id` ASC FOR UPDATE
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 265 page no 12 n bits 160 index index_issues_on_root_id_and_lft_and_rgt of table `redmine_test`.`issues` trx id 56161 lock_mode X waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000028d; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 80000016; asc ;;
3: len 4; hex 8000028d; asc ;;
*** (2) TRANSACTION:
TRANSACTION 56159, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 60 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 139846486537984, query id 1134 localhost root updating
UPDATE `issues` SET lft = CASE WHEN lft > 5 THEN lft - 2 ELSE lft END, rgt = CASE WHEN rgt > 5 THEN rgt - 2 ELSE rgt END WHERE `issues`.`root_id` = 653 AND (lft > 5 OR rgt > 5)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 265 page no 12 n bits 160 index index_issues_on_root_id_and_lft_and_rgt of table `redmine_test`.`issues` trx id 56159 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
0: len 8; hex 73757072656d756d; asc supremum;;
Record lock, heap no 47 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000028d; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 80000016; asc ;;
3: len 4; hex 8000028d; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 265 page no 12 n bits 160 index index_issues_on_root_id_and_lft_and_rgt of table `redmine_test`.`issues` trx id 56159 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 47 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 4; hex 8000028d; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 80000016; asc ;;
3: len 4; hex 8000028d; asc ;;
*** WE ROLL BACK TRANSACTION (1)
</pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=781792017-04-24T11:11:47ZStephane Evr
<ul></ul><p>Should we put the index on :issues => [:root_id, :lft, :rgt] as unique? I think this would play a role in the number of records being locked when we do something such as:</p>
<pre>
In remove_from_nested_set:
self.class.where(:root_id => root_id).where("lft >= ? AND rgt <= ?", lft, rgt).update_all(...)
In add_to_nested_set:
self.class.where(:root_id => root_id).where("lft >= ? OR rgt >= ?", lft, lft).update_all(...)
</pre>
<p>Or is a reorder needed before the update_all clause ?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=786852017-05-18T20:22:24ZMark Anderson
<ul></ul><p>Am about to make the move to Ubuntu 16.04. Can I install Redmine 3.3 and stick with MySQL 5.7 now?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=786862017-05-18T20:56:32ZDeoren Moor
<ul></ul><p>Mark Anderson wrote:</p>
<blockquote>
<p>Am about to make the move to Ubuntu 16.04. Can I install Redmine 3.3 and stick with MySQL 5.7 now?</p>
</blockquote>
<p>For what it is worth, we have been using an Ubuntu 16.04 + MariaDB 10.0.x setup for over six months now without any obvious issues.</p>
<ul>
<li>web server: Ubuntu 16.04, nginx/Passenger, mysql-client 5.7.x</li>
<li>database server: MariaDB 10.0.x</li>
</ul> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=786972017-05-19T10:42:43ZPavel Rosický
<ul><li><strong>File</strong> <a href="/attachments/18363">issue_nested_set.rb.patch</a> <a class="icon-only icon-download" title="Download" href="/attachments/download/18363/issue_nested_set.rb.patch">issue_nested_set.rb.patch</a> added</li></ul><p><a class="user active" href="https://www.redmine.org/users/50304">Stéphane Thomas</a> Evr - Hi, the index on :issues => [:root_id, :lft, :rgt] should be definitely unique.</p>
<p>To avoid duplicate entries during shifts I added an additional reorder statment:<br /><pre>
remove_from_nested_set
.reorder('lft desc')
add_to_nested_set
.reorder('lft asc')
</pre></p>
<p>but it didn't help anyway, I think it's because shifts are overlapping, especially during creating & deleting records at the same time.</p>
<p>unfortunatelly awesome_nested_set has the same issue</p>
<p>locking all issues instead of subtree works correctly (no deadlocks), but it should be definitely avoided for performance reasons<br />self.class.reorder(:id).lock</p>
<p>log<br /><pre>
# Running:
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107872360
Deadlock detected on update, restarting transaction retry #1 thread: 107872360
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863660
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107872360
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #2 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107872360
Deadlock detected on update, restarting transaction retry #1 thread: 107872360
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on update, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863660
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107872360
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863880
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107863660
Deadlock detected on getting lock, restarting transaction retry #1 thread: 107872360
..
Finished in 14.603111s, 0.1370 runs/s, 0.6848 assertions/s.
2 runs, 10 assertions, 0 failures, 0 errors, 0 skips
</pre></p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=865392018-08-08T08:39:39Zyossi edri
<ul></ul><p>Hi,<br />what is the highest tested version of MySql that is supported in Redmine 3.x - 4.x<br />thanks</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=866832018-08-19T14:38:54Zحجتاله م
<ul></ul><p>yossi edri wrote:</p>
<blockquote>
<p>Hi,<br />what is the highest tested version of MySql that is supported in Redmine 3.x - 4.x<br />thanks</p>
</blockquote>
<p>Salam</p>
<p>redmine 3.2 work by last version of mysql but redmine 3.3 and higher not work well with mysql 5.5 or higher</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=867612018-08-25T16:14:09ZAlexandr Kirilov
<ul></ul><p>Just installed Redmine in following ports for FreeBSD. There are included mysql56, and the lowest version available from ports is mysql55. I've been trying Redmine with mysql80. Seems working for the case of mysql. But I got this issue - <a class="external" href="http://www.redmine.org/boards/2/topics/55693">http://www.redmine.org/boards/2/topics/55693</a>.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=880212018-10-16T16:12:48ZThomas Löber
<ul></ul><p>Does it make sense to retry the transaction inside the <code>Issue</code> class?</p>
<p>In <code>app/models/issue.rb</code>:</p>
<pre><code class="ruby syntaxhl"> <span class="k">def</span> <span class="nc">self</span><span class="o">.</span><span class="nf">transaction</span><span class="p">(</span><span class="n">options</span><span class="o">=</span><span class="p">{},</span> <span class="o">&</span><span class="n">block</span><span class="p">)</span>
<span class="n">retry_count</span> <span class="o">=</span> <span class="mi">0</span>
<span class="k">begin</span>
<span class="k">super</span>
<span class="k">rescue</span> <span class="no">ActiveRecord</span><span class="o">::</span><span class="no">StatementInvalid</span> <span class="o">=></span> <span class="n">error</span>
<span class="k">raise</span> <span class="k">if</span> <span class="n">connection</span><span class="p">.</span><span class="nf">adapter_name</span> <span class="o">!~</span> <span class="sr">/mysql/i</span>
<span class="k">raise</span> <span class="k">if</span> <span class="n">error</span><span class="p">.</span><span class="nf">message</span> <span class="o">!~</span> <span class="sr">/Deadlock found when trying to get lock/</span>
<span class="k">raise</span> <span class="k">if</span> <span class="n">retry_count</span> <span class="o">==</span> <span class="mi">10</span>
<span class="n">retry_count</span> <span class="o">+=</span> <span class="mi">1</span>
<span class="n">wait_ms</span> <span class="o">=</span> <span class="nb">rand</span><span class="p">(</span><span class="n">retry_count</span> <span class="o">*</span> <span class="mi">100</span><span class="p">)</span>
<span class="k">if</span> <span class="n">logger</span>
<span class="n">logger</span><span class="p">.</span><span class="nf">info</span><span class="p">(</span><span class="s2">"Deadlock found when saving </span><span class="si">#{</span><span class="nb">self</span><span class="si">}</span><span class="s2">: "</span><span class="p">\</span>
<span class="s2">"Waiting for </span><span class="si">#{</span><span class="n">wait_ms</span><span class="si">}</span><span class="s2"> ms before restarting the transaction (retry #</span><span class="si">#{</span><span class="n">retry_count</span><span class="si">}</span><span class="s2">)"</span><span class="p">)</span>
<span class="k">end</span>
<span class="nb">sleep</span><span class="p">(</span><span class="n">wait_ms</span> <span class="o">/</span> <span class="mf">1000.0</span><span class="p">)</span>
<span class="k">retry</span>
<span class="k">end</span>
<span class="k">end</span>
</code></pre> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=892672019-01-08T05:37:13ZMarius BĂLTEANU
<ul><li><strong>Has duplicate</strong> <i><a class="issue tracker-1 status-5 priority-5 priority-high2 closed" href="/issues/28414">Defect #28414</a>: Does Redmine compatible with MySQL 5.7 or not ?</i> added</li></ul> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=892692019-01-08T06:23:35ZMarius BĂLTEANU
<ul></ul><p>Even if the problem with deadlocks still exists on MySQL 5.7, I think that it is safe to say that Redmine 4.0 officially supports MySQL 5.7 and to close this issue.</p>
<p>CI server run the tests on MySQL 5.7 and the install documentation has been updated.</p>
<p>What do you think?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=892952019-01-09T16:54:26ZDeoren Moor
<ul></ul><blockquote>
<p>Even if the problem with deadlocks still exists on MySQL 5.7, I think that it is safe to say that Redmine 4.0 officially supports MySQL 5.7 and to close this issue.</p>
<p>CI server run the tests on MySQL 5.7 and the install documentation has been updated.</p>
<p>What do you think?</p>
</blockquote>
<p>Loose tangent: Does the same also apply to MariaDB? FWIW, I've run Redmine on MariaDB 10.0 for years without (apparent) issue, hoping to upgrade to a newer MariaDB release in the near future.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=893002019-01-10T02:05:40ZMarius BĂLTEANU
<ul></ul><p>Deoren Moor wrote:</p>
<blockquote><blockquote>
<p>Even if the problem with deadlocks still exists on MySQL 5.7, I think that it is safe to say that Redmine 4.0 officially supports MySQL 5.7 and to close this issue.</p>
<p>CI server run the tests on MySQL 5.7 and the install documentation has been updated.</p>
<p>What do you think?</p>
</blockquote>
<p>Loose tangent: Does the same also apply to MariaDB? FWIW, I've run Redmine on MariaDB 10.0 for years without (apparent) issue, hoping to upgrade to a newer MariaDB release in the near future.</p>
</blockquote>
<p>No, it applies only to MySQL 5.7. MariaDB is not supported (or at least officially) and an user reported some failing tests (please see <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: "Last updated by" filter causes an SQL error with MariaDB (Closed)" href="https://www.redmine.org/issues/30367">#30367</a>).</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=971582020-04-05T16:07:28ZToshi MARUYAMA
<ul></ul><p>Marius BALTEANU wrote:</p>
<blockquote>
<p>Even if the problem with deadlocks still exists on MySQL 5.7, I think that it is safe to say that Redmine 4.0 officially supports MySQL 5.7 and to close this issue.</p>
<p>CI server run the tests on MySQL 5.7 and the install documentation has been updated.</p>
<p>What do you think?</p>
</blockquote>
<p>Tests skip on MySQL (<a class="changeset" title="Skip all issue concurrency tests with MySQL." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/16926">r16926</a>).</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=971592020-04-05T16:14:44ZMarius BĂLTEANU
<ul></ul><p>Toshi MARUYAMA wrote:</p>
<blockquote>
<p>Marius BALTEANU wrote:</p>
<blockquote>
<p>Even if the problem with deadlocks still exists on MySQL 5.7, I think that it is safe to say that Redmine 4.0 officially supports MySQL 5.7 and to close this issue.</p>
<p>CI server run the tests on MySQL 5.7 and the install documentation has been updated.</p>
<p>What do you think?</p>
</blockquote>
<p>Tests skip on MySQL (<a class="changeset" title="Skip all issue concurrency tests with MySQL." href="https://www.redmine.org/projects/redmine/repository/svn/revisions/16926">r16926</a>).</p>
</blockquote>
<p>Yes, but the CI uses MySQL 5.7 so is quite strange to say we do not support MySQL 5.7, but we run the tests against MySQL 5.7.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=1101082023-05-24T08:08:33ZIvan Cenov
<ul></ul><p>Newer versions of MySQL, after 5.7 are still not applicable? MySQL 8.x.x etc? Also does mysql2 gem support higher versions of MySQL?</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=1110712023-10-23T18:08:21ZMarius BĂLTEANU
<ul><li><strong>Status</strong> changed from <i>New</i> to <i>Closed</i></li><li><strong>Assignee</strong> set to <i>Marius BĂLTEANU</i></li><li><strong>Target version</strong> deleted (<del><i>Candidate for next major release</i></del>)</li><li><strong>Resolution</strong> set to <i>Fixed</i></li></ul><p>MySQL 5.7 is supported for some time with a known concurrency issue (<code>Mysql2::Error: Deadlock found when trying to get lock; try restarting transaction</code>). I'm closing this.</p> Redmine - Feature #17460: MySQL 5.7 supporthttps://www.redmine.org/issues/17460?journal_id=1115672023-11-20T23:01:35ZMarius BĂLTEANU
<ul></ul><p>This concurrency issue is fixed in the upcoming Redmine <a class="version" href="https://www.redmine.org/versions/191">5.1.1</a> (it will be released soon) and <a class="version" href="https://www.redmine.org/versions/175">6.0.0</a>, but it may require some changes to the database settings, please see all the comments from <a class="issue tracker-1 status-5 priority-4 priority-default closed" title="Defect: MySQL / MariaDB issue nested set deadlocks and consistency (Closed)" href="https://www.redmine.org/issues/39437">#39437</a> and the <a class="wiki-page" href="https://www.redmine.org/projects/redmine/wiki/MySQL_configuration">MySQL_configuration</a> page.</p>