My Gateway is not logging to a database

This problem is related to database logging not working, database logging icon crossed out, database logging error messages in gateway log.

Problem Copied

1. My Database logging Icon is red and is crossed out.
2. My connectionStatus field is showing as Not Connected in the Gateway Database-Logging plugin.
3. There is an error message in the Gateway Log file.

Possible causes Copied

• Incorrect LD\_LIBRARY\_PATH specified
• Database permissions not set or incorrectly set
• Network connectivity issues or database not available
• Database Logging set to paused
• Version table empty or incorrect version set
• Database table does not exist
• Primary key of the var\_ref table exceeds maximum threshold
• Database Queue buffer exceeded
• Isolation level set to serializable (Oracle)
• Item Values being discarded
• Database version is not supported

Possible solutions Copied

• Incorrect LD\_LIBRARY\_PATH specified — check your LD\_LIBRARY\_PATH and DATABASE path are set correctly in your gateway start script and check that the correct library files exist in the specified directory location.

Example for Postgres:

POSTGRESQL_LIB=/usr/pgsql-11/lib ; export POSTGRESQL_LIB
LD_LIBRARY_PATH=${POSTGRESQL_LIB}:$LD_LIBRARY_PATH;
export LD_LIBRARY_PATH

• Database permissions not set or incorrectly set — check the Gateway Log file for access denied error messages.

Example msg:

Access denied for user 'itrsdb'@'%' to database 'ITRS', db error code: 1044 (type: DBMSAPI) [1044] Access denied for user 'itrsdb'@'%' to database 'ITRS' DBMSAPIx

Set correct permissions as per ITRS guidance. For more information, check MySQL documentation.

• Network connectivity issues or database not available — check the Gateway Log file for Connection Failed error messages.

For example:

2020-05-26 16:48:18.268-0400  WARN: Event:DatabaseLogging 1590526098.268009 DatabaseLogging /geneos/gateway[@name="ITRSGW_7039 Market_Data"] Connection Failed There was a problem connecting => db error msg: Can't connect to MySQL server on xxxx

Check that the Database is running and there are no network connectivity issues between the Gateway and Database server hosts.

• Database Logging set to paused — Check the Gateway-Database-Logging plugin connectionPaused field.

If connectionPaused is set then right click Gateway Icon, select Database Logging > Connection > Resume.

• Version table empty or incorrect version set — check the gateway log for the error.

The table version must match the appropriate schema for the Gateway version that is running.

• Database table does not exist — check gateway log file for invalid object name error messages.

Invalid object name 'test\_table'., db error code: 208

The table needs to be created at the Database Level as well as the Database Logging section within the gateway configuration.

• Primary key of the var\_ref table exceeds maximum threshold — if the Primary key of the var\_ref tables exceeds the maximum value (2147483647) then the gateway will suspend Database Logging.

To remedy this you will need to do the following:

1. Modify Key Value.
2. Create space in the var\_ref table by removing unwanted entries from the table. We would advise contact your DBA to assist with this.
3. Reset the key increment.

For more information, see section about resetting database in the Database Logging documentation.

• Database Queue buffer exceeded — the Gateway will buffer all database updates into an internal queue if the connection to the database is broken. If the maximum allowed threshold for the queue buffer is breached then all database updates are written to files on the Gateway server.

This will continue until the connection is restored. You should involve your DBA in investigating why the connection to the Database is broken.

The Gateway will re-read database updates from files once the connection is reestablished automatically and this process can also be instigated manually. For more details, see Database cache dump files

• Isolation level set to serializable (Oracle) - if the Database isolation level is set to Serializable this may prevent database logging from working. This error is written to the gateway logs.

To remedy this change the IsolationLevel to Read committed in the Database Logging advanced tab.

https://docs.itrsgroup.com/docs/geneos/current/Gateway_Reference_Guide/geneos_dblogging_tr.html#databaseLogging__isolationLevel

• Item Values being discarded — the gateway log file will report value too large for column error message.

ORA-12899: value too large for column "GENEOS"."EVENT_TABLE"."DESCRIPTION" (actual: 261, maximum: 250) DBMSAPI [Discarding event]

You will have to modify the database schema at the database level to increase the string length of the table to copy with longer strings.

• Database version is not supported — check compatibility matrix for supported versions.