Mysql2::Error occured when execute migration datetime column with default option in Rails 7.0

[hotatekaoru]

Steps to reproduce

git clone git@github.com:hotatekaoru/rails_7_sample.git
cd rails_7_sample
bundle install
bin/rails db:create
bin/rails db:schema:load

Then the following error occurs.

rails aborted!
ActiveRecord::StatementInvalid: Mysql2::Error: Invalid default value for 'registered_at'
path_to_project/sample/db/schema.rb:15:in `block in <main>'
path_to_project/sample/db/schema.rb:13:in `<main>'

Caused by:
Mysql2::Error: Invalid default value for 'registered_at'
path_to_project/sample/db/schema.rb:15:in `block in <main>'
path_to_project/sample/db/schema.rb:13:in `<main>'
Tasks: TOP => db:schema:load
(See full trace by running task with --trace)

In Rails 7.0, Error occurs if you are using MySQL and there is a migration file with datetime columns with default option created in Rails 6.1 or earlier.

Also, if you execute migrate add_column or create_table with datetime columns with default option, an error will occur.

After the following PR merged, an error has occurred.
#42297

Expected behavior

not failure migration.

Actual behavior

Mysql2 error occurs.

System configuration

Rails version:
v7.0.0 alpha2
Ruby version:
v3.0.2

[intrip]

Please use

t.datetime :registered_at, default: -> { 'CURRENT_TIMESTAMP()' }, null: false

Instead

[mpg-kaoru-hotate]

@intrip Thanks for your reply.
I tried it and got the same error.
Mysql2::Error: Invalid default value for 'registered_at'

[intrip]

That's strange, I've tried and it worked. Which version of MySQL are you using? I've tested with 5.7

[mpg-kaoru-hotate]

@intrip Hmm... I got an error in both Mysql 5.7.12 and 8.0.26

Through trial and error, I found the following.
I add precision: nil to datetime column, db:migrate worked.
But I remove precision option or set precision: 6, db:migrate failed.

class CreateTestings < ActiveRecord::Migration[7.0]
  def change
    create_table :testings do |t|
      # add `precision: nil` , migration succeed
      t.datetime :registered_at, precision: nil, default: -> { 'CURRENT_TIMESTAMP()' }, null: false

      t.timestamps
    end
  end
end

[intrip]

@mpg-kaoru-hotate You can pass the desired precision as an argument to CURRENT_TIMESTAMP(). The important detail is that both values needs to match.
For example:

 t.datetime :registered_at, precision: 6, default: -> { 'CURRENT_TIMESTAMP(6)' }, null: false

#42297 changes the default precision to 6 so I assume you need to pass 6 if you're not explicitly passing a precision (didn't test it).

[hotatekaoru]

@intrip Thank you so much!
After fixing it, the migration passed 👍
I read your explanation, and I can understand it.

So, If I made a migration file without setting precision with previous Rails version, I need to set precision to 6 to the migration file before upgrade rails version 7. Is that correct?

[intrip]

@intrip Thank you so much!
After fixing it, the migration passed 👍
I read your explanation, and I can understand it.

So, If I made a migration file without setting precision with previous Rails version, I need to set precision to 6 to the migration file before upgrade rails version 7. Is that correct?

Exactly, if you want to have a lower precision you can as well, as long as the CURRENT_TIMESTAMP(value) matches the precision

[hotatekaoru]

@intrip I understand. Thank you so much.