Documentation Home
Changelog

Switch To MySQL Tutorial

A few notes regarding versions of MySQL.

  • For versions of Broadleaf 5.2 and before, the recommended version of MySQL is 5.6.
  • As of Broadleaf 6.0.6+, MySQL 5.7+ is now supported.

Starting with MySQL 5.7+ the query optimizer changed and, in certain use cases, it no longer selects the correct index for queries. This results in unexpected table scans and poor query performance. See Release Notes for version 6.0.6-GA - https://www.broadleafcommerce.com/docs/core/current/release-notes/6.0.6-ga

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 5.6 by opening a Terminal and executing:

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

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.

Update the poms to use MySQL instead of HSQL

In your project's core pom.xml, find the following in the <dependencies> section:

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-hsql-database</artifactId>
</dependency>

and replace it with

<dependency>
    <groupId>com.broadleafcommerce</groupId>
    <artifactId>broadleaf-boot-starter-database</artifactId>
</dependency>

Finally add the MySQL JDBC connector dependency to the <dependencies> section:

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

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

Add the database connection properties

In core/src/main/resources/runtime-properties/common-shared.properties, add your database connection properties like so:

# 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: 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 admin/src/main/resources/runtime-properties/default.properties:

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

After starting up the admin once, you can change all of these back to update so that any data changes stay intact. Also, add the same properties set to update to site/src/main/resources/runtime-properties/default.properties so that starting up site doesn't wipe your changes.

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