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 6 | CentOS 6 | Oracle Linux 6 | Ubuntu 14

service nagios stop
service ndo2db stop

 

RHEL 7 | CentOS 7 | Oracle Linux 7 | 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 | CentOS | Oracle Linux

vi /etc/my.cnf

 

Debian 8 | Ubuntu 14

vi /etc/mysql/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 6 | CentOS 6 | Oracle Linux 6

service mysqld restart

 

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

systemctl restart mariadb.service

 

Ubuntu 14

service mysql restart

 

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 6 | CentOS 6 | Oracle Linux 6 | Ubuntu 14

service ndo2db start
service nagios start

 

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/

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 - Bandwidth Graphs Showing 0Mb/s in Non-English Systems
Viewed 4436 times since Fri, Dec 19, 2014
NDOUtils - Data Processing Options in ndomod.cfg
Viewed 3902 times since Thu, Oct 22, 2015
Nagios XI - Unable To Login Using Two Factor Authentication
Viewed 588 times since Tue, Apr 10, 2018
Nagios Core - Failed to register iobroker
Viewed 1855 times since Wed, Sep 20, 2017
Nagios XI - Login Screen Keeps Redirecting To Itself
Viewed 2862 times since Wed, Jan 27, 2016
Nagios XI - Apply Configuration Fails - Backend login to the Core Configuration failed
Viewed 8826 times since Tue, Aug 2, 2016
Nagios XI - Disabling Database UTF8 Connectivity
Viewed 977 times since Thu, Mar 8, 2018
Nagios XI - SQL Error [nagiosxi] : ERROR: syntax error
Viewed 2174 times since Sun, Sep 10, 2017
Nagios XI - ERROR: PostgresQL not running - exiting
Viewed 3420 times since Tue, Jan 26, 2016
Web Browser Reports 330 Error Content Encoding
Viewed 2879 times since Tue, Mar 7, 2017