Description of bugs caused by MySQL-JDBC driver-Mysql Tutorial-php.cn

Home

Database

Mysql Tutorial

Description of bugs caused by MySQL-JDBC driver

php是最好的语言

Aug 03, 2018 pm 01:56 PM

Problem Background

The company is engaged in e-commerce systems, and the entire system is built on Huawei Cloud. When designing the system, considering that the number of subsequent users and orders is relatively large, some large database components need to be used. For the relational database, considering the rapid growth of subsequent data volume, instead of directly writing to MySQL, Huawei Cloud's distributed database middleware DDM was used. After using DDM, you can directly increase the number of MySQL read instances without the business being aware of it, linearly improving the read performance. It also supports sub-databases and tables at the middleware level, providing operations for massive relational databases. It is simply customized for e-commerce systems.

DDM itself provides services in the form of a cluster, and multiple connection IP addresses are open to the business. A layer of load balancing is required. If you use the traditional method of adding LB for load balancing, there will be an extra layer of transit, which will cause performance losses. Therefore, the client load balancing capability provided by MySQL-JDBC is directly used.

The logical structure is shown in the figure below:

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

Problem Description

The client load balancing capability of MySQL JDBC driver has been running well and the performance is amazing. But a while ago, the business request failed for no reason. I am responsible for the e-commerce order module, which involves real Money. This problem scared me into a cold sweat...

So I quickly checked the background log and found that there was an exception when accessing DDM. Without saying anything, I directly Submitted a work order to Huawei Cloud DDM Service.

I have to say that Huawei Cloud's service is still very good. Within half an hour, a dedicated staff member contacted me and worked with me to troubleshoot the problem.

Took down the logs of our business and analyzed them with DDM support staff, and found that the error was reported as follows: The root cause turned out to be a bug in the MySQL driver, which caused the local stack overflow of StackOverflow. ……It turned out to be a murder caused by a bug. I misunderstood the DDM service. I’m really sorry.

It can be seen from the stack , an exception triggered a bug in MySQL-JDBC, causing loop calls until the stack overflowed. At the suggestion of Huawei DDM support staff, the driver code was decompiled. From the decompilation, we can see that there is indeed the possibility of loop nesting.

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

The relevant code is as follows:

## Such an obvious bug is not very I believe MySQL will not find it. We are currently using the 5.1.44 version of the driver. After checking the latest 5.1.66 code, we found that this problem has indeed been fixed. The code is as follows:

By filtering out SET and SHOW statements, loop nesting is avoided.

But 5.1.66 has introduced a new bug. Since not every place where postProcess is called has SQL, the code here will throw a null pointer exception. Don’t the developers of MySQL JDBC do testing...

No way, I analyzed the code of 5.1.44 and found that by appropriately adjusting the value of the loadBalanceAutoCommitStatementThreshold parameter, loop nesting can also be avoided. . Our environment was changed to 5. After the modification, it ran smoothly for a week without any problems.

Modification plan

loadBalanceAutoCommitStatementThreshold was modified to 5, but the problem introduced is that if the business contains some time-consuming SQL, it may cause DDM load imbalance . However, judging from the current situation, the performance of DDM is still relatively strong~

BUGs and strategies for PHP driver MongoDB integer issues

Configuring JDBC driver for MySql database under WebLogic

The above is the detailed content of Description of bugs caused by MySQL-JDBC driver. For more information, please follow other related articles on the PHP Chinese website!

Statement

The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

MySQL: BLOB and other no-sql storage, what are the differences?May 13, 2025 am 12:14 AM

MySQL'sBLOBissuitableforstoringbinarydatawithinarelationaldatabase,whileNoSQLoptionslikeMongoDB,Redis,andCassandraofferflexible,scalablesolutionsforunstructureddata.BLOBissimplerbutcanslowdownperformancewithlargedata;NoSQLprovidesbetterscalabilityand

MySQL Add User: Syntax, Options, and Security Best PracticesMay 13, 2025 am 12:12 AM

ToaddauserinMySQL,use:CREATEUSER'username'@'host'IDENTIFIEDBY'password';Here'showtodoitsecurely:1)Choosethehostcarefullytocontrolaccess.2)SetresourcelimitswithoptionslikeMAX_QUERIES_PER_HOUR.3)Usestrong,uniquepasswords.4)EnforceSSL/TLSconnectionswith

MySQL: How to avoid String Data Types common mistakes?May 13, 2025 am 12:09 AM

ToavoidcommonmistakeswithstringdatatypesinMySQL,understandstringtypenuances,choosetherighttype,andmanageencodingandcollationsettingseffectively.1)UseCHARforfixed-lengthstrings,VARCHARforvariable-length,andTEXT/BLOBforlargerdata.2)Setcorrectcharacters

MySQL: String Data Types and ENUMs?May 13, 2025 am 12:05 AM

MySQloffersechar, Varchar, text, Anddenumforstringdata.usecharforfixed-Lengthstrings, VarcharerForvariable-Length, text forlarger text, AndenumforenforcingdataAntegritywithaetofvalues.

MySQL BLOB: how to optimize BLOBs requestsMay 13, 2025 am 12:03 AM

Optimizing MySQLBLOB requests can be done through the following strategies: 1. Reduce the frequency of BLOB query, use independent requests or delay loading; 2. Select the appropriate BLOB type (such as TINYBLOB); 3. Separate the BLOB data into separate tables; 4. Compress the BLOB data at the application layer; 5. Index the BLOB metadata. These methods can effectively improve performance by combining monitoring, caching and data sharding in actual applications.

Adding Users to MySQL: The Complete TutorialMay 12, 2025 am 12:14 AM

Mastering the method of adding MySQL users is crucial for database administrators and developers because it ensures the security and access control of the database. 1) Create a new user using the CREATEUSER command, 2) Assign permissions through the GRANT command, 3) Use FLUSHPRIVILEGES to ensure permissions take effect, 4) Regularly audit and clean user accounts to maintain performance and security.

Mastering MySQL String Data Types: VARCHAR vs. TEXT vs. CHARMay 12, 2025 am 12:12 AM

ChooseCHARforfixed-lengthdata,VARCHARforvariable-lengthdata,andTEXTforlargetextfields.1)CHARisefficientforconsistent-lengthdatalikecodes.2)VARCHARsuitsvariable-lengthdatalikenames,balancingflexibilityandperformance.3)TEXTisidealforlargetextslikeartic

MySQL: String Data Types and Indexing: Best PracticesMay 12, 2025 am 12:11 AM

Best practices for handling string data types and indexes in MySQL include: 1) Selecting the appropriate string type, such as CHAR for fixed length, VARCHAR for variable length, and TEXT for large text; 2) Be cautious in indexing, avoid over-indexing, and create indexes for common queries; 3) Use prefix indexes and full-text indexes to optimize long string searches; 4) Regularly monitor and optimize indexes to keep indexes small and efficient. Through these methods, we can balance read and write performance and improve database efficiency.

See all articles