Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "how to solve MySQL JDBC-driven bug". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

1.1 background of the question

The company does e-commerce system, and the whole system is built on Huawei cloud. When designing the system, considering the large number of follow-up users and orders, we need to use some components of large database. Relational database, considering the rapid growth of subsequent data, is not written directly to MySQL, but uses Huawei Cloud's distributed database middleware DDM.

After using DDM, you can directly increase the number of MySQL read instances without business awareness, and linearly improve the read performance. It also supports sub-database and sub-table at the middleware level, providing the operation of a large number of relational databases. It is simply customized for e-commerce systems.

DDM itself provides services in the form of clusters, and multiple connected IP addresses are open to business. A layer of load balancing is required. If you use the traditional form of adding LB to do load balancing, there will be one more layer of transfer, resulting in performance loss. Therefore, the client load balancing capability provided by MySQL-JDBC is directly used.

Services can access multiple DDM nodes through the Loadbalance of MySQL-JDBC. MySQL-JDBC provides load balancing capabilities.

1.2 problem description

The use of MySQL client load balancing capabilities, has been running well, the performance is loud. But some time ago, there was a business request failure for no reason. I am in charge of the e-commerce order module, involving the real Money, this problem scared the baby in a cold sweat. no, no, no. Quickly check the backend log and find that there is an exception in accessing DDM. Without saying a word, you directly submit a ticket to Huawei Cloud DDM service.

[WARN] [2018-07-08 23:11:29] [MySqlValidConnectionChecker:isValidConnection ()] [DubboServerHandler-172.31.0.146:8080-thread-64-txId=00000000657615aa] Unexpected error in ping

Java.lang.reflect.InvocationTargetException

At sun.reflect.NativeMethodAccessorImpl.invoke0 (NativeMethod)

At sun.reflect.NativeMethodAccessorImpl.invoke (NativeMethodAccessorImpl.java:62)

At sun.reflect.DelegatingMethodAccessorImpl.invoke (DelegatingMethodAccessorImpl.java:43)

At java.lang.reflect.Method.invoke (Method.java:498)

At com.alibaba.druid.pool.vendor.MySqlValidConnectionChecker.isValidConnection (MySqlValidConnectionChecker.java:98)

At com.alibaba.druid.pool.DruidAbstractDataSource.testConnectionInternal (DruidAbstractDataSource.java:1252)

At com.alibaba.druid.pool.DruidDataSource.getConnectionDirect (DruidDataSource.java:981)

I have to say, Huawei Cloud's service is still very good, less than half an hour to contact me, but also with me to troubleshoot the problem.

Take down the log of our business and analyze it with the support staff of DDM, and find that the error is as follows:

The root cause is the MySQL-driven bug, which leads to the overflow of the StackOverflow local stack. I really misunderstood the DDM service. I'm sorry.

Caused by: java.lang.StackOverflowError

At com.mysql.jdbc.StringUtils.getBytes (StringUtils.java:2360)

At com.mysql.jdbc.StringUtils.getBytes (StringUtils.java:2344)

At com.mysql.jdbc.StringUtils.getBytes (StringUtils.java:568)

At com.mysql.jdbc.StringUtils.getBytes (StringUtils.java:626)

At com.mysql.jdbc.Buffer.writeStringNoNull (Buffer.java:670)

At com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2636)

At com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2483)

At com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal (ConnectionImpl.java:4961)

At com.mysql.jdbc.ConnectionImpl.setReadOnly (ConnectionImpl.java:4954)

At com.mysql.jdbc.MultiHostConnectionProxy.syncSessionState (MultiHostConnectionProxy.java:381)

At com.mysql.jdbc.MultiHostConnectionProxy.syncSessionState (MultiHostConnectionProxy.java:366)

At com.mysql.jdbc.LoadBalancedConnectionProxy.pickNewConnection (LoadBalancedConnectionProxy.java:344)

At com.mysql.jdbc.LoadBalancedAutoCommitInterceptor.postProcess (LoadBalancedAutoCommitInterceptor.java:104)

At com.mysql.jdbc.MysqlIO.invokeStatementInterceptorsPost (MysqlIO.java:2885)

At com.mysql.jdbc.MysqlIO.sqlQueryDirect (MysqlIO.java:2808)

At com.mysql.jdbc.ConnectionImpl.execSQL (ConnectionImpl.java:2483)

At com.mysql.jdbc.ConnectionImpl.setReadOnlyInternal (ConnectionImpl.java:4961)

At com.mysql.jdbc.ConnectionImpl.setReadOnly (ConnectionImpl.java:4954)

