Bug #4022 Columns defined as NOT NULL accepting NULL values
Submitted: 6 Jun 2004 11:00 Modified: 8 Jun 2004 15:56
Reporter: Denise Subramaniam Email Updates:
Status: Not a Bug Impact on me:
None 
Category:MySQL Server: MyISAM storage engine Severity:S1 (Critical)
Version:4.0.15 standard OS:Linux (Linux)
Assigned to: Dean Ellis CPU Architecture:Any

[6 Jun 2004 11:00] Denise Subramaniam
Description:
I'm not sure if I selected the correct Category - please change as neccessary.

My website is hosted by iPowerWeb (www.ipowerweb.com) 
They supply the following environment:

Operating System Linux 
Perl Version 5.008 
Perl Path /usr/bin/perl 
Installed Perl Modules Click to View 
PHP Version 4.3.2 
Mysql Version 4.0.15-standard 

The crux of the issue is that table columns defined as NOT NULL are inserting NULL values. 

The web form and PHP processing page are correctly capturing user entered non-null values from the form. The values make it into the variables in the SQL statement - however they don't make it into the database. A row is created in the table with only the auto-generated id value and a value in one other column. Two columns defined as NOT NULL accept, without complaint, NULL values.

I don't know if this is repeatable in another environment so I'll supply all code used but if you can't recreate I'll be happy to let you into my database the see what is happening. directly... I have experienced this issue in th epast - but it was a random incident and until now I could never recreate it.

All related code and SQL is below - PLEASE contact me for any additional info you need as this is a serious problem for me and is holding up a project.

How to repeat:
HTML FORM CODE: (Note: I removed unecessary code to make it easier for you...)
_______________________________
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>User Registeration</TITLE>

<script language="JavaScript" type="text/javascript" src="jsfile.js"></script>

</HEAD>

<BODY>
  <FORM name="userRegForm" action="CertRegExe.php" method="post" onsubmit="return validateFields(un, pw, pwcf, Fname, Lname)">
 
 <TABLE CLASS="contact" cellSpacing=0 cellPadding=0 border=0 align="left" width=550>
 
    <TR bgcolor="#DDDDDD">
     <TD COLSPAN=2>
       <CENTER><H2 CLASS="normal">SAI Database Experts Registration Form</H2></CENTER>
     </TD>
   </TR>

	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> User Name:</TD>
		<TD><INPUT TYPE="text" NAME="un" MAXLENGTH="15"></TD>
   </TR>
   
   	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Password:</TD>
		<TD><INPUT TYPE="password" NAME="pw" MAXLENGTH="8"></TD>
   </TR>
   
   	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Confirm Password:</TD>
		<TD><INPUT TYPE="password" NAME="pwcf" MAXLENGTH="8"></TD>
	</TR>
   <BR><BR>
	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> First Name:</TD>
		<TD><INPUT TYPE="text" NAME="Fname" MAXLENGTH="20"></TD>
	</TR>
	
	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Last Name:</TD>
		<TD><INPUT TYPE="text" NAME="Lname" MAXLENGTH="20"></TD>
	</TR>
	
	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Email Address:</TD>
		<TD><INPUT TYPE="text" SIZE="40" NAME="EmailAddress"></TD>
	</TR>
	
	<TR>
		<TD CLASS="label"><SPAN CLASS="red">*</SPAN> Home Phone:</TD>
		<TD><INPUT TYPE="text" NAME="HomePhone" MAXLENGTH="20"></TD>
	</TR>
	
	<TR>
		<TD CLASS="label">Work Phone:</TD>
		<TD><INPUT TYPE="text" NAME="WorkPhone" MAXLENGTH="20"></TD>
	</TR>
	
	<TR>
		<TD CLASS="label">  Cell Phone:</TD>
		<TD><INPUT TYPE="text" NAME="CellPhone" MAXLENGTH="20"></TD>
	</TR>
	<BR><BR>

   <TR align="right">
     <TD width=550>
        <SCRIPT LANGUAGE="JavaScript" TYPE="text/javascript">
         function disableButton(obj) {
         obj.disabled = true;
         }
          </SCRIPT>
        <INPUT type="submit" value="Sign In >" name="submit">
      </FORM>    
     </TD>
  </TR>

</TABLE>
<BR>
</BODY></HTML>

_______________________________

PHP processing page code:

<!DOCTYPE HTML PUBLIC
          "-//W3C//DTD HTML 4.0 Transitional//EN"
          "http://www.w3.org/TR/html4/loose.dtd">

<HTML>
<HEAD>
<TITLE>SAI Database Experts Add Certification Registration to Database</TITLE>
</HEAD>
<BODY bgcolor=white lang=EN-US>

<?php

include("RegFc.php");
include("RegVar.php");

$un = $_POST['un'];
$pw = $_POST['pw'];
$pwcf = $_POST['pwcf'];
$Fname = $_POST['Fname'];
$Lname = $_POST['Lname'];

/* the following code is to print the values in the variables passed from the form */

echo $un;
echo "<BR>";
echo $pw;
echo "<BR>";
echo $Fname;
echo "<BR>";
echo $Lname;
echo "<BR>";
echo $Company;
echo "<BR>";
echo $HomeAddrs1;
echo "<BR>";
echo $HomeAddrs2;
echo "<BR>";
echo $HomeCountry;
echo "<BR>";
echo $State;
echo "<BR>";
echo $HomeCity;
echo "<BR>";
echo $HomeZipcode;
echo "<BR>";
echo $Birthdate;
echo "<BR>";
echo $Gender;
echo "<BR>";
echo $ResumeText;
echo "<BR>";
echo $Referrer;
echo "<BR>";
echo $EmailAddress;
echo "<BR>";
echo $HomePhone;
echo "<BR>";
echo $WorkPhone;
echo "<BR>";
echo $CellPhone;
echo "<BR>";
echo "<BR>";

if(($un=="")||($pw=="")){
	printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Invalid Entry: Please be sure you entered a password & username</B></FONT><BR><BR>");
    printf ("<BR><BR><a href='userReg.htm'>Back</a>");
    exit;
}

if($pwcf !== $pw){
	printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Invalid Entry: Your password confirmation failed. Please try again.</B></FONT><BR><BR>");
    printf ("<BR><BR><a href='userReg.htm'>Back</a>");
    exit;
}

if (empty($Fname))

{
    printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
    printf ("You must enter a first name. Please use your browser's BACK button to return to the registration form and enter your first name.<BR><BR><BR><BR>");
    exit;
}

if (empty($Lname))

{
    printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
    printf ("You must enter a last name. Please use your browser's BACK button to return to the registration form and enter your last name.<BR><BR>");
    exit;
}

 function funcCheckEmail($sEmailAddress)
    {
       // Regex of valid characters
       $sChars = "^[A-Za-z0-9\._-]+@([A-Za-z][A-Za-z0-9-]{1,62})(\.[A-Za-z][A-Za-z0-9-]{1,62})+$";
       // Check to make sure it is valid
       $bIsValid = true;
       if(!ereg("$sChars",$sEmailAddress))
       {
       $bIsValid = false;
       }
       return $bIsValid;
    }

if (empty($EmailAddress))

{
    printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
    printf ("You must enter an email address. Please use your browser's BACK button to return to the registration form and enter an email address.<BR><BR>");
    exit;
}

if (!funcCheckEmail($EmailAddress))

{
    printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
    printf ("You did not enter a valid email address. Please use your browser's BACK button to return to the registration form and enter a valid email address.<BR><BR>");
    exit;
}

if (empty($HomePhone) && empty($WorkPhone) && empty($CellPhone))
{
    printf ("<FONT SIZE=4><B>SAI Database Experts</B></FONT><BR><BR>");
    printf ("<FONT SIZE=2 Color=red><B>Registration Error</B></FONT><BR><BR>");
    printf ("You must provide at least one phone number. Please use your browser's BACK button to return to the registration form and enter a phone number.<BR><BR>");
    exit;
}

$dateTime = date("Y-m-d G:i:s");	//added on 4/2/04

//To record date
$year = date("Y");
$mo = date("m");
$day = date("d");
$hr = date("H");	//Added on 11/4/03
$min = date("i");	//Added on 11/4/03
$sec = date("s");	//Added on 11/4/03

//$dbDate = "$year-$mo-$day";	//Used before
$dbDate = "$year-$mo-$day $hr:$min:$sec";	//date_created field must be DATETIME

dbConnect();

$table = $perTable;
$sVal =  "Student";

