Skip to main content

Database Conversions

Mango supports converting its relational database from one engine to another. This is commonly used when migrating from the default H2 embedded database to a production-grade MySQL or PostgreSQL database, or when migrating from the legacy Derby database.

warning

Never attempt to upgrade Mango and convert a database at the same time. First upgrade Mango to the desired version, verify it starts and runs correctly, then perform the database conversion as a separate step.

Supported Conversions​

The following database conversion paths are supported:

Source DatabaseTarget Database
DerbyH2
DerbyMySQL
H2MySQL
H2PostgreSQL
MySQLH2

You can also use the NoSQL module to migrate historical point values from any of these SQL databases into the NoSQL time-series store. This is the recommended approach for retaining historical data when migrating from the legacy Mango M2M platform to Mango Automation. See Migrating Historical Data to NoSQL for details.

Configuration​

Database conversion settings are configured in the mango.properties file (or env.properties in older versions). The conversion uses two sets of database properties:

  • The standard db.* properties define the target database (the new database you want to use).
  • The convert.db.* properties define the source database (the old database you are converting from).
# Target database (where data will be written)
db.type=mysql
db.url=jdbc:mysql://localhost:3306/mango
db.username=mango
db.password=your_password

# Source database (where data is being read from)
convert.db.type=h2
convert.db.url=jdbc:h2:./databases/mah2
convert.db.username=
convert.db.password=

The convert.db.type accepts the values derby, h2, or mysql.

Performing the Conversion​

  1. Stop Mango and create a full backup of your MA_HOME directory.
  2. Set up the target database. For MySQL, create an empty database and user with appropriate permissions. For H2, ensure there is no existing H2 database file in the databases/ directory.
  3. Edit mango.properties with the target and source database settings as shown above.
  4. Start Mango. The conversion process will begin automatically. Mango will read all data from the source database and write it to the target database.
  5. Monitor the log file (MA_HOME/logs/ma.log) for progress and any errors.
  6. Once the conversion completes, Mango will start normally using the new database.
  7. Remove or comment out the convert.db.* properties to prevent the conversion from running again on the next restart.

Common Issues​

H2 Target Already Exists​

When converting to H2, you must ensure there is no existing H2 database file. Check MA_HOME/databases/ and remove any mah2.h2.db or mah2.*.mv.db files before starting the conversion.

MySQL Character Set​

When creating a MySQL database for Mango, use the utf8mb4 character set to support the full range of Unicode characters:

CREATE DATABASE mango CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Conversion Timeout on Large Databases​

Databases with millions of events or point values may take a long time to convert. Ensure the system has adequate memory and that no other processes are competing for resources during the conversion. Monitor the log file for progress.

Point Values Separate from SQL​

If you are using the NoSQL module for time-series data, the database conversion only affects the relational data (configuration, events, etc.). Point value history stored in the NoSQL database is unaffected by the conversion and does not need to be migrated.