Feature #17460

MySQL 5.7 support

Added by Sam Sheen over 2 years ago. Updated 3 days ago.

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

0%

Category:Database
Target version:Candidate for next major release
Resolution:

Description

Env:
OS: Ubuntu 14.04 LTS
ruby 2.1.2p95 (2014-05-08 revision 45877) [i686-linux]
rails (4.1.4, 3.2.19)
redmine-2.5.2
svn, version 1.8.9 (r1591380)
Environment:
Redmine version 2.5.2.stable
Ruby version 2.1.2-p95 (2014-05-08) [i686-linux]
Rails version 3.2.19
Environment production
Database adapter Mysql2
SCM:
Subversion 1.8.9
Filesystem
Redmine plugins:
no plugin installed

After install mysql, redmine, I try to set DB and find a problem in mysql 5.7.

root@lenovo:/usr/local/src/redmine-2.5.2# RAILS_ENV=production rake db:migrate
Setup: migrating ========================================================
-- create_table("attachments", {:force=>true})
rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: All parts of a PRIMARY KEY must be NOT NULL; if you need NULL in a key, use UNIQUE instead: CREATE TABLE `attachments` (`id` int(11) DEFAULT NULL auto_increment PRIMARY KEY, `container_id` int(11) DEFAULT 0 NOT NULL, `container_type` varchar(30) DEFAULT '' NOT NULL, `filename` varchar(255) DEFAULT '' NOT NULL, `disk_filename` varchar(255) DEFAULT '' NOT NULL, `filesize` int(11) DEFAULT 0 NOT NULL, `content_type` varchar(60) DEFAULT '', `digest` varchar(40) DEFAULT '' NOT NULL, `downloads` int(11) DEFAULT 0 NOT NULL, `author_id` int(11) DEFAULT 0 NOT NULL, `created_on` datetime) ENGINE=InnoDB
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

But, redmine is compatible mysql-5.6.19 and work perfectly.

Sam Sheen

mysql5.7_deadlocks_fix.patch Magnifier (1.54 KB) Jean-Philippe Lang, 2015-03-16 21:20


Related issues

Related to Redmine - Defect #19344: MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency... New

Associated revisions

Revision 14011
Added by Jean-Philippe Lang about 2 years ago

