Home » Categories » Multiple Categories

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/CentOS 6.x

service nagios stop
service ndo2db stop

 

RHEL/CentOS 7.x

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:

vi /etc/my.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/CentOS 6.x

service mysqld restart

 

RHEL/CentOS 7.x

systemctl restart mariadb.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/CentOS 6.x

service ndo2db start
service nagios start

 

RHEL/CentOS 7.x

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/

0 (0)
Article Rating (No Votes)
Rate this article
  • Icon PDFExport to PDF
  • Icon MS-WordExport to MS Word
Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
Nagios XI - Reset Security Credentials
Viewed 2922 times since Mon, Jan 25, 2016
Nagios XI - CentOS 6 Installation Problems XI 2011R1.7 2011R1.8
Viewed 2686 times since Tue, Feb 2, 2016
NRPE - Agent and Plugin Explained
Viewed 12989 times since Fri, Jul 14, 2017
Nagios XI - Scheduled Backup Log Level
Viewed 1828 times since Tue, Apr 18, 2017
Nagios XI - Configuration Applies, but still get "Configuration File Is Out Of Date" Error
Viewed 2279 times since Tue, Jan 26, 2016
Nagios XI - Display All Scheduled Reports
Viewed 2690 times since Wed, Oct 12, 2016
Active Directory / LDAP - Troubleshooting Authentication Integration
Viewed 4000 times since Mon, Jun 26, 2017
Nagios XI - Securing Your Nagios Server - NWC13
Viewed 2193 times since Thu, Feb 4, 2016
Nagios XI - Changing The System Time
Viewed 4171 times since Mon, Jan 25, 2016
Nagios XI - Downloading A System Profile
Viewed 1259 times since Wed, Jul 19, 2017