Switching from HSQLDB to MySQL

By default tinyPM standalone runs on HSQLDB, which should not be used as a production database. It is used for evaluation purposes only. If you want to start working on your real projects we highly recommend to switch to MySQL.

We use %TINYPM_PATH% to refer to a base tinyPM installation path (for example C:\tinyPM). You don't need to set such environment variable anywhere. It's used only for the purpose of this documentation.

Backup your tinyPM HSQLDB data

Follow the instructions from tinyPM Backup

Switching to MySQL

By default tinyPM standalone is configured to work with HSQLDB database. To change the default configuration to use MySQL follow the steps below.

1. Shut down your tinyPM.

2. Download mysql-connector-java-5.1.x.zip from:
http://dev.mysql.com/downloads/connector/j/5.1.html

3. Inside downloaded archive you will find mysql-connector-java-5.1.x-bin.jar

4. Put mysql-connector-java-5.1.x-bin.jar into %TINYPM_PATH%\lib

5. Create a new database for tinyPM in your MySQL:

MySQL console

CREATE DATABASE tinypmdb CHARACTER SET = 'utf8' COLLATE = 'utf8_general_ci';

6. Create a user with SELECT, UPDATE, INSERT, LOCK TABLES and DELETE rights:

MySQL console

GRANT SELECT, UPDATE, INSERT, LOCK TABLES, DELETE ON tinypmdb.* TO 'username'@'localhost' IDENTIFIED BY 'password';
FLUSH PRIVILEGES;

7. Create tinyPM database schema (instructions provided here are for mysql console application):

MySQL console

\u tinypmdb
\. %TINYPM_PATH%/sql/mysql/create_schema-2.x.sql

8a. If you want to start with a fresh MySQL installation then run also script creating basic tinyPM data:

MySQL console

\. %TINYPM_PATH%/sql/mysql/create_data-2.x.sql

8b. If you want to migrate your HSQLDB data into MySQL, you can generate a data script using hsql2mysql.bat (or hsql2mysql.sh) located in %TINYPM_PATH%/bin directory:

The HSQLDB to MySQL tinyPM data migration application is available since v2.6.1, but it can also migrate data from earlier tinyPM versions.
It can be downloaded from http://www.tinypm.com/downloads/tinypm-hsql2mysql.zip To use it, just unzip it in your %TINYPM_PATH%

Command line

cd %TINYPM_PATH%
bin\hsql2mysql.bat (on Windows)
bin/hsql2mysql.sh  (on Unix)

This will create a SQL script %TINYPM_PATH%/tinypm-mysql.sql which you can run against your tinyPM MySQL database:

MySQL console

\. %TINYPM_PATH%/tinypm-mysql.sql

9. Go to %TINYPM_PATH%\webapps\ROOT\WEB-INF\classes where you will find hibernate.properties.mysql file.

10. Rename hibernate.properties.mysql to hibernate.properties and edit the following properties:

hibernate.connection.url = jdbc:mysql://localhost/<your database name>
hibernate.connection.username = <enter your db username here>
hibernate.connection.password = <enter your db password here>

8. Start tinyPM - it will now run on MySQL.

If this documentation doesn't solve your problem or has left you with any questions, please don't hesitate to contact us at support@tinypm.com

Switching from HSQLDB to MySQL

Switching from HSQLDB to MySQL

Backup your tinyPM HSQLDB data

Switching to MySQL

Labels