英文:
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_wait
和max_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代理存在已知的竞态条件问题,导致跳过代理缓冲区中的其他查询。连接可能会处于等待查询响应的挂起状态。
在身份验证期间,套接字具有超时,这将导致引发您描述的错误。
因此,如果启用了usePipelineAuth
或useBatchMultiSend
,它可能会工作...也可能不会。
在使用Aurora时,必须禁用所有流水线选项(usePipelineAuth
和useBatchMultiSend
)。该问题已多次报告给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.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论