Patch #27283

Failed to run “rake db:migrate" command against clean database on MySQL5.7 under Redmine4.0 / Rails5

Added by Akiko Takano about 1 month ago. Updated 4 days ago.

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

0%

Category:Database
Target version:4.0.0

Description

I’m not sure if this is a defect or not, but I hope to feedback this situation.

Summary

Failed to run “rake db:migrate" command against clean database on MySQL5.7 under Redmine4.0 / Rails5.

Description

I always fail to migrate against clean (blank) database on MySQL 5.7 under Redmine4.0 (trunk).

Environment

- MySQL5.7 / Default setup, without any custom configurations
- Redmine trunk (SVN: trunk, Mercurial: default, GitHib: master branch)
- Without plugins
- Database and Redmine under Docker container and VirtualBox VM CentOS7

Visual Proof

Here is an excerpt from the log.

== 20150113194759 CreateEmailAddresses: migrating =============================
-- adapter_name()
   -> 0.0005s
-- adapter_name()
   -> 0.0003s
-- create_table(:email_addresses, {:id=>:integer})
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Invalid default value for 'updated_on': 

.......

ActiveRecord::StatementInvalid: Mysql2::Error: Invalid default value for ‘updated_on’

Steps to reproduce

  1. Install Redmine and MySQL like above. (Especially, run MySQL5.7 default with settings)
  2. Create database.yml for mysql.
  3. Run migration task: rake db:create && rake db:migrate

At first, migration process proceeds normally, but an error occurs in the middle, at "20150113194759 CreateEmailAddresses”.

Expected Results & Actual Results

  • Expected: Migration completed successfully
  • Result: Failed

Workaround

As error message says, this may caused, "Changed the default null value for timestamps to false".
Ref. http://guides.rubyonrails.org/5_0_release_notes.html

A. Change migration file

diff --git a/db/migrate/20150113194759_create_email_addresses.rb b/db/migrate/20150113194759_create_email_addresses.rb
index 22ad19e..fd49722 100644
--- a/db/migrate/20150113194759_create_email_addresses.rb
+++ b/db/migrate/20150113194759_create_email_addresses.rb
@@ -6,7 +6,7 @@ class CreateEmailAddresses < ActiveRecord::Migration[4.2]
       t.column :is_default, :boolean, :null => false, :default => false
       t.column :notify, :boolean, :null => false, :default => true
       t.column :created_on, :timestamp, :null => false
-      t.column :updated_on, :timestamp, :null => false
+      t.column :updated_on, :timestamp
     end
   end
 end

If applied above, the result of “show create table email_addresses;” is following:

| email_addresses | CREATE TABLE `email_addresses` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `address` varchar(255) NOT NULL,
  `is_default` tinyint(1) NOT NULL DEFAULT '0',
  `notify` tinyint(1) NOT NULL DEFAULT '1',
  `created_on` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `updated_on` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_email_addresses_on_user_id` (`user_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4 |

After migration finished, I confirmed EmailAddress model’s behavior.
(Exp. e = EmailAddress.create(..), e.update(address: xxxxx))
It seems works correctly.

B. Change MySQL's sql-mode

Change MySQL sql-mode to “”, or remove “STRICT_ALL_TABLES” statement from sql-mode.
If applied above, migration completed without any change to migration files.

But I’m not sure which is the best way because I have not try some other database engines...


My report is as above.

I hope this would be any help.

migration-failed.jpg (125 KB) Akiko Takano, 2017-10-24 18:09

migration-error-trace.txt Magnifier (14.6 KB) Akiko Takano, 2017-10-24 18:09

Screen Shot 2017-11-15 at 5.50.06 PM.png (350 KB) Nishant Karki, 2017-11-15 13:06

History

#1 Updated by Akiko Takano about 1 month ago

Upload capture and error log.

Screenshot of migration error

Current Env

sh-4.3# rails about
About your application's environment
Rails version             5.1.2
Ruby version              2.4.1-p111 (x86_64-linux)
RubyGems version          2.6.11
Rack version              2.0.3
Middleware                Rack::Sendfile, Rack::ContentLength, ActionDispatch::Static, ActionDispatch::Executor, ActiveSupport::Cache::Strategy::LocalCache::Middleware, Rack::Runtime, Rack::MethodOverride, ActionDispatch::RequestId, ActionDispatch::RemoteIp, Rails::Rack::Logger, ActionDispatch::ShowExceptions, ActionDispatch::DebugExceptions, ActionDispatch::Reloader, ActionDispatch::Callbacks, ActionDispatch::Cookies, ActionDispatch::Session::CookieStore, ActionDispatch::Flash, Rack::Head, Rack::ConditionalGet, Rack::ETag, RequestStore::Middleware, Module
Application root          /tmp/redmine
Environment               development
Database adapter          mysql2
Database schema version   20170723112801

sh-4.3# git branch
* master
sh-4.3# git log 
commit fc1912442fed541c8d3a207ce71bbeb9569a32d1
Author: Jean-Philippe Lang <jp_lang@......>
Date:   Sun Oct 15 19:50:46 2017 +0000

    Adds changes from 3.3.5.

    git-svn-id: http://svn.redmine.org/redmine/trunk@17004 e93f8b46-1217-0410-a6f0-8f06a7374b81

