Broadleaf Commerce Discussion Forums

Good Morning Guys,I need your help with a problem Im Having now about migrating to MySQL database.

I did all what the tutorial said about switching to MySQL db -> http://docs.broadleafcommerce.org/curre ... orial.html

I did this:

1) At "Maven Parents" Folder -> "DemoSite" I changed the pom.xml

---Added these lines

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.21</version>
<type>jar</type>
<scope>compile</scope>
</dependency>

---commented these lines

<!-- <dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
<version>2.2.4</version>
<type>jar</type>
<scope>compile</scope>
</dependency> -->

2) At "My BroadLeaf Site" admin and site projects, in pom.xml file

--- Added those lines

<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
</dependency>

--- Commented these ones

<!-- <dependency>
<groupId>org.hsqldb</groupId>
<artifactId>hsqldb</artifactId>
</dependency> -->

3) Did all what tutorial said about -> Change the JNDI resource in jetty-env.xml to match your MySQL installation. -> Replacing the existing properties with these ones

<New id="webDS" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg>jdbc/web</Arg>
<Arg>
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">com.mysql.jdbc.Driver</Set>
<Set name="url">jdbc:mysql://localhost:3306/ecommerce_db</Set>
<Set name="username">root</Set>
<Set name="password">1234</Set>
</New>
</Arg>
</New>

<New id="webSecureDS" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg>jdbc/secure</Arg>
<Arg>
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">com.mysql.jdbc.Driver</Set>
<Set name="url">jdbc:mysql://localhost:3306/ecommerce_db</Set>
<Set name="username">root</Set>
<Set name="password">1234</Set>
</New>
</Arg>
</New>

<New id="webStorageDS" class="org.eclipse.jetty.plus.jndi.Resource">
<Arg>jdbc/storage</Arg>
<Arg>
<New class="org.apache.commons.dbcp.BasicDataSource">
<Set name="driverClassName">com.mysql.jdbc.Driver</Set>
<Set name="url">jdbc:mysql://localhost:3306/ecommerce_db</Set>
<Set name="username">root</Set>
<Set name="password">1234</Set>
</New>
</Arg>
</New>

4) In common-shared.properties file, I commented the current HSQL lines and added the ones the tutorial said, looking like this

#blCMSStorage.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

#blSecurePU.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

#blPU.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blPU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

5) And finally I executed the ant process "jetty-demo"

Now then I get some previous errors like this one:

[artifact:mvn] [ERROR] 10:15:47 SchemaExport - Unsuccessful: alter table BLC_STATIC_ASSET add constraint FK9875FB05579FE59D foreign key (SANDBOX_ID) references BLC_SANDBOX
[artifact:mvn] [ERROR] 10:15:47 SchemaExport - Can't create table 'ecommerce_db.#sql-12e0_11' (errno: 150)

Before finally get this

