SQL errors running Day 1 example using Hibernate 3.0.5 and MySQL 5.0.15. Same result with any MySQL dialect configured in etc/hibernate.properties. I noticed in documentation "Databases supported by the Hibernate Team" (now http://www.hibernate.org/260.html) that only MySQL up to 4.1 is supported. Should I upgrade to 3.1? Will 3.0.5 ever support MySQL 5.0?

Thanks!

Hibernate version: 3.0.5

Name and version of the database you are using: MySQL 5.0.15 (on Windows XP)

Ant log, with sql shown:

[java] 01:10:36,483 INFO Environment:464 - Hibernate 3.0.5
[java] 01:10:36,503 INFO Environment:482 - loaded properties from resource hibernate.properties: {hibernate.use_sql_comments=true, hibernate.connection.driver_class=com.mysql.jdbc.Driver, hibernate.cglib.use_reflection_optimizer=true, hibernate.cache.provider_class=org.hibernate.cache.HashtableCacheProvider, hibernate.max_fetch_depth=1, hibernate.dialect=org.hibernate.dialect.MySQLDialect, hibernate.jdbc.use_streams_for_binary=true, hibernate.query.substitutions=true 1, false 0, yes 'Y', no 'N', hibernate.proxool.pool_alias=pool1, hibernate.connection.username=wes, hibernate.cache.region_prefix=hibernate.test, hibernate.connection.url=jdbc:mysql:///test, hibernate.show_sql=true, hibernate.connection.password=****, hibernate.jdbc.batch_versioned_data=true, hibernate.connection.pool_size=1}
[java] 01:10:36,503 INFO Environment:509 - using java.io streams to persist binary types
[java] 01:10:36,503 INFO Environment:510 - using CGLIB reflection optimizer
[java] 01:10:36,503 INFO Environment:540 - using JDK 1.4 java.sql.Timestamp handling
[java] 01:10:36,583 INFO Configuration:464 - Mapping resource: org/hibernate/auction/AuctionItem.hbm.xml
[java] 01:10:37,104 INFO HbmBinder:260 - Mapping class: org.hibernate.auction.AuctionItem -> AuctionItem
[java] 01:10:37,224 INFO Configuration:464 - Mapping resource: org/hibernate/auction/Bid.hbm.xml
[java] 01:10:37,284 INFO HbmBinder:260 - Mapping class: org.hibernate.auction.Bid -> Bid
[java] 01:10:37,334 INFO HbmBinder:737 - Mapping subclass: org.hibernate.auction.BuyNow -> Bid
[java] 01:10:37,334 INFO Configuration:464 - Mapping resource: org/hibernate/auction/User.hbm.xml
[java] 01:10:37,384 INFO HbmBinder:260 - Mapping class: org.hibernate.auction.User -> AuctionUser
[java] 01:10:37,394 INFO Configuration:875 - processing extends queue
[java] 01:10:37,394 INFO Configuration:879 - processing collection mappings
[java] 01:10:37,394 INFO HbmBinder:2041 - Mapping collection: org.hibernate.auction.AuctionItem.bids -> Bid
[java] 01:10:37,404 INFO HbmBinder:2041 - Mapping collection: org.hibernate.auction.User.bids -> Bid
[java] 01:10:37,404 INFO HbmBinder:2041 - Mapping collection: org.hibernate.auction.User.auctions -> AuctionItem
[java] 01:10:37,404 INFO Configuration:888 - processing association property references
[java] 01:10:37,404 INFO Configuration:917 - processing foreign key constraints
[java] 01:10:37,605 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 01:10:37,605 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 01:10:37,605 INFO DriverManagerConnectionProvider:45 - autocommit mode: false
[java] 01:10:37,615 INFO DriverManagerConnectionProvider:80 - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql:///test
[java] 01:10:37,615 INFO DriverManagerConnectionProvider:86 - connection properties: {user=wes, password=****}
[java] 01:10:37,955 INFO SettingsFactory:77 - RDBMS: MySQL, version: 5.0.15
[java] 01:10:37,955 INFO SettingsFactory:78 - JDBC driver: MySQL-AB JDBC Driver, version: mysql-connector-java-3.1.11 ( $Date: 2005-09-21 18:20:03 +0000 (Wed, 21 Sep 2005) $, $Revision: 4287 $ )
[java] 01:10:37,985 INFO Dialect:92 - Using dialect: org.hibernate.dialect.MySQLDialect
[java] 01:10:37,995 INFO TransactionFactoryFactory:31 - Using default transaction strategy (direct JDBC transactions)
[java] 01:10:37,995 INFO TransactionManagerLookupFactory:33 - No TransactionManagerLookup configured (in JTA environment, use of read-write or transactional second-level cache is not recommended)
[java] 01:10:37,995 INFO SettingsFactory:125 - Automatic flush during beforeCompletion(): disabled
[java] 01:10:37,995 INFO SettingsFactory:129 - Automatic session close at end of transaction: disabled
[java] 01:10:37,995 INFO SettingsFactory:136 - JDBC batch size: 15
[java] 01:10:37,995 INFO SettingsFactory:139 - JDBC batch updates for versioned data: enabled
[java] 01:10:37,995 INFO SettingsFactory:144 - Scrollable result sets: enabled
[java] 01:10:37,995 INFO SettingsFactory:152 - JDBC3 getGeneratedKeys(): enabled
[java] 01:10:38,005 INFO SettingsFactory:160 - Connection release mode: null
[java] 01:10:38,005 INFO SettingsFactory:184 - Maximum outer join fetch depth: 1
[java] 01:10:38,005 INFO SettingsFactory:187 - Default batch fetch size: 1
[java] 01:10:38,005 INFO SettingsFactory:191 - Generate SQL with comments: enabled
[java] 01:10:38,005 INFO SettingsFactory:195 - Order SQL updates by primary key: disabled
[java] 01:10:38,005 INFO SettingsFactory:334 - Query translator: org.hibernate.hql.ast.ASTQueryTranslatorFactory
[java] 01:10:38,015 INFO ASTQueryTranslatorFactory:21 - Using ASTQueryTranslatorFactory
[java] 01:10:38,015 INFO SettingsFactory:203 - Query language substitutions: {no='N', true=1, yes='Y', false=0}
[java] 01:10:38,015 INFO SettingsFactory:209 - Second-level cache: enabled
[java] 01:10:38,015 INFO SettingsFactory:213 - Query cache: disabled
[java] 01:10:38,015 INFO SettingsFactory:321 - Cache provider: org.hibernate.cache.HashtableCacheProvider
[java] 01:10:38,015 INFO SettingsFactory:228 - Optimize cache for minimal puts: disabled
[java] 01:10:38,015 INFO SettingsFactory:233 - Cache region prefix: hibernate.test
[java] 01:10:38,015 INFO SettingsFactory:237 - Structured second-level cache entries: disabled
[java] 01:10:38,025 INFO SettingsFactory:257 - Echoing all SQL to stdout
[java] 01:10:38,025 INFO SettingsFactory:261 - Statistics: disabled
[java] 01:10:38,025 INFO SettingsFactory:265 - Deleted entity synthetic identifier rollback: disabled
[java] 01:10:38,025 INFO SettingsFactory:279 - Default entity-mode: pojo
[java] 01:10:38,065 INFO SessionFactoryImpl:152 - building session factory
[java] 01:10:38,556 INFO SessionFactoryObjectFactory:82 - Not binding factory to JNDI, no JNDI name configured
[java] 01:10:38,556 INFO Dialect:92 - Using dialect: org.hibernate.dialect.MySQLDialect
[java] 01:10:38,566 INFO Configuration:875 - processing extends queue
[java] 01:10:38,566 INFO Configuration:879 - processing collection mappings
[java] 01:10:38,566 INFO Configuration:888 - processing association property references
[java] 01:10:38,566 INFO Configuration:917 - processing foreign key constraints
[java] 01:10:38,566 INFO Configuration:875 - processing extends queue
[java] 01:10:38,566 INFO Configuration:879 - processing collection mappings
[java] 01:10:38,566 INFO Configuration:888 - processing association property references
[java] 01:10:38,566 INFO Configuration:917 - processing foreign key constraints
[java] 01:10:38,566 INFO SchemaExport:113 - Running hbm2ddl schema export
[java] 01:10:38,566 INFO SchemaExport:129 - exporting generated schema to database
[java] 01:10:38,566 INFO DriverManagerConnectionProvider:41 - Using Hibernate built-in connection pool (not for production use!)
[java] 01:10:38,566 INFO DriverManagerConnectionProvider:42 - Hibernate connection pool size: 1
[java] 01:10:38,576 INFO DriverManagerConnectionProvider:45 - autocommit mode: false
[java] 01:10:38,576 INFO DriverManagerConnectionProvider:80 - using driver: com.mysql.jdbc.Driver at URL: jdbc:mysql:///test
[java] 01:10:38,576 INFO DriverManagerConnectionProvider:86 - connection properties: {user=wes, password=****}
[java] 01:10:38,606 DEBUG SchemaExport:143 - alter table AuctionItem drop foreign key FK522A9BD61107FE9B
[java] 01:10:38,626 DEBUG SchemaExport:149 - Unsuccessful: alter table AuctionItem drop foreign key FK522A9BD61107FE9B
[java] 01:10:38,626 DEBUG SchemaExport:150 - Table 'test.auctionitem' doesn't exist
[java] 01:10:38,626 DEBUG SchemaExport:143 - alter table AuctionItem drop foreign key FK522A9BD6F65B1FAF
[java] 01:10:38,626 DEBUG SchemaExport:149 - Unsuccessful: alter table AuctionItem drop foreign key FK522A9BD6F65B1FAF
[java] 01:10:38,626 DEBUG SchemaExport:150 - Table 'test.auctionitem' doesn't exist
[java] 01:10:38,626 DEBUG SchemaExport:143 - alter table Bid drop foreign key FK104DDEE6E7E98
[java] 01:10:38,756 DEBUG SchemaExport:143 - alter table Bid drop foreign key FK104DDF43A3910
[java] 01:10:38,837 DEBUG SchemaExport:143 - drop table if exists AuctionItem
[java] 01:10:38,837 DEBUG SchemaExport:143 - drop table if exists AuctionUser
[java] 01:10:38,847 DEBUG SchemaExport:143 - drop table if exists Bid
[java] 01:10:38,847 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] 01:10:38,847 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] 01:10:38,847 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 'condition integer, successfulBid bigint, primary key (id), unique (seller, descr' at line 1
[java] 01:10:38,857 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] 01:10:38,917 DEBUG SchemaExport:161 - create table Bid (
[java] id bigint not null auto_increment,
[java] isBuyNow char(1) not null comment 'Y if a "buy now", N if a regular bid.',
[java] item bigint not null,
[java] amount float not null,
[java] `datetime` datetime not null,
[java] bidder bigint not null,
[java] primary key (id),
[java] unique (item, amount)
[java] ) comment 'A bid or "buy now" for an item.'
[java] 01:10:38,977 DEBUG SchemaExport:161 - alter table AuctionItem
[java] add index FK522A9BD61107FE9B (seller),
[java] add constraint FK522A9BD61107FE9B
[java] foreign key (seller)
[java] references AuctionUser (id)
[java] 01:10:38,977 ERROR SchemaExport:167 - Unsuccessful: alter table AuctionItem add index FK522A9BD61107FE9B (seller), add constraint FK522A9BD61107FE9B foreign key (seller) references AuctionUser (id)
[java] 01:10:38,977 ERROR SchemaExport:168 - Table 'test.auctionitem' doesn't exist
[java] 01:10:38,977 DEBUG SchemaExport:161 - alter table AuctionItem
[java] add index FK522A9BD6F65B1FAF (successfulBid),
[java] add constraint FK522A9BD6F65B1FAF
[java] foreign key (successfulBid)
[java] references Bid (id)
[java] 01:10:38,977 ERROR SchemaExport:167 - Unsuccessful: alter table AuctionItem add index FK522A9BD6F65B1FAF (successfulBid), add constraint FK522A9BD6F65B1FAF foreign key (successfulBid) references Bid (id)
[java] 01:10:38,977 ERROR SchemaExport:168 - Table 'test.auctionitem' doesn't exist
[java] 01:10:38,977 DEBUG SchemaExport:161 - alter table Bid
[java] add index FK104DDEE6E7E98 (item),
[java] add constraint FK104DDEE6E7E98
[java] foreign key (item)
[java] references AuctionItem (id)
[java] 01:10:39,037 DEBUG SchemaExport:161 - alter table Bid
[java] add index FK104DDF43A3910 (bidder),
[java] add constraint FK104DDF43A3910
[java] foreign key (bidder)
[java] references AuctionUser (id)
[java] 01:10:39,097 INFO SchemaExport:173 - schema export complete
[java] 01:10:39,097 INFO DriverManagerConnectionProvider:147 - cleaning up connection pool: jdbc:mysql:///test
[java] 01:10:39,097 INFO SessionFactoryImpl:379 - Checking 0 named queries
[java] Setting up some test data
[java] Hibernate: /* insert org.hibernate.auction.User */ insert into AuctionUser (userName, `password`, email, firstName, `initial`, lastName) values (?, ?, ?, ?, ?, ?)
[java] Hibernate: /* insert org.hibernate.auction.User */ insert into AuctionUser (userName, `password`, email, firstName, `initial`, lastName) values (?, ?, ?, ?, ?, ?)
[java] Hibernate: /* insert org.hibernate.auction.User */ insert into AuctionUser (userName, `password`, email, firstName, `initial`, lastName) values (?, ?, ?, ?, ?, ?)
[java] Hibernate: /* insert org.hibernate.auction.AuctionItem */ insert into AuctionItem (seller, description, ends, condition, successfulBid) values (?, ?, ?, ?, ?)
[java] 01:10:39,247 WARN JDBCExceptionReporter:71 - SQL Error: 1064, SQLState: 42000
[java] 01:10:39,247 ERROR JDBCExceptionReporter:72 - 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 'condition, successfulBid) values (1, 'auction item 0', '2005-10-27 01:10:39', 2,' at line 1
[java] Exception in thread "main" org.hibernate.exception.SQLGrammarException: could not insert: [org.hibernate.auction.AuctionItem]
[java] at org.hibernate.exception.ErrorCodeConverter.convert(ErrorCodeConverter.java:70)
[java] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:43)
[java] at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1777)
[java] at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:2178)
[java] at org.hibernate.action.EntityIdentityInsertAction.execute(EntityIdentityInsertAction.java:34)
[java] at org.hibernate.engine.ActionQueue.execute(ActionQueue.java:239)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSaveOrReplicate(AbstractSaveEventListener.java:240)
[java] at org.hibernate.event.def.AbstractSaveEventListener.performSave(AbstractSaveEventListener.java:160)
[java] at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:95)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:184)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:173)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:96)
[java] at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:69)
[java] at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:468)
[java] at org.hibernate.engine.Cascades$5.cascade(Cascades.java:154)
[java] at org.hibernate.engine.Cascades.cascadeAssociation(Cascades.java:771)
[java] at org.hibernate.engine.Cascades.cascade(Cascades.java:720)
[java] at org.hibernate.engine.Cascades.cascadeCollection(Cascades.java:895)
[java] at org.hibernate.engine.Cascades.cascadeAssociation(Cascades.java:792)
[java] at org.hibernate.engine.Cascades.cascade(Cascades.java:720)
[java] at org.hibernate.engine.Cascades.cascade(Cascades.java:847)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.cascadeOnFlush(AbstractFlushingEventListener.java:121)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.prepareEntityFlushes(AbstractFlushingEventListener.java:112)
[java] at org.hibernate.event.def.AbstractFlushingEventListener.flushEverythingToExecutions(AbstractFlushingEventListener.java:59)
[java] at org.hibernate.event.def.DefaultFlushEventListener.onFlush(DefaultFlushEventListener.java:26)
[java] at org.hibernate.impl.SessionImpl.flush(SessionImpl.java:730)
[java] at org.hibernate.impl.SessionImpl.managedFlush(SessionImpl.java:324)
[java] at org.hibernate.transaction.JDBCTransaction.commit(JDBCTransaction.java:86)
[java] at org.hibernate.auction.Main.createTestAuctions(Main.java:348)
[java] at org.hibernate.auction.Main.main(Main.java:369)
[java] Caused by: java.sql.SQLException: 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 'condition, successfulBid) values (1, 'auction item 0', '2005-10-27 01:10:39', 2,' at line 1
[java] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2926)
[java] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1571)
[java] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1666)
[java] at com.mysql.jdbc.Connection.execSQL(Connection.java:2978)
[java] at com.mysql.jdbc.Connection.execSQL(Connection.java:2902)
[java] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:933)
[java] at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1162)
[java] at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1079)
[java] at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1064)
[java] at org.hibernate.persister.entity.BasicEntityPersister.insert(BasicEntityPersister.java:1759)
[java] ... 27 more

