Hi,
I just started using Hibernate and I have some issue with the schema it generate for MySQL.

Using the latest Hibernate and MySQL 5 on windows, I'm getting an invalid create table statement.

The mapping file was generated using the latest Xdoclet.

Any help will be appreciated
Simon

Hibernate 3.1.2

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

<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping
>
<class
name="pl.nask.eiana.objects.Certificate"
table="CERTIFICATES"
>

<id
name="objId"
column="OBJ_ID"
type="java.lang.Long"
>
<generator class="native">
<!--
To add non XDoclet generator parameters, create a file named
hibernate-generator-params-Certificate.xml
containing the additional parameters and place it in your merge dir.
-->
</generator>
</id>

<property
name="publicKey"
type="java.lang.String"
update="true"
insert="true"
column="PUBLICKEY"
length="4096"
not-null="false"
unique="false"
/>

<property
name="publicKeyID"
type="java.lang.Integer"
update="true"
insert="true"
column="PUBLICKEYID"
not-null="true"
unique="false"
/>

<property
name="ver"
type="long"
update="true"
insert="true"
column="VER"
not-null="false"
unique="false"
/>

<property
name="crDate"
type="java.sql.Timestamp"
update="true"
insert="true"
column="CR_DATE"
not-null="true"
unique="false"
/>

<property
name="upDate"
type="java.sql.Timestamp"
update="true"
insert="true"
column="UP_DATE"
not-null="false"
unique="false"
/>

<!--
To add non XDoclet property mappings, create a file named
hibernate-properties-Certificate.xml
containing the additional properties and place it in your merge dir.
-->

</class>

</hibernate-mapping>


MySQL 5.0.18-nt

create table CERTIFICATES (OBJ_ID bigint not null auto_increment, PUBLICKEY text, PUBLICKEYID integer not null, VER bigint, CR_DATE datetime not null, UP_DATE datetime, SYSTEMUSER bigint, index integer, primary key (OBJ_ID));

The error from MySQL is:
ERROR 1064 (42000): 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 'integer, primary key (OBJ_ID))' at line 1

OK, I found my problem and it is not related to hibernate.

INDEX is a reserved word in MySQL.

Simon

...as is KEY, and a few others.

Thanks for posting this the answer, as it helped quickly solve my problem when switching from testing with HSQLDB, to MySQL!