$varArray1 = array($sVal, $Fname, $Lname, $Company, $HomeAddrs1, $HomeAddrs2, $HomeCountry, $State, $HomeCity, $HomeZipcode, $Birthdate, $Gender, $Comments, $ResumeText, $Referrer, $dateTime);
$dbArray1 = array("person_type", "f_name", "l_name", "company", "home_addrs_1", "home_addrs_2", "country", "home_state", "home_city", "home_zipcode", "birthdate", "gender", "comments", "referrer", "date_created");

/* the following code is to print the values passed to the SQL statement */
var_dump($varArray1);
echo "<BR>";
echo "<BR>";

InsertRowInTable($table, $varArray, $dbArray);

$PersonID = mysql_insert_id();

$table = $emailTable;

$varArray2 = array($PersonID, $EmailAddress, $dateTime);
$dbArray2 = array("person_id", "email_addrs", "date_created");

InsertRowInTable($table, $varArray, $dbArray);

if (!empty($HomePhone))

$table = $phoneTable;

$varArray3 = array($PersonID, "Home", $HomePhone, $dateTime);
$dbArray3 = array("person_id", "phone_type", "phone_num", "date_created");

InsertRowInTable($table, $varArray, $dbArray);

if (!empty($WorkPhone))

$table = $phoneTable;

$varArray4 = array($PersonID, "Work", $WorkPhone, $dateTime);
$dbArray4 = array("person_id", "phone_type", "phone_num", "date_created");

InsertRowInTable($table, $varArray, $dbArray);

if (!empty($CellPhone))

$table = $phoneTable;

$varArray5 = array($PersonID, "Cell", $CellPhone, $dateTime);
$dbArray5 = array("person_id", "phone_type", "phone_num", "date_created");

InsertRowInTable($table, $varArray, $dbArray);

$table = $userTable;
$field = "username";
$fieldData = $un;
$more = "pw = '$pw'";

$varArray = array($PersonID, $un, $pw, $dbDate);
$dbArray = array("person_id", "username", "pw", "date_created");

$id = recordAndGetIdByVal($table, $varArray, $dbArray);

echo  $id;
echo "<BR>";
echo "<BR>";

?>
</BODY></HTML>

______________________________

Code for the functions that are called:

function InsertRowInTable($table, $varArray, $dbArray){
    $dbStr = "";
	$varStr = "";

	$num = count($dbArray);
	$numVal = count($varArray);

	if($num!=$numVal){
		//die("recordAndGetIdByVal Function Error: Number of Args doesn't match");
		errorClose("Database Error - 301");	//changed on 11/11/03
	}

	for($i=0; $i<$num; $i++){
		$field = $dbArray[$i];
		$value = $varArray[$i];

		if($i==0){
			$dbStr = "$field";
			$varStr = "'$value'";
		}
		else{
			$dbStr .= ", $field";
			$varStr .= ", '$value'";
		}
	}

	$dbQuery = "INSERT INTO $table ($dbStr)";
	$dbQuery .= " VALUES ($varStr)";

	mysql_query($dbQuery) or errorClose("Database Error - 302");	//changed on 11/11/03
	//die("Couldn't insert data into a table!<br>".mysql_error());

}

function recordAndGetIdByVal($table, $varArray, $dbArray){

	$dbStr = "";
	$varStr = "";

	$num = count($dbArray);
	$numVal = count($varArray);

	if($num!=$numVal){
		//die("recordAndGetIdByVal Function Error: Number of Args doesn't match");
		errorClose("Database Error - 301");	//changed on 11/11/03
	}

	for($i=0; $i<$num; $i++){
		$field = $dbArray[$i];
		$value = $varArray[$i];

		if($i==0){
			$dbStr = "$field";
			$varStr = "'$value'";
		}
		else{
			$dbStr .= ", $field";
			$varStr .= ", '$value'";
		}
	}

	$dbQuery = "INSERT INTO $table ($dbStr)";
	$dbQuery .= " VALUES ($varStr)";

	mysql_query($dbQuery) or errorClose("Database Error - 302");	//changed on 11/11/03
	//die("Couldn't insert data into a table!<br>".mysql_error());

	$id = mysql_insert_id();

	return $id;

}

TABLE DEFS: with affected data exported

# phpMyAdmin MySQL-Dump
# version 2.4.0
# http://www.phpmyadmin.net/ (download page)
#
# Host: localhost
# Generation Time: Jun 06, 2004 at 01:55 AM
# Server version: 4.0.15
# PHP Version: 4.2.3
# Database : `saidata_StudentDB`
# --------------------------------------------------------

#
# Table structure for table `PERSONS`
#

