Bug #98063 json NOT NULL DEFAULT 'null'
Submitted: 24 Dec 2019 6:47 Modified: 25 Dec 2019 1:44
Reporter: Lvanhades feng Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: JSON Severity:S3 (Non-critical)
Version:5.7.14 OS:CentOS
Assigned to: CPU Architecture:Any

[24 Dec 2019 6:47] Lvanhades feng 
Description:
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1101 | BLOB, TEXT, GEOMETRY or JSON column 'strategy_rule' can't have a default value |
+---------+------+--------------------------------------------------------------------------------+

mysql> show variables like '%sql_mode%';
+---------------+------------------------+
| Variable_name | Value                  |
+---------------+------------------------+
| sql_mode      | NO_ENGINE_SUBSTITUTION |
+---------------+------------------------+

mysql version is
mysql> select version();
+------------+
| version()  |
+------------+
| 5.7.14-log |
+------------+

I have a question about json，
`strategy_rule` json NOT NULL DEFAULT '' COMMENT '策略规则'
Will be coerced
`strategy_rule` json NOT NULL DEFAULT 'null' COMMENT '策略规则'

After backing up the database, restore on another instance。

Will cause an error during restore:
/usr/local/mysql57_14/bin/mysql -uroot -p --socket=/tmp/mysql_3347.sock
Enter password: 
ERROR 1101 (42000) at line 3216: BLOB, TEXT, GEOMETRY or JSON column 'strategy_rule' can't have a default value

How to repeat:
 CREATE TABLE `22sku_strategy` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '利用主键策略生成的唯一键',
  `strategy_name` varchar(64) NOT NULL DEFAULT '' COMMENT '策略名称',
  `strategy_type` tinyint(2) NOT NULL DEFAULT '99' COMMENT '策略类型,1:不参与积分(no_points);2:燃脂积分(activity_run);3:集点(collect_points);4:不参与绿卡活动(no_recharge_activity);5:奖励积分(reward_integral);99:无效(invalid)',
  `strategy_rule` json NOT NULL DEFAULT '' COMMENT '策略规则',
  `start_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '策略生效时间',
  `end_date` datetime NOT NULL DEFAULT '2099-01-01 23:59:59' COMMENT '策略失效时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` int(11) NOT NULL DEFAULT '1' COMMENT '特指后台创建人(公司员工 id)',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` int(11) NOT NULL DEFAULT '1' COMMENT '特指后台更新人(公司员工 id)',
  `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_strategy_name` (`strategy_name`,`strategy_type`),
  KEY `idx_strategy_type` (`strategy_type`),
  KEY `idx_start_date` (`start_date`),
  KEY `idx_end_date` (`end_date`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='商品策略表';

mysql> show warnings;
+---------+------+--------------------------------------------------------------------------------+
| Level   | Code | Message                                                                        |
+---------+------+--------------------------------------------------------------------------------+
| Warning | 1101 | BLOB, TEXT, GEOMETRY or JSON column 'strategy_rule' can't have a default value |
+---------+------+--------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table 22sku_strategy\G
*************************** 1. row ***************************
       Table: 22sku_strategy
Create Table: CREATE TABLE `22sku_strategy` (
  `id` bigint(11) unsigned NOT NULL AUTO_INCREMENT COMMENT '利用主键策略生成的唯一键',
  `strategy_name` varchar(64) NOT NULL DEFAULT '' COMMENT '策略名称',
  `strategy_type` tinyint(2) NOT NULL DEFAULT '99' COMMENT '策略类型,1:不参与积分(no_points);2:燃脂积分(activity_run);3:集点(collect_points);4:不参与绿卡活动(no_recharge_activity);5:奖励积分(reward_integral);99:无效(invalid)',
  `strategy_rule` json NOT NULL DEFAULT 'null' COMMENT '策略规则',
  `start_date` datetime NOT NULL DEFAULT '1970-01-01 00:00:00' COMMENT '策略生效时间',
  `end_date` datetime NOT NULL DEFAULT '2099-01-01 23:59:59' COMMENT '策略失效时间',
  `created_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `created_by` int(11) NOT NULL DEFAULT '1' COMMENT '特指后台创建人(公司员工 id)',
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `updated_by` int(11) NOT NULL DEFAULT '1' COMMENT '特指后台更新人(公司员工 id)',
  `remark` varchar(255) NOT NULL DEFAULT '' COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_strategy_name` (`strategy_name`,`strategy_type`),
  KEY `idx_strategy_type` (`strategy_type`),
  KEY `idx_start_date` (`start_date`),
  KEY `idx_end_date` (`end_date`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4 COMMENT='商品策略表'
[24 Dec 2019 10:52] MySQL Verification Team 
We're sorry, but the bug system is not the appropriate forum for asking help on using MySQL products. Your problem is not the result of a bug.
Quoting from manual "A JSON column cannot have a non-NULL default value." https://dev.mysql.com/doc/refman/5.7/en/json.html

For details on getting support for MySQL products see http://www.mysql.com/support/
You can also check our forums (free) at http://forums.mysql.com/

Thank you for your interest in MySQL.
[25 Dec 2019 1:44] Lvanhades feng 
I agree with your opinion，Because I use non-strict mode  NO_ENGINE_SUBSTITUTION。
MySQL Also gave a warning。
After the table is created successfully，But [strategy_rule field] convert [DEFAULT '']  to [DEFAULT 'null']，
Will cause me to have problems migrating the database through the mysqldump tool。