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

[email protected]:~/myapp/current/db$ rake db:drop
(in /home/myuser/myapp/current)
[email protected]:~/myapp/current/db$ rake db:create
(in /home/myuser/myapp/current)
[email protected]:~/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

[email protected]:~/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



[email protected]:~/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

[email protected]:~/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

On Tue, Apr 7, 2015 at 8:57 AM, Heptagone H [email protected]
wrote:

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.

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)

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,

Hassan S. ------------------------ [email protected]
http://about.me/hassanschroeder
twitter: @hassan
Consulting Availability : Silicon Valley or remote

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs