I recently upgraded MySQL to 4.1 to take advantage of subselects. It seemd that my application was working fine, but all of a sudden my columns that are declared not-null no longer use a default value when null is inserted. I just get a "cannot be null" error. I read that explicitly inserting null causes an error, while leaving the column out of the insert will use the default.

How does hibernate handle this, and why am I getting these errors when it was working as expected previously? Is this a MySQL setting I missed, or is it a problem with how hibernate inserts nulls?
Is there something I could put in the mapping file to force it to use the column default value?

Just to clarify the above.

CREATE TABLE `Foo` (
`bar` VARCHAR( 255 ) DEFAULT 'unknown' NOT NULL ,
`baz` ENUM( 'qux', 'xyzzy', 'plugh' ) DEFAULT 'qux' NOT NULL
) TYPE = InnoDB CHARACTER SET = latin1;

INSERT INTO Foo VALUES();
results in one row being added:
bar='unknown', baz='qux'

INSERT INTO Foo VALUES (NULL , NULL);
results in error #1048 - Column 'bar' cannot be null

It seems that Hibernate does the latter, when persisting data. How can I get the behaviour of the former? Do I have to manually synchronize default values in my objects to be the same as my db tables? That seems wrong - if I want to change the default value of a column later, I should be able to do it in the DB without recompiling classes.

Please post your hibernate mapping file.

I actually think your *current* behvaior is correct. Specifying 'null' certainly should trip the "not null" constraint. Not specifying the column at all should substitute the default value.

To make Hibernate do what you want, you could implement a UserType that substitutes your default value if the value is null.

Yeah, I agree that the null inseretion behaviour is correct now - I don't know why it worked before. But my question is more like why does hibernate insert an explicit null value? Wouldn't it make more sense that if the property is not set, just leave it out of the insert?

This is not a big issue, I am just curious. I could always write my classes to initialize all non-null properties to a default, and not use any default values for the columns at all, since they will never have a chance to be used.

The only side effect is that if I later decide to change my default value from "unknown" to "unspecified", I will have to edit the source and recompile.

dynamic-insert="true"


please use documentation

But the bigger question is, why did it "work" before and not now?

Your bigger question (if you really need to know) would be a question for the MySQL folks, as this is always how Hibernate processed inserts without enabling dynamic-insert.

Something changed between 4.1 and the version you were previously using. But the behaviour you are seeing now is much more inline with what most databases do (otherwise, how do you insert a truly null value)?

Agreed. And the answer to this question wasn't so easily found in the documentation. I myself was looking within the "property" node, not the "class" node for something like the "dynamic-insert" attribute that Gavin mentioned. And even that wasn't so obvious from reading the description. I think this actually belongs in a FAQ.

Of course this was exacerbated by the fact that the code "used" to work (even though it really shouldn't have in the 1st place).

I appreciate the knowledge of dynamic-insert, but it doesn't seem to work in this case. Hibernate is still generating explicit nulls. I guess dynamic-insert does not apply to components, or list of components?

Here is the mapping file.
<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 2.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-2.0.dtd">

<hibernate-mapping>
<class name="com.mattvanhorn.simetra.model.PersonName" table="PersonName"
dynamic-update="true"
dynamic-insert="true" >
<id name="id" column="id" type="java.lang.Integer" >
<generator class="native"></generator>
</id>
<property
name="formattedName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="formattedName" />
<property
name="legalName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="legalName" />
<property
name="middleName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="middleName" />
<property name="
preferredGivenName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="preferredGivenName" />
<list
name="givenNames"
table="PersonNameGivenName"
lazy="true"
inverse="false"
cascade="all" >
<key column="personName"/>
<index column="list_index"/>
<element
column="givenName"
type="string"
not-null="false"
unique="false" />
</list>


<list
name="familyNames"
table="PersonNameFamilyName"
lazy="true"
inverse="false"
cascade="all" >
<key column="personName"/>
<index column="list_index" />
<composite-element
class="com.mattvanhorn.simetra.model.FamilyName">
<property
name="familyName"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="familyName" />
<property
name="primary"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="isPrimary" />
</composite-element>
</list>


<list name="affixes"
table="PersonNameAffix"
lazy="true"
inverse="false"
cascade="all" >
<key column="personName" >
</key>
<index column="list_index" />
<composite-element
class="com.mattvanhorn.simetra.model.Affix" >
<property
name="affix"
type="java.lang.String"
update="true"
insert="true"
access="property"
column="affix" />
<property name="type"
type="com.mattvanhorn.simetra.model.AffixType"
update="true"
insert="true"
access="property"
column="type"
/>
</composite-element>
</list>
</class>
</hibernate-mapping>

In my logs I see:
insert into PersonNameFamilyName (personName, list_index, familyName, isPrimary) values (32, 0, 'bar', null)

isPrimary is an enum with values of 'true', 'false' and 'unknown', and the default is unknown.