Building the example (ant eg) the SQL of SchemaExport fails dramatically when I use the mysql database.

The build works fine when I try the example with hsqldb.

Is the mysql-jdbc-adaptor not as transparent as I hoped for? I'm I using the wrong version of mysql? Should I try an other database?

Greets,
Henk-Jan Castermans


Extra info:

Hibernate version: 3.05

Mysql version : 4.0.20-max

MySQL-AB JDBC Driver, version: mysql-connector-java-3.1.8

hibernate.properties:

hibernate.dialect org.hibernate.dialect.MySQLDialect
hibernate.dialect org.hibernate.dialect.MySQLInnoDBDialect
hibernate.dialect org.hibernate.dialect.MySQLMyISAMDialect
hibernate.connection.driver_class com.mysql.jdbc.Driver
hibernate.connection.url jdbc:mysql://localhost/hibernate
hibernate.connection.username xxxx
hibernate.connection.password xxxx


Error
[java] 23:20:57,145 DEBUG SchemaExport:161 - create table AuctionItem (
[java] id bigint not null auto_increment,
[java] seller bigint not null,
[java] description varchar(200) not null,
[java] ends datetime,
[java] condition integer,
[java] successfulBid bigint,
[java] primary key (id),
[java] unique (seller, description)
[java] ) comment 'An item that is being auctioned.'
[java] 23:20:57,151 ERROR SchemaExport:167 - Unsuccessful: create table AuctionItem (id bigint not null auto_increment, seller bigint not null, description varchar(200) not null, ends datetime, condition integer, successfulBid bigint, primary key (id), unique (seller, description)) comment 'An item that is being auctioned.'
[java] 23:20:57,152 ERROR SchemaExport:168 - 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 ''An item that is being auctioned.'' at line 1
[java] 23:20:57,156 DEBUG SchemaExport:161 - create table AuctionUser (
[java] id bigint not null auto_increment,
[java] userName varchar(10) not null,
[java] `password` varchar(15) not null,
[java] email varchar(255),
[java] firstName varchar(50) not null,
[java] `initial` char(1),
[java] lastName varchar(50) not null,
[java] primary key (id),
[java] unique (userName)
[java] ) comment 'Users may bid for or sell auction items.'
[java] 23:20:57,163 ERROR SchemaExport:167 - Unsuccessful: create table AuctionUser (id bigint not null auto_increment, userName varchar(10) not null, `password` varchar(15) not null, email varchar(255), firstName varchar(50) not null, `initial` char(1), lastName varchar(50) not null, primary key (id), unique (userName)) comment 'Users may bid for or sell auction items.'
[java] 23:20:57,163 ERROR SchemaExport:168 - 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 ''Users may bid for or sell auction items.'' at line 1

Hi Henk-Jan
I had exactly the same problem last week. Try with MySQL 4.1.12 and the connector you mentionend.
It will work fine.

greets,
cinqq

I think you also have to choose a dialect, now you specify it three times.
I'm using:

hibernate.dialect org.hibernate.dialect.MySQLInnoDBDialect

And you can specify:

#hibernate.connection.url jdbc:mysql:///database

Thanks guys, for the suggestions, but I found out what the real problem was.

It appears that MySQL has a problem with the "comment" - tag in the produced sql:
The log produced:
[java] 20:35:45,328 ERROR SchemaExport:167 - Unsuccessful: create table AuctionUser (id bigint not null auto_increment, userName varchar(10) not null, `password` varchar(15) not null, email varchar(255), firstName varchar(50) not null, `initial` char(1), lastName varchar(50) not null, primary key (id), unique (userName)) comment 'Users may bid for or sell auction items.' type=MyISAM
[java] 20:35:45,329 ERROR SchemaExport:168 - 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 ''Users may bid for or sell auction items.' type=MyISAM' at line


After editing the the following files

eg/org/hibernate/auction/AuctionItem.hbm.xml
eg/org/hibernate/auction/User.hbm.xml
eg/org/hibernate/auction/Bid.hbm.xml

and removing the "<comment>blabla</comment> tags in the xml-files, the build ran perfectly...


Greets,
Henk

I think the comment generation for MySQL is supposed to be:

CREATE TABLE foo (....) comment='string';

But, instead, it seems that Hibernate generates:

CREATE TABLE foo (....) comment 'string';

The equals sign replaced with a space. I think that's the problem. At least when I manually corrected this and ran the resultant SQL directly it worked fine. Works with MySQL 4.0 and 4.1

The space-less form does work in 4.1, but not in previous versions, so I already added = in CVS.