MySQL Bugs: #63080: Comparison to VARCHAR variable in if statement predicate leads to 100% CPU use

Bug #63080	Comparison to VARCHAR variable in if statement predicate leads to 100% CPU use
Submitted:	2 Nov 2011 18:04	Modified:	2 Dec 2011 18:31
Reporter:	Benjamin Williams	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: Stored Routines	Severity:	S2 (Serious)
Version:	5.5.8	OS:	Linux (Red Hat Enterprise Linux Server release 5.6 (Tikanga))
Assigned to:		CPU Architecture:	Any

Description:
I have a simple stored procedure which, when called repeatedly via a long-lived connection, causes the mysql process to slowly climb to 100% CPU use. The key seems to be having a string comparison in an if statement predicate, either with STRCMP or VARIABLE = 'somestring'.

How to repeat:
Create a new test database with one stored procedure:

create database test;
use test;
delimiter $$
CREATE PROCEDURE `mysql_test_1`()
BEGIN
  DECLARE EMPTY_STRING VARCHAR(1);
  SET EMPTY_STRING = '';
  IF (STRCMP('test',EMPTY_STRING) = 0) THEN
       SELECT 1;
  END IF;
END$$

Call the procedure repeatedly with a simple program like this (Java example):

import java.sql.*;

public class ProcTest {
    public static void main(String[] argv) throws ClassNotFoundException, SQLException {
        ProcTest.class.getClassLoader().loadClass("com.mysql.jdbc.Driver");
        Connection conn = DriverManager.getConnection(
                "jdbc:mysql://localhost:3306/test",
                "root",
                "rootpass");
        CallableStatement stmt = conn.prepareCall("{call mysql_test_1()}");

        long startTime;
        while (true) {
            startTime = System.nanoTime();
            stmt.execute();
            System.out.println(System.nanoTime() - startTime);
        }
    }
}

Over time, the CPU usage of the mysql process will climb to 100% and the procedure execution times printed by the program will climb.

Suggested fix:
If I change my program to close and reopen the database connection every 1000 calls, the problem goes away, so possibly this is related to some resource that is only cleaned up on connection close?

Please, check if the same problem happens with a newer version, 5.5.17. This may be related to infamous bug #60025...

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".