[artifact:mvn] [ WARN] 09:41:51 JDBCExceptionReporter - SQL Error: 1146, SQLState: 42S02
[artifact:mvn] [ERROR] 09:41:51 JDBCExceptionReporter - Table 'ecommerce_db.blc_sku' doesn't exist
[artifact:mvn] [ERROR] 09:41:51 JobRunShell - Job DEFAULT.rebuildIndexJobDetail threw an unhandled Exception:
[artifact:mvn] org.springframework.scheduling.quartz.JobMethodInvocationFailedException: Invocation of method 'rebuildIndex' on target class [class $Proxy126] failed; nested exception is javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
[artifact:mvn] at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:320)
[artifact:mvn] at org.springframework.scheduling.quartz.QuartzJobBean.execute(QuartzJobBean.java:113)
[artifact:mvn] at org.quartz.core.JobRunShell.run(JobRunShell.java:202)
[artifact:mvn] at org.quartz.simpl.SimpleThreadPool$WorkerThread.run(SimpleThreadPool.java:525)
[artifact:mvn] Caused by: javax.persistence.PersistenceException: org.hibernate.exception.SQLGrammarException: could not execute query
[artifact:mvn] at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1389)
[artifact:mvn] at org.hibernate.ejb.AbstractEntityManagerImpl.convert(AbstractEntityManagerImpl.java:1317)
[artifact:mvn] at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:255)
[artifact:mvn] at org.hibernate.ejb.criteria.CriteriaQueryCompiler$3.getResultList(CriteriaQueryCompiler.java:253)
[artifact:mvn] at org.broadleafcommerce.core.catalog.dao.ProductDaoImpl.readAllActiveProducts(ProductDaoImpl.java:449)
[artifact:mvn] at org.broadleafcommerce.core.search.service.solr.SolrSearchServiceImpl.rebuildIndex(SolrSearchServiceImpl.java:119)
[artifact:mvn] at sun.reflect.GeneratedMethodAccessor49.invoke(Unknown Source)
[artifact:mvn] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[artifact:mvn] at java.lang.reflect.Method.invoke(Method.java:597)
[artifact:mvn] at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:318)
[artifact:mvn] at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
[artifact:mvn] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
[artifact:mvn] at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
[artifact:mvn] at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
[artifact:mvn] at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:202)
[artifact:mvn] at $Proxy126.rebuildIndex(Unknown Source)
[artifact:mvn] at sun.reflect.GeneratedMethodAccessor48.invoke(Unknown Source)
[artifact:mvn] at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
[artifact:mvn] at java.lang.reflect.Method.invoke(Method.java:597)
[artifact:mvn] at org.springframework.util.MethodInvoker.invoke(MethodInvoker.java:273)
[artifact:mvn] at org.springframework.scheduling.quartz.MethodInvokingJobDetailFactoryBean$MethodInvokingJob.executeInternal(MethodInvokingJobDetailFactoryBean.java:311)
[artifact:mvn] ... 3 more
[artifact:mvn] Caused by: org.hibernate.exception.SQLGrammarException: could not execute query
[artifact:mvn] at org.hibernate.exception.SQLStateConverter.convert(SQLStateConverter.java:92)
[artifact:mvn] at org.hibernate.exception.JDBCExceptionHelper.convert(JDBCExceptionHelper.java:66)
[artifact:mvn] at org.hibernate.loader.Loader.doList(Loader.java:2545)
[artifact:mvn] at org.hibernate.loader.Loader.listIgnoreQueryCache(Loader.java:2276)
[artifact:mvn] at org.hibernate.loader.Loader.list(Loader.java:2271)
[artifact:mvn] at org.hibernate.loader.hql.QueryLoader.list(QueryLoader.java:459)
[artifact:mvn] at org.hibernate.hql.ast.QueryTranslatorImpl.list(QueryTranslatorImpl.java:365)
[artifact:mvn] at org.hibernate.engine.query.HQLQueryPlan.performList(HQLQueryPlan.java:196)
[artifact:mvn] at org.hibernate.impl.SessionImpl.list(SessionImpl.java:1268)
[artifact:mvn] at org.hibernate.impl.QueryImpl.list(QueryImpl.java:102)
[artifact:mvn] at org.hibernate.ejb.QueryImpl.getResultList(QueryImpl.java:246)
[artifact:mvn] ... 21 more
[artifact:mvn] Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: Table 'ecommerce_db.blc_sku' doesn't exist
[artifact:mvn] at sun.reflect.GeneratedConstructorAccessor77.newInstance(Unknown Source)
[artifact:mvn] at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:27)
[artifact:mvn] at java.lang.reflect.Constructor.newInstance(Constructor.java:513)
[artifact:mvn] at com.mysql.jdbc.Util.handleNewInstance(Util.java:411)
[artifact:mvn] at com.mysql.jdbc.Util.getInstance(Util.java:386)
[artifact:mvn] at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1053)
[artifact:mvn] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4074)
[artifact:mvn] at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4006)
[artifact:mvn] at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2468)
[artifact:mvn] at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2629)
[artifact:mvn] at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2719)
[artifact:mvn] at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:2155)
[artifact:mvn] at com.mysql.jdbc.PreparedStatement.executeQuery(PreparedStatement.java:2318)
[artifact:mvn] at org.apache.commons.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
[artifact:mvn] at org.hibernate.jdbc.AbstractBatcher.getResultSet(AbstractBatcher.java:208)
[artifact:mvn] at org.hibernate.loader.Loader.getResultSet(Loader.java:1953)
[artifact:mvn] at org.hibernate.loader.Loader.doQuery(Loader.java:802)
[artifact:mvn] at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:274)
[artifact:mvn] at org.hibernate.loader.Loader.doList(Loader.java:2542)
[artifact:mvn] ... 29 more

----------------

I notice checking at the database I created, 147 tables has been created but with no data inside, also that Table 'ecommerce_db.blc_sku' has not been created.

I need your help guys, please, to tell me if Im doing something wrong or missed a step or why am I getting those errors trying to start the demo with my own MySQL DB.

Thanks in advance, Greetings.

It looks to me like your dialect is still incorrect. Search for blPU.hibernate.dialect in common.properties and development.properties. You may have it defined again and it could be overriding your change in common-shared.properties.

Also, execute 'drop database ecommerce_db' and 'create database ecommerce_db' to guarantee that you're starting from a clean state.

jefffischer wrote:It looks to me like your dialect is still incorrect. Search for blPU.hibernate.dialect in common.properties and development.properties. You may have it defined again and it could be overriding your change in common-shared.properties.

Also, execute 'drop database ecommerce_db' and 'create database ecommerce_db' to guarantee that you're starting from a clean state.

My File common.properties from "admin" folder is empty, about code that can be overriding common-shared, and says

# These settings override and append settings in:

# common-shared.properties

# Please see common-shared.properties for more information on how the properties files work

So, that file is not the problem,

common.properties from "site" folder has this properties only

web.defaultPageSize=15
web.maxPageSize=100

solr.source=solrEmbedded
solr.index.start.delay=5000
solr.index.repeat.interval=3600000

and development.properties has those options

blPU.hibernate.hbm2ddl.auto=create-drop
blPU.hibernate.hbm2ddl.import_files=/sql/load_admin_security.sql,\
/sql/load_admin_users.sql,\
/sql/load_code_tables.sql,\
/sql/load_table_sequences.sql,\
/sql/load_catalog_data.sql,\
/sql/load_content_structure.sql,\
/sql/load_content_data.sql

blCMSStorage.hibernate.hbm2ddl.auto=create-drop
blCMSStorage.hibernate.hbm2ddl.import_files=/sql/import_storage.sql

blSecurePU.hibernate.hbm2ddl.auto=update
solr.index.repeat.interval=10000

So I dont see anything that can be overriding the options of common-shared.properties.

I dont think the dialect is incorrect.

I ended up with the same problem, don't know why but the admin module is picking up hsql as dialect.
Override the dialect in local.properties and development.properties of admin module with org.hibernate.dialect.MySQL5InnoDBDialect.

Cheers!!
Prakhar