MySqlException in Nhibernate

Question

I have written a test mysql connection project in Nhibernate. this exception is showing..

    NHibernate.HibernateException: '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 'Character (Id VARCHAR(40) not null, Name
    VARCHAR(255), HealthPoints INTEGER, Man' at line 1'

The hibernate.cfg.xml file is

<?xml version="1.0" encoding="utf-8"?>

<hibernate-configuration  xmlns="urn:nhibernate-configuration-2.2" >
    <session-factory name="NHibernate.Test">
        <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
        <property name="connection.connection_string">
            Database=nhibernate;Data Source=localhost;User Id=ajoy;Password=ajoy
        </property>
        <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
    </session-factory>
</hibernate-configuration>

The model class is

namespace NhibernateTest.Domain
{
    public class Character
    {
        public virtual Guid Id { get; set; }
        public virtual string Name { get; set; }
        public virtual int HealthPoints { get; set; }
        public virtual int Mana { get; set; }
        public virtual string Profession { get; set; }
    }
}

The mapping xml is ..

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="NhibernateTest"
                   namespace="NhibernateTest.Domain">

  <class name="Character">
    <id name="Id">
      <generator class="guid" />
    </id>
    <property name="Name" />
    <property name="HealthPoints" />
    <property name="Mana" />
    <property name="Profession" />
  </class>

</hibernate-mapping>

My main class is

class Program
    {
        static void Main(string[] args)
        {
            LoadNHibernateCfg();

            /* CRUD */
            CharacterRepository repo = new CharacterRepository();

            //CREATE!
            var MikeAbyss = new Character { Name = "MikeAbyss", HealthPoints = 700, Mana = 10, Profession = "Knight" };
            repo.Add(MikeAbyss);

            //READ!
            Character mike = repo.GetCharacterByName("MikeAbyss");

            //UPDATE!
            mike.Name = "Mike";
            repo.Update(mike);

            //DELETE!
            repo.Delete(mike);

            Console.ReadKey();
        }

        public static void LoadNHibernateCfg()
        {
            var cfg = new Configuration();
            cfg.Configure();
            cfg.AddAssembly(typeof(Character).Assembly);
            SchemaExport schemaExport = new SchemaExport(cfg);
            schemaExport.SetDelimiter(";");
            schemaExport.Execute(true, true, false);

        }

I have tried without schemaExport.SetDelimiter(";"); but still the same error. Output in the console is

drop table if exists Character;

    create table Character (
        Id VARCHAR(40) not null,
       Name VARCHAR(255),
       HealthPoints INTEGER,
       Mana INTEGER,
       Profession VARCHAR(255),
       primary key (Id)
    ); 

Mysql version is 5.7.14. How can I resolve the issue?

Answer 1

One or more of your identifiers are probably reserved words. You need to quote them: define their db names surrounded with back-ticks.

By example, quoting Character and Name:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2"
                   assembly="NhibernateTest"
                   namespace="NhibernateTest.Domain">      
  <class name="Character" table="`Character`">
    <id name="Id">
      <generator class="guid" />
    </id>
    <property name="Name" column="`Name`" />
    <property name="HealthPoints" />
    <property name="Mana" />
    <property name="Profession" />
  </class>        
</hibernate-mapping>