ClearDB on Heroku throws an error when running a migration to create a new table that has a `details` field of type `json`

Question

I am running a Rails 5.2.2 and ruby 2.6.3 app on Heroku backed by ClearDB database.

NOTE: Locally this all works fine

my gem file has the mysql2 version as such: gem 'mysql2', '>= 0.4.4', '< 0.6.0'

When I attempt to run a migration to create a new table that has a details field of type json it fails with a syntax error.

Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json'

Removing the details field of type json and re-running the migration succeeeds.

Here is an example of the contents of the migration.

class InitTables < ActiveRecord::Migration[5.2]
  def change
    create_table :services do |t|
      t.string :public_id, null: false, index: { unique: true }
      t.json :details
    end
  end
end

This is the error message: Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'json'

Answer 1

I faced same issues with ClearDB (Ignite plan). Switched to JAWS_DB (free plan) and migration was run successfully.