MySQL Bugs: #52924: Memory leak in MySQL version 5.0.18

Bug #52924	Memory leak in MySQL version 5.0.18
Submitted:	18 Apr 2010 4:26	Modified:	23 Jul 2010 18:43
Reporter:	Sri Kal	Email Updates:
Status:	Can't repeat	Impact on me:	None
Category:	MySQL Server: Memory storage engine	Severity:	S2 (Serious)
Version:	5.0.18	OS:	Any
Assigned to:		CPU Architecture:	Any

Description:
Hi,
 I am running connecting to MySQL server through JBoss. I am seeing a memeory leak of  min 50 MB per day on the SQL server. 

mysql> SHOW GLOBal status;

| Threads_cached                    | 0          |
| Threads_connected                 | 4          |
| Threads_created                   | 212        |
| Threads_running                   | 1          |
| Uptime                            | 471137     |
+-----------------------------------+------------+

I closed all the ResultSets and preparedStatements from my calling application.  Currently only one SELCT query is running from the client. I am not sure about the reason for leak. 

How to repeat:
Leave the MYSQL server running  for few days with only one SELECT query occuring per min basis.

5.0.18 has many memory leaks that were fixed..
can you try 5.0.91 or 5.1.45 and check if that works better?

Have you worked on any memeory leak related to use of SQL with JBOSS server?

From the Java application all we are doing it sending a select query every min. As the global status dump indicates this query is done on the currently connected one thread. Both the ResultSet and the PreparedStatements are closed after execution. Is there something else that also needs to be done from the Java application point to avoid memory leaks?

Had you tried newer version, 5.0.90 at least, as suggested? 

Please, send also the results of EXPLAIN for the SELECT statement you run.

We might have difficulty changing the version of SQL server. But here is the Query that is executed SELECT * FROM <TABLE_NAME>  WHERE <field>=?. There are about 20-30 of it executed every minute. All the 20-30 times Query is to look up the same table and same field.

Please, send the results of:

show create table <TABLE_NAME>\G

I want to know what storage engine is used and is that <field> indexed. my.cnf/my.ini file content may be also useful.

Even if this problem is a result of a bug in 5.0.18, nobody fixes bugs in old (years old!) versions. So, upgrade looks unavoidable in a long run.

MYSQL 5.0.18 is running on Solaris 10 Unix server.

OK, if you have problems finding/accessing my.cnf on Solaris, please, send the results of:

show global variables;

I am also waiting for that SHOW CREATE TABLE results.

Here is the output of 
show create table <table_name>;
------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table            | Create Table                                                                                                                                                                   |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| SW_DROPBOX_FILES | CREATE TABLE `SW_DROPBOX_FILES` (
  `filename` varchar(255) NOT NULL,
  `filesize` bigint(20) default NULL,
  PRIMARY KEY  (`filename`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+

The <field > = filename

Global Variables

Attachment: glob_var.txt (text/plain), 23.32 KiB.

Global Variables

Attachment: glob_var.txt (text/plain), 23.32 KiB.

Any updates on this bug. I want to see what is happening (queiries going back and forth) on my MYSQL server installed on Solaris 10. MYSQL administrator does not seem to work with Solaris 10. Can u suggest me other method of viewing the activities on the SQLserver?.

Had you already tried 5.0.90? Also I had noted these rows in the global status results uploaded:

| Com_stmt_close                    | 6750389    |
| Com_stmt_execute                  | 7114999    |
| Com_stmt_fetch                    | 0          |
| Com_stmt_prepare                  | 6756877    |

Compare number of prepared statements with number of statements closed. Looks like your application does NOT close them all properly...

I found that the reason for the memory leak was the 5.0.18 MySQL version did not handle SELECT..WHERE..NOTIN query well. Once the query was changed the memory leak stopped. I moved the intelligence of "NOTIN" to my client side code and problem fixed.

I see no evidence that this still happens in recent 5.0 versions, 5.0.90+.