Heroku, Rails: PG::SyntaxError

Question

When loading the schema in my Rails 5.1 application on heroku, the following exception gets raised:

ActiveRecord::StatementInvalid: PG::SyntaxError: ERROR: syntax error at or near "ENGINE" LINE 1: ...estamp NOT NULL, "updated_at" timestamp NOT NULL) ENGINE=Inn...

---

Details:

Trace

-- create_table("ads_dashboard_campaigns", {:force=>:cascade, :options=>"ENGINE=InnoDB DEFAULT CHARSET=utf8"})
   (5.0ms)  DROP TABLE IF EXISTS "ads_dashboard_campaigns" CASCADE
   (7.3ms)  CREATE TABLE "ads_dashboard_campaigns" ("id" bigserial primary key, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8
rails aborted!

config/database.yml

# # SQLite version 3.x
# #   gem install sqlite3-ruby (not necessary on OS X Leopard)
# development:
#   adapter: sqlite3
#   database: db/development.sqlite3
#   pool: 5
#   timeout: 5000

# # Warning: The database defined as "test" will be erased and
# # re-generated from your development database when you run "rake".
# # Do not set this db to the same as development or production.
# test:
#   adapter: sqlite3
#   database: db/test.sqlite3
#   pool: 5
#   timeout: 5000

production:
  adapter: sqlite3
  database: db/production.sqlite3
  pool: 5
  timeout: 5000


# Custom stuff
development:
  adapter: mysql2
  encoding: utf8
  pool: 5
  database: slooob_development
  username: root
  password: 0402Jonas
  port: 3306

test:
  adapter: mysql2
  encoding: utf8
  pool: 5
  database: slooob_test
  username: root
  password: 0402Jonas
  port: 3306

---

Sidenote: I am aware that Heroku uses a PostgreSQL database, but using the default settings for production has worked before setting the development and test databases to MySQL. I also tried to set the adapter to postgresql.

---

What have I done wrong?

Answer 1

Your database schema was generated on Mysql database and contains Mysql specific options. In your case it is ENGINE option. I am not sure if it was generated automatically or you added those options manually to migrations.

Try running migrations instead of loading the schema:

heroku run rake db:migrate

You can also use Mysql on Heroku. You need to add appropriate addon.

Answer 2

Have a look at your migrations - do they have this: options: "ENGINE=InnoDB DEFAULT CHARSET=utf8"? Removing these and pushing back up to Heroku might get around the problem.

---

More detail

This might be new with Rails 5, and makes it hard for the migrations to remain database-agnostic. The options are targeted at MySQL, and specify that the InnoDB storage engine should be used.

When you push up to Heroku, your database.yml is automatically updated to use Postgres. However, your migrations are untouched, and the engine parameters aren't valid for Postgres and cause an error.

This may also affect your schema.rb?