MySQL 8 and MariaDB 10 Support
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:
- 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:
NoteThe 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
innodb_file_per_tablewill be set to
sql_modewill 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.
If you previously had
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 (
user_secrets.ymlso they have at least one of the following characters:
Opsview Monitor has been tested against the following databases:
- MySQL 5.5, 5.7, 8.0.
- MariaDB 5.5, 10.3, 10.5.
Ensure there is enough disk space for the upgrade, especially if you have
innodb_file_per_table set to
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
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
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.
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 servicenamemay need to be amended to
GROUP BY servicename, hostname.
- Add an aggregation to the column. For example,
SELECT performance_labels.units AS unitsmay need to be amended to
SELECT MIN(performance_labels.units) AS units.