Migrating from in memory database to MySQL Broadleaf 2.0

[aazzolini]

Let's walk through how databases are configured.

The first step is to configure your JNDI datasource in your application server. We're using JNDI because it allows you to specify username/password and database configuration details on the application server itself, which keeps sensitive information outside source control.

As I'm sure you're aware, we use Jetty by default. With Jetty, JNDI is configured in jetty-env.xml (the Tomcat equivalent is context.xml). There are three definitions in this file. Let's take a look at one of them:

Code: Select all

<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">org.hsqldb.jdbcDriver</Set>
            <Set name="url">jdbc:hsqldb:hsql://localhost/broadleaf</Set>
            <Set name="username">sa</Set>
            <Set name="password"></Set>
        </New>
    </Arg>
</New>


Notice how we're defining a reference to an HSQL database, and we're calling it "jdbc/web". From there, this definition gets picked up by applicationContext-datasource.xml. Notice how we trigger a JNDI lookup with this line:

Code: Select all

<jee:jndi-lookup id="webDS" jndi-name="jdbc/web"/>


After we have it looked up, we need to tell Broadleaf about it. That's done in applicationContext.xml with this snippet:

Code: Select all

<bean id="blMergedDataSources" class="org.springframework.beans.factory.config.MapFactoryBean">
    <property name="sourceMap">
        <map>
            <entry key="jdbc/web" value-ref="webDS"/>
        </map>
    </property>
</bean>


Finally, you have to set the Hibernate dialect for the environment you're running in, which is done in the specific environment.properties file. By default, common-shared.properties defines the HSQL dialect for all persistence units:

Code: Select all

blPU.hibernate.dialect=org.hibernate.dialect.HSQLDialect


We know that we need to use the blPU key to map to our datasource because it's defined in persistence.xml:

Code: Select all

<persistence-unit name="blPU" transaction-type="RESOURCE_LOCAL">
    <non-jta-data-source>jdbc/web</non-jta-data-source>
</persistence-unit>


[kava]

Hello,
Im a newbie.
I downloaded DemoSite-2.0.1-GA-eclipse-workspace and I followed all the steps in the guide lines. As soon as I use Hypersonic DB everything is ok.
When I try to use Mysql (followed ALL the steps described in the guide) I got some problems.

I launch jetty-demo ant task, the task tries to create the schema but I have the same problems mentioned by Saman at the beginning of this post.
I changed MySql server (now I am at th latest)....but still have problems in MySql Syntax creating many tables of the schema.

Am I the last person who has this problem? Is this fixed in 2.0.1.-GA Version?

Thanks
Kava

[kava]

SOLVED

As reported in the link below

viewtopic.php?f=14&t=607&start=10

I just added the following lines:


blPU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect


in the 2 files developement.properties

Anyway I am a bit suprised since I expected that those configurations should have been inherited by :

# These settings override and append settings in:

# common-shared.properties
# development-shared.properties
# common.properties

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

Hello,
Im a newbie.
I downloaded DemoSite-2.0.1-GA-eclipse-workspace and I followed all the steps in the guide lines. As soon as I use Hypersonic DB everything is ok.
When I try to use Mysql (followed ALL the steps described in the guide) I got some problems.

I launch jetty-demo ant task, the task tries to create the schema but I have the same problems mentioned by Saman at the beginning of this post.
I changed MySql server (now I am at th latest)....but still have problems in MySql Syntax creating many tables of the schema.

Am I the last person who has this problem? Is this fixed in 2.0.1.-GA Version?

Thanks
Kava

[albertogiantin]

Hi all, I've just dowloaded the latest version of broadleaf and tried to migrate to mysql, but I have this error when I try to run the website ant build file.

Code: Select all

