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.