ActiveRecord::StatementInvalid: Mysql2::Error: Error on rename of

Question

Please skip and jump directly to my update 4 below. I have summarised the problem in a better way.

I'm using mysql (not sure if I'm using mariadb)

When I do a rails db:migrate, I got this error:

ActiveRecord::StatementInvalid: Mysql2::Error: Error on rename of
'./my_database_name/#sql-228c_e' to './my_database_name/apples' (errno: 150): ALTER TABLE
`apples` CHANGE `gardener_id` `farmer_id` bigint(20) DEFAULT NULL

I think I got this error because of this file:

# db/migrate/20171017044807_rename_gardeners_to_farmers.rb
class RenamegardenersToFarmers < ActiveRecord::Migration[5.1]
  def change
    rename_table :gardeners, :farmers
  end
end

My app works fine and rails db:migrate work fine after I removed the rename_table line above and go through all my migration files and change all gardeners word to farmers word. So I think the problem is indeed about renaming.

My question is why is it a problem and how do I fix it?

Update 1

Sorry, I just rerun the migration files until # db/migrate/20171017044807_rename_gardeners_to_farmers.rb (inclusive) and actually this file does not cause problem. It's the next migration file that cause problem.

The schema just after running migration file db/migrate/20171017044807_rename_gardeners_to_farmers.rb:

  create_table "apples", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.bigint "gardener_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["gardener_id"], name: "index_apples_on_gardener_id"
  end
  add_foreign_key "apples", "farmers", column: "gardener_id"

The next migration file that cause the above error:

# 20171017051611_rename_gardener_id_column_for_apples.rb
class RenameGardenerIdColumnForApples < ActiveRecord::Migration[5.1]
  def change
    rename_column :apples, :gardener_id, :farmer_id
  end
end

Update 2 Trying to solve the problem after hearing Ngoral's answer, I tried to add a migration just before the failed one with the content: change_column :apples, :gardener_id, :references, null: false but it doesn't work.

By 'does not work', I mean I see migration failing error when I try to run rails db:migrate after adding the new migration file that contains the line I show on the first comment to your answer.

The error is this:

rails db:migrate
== 20171017044808 PreventGardenerIdNull: migrating ==============================
-- change_column(:apples, :gardener_id, :references, {:null=>false})
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'references NOT NULL' at line 1: ALTER TABLE `apples` CHANGE `gardener_id` `gardener_id` references NOT NULL

Update 3

I think the error in update 2 shows me that there is somehting wrong with the code I wrote, so I change it from change_column :apples, :gardener_id, :references, null: false to change_column_null :replies, :author_id, false The reason I did this is because I saw it in Ngoral's comment below (to her won answer) and this Stack Overflow post: https://stackoverflow.com/a/20255645/6359753

Now after I re-run rails db:migrate, I got this:

rails db:migrate
== 20171017044808 PreventGardenerIdNull: migrating <--------this is the migration file I added in Update 2==============================
-- change_column_null(:apples, :gardener_id, false)
   -> 0.0068s
== 20171017044808 PreventGardenerIdNull: migrated (0.0069s) =====================

== 20171017051611 RenameGardenerIdColumnForApples: migrating ===================
-- rename_column(:apples, :gardener_id, :farmer_id)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Error on rename of './my_database_name/#sql-71a_3' to './my_database_name/apples' (errno: 150): ALTER TABLE `apples` CHANGE `gardener_id` `farmer_id` bigint(20) NOT NULL

From what I can see, it seems like the problem is not to do with gardener_id can be null because the migration file PreventGardenerIdNull succeed and RenameGardenerIdColumnForApples failed.

Note: I think I somehow violate my foreign key constraint, according to this post: https://stackoverflow.com/a/4061333/6359753, but I'm still figuring out what exactly went wrong. Note: mysql -u root shows that my mariaDB server version is: Server version: 5.5.56-MariaDB MariaDB Server

Update 4: I think after all the investigations and the answer here, I can summarise the question a bit better now:

1) Ignore everything in this question post above

2) mysql -u root shows that my mariaDB server version is: Server version: 5.5.56-MariaDB MariaDB Server

3) Below are displayed in the order of time:

[Time = 1] Schema 1:

  create_table "gardeners", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "apples", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.bigint "gardener_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["gardener_id"], name: "index_apples_on_gardener_id"
  end
  add_foreign_key "apples", "gardeners"

