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: | |
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
[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