Project

General

Profile

Actions

Defect #27283

closed

Failed to run "rake db:migrate" command against clean database on MySQL 5.7

Added by Akiko Takano about 7 years ago. Updated almost 6 years ago.

Status:
Closed
Priority:
Normal
Category:
Database
Target version:
-
Start date:
Due date:
% Done:

0%

Estimated time:
Resolution:
Fixed
Affected 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.


Files

migration-failed.jpg (125 KB) migration-failed.jpg Akiko Takano, 2017-10-24 18:09
migration-error-trace.txt (14.6 KB) migration-error-trace.txt Akiko Takano, 2017-10-24 18:09
Screen Shot 2017-11-15 at 5.50.06 PM.png (350 KB) Screen Shot 2017-11-15 at 5.50.06 PM.png Nishant Karki, 2017-11-15 13:06
0001-fix-migration.patch (1.07 KB) 0001-fix-migration.patch Marius BĂLTEANU, 2018-06-26 09:25

Related issues

Related to Redmine - Feature #23630: Migrate to Rails 5.2ClosedJean-Philippe Lang

Actions
Actions

Also available in: Atom PDF