Skip to main content

H2 to MySQL Migration Issues

Migrating from the embedded H2 database to MySQL (or MariaDB) is one of the most common database operations for Mango deployments moving to production. While Mango supports this migration, several issues can arise due to differences between H2's SQL dialect and MySQL's handling of character encoding, data types, and SQL syntax. This page covers the most frequently encountered problems and their solutions.

Symptoms​

  • Mango fails to start after changing the database configuration from H2 to MySQL, with errors in the log.
  • The log shows BadSqlGrammarException or SQLSyntaxErrorException during schema creation or data migration.
  • Character encoding errors appear: garbled text, question marks replacing special characters, or Incorrect string value errors.
  • Column type mismatch errors: Data truncation: Data too long for column or similar.
  • The migration tool reports errors while migrating point values, events, or user data.
  • Mango starts but certain pages show errors or missing data after migration.

Common Causes​

1. Incorrect MySQL Character Set and Collation​

MySQL defaults to latin1 character set on older versions. Mango requires utf8mb4 (or at minimum utf8) to properly store all characters, including those from internationalized content, device names, and point tags.

2. SQL Grammar Differences Between H2 and MySQL​

H2 uses a SQL dialect that includes syntax not supported by MySQL. While Mango handles most dialect differences internally, custom SQL data source queries, scripts, or manually created views may contain H2-specific syntax.

3. Column Length Mismatches​

Some Mango schema columns may be defined with different lengths between H2 and MySQL. MySQL enforces stricter limits on VARCHAR and TEXT column sizes, especially when using utf8mb4 encoding (where each character can use up to 4 bytes).

4. MySQL Connection Configuration Errors​

The JDBC connection string, driver class, username, or password may be incorrect. MySQL requires specific connection parameters for proper timezone handling and character encoding.

5. MySQL Version Incompatibilities​

Very old MySQL versions (5.5 and earlier) may not support features required by Mango's schema. Additionally, the JDBC driver version must be compatible with the MySQL server version.

6. InnoDB Row Size Limit​

When using utf8mb4, MySQL's InnoDB engine may hit the maximum row size limit (65,535 bytes) for tables with many VARCHAR columns.

7. Missing MySQL JDBC Driver​

The MySQL JDBC driver JAR file may not be in the correct location for Mango to find it.

Diagnosis​

Check the Mango Log for Specific Errors​

grep -i "BadSqlGrammar\|SQLSyntax\|Data truncation\|Incorrect string\|Communications link" MA_HOME/logs/ma.log

Verify MySQL Connection​

Test the connection independently before configuring Mango:

mysql -h localhost -u mango_user -p mango_db -e "SELECT 1;"

Check MySQL Character Set​

-- Check server-level defaults
SHOW VARIABLES LIKE 'character_set%';
SHOW VARIABLES LIKE 'collation%';

-- Check database-level settings
SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME
FROM INFORMATION_SCHEMA.SCHEMATA
WHERE SCHEMA_NAME = 'mango_db';

Check MySQL Version​

SELECT VERSION();

Mango requires MySQL 5.7 or later, or MariaDB 10.2 or later.

Verify the JDBC Driver is Present​

ls MA_HOME/lib/mysql-connector*.jar
# or for the SQL Data Source lib directory
ls MA_HOME/web/modules/sqlDS/lib/mysql-connector*.jar

Solutions​

Solution 1: Create the Database with Correct Character Set​

Before configuring Mango, create the MySQL database with the correct character set and collation:

CREATE DATABASE mango_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

CREATE USER 'mango_user'@'localhost' IDENTIFIED BY 'your_secure_password';
GRANT ALL PRIVILEGES ON mango_db.* TO 'mango_user'@'localhost';
FLUSH PRIVILEGES;

If the database already exists with the wrong character set, convert it:

ALTER DATABASE mango_db
  CHARACTER SET utf8mb4
  COLLATE utf8mb4_unicode_ci;

You may also need to convert existing tables:

-- Convert all tables (run for each table)
ALTER TABLE table_name CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

