Bug #104530 show create table or mysqldump display 'null' default value on json field
Submitted: 4 Aug 2021 8:53 Modified: 5 Aug 2021 13:49
Reporter: yuxiang jiang (OCA) Email Updates:
Status: Verified Impact on me:
None 
Category:MySQL Server: DDL Severity:S3 (Non-critical)
Version:5.7.34, 5.7.35, 8.0.26 OS:Any
Assigned to: CPU Architecture:Any
Tags: Contribution

[4 Aug 2021 8:53] yuxiang jiang
Description:
After setting sql_mode to 0, SQL statement 'create table tjson7(f1 int, f2 json not null default '');' can be executed successfully.

But retrieving create table SQL by show create table, the SQL will fail whatever sql_mode is stetted. And mysqldump will fail when import data.

By checking the statement, we found that definition of " `f2` json NOT NULL DEFAULT 'null'" will cause stop creating table successfully. 

How to repeat:
set sql_mode=0;
create table tjson7(f1 int, f2 json not null default '');

======
show create table tjson7;
mysql> show create table tjson7\G
*************************** 1. row ***************************
       Table: tjson7
Create Table: CREATE TABLE `tjson7` (
  `f1` int DEFAULT NULL,
  `f2` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=latin1

======
./bin/mysqldump -uroot -S mysql.sock -d tdb1 tjson7 > /tmp/tjson7.sql
--
-- Table structure for table `tjson7`
--

DROP TABLE IF EXISTS `tjson7`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `tjson7` (
  `f1` int(11) DEFAULT NULL,
  `f2` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
[5 Aug 2021 13:49] MySQL Verification Team
Hello yuxiang jiang,

Thank you for the report.

regards,
Umesh
[5 Aug 2021 13:50] MySQL Verification Team
-
 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> set sql_mode=0;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> create table tjson7(f1 int, f2 json not null default '');
Query OK, 0 rows affected, 1 warning (0.01 sec)

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

mysql> show create table tjson7\G
*************************** 1. row ***************************
       Table: tjson7
Create Table: CREATE TABLE `tjson7` (
  `f1` int(11) DEFAULT NULL,
  `f2` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)

-
 bin/mysqldump  -uroot -S /tmp/mysql_ushastry.sock -d test tjson7 > /tmp/tjson7.sql
[umshastr@support-cluster03:/export/home/tmp/ushastry/mysql-5.7.35]$ cat /tmp/tjson7.sql

CREATE TABLE `tjson7` (
  `f1` int(11) DEFAULT NULL,
  `f2` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

--

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.35 MySQL Community Server (GPL)

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> drop table tjson7;
Query OK, 0 rows affected (0.01 sec)

mysql> source /tmp/tjson7.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'f2' can't have a default value
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected, 1 warning (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set sql_mode='';
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> source /tmp/tjson7.sql
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

ERROR 1101 (42000): BLOB, TEXT, GEOMETRY or JSON column 'f2' can't have a default value
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
[5 Aug 2021 13:53] MySQL Verification Team
- 8.0.26 - same issue

 bin/mysql -uroot -S /tmp/mysql_ushastry.sock --local-infile
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.26 MySQL Community Server - GPL

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> create database test;
Query OK, 1 row affected (0.00 sec)

mysql> use test
Database changed
mysql> set sql_mode=0;
Query OK, 0 rows affected (0.00 sec)

mysql> create table tjson7(f1 int, f2 json not null default '');
Query OK, 0 rows affected, 1 warning (0.02 sec)

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

mysql> show create table tjson7\G
*************************** 1. row ***************************
       Table: tjson7
Create Table: CREATE TABLE `tjson7` (
  `f1` int DEFAULT NULL,
  `f2` json NOT NULL DEFAULT 'null'
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
1 row in set (0.01 sec)

mysql>
[10 Aug 2021 4:38] yuxiang jiang
fix for show create table

(*) I confirm the code being submitted is offered under the terms of the OCA, and that I am authorized to contribute it.

Contribution: show_create_table_json.patch (application/octet-stream, text), 3.90 KiB.

[10 Aug 2021 5:21] MySQL Verification Team
Thank you for the contribution.

regards,
Umesh