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

[25 Apr 2008 14:42] Tim Waddell
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` ,
'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!
[25 Apr 2008 15:32] Valeriy Kravchuk
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.