Mysql2::错误:BLOB、TEXT、GEOMETRY或JSON列'body'可以't具有默

Question

我有迁移：

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数据库：迁移显示错误：

Mysql2:：错误：BLOB、TEXT、GEOMETRY或JSON列"body"不能有默认值：ALTER TABLE blog_posts CHANGE body body TEXT default"NOT NULL/home/user/projects/projectname/db/migrate/2012508203410_CHANGE_default_value_body_for_blog_posts.rb:3:在"up"中

我使用ubuntu 16.04，

mysql服务器5.7（版本：5.7.12-0ubuntu1.1版本：5.7.12-0ubuntu1版本：5.7.11-0ubuntu6)

我在谷歌上看了看，解决这个问题需要更改

sql mode="STRICT_TRANS_TABLES，NO_AUTO_CREATE _USER，NO_ENGINE_SUBSTITION"

至

sql-mode="NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"

但是my.conf文件为空。

#
# 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/

如何解决这个问题，请帮忙。非常感谢。

Answer 1

您需要找到正确的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

我基于编辑了/etc/mysql/mysql.conf.d/mysqld.cnf

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)

找到正确的文件后，在[mysqld]语句下方添加sql mode="NO_AUTO_REATE_USER，NO_ENGINE_SUBSTITUTION"

我找到了解决方案：需要添加my.cnf

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

从MySQL 8.0.13开始，现在可以为TEXT/BLOB字段设置默认值。但是，文字默认值需要指定为表达式。

例如，这是有效的：

`title` text COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ('Title')

但这会产生一个错误：

`title` text COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'Title'

我在本地机器上使用xampp中的mysql迁移时没有遇到任何问题。只有在生产服务器上它才开始出现故障。我发现xampp运行的是mariadb，而不是普通的mysql。所以在服务器上使用mariaDB解决了我的问题。

再次检查您的sql代码，上面清楚地指出"body"不是默认值
示例：

 `completed` text NOT NULL  DEFAULT 'false',

删除后默认值

`completed` text NOT NULL,

现在希望你的错误已经过去了。对不起我的英语。

我通过这种方式解决了这个问题：我曾经想在服务器上导出mySql数据库，然后将其导入另一台服务器（CPanel主机）。每次我尝试时，phpMyAdmin都会在其中一个数据库表的一个字段上给出这个错误。然后我转到我的源主机；打开phpMyAdmin并打开该表结构并删除该字段的默认值。然后我再次出口，这次成功了。