Using opentaps ERP + CRM with MySQL

This short guide will explain how to use opentaps Open Source ERP + CRM with MySQL 5.0 or later. We highly recommend that you use MySQL 5.0 or later. While it is possible to configure opentaps Open Source ERP + CRM with earlier versions of MySQL, many additional steps may be required. In particular, you would need to set up InnoDB instead of MyISAM as the database engine, since it supports transactions and foreign keys, which are very important for business applications such as ERP or CRM.

Installing MySQL

Install MySQL using the binary installation files from the MySQL download site. Select "Transactional Database" and "Online Transactions Processing (OLTP)" so that MySQL will use the right database engine which would support transactions and foreign-key constraints, which are critical for an ERP/CRM application. Make sure that TCP/IP access is enabled on port 3306. Choose "UTF-8" encoding for support for internationalization character sets. MySQL will prompt you for a root user password. Write it down--you will use it later to create databases.

We recommend that you set up MySQL to be case insensitive so that it would be compatible under all platforms. See this document on MySQL case sensitivity.

Creating a MySQL Database for opentaps ERP + CRM

Once MySQL is installed, go to a command line or Windows Terminal prompt and access the MySQL client as the root user and your root password:

$ mysql -u root -h 127.0.0.1 -p <root-password>

Now create a database, a user, and grant privileges to the user on all operations for this database:

mysql> create database opentaps;

mysql> create user opentaps;

mysql> grant all privileges on opentaps.* to 'opentaps'@'localhost' identified by 'opentaps-password' with grant option;
mysql> flush privileges;

Now quit the MySQL clilent and try to access the "opentaps" database you just created with your "opentaps" user:

$ mysql -h 127.0.0.1 -u opentaps -p <opentaps-password>

mysql> use opentaps;

If all is successful, then you have successfully created your database and user. Now you are ready to use it with opentaps ERP + CRM.

Setting Up opentaps ERP + CRM to Use a MySQL Database

A copy of the current MySQL JDBC driver is included with opentaps in the framework/entity/lib/jdbc sub-directory. You should go to the MySQL JDBC driver download site if you need to use a different version and copy it into framework/entity/lib/jdbc. (Note: There have been reports of incompatibility with MySQL connector/J version 3.1 JDBC drivers, so we recommend that you use version 3.0.)

Next, change the file framework/entity/config/entityengine.xml to point to your MySQL database:

1. Change the "datasource-name" attribute under the <delegator name="default"...> from the default "localderby" to "localmysql". (Note: older version of opentaps might have a default of "localhsql") Do not change localderbyodbc.
2. Look for <datasource name="localmysql"...> further down.
3. Change the "jdbc-uri" under "localmysql" to "jdbc:mysql://127.0.0.1/opentaps?autoReconnect=true&amp;useOldAliasMetadataBehavior=true"
4. Change the "jdbc-username" to "opentaps"
5. Change the "jdbc-password" to the password you chose above
6. Set the correct character set and collation. By default, the entity engine is configured to use case-sensitive Latin character sets. If you are going to use opentaps with a different language, you can change it to UTF8 by setting character-set="utf8" and collate="utf8_general_ci" or at your language's UTF8 setting. Note that MySQL may not support case-sensitive characters for your UTF8 character set.

If you are using opentaps 1.5M2 or later, you will also need to configure your MySQL database as the tenant. See opentaps 1.5 entity engine changes.

Now stop opentaps ERP + CRM. You will need to reload the seed data into the new MySQL database by typing in this command from the opentaps directory

$ ant run-install

Make sure you read the General Installation instructions first and configure the correct JVM. This is the most common cause of problems.

Once it is done, you can use opentaps ERP + CRM again using the instructions from General Installation. It should now be running with a MySQL database.

If you just want a fresh install with the seed data only but none of the demo data product data, use

$ ant run-install-seed

instead.