# Overview

Opsview Monitor 6.5 includes support for MySQL 8.x and MariaDB 10.x. All new installations of Opsview will automatically apply the required schema updates.

If you are upgrading Opsview from 6.4 or earlier, some database schemas will be automatically converted using the existing character set. The following databases will be automatically converted to use utf8mb4 character set:

  • dashboard

  • notifications (for Opsview Service Desk Connector)

For the following databases, you will need to follow the manual migration process after your software upgrade is completed; this is documented on [Database Migration for SQL Strict Mode](🔗):

  • opsview

  • runtime

  • odw

The manual schema upgrade _may_ take many hours to run and you need to ensure your database server has adequate disk space available. The schema upgrade does _not_ have to be run at the same time as the Opsview Monitor upgrade, but may be performed at a later date. However, future upgrades may be blocked until this manual schema process is performed. See [MySQL 8 / MariaDB 10 Support](🔗) for more details.

This version of Opsview will make the following changes to your MySQL or MariaDB configuration (these can be overridden in Opsview Deploy’s `user_vars.yml`):

  • `innodb_file_per_table` will be set to `1`

  • `sql_mode` will be set to '' (using the default value based on your database server).

For upgrades, your previous `sql_mode` will be used (usually `MYSQL40`). This should be changed after the manual migration steps

Note: if you previously had `innodb_file_per_table` set to `0`, you will need additional disk space on your database server for tables.

Note: MySQL 8 / MariaDB 10 has more stringent rules for passwords where special characters must be used and must have at least one digit. We recommend you change your database passwords (`opsview_database_password` and `opsview_database_root_password`) in `user_secrets.yml` so they have at least one of the following characters: `+-_.,^`

# Supported Databases

Opsview Monitor has been tested against the following databases:

  • MySQL 5.5, 5.7, 8.0

  • MariaDB 5.5, 10.3, 10.5

# Prerequisites

Ensure there is enough disk space for the upgrade, especially if you have `innodb_file_per_table` set to `0` or `OFF`. You can check the current configuration by using:

and also by checking a datafile on your database server filesystem is not over 1GB:

# Post Upgrade

If you have upgraded Opsview Monitor from 6.4 or earlier, you have to run a manual migration process to convert the remaining databases to UTF8 - further instructions at [Database Migration for SQL Strict Mode](🔗)

# Troubleshooting

## Create local MySQL root users - unable to connect to database

If this `opsview-deploy` task fails with this error:

This probably means the password does not meet the minimum requirements for MySQL 8+. Check mysql logs for the following:

To resolve, amend the `etc/user_secrets.yml` deploy configuration file so that the database passwords contains at least 1 digit and at least 1 special character from this list: `+-_.,^`, and rerun Deploy.

## Reports may give errors

Since `sql_mode` will be set based on the database engine, this may include the mode `ONLY_FULL_GROUP_BY`. If you have queries in your reports that use `GROUP BY`, you may get errors such as:

To resolve, you will need to amend the SQL query in the jasperreport definition so that it is compatible. There are two general fixes:

  • Add the column that should be included in the `GROUP BY`. For example, `GROUP BY servicename` may need to be amended to `GROUP BY servicename, hostname`

  • Add an aggregation to the column. For example, `SELECT performance_labels.units AS unit`s may need to be amended to `SELECT MIN(performance_labels.units) AS units`