Bug #53877 Table with columns set at NOT NULL accepting NULL values
Submitted: 21 May 2010 11:02 Modified: 21 May 2010 11:21
Reporter: Allan Hart Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server Severity:S1 (Critical)
Version:5.1.47 OS:MacOS (Snow Leopard)
Assigned to: CPU Architecture:Any

[21 May 2010 11:02] Allan Hart
Description:
I created a table with columns set to NOT NULL. When I used the INSERT command in the terminal to add records to the table I tried to enter information that should have created an error and it didn't. It allowed the record to be created with NULL values in the columns?

How to repeat:
mysql> create table users(user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,first_name VARCHAR(20) NOT NULL,last_name VARCHAR(40) NOT NULL,email VARCHAR(60) NOT NULL,pass CHAR(40) NOT NULL,registration_date DATETIME NOT NULL,PRIMARY KEY(user_id));
Query OK, 0 rows affected (0.05 sec)

mysql> show tables;
+--------------------+
| Tables_in_sitename |
+--------------------+
| users              |
+--------------------+
1 row in set (0.00 sec)

mysql> show columns from users;
+-------------------+-----------------------+------+-----+---------+----------------+
| Field             | Type                  | Null | Key | Default | Extra          |
+-------------------+-----------------------+------+-----+---------+----------------+
| user_id           | mediumint(8) unsigned | NO   | PRI | NULL    | auto_increment |
| first_name        | varchar(20)           | NO   |     | NULL    |                |
| last_name         | varchar(40)           | NO   |     | NULL    |                |
| email             | varchar(60)           | NO   |     | NULL    |                |
| pass              | char(40)              | NO   |     | NULL    |                |
| registration_date | datetime              | NO   |     | NULL    |                |
+-------------------+-----------------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> insert into users(first_name) values('Allan');
Query OK, 1 row affected, 4 warnings (0.00 sec)

mysql> select * from users;
+---------+------------+-----------+-------+------+---------------------+
| user_id | first_name | last_name | email | pass | registration_date   |
+---------+------------+-----------+-------+------+---------------------+
|       1 | Allan      |           |       |      | 0000-00-00 00:00:00 |
+---------+------------+-----------+-------+------+---------------------+
1 row in set (0.00 sec)

Any suggestions?
[21 May 2010 11:21] Giuseppe Maxia
Thank you for taking the time to write to us, but this is not a bug. Please double-check the documentation available at http://dev.mysql.com/doc/refman/5.1/en/insert.html and the instructions on how to report a bug at http://bugs.mysql.com/how-to-report.php

Specifically, if you set SQL_MODE='STRICT_ALL_TABLES' an error will be issued for not null values inserted.
[21 May 2010 11:23] Giuseppe Maxia
I mean an error will be issued when you enter a null value in a non null field without a default value