Bug #96544 | JDBC driver run too many "SHOW FULL TABLES FROM `INFORMATION_SCHEMA` LIKE PAR.." | ||
---|---|---|---|
Submitted: | 15 Aug 2019 3:20 | Modified: | 20 Aug 2019 1:55 |
Reporter: | arstercz cz | Email Updates: | |
Status: | Can't repeat | Impact on me: | |
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 2019 3:20]
arstercz cz
[15 Aug 2019 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 2019 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 2019 11:09]
MySQL Verification Team
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 2019 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.