[artifact:mvn] [ERROR] 12:01:34 SchemaExport - Unsuccessful: create table BLC_CATEGORY (CATEGORY_ID bigint not null, ACTIVE_END_DATE timestamp, ACTIVE_START_DATE timestamp, ARCHIVED char(255), DESCRIPTION varchar(255), DISPLAY_TEMPLATE varchar(255), LONG_DESCRIPTION clob, NAME varchar(255) not null, URL varchar(255), URL_KEY varchar(255), DEFAULT_PARENT_CATEGORY_ID bigint, primary key (CATEGORY_ID))
[artifact:mvn] [ERROR] 12:01:34 SchemaExport - 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 'clob, NAME varchar(255) not null, URL varchar(255), URL_KEY varchar(255), DEFAUL' at line 1

I've customize configurations like this:

jetty-env.xml

Code: Select all

   <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/gecommerce</Set>
            <Set name="username">root</Set>
            <Set name="password"></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/gecommerce</Set>
            <Set name="username">root</Set>
            <Set name="password"></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/gecommerce</Set>
            <Set name="username">root</Set>
            <Set name="password"></Set>
         </New>
      </Arg>
   </New>


common-shared.properties

Code: Select all

# Settings for the default persistence unit
blPU.hibernate.hbm2ddl.auto=validate
#blPU.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blPU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
blPU.hibernate.show_sql=false
blPU.hibernate.cache.use_second_level_cache=true
blPU.hibernate.cache.use_query_cache=true
blPU.hibernate.hbm2ddl.import_files=null
# Settings for the CMS storage persistence unit
blCMSStorage.hibernate.hbm2ddl.auto=validate
#blCMSStorage.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blCMSStorage.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
blCMSStorage.hibernate.show_sql=false
blCMSStorage.hibernate.cache.use_second_level_cache=true
blCMSStorage.hibernate.cache.use_query_cache=true
blCMSStorage.hibernate.hbm2ddl.import_files=null
# Settings for the secure persistence unit
blSecurePU.hibernate.hbm2ddl.auto=validate
#blSecurePU.hibernate.dialect=org.hibernate.dialect.HSQLDialect
blSecurePU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect
...


BLC version is <blc.version>2.0.2-GA</blc.version>,
mysql 5.6.10 and
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.24</version>
<type>jar</type>
<scope>compile</scope>
</dependency>

Thanks in advance for help...

[nieky]

I'm expiriencing next warn:
[ WARN] 17:21:23 PageNotFound - No mapping found for HTTP request with URI [/] in DispatcherServlet with name 'mycompany'

Before this i have done switch to the mysql 5.6.16.
Version on the demo: 3.1
Tried different ways to rebuild the project: through eclips and in cmd too.
css file i able to open.

Need your help:)



Full log of jetty-demo:

