1. Background
1.1. Interface to SQL
- interface : the interface used to query user info in batch, and the URL is similar as “/userinfo/batch?usids={usid1,usid2,usid3}”, and the parameter usids is mandatory
- handle param usids : the parameter usids will be splitted by delimiter comma into a list, and used as a inputting condition to query database in batches.
- SQL example:
SELECT us_id, us_name
FROM table
WHERE us_id in (
<foreach collection="usids" item="usid" index="index" open="(" close=")" separator=",">
#{usid, jdbcType=NUMERIC}
</foreach>1.2. An illegal interface invocation
- the invoker calls interface illegally withou passing any usid to the paramter usids, that is the param usids = “”
- after splitting the parameter usids, the result list is not null but empty, that is the size of list usids is zero
- then MyBatis use the list usids as query condition to select userinfo in batches(in fact, database should throw a error)
- when the request of querying userinfo handled by my service, the invoker did not get the response immediately until the request timed out
- One illegal request cause one server broken down, until all servers broken down
2. Preliminary investigation
Actually, I didn’t find any valuable ERROR or Exception info from log of application. So, I guess if MyBatis spliced string “null” somewhere to the condition of IN query when the list usids is empty, that is “SELECT * FROM table WHERE usid in (null)”. The reason of my deduction as following:
2.1. Debug the interceptor of MyBaits
I preliminarily debug the source code of Interceptor of MyBatis to check the splicing of dynamic SQL by putting on a breakpoint. Check the result:
Until this step, the process of dynamic SQL is normal. Check the IN query condition, there is no any placeholder “?”, that is mean the string “null” maybe spliced in another place if follow my guessing. Let’s carry on my deduction.
2.2. The empty condition IN query of MySQL – IN ()
As we known, a ERROR will be threw by MySQL if we execute a SQL like as ” SELECT * FROM table WHERE usid in () “, it is a error syntax.
2.3. The null condition IN query of MySQL – IN (null)
Based on the analysis of “2.1” & “2.2”, and according to the scenario of the issue that not receive the response immediately until request timed out. I futther believe that MyBatis, maybe, splicing string “null” to the mapper statement before invoking JDBC. Because we know, if MySQL execute a select statement with IN (null) will cause full table scanning. About this point, let’s take a look at a example:
type is “ALL”, this mean IN(null) caused full table scanning.
2.4. Local reproduce failed
Based on above analysises, I reproduce the issue from my local. I reconnect the database to the testing RDS of Aliyun from my local. If my deduction is right, the result of reproducing will be :
- the invoker cannot get the response immediately for a little while
- the application will hung on executing SQL of scanning full table
- the performance of RDS will be promoted
But the result is always contrary to what you want. I haven’t seen any performance promoted of RDS, not even the records of executing SQL form log. What’s going on? 🙁 My guessing is wrong. So I have to debug the source code.
3. Debug the source code
3.1. My Application is hung
Locally, I reproduced the issue several times. But I don’t know which line code hangs the whole service. As we known, the situation can cause a lot of performance loss, CPU or Memory. So, let’s check the usage of resources of my PC. Before I reproduce, the resources usage as following:
Well, well, well, that’s all right. Then I reproduce the issuse, let’s check the result:
Oh, my mama~ just a little while, the CPU and Memory reach their usage limit, CUP 100%, Memory 93%. And the root cause comes from IntelliJ held my Application.
3.2. Continue to investigate by JvisualVM
3.2.1. The usage of CPU & Memory of JVM
Then I launch the local JvisualVM to help me locate the issue. I start my application again and reproduce the issue, just a little while, I get a screenshot of the usage of JVM resource of my application:
As you can see, the CPU & Memory is soared, other resources is ok.
3.2.2. The threaddump of JVM
Next, let’s have a look at the threaddump of JVM to try find some clues.
After a little while, I dump the thread again.
Analyse the two threaddumps, we can find a method “parseInCondition” from ShardingJDBC is alway running between the two threaddumps. Obviously, that’s the problem. Next, I put a breakpoint on line 133 of this method.
3.3. The final problematic code
Finally, I find out the problematic code in ShardingJDBC, the code snippet as follow:
The full service hung on the do-while loop. This code snippet used to parse IN query SQL script, when the condition of IN query from MyBatis is emtpy(IN()), dead loop will happen in the code, then it will lead to increase the usage of CPU & Memory of System.
3.4. The version of problemaic JAR
sharding-jdbc-code-2.0.0.jar