Bug #96544 JDBC driver run too many "SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE PAR.."
Submitted: 15 Aug 3:20 Modified: 20 Aug 1:55
Reporter: arstercz cz Email Updates:
Status: Can't repeat Impact on me:
None 
Category:Connector / J Severity:S5 (Performance)
Version:5.1.47 OS:Linux (Centos6, Centos7)
Assigned to: CPU Architecture:Any (x86_64)
Tags: DatabaseMetaData, getTables, jdbc

[15 Aug 3:20] arstercz cz
Description:
The following method will be invoke many times when application run sql(select,update,insert or delete) with mysql-connection-5.1.47, in our production and test environment, the 13 thousands select queries correspond to 28 thousands "SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'" queries. This sql execute too many times to eat host cpus, in our mysqlslap test, 30 thousands "SHOW FULL TABLES ... PARAMETERS" queries will eat the host for 400%(CPU). 

// com/mysql/jdbc/DatabaseMetaDataUsingInfoSchema.java

  34 public class DatabaseMetaDataUsingInfoSchema extends DatabaseMetaData {
  35 
  ...
  46     protected DatabaseMetaDataUsingInfoSchema(MySQLConnection connToSet, String databaseToSet) throws SQLException {
  47         super(connToSet, databaseToSet);
  48 
  49         this.hasReferentialConstraintsView = this.conn.versionMeetsMinimum(5, 1, 10);
  50 
  51         ResultSet rs = null;
  52 
  53         try {
  54             rs = super.getTables("INFORMATION_SCHEMA", null, "PARAMETERS", new String[0]);
  55 
  56             this.hasParametersView = rs.next();
  57         } finally {
  58             if (rs != null) {
  59                 rs.close();
  60             }
  61         }
  62     }

How to repeat:

run any sql(select, insert, update or delete) quries by mysql-connector-java-5.1.47, then the Com_show_tables status will grow a lot.

Suggested fix:
maybe support the folloing features:

1. add option to disable jdbc invoke DatabaseMetaDataUsingInfoSchema.

2. cache the queries result, because the result is allways the same:

mysql > SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS';
+-------------------------------------------+-------------+
| Tables_in_information_schema (PARAMETERS) | Table_type  |
+-------------------------------------------+-------------+
| PARAMETERS                                | SYSTEM VIEW |
+-------------------------------------------+-------------+
[15 Aug 8:28] arstercz cz
It seems like the select queries will cause the "SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'" query. other SQL does not necessarily cause this query.
[15 Aug 10:10] arstercz cz
jdbc support INFORMATION_SCHEMA.PARAMETERS from version 5.1.8:

   - Support use of INFORMATION_SCHEMA.PARAMETERS when "useInformationSchema" is set "true" and the view exists
      for DatabaseMetaData.getProcedureColumns() and getFunctionColumns().

https://github.com/mysql/mysql-connector-j/commit/afcc630355ef06b51d440953537cd88a2494afcf

we can disable invoke "SHOW FULL ..." query when useInformationSchema is false. but it's still a performance issue when useInformationSchema is true.
[16 Aug 11:09] Umesh Shastry
Hello arster Chen,

Thank you for the report and feedback.
I'm not seeing this issue with latest MySQL Connector/J 8.0 regardless of useInformationSchema settings. MySQL Connector/J 8.0 is highly recommended for use with MySQL Server 8.0, 5.7, 5.6, and 5.5. Also we don't fix bugs in old versions, don't backport bug fixes, so need to check with latest version anyway. So,please upgrade to MySQL Connector/J 8.0 and let us know if you are still seeing the issue.

--
--- 5.1.47 - useInformationSchema=True

2019-08-16T11:03:08.161725Z        16 Query     /* mysql-connector-java-5.1.47 ( Revision: fe1903b1ecb4a96a917f7ed3190d80c049b1de29 ) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_buffer_length AS net_buffer_length, @@net_write_timeout AS net_write_timeout, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2019-08-16T11:03:08.368753Z        16 Query     SET NAMES utf8mb4
2019-08-16T11:03:08.550179Z        16 Query     SET character_set_results = NULL
2019-08-16T11:03:08.731674Z        16 Query     SET autocommit=1
2019-08-16T11:03:09.039742Z        16 Query     SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE 'PARAMETERS'
2019-08-16T11:03:09.231141Z        16 Query     SELECT id from t1

--- 8.0.17 - useInformationSchema=True

2019-08-16T11:04:07.632411Z        17 Query     /* mysql-connector-java-8.0.17 (Revision: 16a712ddb3f826a1933ab42b0039f7fb9eebc6ec) */SELECT  @@session.auto_increment_increment AS auto_increment_increment, @@character_set_client AS character_set_client, @@character_set_connection AS character_set_connection, @@character_set_results AS character_set_results, @@character_set_server AS character_set_server, @@collation_server AS collation_server, @@collation_connection AS collation_connection, @@init_connect AS init_connect, @@interactive_timeout AS interactive_timeout, @@license AS license, @@lower_case_table_names AS lower_case_table_names, @@max_allowed_packet AS max_allowed_packet, @@net_write_timeout AS net_write_timeout, @@performance_schema AS performance_schema, @@query_cache_size AS query_cache_size, @@query_cache_type AS query_cache_type, @@sql_mode AS sql_mode, @@system_time_zone AS system_time_zone, @@time_zone AS time_zone, @@transaction_isolation AS transaction_isolation, @@wait_timeout AS wait_timeout
2019-08-16T11:04:07.834690Z        17 Query     SHOW WARNINGS
2019-08-16T11:04:08.029268Z        17 Query     SET NAMES utf8mb4
2019-08-16T11:04:08.212184Z        17 Query     SET character_set_results = NULL
2019-08-16T11:04:08.394967Z        17 Query     SET autocommit=1
2019-08-16T11:04:08.592311Z        17 Query     SELECT id from t1

regards,
Umesh
[20 Aug 1:55] arstercz cz
Thanks Umesh. we'll disable useInformationSchema when use the lower version, and use MySQL connector J/8.0 versions if needed.