At com.mysql.jdbc.MultiHostConnectionProxy.syncSessionState (MultiHostConnectionProxy.java:381)

At com.mysql.jdbc.MultiHostConnectionProxy.syncSessionState (MultiHostConnectionProxy.java:366)

At com.mysql.jdbc.LoadBalancedConnectionProxy.pickNewConnection (LoadBalancedConnectionProxy.java:344)

At com.mysql.jdbc.LoadBalancedAutoCommitInterceptor.postProcess (LoadBalancedAutoCommitInterceptor.java:104)

At com.mysql.jdbc.MysqlIO.invokeStatementInterceptorsPost (MysqlIO.java:2885)

. . . 10000 lines are omitted here.

As you can see from the stack, an exception triggers the bug of MySQL-JDBC, causing a circular call until the stack overflows.

At the suggestion of Huawei DDM support staff, the driver code is decompiled. From the case of decompilation, we can see that there is indeed the possibility of loop nesting.

Loadbalance polling connection-> synchronizing the status of new and old connections-> sending sql to the server-> Loadbalance polling connection.

The related code is as follows:

The pickNewConnection () function of com/mysql/jdbc/LoadBalancedConnectionProxy.java

For (int hostsTried =, hostsToTry = this.hostList.size (); hostsTried

< hostsToTry; hostsTried++) { ConnectionImpl newConn = null; try { newConn = this.balancer.pickConnection(this, Collections.unmodifiableList(this.hostList), Collections.unmodifiableMap(this.liveConnections), this.responseTimes.clone(), this.retriesAllDown); if (this.currentConnection != null) { if (pingBeforeReturn) { if (pingTimeout == ) { newConn.ping(); } else { newConn.pingInternal(true, pingTimeout); } } syncSessionState(this.currentConnection, newConn); } this.currentConnection = newConn; return; } catch (SQLException e) { if (shouldExceptionTriggerConnectionSwitch(e) && newConn != null) { // connection error, close up shop on current connection invalidateConnection(newConn); } } } syncSessionState()函数，在执行完SQL之后，又会调用postProcess()函数，如此嵌套循环就来了。 if (!this.conn.getAutoCommit()) { this.matchingAfterStatementCount = ; } else { if (this.proxy == null && this.conn.isProxySet()) { MySQLConnection lcl_proxy = this.conn.getMultiHostSafeProxy(); while (lcl_proxy != null && !(lcl_proxy instanceof LoadBalancedMySQLConnection)) { lcl_proxy = lcl_proxy.getMultiHostSafeProxy(); } if (lcl_proxy != null) { this.proxy = ((LoadBalancedMySQLConnection) lcl_proxy).getThisAsProxy(); } } if (this.proxy != null) { if (this.matchingAfterStatementRegex == null || sql.matches(this.matchingAfterStatementRegex)) { this.matchingAfterStatementCount++; } } if (this.matchingAfterStatementCount >

= this.matchingAfterStatementThreshold) {this.matchingAfterStatementCount =; try {if (this.proxy! = null) {this.proxy.pickNewConnection ();}} catch (SQLException e) {}

With such an obvious bug, I don't believe that MySQL will not find out. At present, we are using the 5.1.44 driver. After looking at the latest 5.1.66 code, we found that this problem has indeed been fixed, as follows:

Public ResultSetInternalMethods postProcess (String sql, Statement interceptedStatement, ResultSetInternalMethods originalResultSet, Connection connection, int warningCount, boolean noIndexUsed, boolean noGoodIndexUsed, SQLException statementException) throws SQLException {if (! this.countStatements | | StringUtils.startsWithIgnoreCase (sql, "SET") | | StringUtils.startsWithIgnoreCase (sql, "SHOW")) {return originalResultSet;}

Loop nesting is avoided by filtering out SET and SHOW statements.

But 5.1.66 introduces a new bug, and since there is not a SQL in every place where postProcess is called, the code here will empty the pointer exception. Don't developers of MySQL JDBC do testing?

No way, after analyzing the code of 5.1.44 below, it is found that the occurrence of loop nesting can be avoided by properly adjusting the value of the parameter loadBalanceAutoCommitStatementThreshold. Our environment has been changed to 5, and after the revision, it has been running smoothly for a week, and there have been no more problems.

1.3 modify the scheme

The loadBalanceAutoCommitStatementThreshold has been modified to 5, but the problem introduced is that if the business contains some time-consuming SQL, the load of the DDM may be uneven. However, so far, it seems to be good, DDM performance is relatively strong.

This is the end of the content of "how to solve the MySQL JDBC driver bug". Thank you for your reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.