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'

score 4 · Answer 1 · answered Jul 26 '20 at 16:03

4

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

answered Jul 26 '20 at 16:03

Ashish

81
1
3
4

thank you so much for this, i was actually using laravel, these frustrated me for days – Arinzehills Jun 08 '22 at 20:39

score 3 · Answer 2 · answered May 28 '19 at 15:42

According to the ClearDB support, 5.7 version of MySQL is required to be compatible with my migration to use a field of json type.

The Heroku Ignite, Punch, Drift and Scream plans shared resource plans are currently only available on clusters running 5.5 or 5.6 (5.6 in Europe) versions of MySQL and I AM ON the Heroku Ignite plan.

To solve this issue I'll need a dedicated resource(instance) plans that can be configured to use 5.5, 5.6, or 5.7 version of MySQL.

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

2 Answers2