MySQL 8 / MariaDB 10 Support
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 to1
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:
/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
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:
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
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 toGROUP BY servicename, hostname
- Add an aggregation to the column. For example,
SELECT performance_labels.units AS unit
s may need to be amended toSELECT MIN(performance_labels.units) AS units
Updated about 1 year ago