Documentation Home
Changelog

Switch To MySQL Tutorial

Broadleaf works with a minimum MySQL version of 5.5. We recommend using the latest version.

Download and install MySQL Server

Windows

  1. Download the [MySQL installer[(http://dev.mysql.com/downloads/windows/installer/)]
  2. Execute the MySQL installer. We recommend using the 'Server Only' installation since you will not need most of the other products. You might want to download the 'MySQL Workbench' later as a schema browser

You don't have to create a separate user and role for a broadleaf user but you can if you want. Otherwise remember the root password and use that as your database properties

OSX

The easiest way is to use Homebrew. After Homebrew is installed, you can install MySQL by opening a Terminal and executing:

brew install mysql
brew tap homebrew/services
brew services restart mysql

The last 2 commands install the Homebrew services module that allows for easier manipulation of the MySQL daemon. During installation, make sure that you keep track of the username and password that you set, Broadleaf will need it for connection details.

Linux

Use your favorite package installer to install MySQL e.g.

apt-get install mysql-server

Then run the installer to complete the database setup. Keep track of the database root password, you will need it later.

Update the MySQL configuration for UTF-8

For proper UTF8 configuration and ease of use across different OS's we also recommend these minimum settings in my.cnf:

[mysqld]
lower_case_table_names=1
character-set-server=utf8
collation-server=utf8_general_ci

After updating this file you will need to restart the MySQL server

Location of my.cnf

The location of my.cnf varies on each system. This is the official MySQL reference guide for these locations. We recommend the following locations for modifying this file (this might require creating the file if it does not already exist`):

  • Windows - C:\my.cnf
  • OSX/Linux - ~/.my.cnf or /etc/my.cnf**

** In some versions of MySQL /etc/my.cnf is a reserved location that has the potential to be overwritten on upgrades. If this is not an issue for you then this file can be modified

The user specific .my.cnf file should be in the home directory that the MySQL user is running as

Create a new database schema

Create a new schema in the database (e.g. broadleaf). If you created a new user in the installation step, make sure that your new user owns the schema and can modify tables.

Add the MySQL client driver dependency

In your root pom.xml, find the following in the <dependencies> section under the <plugin> with <groupId>org.apache.tomcat.maven</groupId>

<dependency>
    <groupId>org.hsqldb</groupId>
    <artifactId>hsqldb</artifactId>
    <version>2.3.1</version>
</dependency>

and replace it with

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

Remove the HSQLDB startup execution

Since you will no longer be using HSQLDB, in the root pom.xml file, you can remove the task definitions that start and shutdown HSQLDB. Look for <id>hsqldb-start</id> and <id>hsqldb-stop</id>. Delete both of those executions.

Also, if you are using the start.sh commands in the site and admin projects, you should delete the antrun:run@hsqldb-start section. For instance, given the line that looks like:

MAVEN_OPTS="$MAVEN_OPTS $BROADLEAF_OPTS" mvn antrun:run@hsqldb-start tomcat7:run-war

Replace it with:

MAVEN_OPTS="$MAVEN_OPTS $BROADLEAF_OPTS" mvn tomcat7:run-war

Remove the HSQL demo command extractor

In core/src/main/resources/runtime-properties/common-shared.properties you will see a set of properties that look like:

blPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoHsqlSingleLineSqlCommandExtractor
blSecurePU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoHsqlSingleLineSqlCommandExtractor
blCMSStorage.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoHsqlSingleLineSqlCommandExtractor
blEventPU.hibernate.hbm2ddl.import_files_sql_extractor=org.broadleafcommerce.common.util.sql.importsql.DemoHsqlSingleLineSqlCommandExtractor

Remove all of these properties, they will cause errors with MySQL

Update the Hibernate dialect

Update the runtime properties to use the correct MySQL dialect. In core/src/main/resources/runtime-properties/common-shared.properties, you will want to update the three persistence unit dialects to say:

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

Enterprise and Multi-Tenant

You will need to update a 4th location as well:

blEventPU.hibernate.dialect=org.hibernate.dialect.MySQL5InnoDBDialect

Update the database connection properties

Update your database.properties to connect to your MySQL database. In database.properties, you will want to update the following:

# your local database username, just a user that has readwrite permissions
database.user=root
# local database password
database.password=
database.driver=com.mysql.jdbc.Driver
# this connection URL assumes that it is connecting to a schema called broadleaf
database.url=jdbc:mysql://localhost:3306/broadleaf?useUnicode=true&amp;characterEncoding=utf8

Note: database.driver and database.url have been changed from HSQLDB to MySQL.
Note: I named my database broadleaf, make sure you use your database name in the url.

Update runtime properties to create the initial schema

It is likely that you will still need to initialize and seed the new MySQL schema. Ensure that these properties are set in either site/src/main/resources/runtime-properties/development.properties:

blPU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create
blSecurePU.hibernate.hbm2ddl.auto=create
blEventPU.hibernate.hbm2ddl.auto=create

After starting up the frontend once, you can change all of these back to update so that any data changes stay intact.

And that's it! You should now be up and running with MySQL.