MySQL Bugs: #16987: With parameter profileSQL=true the VM runs OutOfMemory

Bug #16987	With parameter profileSQL=true the VM runs OutOfMemory
Submitted:	31 Jan 2006 21:48	Modified:	26 Jul 2006 18:17
Reporter:	Andreas Longwitz	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S2 (Serious)
Version:	3.12	OS:	FreeBSD (FreeBSD 4.11)
Assigned to:		CPU Architecture:	Any

Description:
I use MySQL 4.0.24, MySQL Connector/J 3.1.12 and JBoss 4.02 with
default configuration. Then I deploy the datasource example-ds.xml from the documentation ( 1.4.3. Using Connector/J with JBoss) with one line added:

 <connection-property name="profileSQL">true</connection-property>

and the name and user of a real database.

In the JBoss logfile I can see some Profiler Events and the SHOW VARIABLES
query every three or five minutes. In this situation some com.mysql.jdbc
classes (e.g. Connection) grow up and after some hours JBoss runs OutOfMemory.  No other JBoss activity is necessary meanwhile, it seems to me that MySQL Connector/J causes the problem.

How to repeat:
Please look at my description.

Are you sure it doesn't have anything to do with the logging? 

Is there a reason you want to log _every_single_ query?

(it doesn't make sense in a production envrionment).

I am not sure if logging or the parameter profileSQL is the problem. 
If I start the VM with 
   -Xrunhprof:heap=all,doe=n,file=proxy.hprof,format=b
then I see the objects that grow up after some time:
Class                                                                                        Instance Count
class java.lang.String                                                                            323599
class [C                                                                                               320611
class java.util.HashMap$Entry                                                                157808
class java.util.Hashtable$Entry                                                               135878
class java.lang.Boolean                                                                         102554
class com.mysql.jdbc.ConnectionProperties$BooleanConnectionProperty      96150
class java.lang.Integer                                                                            23076
class com.mysql.jdbc.ConnectionProperties$IntegerConnectionProperty       19230
class com.mysql.jdbc.ConnectionProperties$StringConnectionProperty         14102
class java.lang.Object                                                                               5005
class com.mysql.jdbc.ConnectionProperties$MemorySizeConnectionProperty  2564
class java.util.Properties                                                                           2564
class java.util.HashMap                                                                             2328
class java.lang.ref.Finalizer                                                                        1285
class com.mysql.jdbc.Connection                                                               1282
class sun.util.calendar.ZoneInfo                                                                 1282
class com.mysql.jdbc.DatabaseMetaData                                                    1282
class java.lang.Throwable                                                                         1282
class com.mysql.jdbc.log.Log4JLogger                                                       1282
class com.mysql.jdbc.profiler.ProfileEventSink                                             1282
class java.util.Hashtable$KeySet                                                                1282
class [S                                                                                                  1271
class java.util.ArrayList                                                                             1237
class java.util.Collections$SynchronizedSet                                                 1074

Without the parameter profileSQL I always have 11 instances of class com.mysql.jdbc.Connection,
it seems that every five minutes or so some new connection instances are created.

I would like to have a log of every single query in my production system for one or two days
because I have some performance problems in my application and I do not know if the
problem arises from SQL-statements or programs running under jboss. Mysql-connector-java sits in the middle between jboss and mysqld, so I hoped, that I can get some useful information
with profileSQL=true. This diagnostic is better than a full mysql-log, because profileSQL gives me comlete time information and I see this in the jboss-log together with the log of my jboss application.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/7722