#2 Updated by Toshi MARUYAMA about 1 month ago

  • Description updated (diff)

#3 Updated by Nishant Karki 29 days ago

I'm seeing this issue with both master branch and 3.4-stable on github

Current Env: OSX
$ rails about
About your application's environment
Rails version 5.1.2
Ruby version 2.3.0-p0 (x86_64-darwin16)
RubyGems version 2.5.1
Rack version 2.0.3
Middleware Rack::Sendfile, Rack::ContentLength, ActionDispatch::Static, ActionDispatch::Executor, ActiveSupport::Cache::Strategy::LocalCache::Middleware, Rack::Runtime, Rack::MethodOverride, ActionDispatch::RequestId, ActionDispatch::RemoteIp, Rails::Rack::Logger, ActionDispatch::ShowExceptions, ActionDispatch::DebugExceptions, ActionDispatch::Reloader, ActionDispatch::Callbacks, ActionDispatch::Cookies, ActionDispatch::Session::CookieStore, ActionDispatch::Flash, Rack::Head, Rack::ConditionalGet, Rack::ETag, RequestStore::Middleware, Module
Application root /Users/nishant/projects/ruby/redmine
Environment development
Database adapter mysql2
Database schema version 0

#4 Updated by Toshi MARUYAMA 21 days ago

  • Category set to Database
  • Target version set to 4.0.0

I tried this.

diff --git a/db/migrate/20150113194759_create_email_addresses.rb b/db/migrate/20150113194759_create_email_addresses.rb
--- a/db/migrate/20150113194759_create_email_addresses.rb
+++ b/db/migrate/20150113194759_create_email_addresses.rb
@@ -6,7 +6,7 @@ class CreateEmailAddresses < ActiveRecor
       t.column :is_default, :boolean, :null => false, :default => false
       t.column :notify, :boolean, :null => false, :default => true
       t.column :created_on, :timestamp, :null => false
-      t.column :updated_on, :timestamp, :null => false
+      t.column :updated_on, :timestamp, :null => false, default: -> { 'NOW()' }
     end
   end
 end
mysql> desc email_addresses;
+------------+--------------+------+-----+-------------------+-----------------------------+
| Field      | Type         | Null | Key | Default           | Extra                       |
+------------+--------------+------+-----+-------------------+-----------------------------+
| id         | int(11)      | NO   | PRI | NULL              | auto_increment              |
| user_id    | int(11)      | NO   | MUL | NULL              |                             |
| address    | varchar(255) | NO   |     | NULL              |                             |
| is_default | tinyint(1)   | NO   |     | 0                 |                             |
| notify     | tinyint(1)   | NO   |     | 1                 |                             |
| created_on | timestamp    | NO   |     | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| updated_on | timestamp    | NO   |     | CURRENT_TIMESTAMP |                             |
+------------+--------------+------+-----+-------------------+-----------------------------+
7 rows in set (0.00 sec)

#5 Updated by Toshi MARUYAMA 21 days ago

Nishant Karki wrote:

I'm seeing this issue with both master branch and 3.4-stable on github

I cannot reproduce on 3.4-stable MySQL 5.7.

#6 Updated by Akiko Takano 4 days ago

MARUYAMA-San,

Thank you for handling this report.
I’ve tried to run migration task against MySQL 5.7, and completed successfully.

-      t.column :updated_on, :timestamp, :null => false
+      t.column :updated_on, :timestamp, :null => false, default: -> { 'NOW()' }

But, it seems there are some difference from above table definition.
Here is my result, as FYI.

Please note: No customize against SQL mode. (I used MySQL official Docker image)

mysql> SELECT @@GLOBAL.sql_mode;
+-------------------------------------------------------------------------------------------------------------------------------------------+
| @@GLOBAL.sql_mode                                                                                                                         |
+-------------------------------------------------------------------------------------------------------------------------------------------+
| ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION |
+-------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> SELECT @@VERSION;
+-----------+
| @@VERSION |
+-----------+
| 5.7.20    |
+-----------+
1 row in set (0.00 sec)

mysql> desc email_addresses;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | bigint(20)   | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   | MUL | NULL    |                |
| address    | varchar(255) | NO   |     | NULL    |                |
| is_default | tinyint(1)   | NO   |     | 0       |                |
| notify     | tinyint(1)   | NO   |     | 1       |                |
| created_on | datetime     | NO   |     | NULL    |                |
| updated_on | datetime     | NO   |     | NULL    |                |
+------------+--------------+------+-----+---------+----------------+
7 rows in set (0.00 sec)

On the other hand, I also tried to run migrate task against new SQLite database, and unfortunately, migration was failed.
This is because SQLite3 does not support 'NOW’ function.
(It seems both PostgreSQL and MySQL support this function.)

Maybe MSSQL also does not support NOW function.
I hope this will be of some help.

Also available in: Atom PDF