MySQL Bugs: #56688: JDBC - insert error - field doesn't have a default value

Bug #56688	JDBC - insert error - field doesn't have a default value - NOT NULL field
Submitted:	9 Sep 2010 12:04	Modified:	9 Sep 2010 14:22
Reporter:	N N	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	5.1.13	OS:	Any
Assigned to:		CPU Architecture:	Any
Tags:	DEFAULT, field, jdbc, not, null, value

Description:
If a field is defined as being NOT NULL, then when the MySQL JDBC driver tries to do an insert without specifying a value for that field, the insert fails and we get the SQLException:

"Field doesn't have a default value".

The problem is that the INSERT succeeds with the mysql command line client, albeit with the above warning.  On the JDBC driver, the insert fails with the an SQLException.

How to repeat:
CREATE TABLE TestFoo (A INT, B VARCHAR(128) NOT NULL);
INSERT INTO TestFoo (A) VALUES (123);

Suggested fix:
Expected Result:
================
JDBC driver to behave similarly to the command line client: allow inserts that do not specify values for NOT NULL fields.

This isn't a bug with the JDBC driver. The JDBC driver sets the sql_mode to STRICT_TRANS_TABLES, as this is the mode that aligns with JDBC specification requirements for things like missing values for fields with no defaults, truncation errors, etc. 

If there is no DEFAULT value for a field, this is an error according to the SQL standard, and thus the JDBC specification, and the driver must follow this behavior.