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/
Article ID: 822
Created On: Tue, Aug 14, 2018 at 11:19 PM
Last Updated On: Mon, Apr 19, 2021 at 10:46 AM
Authored by: tlea
Online URL: https://support.nagios.com/kb/article/nagios-xi-mysql_error-out-of-range-value-for-column-822.html