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 - MSSQL Query Wizard - Invalid characters in the username
Viewed 1603 times since Thu, Aug 3, 2017
Nagios XI - Event Data Is Stale
Viewed 2970 times since Wed, Jan 27, 2016
Nagios XI - Core 4 Load Spikes on 1.75 and 7 Hour Intervals
Viewed 2381 times since Mon, Jan 25, 2016
Nagios XI - Monitoring Using the Full Power of Nagios XI Enterprise - NWC15
Viewed 3601 times since Mon, Feb 8, 2016
Nagios XI - Installing Components
Viewed 2404 times since Wed, Jan 27, 2016
CCM says unapplied changes exist, but none listed
Viewed 2140 times since Mon, Feb 27, 2017
Nagios XI - Common Upgrade Failures And Solutions
Viewed 3016 times since Thu, Jun 29, 2017
Nagios XI - Plain Text Password Considerations
Viewed 939 times since Mon, Jun 18, 2018
Nagios XI - Defining Global Environment Variables
Viewed 4614 times since Thu, Mar 17, 2016
Nagios XI - Hardware Requirements - Baseline Testing
Viewed 1303 times since Sun, Jul 17, 2016