Database MySQL Change PROBLEM!!!! Help Please
Posted: Thu Mar 07, 2013 10:00 am
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.
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.