CREATE TABLE PERSONS (
  person_id int(10) unsigned NOT NULL auto_increment,
  person_type varchar(50) default 'Student',
  f_name varchar(25) NOT NULL default '',
  l_name varchar(25) NOT NULL default '',
  company varchar(100) default NULL,
  company_id int(10) default NULL,
  home_addrs_1 varchar(150) NOT NULL default '',
  home_addrs_2 varchar(100) default NULL,
  country varchar(15) NOT NULL default 'USA',
  home_state varchar(50) NOT NULL default '',
  home_city varchar(100) NOT NULL default '',
  home_zipcode varchar(15) NOT NULL default '',
  birthdate datetime default NULL,
  gender char(1) NOT NULL default '',
  comments varchar(255) default NULL,
  resume_text longtext,
  referrer varchar(100) default NULL,
  date_created datetime default NULL,
  last_date_edited datetime NOT NULL default '0000-00-00 00:00:00',
  PRIMARY KEY  (person_id),
  KEY l_name (l_name)
) TYPE=MyISAM COMMENT='collect data about persons';

#
# Dumping data for table `PERSONS`
#

Affected rows of data entered through testing

INSERT INTO PERSONS VALUES (118, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (119, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (120, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (121, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (122, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (123, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (124, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (125, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (126, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (127, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (128, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (129, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (130, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (131, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (132, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (133, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
NSERT INTO PERSONS VALUES (136, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (137, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');
INSERT INTO PERSONS VALUES (138, 'Student', '', '', NULL, NULL, '', NULL, 'USA', '', '', '', NULL, '', NULL, NULL, NULL, NULL, '0000-00-00 00:00:00');

Suggested fix:
not sure - this is a pretty serious issue.
[7 Jun 2004 13:01] Denise Subramaniam
I found an error in my PHP code that caused the blank values to be passed into my SQL statement - however this is still a bug in MySQL because the database should never have allowed a row be inserted with NULL values in columns defined as NOT NULL. This is not expected behavior for a RDBMS. The database should have generated an error
[8 Jun 2004 0:27] Dean Ellis
I cannot repeat this with 4.0.21 or 4.1.3.  Also, looking at your SQL, I do not see that a NULL value has been inserted into a NOT NULL column.  If you have SQL demonstrating the insertion of a NULL value into a NOT NULL column (other than special types which handle this), please submit it so we can re-test.
[11 Jun 2004 19:26] Paul Dubois
I too do not see that NULL is actually being inserted into any NOT NULL
columns -- that is, they might be specified in the INSERT statement,
but they are not *stored* in the record.  This occurs because MySQL
maps illegal values to its best attempt at a legal value. See, for example:

http://dev.mysql.com/doc/mysql/en/constraint_NOT_NULL.html

Best to check the values on the application side before attempting to insert them.
Note that even if MySQL were to refuse an INSERT statement with an
error when it contains NULL for a NOT NULL column, it would then be necessary
to determine on the application side what to do with the bad values. Doing so
before issuing any INSERT avoids these problems.
[11 Jun 2004 20:49] Denise Subramaniam
I'm a fairly new with PHP and it turned out there was a bug in my function call where I passed the parameter variables used to create the SQL statement for the insert. I had a terrible time finding that bug because MySQL performed the insert and bypassed my NOT NULL settings by using a hard coded default value of an empty string.

I don't think this is a good programming choice for MySQL. If you think some users will want to allow an empty string value to be inserted into a column they defined as NOT NULL, then I can say that at least some database developers DO NOT want this behavior. At the very least you ought to leave the decision about what default, if any, is used for a NOT NULL column up to the database developer instead of forcing a particular option on them.

The result of the current MySQL logic is several useless rows in my database. If this went into production, then I'd have visitors to my website registering and entering data into my web forms that never makes it into the database, but since MySQL never throws an error and instead creates rows with nothing but the auto-incremented id value, the PHP code goes right on to inform the newly registered user and the database/business administrator that a new record was sucessfully added to the database. When the marketing folks follow-up on the new "contact" they get no where because there is no name, phone number etc. So from a business perspective, this is not a positive feature.
[11 Jun 2004 21:35] Dean Ellis
Empty strings are not NULL; NULL is a special value and allowing the insertion of '' into a NOT NULL column is perfectly normal.

If the user does not provide a value, you should be inserting NULL rather than '', which will indeed throw an error for a NOT NULL column.