Patch #27283

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

Added by Akiko Takano 4 months ago. Updated 23 days ago.

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

0%

Category:Database
Target version:-

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 4 months 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 4 months ago

  • Description updated (diff)

#3 Updated by Nishant Karki 3 months 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 3 months 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 3 months 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 2 months 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.

#7 Updated by Toshi MARUYAMA about 1 month ago

Ref: https://github.com/rails/rails/issues/23418

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
@@ -1,12 +1,13 @@
-class CreateEmailAddresses < ActiveRecord::Migration[4.2]
+class CreateEmailAddresses < ActiveRecord::Migration[5.0]
   def change
     create_table :email_addresses do |t|
       t.column :user_id, :integer, :null => false
       t.column :address, :string, :null => false
       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.timestamps
     end
+    rename_column :email_addresses, :created_at, :created_on
+    rename_column :email_addresses, :updated_at, :updated_on
   end
 end

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 redmine.email_addresses;
+------------+--------------+------+-----+---------+----------------+
| Field      | Type         | Null | Key | Default | Extra          |
+------------+--------------+------+-----+---------+----------------+
| id         | int(11)      | NO   | PRI | NULL    | auto_increment |
| user_id    | int(11)      | NO   |     | 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)

#8 Updated by Toshi MARUYAMA about 1 month ago

  • Target version deleted (4.0.0)

Also available in: Atom PDF