google can solve 99.99% of problems.
http://forums.mysql.com/read.php?101,40643,40643

Same result with Hibernate 3.1 and after dropping any tables in the test database.

rename "condition" field or quote it in mapping file

Thanks for the tip.

Quoting the field in the mapping file AuctionItem.hbm.xml did not work; hibernate was unable to unquote and find the field.

Renaming the field to "conditions" worked. That means:

- updating property in AuctionItem.hbm.xml
- updating field name in AuctionItem.java
- updating method names (setConditions(int) and getConditions()) in Main.java and AuctionItem.java

I just hit this and solved by setting the column attribute of the property element in the AuctionItem.hbm.xml file. What I mean is that I changed the property element entry for condition from


to


That at least allowed the build run via 'ant eg' to complete. So this works in theory. If I find that the sample app fails 'in practice' as I work through the sample I'll post another reply later.

In case someone is thinking of using desc as the column name. You will hit the same problem

Thanks for the tip.

HI,

Thanks JasonHeath for your usefull tip.....being a learner I was having hell like time with the query and after lots of head hitting finally hit the Google and found your tip, it solved my problem....

As suggested by you i have changed the AuctionItem.hbm.xml file from "<property name="condition"/>" to "<property name="condition" column="`condition`"/>" and my ant went through...

Thank you once more...

Siuli