Mysql2::Error: BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value

Question

I have migration:

lass ChangeDefaultValueBodyForBlogPosts < ActiveRecord::Migration
  def up
    change_column :blog_posts, :body, :text, :null => false
    change_column :comments, :text, :text, :null => false
  end

  def down
    change_column :comments, :text, :text, :default => nil
    change_column :blog_posts, :body, :text, :default => nil
  end
end

rake db:migrate display error:

Mysql2::Error: BLOB, TEXT, GEOMETRY or JSON column 'body' can't have a default value: ALTER TABLE blog_posts CHANGE body body text DEFAULT '' NOT NULL/home/user/projects/projectname/db/migrate/20120508203410_change_default_value_body_for_blog_posts.rb:3:in `up'

I use ubuntu 16.04,

mysql server 5.7(Version: 5.7.12-0ubuntu1.1 Version: 5.7.12-0ubuntu1 Version: 5.7.11-0ubuntu6 )

I looked in google, solving this issue need change

sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

to

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

But my.conf file is empty.

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

How solve this issue, please help. Thank you.

Answer 1

You need to find the correct my.cnf:

sudo find / -name "*.cnf"

/etc/mysql/mysql.conf.d/mysqld.cnf
/etc/mysql/my.cnf
/etc/mysql/mysql.cnf
/etc/mysql/conf.d/mysqldump.cnf
/etc/mysql/conf.d/mysql.cnf

I edited /etc/mysql/mysql.conf.d/mysqld.cnf based on

strace mysql ";" 2>&1  | grep cnf

stat("/etc/my.cnf", 0x7ffda9472660)     = -1 ENOENT (No such file or directory)
stat("/etc/mysql/my.cnf", {st_mode=S_IFREG|0644, st_size=683, ...}) = 0
open("/etc/mysql/my.cnf", O_RDONLY)     = 3
stat("/etc/mysql/conf.d/mysql.cnf", {st_mode=S_IFREG|0644, st_size=8, ...}) = 0
open("/etc/mysql/conf.d/mysql.cnf", O_RDONLY) = 4
stat("/etc/mysql/conf.d/mysqldump.cnf", {st_mode=S_IFREG|0644, st_size=55, ...}) = 0
open("/etc/mysql/conf.d/mysqldump.cnf", O_RDONLY) = 4
stat("/etc/mysql/mysql.conf.d/mysqld.cnf", {st_mode=S_IFREG|0644, st_size=3034, ...}) = 0
open("/etc/mysql/mysql.conf.d/mysqld.cnf", O_RDONLY) = 4
stat("/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf", {st_mode=S_IFREG|0644, st_size=21, ...}) = 0
open("/etc/mysql/mysql.conf.d/mysqld_safe_syslog.cnf", O_RDONLY) = 4
stat("/root/.my.cnf", 0x7ffda9472660)   = -1 ENOENT (No such file or directory)
stat("/root/.mylogin.cnf", 0x7ffda9472660) = -1 ENOENT (No such file or directory)

Once you find the correct file add sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION" below [mysqld] statement

Answer 2

I found solution: Need add in my.cnf

[mysqld]
sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"