MySql schema generation with NHibernate

Question

I've used following code to generate database creation script

Configuration configuration = new Configuration();
configuration.Configure();
SchemaExport schemaExport = new SchemaExport(configuration);
using(TextWriter stringWriter = new StreamWriter("create.sql"))
{
   schemaExport.Execute(false, false, false, true, null, stringWriter); 
}

and sql, it generates worked well with MS SQL. When I had to move to MySql I've changed NHibernate configuration to:

<hibernate-configuration xmlns="urn:nhibernate-configuration-2.2">
<session-factory>
  <property name="connection.provider">NHibernate.Connection.DriverConnectionProvider</property>
  <property name="connection.driver_class">NHibernate.Driver.MySqlDataDriver</property>
  <property name="connection.connection_string">Server=localhost;Database=db;User ID=root;Password=sa;</property>
  <property name="dialect">NHibernate.Dialect.MySQLDialect</property>
</session-factory>

but sql generated this time does't execute in MySQL Query Browser because of errors. Here's sample of mysql creation script generated by Nhibernate:

alter table Order  drop foreign key FK_User_Order
alter table OrderItem  drop foreign key FK_Order_Item
alter table Item  drop foreign key FK742DC178AD99756A

drop table if exists User
drop table if exists Order
drop table if exists OrderItem
drop table if exists Item
drop table if exists Category
create table User (
  Id INTEGER NOT NULL AUTO_INCREMENT,
   role INTEGER not null,
   is_active TINYINT(1) not null,
   contact_name VARCHAR(100),
   phone VARCHAR(100),
   address VARCHAR(100),
   email VARCHAR(100) not null,
   name VARCHAR(100) not null,
   password_hash LONGBLOB not null,
   login VARCHAR(100) not null unique,
   is_new TINYINT(1) not null,
   price_type INTEGER not null,
   access_id INTEGER not null,
   primary key (Id)

I've got following error when trying to execute it

Script line: 2 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 'Order drop foreign key FK_User_Order

alter table OrderItem drop foreign k' at line 1

Any ideas why Nhiberante-generated sql won't work?

Answer 1

schemaExport.SetDelimiter(";"); if it needs to be delimited (ie to run them as seperate statements

Also, is Order a keyword? can you remap the order to Orders in the database, to avoid the keyword conflict?