[#CONJ-824] Aurora db connection with maria driver after minor version upgrade

[CONJ-824] Aurora db connection with maria driver after minor version upgrade Created: 2020-08-29 Updated: 2021-05-17 Resolved: 2021-05-17
Status:	Closed
Project:	MariaDB Connector/J
Component/s:	aurora, JDBC compatibility
Affects Version/s:	2.6.2
Fix Version/s:	N/A

Type:

Bug

Priority:

Major

Reporter:

Heungsu Ha

Assignee:

Diego Dupin

Resolution:

Not a Bug

Votes:

Labels:

None

Environment:

Aurora mysql 2.07.2
Java 8 (1.8.0_191-b12)
Mariadb driver 2.6.2 (mariadb-java-cleint-2.6.2.jar)

Attachments:

mysqlconn2.java

Description

When I connect to Aurora Mysql 2.07.2 using route53 cname, error occurred.
In 2.04.8 of Aurora Mysql, there is no problem to connect to DB using Route 53 cname.
However, after upgrade to 2.07.2, slave node can't be connected.

I made simple java code like this. (It is not easy to read and I attached the source code file to this issue.)

import java.sql.*;
public class mysqlconn2 {
static final String JDBC_DRIVER = "org.mariadb.jdbc.Driver";
static final String DB_URL = "jdbc:mysql://slavedb.hhs.local:3306/board";
static final String USERNAME = "dbadmin";        static final String PASSWORD = "123456";
public static void main(String[] args) {
Connection conn = null;                Statement stmt = null;
try{                        //Class.forName(JDBC_DRIVER);
                  Class.forName("org.mariadb.jdbc.Driver");
conn = DriverManager.getConnection(DB_URL,USERNAME,PASSWORD);
                     System.out.println("\n- MySQL Connection");
stmt = conn.createStatement();
String sql;
sql = "SELECT * FROM board";
ResultSet rs = stmt.executeQuery(sql);
            .... (I attached whole source code to this issue.)

---execution error is like this.

java.sql.SQLNonTransientConnectionException: Could not connect to address=(host=slavedb.hhs.local)(port=3306)(type=master) : (conn=816) 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) at org.mariadb.jdbc.internal.util.Utils.retrieveProxy(Utils.java:635) at org.mariadb.jdbc.MariaDbConnection.newConnection(MariaDbConnection.java:150) at org.mariadb.jdbc.Driver.connect(Driver.java:89) at java.sql.DriverManager.getConnection(DriverManager.java:664) at java.sql.DriverManager.getConnection(DriverManager.java:247) at mysqlconn2.main(mysqlconn2.java:17)Caused by: java.sql.SQLNonTransientConnectionException: (conn=816) 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:183) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.requestSessionDataWithShow(AbstractConnectProtocol.java:1093) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.additionalData(AbstractConnectProtocol.java:1106) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.postConnectionQueries(AbstractConnectProtocol.java:880) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.createConnection(AbstractConnectProtocol.java:588) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.connectWithoutProxy(AbstractConnectProtocol.java:1367) ... 6 moreCaused by: java.sql.SQLNonTransientConnectionException: (conn=816) 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) at org.mariadb.jdbc.internal.protocol.AbstractQueryProtocol.executeQuery(AbstractQueryProtocol.java:249) at org.mariadb.jdbc.internal.protocol.AbstractConnectProtocol.requestSessionDataWithShow(AbstractConnectProtocol.java:1068) ... 10 more

I tried to use aurora option and result is also same:error.But replication option worked, but slow.
jdbc:mysql:aurora://slavedb.hhs.local:3306/board -> fail
jdbc:mysql:replication://slavedb.hhs.local:3306/board -> success , but slow.
(masterdb.hhs.local don't have problem to connect
I matched Route 53 cname with cluster endpoints of DB.
masterdb.hhs.local -> up-version-2-07-2.cluster-xxxxxxxxxxx.ap-northeast-2.rds.amazonaws.com
slavedb.hhs.lcao ->up-version-2-07-2.cluster-ro-xxxxxxxxxxx.ap-northeast-2.rds.amazonaws.com
)

In 2.04.8 of Aurora DB, there is no problem connecting to db with this url.
jdbc:mysql://slavedb.hhs.local:3306/board

I guess this problem is caused by that maria driver can't support the recent version of aurora mysql (over 2.04.8).
Can you check this problem please?
Thank you very much

Today, I compiled source of maria 2.6.2 connector/j and tried to find when this error occurred.
In the Results.java, executionResults value is null!
public boolean commandEnd() {
if (cmdInformation != null) {
System.out.println("\n test " + executionResults);      //test!!
if (executionResults != null && !cmdInformation.isCurrentUpdateCount())

{ * resultSet = executionResults.poll(); }

else

{ resultSet = null; }

cmdInformation.setRewrite(rewritten);
} else

{ resultSet = null; }

return cmdInformation != null;
}

Best Regards,
Heungsu

Comments

Comment by Heungsu Ha [ 2020-09-03 ]

I solved the issue by adding URL with "&usePipelineAuth=false".
But I wonder why this option is needed or not according to version (2.04.8 is fine when usePipelineAuth=true)
What exactly is the reason?
I tried to know the reason by recompliing source by adding logging, desribed above.
Can I know the reason? I need explanation for customer.

Comment by Diego Dupin [ 2020-09-03 ]

pipeline consist to send multiple queries and then only read multiple responses.
like this. This save network time.

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.

Comment by Heungsu Ha [ 2020-09-08 ]

Thank you for the answer.
With debugging the source code several times, I have almost understood what you answered.
Now there is one question.
After I replaced URL with Aurora mysql 2.04.8, it also has "usePipelineAuth" as "True" and connection is successfully established.
With the version 2.07.2 of Aurora, the connection has error.
Does this mean that 2.04.8 version of Aurora has no problem with pipelining at connection?
I 'm not sure 2.04.8 version support this pipelining yet.
However I guess it may be true.
Because the exception error occurred when calling AAbstractConnectProtocol.readPipelineAdditionalData at 2.07.2 version.
But there is no exception through same method above at 2.04.8 version.
Can you check this finally?

Comment by László Végh [ 2020-09-28 ]

I ran into this issue today using driver version 2.7.0.

Comment by Diego Dupin [ 2021-05-17 ]

Since we have no Aurora server to try to reproduced that, i'll close this issue.
Last answers would make think that some corrections have been added on aurora, but not stable.
Since this really depends on aurora (or more probably some underlying proxy they use), only good solution is to use option `usePipelineAuth=false&useBatchMultiSend=false`

Feel free to create a new ticket link to this one if reproduced even with those options.

Generated at Thu Feb 08 03:18:35 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.

[CONJ-824] Aurora db connection with maria driver after minor version upgrade Created: 2020-08-29 Updated: 2021-05-17 Resolved: 2021-05-17