hello everybody. i'got in little problem with handling my mysql backend. my task is to sync a mysql db with an mssql db. to get all data out of one table i just used the session.CreateCriteria(type).List(); with my mapped dataclasses. i've done the same thing many times with the mssql and there is nothing special about the table fileds. only the nullable datetime is a problem. i found a solution from castle project and used this workaround with direct mapping in the dataclasse.

so to explain my problem i put in some shorten informations about my basic sessionprovider and mapping files.

waht happens is an exception as you can see below which occures when the the session.CreateCriteria(type).List(); is fired. nhibernate tries to do an update to this table but there is a foreign key not null problem. i understand the exception but not why nhibernate tries to do an update.
the database is in use to a big live system and i do not want any changes in these tables.

so can anybody explain me why the update occurs?



Hibernate version:
net-2.0

Mapping documents:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" namespace="mysqlImport.DataClasses" assembly="mysqlImport" default-cascade="save-update">
<class name="mysqlImport.DataClasses.Pres" table="pres">
<id name="UploadID" column="UploadID" type="Int32">
<generator class="identity" />
</id>
<property name="Uid" column="uid" type="Int32" />
<property name="Vid" column="vid" type="Int32" />
<property name="Sid" column="sid" type="Int32" />
<property name="Rid" column="rid" type="Int32" />
....
...
..
.
<property name="C2wExport" column="c2wExport" type="DateTime" />
<property name="C2wGrabbed" column="c2wGrabbed" type="DateTime" />
<property name="PresTimerValue" column="presTimerValue" type="String" />
<many-to-one name="TheEvent" column="vid" cascade="none" not-found="ignore" />
<many-to-one name="TheRoom" column="rid" cascade="none" not-found="ignore" />
<many-to-one name="TheSession" column="sid" cascade="none" not-found="ignore" />
<many-to-one name="TheUser" column="uid" cascade="none" not-found="ignore" />
</class>
</hibernate-mapping>


Code between sessionFactory.openSession() and session.close():
public sealed class SessionProvider
{
private const string CurrentSessionKey = "nhibernate.current_session";
private static readonly ISessionFactory sessionFactory;
private static ISession theSession;

static SessionProvider()
{
NHibernate.Cfg.Configuration configuration = new NHibernate.Cfg.Configuration();
configuration.SetProperty("hibernate.connection.provider", "NHibernate.Connection.DriverConnectionProvider");
configuration.SetProperty("hibernate.dialect", "NHibernate.Dialect.MySQLDialect");
configuration.SetProperty("hibernate.connection.driver_class", "NHibernate.Driver.MySqlDataDriver");
configuration.SetProperty("hibernate.connection.connection_string", ConfigurationManager.ConnectionStrings["mtalk"].ToString());
// --- Add the MySQL Dataclasses and their mappings
configuration.AddClass(typeof(mysqlImport.DataClasses.Event));
configuration.AddClass(typeof(mysqlImport.DataClasses.File));
configuration.AddClass(typeof(mysqlImport.DataClasses.Pres));
configuration.AddClass(typeof(mysqlImport.DataClasses.Room));
configuration.AddClass(typeof(mysqlImport.DataClasses.Session));
configuration.AddClass(typeof(mysqlImport.DataClasses.User));
sessionFactory = configuration.BuildSessionFactory();
}

public static ISession GetCurrentSession()
{
ISession lSession = theSession;

if (lSession == null)
{
lSession = sessionFactory.OpenSession();
}

return lSession;
}

public static void CloseSession()
{
if (theSession == null)
{
// No current session
return;
}

theSession.Close();
}

public static void CloseSessionFactory()
{
if (sessionFactory != null)
{
sessionFactory.Close();
}
}

}


Name and version of the database you are using:
MySQL-Client-Version: 5.0.37

The generated SQL (show_sql=true):
UPDATE pres SET uid = ?, vid = ?, sid = ?, rid = ?, speachTITLE = ?, speachFiles = ?, speachSNR = ?, speachSTART = ?, speachEND = ?, speachLENGTH = ?, speachTYPE = ?, speachID = ?, speachADDON = ?, checkSTATUS = ?, checkADMIN = ?, checkNOTE = ?, var1 = ?, var2 = ?, var3 = ?, var4 = ?, var5 = ?, var6 = ?, var7 = ?, var8 = ?, var9 = ?, var0 = ?, rec = ?, check1 = ?, check2 = ?, c2wExport = ?, c2wGrabbed = ?, presTimerValue = ?, vid = ?, rid = ?, sid = ?, uid = ? WHERE UploadID = ?

If i remember correctly, linq internally calls Count on List() which might trigger this.

I had the same problem. It was even worse since the update was deleting a collection attribute too.

The update doesn't occur when you use the criteria you've just created. In my case I do

Hibernate flushes the session before it executes the criteria. You can disable set by setting the flush mode of the session to either FlushMode.Never or FlushMode.Commit.

But there must be a reason (meaning modification) for the update. Have you double checked that you do not modify the entity in any way (interceptor, some setter logic, ...) ?

Yes, I've double-checked, I don't do any modification.

Anyway, FlushMode.MANUAL works great since I'm doing what's supposed to be a read-only transaction.

Thanks wolli!

Is it possible that you have null values in the db for these dates ?

<property name="C2wExport" column="c2wExport" type="DateTime" />
<property name="C2wGrabbed" column="c2wGrabbed" type="DateTime" />

and the properties are not nullable (DateTime?). In that case hibernate probably thinks that something has changed.

Wolli, you were right, the problem was in the setter. I had

instead of:


That probably happened in a too quick refactoring. Thank you for your help!