Workaround for timestamps rounding issues with Rails4.2 and mysql5.7 that may kill user session after password is changed (#17460).

Revision 14077
Added by Toshi MARUYAMA about 2 years ago

add MariaDB 10.0 environment to travis (#17460, #19344)

Revision 14085
Added by Toshi MARUYAMA about 2 years ago

add MySQL 5.6 and 5.7 environments to travis (#17460, #19344)

Revision 14128
Added by Toshi MARUYAMA about 2 years ago

add MariaDB 5.5 environment to travis (#17460, #19344, #19395)

History

#1 Updated by Go MAEDA over 2 years ago

This is caused by a change of MySQL 5.7.3-m13. Please see the following URL for details.

mysql - Creating tables and problems with primary key in Rails - Stack Overflow

The workaround is included in Rails 4.1. But current Redmine is based on Rails 3.2, so it seems that we have to rely on monkey patch for now.

#2 Updated by Etienne Massip over 2 years ago

  • Target version set to Candidate for next minor release

Don't know what to do with this one, guess the patch can't be avoided?

#3 Updated by Go MAEDA over 2 years ago

  • Target version deleted (Candidate for next minor release)

Probably this issue will be resolved in Redmine 3.0.0 because it is based on Rails 4.1.

#4 Updated by Enderson Maia over 2 years ago

Same problem here using redmine-2.6.0.

# 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]

#5 Updated by Enderson Maia over 2 years ago

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.

#6 Updated by Jean-Philippe Lang over 2 years ago

  • Target version set to 3.0.0

Note about this incompatibillity added to RedmineInstall.

#7 Updated by Jean-Philippe Lang about 2 years ago

  • Tracker changed from Defect to Feature
  • Subject changed from Redmine 2.5.2 incompatible with mysql-5.7.3-m13 to MySQL 5.7 support

There are still some issues with mysql 5.7 and Rails 4.2:

  1. it does not pass the issue concurrency test (dead locks), although the 5.7 changelog does not mention any changes to the lock mechanism
  2. timestamps rouding issues after reload that trigger failures in AccountTest#test_user_with_must_change_passwd_should_be_able_to_change_its_password. Here is an example that shows a timestamp returning a different value after reload:
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

A workaround was committed in r14011 for 2.

#8 Updated by Jean-Philippe Lang about 2 years ago

  • Target version changed from 3.0.0 to Candidate for next major release

#9 Updated by Toshi MARUYAMA about 2 years ago

Jean-Philippe Lang wrote:

  1. it does not pass the issue concurrency test (dead locks), although the 5.7 changelog does not mention

http://dev.mysql.com/doc/refman/5.6/en/mysql-nutshell.html

InnoDB uses a new, faster algorithm to detect deadlocks.

#10 Updated by Toshi MARUYAMA about 2 years ago

  • Related to Defect #19344: MySQL 5.6: IssueNestedSetConcurrencyTest#test_concurrency : always fails added

#11 Updated by Toshi MARUYAMA about 2 years ago

Jean-Philippe Lang wrote:

  1. it does not pass the issue concurrency test (dead locks)

#19344 says MySQL 5.6 too.

#12 Updated by Jean-Philippe Lang about 2 years ago

Toshi MARUYAMA wrote:

#19344 says MySQL 5.6 too.

Indeed, the CI server runs MySQL 5.1.

I had a deeper look at the deadlocks issue and it seems to work when doing SELECT * ... FROM UPDATE instead of SELECT id ... FOR UPDATE. Here is a patch for current trunk tested with mysql5.7, the concurrency test passes for me. Could you give it a try?

#14 Updated by Toshi MARUYAMA about 2 years ago

On my CentOS7 mariadb-5.5.41-2.el7_0.x86_64:

clean r14128:

$ 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

r14128 with note-13 patch:

$ 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

#15 Updated by Toshi MARUYAMA about 2 years ago

This change passes test half times, but fails half times on my MariaDB 5.5.

diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
--- a/lib/redmine/nested_set/issue_nested_set.rb
+++ b/lib/redmine/nested_set/issue_nested_set.rb
@@ -158,7 +158,8 @@ module Redmine
           self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
         else
           sets_to_lock = [id, parent_id].compact
-          self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
+          root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a
+          self.class.where(:root_id => root_ids).lock.ids
         end
       end

$ 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

$ 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

#16 Updated by Jean-Philippe Lang about 2 years ago

Toshi, your patch does not do what it's supposed to.

You may want to write:

root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id)

instead of:

root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a

which returns records without their ids. The lock after that does nothing:

SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` IS NULL FOR UPDATE

#17 Updated by Jean-Philippe Lang about 2 years ago

I've isolated the log for a thread that triggers a dead lock. It ends with:

[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

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?

#18 Updated by Toshi MARUYAMA about 2 years ago

Jean-Philippe Lang wrote:

Toshi, your patch does not do what it's supposed to.

You may want to write:

root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id)

instead of:

root_ids = self.class.where(:id => sets_to_lock).select(:root_id).to_a

which returns records without their ids. The lock after that does nothing:

SELECT `issues`.`id` FROM `issues` WHERE `issues`.`root_id` IS NULL FOR UPDATE

This changes fails 3/4 times.

diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
--- a/lib/redmine/nested_set/issue_nested_set.rb
+++ b/lib/redmine/nested_set/issue_nested_set.rb
@@ -158,7 +158,8 @@ module Redmine
           self.class.reorder(:id).where(:root_id => sets_to_lock).lock(lock).ids
         else
           sets_to_lock = [id, parent_id].compact
-          self.class.reorder(:id).where("root_id IN (SELECT root_id FROM #{self.class.table_name} WHERE id IN (?))", sets_to_lock).lock.ids
+          root_ids = self.class.where(:id => sets_to_lock).pluck(:root_id).compact.uniq
+          self.class.where(:root_id => root_ids).lock.ids
         end
       end
$ 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

#19 Updated by Toshi MARUYAMA about 2 years ago

"SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;" reduces failure times on my MariaDB 5.5.

#20 Updated by Toshi MARUYAMA about 2 years ago

This is code from source:tags/2.6.3/lib/plugins/awesome_nested_set/lib/awesome_nested_set/model/transactable.rb .

diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
--- a/lib/redmine/nested_set/issue_nested_set.rb
+++ b/lib/redmine/nested_set/issue_nested_set.rb
@@ -148,7 +148,29 @@ module Redmine
         new_record? || !is_or_is_ancestor_of?(issue)
       end

+      def in_tenacious_transaction(&block)
+        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
+
       def lock_nested_set
+        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
         if self.class.connection.adapter_name =~ /sqlserver/i
           lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)" 
           # Custom lock for SQLServer

#21 Updated by Toshi MARUYAMA about 2 years ago

Note-20 is wrong because it uses nested transaction and parent transaction does not use lock.
This is fix.

diff --git a/lib/redmine/nested_set/issue_nested_set.rb b/lib/redmine/nested_set/issue_nested_set.rb
--- a/lib/redmine/nested_set/issue_nested_set.rb
+++ b/lib/redmine/nested_set/issue_nested_set.rb
@@ -148,7 +148,29 @@ module Redmine
         new_record? || !is_or_is_ancestor_of?(issue)
       end

+      def get_lock_mysql(&block)
+        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
+
       def lock_nested_set
+        if self.class.connection.adapter_name =~ /mysql/i
+          get_lock_mysql { get_lock }
+        else
+          get_lock
+        end
+      end
+
+      def get_lock
         if self.class.connection.adapter_name =~ /sqlserver/i
           lock = "WITH (ROWLOCK HOLDLOCK UPDLOCK)" 
           # Custom lock for SQLServer

#22 Updated by Jean-Philippe Lang about 2 years ago

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.

#23 Updated by Toshi MARUYAMA about 2 years ago

I think MySQL uses Gap Locks, so we cannot avoid deadlock.
http://dev.mysql.com/doc/refman/5.6/en/innodb-record-level-locks.html#idm140169015854080

I tried READ COMMITTED on MariaDB 5.5, but deadlock raised. I don't know the reason.

#24 Updated by Dave Martin about 1 month ago

Do current versions of Redmine still not support MySQL 5.7?

#25 Updated by Toshi MARUYAMA 3 days ago

#23318#note-18 patch reduces test failure times from about 100% to 50% on my CentOS7 mariadb-5.5.52-1.el7.x86_64.

Also available in: Atom PDF