IBM No data in some workspaces for MySQL agent - United States  TECHNOTE (TROUBLESHOOTING)

PROBLEM(ABSTRACT)
 Monitoring agent for MySQL when configured to monitor MySQL server versions 5.7.6 and above does not collect data for some of the attribute groups. 

SYMPTOM
Data does not get collected for the following views that show system and status variable related information: 

View name Workspace name Navigator group name Statistics Statistics Availability Operations per second MySQL status data Availability Session status Agent Configuration Configuration Global status Global variables Server String properties Numeric properties Following exception is seen in the JDBC trace logs: 

- JdbcConnection.executeFromList-new - java.sql.SQLException: The 'INFORMATION_SCHEMA.GLOBAL_STATUS' feature is disabled; see the documentation for 'show_compatibility_56' 

The above error is seen for MySQL versions 5.7.9 and higher. For MySQL versions prior to 5.7.9, selecting from the INFORMATION_SCHEMA tables produces an empty result set with a deprecation warning. 

 

Note: MySQL agent is packaged with ITCAM for Applications (IBM Tivoli Monitoring) and Application Performance Monitoring 8.1.x solutions.


CAUSE
Few tables in the INFORMATION_SCHEMA that are used by the agent to collect monitoring data are deprecated in the MySQL server versions 5.7.6 and above. Due to this deprecation these tables cannot be queried normally and cause an error when the agent tries to query these tables to get data.


More details about the MySQL INFORMATION_SCHEMA migration to performance_schema here: 

https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html [https://dev.mysql.com/doc/refman/5.7/en/performance-schema-variable-table-migration.html]



ENVIRONMENT
MySQL agent installed on MySQL Server versions 5.7.6 and above.



RESOLVING THE PROBLEM
Users wanting to run the agent on MySQL server versions 5.7.6 and above should use the 'show_compatibility_56' [https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html] system variable which affects whether MySQL 5.6 compatibility is enabled with respect to how system and status variable information is provided by the INFORMATION_SCHEMA. 

This variable can be set from the MySQL command prompt as: 

set global show_compatibility_56=ON; 

 

The default value of the variable 'show_compatibility_56' for MySQL server versions <=5.7.7 is ‘ON’ and for versions >=5.7.8 is ‘OFF’. 

 

User can check the value of this variable by running the following command from the MySQL command prompt: 

show global variables like "show_compatibility_56"; 

 

This variable can also be set in the MySQL server configuration file also called as Option file. 

User needs to add the following line to the ‘my.cnf’ file (located at ‘<MySQL_Installation_Dir>/my.cnf’ for Windows and ‘/etc/my.cnf’ for UNIX/Linux): 

show_compatibility_56=ON 

 

The paths mentioned above are the default paths for the Option file. For more details on where your installation of MySQL server looks for the 'my.cnf' file, refer the URL: 

https://dev.mysql.com/doc/refman/5.7/en/option-files.html [https://dev.mysql.com/doc/refman/5.7/en/option-files.html] 

 

Note: Change in 'my.cnf' file will require a MySQL server re-start for the changes to take effect. 

 

More details on the system variable 'show_compatibility_56' here: 

https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56 [https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html#sysvar_show_compatibility_56]



PRODUCT ALIAS/SYNONYM
 Monitoring agent for MySQL Sever
No data in some workspaces for MySQL agent Why Monitoring agent for MySQL when configured to monitor MySQL server versions 5.7.6 and above does not collect data for some of the attribute groups? 
Users wanting to run the agent on MySQL server versions 5.7.6 and above should use the 'show_compatibility_56' [https://dev.mysql.com/doc/refman/5.7/en/server-system-variables.html] system variable which affects whether MySQL 5.6 compatibility is enabled with respect to how system and status variable information is provided by the INFORMATION_SCHEMA.