[Time = 2] Run migration with code:

rename_table :gardeners, :farmers

[Time = 3] Schema 2:

  create_table "farmers", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "apples", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.bigint "gardener_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["gardener_id"], name: "index_apples_on_gardener_id"
  end
  add_foreign_key "apples", "farmers", column: "gardener_id"

[Time = 4] Run migration with code:

rename_column :apples, :gardener_id, :farmer_id

Got error:

== 20171017051611 RenameGardenerIdColumnForApples: migrating ===================
-- rename_column(:apples, :gardener_id, :farmer_id)
rails aborted!
StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Error on rename of './my_database_name/#sql-71a_3' to './my_database_name/apples' (errno: 150): ALTER TABLE `apples` CHANGE `gardener_id` `farmer_id` bigint(20) NOT NULL

[Time = 5] (never happen) the schema I want to change to:

  create_table "farmers", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.string "name"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

  create_table "apples", force: :cascade, options: "ENGINE=InnoDB DEFAULT CHARSET=utf8" do |t|
    t.bigint "gardener_id"
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
    t.index ["gardener_id"], name: "index_apples_on_gardener_id"
  end
  add_foreign_key "apples", "gardeners"

I have 2 questions:

1) Why do I fail at [time = 4]

2) What migration code should I have used at [time = 4], or, what migration code should I have used at [time = 2] to prevent all these problems?

Thank you very much!

Please, provide a schema for `apples` tables. The problem is there, in `gardeners_id`. — Ngoral, Feb 26 '18 at 13:36
Sorry, I rerun the migration files until `# db/migrate/20171017044807_rename_gardeners_to_farmers.rb ` (inclusive) and actually this file does not cause problem. It's the next migration file that cause problem — Henry Yang, Mar 01 '18 at 00:47
Sorry, I just rerun the migration files until # db/migrate/20171017044807_rename_gardeners_to_farmers.rb (inclusive) and actually this file does not cause problem. It's the next migration file that cause the above error I mentioned — Henry Yang, Mar 01 '18 at 00:55
Could `gardener_id` be `nil`? Not by your schema (I see it), but by logic and reality — Ngoral, Mar 01 '18 at 13:34

score 1 · Accepted Answer · answered Nov 28 '19 at 03:27

1

I was using MariaDB version 5.5.64. I changed to using MariaDB version 10.4 and this issue for migration goes away.

answered Nov 28 '19 at 03:27

Henry Yang

2,283
3
21
38

Ngoral · Answer 2 · 2018-03-12T22:15:34.167

0

Your problem is covered in this question.

The problem is caused by DEFAULT NULL statement which is called by migration and can clearly be seen in your SQL dump. Such statement is incorrect for InnoDB which you using (it is seen from the schema).

So, I would guess you do not want gardener_id to be nil. In this case, creating a migration that sets null: false for gardener_id should help.

So, you first create a migration not to allow gardener_id to be nil and then put your migration that fails now.

============ Answering your questions after update: Your migration fails at step 4 with the reason I stated above: you have a null default value for gardener_id and this is not allowed by your DB.

You should run a migration with a command change_column_null :apples, :gardener_id, false before renaming. And after changing the default null, run your renaming migration. The command you run has invalid syntax and it clearly tells you so.

edited Mar 12 '18 at 22:15

answered Mar 01 '18 at 13:54

Ngoral

4,215
2
20
37

Do you know how do I set it to not null in a migration? I try to add a migration just before the failed one with the content: `change_column :apples, :gardener_id, :references, null: false` but it doesn't work. Or am I missing something here? – Henry Yang Mar 06 '18 at 02:03
What do you mean saying 'does not work'? It does not changes null to false or does not help to solve your problem? If first, try a `change_column_null :apples, :gardener_id, false` – Ngoral Mar 06 '18 at 10:33
Try the command I gave: `change_column_null`, the syntax you use in your command is incorrect. `:references` is not needed there. – Ngoral Mar 12 '18 at 22:10
I think that's what I did in update 3, can you please have a look again? If I made some mistake in my code, please tell me – Henry Yang Mar 12 '18 at 22:48

ActiveRecord::StatementInvalid: Mysql2::Error: Error on rename of

2 Answers2