Bug #10629 Resource leakage causes SELECT with BINARY to eventually stop working

• Submitted: 13 May 2005 17:08
• Modified: 13 May 2005 19:25
• Reporter: [ name withheld ]
• Status: Can't repeat
• Category: MySQL Server
• Severity: S2 (Serious)
• Version: 4.1.x
• OS: Linux (FC3 RH9)
• Assigned to: Mark Matthews
• CPU Architecture: Any

[13 May 2005 17:08] [ name withheld ]

Description:
A persistent incremental resource leakage occurs when using SELECT with the BINARY option.   Queries will initially be in the low millisecond range and after 10,000 queries each SELECT will take 150 msec, after 40,000 queries SELECT slows down to where it appears to stop working [4-5 seconds per select].

This was checked using MySQL 4.1.x servers and all JDBC drivers from the oldest to the latest  release [alpha].  All had the same issue.

Issue occurs even if the allocated resources for the query are released.  That is, even if the database connection is closed the problem is persistent indicating that it is likely to be in the Server code.

How to repeat:

Create a small table with containing VARCHAR columns, and one INT column.  Populate the VARCHAR with some random mixed-case text.

Write a SELECT loop with timing.

some java pseudo code
----------------------
public void timedSelect() {
long s1, e1;
s1 = System.currentTimeMillis();

String sql = "SELECT lookupId FROM mixedCaseTable WHERE BINARY mixedText='FixMe' LIMIT 1";
try { 
      Connection conn = ...
      Statement statement = conn.createStatement();  
      ResultSet resultset    = statement.executeQuery(sql);
   } catch (Exception ex) {
   } finally {
       // Close the resultset
       if ( resultset != null ) {
            try {
               resultset.close();
            } catch (Exception ex) {
            }
         }
         
         // Close the statement
         if statement != null) {
            try {
               statement.close();
            } catch (Exception ex) {
            }
         }
         
         // Close the database connection
         if (conn != null) {
            conn.close();
         }
    }

e1 = System.currentTimeMillis();

System.out.println("[timedSelect] elapsed time: " + (e1 - s1) );
}

-------------------

If the timedSelect() is called 40,000 times, the initial elapsed time will be in low milliseconds and grow toward 4-5 seconds per SELECT.  It's pretty linear/incremental.

Suggested fix:
None

[13 May 2005 17:33] Mark Matthews

When you say resource leakage without the "binary", do you mean client-side, or server-side (you don't make it clear. Since you've run this under a profiler, you must have an insight into that)?

[13 May 2005 19:25] Mark Matthews

How many rows are in this table?

I just tried your testcase with the nightly of Connector/J 3.1.x, and MySQL-4.1.11 on FC3 with a _one_ row table, both using a new connection for every round, and using a single connection for the entire test, and almost all queries were on the order of a few ms. over 40,000 tests.

This was w/ JDK-1.5.0, btw. Are you using something different?