Nagios XI - mysql_error out of range value for column

Overview

This KB article explains how to resolve the following errors that appear in the /var/log/messages file on your Nagios XI server:

Aug 14 23:30:03 centosxx ndo2db: Error: mysql_query() failed for 'INSERT INTO nagios_scheduleddowntime SET instance_id='1', downtime_type='1', 
object_id='830', entry_time=FROM_UNIXTIME(1517155261), author_name='nagios_user', comment_data='AUTO: Standby Server', internal_downtime_id='804',
triggered_by_id='0', is_fixed='1', duration='31536000', scheduled_start_time=FROM_UNIXTIME(1517239800), 
scheduled_end_time=FROM_UNIXTIME(1548775800) ON DUPLICATE KEY UPDATE instance_id='1', downtime_type='1', object_id='830', 
entry_time=FROM_UNIXTIME(1517155261), author_name='nagios_user', comment_data='AUTO: Standby Server', internal_downtime_id='804', triggered_by_id='0', 
is_fixed='1', duration='31536000', scheduled_start_time=FROM_UNIXTIME(1517239800), scheduled_end_time=FROM_UNIXTIME(1548775800)'

Aug 14 23:30:03 centosxx ndo2db: mysql_error: 'Out of range value for column 'duration' at row 1'

The first error above will not be identical but it usually is followed by the Out of range value for column message.

Explanation

The errors shown above result from the MySQL / MariaDB server having the SQL Mode set to STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION. Most commonly this problem arises when you attempt to offload the databases to an external server using a MySQL / MariaDB custom installation.

The following steps will identify what the SQL Mode is currently configured for. Establish a terminal session to your MySQL / MariaDB server that is hosting your Nagios XI databases. Execute the following command:

mysql -u root -p'nagiosxi' -e "SELECT @@GLOBAL.sql_mode;"

You will need to replace the password of nagiosxi in the command above with the password of the root user on your database server.

Here is output that shows that the SQL Mode has been defined:

+--------------------------------------------+
| @@GLOBAL.sql_mode                          |
+--------------------------------------------+
| STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION |
+--------------------------------------------+

Here is output that shows that NO SQL Mode has been defined:

+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

Resolution

To resolve this issue you will need to define the SQL Mode in the MySQL / MariaDB my.cnf configuration file.

The first step is to stop the required services on your Nagios XI server:

RHEL 7/8 | CentOS 7/8 | Oracle Linux 7/8 | Debian | Ubuntu 16/18

systemctl stop nagios.service
systemctl stop ndo2db.service 

The next step is to edit the my.cnf configuration file on your MySQL / MariaDB database server. Establish a terminal session to your database server and edit the my.cnf file by executing the following command:

RHEL 8 | CentOS 8| Oracle Linux 8

vi /etc/my.cnf.d/mysql-server.cnf

RHEL 6/7 | CentOS 6/7| Oracle Linux 6/7

vi /etc/my.cnf

Ubuntu 16/18

vi /etc/mysql/mysql.conf.d/mysqld.cnf

Debian 9

vi /etc/mysql/mariadb.conf.d/50-server.cnf

When using the vi editor, to make changes press i on the keyboard first to enter insert mode. Press Esc to exit insert mode.

Locate the [mysqld] section and check to see if there is an sql_mode already defined:

[mysqld]
# Recommended in standard MySQL setup
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

If the sql_mode= line already exists you will need to replace it with the following. If the line does not exist you will need to add the following line:

[mysqld]
sql_mode=""

When you have finished, save the changes in vi by typing:

:wq

and press Enter.

You now need to restart the database service:

RHEL 7 | CentOS 7 | Oracle Linux 7 | Debian 9

systemctl restart mariadb.service

Debian 8 | Ubuntu 16/18

systemctl restart mysql.service

After the database service has restarted execute the following command to ensure the SQL Mode is no longer set:

mysql -u root -p'nagiosxi' -e "SELECT @@GLOBAL.sql_mode;"

You will need to replace the password of nagiosxi in the command above with the password of the root user on your database server. Here is output that shows that NO SQL Mode has been defined:

+-------------------+
| @@GLOBAL.sql_mode |
+-------------------+
|                   |
+-------------------+

If the output is correct you can now start the services on your Nagios XI server:

RHEL 7 | CentOS 7 | Oracle Linux 7 | Debian | Ubuntu 16/18

systemctl start ndo2db.service 
systemctl start nagios.service

You should now check the /var/log/messages file on your Nagios XI server to ensure the error messages are no longer appearing.

Final Thoughts

For any support related questions please visit the Nagios Support Forums at:

http://support.nagios.com/forum/