I am relatively new to Hibernate and am using in conjuction with the Spring framework. I have loved the ease of use the pairing provides and was happy to complete my first project recently.
I developed the project locally using Eclipse running Tomcat 4.1 and MySQL 4.1.7 and connector/j 3.1 on Windows XP. Everything works like a charm and I was ready to deploy.
The target environment is running apache with Tomcat 4.0 and MySQL 4.0.22 with the same connector/j 3.1 on Unix. Everything there appeared to work fine except for one single piece. I've been searching high and low for any known issues with the MySqlDialect between MySQL versions and haven't found any.
I would appreciate any help on this issue as this will continue to be my development/target environment pairing unless there is no possible resolution. I am having trouble figuring out why just one find is erroring while all the other queries, saves, and updates work just fine. When I copy the generated SQL into a MySQL window it gives me the same error.
The details of the error follow below:
Hibernate version: 2.1.7
Mapping documents:
Code:
<class name="com.idxcel.cpa.bo.User" table="cpauser">
<id name="id">
<generator class="identity"/>
</id>
<property name="username"/>
<property name="password"/>
<property name="fname"/>
<property name="lname"/>
<property name="target" />
<property name="targetAchievedDate" column="target_achieved_date"/>
<set name="goals" table="user_goal" sort="natural" lazy="false">
<key column="user_id"/>
<many-to-many column="goal_id" class="com.idxcel.cpa.bo.Goal"/>
</set>
</class>
<class name="com.idxcel.cpa.bo.Goal" table="goal">
<id name="id">
<generator class="identity"/>
</id>
<property name="name"/>
<property name="points" type="integer"/>
</class>
<class name="com.idxcel.cpa.bo.GoalStatus" table="goal_status">
<id name="id">
<generator class="identity"/>
</id>
<property name="day" type="date"/>
<property name="achieved" type="boolean"/>
<many-to-one name="goal" class="com.idxcel.cpa.bo.Goal" column="goal_id"/>
</class>
Code:
List l = getHibernateTemplate().find("SELECT sum(gs.goal.points) " +
"FROM GoalStatus as gs, User as u " +
"WHERE gs.goal IN elements(u.goals) " +
"AND gs.achieved = true " +
"AND u = ?",u.getId(),Hibernate.INTEGER);
return (Integer) l.get(0);
Full stack trace of any exception that occurs:Code:
org.springframework.jdbc.BadSqlGrammarException: Bad SQL grammar [] in task 'Hibernate operation'; nested exception is 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 'select goals2_.goal_id from user_goal goals2_ where user1_.id=g
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 'select goals2_.goal_id from user_goal goals2_ where user1_.id=g
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2847)
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1531)
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1622)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2376)
at com.mysql.jdbc.Connection.execSQL(Connection.java:2297)
at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1860)
at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:1705)
at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:92)
at net.sf.hibernate.impl.BatcherImpl.getResultSet(BatcherImpl.java:89)
at net.sf.hibernate.loader.Loader.getResultSet(Loader.java:880)
at net.sf.hibernate.loader.Loader.doQuery(Loader.java:273)
at net.sf.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:138)
at net.sf.hibernate.loader.Loader.doList(Loader.java:1063)
at net.sf.hibernate.loader.Loader.list(Loader.java:1054)
at net.sf.hibernate.hql.QueryTranslator.list(QueryTranslator.java:854)
at net.sf.hibernate.impl.SessionImpl.find(SessionImpl.java:1554)
at net.sf.hibernate.impl.QueryImpl.list(QueryImpl.java:49)
at org.springframework.orm.hibernate.HibernateTemplate$25.doInHibernate(HibernateTemplate.java:641)
at org.springframework.orm.hibernate.HibernateTemplate.execute(HibernateTemplate.java:312)
at org.springframework.orm.hibernate.HibernateTemplate.find(HibernateTemplate.java:631)
at com.idxcel.cpa.dao.HibernateDao.getTotalForUser(HibernateDao.java:76)
at com.idxcel.cpa.controller.MainController.displaySummary(MainController.java:94)
at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
at java.lang.reflect.Method.invoke(Method.java:324)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.invokeNamedMethod(MultiActionController.java:352)
at org.springframework.web.servlet.mvc.multiaction.MultiActionController.handleRequestInternal(MultiActionController.java:305)
at org.springframework.web.servlet.mvc.AbstractController.handleRequest(AbstractController.java:128)
at org.springframework.web.servlet.mvc.SimpleControllerHandlerAdapter.handle(SimpleControllerHandlerAdapter.java:44)
at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:595)
at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:543)
at org.springframework.web.servlet.FrameworkServlet.serviceWrapper(FrameworkServlet.java:390)
at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:341)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:740)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:247)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at com.idxcel.filter.SecurityFilter.doFilter(SecurityFilter.java:57)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:213)
at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:193)
at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:256)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:191)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardContext.invoke(StandardContext.java:2422)
at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:180)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.valves.ErrorDispatcherValve.invoke(ErrorDispatcherValve.java:171)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:163)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:641)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:174)
at org.apache.catalina.core.StandardPipeline$StandardPipelineValveContext.invokeNext(StandardPipeline.java:643)
at org.apache.catalina.core.StandardPipeline.invoke(StandardPipeline.java:480)
at org.apache.catalina.core.ContainerBase.invoke(ContainerBase.java:995)
at org.apache.coyote.tomcat4.CoyoteAdapter.service(CoyoteAdapter.java:199)
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyoteHandler.java:324)
at org.apache.jk.common.HandlerRequest.invoke(HandlerRequest.java:395)
at org.apache.jk.common.ChannelSocket.invoke(ChannelSocket.java:673)
at org.apache.jk.common.ChannelSocket.processConnection(ChannelSocket.java:615)
at org.apache.jk.common.SocketConnection.runIt(ChannelSocket.java:786)
at org.apache.tomcat.util.threads.ThreadPool$ControlRunnable.run(ThreadPool.java:683)
at java.lang.Thread.run(Thread.java:534)
Name and version of the database you are using: MySQL 4.0.22
The generated SQL (show_sql=true):Code:
Hibernate: select sum(goal3_.points) as x0_0_ from goal_status goalstatus0_, cpauser user1_, goal goal3_ where goalstatus0_.goal_id=goal3_.id and ((goalstatus0_.goal_id IN(select goals2_.goal_id from user_goal goals2_ where user1_.id=goals2_.user_id))AND(goalstatus0_.achieved=true )AND(user1_.id=? ))
Debug level Hibernate log excerpt:Code:
2005-04-05 13:54:04,671 WARN [net.sf.hibernate.util.JDBCExceptionReporter] - <SQL Error: 1064, SQLState: 42000>
2005-04-05 13:54:04,671 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] - <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 'select goals2_.goal_id from user_goal goals2_ where user1_.id=g>
2005-04-05 13:54:04,673 WARN [net.sf.hibernate.util.JDBCExceptionReporter] - <SQL Error: 1064, SQLState: 42000>
2005-04-05 13:54:04,676 ERROR [net.sf.hibernate.util.JDBCExceptionReporter] - <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 'select goals2_.goal_id from user_goal goals2_ where user1_.id=g>
2005-04-05 13:54:04,678 INFO [com.idxcel.cpa.controller.MainController] - <Trying to find handler for exception class [org.springframework.jdbc.BadSqlGrammarException]>
Login
FAQ
Search
