Note: Note: be sure you do not try to upgrade Mango and convert a database at the same time.

In this section we will discuss how to convert a database. There are a few kind of conversion you can do:

  • Derby to H2
  • Derby to MySQL
  • H2 to MySQL

You can also use the NoSQL module and migrate the historical values from a Derby, H2 or MySQL database. This also works to migrate historical values from an older Mango M2M database as way of retaining all data when migrating from Mango M2M to Mango Automation.

You will find the database conversion settings in the env.properties file

# Database settings for conversion. If the db.* settings point to a new database instance, and the convert type setting
# is set, Mango Automation will attempt to convert from the convert.db.* settings to the db.* settings
# Note that database conversions should not be performed in the same step as an upgrade. First upgrade, then convert.
convert.db.type=
convert.db.url=${convert.db.url}
convert.db.username=${convert.db.username}
convert.db.password=${convert.db.password}

The convert.db.type= (derby, h2, mysql) is the database you are converting from and the url, username and passwords are for that database.

This database will be converted to the main database type set in the env.properties file.

Note: When converting to H2 you need to make sure there is not an existing H2 database. If the conversion fails check in the MA_HOME/databases and make sure there is no ma.h2.db file


Migrating Historical Data to the NoSQL Database

You can migrate the historical point values from a Derby, H2 or MySQL database. This even works from an older Mango M2M database which is the only way to easily upgrade from Mango M2M to Mango Automation.

The NoSQL migration can take several hours to complete but it will happen in the background. Even during migration Mango will continue to collect and store current point values into the NoSQL database so no data is lost in the process.

This page is used to import data from an external SQL database while Mango is running.

The Driver class name specifies the Java class that implements the JDBC Driver interface within your JDBC driver. This value can be found in the documentation for your driver. The following is a list of driver class names for the given database, and is provided as convenience only. Note that connectivity typically requires the installation of driver software on the Mango server. This is achieved by placing the Driver's .jar file into the SQL datasource's “lib” directory, again referring to the documentation to locate the JDBC Driver interface.

  • MySQL: com.mysql.jdbc.Driver
  • Oracle thin driver: oracle.jdbc.driver.OracleDriver
  • Derby: org.apache.derby.jdbc.EmbeddedDriver
  • H2: org.h2.Driver

The Connection string depends both upon the database you are using and the specifics of its installation. Your JDBC driver documentation will explain how to derive the value for your database. The Username andPassword fields provide the necessary credentials for your database to accept connections from this application.

  • H2 Connection String example: jdbc:h2:./databases/mah2
  • MySQL Connection String example: jdbc:mysql:localhost/<your mysql schema name>
  • Username and Password are the database user's login credentials. You can find this in your env.properties file.

Advanced Options

These values should be modified to assist in performance and memory management during large imports of data (millions or billions of point values). There is a tradeoff between speed/memory use and keeping mango collecting data effectively during this process.

The Number of threads defines how many threads the points will be broken into to concurrently import the data. For example if importing 10 points Mango can use up to 10 threads to import the data. The more threads the faster the import will run, but the more resources it will consume and potentially affect the data collection of Mango.

Thread priority can be set to any of the following:

  • High priority - will run all threads almost immediately in parallel in a pool of 1000's of threads (same priority as data collection)
  • Medium priority - will run in parallel without slowing down the high priority thread pool and will run in a pool of up to 30 threads.
  • Low priority - will run one thread at a time giving priority to all other threads

The Maximum point value queue size is used to ensure that Mango has enough memory to perform other tasks. Each thread will use a block of memory large enough to store this many point values while streaming them from the SQL database into MangoNoSQL. If the SQL database is able to provide Mango with data faster than it can store it, this block of memory will fill up. Once full Mango will slow down the rate at which it is receiving data from the SQL database to ensure that extra memory is not required.

Stopping a running migration is possible by pressing the Cancel button. This does not happen immediately as Mango must contact the database and request the query to be terminated. This is not supported on all database types but has been tested and works (slowly) on MySQL. Note that data will stop streaming to mango immediately as the results are discarded but this does not mean the query has stopped running on the Database end of the connection.