c# - NHibernate mysql 更新时出错，GenericADOException，服务器错误代码 1064

Question

通过 mysql 更新时出现异常，同样的代码适用于 microsoft sql。

mysql服务器版本5.6.28

string connectionString = "Server=xxxx.is;Database=thebase_beta;Uid=userman1;Pwd=notherealpassword;sslmode=none";代码:

var cfg = new NHibernate.Cfg.Configuration();
cfg.DataBaseIntegration(x =>
{
    x.ConnectionString = connectionString;
    x.Driver<NHibernate.Driver.MySqlDataDriver>();
    x.Dialect<NHibernate.Dialect.MySQLDialect>();
});
cfg.AddAssembly(cfg.GetType().Assembly);

var mapper = new ModelMapper();
mapper.AddMappings(Assembly.GetExecutingAssembly().GetExportedTypes());
HbmMapping domainMapping = mapper.CompileMappingForAllExplicitlyAddedEntities();
cfg.AddMapping(domainMapping);
cfg.SetProperty(NHibernate.Cfg.Environment.ShowSql, "true");
var sefact = cfg.BuildSessionFactory();     
using (var session = sefact.OpenSession())  
    using (var tx = session.BeginTransaction())
    {           
        var t = session.Query<oc_setting>()
           .Where(c => c.store_id> 0).First();//This works, the first record is fetched.               
        t.key = "fk";
        session.Save(t); 
        tx.Commit();//Error is thrown here.
    }

异常详细信息:

Message:
could not update: [UserQuery+oc_setting#18026][SQL: UPDATE oc_setting SET store_id = ?, code = ?, key = ?, value = ?, serialized = ? WHERE setting_id = ?] 
SqlString
UPDATE oc_setting SET store_id = ?, code = ?, key = ?, value = ?, serialized = ? WHERE setting_id = ? 

Innerexception (MySqlException):
Message:
    You 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 = 'fk', value = '1', serialized = 0 WHERE setting_id = 18026' at line 1

Server Error Code 1064 
    actual-sql-query UPDATE oc_setting SET store_id = ?p0, code = ?p1, key = ?p2, value = ?p3, serialized = ?p4 WHERE setting_id = ?p5 

这是 NHibernate 记录的 sql 输出:

NHibernate: 
UPDATE oc_setting SET store_id = ?p0, code = ?p1, key = ?p2, value = ?p3, serialized = ?p4 WHERE setting_id = ?p5;?p0 = 1 [Type: Int32 (0:0:0)], ?p1 = 'custom_email_templates' [Type: String (22:0:0)], ?p2 = 'fk' [Type: String (2:0:0)], ?p3 = '1' [Type: String (1:0:0)], ?p4 = 0 [Type: Int32 (0:0:0)], ?p5 = 18026 [Type: Int32 (0:0:0)]

如果我执行替换 sql 控制台中的参数的命令，它就会起作用。

映射代码:

public partial class OcSettingMap : ClassMapping<oc_setting>
{
    public OcSettingMap()
    {   
        Table("oc_setting");
        Lazy(false);
        Id(x => x.setting_id, map => { map.Column("setting_id"); map.Generator(Generators.Assigned); });
        Property(x => x.store_id, map => { map.Column("store_id"); map.NotNullable(true); });
        Property(x => x.code, map => { map.Column("code"); map.NotNullable(true); });
        Property(x => x.key, map => { map.Column("key"); map.NotNullable(true); });
        Property(x => x.value, map => { map.Column("value"); map.NotNullable(true); });
        Property(x => x.serialized, map => { map.Column("serialized"); map.NotNullable(true); });
    }
}
public partial class oc_setting
{
    public int setting_id { get; set; }
    [Required]
    public int store_id { get; set; }
    [Required]
    public string code { get; set; }
    [Required]
    public string key { get; set; }
    [Required]
    public string value { get; set; }
    [Required]
    public int serialized { get; set; }
}

我更新时也出错了。

不确定这是否重要，但这些是我的“用途”

NHibernate.Cfg、NHibernate.Mapping.ByCode、NHibernate.Mapping.ByCode.Conformist、Sytem.Data.Entity.ModelConfiguration、System.Net、System.Threading.Tasks、FluentNHiber、ate.Mapping、NHibernate.Cfg.MappingSchema ,System.ComponentModel.DataAnnotations,N,ibernate,NHibernate.DriverNHibernate.AdoNet

Answer 1

添加 SchemaMetadataUpdater.QuoteTableAndColumns(cfg);

这里:

cfg.SetProperty(NHibernate.Cfg.Environment.ShowSql, "true");
var sefact = cfg.BuildSessionFactory();     
SchemaMetadataUpdater.QuoteTableAndColumns(cfg); //here
using (var session = sefact.OpenSession())
...snip

解决了。