Buildfile: D:\ws\DemoSite-master\site\build.xml
start-db:
[echo] Starting Data Base...
jetty-demo:
[artifact:mvn] Listening for transport dt_socket at address: 8000
[artifact:mvn] [INFO] Scanning for projects...
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] Using the builder org.apache.maven.lifecycle.internal.builder.singlethreaded.SingleThreadedBuilder with a thread count of 1
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] ------------------------------------------------------------------------
[artifact:mvn] [INFO] Building site 1.0
[artifact:mvn] [INFO] ------------------------------------------------------------------------
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- keytool-maven-plugin:1.2:clean (clean) @ site ---
[artifact:mvn] [INFO] Keystore file 'D:\ws\DemoSite-master\site\target\mycompany\WEB-INF\blc-example.keystore' deleted successfully.
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- keytool-maven-plugin:1.2:genkey (genkey) @ site ---
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ site ---
[artifact:mvn] [INFO] Using 'UTF-8' encoding to copy filtered resources.
[artifact:mvn] [INFO] Copying 20 resources
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- jrebel-maven-plugin:1.1.3:generate (generate-rebel-xml) @ site ---
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-compiler-plugin:2.0.2:compile (default-compile) @ site ---
[artifact:mvn] [INFO] Nothing to compile - all classes are up to date
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-war-plugin:2.1.1:exploded (default-cli) @ site ---
[artifact:mvn] [INFO] Exploding webapp
[artifact:mvn] [INFO] Assembling webapp [site] in [D:\ws\DemoSite-master\site\target\mycompany]
[artifact:mvn] [INFO] Processing war project
[artifact:mvn] [INFO] Copying webapp resources [D:\ws\DemoSite-master\site\src\main\webapp]
[artifact:mvn] [INFO] Webapp assembled in [866 msecs]
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] >>> jetty-maven-plugin:8.1.5.v20120716:run (default-cli) @ site >>>
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- keytool-maven-plugin:1.2:clean (clean) @ site ---
[artifact:mvn] [INFO] Keystore file 'D:\ws\DemoSite-master\site\target\mycompany\WEB-INF\blc-example.keystore' deleted successfully.
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- keytool-maven-plugin:1.2:genkey (genkey) @ site ---
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-resources-plugin:2.6:resources (default-resources) @ site ---
[artifact:mvn] [INFO] Using 'UTF-8' encoding to copy filtered resources.
[artifact:mvn] [INFO] Copying 20 resources
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- jrebel-maven-plugin:1.1.3:generate (generate-rebel-xml) @ site ---
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-compiler-plugin:2.0.2:compile (default-compile) @ site ---
[artifact:mvn] [INFO] Nothing to compile - all classes are up to date
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-resources-plugin:2.6:testResources (default-testResources) @ site ---
[artifact:mvn] [INFO] Using 'UTF-8' encoding to copy filtered resources.
[artifact:mvn] [INFO] skip non existing resourceDirectory D:\ws\DemoSite-master\site\src\test\resources
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- maven-compiler-plugin:2.0.2:testCompile (default-testCompile) @ site ---
[artifact:mvn] [INFO] No sources to compile
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] <<< jetty-maven-plugin:8.1.5.v20120716:run (default-cli) @ site <<<
[artifact:mvn] [INFO]
[artifact:mvn] [INFO] --- jetty-maven-plugin:8.1.5.v20120716:run (default-cli) @ site ---
[artifact:mvn] 2014-03-06 17:09:53.105:INFO:oejs.Server:jetty-8.1.5.v20120716
[artifact:mvn] [INFO] Configuring Jetty for project: site
[artifact:mvn] [INFO] Webapp source directory = D:\ws\DemoSite-master\site\target\mycompany
[artifact:mvn] [INFO] Reload Mechanic: automatic
[artifact:mvn] [INFO] Classes = D:\ws\DemoSite-master\site\target\mycompany\WEB-INF\classes
[artifact:mvn] [INFO] Context path = /
[artifact:mvn] [INFO] Tmp directory = D:\ws\DemoSite-master\site\target\tmp
[artifact:mvn] [INFO] Web defaults = org/eclipse/jetty/webapp/webdefault.xml
[artifact:mvn] [INFO] Web overrides = none
[artifact:mvn] [INFO] web.xml file = file:/D:/ws/DemoSite-master/site/target/mycompany/WEB-INF/web.xml
[artifact:mvn] [INFO] Webapp directory = D:\ws\DemoSite-master\site\target\mycompany
[artifact:mvn] 2014-03-06 17:10:02.306:INFO:oejpw.PlusConfiguration:No Transaction manager found - if your webapp requires one, please configure one.
[artifact:mvn] 2014-03-06 17:12:39.093:INFO:/:No Spring WebApplicationInitializer types detected on classpath
[artifact:mvn] 2014-03-06 17:12:39.648:INFO:oejsh.ContextHandler:started o.m.j.p.JettyWebAppContext{/,file:/D:/ws/DemoSite-master/site/target/mycompany/},file:/D:/ws/DemoSite-master/site/target/mycompany/
[artifact:mvn] 2014-03-06 17:12:39.648:INFO:oejsh.ContextHandler:started o.m.j.p.JettyWebAppContext{/,file:/D:/ws/DemoSite-master/site/target/mycompany/},file:/D:/ws/DemoSite-master/site/target/mycompany/
[artifact:mvn] 2014-03-06 17:12:39.648:INFO:oejsh.ContextHandler:started o.m.j.p.JettyWebAppContext{/,file:/D:/ws/DemoSite-master/site/target/mycompany/},file:/D:/ws/DemoSite-master/site/target/mycompany/
[artifact:mvn] 2014-03-06 17:12:39.654:INFO:/:Set web app root system property: 'site.root' = [D:\ws\DemoSite-master\site\target\mycompany]
[artifact:mvn] SLF4J: Class path contains multiple SLF4J bindings.
[artifact:mvn] SLF4J: Found binding in [jar:file:/C:/Users/nkabanenko/.m2/repository/org/slf4j/slf4j-log4j12/1.6.1/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
[artifact:mvn] SLF4J: Found binding in [jar:file:/D:/ws/DemoSite-master/site/target/mycompany/WEB-INF/lib/slf4j-log4j12-1.6.1.jar!/org/slf4j/impl/StaticLoggerBinder.class]
[artifact:mvn] SLF4J: See http://www.slf4j.org/codes.html#multiple_bindings for an explanation.
[artifact:mvn] SLF4J: Actual binding is of type [org.slf4j.impl.Log4jLoggerFactory]
[artifact:mvn] 2014-03-06 17:12:39.690:INFO:/:Initializing log4j from [D:\ws\DemoSite-master\site\target\mycompany\WEB-INF\log4j.xml]
[artifact:mvn] 2014-03-06 17:12:39.780:INFO:oejsh.ContextHandler:started o.m.j.p.JettyWebAppContext{/,file:/D:/ws/DemoSite-master/site/target/mycompany/},file:/D:/ws/DemoSite-master/site/target/mycompany/
[artifact:mvn] 2014-03-06 17:12:39.781:INFO:/:Initializing Spring root WebApplicationContext
[artifact:mvn] [ WARN] 17:12:59 AbstractEhcacheRegionFactory - HHH020003: Could not find a specific ehcache configuration for cache named [org.hibernate.cache.spi.UpdateTimestampsCache]; using defaults.
[artifact:mvn] [ WARN] 17:13:00 AbstractEhcacheRegionFactory - HHH020003: Could not find a specific ehcache configuration for cache named [blSandBoxElements]; using defaults.
[artifact:mvn] [ WARN] 17:13:10 SolrResourceLoader - Can't find (or read) directory to add to classloader: lib (resolved as: C:\Users\NKABAN~1\AppData\Local\Temp\nkabanenko\solrhome\lib).
[artifact:mvn] 2014-03-06 17:13:15.665:INFO:oejsh.ContextHandler:started o.m.j.p.JettyWebAppContext{/,file:/D:/ws/DemoSite-master/site/target/mycompany/},file:/D:/ws/DemoSite-master/site/target/mycompany/
[artifact:mvn] 2014-03-06 17:13:15.789:INFO:/:Initializing Spring FrameworkServlet 'mycompany'
[artifact:mvn] 2014-03-06 17:13:18.352:INFO:oejs.AbstractConnector:Started SelectChannelConnector@0.0.0.0:8080
[artifact:mvn] [INFO] Started Jetty Server
[artifact:mvn] 2014-03-06 17:13:18.503:INFO:oejus.SslContextFactory:Enabled Protocols [SSLv2Hello, SSLv3, TLSv1, TLSv1.1, TLSv1.2] of [SSLv2Hello, SSLv3, TLSv1, TLSv1.1, TLSv1.2]
[artifact:mvn] 2014-03-06 17:13:18.505:INFO:oejs.AbstractConnector:Started SslSelectChannelConnector@0.0.0.0:8443
[artifact:mvn] [ WARN] 17:21:23 PageNotFound - No mapping found for HTTP request with URI [/] in DispatcherServlet with name 'mycompany'