MySQL Bugs: #92553: Default Value in the table column is Null when column is set as Not NULL.

Bug #92553	Default Value in the table column is Null when column is set as Not NULL.
Submitted:	25 Sep 2018 7:31	Modified:	25 Sep 2018 13:16
Reporter:	Kapil Baheti	Email Updates:
Status:	Duplicate	Impact on me:	None
Category:	MySQL Server: DDL	Severity:	S2 (Serious)
Version:	5.7.20-0ubuntu0.16.04.1	OS:	Ubuntu (0.16.04.1)
Assigned to:		CPU Architecture:	Any
Tags:	Data Entry in Table

Description:
I created a table with columns as NOT NULL, by default MySQL set those columns default value as NULL and then the entries are also stored in the table with null values.

How to repeat:
Create table with columns as NOT NULL and then try to insert a record with default values. I encountered this when I am trying to insert value from my spring app using the command. By mistake the app doesn't took any value but the query got executed and it took default values as data but I guess when the column in the table is set as NOT NULL it should not be accepting null values.

Suggested fix:
Default Value can be made as mandate in query when column is set NOT NULL
In case the column is NOT NULL and if by mistake NULL is inserted it should be throwing the error from any platform.

Display of value in table and description of table.

Attachment: mysql_info.png (image/png, text), 149.76 KiB.

Thank you for the bug report. Please provide the create table statement you have used to create the table. Thanks.

Here is the create table statement I used:
create table users(phoneNumber varchar(30) NOT NULL,name varchar(30),email varchar(40) NOT NULL,password varchar(30));

Again describing the table I got the same response as shown in the image attached.

Thanks.

Thank you for  the feedback. Duplicate of older bug: https://bugs.mysql.com/bug.php?id=34198.

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.25 Source distribution BUILD: 2018-SET-08

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

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 5.7 > CREATE DATABASE d;
Query OK, 1 row affected (0,00 sec)

mysql 5.7 > USE d
Database changed
mysql 5.7 > create table users(phoneNumber varchar(30) NOT NULL,name
    -> varchar(30),email varchar(40) NOT NULL,password varchar(30));
Query OK, 0 rows affected (1,19 sec)

mysql 5.7 > SHOW CREATE TABLE users;
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                                                                 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| users | CREATE TABLE `users` (
  `phoneNumber` varchar(30) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `email` varchar(40) NOT NULL,
  `password` varchar(30) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0,01 sec)

mysql 5.7 > desc users;
+-------------+-------------+------+-----+---------+-------+
| Field       | Type        | Null | Key | Default | Extra |
+-------------+-------------+------+-----+---------+-------+
| phoneNumber | varchar(30) | NO   |     | NULL    |       |
| name        | varchar(30) | YES  |     | NULL    |       |
| email       | varchar(40) | NO   |     | NULL    |       |
| password    | varchar(30) | YES  |     | NULL    |       |
+-------------+-------------+------+-----+---------+-------+
4 rows in set (0,00 sec)

mysql 5.7 > INSERT INTO users (phoneNumber) Values ("222-2345");
ERROR 1364 (HY000): Field 'email' doesn't have a default value
mysql 5.7 >