Home » Categories » Products » Nagios XI » Troubleshooting » Common Problems

Nagios XI - STRICT_TRANS_TABLES

Overview

This KB article is about the MySQL / MariaDB database and adjusting the STRICT_TRANS_TABLES setting.

As of [MySQL 5.7.5] and [MariaDB 10.2.4] the STRICT_TRANS_TABLES mode is enabled by default. When this is enabled it causes database changes to be rolled back if they do not meet strict requirements. This causes problems with Nagios XI and how it saves it's data to the database.

Nagios XI requires the STRICT_TRANS_TABLES to be disabled, this KB article instructs you on how to do this.

The steps in this KB article need to be applied to your database server. Normally this is your Nagios XI server however if you have offloaded the databases to an external server then the commands need to be executed on that server.

 

 

Change Setting

Establish a terminal session to your Nagios XI server and execute the following command to open the database client:

mysql -u root -pnagiosxi

Execute these command to determine if the setting is currently disabled:

use nagiosql;
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G;

 

The output will be something like this:

*************************** 1. row ***************************
@@SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
@@GLOBAL.SQL_MODE: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)

 

You can see from the output above that the setting STRICT_TRANS_TABLES exists, this means is it enabled.

 

To disable the setting execute the following commands:

SET @@SQL_MODE = REPLACE(@@SQL_MODE,'STRICT_TRANS_TABLES', '');
SET @@GLOBAL.SQL_MODE = REPLACE(@@GLOBAL.SQL_MODE,'STRICT_TRANS_TABLES', '');
SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G;

 

The output should be something like:

MariaDB [nagiosql]> SET @@SQL_MODE = REPLACE(@@SQL_MODE,'STRICT_TRANS_TABLES', '');
Query OK, 0 rows affected (0.00 sec)

MariaDB [nagiosql]> SET @@GLOBAL.SQL_MODE = REPLACE(@@GLOBAL.SQL_MODE,'STRICT_TRANS_TABLES', '');
Query OK, 0 rows affected (0.00 sec)

MariaDB [nagiosql]> SELECT @@SQL_MODE, @@GLOBAL.SQL_MODE\G;
*************************** 1. row ***************************
@@SQL_MODE: ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
@@GLOBAL.SQL_MODE: ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
1 row in set (0.00 sec)


You can see from the output above that the STRICT_TRANS_TABLES setting no longer exists, this means it is disabled.

 

This completes the changes required to disable the STRICT_TRANS_TABLES setting. You can now exit the database client with:

quit;

 

You will also need to remove STRICT_TRANS_TABLES 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 locate the sql_mode already defined:

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

 

Remove the STRICT_TRANS_TABLES in the line so it looks similar as follows:

[mysqld]
sql_mode=NO_ENGINE_SUBSTITUTION

 

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

 

 

You now need to start the Nagios services:

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

 

 

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 Core - Nagios did not exit in a timely manner
Viewed 800 times since Wed, Jan 27, 2016
Nagios XI - WMI Authentication Problems
Viewed 1139 times since Thu, Feb 25, 2016
NDOUtils - Message Queue Exceeded
Viewed 2133 times since Thu, Jan 21, 2016
Nagios XI - Problems Using Nagios XI With Proxies
Viewed 2060 times since Mon, Jan 25, 2016
Nagios XI - Resolving "Cannot connect to database" Error - Core Config Manager
Viewed 761 times since Mon, Jan 25, 2016
Nagios XI - Debugging Bandwidth Performance Graphs
Viewed 1506 times since Tue, Jan 27, 2015
Nagios XI - Bandwidth Graphs Showing 0Mb/s in Non-English Systems
Viewed 934 times since Fri, Dec 19, 2014
Nagios XI - Common Upgrade Failures And Solutions
Viewed 1353 times since Thu, Jun 29, 2017
Nagios XI - mysql_error out of range value for column
Viewed 587 times since Tue, Aug 14, 2018
Nagios XI - Schedulded Backups No Longer Working
Viewed 833 times since Fri, Jun 3, 2016