MySQL 8 and MariaDB 10 Support

Overview Copied

Opsview Monitor includes support for MySQL and MariaDB as specified in Supported Databases. 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

Note

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.

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.

MySQL 8 and 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 Copied

Opsview Monitor has been tested against the following databases:

• MySQL 5.5, 5.7, 8.0.
• MariaDB 5.5, 10.3, 10.5.

Prerequisites Copied

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:

/opt/opsview/coreutils/utils/cx opsview "show variables like 'innodb_file_per_table'"

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

ls -lh /var/lib/mysql/ibdata1

Post Upgrade Copied

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 Copied

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

If this opsview-deploy task fails with this error:

TASK [opsview_database : Create local MySQL root users] *********************************************************************************************************
Monday 25 January 2021  12:30:40 +0000 (0:00:00.405)       0:00:21.783 ******** 
failed: [tv-centos7-mysql8] (item=localhost) => changed=false 
  item: localhost
  msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1045, "Access denied for user ''root''@''localhost'' (using password: YES)")'
failed: [tv-centos7-mysql8] (item=127.0.0.1) => changed=false 
  item: 127.0.0.1
  msg: 'unable to connect to database, check login_user and login_password are correct or /root/.my.cnf has the credentials. Exception message: (1045, "Access denied for user ''root''@''localhost'' (using password: YES)")'

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

2021-01-25T12:34:40.231982Z 7 [ERROR] [MY-000061] [Server] 1819  Your password does not satisfy the current policy requirements.

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 Copied

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:

Caused by: java.sql.SQLSyntaxErrorException: Expression #6 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'odw.performance_labels.units' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

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 units may need to be amended to SELECT MIN(performance_labels.units) AS units.