Page 1 of 1

SQL syntax error at startup of site-admin-0.0.1-SNAPSHOT

Posted: Mon Nov 14, 2011 4:40 am
by Theo Schumacher
I have successfully deployed the site-admin from ecommerce.zip with mysql 5.1.56 database on glassfish 3.1.
I have populated the database with import.sql and checked that the data is effectively loaded.
When I launch http://localhost:8080/site-admin-0.0.1-SNAPSHOT I get an error from hibernate: (during request of login.jsp)

INFO: Hibernate: select top ? idgenerati0_.ID_TYPE as ID1_10_, idgenerati0_.BATCH_SIZE as BATCH2_10_, idgenerati0_.BATCH_START as BATCH3_10_, idgenerati0_.ID_MIN as ID4_10_, idgenerati0_.ID_MAX as ID5_10_, idgenerati0_.version as version10_ from BLC_ID_GENERATION idgenerati0_ where idgenerati0_.ID_TYPE=?
INFO: [ WARN] 09:40:06 JDBCExceptionReporter - SQL Error: 1064, SQLState: 42000

INFO: [ERROR] 09:40:06 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 '2 idgenerati0_.ID_TYPE as ID1_10_, idgenerati0_.BATCH_SIZE as BATCH2_10_, idgene' at line 1

WARNING: StandardWrapperValve[jsp]: PWC1406: Servlet.service() for servlet jsp threw exception
java.lang.RuntimeException: Unable to retrieve id range for org.broadleafcommerce.profile.core.domain.Customer
at org.broadleafcommerce.profile.core.service.IdGenerationServiceImpl.getCurrentIdRange(IdGenerationServiceImpl.java:88)

It seams that MySQL does not know the "select top" statement. I have tried with MySQLDialect, MySQL5Dialect and MySQLInnoDBDialect but this makes no difference. I have removed any reference to hsqldb in persistence-admin.xml and applicationContext-admin.xml and removed the dependency in pom.xml.

Any help appreciated

Thank you
Theo

Re: SQL syntax error at startup of site-admin-0.0.1-SNAPSHOT

Posted: Mon Nov 14, 2011 11:54 am
by jefffischer
There is a file name conflict in that ecommerce.zip. persistence-admin.xml is also used by the framework, so you're changes are getting ignored. Change the name of your persistence-admin.xml to something else and then update the reference to this file in your application context. After this change, the system will recognize your change to mysql and will use the mysql dialect that you have defined.

Re: SQL syntax error at startup of site-admin-0.0.1-SNAPSHOT

Posted: Tue Nov 15, 2011 11:59 am
by Theo Schumacher
I'am still struggling with the same problem. I have renamed the file persistence-admin.xml to mypersistence .xml and changed the applicationContext-admin.xml to reflect this change. But hibernate is still using hsqldb dialect.
When I remove the classpath:* or change it to classpath: glassfish complains not to find mypersistence.xml or ignores it. Deployment is not possible as well. If I leave the classpath:* , mypersistence is never taken into account.

I tried as well to move mypersistence.xml to /WEB-INF/. But again, glassfish ignores it.

The last status is for applicationContext-admin.xml:
<bean id="blPersistenceUnitManager"
class="org.broadleafcommerce.profile.extensibility.jpa.MergePersistenceUnitManager">
<property name="persistenceXmlLocations">
<list>
<value>/WEB-INF/mypersistence.xml</value>
</list>
</property>
<property name="dataSources">
<map>
<entry key="jdbc/web" value-ref="webDS"/>
<entry key="jdbc/webSecure" value-ref="webDS"/>
</map>
</property>
<property name="defaultDataSource" ref="webDS"/>
</bean>

<bean id="webDS" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">
<property name="driverClassName" value="com.mysql.jdbc.Driver" />
<property name="url" value="jdbc:mysql://localhost/broadleafcommerce" />
<property name="username" value="broadleaf" />
<property name="password" value="broadleaf" />
</bean>

and in /WEB-INF/mypersistence.xml
<persistence-unit name="blPU" transaction-type="RESOURCE_LOCAL">
<non-jta-data-source>jdbc/web</non-jta-data-source>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.hbm2ddl.auto" value="create-drop"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.show_sql" value="true"/>
</properties>
</persistence-unit>

<persistence-unit name="blSecurePU" transaction-type="RESOURCE_LOCAL">
<non-jta-data-source>jdbc/webSecure</non-jta-data-source>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.hbm2ddl.auto" value="update"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MySQLDialect"/>
<property name="hibernate.show_sql" value="false"/>
</properties>
</persistence-unit>

Any idea why this happens ?

Re: SQL syntax error at startup of site-admin-0.0.1-SNAPSHOT

Posted: Tue Nov 15, 2011 12:20 pm
by jefffischer
Post the contents of your web.xml. It may be a problem with your patchConfig. The elements listed there are merged in order - last one wins. You may have another config overwriting yours.

Re: SQL syntax error at startup of site-admin-0.0.1-SNAPSHOT

Posted: Wed Nov 16, 2011 10:22 am
by Theo Schumacher
You were right. I have changed:

<context-param>
<param-name>patchConfigLocation</param-name>
<param-value>
/WEB-INF/applicationContext-admin.xml
/WEB-INF/applicationContext-security.xml
classpath:/mycompany-applicationContext.xml
classpath:/bl-open-admin-applicationContext.xml
classpath:/bl-admin-applicationContext.xml
</param-value>
</context-param>

into

<context-param>
<param-name>patchConfigLocation</param-name>
<param-value>
classpath:/mycompany-applicationContext.xml
classpath:/bl-open-admin-applicationContext.xml
classpath:/bl-admin-applicationContext.xml
/WEB-INF/applicationContext-admin.xml
/WEB-INF/applicationContext-security.xml
</param-value>
</context-param>

I think I should have used mycompany-applicationContext.xml instead of changing applicationContext-admin.xml.
Thank you. I can use both parts now and will continue to investigate
Theo