I cannot succeed in updating records on a MySQL database.

I get the following error:

[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '13' to parameter: 0
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '2005' to parameter: 1
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.StringType [(null)] <(null)> - binding 'test bartddjdj' to parameter: 2
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '2001' to parameter: 3
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '1' to parameter: 4
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.StringType [(null)] <(null)> - binding 'La Rasina' to parameter: 5
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '6' to parameter: 6
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '1' to parameter: 7
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.Type.Int32Type [(null)] <(null)> - binding '1' to parameter: 8
[1424] 2005-11-04 16:42:15,033 [2492] DEBUG NHibernate.SQL [(null)] <(null)> - UPDATE wines SET region = ?p0, year_drink = ?p1, notes = ?p2, year = ?p3, Color = ?p4, name = ?p5, amount = ?p6, country = ?p7 WHERE key = ?p8
[1424] 2005-11-04 16:42:15,193 [2492] DEBUG NHibernate.Impl.BatcherImpl [(null)] <(null)> - Closed IDbCommand, open IDbCommands :0
[1424] 2005-11-04 16:42:15,203 [2492] DEBUG NHibernate.Util.ADOExceptionReporter [(null)] <(null)> - could not update: [WineManager.Wines#1]
#42000You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'key = 1' at line 1


I have the .NET connector 1.0.6.

Anyone an idea in what direction I should search

ok, after a couple of evenings trial and error I managed to find the solution. I thought I'd share it here, maybe others are having similar problems.

First I noticed that I did not have foreign keys defined on my MySQL database. I thought that it might be interesting to have those defined, so I tried defining them. That did not work!

For some kind of reason the referencing and the referenced columns must have the same name. At least, when I did that I was able to define foreign keys. Can anyone confirm this? (I normally use Oracle and there it is not needed)

When I did that, and after changing the mapping files, it worked perfectly.

"key" is a reserved word in MySQL probably, you need to quote it in the mapping file.