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

Answer 2

The big issue with the ActiveRecord migration 5.1 is that now the id are expected to be BIGINT instead of INT, so when you adding a column referring another table created before rails 5.1 it expect the column type to be BIGINT but instead is just an INT, hence the error. The best solution is just modify your migration and change the type of the column to int.

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

that should work.

Answer 3

I figured out a work around, but it is still very puzzling to me.

The error message in the log file was not exactly pointing to the issue. For some reason, it might be rails 5.1.1 or it might be mysql2 0.4.6, but it doesn't like using references within the create_table block for some reason. Very odd because it has worked for me in the past.

So I changed the migration from this:

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

To this:

class CreateTableSomeTable < ActiveRecord::Migration[5.1]
  def change
    create_table :some_tables do |t|
      t.integer :user_id
      t.integer :author_id
      t.text :summary
    end
  end
end

And it worked.

It is very odd because references works just fine with sqlite3 (I tested this by generating a dummy app, ran a scaffold command with a references column, and ran rails db:migrate and it all worked).