0

I'm having many useless MySQL connections from my application, using mysql-connector-java-5.1.32. The MySQL server log looks like this :

root@scet-prod-qlik:/var/log/mysql $ tail mysql.log
2021-04-05T08:49:24.306318Z 246109 Query    SET character_set_results = NULL
2021-04-05T08:49:25.004308Z 246110 Connect  qlik@192.168.1.12 on qlik using TCP/IP
2021-04-05T08:49:25.004553Z 246110 Query    SET NAMES utf8mb4
2021-04-05T08:49:25.004724Z 246110 Query    SET character_set_results = NULL
2021-04-05T08:49:25.500181Z 246111 Connect  qlik@192.168.1.12 on qlik using TCP/IP
2021-04-05T08:49:25.500429Z 246111 Query    SET NAMES utf8mb4
2021-04-05T08:49:25.500613Z 246111 Query    SET character_set_results = NULL
2021-04-05T08:49:25.763629Z 246112 Connect  qlik@192.168.1.12 on qlik using TCP/IP
2021-04-05T08:49:25.763942Z 246112 Query    SET NAMES utf8mb4
2021-04-05T08:49:25.764126Z 246112 Query    SET character_set_results = NULL

This is a followup of one of my own question, that has been closed for duplication reason : Many useless SHOW VARIABLES queries with mysql-connector-java-5.1.32

Here are the solutions I have been pointed to (but they did not solve my problem).

Namely :

  1. upgrade my connector version, but this is not feasible in my situation
  2. add parameters in the jdbc url such as cacheServerConfiguration=YES and useLocalSessionState=YES

Solution 2 partially solved the problem, as now, the connection queries are less verbose.

Before putting cacheServerConfiguration=YES, it looked like :

2021-04-03T03:00:00.269907Z     183836 Connect  qlik@192.168.1.11 on qlik using TCP/IP
2021-04-03T03:00:00.270146Z     183836 Query    /* mysql-connector-java-5.1.32 ( Revision: jess.balint@oracle.com-20140716155848-mlwabor66widht1n ) */SHOW VARIABLES WHERE Variable_name ='language' OR Variable_name = 'net_write_timeout' OR Variable_name = 'interactive_timeout' OR Variable_name = 'wait_timeout' OR Variable_name = 'character_set_client' OR Variable_name = 'character_set_connection' OR Variable_name = 'character_set' OR Variable_name = 'character_set_server' OR Variable_name = 'tx_isolation' OR Variable_name = 'transaction_isolation' OR Variable_name = 'character_set_results' OR Variable_name = 'timezone' OR Variable_name = 'time_zone' OR Variable_name = 'system_time_zone' OR Variable_name = 'lower_case_table_names' OR Variable_name = 'max_allowed_packet' OR Variable_name = 'net_buffer_length' OR Variable_name = 'sql_mode' OR Variable_name = 'query_cache_type' OR Variable_name = 'query_cache_size' OR Variable_name = 'license' OR Variable_name = 'init_connect'
2021-04-03T03:00:00.272279Z     183836 Query    /* mysql-connector-java-5.1.32 ( Revision: jess.balint@oracle.com-20140716155848-mlwabor66widht1n ) */SELECT @@session.auto_increment_increment
2021-04-03T03:00:00.272463Z     183836 Query    SET NAMES utf8mb4
2021-04-03T03:00:00.272557Z     183836 Query    SET character_set_results = NULL
2021-04-03T03:00:00.272641Z     183836 Query    SET autocommit=1
2021-04-03T03:00:00.272736Z     183836 Query    select @@session.tx_read_only

After putting cacheServerConfiguration=YES, it looked like :

2021-04-04T23:49:24.306318Z 246109 Query    SET character_set_results = NULL
2021-04-04T23:49:25.004308Z 246110 Connect  qlik@192.168.1.12 on qlik using TCP/IP
2021-04-04T23:49:25.004553Z 246110 Query    SET NAMES utf8mb4
2021-04-04T32:49:25.004724Z 246110 Query    SET character_set_results = NULL    
2021-04-04T32:49:25.004924Z 246110 Query    SET autocommit=1

And now after putting useLocalSessionState=YES, it looks like :

2021-04-05T07:15:22.853774Z 243663 Connect  qlik@192.168.1.11 on qlik using TCP/IP
2021-04-05T07:15:22.854052Z 243663 Query    SET NAMES utf8mb4
2021-04-05T07:15:22.854238Z 243663 Query    SET character_set_results = NULL
2021-04-05T07:15:27.703694Z 243664 Connect  qlik@192.168.1.11 on qlik using TCP/IP
2021-04-05T07:15:27.703960Z 243664 Query    SET NAMES utf8mb4
2021-04-05T07:15:27.704134Z 243664 Query    SET character_set_results = NULL

(the autocommit part is no more there)

However, I still get those connections, which was what I don't want in the first place.

Perhaps other parameters in the url could help to remove completely these connections ? (but I'm quite shy at doing it as I don't want to risk causing a problem)

Or tell me if you have other ideas...

AlexandrePhili
  • 73
  • 1
  • 1
  • 3
  • 1
    Useless in what sense? As I understand, you're using HikariCP with the default settings of `minimumIdle=10` and `maximumPoolSize=10`, meaning exactly 10 connections are kept in the pool at all times. If you want to reduce this number, just tweak the configuration to your liking – crizzis Apr 05 '21 at 09:22
  • That's a good point, my settings are : - minimumIdle=10 - maximumPoolSize=48 However, I see way more simultaneous connections when I do "show processlist;". Meaning that it sometimes reachs the 400 maximum, which causes problems of connections when we want to access the database. – AlexandrePhili Apr 05 '21 at 10:00
  • Also, this connection is used by the application once everyday at 5am to perform maintenance operations, which lasts 20 minutes. And then we don't need it anymore for the remaining of the day. So would be nice if it could shut down completely during this idle time. I checked inside the code, and we use try-with-resources Statements that are supposed to automatically close the connections that have been opened, so I don't know what to do.. – AlexandrePhili Apr 05 '21 at 10:15
  • 1
    The whole point of connection pools is to keep connections open. It is unclear to me what your real problem is, and what you mean with _"many useless MySQL connections"_, or why your consider the logged statements problematic. If you think your application is opening too many connections, you need to tweak your pool configuration (e.g. maybe your connections are recycled to quick, or you're allocating too many overall) and maybe hunt for connection leaks. – Mark Rotteveel Apr 05 '21 at 11:13
  • Thanks Mark, I already started to tweak the minimumIdle & maximumPoolSize values. Also, is there a way to close the pool once my job has been done, since it starts at 5 am and lasts 20 minutes, then I don't need any connection until the following day... – AlexandrePhili Apr 05 '21 at 11:32
  • @MarkRotteveel : finally, I discovered that I used: cf.setProperty("hibernate.connection.driver_class", "com.mysql.jdbc.Driver"); instead of : cf.setProperty("hibernate.connection.provider_class", "org.hibernate.hikaricp.internal.HikariCPConnectionProvider"); It seems the hikari one is better, as now there is only one process when I do "show processlist", instead of a list of many processes (one process for each connection). – AlexandrePhili Apr 05 '21 at 19:26

0 Answers0