balin998
commented
1 year ago I encountered the same error, which requires the following conditions to be met at the same time:
- Using azure-cloud-mysql (version: 5.7.32-log/5.6.50-log), manually installed mysql-5.7.28 on Azure virtual machine shows no issues.
- Optimization parameters for MySQL have been configured in Hikari:
useServerPrepStmts=true. - The version of mysql-connector-j must be: 8.0.32.
Breaking any of the above conditions will not cause the problem, for example:
- Using a self-installed MySQL instance, even if it is installed on an Azure virtual machine.
- Removing the parameter configuration of
useServerPrepStmts=true. Other parameters may also cause this problem. - Use new version of mysql-connector later than 8.0.32, for example, 8.0.33
Problem recurrence
build.gradle
plugins {
id('java')
}
sourceCompatibility = JavaVersion.VERSION_17
dependencies {
implementation 'com.zaxxer:HikariCP:5.0.1'
// conditon2: mysql-connector-j version should be 8.0.32
// It is recommended to use "com.mysql:mysql-connector-java" instead of "mysql:mysql-connector-java".
runtimeOnly 'com.mysql:mysql-connector-j:8.0.32'
}test table: t1
CREATE TABLE `t1` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`fld1` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8mb4;test code: A1.java
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;
import java.sql.SQLException;
import java.time.Duration;
import java.util.Properties;
public class A1 {
public static void main(String[] args) throws SQLException {
final var config = new HikariConfig();
// condition 1: use an azure-cloud-mysql instance
config.setJdbcUrl("jdbc:mysql://yourHost:3306/yourDatabase");
config.setUsername("yourUser");
config.setPassword("yourPass");
// Make sure that maxLifetime/connectionTimeout is less than the relevant configuration in MySQL.
config.setMaxLifetime(Duration.ofSeconds(30).toMillis());
config.setConnectionTimeout(Duration.ofSeconds(3).toMillis());
Properties properties = new Properties();
// condition 3: useServerPrepStmts=true
properties.setProperty("useServerPrepStmts", "true");
config.setDataSourceProperties(properties);
try (var pool = new HikariDataSource(config)) {
try (var conn = pool.getConnection();
var pst = conn.prepareStatement("INSERT INTO t1(fld1) values(?) ")) {
pst.setString(1, System.currentTimeMillis() + "");
final var insert = pst.executeUpdate();
System.out.println("insert = " + insert);
}
}
}
}btw The correspondence between Springboot version and the default included mysql-connector-j version may be unordered, and the correspondence can be viewed in the official website instructions. The tested correspondence is as follows:
| springboot | mysql-connector-j | match condition-2? |
|---|---|---|
| 2.7.16 | 8.0.33 | NO |
| 3.0.2 | 8.0.32 | YES |
| 3.1.4 | 8.0.33 | NO |
As described above, under the premise of meeting conditions 1 and 3, when using Springboot 3.0.2 and not explicitly declaring the version of mysql-connector-j, mysql-connector-j-8.0.32 was introduced, thus meeting condition 2 and triggering the Communications link failure error. However, When downgraded to Springboot 2.7.16 or using Springboot 3.1.4, mysql-connector-j-8.0.33 will be introduced, which does not meet condition 2, and the program can run normally. When using Springboot 3.0.2, if you wish to circumvent condition 2, you need to explicitly specify the version of mysql-connector-j to 8.0.33.
runtimeOnly 'com.mysql:mysql-connector-j:8.0.33'However, I don't know if this is a bug in HikariCp or a problem with mysql-connector-j?
JWebDev
Hi,
Help me to understand and solve this error. I've been trying to figure out different options for days/weeks now. I probably read everything that is on the Internet and tried all kinds of options and combinations. HikariCP refuses to work as expected.
So, I have HikariCP-5.0.1 and MySQL 8.1
Pods are located in Kubernetes in various namespaces. There are no proxies, firewalls or service meshes between them. I run a task that runs 20-30 minutes. That is, at startup there is a connection to the database, then the logic works, and then I need to save the results in the database. It's simple case.
In the debug output I can see that Hikari pings the database every 30 seconds and says that everything is ok.
Here are the HIkariCP settings in Spring Autoconfiguration:
Here are the HikariCP settings in debug mode:
Here is my my.cnf:
Here are the results of the database queries:
show global variables LIKE "%timeout%";
show global variables LIKE "%wait%";
show global variables LIKE "%conn%";
Here is the complete Stacktrace::
Here is the application start and its end time.
Almost 21 minutes in total.
As you can see from the stacktrace message, the last successful connection to the database was 1,214,276 milliseconds ago, which is equivalent to 20.2379 minutes.
And I think that this is some kind of incomprehensible bug, because I have a running application for clients and it works great, there are no glitches. With the same settings and versions.
I would be grateful for your help. I have already seen many similar topics on the Internet and here, but not a single proposed solution helped.
Thanks!