Migration to create table raises Mysql2::Error: Table doesn't exist

Question

I wrote a migration with the following:

class CreateTableSomeTable < ActiveRecord::Migration[5.1]
  def change
    create_table :some_tables do |t|
      t.references :user, foreign_key: true
      t.references :author, references: :user, foreign_key: true
      t.text :summary
    end
  end
end

It is a basic migration that is creating a database table. However: when I run rails db:migrate a very odd error message aborts the migration:

Mysql2::Error: Table 'my_database.some_tables' doesn't exist: SHOW FULL FIELDS FROM 'some_tables'

It is as if the error is saying it can't create the table because the table does exist, which doesn't make sense.

Things I have looked at and tried:

• reviewed the database.yml which seems fine. Nothing has changed, and I have recently run other migrations just fine (though no migrations that created database tables)
• ran bundle to ensure all gems were installed
• deleted the schema.rb file, recreated the database with data from another copy, and I ran rake db:schema:dump to recreate the schema.rb file. I attempted to run the migration again and still got the same error.

I am using rails 5.1.1 as well as mysql2 0.4.6

Any tips on how I can get the migration to run?

Answer 1

I got a similar error when trying to create a new model that has a reference to an existing model that was created before migrating to Rails 5.1.

Although the error message was not very clear about that, in my case it turned out that the problem was data type mismatch between the primary key of the old model and the foreign key of the new model (MySQL does not permit that). It was so because since Rails 5.1 the default data type of all the primary and foreign keys is bigint, but for the old model the primary key type was still integer.

I solved this by converting all the primary and foreign keys of the current models to bigint, so I can use the Rails new defaults and forget about it.

A workaround could also be specifying integer type for the new foreign keys so that they match the primary keys type of the old models. Something like the following:

class CreateUserImages < ActiveRecord::Migration[5.1]
  def change
    create_table :user_images do |t|
      t.references :user, type: :integer, foreign_key: true
      t.string :url
    end
  end
end