问题:将MariaDB客户端集成到MySQL数据库中。

huangapple go评论72阅读模式
英文:

Issue integrating mariaDB client with MySQL DB

问题

我正在运行一个使用MySQL作为DB服务器的Java应用程序。DB是AWS RDS。MySQL连接器发布在GNU/GPL下,因此必须用更宽松的东西替代。Maria DB客户端声称兼容。但自从使用了MariaDB客户端后,在DB密集活动期间我们一直看到以下间歇性问题:

异常:-
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/Projectname] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection] with root cause
java.sql.SQLNonTransientConnectionException: (conn=1530488) Connection is closed
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:187)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.cmdPrologue(AbstractQueryProtocol.java:1940)

Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)

Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=XXXXXXXXXX)(port=3306)(type=master) : (conn=1530487) could not load system variables
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:192)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1372)

我尝试将time_waitmax_allowed_packet增加到允许的最大值,但问题仍然存在。

版本信息:-
MySQL - 5.7.26
MariaDB连接器 - 2.6.2

英文:

I am running a java application with MySQL as the DB server. DB is a AWS RDS. The MySQL connector is published under GNU/GPL so it has to be substituted with something more lenient. Maria DB client claimed to be compatible. But ever since MariaDB client is being used we are seeing the below intermittent issue during DB intensive activities:

Excption:-
SEVERE: Servlet.service() for servlet [dispatcher] in context with path [/Projectname] threw exception [Request processing failed; nested exception is org.springframework.transaction.CannotCreateTransactionException: Could not open Hibernate Session for transaction; nested exception is org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection] with root cause
java.sql.SQLNonTransientConnectionException: (conn=1530488) Connection is closed
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:187)
	at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.cmdPrologue(AbstractQueryProtocol.java:1940)

Caused by: org.hibernate.exception.JDBCConnectionException: Unable to acquire JDBC Connection
	at org.hibernate.exception.internal.SQLExceptionTypeDelegate.convert(SQLExceptionTypeDelegate.java:48)
	at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:42)
	at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:113)

Caused by: java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=XXXXXXXXXX)(port=3306)(type=master) : (conn=1530487) could not load system variables
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.createException(ExceptionFactory.java:73)
	at org.mariadb.jdbc.internal.util.exceptions.ExceptionFactory.create(ExceptionFactory.java:192)
	at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1372)

I have tried increasing the time_wait and max_allowed_packet to the maximum allowed but the issue still exists.

Versions:-
MySQL - 5.7.26
MariaDB connector - 2.6.2

答案1

得分: 6

只是偶然发现这个,找到了一个答案。在我的情况下,这是针对Aurora 1 MySQL数据库(相当于5.6版本)。

根据Diego Dupin在https://jira.mariadb.org/browse/CONJ-824中提到的:

唯一的好解决方案是使用选项usePipelineAuth=false&useBatchMultiSend=false

因此,您的数据库连接字符串可能如下所示:
jdbc:mysql://127.0.0.1:3307/mydbname?usePipelineAuth=false&useBatchMultiSend=false

更多细节:

Aurora代理存在已知的竞态条件问题,导致跳过代理缓冲区中的其他查询。连接可能会处于等待查询响应的挂起状态。
在身份验证期间,套接字具有超时,这将导致引发您描述的错误。

因此,如果启用了usePipelineAuthuseBatchMultiSend,它可能会工作...也可能不会。
在使用Aurora时,必须禁用所有流水线选项(usePipelineAuthuseBatchMultiSend)。该问题已多次报告给Aurora,但尚未得到修复。

英文:

Just came across this, found an answer. On my side this was for an Aurora 1 MySQL DB (equiv to 5.6).

As per Diego Dupin in https://jira.mariadb.org/browse/CONJ-824:

only good solution is to use option `usePipelineAuth=false&useBatchMultiSend=false`

So your DB connection string might look like this:
jdbc:mysql://127.0.0.1:3307/mydbname?usePipelineAuth=false&useBatchMultiSend=false

More details:

Aurora proxy has a known race condition issue that results in skipping other 
queries in proxy buffer. connection might be in hang waiting for query response. 
During authentication, socket has a timeout, that will result throwing the error 
you describe.

So if `usePipelineAuth` or `useBatchMultiSend` is enable, it might work ... or not. 
All pipeline options (`usePipelineAuth` and `useBatchMultiSend`) must be disabled when using aurora. 
Problem has been reported to aurora a few times without correction.

huangapple
  • 本文由 发表于 2020年8月6日 20:40:30
  • 转载请务必保留本文链接:https://go.coder-hub.com/63283846.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定