Defect #27283
Updated by Toshi MARUYAMA over 6 years ago
I’m not sure if this is a defect or not, but I hope to feedback this situation.
h2. Summary
Failed to run “rake db:migrate" command against clean database on MySQL5.7 under Redmine4.0 / Rails5.
h2. Description
I always fail to migrate against clean (blank) database on MySQL 5.7 under Redmine4.0 (trunk).
h2. 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
h2. Visual Proof
Here is an excerpt from the log.
<pre>
== 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’
</pre>
h2. Steps to reproduce
# Install Redmine and MySQL like above. (Especially, run MySQL5.7 default with settings)
# Create database.yml for mysql.
# 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”.
h2. Expected Results & Actual Results
* Expected: Migration completed successfully
* Result: Failed
h2. 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
h4. A. Change migration file
<pre><code class="diff"> <pre>
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
</code></pre> </pre>
If applied above, the result of “show create table email_addresses;” is following:
<pre><code class="sql"> <pre>
| 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 |
</code></pre> </pre>
After migration finished, I confirmed EmailAddress model’s behavior.
(Exp. e = EmailAddress.create(..), e.update(address: xxxxx))
It seems works correctly.
h4. 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.