Rails4でDBにjsonのカラムを追加することができない

Question

rails4でDBにjsonのカラムを追加しようと考えています。しかし、bundle exec rake db:migrateを実行しようとすると、エラーが出てしまい、それ以降進まなくなっています。もし、わかる方がいらしたら、嬉しいです。

MYSQLのバージョン
mysql Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using EditLine wrapper

問題
== 20190123214656 AddPlansToImages: migrating =================================
-- add_column(:images, :plans, :json)
rake aborted!
StandardError: An error has occurred, all later migrations canceled:

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' at line 1: ALTER TABLE images ADD plans json
/Users/hiroshi/Desktop/sunny/db/migrate/20190123214656_add_plans_to_images.rb:3:in `change'

Caused by:
ActiveRecord::StatementInvalid: 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' at line 1: ALTER TABLE images ADD plans json
/Users/hiroshi/Desktop/sunny/db/migrate/20190123214656_add_plans_to_images.rb:3:in `change'

Caused by:
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' at line 1
/Users/hiroshi/Desktop/sunny/db/migrate/20190123214656_add_plans_to_images.rb:3:in `change'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

$ rails generate migration AddPlansToImages plans:json

$ rake db:migrate

source 'https://rubygems.org'


# Bundle edge Rails instead: gem 'rails', github: 'rails/rails'
gem 'rails', '4.2.8'
# Use mysql as the database for Active Record
gem 'mysql2', '0.3.18'
# Use SCSS for stylesheets
gem 'sass-rails', '~> 4.0.2'
# Use Uglifier as compressor for JavaScript assets
gem 'uglifier', '>= 1.3.0'
# Use CoffeeScript for .coffee assets and views
gem 'coffee-rails', '~> 4.1.0'
# See https://github.com/rails/execjs#readme for more supported runtimes
# gem 'therubyracer', platforms: :ruby

# Use jquery as the JavaScript library
gem 'jquery-rails'
# Turbolinks makes following links in your web application faster. Read more: https://github.com/rails/turbolinks
gem 'turbolinks'
# Build JSON APIs with ease. Read more: https://github.com/rails/jbuilder
gem 'jbuilder', '~> 2.0'
# bundle exec rake doc:rails generates the API under doc/api.
gem 'sdoc', '~> 0.4.0', group: :doc

# Use ActiveModel has_secure_password
# gem 'bcrypt', '~> 3.1.7'

# Use Unicorn as the app server
# gem 'unicorn'

# Use Capistrano for deployment
# gem 'capistrano-rails', group: :development

group :development, :test do
  # Call 'byebug' anywhere in the code to stop execution and get a debugger console
  gem 'byebug'
end

group :development do
  # Access an IRB console on exception pages or by using <%= console %> in views
  gem 'web-console', '~> 2.0'

  # Spring speeds up development by keeping your application running in the background. Read more: https://github.com/rails/spring
  gem 'spring'
end

gem 'pry-rails'
gem 'compass-rails','~> 2.0'
gem 'sprockets', '2.11.0'
#pagenation
gem 'kaminari'
#login_logout
gem 'devise'
gem 'paperclip'

#chat
gem 'websocket-rails'
gem 'faye-websocket', '0.10.0'
#image
gem 'carrierwave'
gem 'rmagick'
#date
gem 'momentjs-rails'
gem 'bootstrap3-datetimepicker-rails'

class AddPlansToImages < ActiveRecord::Migration
  def change
    add_column :images, :plans, :json
  end
end

Answer 1

You have an error in your SQL syntaxと返しているということは、MySQLサーバがJSON型を処理できていないものと思われます。

JSON型へのMySQLの対応は、5.7.8以上です。MariaDBでは、10.2.7以上でJSONをTEXTに読み替える措置が入っています。

Comments

[avicii]

mysqlは、mysql Ver 14.14 Distrib 5.7.19, for osx10.12 (x86_64) using EditLine wrapperでした。

[avicii]

どうやってアップグレードすることが可能ですか？(mysql8に上げなければできないでしょうか？）

[maisumakun]

となると、この線は違いそうですね（5.7.19は5.7.8より新しいです）。

[avicii]

そうなんですね！mysql2の方を新しくした方がいいですかね？

Answer 2

手元で試してはいませんが
mysql2のバージョンが古くmysqlのjson対応前のバージョンのようなので
gemをupdateして試して見ましょう。

Comments

[avicii]

mysql2のバージョンは〇〇以上必要ですか？