Solution 2: Configure mango.properties for MySQL​

Update the database settings in mango.properties:

# MySQL/MariaDB configuration
db.type=mysql
db.url=jdbc:mysql://localhost:3306/mango_db?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC&characterEncoding=UTF-8&useUnicode=true
db.username=mango_user
db.password=your_secure_password

Key connection parameters:

ParameterPurpose
useSSL=falseDisable SSL if not configured (set to true in production with proper SSL setup)
allowPublicKeyRetrieval=trueRequired for MySQL 8.0+ authentication
serverTimezone=UTCPrevents timezone-related errors
characterEncoding=UTF-8Ensures proper character handling
useUnicode=trueEnables Unicode support

Solution 3: Install the MySQL JDBC Driver​

Download the MySQL Connector/J driver and place it in the Mango library directory:

# Download the driver (check for the latest version)
# Place the JAR file in MA_HOME/lib/
cp mysql-connector-j-8.x.x.jar MA_HOME/lib/

For MariaDB, use the MariaDB Connector/J instead:

cp mariadb-java-client-3.x.x.jar MA_HOME/lib/

Solution 4: Fix InnoDB Row Size Issues​

If you encounter row size errors with utf8mb4, adjust the MySQL configuration in my.cnf (or my.ini on Windows):

[mysqld]
innodb_file_format=Barracuda
innodb_file_per_table=ON
innodb_large_prefix=ON
innodb_default_row_format=DYNAMIC

Restart MySQL after making these changes.

Solution 5: Migrate Data Using the Mango Migration Tool​

Mango provides a built-in SQL data migration tool for moving data from one database to another. This is the recommended approach for migrating point values and configuration:

  1. Start Mango with the new MySQL database configured (empty).
  2. Mango will create the schema automatically on first start.
  3. Use the Migrate SQL Data Into Mango tool (available under System Settings or the NoSQL module page) to import data from the old H2 database.
  4. Configure the H2 JDBC connection string pointing to your old database file: 
    jdbc:h2:file:/path/to/old/databases/mah2;AUTO_SERVER=TRUE
  5. Driver class: org.h2.Driver
  6. Start the migration. Point data will be imported for all points whose XIDs match between the old and new databases.

Solution 6: Resolve Specific SQL Grammar Errors​

If custom SQL data source queries use H2-specific syntax, update them for MySQL:

H2 SyntaxMySQL Equivalent
LIMIT n OFFSET mLIMIT m, n or LIMIT n OFFSET m (MySQL 5.7+ supports both)
IDENTITY()LAST_INSERT_ID()
MERGE INTOINSERT ... ON DUPLICATE KEY UPDATE
BOOLEAN column typeTINYINT(1)
CLOB column typeLONGTEXT
DOUBLE with precisionDOUBLE (MySQL ignores precision on DOUBLE)

Solution 7: Handle Timezone Issues​

If you see errors related to timestamps or timezones:

-- Set MySQL server timezone
SET GLOBAL time_zone = '+00:00';

Or add to my.cnf:

[mysqld]
default-time-zone='+00:00'

Ensure the JDBC connection string includes serverTimezone=UTC.

Prevention​

  • Always create the MySQL database with utf8mb4 character set before configuring Mango to use it. This prevents encoding issues from the start.
  • Use the latest MySQL JDBC driver compatible with your MySQL server version.
  • Test the migration on a non-production copy first. Export the H2 database, set up a test MySQL instance, and run the full migration to identify issues before touching production data.
  • Back up your H2 database before starting the migration. Copy the entire MA_HOME/databases/ directory.
  • Document your MySQL connection string including all necessary parameters. A working connection string is the most frequently needed reference during database troubleshooting.
  • Use MySQL 8.0 or MariaDB 10.4+ for the best compatibility with Mango's schema and data types.
  • Monitor MySQL performance after migration. The query patterns for Mango can be different from what H2 handles, and MySQL may need index tuning or buffer pool adjustments for optimal performance.