MySQL server status shows incorrect data when time zone property is set in JDBC config file

Problem

MySQL server status shows incorrect data when the time zone property is set in JDBC config file.
The following exception is displayed in logs: CANDLE HOME/logs/kse_instance_name_trace0.log:

agent.client.attributeGroups.Application_Availability.collectData - Server is down and Server FQDN: bodkatamari.PrivateCloud.cloud - The server time zone value 'CDT' is unrecognized or represents more than one time zone. You must configure either the server or JDBC driver (via the serverTimezone configuration property) to use a more specifc time zone value if you want to utilize time zone support.

Where:

• instance_name is the agent instance name.
• CANDLE_HOME is the agent installation directory.

Symptom

MySQL server status is showed as inactive.

Cause

The server time zone value is unrecognized or represents more than one time zone.

Workaround

Based on the time zone value in agent configuration file kse_config.properties, you can configure time zone offset from Coordinated Universal Time (UTC) or Greenwich Mean Time (GMT) in the MySQL server configuration file, my.ini or my.cnf.

The agent configuration file kse_config.properties is located at the following path depending on your platform:

• Windows platform: CANDLE HOME\TMAITM6_x64\kse_config.properties
• Non-Windows platform: CANDLE HOME/lx8266/se/lib/kse_config.properties
• CANDLE_HOME is the agent installation directory.

The MySQL server configuration file is located at the following path depending on your platform:

• Windows platform: C:\ProgramData\MySQL\MySQL Server 8.0\my.ini
• non-Windows platform: /etc/my.cnf

Set the time zone offset in the MySQL server configuration file, my.ini or my.cnf.

default-time-zone ='+/-[hh]:[mm]'

Where:

• The sign + indicates the difference of [hh]:[mm] ahead of UTC or GMT.
• The sign - indicates the difference of [hh]:[mm] behind UTC or GMT.
• hh is the hour(s).
• mm is the minutes.

For example, in agent configuration file kse_config.properties, server time zone is set to America/Chicago:

serverTimezone=America/Chicago

The time zone offset for America/Chicago (CDT) is UTC/GMT-05:00. Hence, you need to set the default-time-zone as '-05:00' in the MySQL server configuration file, my.ini or my.cnf.

default-time-zone ='-05:00'