Rails migration issue with foreign key after moving from sqlite to mysql

Hi,

As much as I like Rails, I’m not very proficient at databases/schemas and migrations.

Right now the data inside the db does not matter as nothing has been populated yet, I’m just trying to get my app working.

I used scaffolding to generate my models, controllers, views etc. Originally I wrote the app to use sqlite3, but now that I want to move my app to a production environment with mysql or even postgres, I keep on getting an error involving foreign keys.

It appears there is a syntax issue somewhere with author_id inside of the article model

When I try to migrate to the database, this is the output I get

user@localhost:~/myapp/current/db$ rake db:drop

(in /home/myuser/myapp/current)

user@localhost:~/myapp/current/db$ rake db:create

(in /home/myuser/myapp/current)

user@localhost:~/myapp/current/db$ rake db:migrate

(in /home/myuser/myapp/current)

== 20150321171546 DeviseCreateUsers: migrating ================================

– create_table(:users)

-> 0.0080s

– add_index(:users, :email, {:unique=>true})

-> 0.0249s

– add_index(:users, :reset_password_token, {:unique=>true})

-> 0.0232s

== 20150321171546 DeviseCreateUsers: migrated (0.0571s) =======================

== 20150321171548 AddNameToUsers: migrating ===================================

– add_column(:users, :name, :string)

-> 0.0109s

== 20150321171548 AddNameToUsers: migrated (0.0114s) ==========================

== 20150321172618 CreateArticles: migrating ===================================

– create_table(:articles)

rake aborted!

StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Can’t create table ‘myapp_dev.#sql-50a3_a4’ (errno: 150): ALTER TABLE articles ADD CONSTRAINT fk_rails_e74ce85cbc

FOREIGN KEY (author_id)

REFERENCES authors (id)

/usr/local/rvm/gems/ruby-2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `query’

/usr/local/rvm/gems/ruby-2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_mysql_adapter.rb:299:in `block in execute’

/usr/local/rvm/gems/ruby-2.2.0/gems/activerecord-4.2.1/lib/active_record/connection_adapters/abstract_adapter.rb:473:in `block in log’

/usr/local/rvm/gems/ruby-2.2.0/gems/activesupport-4.2.1/lib/active_support/notifications/instrumenter.rb:20:in `instrument’

Here are what my migrations and schema.rb looks like

user@localhost:~/myapp/current/db/migrate$ cat 20150321215751_create_authors.rb

class CreateAuthors < ActiveRecord::Migration

def change

create_table :authors do |t|

t.string :name

t.string :hashtags

t.string :avatar

t.string :email

t.timestamps null: false

end

end

end

user@localhost:~/myapp/current/db/migrate$ cat 20150321172618_create_articles.rb

class CreateArticles < ActiveRecord::Migration

def change

create_table :articles do |t|

t.belongs_to :author, index: true, foreign_key: true

t.string :title

t.string :bannerurl

t.string :thumbnailurl

t.text :content

t.timestamps null: false

end

end

end

user@localhost:~/myapp/current/db$ cat schema.rb

ActiveRecord::Schema.define(version: 20150321171548) do

create_table “authors”, force: :cascade do |t|

t.string “name”

t.string “hashtags”

t.string “avatar”

t.string “email”

t.datetime “created_at”, null: false

t.datetime “updated_at”, null: false

end

create_table “articles”, force: :cascade do |t|

t.integer “author_id”, limit: 4

t.string “title”, limit: 255

t.string “bannerurl”, limit: 255

t.string “thumbnailurl”, limit: 255

t.text “content”, limit: 65535

t.datetime “created_at”, null: false

t.datetime “updated_at”, null: false

end

add_index “articles”, [“author_id”], name: “index_articles_on_author_id”, using: :btree

end

MySQL offers multiple table "engines"; make sure the default engine
isn't MyISAM, because that doesn't support foreign keys.

You can set your default to InnoDB, which does.

HTH,