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

[25 Sep 2018 7:31] Kapil Baheti
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.
[25 Sep 2018 7:32] Kapil Baheti
Display of value in table and description of table.

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

[25 Sep 2018 12:48] Miguel Solorzano
Thank you for the bug report. Please provide the create table statement you have used to create the table. Thanks.
[25 Sep 2018 13:06] Kapil Baheti
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.
[25 Sep 2018 13:16] Miguel Solorzano
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 >