MySQL Bugs: #36346: Tables accepting NULL in NOT NULL fields

Bug #36346	Tables accepting NULL in NOT NULL fields
Submitted:	25 Apr 2008 14:42	Modified:	25 Apr 2008 15:32
Reporter:	Tim Waddell	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Websites: bugs.mysql.com	Severity:	S2 (Serious)
Version:	5.0.45-community-log	OS:	Linux (PHP 5.2.5)
Assigned to:		CPU Architecture:	Any
Tags:	NOT NULL, null

Description:
I have noticed that fields marked as NOT NULL are accepting NULL values. I have tested this through PHP Insert statements and directly through phpMyAdmin.

I have a simple table with 3 fields  - id, username and password. Both the username and password fields (varchar 255) are set to NOT NULL but when I insert a record via PHP insert or direct through PHPMyAdmin the new record is created with no MySQL error even though there is no data the field passed.

How to repeat:
I repeated the test passing NULL not an empty "" and the same error occurred.

In PhpMyAdmin I went to INSERT and tried to insert a record by only assigning an id field. The record inserted with no errors.

If I use phpMyAdmin to insert a record and I type NULL in the username and password fields. The record is still inserted.

SQL query:

INSERT INTO `dotbiz_test`.`users` (
`ID` ,
`username` ,
`password` 
)
VALUES (
'1', '', ''
), (
'2', 'NULL', 'NULL'
); 

It seems that by deafult the empty string (or NULL) is being encased in quotes which is then read as being NOT NULL where infact it is NULL.

Please help!

Sorry, but 'NULL' is not the same as NULL. So, server surely can accept 'NULL' string as a value for some NOT NULL columns. This is not a bug of server, though.