title: ActiveRecord::StatementInvalid: Mysql2::Error: Error on rename of
body: <p><strong>Please skip and jump directly to my update 4 below. I have summarised the problem in a better way.</strong></p>
  
  <p>I'm using mysql (not sure if I'm using mariadb)</p>
  
  <p>When I do a rails db:migrate, I got this error:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>I think I got this error because of this file:</p>
  
  <pre><code># db/migrate/20171017044807_rename_gardeners_to_farmers.rb
  class RenamegardenersToFarmers &lt; ActiveRecord::Migration[5.1]
    def change
      rename_table :gardeners, :farmers
    end
  end
  </code></pre>
  
  <p>My app works fine and <code>rails db:migrate</code> work fine after I removed the <code>rename_table</code> line above and go through all my migration files and change all <code>gardeners</code> word to <code>farmers</code> word. So I think the problem is indeed about renaming.</p>
  
  <p>My question is why is it a problem and how do I fix it?</p>
  
  <p><strong>Update 1</strong></p>
  
  <p>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.</p>
  
  <p>The schema just after running migration file <code>db/migrate/20171017044807_rename_gardeners_to_farmers.rb</code>:</p>
  
  <pre><code>  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"
  </code></pre>
  
  <p>The next migration file that cause the above error:</p>
  
  <pre><code># 20171017051611_rename_gardener_id_column_for_apples.rb
  class RenameGardenerIdColumnForApples &lt; ActiveRecord::Migration[5.1]
    def change
      rename_column :apples, :gardener_id, :farmer_id
    end
  end
  </code></pre>
  
  <p><strong>Update 2</strong>
  Trying to solve the problem after hearing Ngoral's answer, I tried to add a migration just before the failed one with the content: <code>change_column :apples, :gardener_id, :references, null: false</code> but it doesn't work.</p>
  
  <p>By 'does not work', I mean I see migration failing error when I try to run <code>rails db:migrate</code> after adding the new migration file that contains the line I show on the first comment to your answer.</p>
  
  <p>The error is this:</p>
  
  <pre><code>rails db:migrate
  == 20171017044808 PreventGardenerIdNull: migrating ==============================
  -- change_column(:apples, :gardener_id, :references, {:null=&gt;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
  </code></pre>
  
  <p><strong>Update 3</strong></p>
  
  <p>I think the error in update 2 shows me that there is somehting wrong with the code I wrote, so I change it from
  <code>change_column :apples, :gardener_id, :references, null: false</code>
  to
  <code>change_column_null :replies, :author_id, false</code>
  The reason I did this is because I saw it in Ngoral's comment below (to her won answer) and this Stack Overflow post: <a href="https://stackoverflow.com/a/20255645/6359753">https://stackoverflow.com/a/20255645/6359753</a></p>
  
  <p>Now after I re-run <code>rails db:migrate</code>, I got this: </p>
  
  <pre><code>rails db:migrate
  == 20171017044808 PreventGardenerIdNull: migrating &lt;--------this is the migration file I added in Update 2==============================
  -- change_column_null(:apples, :gardener_id, false)
     -&gt; 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
  </code></pre>
  
  <p>From what I can see, it seems like the problem is not to do with gardener_id can be null because the migration file <code>PreventGardenerIdNull</code> succeed and <code>RenameGardenerIdColumnForApples</code> failed.</p>
  
  <p>Note: I think I somehow violate my foreign key constraint, according to this post: <a href="https://stackoverflow.com/a/4061333/6359753">https://stackoverflow.com/a/4061333/6359753</a>, but I'm still figuring out what exactly went wrong.
  Note: <code>mysql -u root</code> shows that my mariaDB server version is: <code>Server version: 5.5.56-MariaDB MariaDB Server</code></p>
  
  <p><strong>Update 4:</strong>
  I think after all the investigations and the answer here, I can summarise the question a bit better now: </p>
  
  <p>1) Ignore everything in this question post above</p>
  
  <p>2) <code>mysql -u root</code> shows that my mariaDB server version is: <code>Server version: 5.5.56-MariaDB MariaDB Server</code></p>
  
  <p>3) Below are displayed in the order of time:</p>
  
  <p>[Time = 1] Schema 1:</p>
  
  <pre><code>  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"
  </code></pre>
  
  <p>[Time = 2] Run migration with code:</p>
  
  <pre><code>rename_table :gardeners, :farmers
  </code></pre>
  
  <p>[Time = 3] Schema 2:</p>
  
  <pre><code>  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"
  </code></pre>
  
  <p>[Time = 4] Run migration with code:</p>
  
  <pre><code>rename_column :apples, :gardener_id, :farmer_id
  </code></pre>
  
  <p>Got error:</p>
  
  <pre><code>== 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
  </code></pre>
  
  <p>[Time = 5] (never happen) the schema I want to change to:</p>
  
  <pre><code>  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"
  </code></pre>
  
  <p>I have 2 questions:</p>
  
  <p>1) Why do I fail at [time = 4]</p>
  
  <p>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?</p>
  
  <p>Thank you very much!</p>