MySQL Bugs: #16439: Field declared as NOT NULL accepts null value (MSAccess/MySQL)

Bug #16439	Field declared as NOT NULL accepts null value (MSAccess/MySQL)
Submitted:	12 Jan 2006 7:50	Modified:	19 Jan 2006 8:06
Reporter:	Grace Coronado	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S3 (Non-critical)
Version:	3.51.12	OS:	Windows (Microsoft-XP)
Assigned to:		CPU Architecture:	Any

Description:
When entering data from MS Access form, the MySQL database still accepts and save the record even if a NON-NULL field contains a NULL value.

Our current settings:

Windows XP 5.1
MS Access 2002
MySQL Server 5.0.4
MyODBC 3.51.12 (updated from 3.51.11-2, but the error still occurs)
MS Jet Engine 4.0

How to repeat:
Using Navicat, create a MySQL table “testtab” with the following columns:
Col1 – int, autoincrement, not null, primary key
Col2 – varchar, not null
Col3 – varchar, allow null
Col4 – timestamp, allow null (this is to prevent displaying #Deleted value)

Create an MS Access database, “testMySQLDB”, then link “testtab” table from MySQL.  From the table tab, just click on testtab to add records.  Enter values for “Col3” field only.  The record will be saved even if “Col2” field is NULL.

This occurs whether you are using MyISAM or InnoDB Engine.

Thank you for a problem report. First of all, please upgrade your MySQL Server 5.0.4 to some current version (it is really old). 5.0.18 is generally available for some time already. Then, send the SHOW CREATE TABLE results for the problematic table from mysql command line client. 

Are you sure that Ms Access really put NULL values into that column? Not '' or something else? Just execute SELECT count(*) FROM table WHERE not_null_col IS NULL - how many such rows are found?

Yes, I think you’re right about MySQL storing a default value:”” to a NOT NULL field. Because in MS Access it’s treated differently.

-----------------
If you have a MySQL table called “mysqltab”, created using Navicat with the following columns:
Col1 – int, autoincrement, not null, primary key
Col2 – varchar, not null
Col3 – varchar, allow null
Col4 – timestamp, allow null (this is to prevent displaying #Deleted value)

When viewed in MS Access, “Col2” will have the following properties:
Required: Yes
Allow Zero Length: Yes

Then create an MS Access table called “accesstab”, with the same columns and properties, it will give a warning that:

“Col2 cannot contain a null value. Required property is set to True.”

--------------
We just migrated our existing database from MS Access to MySQL but intend to keep the data entry interface in MS Access. The centralized MySQL database will be accessed also by PHP for our web-based applications.

We haven’t upgraded yet to 5.0.18 as you have advised. Though, I’m not sure if this is MySQL or MyODBC problem. We’ll test again once we have upgraded. Hope this can be handled in this new version or future release of MySQL/MyODBC. Otherwise, will this mean we have to add a check for all NOT NULL fields that we have?

Anyway, thanks a lot for your fast reply. Will send you an update once we have tested our database with the latest MySQL release. We really appreciate your help.

- Grace

I am waiting for your results. By the way, I've got no answer to my question from the previous comment:

Send the SHOW CREATE TABLE results for the problematic table from mysql command line client. 

It may be a problem of your Navicat tool. Please, create the table in mysql command line client and try with it.

Updating to MySQL 5.0.12 solved the problem with ‘NOT NULL’ field.

Result of SHOW CREATE TABLE testtab:
NOTE: Testtab (created in Navicat)

CREATE TABLE `testtab` 
   (   `col1` int(11) NOT NULL auto_increment,   
       `col2` varchar(255) NOT NULL,   
       `col3` varchar(255) default NULL,   
       `col4` timestamp NULL default NULL,   PRIMARY KEY  (`col1`) 
    ) ENGINE=MyISAM DEFAULT CHARSET=latin1 

Now, if you will add a record from MS Access form and you will not provide any value to a ‘NOT NULL’ field the following error message will be displayed:

   "ODBC-insert on a linked table ‘colname’ failed.
    [MySQL] [ODBC 3.51 Driver] [mysqld-5.0-18-nt] Field ‘colname’ doesn’t have a 
    default value (#1364) "

NOTE:  I also tried creating a table through MySQL Administrator.  If you define a column as ‘NOT NULL’ it will automatically set the default value to ‘ ‘.  Then I tried creating a table from MySQL command line (a column with NOT NULL property without a default value), linked the table to MS Access and added records.  Adding records without any data to a ‘NOT NULL’ field now results to an error.

Thanks again for the support that you provide us.

- Grace

So, looks like a bug is related to the (ages old and beta) 5.0.4 server version. It is fixed later.